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 here. 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

• A table needs a primary key, and the publication has to include the primary-key columns. No primary key, no replication.
• Replica identity decides whether your updates survive. DEFAULT covers most tables; large text, jsonb and bytea columns need FULL, and FULL costs write-ahead log volume.
• 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 with a primary key covers most tables. FULL is what you want for tables carrying large text, jsonb or bytea values.

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, n is nothing. Only the first two work here.

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:

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

What we leave out on purpose

Our publication does not include the auth schema, anything holding a token or a key, or the columns carrying raw diff payloads. Three reasons, in order of what they would cost us.

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 the gap Skene works on: it reviews the pull request before the schema change lands and comments on the line that breaks what your tracking records. 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, where a replicated table turns out not to be a table at all, and pointing a dashboard at the wrong object works right up until someone truncates the source. The full run is at From Supabase Pipelines to GTM.