Re: Proposal: Conflict log history table for Logical Replication

On Sun, 30 Aug 2026 at 20:43, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Wed, Aug 26, 2026 at 8:01 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> Updated version of 0002, based on offlist testing by Nisha, revealed
> that the assumption that a column's maximum size could become 6x its
> original size during JSON conversion is incorrect. One edge case is a
> column of type array (int[]). A huge array but mostly empty(all NULL),
> can have a small storage size but produce a much larger JSON since
> each element is serialized. For example
> - A 4096 byte text column becomes ~24KB json (6x), which stays under
> the budget even accumulated across all columns (24,554 × 1600 × 3 ≈
> 118 MB).
> - But a 4096 byte all-NULL int[] column can produce ~480KB(120x) of
> json in a edge case.
> Based on this, Amit suggested a offlist POC of the patch which Nisha
> and I further modified. It still needs more review, testing, and logic
> validation, but I am sharing it here so we can review and provide
> feedback.

Thanks for the updated patch.
Few comments:
1) The omitted length shown in some cases is not correct.
This can be reproduced by the following:
-- Create type and table in both publisher and subscriber
CREATE TYPE bigenum AS ENUM ('x');
CREATE TABLE conf_marker (a int PRIMARY KEY, arr int[], e bigenum);

-- Subscriber
-- Only the subscriber serializes to JSON, so only it needs the cast. This is
-- what puts the enum column on datum_to_json_internal()'s JSONTYPE_CAST path
-- with a rendering far larger than the value.
CREATE FUNCTION bigenum_to_json(bigenum) RETURNS json
AS $$ SELECT to_json(repeat('a', 30000)) $$
LANGUAGE sql IMMUTABLE;
CREATE CAST (bigenum AS json) WITH FUNCTION bigenum_to_json(bigenum);

-- Have logical replication setup with above using
track_commit_timestamp as true in subscriber.

-- Publisher
INSERT INTO conf_marker VALUES (1, NULL, NULL);

-- Subscriber
-- Delete the record in subscriber to simulate update_missing conflict
DELETE FROM conf_marker;

-- Publisher
UPDATE conf_marker
SET arr = array_fill(NULL::int, ARRAY[4000]), e = 'x'
WHERE a = 1;

-- The above update generates update_missing conflict in the subscriber
postgres=# select relname, conflict_type, remote_tuple,
has_omitted_values from pg_conflict.pg_conflict_log_16395;
relname | conflict_type |
remote_tuple | has_omitted_values
-------------+----------------+-----------------------------------------------------------------------------+--------------------
conf_marker | update_missing |
{"a":1,"arr":{"omitted":true,"length":524},"e":{"omitted":true,"length":0}}
| t
(1 row)

The omitted length shown as 0 for column e is not correct.

Also the 524 shown for column arr seems incorrect, should it be 20001:
SELECT length(to_json(array_fill(NULL::int, ARRAY[4000]))::text);
length
--------
20001
(1 row)

2) The 16kB mentioned is not correct:
+ <para>
+ A column value is either recorded exactly or not at all; it is never
+ truncated. Any value larger than 16kB is replaced in the
+ JSON columns by an object recording that it was omitted
+ together with its length in bytes, for example:
+<programlisting>
+{"a" : 1, "b" : {"omitted":true,"length":190000000}}

We can see omitted column having lengths 3004, 5004, 10004, 15004
which are less than 16kB:
postgres=# select relname, conflict_type, remote_tuple from
pg_conflict.pg_conflict_log_16395 where has_omitted_values = true;
relname | conflict_type | remote_tuple
---------+-----------------------+--------------------------------------------------
tab | update_origin_differs |
{"a":10,"b":{"omitted":true,"length":190000004}}
tab | update_origin_differs | {"a":10,"b":{"omitted":true,"length":15004}}
tab | update_origin_differs | {"a":10,"b":{"omitted":true,"length":10004}}
tab | update_origin_differs | {"a":10,"b":{"omitted":true,"length":5004}}
tab | update_origin_differs | {"a":10,"b":{"omitted":true,"length":3004}}
(5 rows)

This needs to be updated accordingly to mention based on the new code
changes done.

3) The actual output from clt does not have the spaces around "a" and "b":
<programlisting>
{"a" : 1, "b" : {"omitted":true,"length":190000000}}
</programlisting>

I felt it should be:
{"a":1,"b":{"omitted":true,"length":190000000}}

Regards,
Vignesh