Re: Proposal: Conflict log history table for Logical Replication

On Fri, Jun 26, 2026 at 3:40 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Jun 25, 2026 at 4:57 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Thu, Jun 25, 2026 at 10:34 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> > > On Wed, 24 Jun 2026 at 19:27, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > >
> > >
> > > 2) jsonb supports indexing, whereas json does not. Was json chosen
> > > here because inserts are faster due to the lack of parsing and binary
> > > conversion?
> > >
> >
> > I think it is more because the local/remote tuples are usually data
> > which we want users to see as it was in original tables, using jsonb
> > can change the ordering of columns or remove some space etc. We can
> > add a comment on the lines of: "preserve the exact tuple
> > representation (column order, formatting) for the audit record; the
> > value is natively json so this avoids a per-conflict conversion". Now,
> > this is true for tuple data but I am not so sure if the same thing
> > applies to the replica_identity column in CLT. Would users ever want
> > to query using ORDER/GROUP BY on replica_identity or use DISTINCT on
> > it, because if that is possible then the current schema would result
> > into ERROR as follows:
> > postgres=# select * from pg_conflict.pg_conflict_log_16394 order by
> > replica_identity;
> > ERROR: could not identify an ordering operator for type json
> >
> > This needs more thoughts from the perspective of how users would like
> > to fetch the data.
> >
>
> The values stored for replica_identity are heterogeneous across the
> table (different key shapes per relid), and for the RI-FULL case it
> isn't even a stable identifier, two conflicts on the "same row" can
> carry different whole-tuple JSON. So, I don't see a case for creating
> an index on the RI column as well and rather users would like to see
> the exact value used to find the local tuple. So, we can add a comment
> on the lines: "The tuple/key columns (remote_tuple, replica_identity,
> local_conflicts) are typed json rather than jsonb on purpose: they
> hold an exact audit snapshot of the applied tuples and replica
> identity, and json preserves the verbatim representation whereas jsonb
> would normalize it. Indexing them (jsonb's main advantage) wouldn't
> help anyway, as the conflict log is looked up by its scalar columns
> (relid, conflict_type, commit timestamp) while these JSON columns are
> per-conflict payload to inspect, not search keys."

I have added this comments.

> One more thing that bothers me is that it will be inconvenient for
> users to identify whether logged values for replica_identity are for
> an index or a complete tuple via replica identity full, so I propose
> to add a boolean column replica_identity_full. I also considered to
> add replica_identity_index but preferred a boolean instead because
> this is a historical/audit row, and the index may be dropped or
> replaced later, leaving a meaningless OID which is opposite of what an
> audit record should do.

I have added the column replica_identity_full and ordered the column as follows:

static const ConflictLogColumnDef ConflictLogSchema[] = {
<----------Other columns---------------->
{ .attname = "replica_identity_full", .atttypid = BOOLOID },
{ .attname = "replica_identity", .atttypid = JSONOID },
{ .attname = "remote_tuple", .atttypid = JSONOID },
{ .attname = "local_conflicts", .atttypid = JSONARRAYOID }
};

> The other question is whether we create an internal index on one of
> the columns? Currently, there's no established query pattern to index
> for. The plausible candidates are relid ("conflicts for table X"), or
> remote_commit_ts (time-based cleanup) but those are guesses. Indexing
> the JSON columns we've already ruled out (payload, not keys). Picking
> an index before we know how people actually query/prune the table
> risks indexing the wrong thing. We can cosider adding the index in
> later versions or even in PG20 itself if we get some user feedback.

Yeah, I think we can have index once the first patch is committed
based on the usage pattern.

--
Regards,
Dilip Kumar
Google