Logical Replication of DDLs: A Multi-Year Design Struggle

The Core Problem

PostgreSQL's logical replication, since its introduction in 10, has replicated only DML (INSERT/UPDATE/DELETE/TRUNCATE). Schema changes must be applied manually on both publisher and subscriber, with users running ALTER SUBSCRIPTION ... REFRESH PUBLICATION after structural changes. This is a significant pain point for:

1. Online major-version upgrades — the most-cited motivating use case. Users want to create a logical replica on a newer version, catch it up, then switch over.
2. Long-running subscriptions where schemas evolve (adding columns, indexes, tables).
3. Multi-database consolidation for analytics.

The thread, started by Zheng Li (AWS/RDS) in Feb 2022, proposed closing this gap. It ran for ~4 years with dozens of patch revisions, many reviewers, and ended without commit — a cautionary tale of a feature whose scope repeatedly outran the committer community's appetite for the implementation complexity.

Two Competing Design Approaches

Approach 1: Raw DDL String + search_path (Zheng Li's original)

The simplest approach: WAL-log the original DDL command string via LogLogicalMessage() from ProcessUtilitySlow(), along with search_path and role context, then replay it verbatim on the subscriber. A new WAL record type xl_logical_ddl_message was introduced (similar to xl_logical_message but carrying extra context).

Advantages: Small patch, easy to understand, no maintenance burden when DDL syntax evolves.

Fatal limitations identified during review:

• Volatile functions in DDL — ALTER TABLE foo ADD COLUMN bar double precision DEFAULT random() would produce divergent data on publisher vs. subscriber. CREATE TABLE AS SELECT ... similarly depends on publisher state.
• Partial-table publications — DROP TABLE a, b where only a is published cannot be faithfully replayed without splitting.
• Cross-version compatibility — syntax changes between major versions make naive string replay fragile in precisely the online-upgrade case that motivates the feature.
• Cannot rewrite the command (e.g., schema remapping, filtering by column list) without re-parsing.

Approach 2: Event Triggers + JSON Deparsing (Álvaro Herrera's resurrected 2015 design)

Proposed by Amit Kapila (with Ajin Cherian, Hou Zhijie) in May 2022, reviving Álvaro's abandoned deparsing work. The flow:

1. Register event triggers (ddl_command_end, ddl_command_start, table_rewrite, table_init_write) when CREATE PUBLICATION ... WITH (ddl=...) is executed.
2. On trigger fire, deparse the parse tree into a structured JSON "blob" with a format-string template and named parameter slots:

   {"fmt": "CREATE %{persistence}s TABLE %{if_not_exists}s %{identity}D (%{table_elements:, }s) ...",
    "identity": {"schemaname": "public", "objname": "foo"}, ...}
   

3. WAL-log via LogLogicalDDLMessage (new xl_logical_ddl_message record and REORDER_BUFFER_CHANGE_DDL).
4. Subscriber's apply worker expands the JSON back to SQL via ddl_deparse_expand_command() and executes it.

Advantages (championed by Álvaro, Robert Haas):

• Schema-qualified identifiers everywhere — no search_path ambiguity.
• Arbitrary machine transformation — remap schema A→B with a JSON edit.
• Filterable — decompose DROP TABLE a,b into individual drops, filter unpublished tables on the publisher.
• Portable across versions — a newer subscriber can process an older publisher's JSON.
• Reusable infrastructure — other logical decoding plugins can consume the format.

Fatal limitations repeatedly identified:

• Enormous maintenance burden — every new DDL node field must be taught to the deparser, with no compiler help. ddl_deparse.c grew to 9000+ lines.
• Special-case code for catalog lookups — deparser must read pg_class, pg_constraint, pg_index, compute schema-qualified type names, handle printTypmod specially, etc. Cannot be fully auto-generated from node definitions (Vignesh confirmed this after a PoC).
• Testing gap — no reliable way to verify all DDL paths are covered. Test module test_ddl_deparse_regress grew to thousands of lines of expected output.
• ObjTree intermediate representation added overhead — ParseTree → ObjTree → JSONB → string. Eventually removed (patch v0008 by Shveta/Vignesh), compressing to ParseTree → JSONB directly.

Key Technical Sub-Problems and Their Resolutions (or lack thereof)

Table Rewrite During ALTER

ALTER TABLE ... ALTER COLUMN ... TYPE ... with a USING clause rewrites the entire heap. Hou Zhijie's design:

1. Send the ALTER command first.
2. Convert the rewrite's synthetic INSERTs into UPDATEs using replica identity, and ship them.

This handles the case where publisher and subscriber have diverged data (extra rows on subscriber get rewritten locally via the ALTER; rows on both get the publisher's computed values via UPDATE). It requires replica identity on rewritten tables and was restricted to immutable functions (volatile functions in such clauses raise an error).

Andres Freund pushed back that USING(foo::int8) vs USING(pg_size_bytes(foo)) are invisible in WAL — the parse tree matters. Sawada-san countered that replicating the statement (not the rewritten rows) is the right long-term design since newer Postgres versions may eliminate rewrites.

CREATE TABLE AS / SELECT INTO

The query portion cannot be safely re-executed on the subscriber (subquery tables may not exist, or differ). Resolution: force skipData=true on the subscriber's parsetree so only the table shell is created; data flows via normal DML replication. Hou later proposed deparsing CTAS into a plain CREATE TABLE (no subquery) via a new table_init_write event trigger fired between table creation and data population.

CREATE TABLE ... LIKE ... INCLUDING ALL

Event triggers fire for both the parent CREATE TABLE and all synthesized ALTER TABLE subcommands, causing duplicate-replay errors. Zheng's fix: add table_like flag to AlterTableStmt so the deparser can skip internally generated subcommands. Li Jie's test caught LIKE STORAGE leaking because storage isn't expressible in the CREATE TABLE body — an unresolved edge case.

search_path Security vs. Usability (Jonathan Katz's test case)

Jonathan demonstrated that unqualified table references inside plpgsql function bodies (a common pattern) fail on the subscriber because InitializeApplyWorker hardens search_path='' (post-CVE commit 11da97024a). Options discussed:

• Turn off check_function_bodies on the subscriber (agreed).
• Allow a per-subscription search_path option.
• Use SQL-standard BEGIN ATOMIC bodies which embed fully-qualified references at parse time.

No clean resolution — this is a fundamental tension between the search_path hardening done for security and the usability expectation that functions replicate transparently.

Ownership of Replicated Objects

By default, replicated objects are owned by the subscription owner (a superuser). Zheng added a subscription option match_ddl_owner=on (default true) that embeds myowner in the JSON and restores ownership via SET ROLE. Amit noted this interacts with the separate effort to allow non-superuser subscription owners, and ideally depends on role replication.

Infinite Loop in Bidirectional Setups

Replication origins (the DML solution in the concurrent thread by Vignesh) were confirmed sufficient — LogLogicalDDLMessage naturally includes the origin, so origin=none filtering on subscriptions prevents loops.

Global Objects (Roles, Databases, Tablespaces)

Zheng spun off a separate thread. These are not captured by event triggers and aren't database-scoped. Agreement: defer to a later phase.

Temporary/Unlogged Tables

Agreement (Amit Kapila): don't replicate temporary tables; document it. Debate on unlogged: initially replicate (to support SET LOGGED transition), later dropped.

Initial Schema Sync

Recognized early as dependent on DDL replication but deferred to a separate patch. Euler Taveira started a thread. By 2025, Sawada-san observed that initial sync and DDL replication should share a pg_get_table_ddl()-style function.

Why the Project Stalled

Tom Lane's Mar 2023 intervention crystallized the doubts:

"I came away quite depressed. The patchset has ballooned to over 2MB... fundamental semantics issues remaining, not to mention clear-and-present security dangers, not to mention TODO comments all over the code... translate utility parse trees into JSON and send that down the wire... I don't see evidence that anyone's thought much about how [it will be more robust than outfuncs.c]... The whole point of using logical rather than physical replication is that the subscriber installation(s) aren't exactly like the publisher. Given that, how can we expect that automated DDL replication is going to do the right thing often enough to be a useful tool rather than a disastrous foot-gun?"

Michael Paquier's Jun 2023 review echoed: "extremely high maintenance cost long-term because it requires anybody doing a change in the parsed tree structures... to also change the code paths introduced by this patch set."

Álvaro (2023-05-03) and Amit repeatedly urged splitting into smaller pieces, but the patch series remained unwieldy (8+ numbered patches, dependencies between deparser, event triggers, replication protocol, apply worker, pg_dump).

The thread effectively died in Jul 2023, was formally closed "Returned With Feedback / Withdrawn" in Mar 2024 (Amit Kapila confirming "requires a lot of work"), and was resurrected by Sawada-san in Apr 2026 with a new proposal.

The 2026 Restart: DDLCommand Data Type

Sawada's new proposal abandons both prior approaches:

1. No event triggers — write a new DDLCommand (like CollectedCommand) directly to WAL at DDL execution sites. Plugin-agnostic; test_decoding sees it for free.
2. No JSON deparsing from parse trees — reconstruct DDLs from catalog state using pg_get_xxx_ddl()-style functions with historical snapshots at decode time. Maintenance cost is bounded since catalog schemas change less than parse nodes.
3. Finer-grained filters — publish_ddl = 'create, alter' to let users exclude DROP.

Amit Kapila objected that multiple senior committers (Álvaro, Robert Haas) previously endorsed the JSON deparsing approach and it shouldn't be abandoned without their input.

Hannu Krosing proposed a pg_dump-style approach: deparse from the time-travel catalog at the decode snapshot. Dilip and Andres countered that this cannot work for cases like ALTER ... TYPE ... USING (expr) where the expression is invisible in the resulting catalog state. Vitaly Davydov proposed a middle path: log catalog tuple changes (wal_level=logical_ddl) and reconstruct ParseNodes at decode time.

The fundamental tension remains unresolved: any approach that reconstructs DDL from catalog state loses information present only in the parse tree; any approach based on the parse tree has a punishing maintenance burden across major versions.

Architectural Lessons

1. Event triggers as an implementation substrate for replication have subtle lifecycle problems — they aren't transactional with slot creation/drop, can't be created on standbys, and multiple plugins clash.
2. Deparsing is genuinely hard: catalog access is needed for names/types/defaults; field ordering in JSON format strings doesn't map 1:1 to struct fields (making auto-generation of the deparser from node definitions infeasible, as Vignesh's PoC demonstrated).
3. The present: false pattern in JSON — elements are emitted with absent markers so consumers can "unhide" them to add clauses (e.g., adding a tablespace). This flexibility was cited as justification for the verbose JSON representation but added significant code volume and testing surface.
4. Subcommand attribution for partitioned/inherited tables — ALTER on a parent may fire subcommands attributed to children; the patch added logic to skip deparsing where subcmd->address.objectId != relId, a fragile heuristic.
5. Publication syntax evolution — initial ddl='database'|'table' evolved through discussions toward command-tag-level granularity (ddl='create table, alter table'), and ultimately the 2026 proposal adds publish_ddl separate from publish.

Summary

This is the longest-running unresolved feature in logical replication's history. It pits the flexibility and correctness guarantees of structured intermediate representations against the practical maintenance burden of keeping such representations synchronized with the evolving catalog and parse-tree schema. The core community consensus — articulated most forcefully by Tom Lane — is that DDL replication is inherently risky and must be deliverable in a small, well-tested initial form restricted to tables, with the broader "replicate everything" scope deferred. As of Sawada-san's 2026 revival, no committable design has emerged.