Prompt and context
A PostgreSQL 18 publisher must replicate selected tenants and columns to a subscriber used for reporting and regional services. Explain row-filter and column-list semantics, what happens when an UPDATE crosses the filter boundary, initial synchronization, partition behavior, and a safe operations plan.
PostgreSQL documents that a row filter decides before publication whether a row satisfies an expression, while a column list reduces transported columns but is not a security boundary. The interview tests replication semantics, consistency, and change governance rather than adding a WHERE clause to a publication.
What the interviewer is testing
You should explain that the replication-connection role evaluates the filter, false or NULL suppresses a row, and TRUNCATE is unaffected. You should derive the old/new UPDATE transformations, handle replica identity, initial sync, OR-combined filters across publications, partition roots, and column-list evolution, and recognize that column lists do not provide confidentiality.
Questions to clarify first
Replication target
Confirm the tenants, operations, and columns needed by the subscriber, whether an initial snapshot is allowed, whether writes are bidirectional, and whether the subscriber is older than version 15 or 18.
Identity and filtering
Confirm the table's replica identity, whether filter columns can change, whether partition-root publication is used, and whether multiple publications cover the same table.
Security and recovery
Confirm whether sensitive columns must be completely invisible, the replication role's permissions, network isolation, subscription rebuild procedures, and the rollback window for a bad filter.
A 30-second answer
“A row filter defines which row changes enter a publication; false or NULL drops the change, while a column list only reduces transported columns and is not a security boundary. An UPDATE evaluates both old and new rows: no-match to match becomes INSERT, match to no-match becomes DELETE, and only match to match remains UPDATE. For UPDATE or DELETE, filter and published columns must cover replica identity. Before rollout I would freeze publication changes, test initial sync, partitions, OR-combined filters, and old-version behavior, while protecting sensitive data with publisher-side privileges and views.”
Step-by-step deep answer
Step 1: Define publication invariants
Record the allowed tenant set, operations, column set, filter expression, and subscriber version for each table. Decide whether the goal is performance trimming, behavioral isolation, or security; a security goal cannot rely on a column list alone.
Step 2: Design the row filter
The filter runs before publication using the role of the replication connection and only the documented simple expressions. A false or NULL result suppresses the row, and TRUNCATE is unaffected. For publications containing UPDATE or DELETE, filter columns must be covered by replica identity; INSERT-only publications may use other columns.
Step 3: Derive UPDATE transformations
Evaluate the filter before and after the update. Match-to-match sends UPDATE; no-match-to-no-match sends nothing; no-match-to-match sends INSERT; match-to-no-match sends DELETE. The subscriber therefore represents the current set of rows satisfying the filter instead of blindly replaying every UPDATE.
Step 4: Constrain the column list
The column list must include replica-identity columns, and the subscriber table must contain at least the published columns. List order does not affect replication. Omitting a list automatically includes columns added later; explicitly naming all current columns does not include future columns. Different lists for the same table across publications can prevent a subscription from resuming, so review them before changing.
CREATE PUBLICATION tenant_eu
FOR TABLE orders (order_id, tenant_id, status, total)
WHERE (tenant_id = 'eu');Step 5: Handle initial synchronization
Initial synchronization copies existing rows that satisfy row filters and only the columns in the list. A subscriber older than 15 may copy the whole table, and versions older than 18 have additional generated-column limitations. Check the version matrix and write backlog during the snapshot before switching traffic.
Step 6: Handle partitions and multiple publications
With publishviapartition_root=true, the root partitioned table's filter or column list is used; with the default false, each partition's definition is used. Row filters for the same table and publish operation across publications are ORed. An unfiltered publication can therefore defeat the pruning expected from other filters.
Step 7: Add operational guardrails
Review publication definitions as migrations, and record filter hit rate, replication lag, conflicts, initial-sync row counts, and rule versions. Test old/new boundary cases on a shadow subscription. If rows leak or disappear, pause the subscription, correct the publication, and rebuild after consistency checks.
Model answer
I would treat the row filter as the published-set definition and the column list as transport pruning. The replication role evaluates the filter, so false/NULL and TRUNCATE need explicit tests. UPDATE compares old and new rows and becomes INSERT or DELETE when crossing the boundary; for UPDATE/DELETE, filter and published columns must cover replica identity. Initial sync, partition roots, and OR-combined publications belong in the test matrix. Protect sensitive data with publisher privileges, views, or a separate redaction pipeline; use column lists for performance and shape control.
Common mistakes
- Mistake: Assuming a column list stops a malicious subscriber from obtaining unpublished columns. → Why it fails: The official documentation says it is not a security mechanism. → Fix: Enforce confidentiality with publisher privileges, views, or redaction.
- Mistake: Evaluating an UPDATE only on the new row. → Why it fails: A row can leave or enter the filtered set. → Fix: Evaluate old and new rows and apply all four outcomes.
- Mistake: Ignoring replica identity. → Why it fails: UPDATE/DELETE cannot safely locate the old row. → Fix: Check identity coverage in the filter and column list.
- Mistake: Assuming several narrow publications imply a narrow total stream. → Why it fails: Filters for the same publish operation are ORed. → Fix: Compute the union and reject unfiltered coverage.
Follow-up questions and responses
When should you use a row filter versus separate publications?
Use a row filter when tenant or region boundaries are stable and expressions are simple. Separate publications and subscriptions are easier to audit when permissions, lifecycle, and operating ownership differ. In both cases, evaluate initial-sync and change costs.
How do you prevent accidental replication of a new column?
Use an explicit column list and update it through schema review. Do not confuse explicitly naming every current column with omitting the list; omission automatically includes future columns.
How do you test an UPDATE crossing the boundary?
Create four cases: old false/new true, old true/new false, both true, and both false. Verify INSERT, DELETE, UPDATE, and no event on the subscriber respectively.
Can filtering replace multi-tenant security isolation?
No. It selects logical-replication data; isolation still needs least-privilege publisher roles, separate credentials, network controls, and redaction where required.