Row Filters, Column Lists, Selective Replication, and Multi-Database Integration Patterns

Learning outcomes

ServiceHub does not always need every row and every column downstream. An analytics database might need only northern-region work orders and operational fields, while a migration target might need all rows but omit a source-only column. PostgreSQL publications can express those choices, but they are selection mechanisms, not a transformation engine.

1. Row filters select source rows

A row filter is a WHERE expression attached to a published table. If it evaluates false or null, the change is not published. Filters are evaluated on the publisher before the change is sent. They use a deliberately restricted expression language: simple immutable expressions, no user-defined functions/operators/types, no system columns, and no non-immutable built-ins.

CREATE PUBLICATION ch15_north_pubFOR TABLE app.ch15_work_ordersWHERE (region = 'north');

For publications that include UPDATE or DELETE, every column used by the row filter must be covered by replica identity. The current ch15_work_orders primary key contains only work_order_id, so a filter on region would be rejected for update/delete publication semantics. A design option is a suitable replica-identity unique index that includes the required identity columns, or a publication that only publishes INSERT if that truly matches the use case.

CREATE UNIQUE INDEX ch15_work_orders_repl_identityON app.ch15_work_orders (work_order_id, region);ALTER TABLE app.ch15_work_ordersREPLICA IDENTITY USING INDEX ch15_work_orders_repl_identity;DROP PUBLICATION IF EXISTS ch15_north_pub;CREATE PUBLICATION ch15_north_pubFOR TABLE app.ch15_work_ordersWHERE (region = 'north');

2. UPDATE across a filter boundary can change operation shape

Suppose a row currently matches region='north' and an update moves it to south. From the subscriber's selective viewpoint the row has left the published set, so PostgreSQL can logically produce a delete-like effect. The reverse transition can appear insert-like. This is why row-filtered consumers must reason about set membership, not just the SQL verb originally issued by the publisher application.

UPDATE app.ch15_work_ordersSET region = 'south', changed_at = clock_timestamp()WHERE work_order_id = 15001;UPDATE app.ch15_work_ordersSET region = 'north', changed_at = clock_timestamp()WHERE work_order_id = 15001;

Validate the subscriber's resulting set, not merely whether the source UPDATE committed. A row filter has no effect on TRUNCATE, which is another reason to treat publication operation choices deliberately.

3. Column lists select attributes—not schemas

A publication can publish only selected columns. The subscriber table must contain at least those columns, matched by name. Column order need not match. For UPDATE/DELETE publications, the column list must include replica-identity columns.

CREATE PUBLICATION ch15_ops_pubFOR TABLE app.ch15_work_orders  (work_order_id, region, status, changed_at)WHERE (region = 'north');

A column list is not a durable security boundary. PostgreSQL has no publication privilege that prevents a sufficiently capable subscriber from using another accessible publication to request more data. Protect sensitive source data with publisher-side roles, network policy, database design, and controlled publication ownership—not merely a narrow column list.

4. PostgreSQL 18 generated-column behavior is version-sensitive

PostgreSQL 18 added the ability to publish stored generated-column values. By default generated columns are not published; if both publisher and subscriber define the column as generated, the subscriber computes its own generated value. PostgreSQL 18 can instead publish a stored generated column through a column list or publish_generated_columns='stored'.

ALTER TABLE app.ch15_work_ordersADD COLUMN labor_hours numericGENERATED ALWAYS AS (labor_minutes / 60.0) STORED;-- Publish the generated value explicitly as part of a column contract:CREATE PUBLICATION ch15_generated_pubFOR TABLE app.ch15_work_orders  (work_order_id, labor_minutes, labor_hours);

If the generated value is actually published, the subscriber target for that published value must be a regular column; publishing a generated value into another generated column is not supported. Virtual generated columns are not publishable in this feature. A subscriber older than PostgreSQL 18 also has initial-copy limitations for generated columns.

5. Partitioned tables: leaf identity or root identity?

By default, changes originate using each publisher leaf partition's identity and schema. publish_via_partition_root=true changes that contract so the partitioned root's identity and schema are published. This can be useful when the subscriber has a different partition layout—or even a non-partitioned target table—but it also changes which row filter and column list apply.

CREATE PUBLICATION ch15_partition_root_pubWITH (publish_via_partition_root = true);-- Add a partitioned table only after its target contract is designed:-- ALTER PUBLICATION ch15_partition_root_pub ADD TABLE app.some_partitioned_table;

Do not assume that logical replication automatically creates, attaches, or routes subscriber partitions. The subscriber must provide a compatible target schema.

6. Fan-out and multi-database integration patterns

One publication can have multiple subscribers. A subscriber can also publish onward, creating logical chains, but that does not automatically prevent loops or guarantee a coherent multi-master conflict policy. Replication origins help track where changes came from, while application/topology design must decide ownership of each write domain.

A safe ServiceHub fan-out design might publish an operational subset to an analytics database and a complete dataset to a migration target. Keep those contracts as separate publications so row/column scope, retention, and cutover ownership can be reasoned about independently.

SELECT pubname, schemaname, tablename,       attnames, rowfilterFROM pg_publication_tablesWHERE pubname IN ('ch15_ops_pub','ch15_generated_pub','ch15_servicehub_pub')ORDER BY pubname, schemaname, tablename;

The exact catalog rendering of column names and row filters is useful evidence during change review. Treat publication definitions as production configuration that should be version-controlled and peer-reviewed.

7. Wrong assumption: logical replication is schema mapping

Even when source and target table names match, text-mode replication only requires convertible target types, while binary mode has stricter compatibility requirements. Cross-version migration therefore deserves explicit type and extension compatibility testing.

8. Lab verification

SELECT work_order_id, region, status, changed_atFROM app.ch15_work_ordersORDER BY work_order_id;-- Compare with publisher contract:-- north rows should be present for ch15_ops_pub;-- unpublished attributes should not be assumed synchronized.

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 — Row Filters
• PostgreSQL 18 — Column Lists
• PostgreSQL 18 — Generated Column Replication
• PostgreSQL 18 — CREATE PUBLICATION
• PostgreSQL 18 — Logical Replication Restrictions