A PostgreSQL Replica Identity Change Study for Offshore Data Pipeline Work

Key Stats

• 1 version-matched publisher/subscriber pair
• 4 replica identity modes
• 5 update and delete shapes

Key Takeaways

• Judge identity changes through decoded records and subscriber outcomes, not catalog labels alone.
• Separate publisher eligibility, emitted key material, subscriber matching, and operational cost.
• Require data and platform owners to approve production identity and retention decisions.

Decision and bounded scope

The decision is whether one table can support a declared logical replication or change-data-capture consumer after a replica identity change. PostgreSQL uses replica identity to identify rows for update and delete records. The study asks which row-identifying values are emitted for one version-pinned publisher, whether the subscriber can apply them without ambiguity, and what storage or write cost accompanies the selected index. It does not benchmark PostgreSQL generally or promise zero downtime. One table schema, publication, slot, consumer, and synthetic dataset form the unit of analysis.

This is a useful offshore data-pipeline lane because the developer can build a disposable topology, execute a reviewed matrix, and preserve evidence without holding production authority. The data owner defines row identity semantics and acceptable duplicates or misses. The database owner approves indexes, locks, and catalog changes. The platform owner governs slots, WAL retention, monitoring, and recovery. A successful fixture supports a proposal for those owners; it does not itself authorize a live ALTER TABLE or subscriber cutover.

Authoritative facts and hypotheses

PostgreSQL documents DEFAULT, USING INDEX, FULL, and NOTHING replica identity modes. DEFAULT normally relies on the primary key; USING INDEX requires a suitable unique, non-partial, non-deferrable index whose indexed columns are marked not null; FULL records the old values of all columns for update and delete; NOTHING records no old row identity. Logical replication restrictions depend on this identity when published tables replicate updates and deletes. Those are documented mechanics, while actual consumer behavior remains a local observation.

The working hypothesis is that the narrowest stable business key that satisfies PostgreSQL requirements will allow deterministic subscriber matching with less old-row material than FULL. A second hypothesis is that identity suitability is not the same as semantic uniqueness across time: a reused external identifier may be unique at a moment yet wrong for downstream history. A third is that an accepted publisher command does not establish end-to-end correctness. Decode output, apply results, lag, and failure recovery must agree before a recommendation is made.

Disposable topology and data design

Create isolated version-matched publisher and subscriber instances. Declare wal_level, slot, publication, subscription or decoding client, table DDL, index definitions, extension set, and configuration hashes. Seed rows with ordinary values, wide text, nullable candidates, changed key candidates, and a deliberate near-duplicate that remains legal under the selected constraints. Use synthetic identifiers and no copied production data. Record row and index sizes because FULL on a wide table and USING INDEX on a compact key create different evidence surfaces.

The fixture must reset cleanly between modes. For DEFAULT, verify the primary key and then remove or alter it only in a separate scenario. For USING INDEX, test a qualifying index and attempted disqualifying variants. For FULL, include a TOAST-able value and an unchanged wide column. For NOTHING, attempt an update and delete under the declared publication so failure is observed rather than inferred. Keep inserts as a control because their behavior does not establish update or delete identity correctness.

Operation matrix and synchronization

Execute an insert, non-key update, identity-column update where permitted, delete, transaction rollback, and a multi-row transaction. Add concurrent writers that touch different rows and a controlled retry after a subscriber interruption. Capture the exact commit LSN, decoded message or protocol observation, subscriber receipt, apply decision, resulting row, and error. Synchronize clocks but order evidence primarily by transaction and log sequence positions. A timestamp-only narrative can misorder buffered or retried events.

Change one independent condition per comparison. Do not switch identity, schema, publication columns, and consumer mapping in a single run. Pause consumption within bounded storage limits to inspect retained WAL and recovery behavior, then resume and confirm the same transactions are applied according to the consumer contract. Seed one intentionally broken mapping so the checker demonstrates it can detect an incorrect target. The study must retain failed and excluded runs with reasons instead of deleting inconvenient output.

Measurements that support a decision

For each operation, retain catalog identity state, index OID and definition, transaction ID where available, commit position, old-key fields, new tuple fields, serialized bytes, subscriber match count, apply status, lag observation, retry count, and final checksum over ordered synthetic rows. Report write latency and WAL volume only as fixture observations with sample size and window. Do not turn them into production forecasts. Pair summary numbers with case records, especially for the wide row and changed-key case.

A correct result requires exactly the outcome defined by the consumer contract. Zero matched rows, multiple matches, a silent stale row, and a duplicate insert are different failures. A decoded event can be mechanically valid while semantically unusable because the consumer lacks a stable mapping. Conversely, a subscriber error can be an expected safety stop rather than publisher failure. Separate publisher eligibility, message content, transport delivery, subscriber interpretation, target mutation, and owner judgment in the evidence table.

Operational hazards and recovery tests

Replica identity changes can interact with index builds, locks, schema migrations, publication configuration, slot retention, and consumer assumptions. The pilot records lock acquisition and wait events for the exact DDL used, but a quiet synthetic result cannot forecast production blocking. A long transaction and a subscriber pause should be included to show how evidence and retention behave under delay. Set bounded timeouts in advance. Do not respond to a stall by force-dropping a production slot or improvising a larger retention setting.

After interruption, inspect publisher catalog state, slot positions, subscriber state, and target rows before choosing recovery. Test resuming consumption, replaying from a recorded safe position where the selected tool supports it, and rebuilding only the disposable subscriber. The runbook distinguishes a data correction, consumer-code repair, schema rollback, and resynchronization. Each action has a named owner. The developer documents options and evidence; database and data owners decide which recovery is acceptable for a real system.

Handoff and review procedure

The handoff contains DDL, configuration, image or package versions, fixture generator seed, mode-by-mode operation matrix, raw decoded samples with synthetic values, target checksums, timing window, excluded runs, and unresolved questions. The reviewer recreates one qualifying USING INDEX case and one expected failure, then traces a key-changing update from source transaction to target row. This confirms both the mechanism and the measuring method. Screenshots may supplement but cannot replace machine-readable catalog and message evidence.

Access remains limited to disposable infrastructure. No production values, customer identifiers, reusable secrets, or unmanaged export files belong in the fixture. The data owner reviews key meaning and retention, the database owner reviews index and DDL effects, security reviews data fields leaving the publisher, and the platform owner reviews slot monitoring and storage controls. A handoff is incomplete if it says only that replication worked; it must name the tested boundary, failure behavior, recovery path, and next accountable decision.

Limitations and decision rule

Results apply only to the pinned PostgreSQL versions, plugins or built-in logical replication path, schema, consumer, dataset, configuration, and operation matrix. Different connectors may transform records, use snapshots, or manage offsets differently. Synthetic cardinality cannot predict production index cost, WAL growth, cache effects, or apply throughput. A checksum over the fixture cannot prove every business invariant. Failover, partitioned tables, generated columns, row filters, and bidirectional replication require separate protocols if they matter.

Conclude pass only when the selected identity is accepted, emits the required old-key material, supports exactly one subscriber match, survives the tested interruption, and has owner-reviewed cost and recovery boundaries. A conditional pass names excluded operations and monitoring requirements. A fail identifies whether the defect is key semantics, publisher configuration, transport, mapping, or apply logic. The recommendation remains a bounded engineering proposal. It must never be framed as a universal best replica identity or permission for an offshore developer to alter production independently.

Sources checked October 2, 2026

PostgreSQL documentation, ALTER TABLE replica identity: https://www.postgresql.org/docs/current/sql-altertable.html. PostgreSQL documentation, logical replication restrictions: https://www.postgresql.org/docs/current/logical-replication-restrictions.html. PostgreSQL documentation, logical decoding concepts: https://www.postgresql.org/docs/current/logicaldecoding-explanation.html. These primary sources establish supported modes, restrictions, and decoding concepts; local outcomes still require the described fixture.

Documentation cannot determine whether a company identifier is semantically stable or whether a downstream model may accept replays. Those remain owner decisions informed by system-specific evidence. Record the exact documentation version checked and the actual server version because current online documentation may describe behavior newer than the deployed server.

Evidence table

Good distributed work is observable at the handoff: the result, evidence, limitations, and next owner are all explicit.

Frequently asked questions

Related Research

• Data pipeline development
• DevOps release support
• Research library