How Not To Use PostgreSQL DDL Replication

Introduction

PostgreSQL's native logical replication enables selective replication of data changes from a publisher to one or more subscribers. However, it doesn't replicate Data Definition Language (DDL) commands, such as CREATE, ALTER, or DROP statements. This means that schema modifications made on the publisher must be manually applied to each subscriber node to maintain consistency. The official PostgreSQL documentation highlights this limitation and suggests using tools like pg_dump --schema-only for initial schema synchronization, with subsequent changes requiring manual intervention on each subscriber node.

If you're a developer or DBA working with logical replication, you should:

• Use pgEdge or other products or tools for complex or distributed setups.

• Plan ahead for TABLE REWRITE scenarios.

• Implement CI/CD pipelines that monitor schema changes.

• Stay informed with PostgreSQL’s roadmap and feature announcements.

Above all, don't assume logical replication will automatically replicate schema changes!

Choosing an Extension that Bridges the DDL Replication Gap

To address the lack of native DDL replication in PostgreSQL, community-driven and commercial products and tools have emerged, each offering varying degrees of automation and flexibility.

pgEdge
pgEdge takes an advanced approach by offering built-in, seamless DDL replication within its distributed PostgreSQL platform. Any schema changes made on one node are automatically and reliably propagated across all other participating nodes in a pgEdge cluster. This automation significantly reduces operational complexity, particularly in geo-distributed and multi-active environments. By handling both data and schema consistency out of the box, pgEdge enables truly distributed PostgreSQL deployments without the usual pain points.

pglogical
pglogical is a dual-licensed PostgreSQL extension that extends logical replication capabilities with support for DDL replication. The community version of pglogical provides the pglogical.replicate_ddl_command function, which captures DDL statements and sends them to a replication queue for execution on each subscriber node. This allows for automatic propagation of schema changes across instances, eliminating the need for manual synchronization, but requires careful configuration and compatibility checks to ensure DDL changes execute identically on all subscribers.

Thoughts on Production Implementation

While these extensions offer much-needed solutions for replicating DDL changes, it’s critical that you approach their implementation and daily use with care. It is best not to implement DDL replication until you fully understand the limitations, operational risks, and maintenance requirements of the replication solution you choose.

Blindly enabling DDL replication without considering schema compatibility, TABLE REWRITE scenarios, and transactional integrity can lead to future downtime, potential replication breakages, and unfortunately, data inconsistency. Simply put, DDL replication is not a “set it and forget it” feature, especially in a production environment.

To avoid replication pitfalls:

• Always test schema changes and DDL replication in a staging environment that mirrors production.

• Validate how your replication solution handles complex changes like column type alterations, constraint updates, or table rewrites.

• Monitor your database for subtle drift between publisher and subscriber schemas.

• Treat DDL replication as a controlled process within your CI/CD pipeline—not an ad-hoc change.

In short, the wrong way to use PostgreSQL replication (especially DDL replication)  is to assume it works like magic out of the box. The right way to use replication is to leverage purpose-built tools like pgEdge with strong operational discipline to ensure safe, predictable, and consistent schema evolution across distributed PostgreSQL systems.

Comparing Traditional PostgreSQL Replication vs. pgEdge

As you can see, pgEdge significantly simplifies operations while adding robust capabilities.

Best Practices When Using DDL Replication

If you're using or planning to use DDL replication, keep these best practices in mind:

• Apply additive schema changes to subscriber nodes first, or simply use the pgEdge AutoDDL feature.

• Avoid making schema changes during peak replication times.

For example, if you have a busy system and a relatively large table, you might want to split the following command into multiple SQL statements to avoid extensive table rewrites and locking:

ALTER TABLE my_table
ADD COLUMN IF NOT EXISTS my_table_uuid UUID NOT NULL
DEFAULT uuid_generate_v1();

That command could be split into multiple SQL statements (as follows):

ALTER TABLE my_table
ADD COLUMN IF NOT EXISTS my_table_uuid UUID NOT NULL;
UPDATE my_table_table SET my_table_uuid uuid_generate_v1();

After making changes to your table, always:

• Test DDL changes in a staging environment before applying them to production.

• Monitor replication status closely for early detection of schema mismatch.

• Use automation tools wherever possible to reduce human error.

When Not to Use DDL Replication

• There are still situations where you might choose not to use DDL replication, such as:
  When schema stability is high and changes to your DDL are rare.

• In single-region configurations with low complexity.

• When migration and synchronization are handled manually and controlled.

With pgEdge, you can use the spock.repair_mode function to control DDL and data replication.

FAQs

What is DDL replication and why does PostgreSQL not support it natively?
DDL replication refers to the propagation of schema changes across databases. PostgreSQL’s native logical replication currently doesn’t support DDL replication due to architectural limitations.

How does pgEdge handle TABLE REWRITE operations?
pgEdge tracks schema changes and manages table rewrites internally to ensure consistency across all nodes.

Can I use pgEdge for multi-cloud deployments?
Yes. pgEdge is designed to manage geo-distributed, multi-cloud, and multi-region PostgreSQL deployments.

Is there performance overhead when using pgEdge DDL replication?
Minimal. pgEdge optimizes the replication of DDL events without significantly impacting runtime performance.

How can I migrate my existing logical replication setup to pgEdge?
You can transition to pgEdge by first exporting your schema definition and data, then initializing pgEdge clusters, and finally leveraging its replication features for seamless sync.

For more information about pgEdge Distributed PostgreSQL, visit the pgEdge website.