Subject: Re: Support logical replication of DDLs

From: Amit Kapila <[email-masked]>
Date: Thu, 28 Mar 2024 17:33:15 +0530

On Thu, Mar 28, 2024 at 5:31 PM Andrey M. Borodin <[email-masked]> wrote:
>
> This thread is registered on CF [0] but is not active since 2023. Is anyone working on this? I understand that this is a nice feature. Should we move it to next CF or withdraw CF entry?
>

At this stage, we should close either Returned With Feedback or
Withdrawn as this requires a lot of work.

-- 
With Regards,
Amit Kapila.

---

From: Konstantin Berkaev <[email-masked]>
Date: Thu, 25 Jul 2024 12:42:34 +0700

чт, 25 июл. 2024 г. в 12:35, Amit Kapila <[email-masked]>:

> > This thread is registered on CF [0] but is not active since 2023. Is
> anyone working on this? I understand that this is a nice feature. Should we
> move it to next CF or withdraw CF entry?
> >
>
Hello there,
I'm interested in logical DDL replication and I've read through this
thread, which has provided me with a lot of valuable information. However,
I have a couple of questions that I hope, you could help me with:
1) It seems that a lot of the work done here is simply to extend the
existing functionality to work with JSONB. From a development point of
view, it seems appropriate to separate this into a new discussion and
commit, just to expand the functionality of JSONB in terms of use for
development inside the postgres.
2) inside the timeline, it seems that work is moving towards creating an
MVP, which can then be finalized. As a result, it seems that the most
recent fixes, especially those related to table replication, have been
completed, or at least are not discussed in this section. The discussion
ends suddenly, so I don't quite understand: are we facing some unsolvable
problems, or we just didn't have enough time and energy to bring this to an
end?
3) Unfortunately, I couldn't find any specific tests specifically for ddl
logical replication. It seems logical to me that covering all possible
cases of ddl replication with tests will help move the work forward and
convince possible skeptics about worked issues. Did I miss this work or
were they just not implemented for some other reason?
4) We decided to use event trigger and logical_message instead of extending
the standard logical replication functionality by iterating through WAL
records in walsender and decoding there. Was there any reason why we didn't
even consider the possibility of doing everything within the structure of
the existing logical replication architecture, simply extending it to work
with ddl?
5) As for event triggers, I am confused by its use in terms of security and
fault tolerance. After reviewing the source code of event triggers, I did
not find any problems, but it seems strange that this technology is not
used inside Postgres. Perhaps there are reasons for this, or is it such a
good technology that it has no problems (or did I just skip this
discussion)?
6) Regarding testing: Have any synthetic tests been performed to measure
the speed of our replication? How much can we increase the size of WAL, and
how does it compare with classical logical and physical replication?

--
With Regards,
Konstantin Berkaev

---

From: Vitaly Davydov <[email-masked]>
Date: Mon, 2 Feb 2026 19:04:32 +0300

Dear Hackers,

I see, that the primary idea in this thread is to capture DDL statements with
the use of the utility hook, convert it into a json-like format, save it in the
WAL (xl_logical_message) and send it to a peer using the logical replication
subsystem (walsender). This approach has a major challenge that was already
highlighted: DDL statements may be too complex to convert it into a json string
(for example, unlogged/temporary objects in DDL statements).

An alternative approach, that was highlighted in the discussion by Dilip Kumar
is to decode system catalog changes. It helps to deal with complex DDL
statements containing temp objects and to work with a final representation of
changes stored in the WAL. Personally, I like this approach.

I would share the following idea:

1. Log into the WAL system catalog changes (tuples) suitable for logical
decoding (introduce a new wal_level = logical_ddl). I think, not all system
catalog changes are needed for decoding (not sure, we have to decode pg_depend
changes).

2. Implement a decoder of system catalog changes, that can produce a parse tree
using existing structures from parsenodes.h.

3. Based on the decoded parse tree, we can convert it into json or DDL SQL
statements in the output plugin. ParseTree to DDL SQL converter can be built-in
into the core. Output plugin can decide which converter to use. DDL sql can be
directly applied on the replica.

4. Another option is to create json/ddl-sql from system catalog changes without
an intermediate representation, but, anyway, when we interpret system catalog
changes we have to temporary save current data in some structures. Parsenodes
is the already existing solution for it.

The open question: can we unambiguously decode system catalog changes?

I would appreciate any feedback.

With best regards,
Vitaly

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 4 Feb 2026 16:39:38 +0900

Hi,

> is the already existing solution for it.

IIUC, one of the main challenges of the "deparsing DDL parse tree"
idea is the maintenance burden. If we implement logic to deparse parse
nodes back to SQL text, we would end up updating that deparsing code
every time the underlying parse node definition changes (which happens
frequently in internal structures). This introduces a substantial and
ongoing maintenance cost.

In that light, the idea proposed above seems to increase this burden
rather than alleviate it. It requires implementing not only the DDL
deparsing (converting parse nodes back to SQL query) at step 3, but
also the reconstruction of system catalog changes into parse nodes at
Step 2. This implies maintaining complex mappings in two places
whenever internal structures change.

I've been researching this area lately and experimenting with several
approaches (including the idea of decoding system catalog changes).
While I'm still not sure what is the best approach for DDL
replication, even with the deparse approach I agree that performing
this work at decoding time would be better than doing that at
execution time using event triggers, as it allows all logical decoding
plugins to handle DDLs consistently.

> The open question: can we unambiguously decode system catalog changes?

While it sounds challenging, it sounds promising. ISTM it is possible
to infer the executed DDL command from catalog changes. The crucial
point is how to implement this cleanly and to minimize the maintenance
burden as much as possible, especially because this code would be used
only for logical decoding. Instead of reconstructing the full internal
parse nodes, we might want to construct a stable, abstract
representation containing only the necessary information for
replication.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

---

From: Ashutosh Bapat <[email-masked]>
Date: Wed, 4 Feb 2026 17:14:10 +0530

> representation containing only the necessary information for

Consider an example of ALTER TABLE tab ADD PRIMARY KEY (id) where id
is an existing column in table tab. This will add a constraint on the
table and also create an index. Thus effectively two DDLs will be
executed. If we decode catalog changes we need to make sure that only
the original DDL is replicated; else the apply worker downstream will
cause an ERROR, stalling the replication. Similar is the case with
CREATE TABLE ... AS ... - we should only replicate the CREATE TABLE
and let the regular replication handle data replication. I think we
need to somehow annotate the WAL containing catalog changes to
indicate whether those represent the original DDL or derived DDL and
decode only the WAL corresponding to the original DDL.

If there are multiple DDLs in the same user SQL, decoding only the
original DDL from the WAL would help. Also we will be able to apply
table/column based filtering appropriately.

-- 
Best Wishes,
Ashutosh Bapat

---

From: Andreas Karlsson <[email-masked]>
Date: Mon, 9 Feb 2026 14:40:42 +0100

> is the already existing solution for it.

With this approach how do you intend to handle DDL changes which alter 
data? To take a simple case we have the USING clause when altering a 
column. Maybe it is a fine limitation to just not support it but I am 
not convinced.

Andreas

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 12 Feb 2026 17:12:02 -0500

> ongoing maintenance cost.

I agree maintenance is the big blocker, but the maintenance is two
parts:

1.  writing the patch to adjust for new features in each major release 
2.  testing the patch

People create some strange database schemas, so testing will be
difficult.

pg_upgrade had a similar challenge, and I found that pushing as much of
the changes _out_ of pg_upgrade and to other parts of the system, e.g,,
pg_dump, was a big help.  I am not sure if that is possible for
replicated DDL, but if it is, I would pursue it.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.

---

From: Amit Kapila <[email-masked]>
Date: Mon, 16 Feb 2026 15:24:16 +0530

> replicated DDL, but if it is, I would pursue it.

The other reason to pursue a deparsing approach is to allow "Schema
and Object Redirection" between source and target. For example, users
may want to redirect operations from a source object (e.g.,
HR.ACCOUNTS on the source) to a different target object (e.g.,
HR_BACKUP.ACCT on the target). Many people in the past supported such
flexibility in DDL replication which is one of the reasons we pursued
a deparsing approach in the past though that project/approach is
paused for the time being. I admit that there were many unresolved
challenges apart from the maintenance part of that approach.

Personally, I think it is better to first finish initial sync of DDLs
as previously proposed in thread [1]. That part is independently
useful and would be a good base step for the incremental replication
as discussed here. For example, users don't need to worry whether
their ALTER's will be replicated without an ERROR. OTOH, initial sync
of schema prevents users from snapshot related problems during initial
data transfer during set up of subscriber. Now, users need to use
pg_dump/restore kind of functionality to do initial setup between
publisher and subscriber then start replication. If there are more
DDLs ini-between those steps then the initial copy may turn out to be
not useful. Additionally, initial sync could be useful in a number of
other cases: (a) Many times
one needs a clone of the production schema to run tests against, (b)
Sometimes users want to run heavy, complex reporting queries without
slowing down the main application. So, one can clone required tables
and start such reporting queries, (c) In cases like (b), many times,
users may not want incremental sync that drops the index as it could
be required for reporting purposes.

[1]: https://www.postgresql.org/message-id/db02e6773adb4dbcb5b9bb3803ebe340%40amazon.com
-- 
With Regards,
Amit Kapila.

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 16 Feb 2026 10:16:56 -0500

> challenges apart from the maintenance part of that approach.

I understand the desire to get two features from one set of changes, but
taking one hard feature and trying to get a second one accomplished
usually, for me, ends in failure.

---

From: Vitaly Davydov <[email-masked]>
Date: Fri, 20 Feb 2026 18:07:07 +0300

Hi Andreas,

On 2/9/26 16:40, Andreas Karlsson wrote:
 > With this approach how do you intend to handle DDL changes which alter data?
 > To take a simple case we have the USING clause when altering a column. Maybe
 > it is a fine limitation to just not support it but I am not convinced.

When a column is altered, new tuples with altered data are inserted. Such tuples
are decoded as inserted in logical replication. The pg_attribute change
(ALTER COLUMN) will be decoded first, but inserted tuples will be decoded later.

In another words, I may say that ALTER COLUMN consists of two steps:
(1) create a new column (and drop old column)
(2) insert modified tuples

The USING clause just affects the values of the inserted tuples, I think.

Consider the following original schema with data:
CREATE TABLE t(x int);
INSERT INTO t(x) VALUES(0);
INSERT INTO t(x) VALUES(1);
INSERT INTO t(x) VALUES(2);

When we apply:
ALTER TABLE t ALTER COLUMN x TYPE double precision;

The decoded operation sequence looks like below. In brackets: (txid, cid).

pg_attribute_update (766, 1) rel = t, attname = x, atttypid (new/old): double 
precision / integer
pg_class_insert (766, 2) rel = pg_temp_16384, relkind = r
pg_attribute_insert (766, 2) rel = pg_temp_16384, attname = x
...
pg_decode_change (766, 3): rel = t, insert
pg_decode_change (766, 3): rel = t, insert
pg_decode_change (766, 3): rel = t, insert
pg_class_update (766, 3) rel = t, relkind = r, relrewrite: 0
pg_class_update (766, 3) rel = pg_temp_16384, relkind = r, relrewrite: 16384
pg_attribute_delete (766, 6)
...
pg_class_delete
commit_txn

For query:
ALTER TABLE t ALTER COLUMN x TYPE double precision USING (x::double precision + 1.2)
the operation sequence seems to be the same.

By decoding pg_attribute update we decide that ALTER COLUMN is executing.
At first glance, column type change with USING should be easily decoded. I think
the temp table is created here to convert values to the new type, and it should
be ignored when decoding, because new tuple values will be inserted and decoded
as new ones.

With best regards,
Vitaly