Re: Proposal: Conflict log history table for Logical Replication

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."

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.

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.

--
With Regards,
Amit Kapila.