Schema Evolution, DDL Coordination, Sequences, Large Objects, and Operational Gaps

Learning outcomes

Logical replication can keep table data moving while schema evolves—but it does not replicate most database-definition changes. That distinction becomes operationally critical during rolling application releases and major-version migrations: a perfectly healthy subscription can stop because the subscriber schema no longer accepts incoming data.

1. Built-in logical replication is mainly table DML

PostgreSQL logical replication replicates changes for published tables. It does not automatically replicate database schema, DDL commands, sequence state, or large objects. Views, materialized views, and foreign tables are not publication targets. Roles, grants, extensions, configuration, and application secrets are also outside the table-change stream.

2. Failure mode: publisher adds a required column first

Assume the application now needs a priority column. If the publisher starts sending a column that the subscriber target does not have, apply fails until the subscriber schema becomes compatible. The safer rolling-change direction for additive DDL is usually subscriber first.

ALTER TABLE app.ch15_work_ordersADD COLUMN priority smallint NOT NULL DEFAULT 3;SELECT column_name, data_type, is_nullable, column_defaultFROM information_schema.columnsWHERE table_schema = 'app'  AND table_name = 'ch15_work_orders'ORDER BY ordinal_position;

ALTER TABLE app.ch15_work_ordersADD COLUMN priority smallint NOT NULL DEFAULT 3;UPDATE app.ch15_work_ordersSET priority = 1,    changed_at = clock_timestamp()WHERE work_order_id = 15004;

When the publication has no explicit column list, newly added source columns are automatically included. If you use a column list, changing the schema alone does not add the new column to the publication contract; alter the publication deliberately after subscriber compatibility is proven.

3. Expand/contract instead of synchronized breaking DDL

A rolling migration often has old application instances and new application instances running simultaneously. The schema must support both during the transition. Use an expand/contract sequence:

1. Add subscriber-compatible nullable/defaulted structures first.
2. Add the publisher change.
3. Refresh/adjust the publication if necessary.
4. Deploy applications that can read/write the expanded schema.
5. Backfill or derive new values and validate invariants.
6. Stop old application versions.
7. Only then remove old columns/constraints in a coordinated contract phase.

SELECT pubname, schemaname, tablename, attnames, rowfilterFROM pg_publication_tablesWHERE pubname = 'ch15_servicehub_pub'  AND schemaname = 'app'  AND tablename = 'ch15_work_orders';

The key principle is that the subscriber must be able to apply every incoming change throughout the rollout. “DDL is transactional” does not make independently executed DDL on two databases atomic.

4. Sequence values are not synchronized

An identity column uses a sequence. Replicated inserts can carry explicit key values into the subscriber table, but the subscriber's independent sequence state does not automatically advance to reflect those incoming keys. If you later make the subscriber writable, its next sequence value can collide with already replicated rows.

SELECT max(work_order_id) AS max_replicated_idFROM app.ch15_work_orders;-- If this table used an identity/serial sequence:SELECT pg_get_serial_sequence('app.some_identity_table','id') AS seq_name;

-- Example pattern; substitute the verified sequence and table:SELECT setval(  'app.some_identity_table_id_seq',  (SELECT max(id) FROM app.some_identity_table),  true);

Sequence reconciliation belongs inside cutover acceptance, not as a one-time migration footnote. If writes continue on the publisher after you capture max(id), your sequence fix can become stale before cutover.

5. Large objects and out-of-band state

PostgreSQL large objects are not replicated by built-in logical replication. If ServiceHub stores binary documents through the large-object subsystem, a logical migration needs a separate transfer and validation path. Storing application payloads in normal table columns (including TOASTed bytea) has different replication semantics because those values belong to ordinary table rows.

6. Triggers, row security, and apply privilege are part of schema behavior

The apply process runs with session_replication_role=replica, so ordinary triggers/rules do not fire by default during ongoing apply. Initial table synchronization is COPY-like and has different trigger behavior. Subscription ownership and target table ownership also determine which role applies changes. An unexpected trigger/RLS/permission configuration can therefore create conflicts even when columns match.

SELECT n.nspname, c.relname,       pg_get_userbyid(c.relowner) AS ownerFROM pg_class AS cJOIN pg_namespace AS n ON n.oid = c.relnamespaceWHERE n.nspname = 'app'  AND c.relname = 'ch15_work_orders';SELECT tgname, tgenabledFROM pg_triggerWHERE tgrelid = 'app.ch15_work_orders'::regclass  AND NOT tgisinternal;

7. PostgreSQL 18 generated-column coordination

If publisher and subscriber both define a generated column and it is not explicitly published, the subscriber computes its own value. If PostgreSQL 18 publishes a stored generated value, the target published column must be a regular column. This is a version-sensitive migration contract: subscribers before PostgreSQL 18 have additional initial-copy limitations.

Record server major versions on both ends in every logical-replication change ticket. “Both are PostgreSQL” is not enough for generated-column and other evolving capabilities.

8. Verification and rollback discipline

SELECT work_order_id, priority, statusFROM app.ch15_work_ordersORDER BY work_order_id;SELECT subname, apply_error_count, sync_error_countFROM pg_stat_subscription_statsWHERE subname = 'ch15_servicehub_sub';

If apply errors begin immediately after DDL, stop changing multiple variables. Freeze application schema rollout, inspect the first subscriber error, restore target compatibility, then let the original source transaction retry. Avoid “fixing” by skipping transactions unless the skipped business changes are explicitly reconciled.

9. Schema compatibility is directional, not necessarily identical

The subscriber schema does not have to be byte-for-byte identical to the publisher. Target column order may differ, and a subscriber can have additional columns when defaults make incoming rows valid. That flexibility is useful during migration, but it increases the need for an explicit compatibility matrix. Record which columns are published, which target-only columns are populated by defaults, which constraints are stricter downstream, and whether text-format type conversion is relied upon.

Binary-format subscriptions narrow that flexibility because binary send/receive compatibility is type-specific and can differ across PostgreSQL major versions. For cross-version migration, default text format is usually the easier compatibility baseline unless binary behavior has been tested for every replicated data type.

Authoritative references

Logical replication is version-, privilege-, topology-, and schema-sensitive. These PostgreSQL 18 primary sources define the behavior used in this lesson.

• PostgreSQL 18 — Logical Replication Restrictions
• PostgreSQL 18 — Logical Replication Architecture
• PostgreSQL 18 — Generated Column Replication
• PostgreSQL 18 — Logical Replication Security
• PostgreSQL 18 — Subscription