skip to content

In a PostgreSQL publication, how do a row filter and a column list limit what reaches the subscriber, and what rules constrain each?

level: middleimportance: should knowfreq 36%

answer

  1. WHERE per table, columns per table
  2. replica identity must cover them
  3. UPDATE can become INSERT or DELETE
  4. not a security boundary

basics

~20 s

A row filter (WHERE after the table name) sends only matching rows; a column list sends only named columns. If updates or deletes are published, both must stay within the replica identity, or the publisher's next UPDATE or DELETE fails.

solid answer

~40 s

In `CREATE PUBLICATION ... FOR TABLE sales.orders WHERE (region = 'EU'), sales.customers (customer_id, country, created_at)`, the filter drops rows where it is false or NULL and the list drops unnamed columns; both shape the initial copy as well as the stream, and neither affects `TRUNCATE`. When the publication publishes updates or deletes, the filter may reference only replica-identity columns and the list must include them. Break that and PostgreSQL accepts the publication but fails the next `UPDATE` or `DELETE` on the publisher with SQLSTATE `42P10`. For an `UPDATE`, the filter is checked on the old and new row, so a row leaving the filter arrives as a `DELETE` and one entering it as an `INSERT`. Filters allow only simple immutable expressions, and column lists are not a security boundary.

code

sql · 10 lines
sql
CREATE PUBLICATION orders_to_analytics
  FOR TABLE sales.orders WHERE (region = 'EU'),
            sales.customers (customer_id, country, created_at);

-- region is not in sales.orders' replica identity (order_id),
-- so this now fails on the publisher with SQLSTATE 42P10:
UPDATE sales.orders SET status = 'shipped' WHERE order_id = 1001;

SELECT pubname, tablename, attnames, rowfilter
FROM pg_publication_tables;

go deeper

for a junior

Recall that a publication can send a subset of rows with a WHERE clause and a subset of columns with a column list, per table.

for a middle

Explain the UPDATE transformation table and why filters and lists must stay within the replica identity when updates or deletes are published.

for a senior

Anticipate the production trap: the publication is accepted and the next UPDATE fails on the source with 42P10. Choose between filtering on identity columns, insert-only publishing and a wider identity.

for a principal

Decide where data minimisation belongs: column lists trim volume, but PII protection has to live on the publisher because publications carry no privileges.

## Two ways to publish less A publication can narrow each table it lists in two directions: - a **row filter**, a parenthesized `WHERE` after the table name: only rows for which the expression is true are sent; `false` and `NULL` both drop the row; - a **column list** after the table name: only those columns are sent, and the subscriber's table must have at least them. Both are per table and applied on the publisher before changes leave it. Both shape the **initial copy** as well as the stream, and neither has any effect on `TRUNCATE`. `pg_publication_tables` shows the result in its `rowfilter` and `attnames` columns. ## Filters and the initial copy - With `copy_data = true`, the table-sync worker copies only the rows that satisfy the filter, and only the listed columns. - The copy ignores the publication's `publish` parameter, so an insert-only publication still copies existing rows that later updates would never touch. - If several publications in one subscription filter the same table differently, a row matching any of them is copied. - Rows already on the subscriber are never re-filtered; tightening a filter later leaves old rows in place. ## The replica-identity rule If the publication publishes `update` or `delete` (the default publishes both), the row filter may reference **only replica-identity columns**, and the column list must **include** them. The reason is mechanical: for an `UPDATE` or `DELETE` the WAL holds only the identity columns of the old row, so a filter on any other column could not be evaluated against it. PostgreSQL does not reject the publication for breaking this. It rejects the **next `UPDATE` or `DELETE` on the publisher**: - `ERROR: cannot update table "orders"` with `DETAIL: Column used in the publication WHERE expression is not part of the replica identity.` - or `DETAIL: Column list used by the publication does not cover the replica identity.` - both with SQLSTATE `42P10` (`invalid_column_reference`). So a filter `WHERE (region = 'EU')` on `sales.orders`, whose identity is the primary key `order_id`, stops order updates on the orders database. The ways out: 1. Filter only on identity columns. 2. Publish only inserts (`WITH (publish = 'insert')`): any column may then be filtered, but updates and deletes no longer flow. 3. Widen the identity: `REPLICA IDENTITY FULL` makes every column part of it, at the WAL and lookup cost that implies, or `USING INDEX` on a unique index that includes the filter column, such as one on `(order_id, region)` with both `NOT NULL`. ## How UPDATEs are transformed The filter is evaluated against both the old and the new row of each `UPDATE`, so the subscriber holds exactly the rows that match: | Old row matches | New row matches | Sent as | |---|---|---| | no | no | nothing | | no | yes | `INSERT` | | yes | no | `DELETE` | | yes | yes | `UPDATE` | An order moved from `'EU'` to `'US'` therefore disappears from the analytics copy, and one moved the other way appears. ## Expression restrictions - Only simple expressions: no user-defined functions, operators, types or collations, no system columns, and no non-immutable built-in functions. A filter such as `created_at > now() - interval '90 days'` is rejected when the publication is created, with `invalid publication WHERE expression`. - The filter is evaluated with the role of the replication connection. - When a subscription takes the same table from several publications, their filters for the same operation are **ORed**; a publication with no filter, or a `FOR ALL TABLES` one, makes the others irrelevant. - For partitioned tables, `publish_via_partition_root` decides whether the root's or each partition's filter and column list apply; the default `false` uses each partition's. ## Column-list rules - **No list is not the same as a list of every column.** Without a list, columns added later are replicated automatically; with a list, a new column stays unpublished until the list is changed. - A column list cannot be combined with `FOR TABLES IN SCHEMA` in the same publication. - A subscription cannot take one table from several publications with **different** column lists; `CREATE SUBSCRIPTION` refuses, and changing a list later makes the subscriber error. - **It is not a security control.** There are no privileges on publications, any subscription that can connect may use any publication in the database, and the documentation warns that a malicious subscriber can obtain unpublished columns. Keep customer emails off the analytics server by protecting them on the publisher, for example in a table that is never published.

  • You change a table's row filter with ALTER PUBLICATION; do already-copied rows on the subscriber change?
    No. The new filter applies to changes decoded after the change commits; rows already on the subscriber stay as they are. `ALTER SUBSCRIPTION ... REFRESH PUBLICATION` does not re-copy tables already subscribed, even when their filter changed, so you clean up rows that no longer match yourself, or re-sync the table.
  • Why does a publication with publish = 'insert' allow a filter on any column?
    The identity rule exists because UPDATE and DELETE carry only the identity columns of the old row. An insert carries the full new row, so any column can be evaluated. The price is that updates and deletes are not replicated at all, so the subscriber's copy drifts from the publisher's.

  • CREATE PUBLICATION rejects a row filter on a non-identity column immediately.
  • An UPDATE that moves a row out of the filter is simply not replicated.
  • Column lists are a safe way to keep PII off a subscriber.
  • A column list naming all current columns is the same as no column list.
  • Row filters may call any function, including now() or your own SQL functions.