Thread

1. Proposal: Support Logical replication of large objects

   Dilip Kumar <dilipbalaut@gmail.com> — 2025-12-01T03:57:22Z

   Problem Statement
   ===============
   Large object replication is currently unsupported in logical
   replication, which presents a significant barrier to using logical
   replication when large objects are in use. This lack of support limits
   the usability of logical replication for applications that heavily
   rely on large objects.
   
   Background
   ==========
   The primary challenge preventing support for this feature stems from
   large objects being stored within the catalog. Catalog decoding is
   intentionally unsupported because replicating a catalog entry, such as
   a pg_class row, is meaningless without the underlying storage and
   related catalog entries (e.g., pg_attribute, pg_constraint, etc.).
   Nevertheless, the data within pg_largeobjects is different; it behaves
   more like regular user table data than typical catalog data.
   
   While it is technically possible to enable logical logging and
   replication for rows in pg_largeobjects by modifying certain macros,
   this approach presents several key drawbacks:  1) The decoded data
   needs a logical representation, as a single pg_largeobject row is
   meaningless outside of PostgreSQL.  2) The apply worker may not have
   the necessary permissions to directly insert into the pg_largeobject
   catalog if the subscription was not created by a superuser.
   
   Proposal
   =======
   The proposed solution involves introducing a configuration parameter
   that allows a large object to be logically logged, similar to a
   standard user table. Then the decoder will convert the pg_largeobjects
   rows into logical operations, such as LOWRITE: OID, OFFSET, DATA,
   LENGTH.  The decoder will then translate the standard single-row
   insert/update WAL records generated for the internal pg_largeobject
   table rows into logical operations formatted as LOWRITE: OID, OFFSET,
   DATA, LENGTH, where the OFFSET is calculated as pageno (corresponding
   to pg_largeobject row) * LOBLKSIZE. Subsequently, the apply worker on
   the subscriber side converts this logical operation into lo_open(),
   lo_seek() and lowrite() operation.  While there is potential for
   further optimization by generating the LOWRITE operation only for the
   modified data instead of for the entire LOBLKSIZE bytes this behavior
   is consistent with how updates are currently logged for standard user
   tables.
   
   I have a POC patch for the decoding part of this implementation. I
   need feedback on the overall strategy before I put effort on cleanup,
   testing, and further development. The patch still requires the
   following to be completed:
   a) This is just POC so it needs substantial cleanup and testing,
   implementing other large objects operations other than lowrite.
   b) Development for the 'apply' side of the implementation.
   c) Implementation of a configuration parameter to conditionally enable
   logically logging the large object (currently, it always logs the
   large object).
   
   Open points to be discussed
   ======================
   1. I propose that logically logging the pg_largeobject tuple data
   should be controlled by a configuration parameter, rather than being
   implemented unconditionally.  Making it configurable allows users to
   opt-in to this behavior, preventing unnecessary logging overhead for
   users who do not require large object replication. Thoughts?
   
   2. Supporting lo_create() operations : We should consider extending
   this proposal to support the lo_create() operation as well.  If the
   large object replication configuration is enabled, we could generate a
   new WAL record directly from lo_create().  Alternatively, we could
   leverage the existing WAL record for the insertion into
   pg_largeobject_metadata and decode it into a logical lo_create
   operation.
   
   While some may categorize lo_create() as a DDL operation, it behaves
   practically like a DML operation. When a table contains a large object
   column, new large objects are created frequently, often for every row
   insertion making it a commonplace runtime event rather than a schema
   design activity.
   
   Acknowledgements:
   ================
   I would like to express my sincere thanks to Amit Kapila, Michael
   Bautin, Hannu Korosing, Noah Misch, and Joe Conway for their valuable
   input, including discussing various alternatives and suggesting
   different approaches to this.
   
   Also added some tests using test decoding to show how it works and
   here is one of the examples.
   
   postgres[1000776]=# select lo_create(1000);
    lo_create
   -----------
         1000
   (1 row)
   
   postgres[1000776]=# SELECT lowrite(lo_open(1000, CAST(x'20000' |
   x'40000' AS integer)), 'try decoding test data');
    lowrite
   ---------
         22
   (1 row)
   
   postgres[1000776]=# SELECT data FROM
   pg_logical_slot_get_changes('regression_slot', NULL, NULL,
   'include-xids', '0', 'skip-empty-xacts', '1');
                                     data
   -------------------------------------------------------------------------
    BEGIN
    LO_WRITE: loid: 1000 offset: 0 datalen: 22 data: try decoding test data
    COMMIT
   (3 rows)
   
   -- 
   Regards,
   Dilip Kumar
   Google
   

2. Re: Proposal: Support Logical replication of large objects

   Bernd Helmle <mailings@oopsware.de> — 2025-12-02T13:06:06Z

   Am Montag, dem 01.12.2025 um 09:27 +0530 schrieb Dilip Kumar:
   > The decoder will then translate the standard single-row
   > insert/update WAL records generated for the internal pg_largeobject
   > table rows into logical operations formatted as LOWRITE: OID, OFFSET,
   > DATA, LENGTH, where the OFFSET is calculated as pageno (corresponding
   > to pg_largeobject row) * LOBLKSIZE. Subsequently, the apply worker on
   > the subscriber side converts this logical operation into lo_open(),
   > lo_seek() and lowrite() operation.  While there is potential for
   > further optimization by generating the LOWRITE operation only for the
   > modified data instead of for the entire LOBLKSIZE bytes this behavior
   > is consistent with how updates are currently logged for standard user
   > tables.
   
   
   Thanks for this, i think this is a long awaited feature, at least for
   those workloads that can't easily get rid of LOs...
   
   I didn't look into your POC (yet), but what happens if the subscriber
   database concurrently does lo_create()? Would that cause conflicting
   OIDs, preventing applying the records decoded from the publisher?
   
   	Bernd
   
   
   
   

3. Re: Proposal: Support Logical replication of large objects

   Dilip Kumar <dilipbalaut@gmail.com> — 2025-12-04T15:11:48Z

   On Tue, Dec 2, 2025 at 6:41 PM Bernd Helmle <mailings@oopsware.de> wrote:
   >
   > Am Montag, dem 01.12.2025 um 09:27 +0530 schrieb Dilip Kumar:
   > > The decoder will then translate the standard single-row
   > > insert/update WAL records generated for the internal pg_largeobject
   > > table rows into logical operations formatted as LOWRITE: OID, OFFSET,
   > > DATA, LENGTH, where the OFFSET is calculated as pageno (corresponding
   > > to pg_largeobject row) * LOBLKSIZE. Subsequently, the apply worker on
   > > the subscriber side converts this logical operation into lo_open(),
   > > lo_seek() and lowrite() operation.  While there is potential for
   > > further optimization by generating the LOWRITE operation only for the
   > > modified data instead of for the entire LOBLKSIZE bytes this behavior
   > > is consistent with how updates are currently logged for standard user
   > > tables.
   >
   >
   > Thanks for this, i think this is a long awaited feature, at least for
   > those workloads that can't easily get rid of LOs...
   
   Right
   
   > I didn't look into your POC (yet), but what happens if the subscriber
   > database concurrently does lo_create()? Would that cause conflicting
   > OIDs, preventing applying the records decoded from the publisher?
   
   I've implemented decoding for lowrite operations, specifically
   handling INSERT statements into pg_largeobject by translating them
   into corresponding lowrite operations. However, lo_create is currently
   not decoded. The patch expects the user to execute lo_create on the
   subscriber explicitly, similar to how DDL for tables is handled.
   
   But I understand the lo_create can be a very frequent activity so
   instead of treating them as DDL we should decode and replicate the
   lo_create as well and I have asked for the suggestion in my email.
   And if we replicate the lo_create as well then any existing
   conflicting OID on the subscriber will create conflict and I believe
   we can handle that as part of conflict detection.
   
   -- 
   Regards,
   Dilip Kumar
   Google
   
   
   
   

4. Re: Proposal: Support Logical replication of large objects

   Nitin Motiani <nitinmotiani@google.com> — 2026-05-04T04:53:44Z

   >
   > 4. If we want a more performant version, one idea is to support bulk
   > writes for large objects. Then the above solution can be made more
   > performant. I have not analyzed the work required. Suggestions on this
   > would be welcome.
   >
   
   I experimented with the bulk write idea. I've created a couple of
   patches for it. One implements lo_bulk_write and lo_bulk_put APIs. The
   other patch has the benchmark script to test out these APIs and shows
   3x improvement.
   
   Note I have not yet integrated this with the remaining replication
   work. That might also require a bulk read or copy API. I have worked
   on the bulk API independently of this patch and will look into
   integration if the general idea looks good. The bulk write (and bulk
   read) could also be useful for pg_dump and pg_restore. So I might move
   those to a different thread later. Currently I've reordered the
   patches and 0001 and 0002 are the bulk write and benchmark patch. And
   the original 4 patches for replication are now 0003-0006. Let me know
   if I should change this ordering.
   
   Here are the major changes for bulk write.
   
   1. I introduced two new functions in be-fsstubs.c:
      * lo_bulk_write(fds int4[], data bytea[])
      * lo_bulk_put(oids oid[], data bytea[])
   
   2. I created inv_bulk_write, inv_bulk_create, and inv_bulk_put
   functions to do the actual work.
   
   3. I use table_multi_insert to perform the actual bulk writes. The
   index updates are still done one by one.
   
   4. This optimization is limited to creating new objects (using
   lo_bulk_put) or appending to existing objects (lo_bulk_write). If
   lo_bulk_writes tries to insert in the middle of an existing object, we
   fall back to the standard inv_write instead of using `inv_bulk_write`.
   We check this using the large object's current size and by ensuring
   `offset % LOBLKSIZE` is zero, which prevents starting a bulk write in
   the middle of a page.
   
   5. I've added a few regress tests for these cases and benchmark tests
   which run multiple transactions of 1000 inserts each (bulk vs
   one-by-one insert). The bulk insert shows a 3x improvement. I think it
   might be bigger when a remote client is involved and network overhead
   is added. But I've not looked into it in too much detail.
   
   Let me know what you think.
   
   Thanks & Regards
   
   Nitin Motiani,
   Google