What to publish: a Postgres publication is an interface, not a backup

This is the written version of episode 3 of our build series. Watch it on the episode page. Episode 2 got a table to Live; this one is about which tables should have been in the publication in the first place.

The temptation, once the pipe works, is for all tables. Resist it. A publication is not a backup. It is an interface, and every table in it is a promise you now have to keep.

Key takeaways

• For update and delete replication, identity columns must be available. DEFAULT uses a primary key; USING INDEX requires an index whose columns exactly match the source primary key for Supabase BigQuery Pipelines. NOTHING is insert-only.
• FULL is recommended when updates must carry unchanged toasted values, and it increases write-ahead log volume. Choose identity from the operations and columns you publish.
• You can publish a subset of columns and a filtered subset of rows. Both are one line of SQL, and they are what make selective publishing pleasant rather than a chore.
• Three things stay in Postgres on purpose: the auth schema, anything holding a token or a key, and the columns carrying raw payload blobs.

It needs a primary key

Supabase Pipelines declares the primary-key columns as the BigQuery primary key, so BigQuery's change data capture can apply upserts and deletes against the right row. A table without one cannot be replicated, and a publication that omits the key columns of a table that has one is the same problem wearing a different hat.

This is the requirement most likely to bite a table that has been in production for a year. Retrofitting a primary key onto one is a worse afternoon than getting it right on the way in.

Its replica identity has to be compatible

DEFAULT requires a primary key. USING INDEX is supported only when the selected index columns exactly match the source primary-key columns. NOTHING supports inserts only. Supabase recommends FULL for updates that must preserve unchanged toasted values. See the BigQuery Pipelines guide.

The reason is worth knowing rather than memorising. Postgres stores large values out of line, and it can send an update with the unchanged value marked as toasted rather than resending it. A BigQuery upsert needs a complete row, so those updates can fail. Check before you find out the hard way:

select
  n.nspname as schema_name,
  c.relname as table_name,
  c.relreplident as replica_identity
from pg_class as c
  join pg_namespace as n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relname = 'checks';

d is default, f is full, i is index, and n is nothing. Their suitability depends on whether you publish inserts only or also updates and deletes, and on the identity columns available.

alter table public.checks replica identity full;

FULL is not free. Postgres then logs the whole old row on every update and delete, so it costs write-ahead log volume. Spend it where update correctness actually matters, which is exactly the tables with the big columns.

Publish fewer columns, and fewer rows

This is the pair that turns selective publishing from a chore into a one-line decision. Replicate the four columns the warehouse needs and leave the other twenty in Postgres:

create publication skene_workspaces
for table workspaces (id, plan, created_at, region);

Same idea, one axis over. Where a predicate covers what analysis needs, it beats the whole table. For UPDATE and DELETE, PostgreSQL requires row-filter columns to be covered by replica identity. Row-filter rules and column-list rules both require published identity columns. With REPLICA IDENTITY FULL, that means every column must be included for updates and deletes. The earlier checks/FULL example and the workspaces column-list example are different tables; do not combine their settings by implication. This filtered checks example explicitly publishes inserts only:

create publication skene_recent_checks
for table checks where (created_at > '2026-01-01')
with (publish = 'insert');

What we leave out on purpose

Our publication does not include the auth schema, anything holding a token or a key, or columns carrying raw diff payloads. Column lists minimise replicated data, but PostgreSQL explicitly warns that they are not a security boundary against a malicious subscriber. Access control still has to protect the publisher and subscriber.

Personal data replicated to a second continent is a second place to answer for it. Secrets replicated anywhere are secrets in one more system. And large payload blobs are the fastest way to turn a cheap warehouse into an expensive one, because BigQuery charges for bytes scanned and those columns are almost never the ones you are querying.

None of this is exotic. It is the difference between publishing what analysis needs and publishing what happens to be in the database.

The part a publication cannot decide

Every rule above is about which rows leave Postgres. None of them has an opinion about whether those rows are right.

A publication scoped perfectly to what analysis needs will still carry a renamed event, a dropped write, or a column that stopped being filled, straight to the warehouse. That is one gap a configured Skene pull request review may flag from supported changed code and policy. It is not proof of complete capture or every downstream dependency. Episode 8 takes four ordinary migrations through this same chain and shows where each one comes apart.

Watch the series

Episode 3 is the video version of this post, with the publication being edited on screen. Before it: episode 1, the four layers and episode 2, the setup end to end.

Next is modeling in BigQuery. Current Supabase BigQuery Pipelines documentation describes a current-state destination table; it does not promise a complete version-history table. The full run is at From Supabase Pipelines to GTM.