Episode 3: What to publish

A publication is an interface

The temptation 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.

Four things decide whether a table earns a place in it.

It needs a primary key

The publication has to include the primary-key columns. Pipelines declares those columns as the BigQuery primary key so BigQuery’s change data capture can apply upserts and deletes. No primary key, no replication.

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, because Postgres stores those out of line and 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;

What full costs. 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.

Column subsets and row filters

This is the pair that makes selective publishing pleasant rather than a chore. 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. A predicate can beat a 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.