Postgres to BigQuery Replication: CDC vs Batch Guide 2026

Postgres to BigQuery replication should start as scheduled incremental batch loads. Move to change data capture (CDC) only when you need deletes, intermediate row states or sub-hour freshness. Most pre-seed to Series A companies need batch for their first year. CDC is the right upgrade once a table's history, or its hard deletes, starts to affect money or compliance.

The mistake we see most often is not choosing the wrong tool. It is choosing CDC too early, or staying on batch too long. A CDC stream nobody understands quietly inflates your BigQuery bill. A nightly batch job that never sees a deleted row quietly corrupts your finance numbers. Both are avoidable, and the decision is simpler than vendor marketing makes it look.

This guide is for founders, CTOs and heads of data who have a production PostgreSQL database holding orders, ledgers, customers or transactions, and need it in BigQuery for reporting. It covers what each pattern costs, what breaks in production, and how to decide at each growth stage.

When Is Batch Postgres to BigQuery Replication Enough?

Batch is enough when your reporting questions are about the current state of the business, once or a few times a day. That covers most early-stage analytics: daily revenue, signups, active customers, funnel conversion and weekly executive reporting.

Batch means a scheduled job queries Postgres for rows where an updated_at timestamp is newer than the last run, then loads them into BigQuery. Loading itself is cheap. Batch loading into BigQuery goes through a shared slot pool and carries no load charge, so you pay for storage and for whatever tool orchestrates the extract (the Erathos guide to Postgres sync covers the cost split).

Batch has three structural weaknesses. If you cannot live with them, you have outgrown it.

1. It cannot see hard deletes. A row deleted in Postgres simply stops appearing in the extract. BigQuery keeps the stale copy forever. The BigQuery Data Transfer Service, for example, does not capture deletes at all, according to the same Erathos comparison.

2. It trusts updated_at. Any code path that updates a row without touching that column, such as a manual SQL fix or a bulk migration, becomes invisible to your warehouse.

3. It collapses history. If an order moves from pending to authorised to settled to refunded between two runs, BigQuery sees only the final state.

A pattern we see repeatedly in early-stage fintech and e-commerce companies is that nobody notices weakness 1 until finance asks why the warehouse total is higher than the ledger. In one reconciliation engagement, the root cause of a large unexplained gap was exactly this kind of divergence between source and reporting copy. The same engagement uncovered a $25M discrepancy that had gone undetected, and at market borrowing rates that gap was costing over $6,000 per day. Replication gaps are not a cosmetic issue once money is involved.

---

📺 Watch: Data Analytics Deep Dives - Datastream - Postgres to BigQuery

---

What Does CDC Give You That Batch Doesn't?

CDC reads the Postgres write-ahead log through logical decoding instead of querying tables. Every committed insert, update and delete becomes an event. For Postgres, Google's Datastream uses the standard pgoutput plugin, available from PostgreSQL 10 onward (see the Datastream PostgreSQL documentation).

In practice, CDC buys you four things:

• Deletes are first-class. A deleted customer or voided transaction is removed or flagged in the warehouse.
• No dependence on updated_at. The log records what happened, regardless of application code.
• Low latency. Minutes rather than hours, which matters for operational dashboards and alerting.
• Optional full history. If you land change events in an append-only table, you can reconstruct how any row evolved.

BigQuery also has native CDC ingestion. You stream UPSERT and DELETE operations through the Storage Write API, and BigQuery applies them to the destination table. According to Google's BigQuery documentation, this requires declared primary keys (up to 16 columns in a composite key) and the default stream with protobuf rows.

That last requirement matters more than it looks. BigQuery does not enforce primary key uniqueness, so correctness depends entirely on the upstream data being clean. If your Postgres table has no real primary key, or a legacy table has duplicates, CDC will not tell you. It will just give you a warehouse table that disagrees with production.

CDC is not the same as real-time analytics. If your only consumer is a daily dashboard, CDC adds cost and operational burden for freshness nobody looks at.

How Much Does Postgres to BigQuery Replication Cost?

Batch is cheap and predictable. CDC has three separate cost layers, and the third is the one that surprises people.

Layer 1: the replication service. Google's Datastream pricing is based on gibibytes processed. Google notes that the processed byte is an internal representation that, for most use cases, can be 2 to 5 times larger than the source data. The CDC rate is tiered and starts at roughly $2 per GiB for the first tier, falling as volume grows. Backfill (the initial snapshot) is free for the first 500 GiB per month per account, then $0.40 per GiB.

As a rough illustration, a database generating 20 GB of changes a month could be billed for 40 to 100 GiB processed. That is about $80 to $200 a month at the first-tier rate. That is fine for most startups, but only if your write volume is what you think it is. A single noisy table, like an events log or a session table, can multiply it.

Layer 2: managed ELT pricing. If you use a managed tool instead, check the billing model carefully. Fivetran bills on monthly active rows, and since March 2025 that is calculated per connection rather than across your account. Reporting from 2026 indicates that deletes now count toward active rows and that each connection carries a $5 monthly minimum. Many teams with several connectors saw bills rise. If you replicate five or six Postgres databases or schemas as separate connections, model the bill before you commit.

Layer 3: BigQuery merge compute. This is the hidden one. With BigQuery CDC, background apply jobs fold changes into the baseline table. Google's documentation says that if you set max_staleness, BigQuery applies changes at least once within that interval. Applying changes at query time instead increases query latency and cost. One independent analysis of Datastream to BigQuery pipelines notes that these background jobs read the entire baseline table and are billed under on-demand pricing by default, at $6.25 per TiB processed.

Here is worst-case arithmetic, treated as an upper bound and not a prediction. Take a 500 GB table where every apply job scans the full baseline:

Max staleness 15 minutes: 96 runs a day x 30 days x 0.5 TiB x $6.25 = roughly $9,000 a month Max staleness 6 hours: 4 runs a day x 30 days x 0.5 TiB x $6.25 = roughly $375 a month

The real figure depends on how often changes arrive and how BigQuery schedules jobs, so measure it from your own job history in INFORMATION_SCHEMA. But the shape of the curve is the lesson. Freshness is not free, and it is priced per table. A 15-minute staleness setting on a large, rarely queried table is one of the most common avoidable costs we find when auditing startup BigQuery projects.

If you're looking to implement this in your organisation, explore how Fintel Analytics approaches this. We work with growth-stage businesses globally to design and deliver replication layers that stay both accurate and affordable.

What Breaks in Production Postgres to BigQuery Pipelines?

The tutorials end when the first row lands. These are the failures that show up in months two to twelve.

Replica identity and the 16-column wall

Datastream uses the logged columns as the logical key for BigQuery MERGE operations. According to Google's documentation, if a table is set to REPLICA IDENTITY FULL and has more than 16 columns, it exceeds BigQuery's 16-column limit for primary keys in MERGE operations and breaks the stream. Teams often set REPLICA IDENTITY FULL as a quick fix for tables with no primary key. Fix the table instead and give it a real key.

Replication slots that fill the production disk

Logical replication relies on a slot in Postgres that retains WAL until the consumer confirms it. If the stream is paused, misconfigured or abandoned, WAL accumulates on your production database and can fill the disk. This is not a warehouse problem. It is an outage. Alert on replication slot lag from day one, and never leave a test stream pointed at production.

Schema changes nobody told the data team about

A developer renames a column or changes a type, and either the stream stalls or the downstream model silently returns NULLs. Neither is acceptable for finance reporting. This is a governance problem more than a tooling one, and a lightweight contract between application and data engineers (a changelog, a required review, or automated schema tests) solves most of it.

Timestamp semantics

Postgres has both timestamp and timestamptz, and replication tools map them in ways that are easy to misread. A day boundary calculated in the wrong zone shifts revenue between days. We cover that failure mode in detail in timezone errors in financial reporting.

Duplicates in the baseline

Because BigQuery does not enforce primary key uniqueness, a bad backfill or a retried batch can create duplicate keys that corrupt CDC merges. Run a uniqueness test on every replicated table, every day, and fail loudly.

Which Replication Pattern Fits Your Growth Stage?

This is the framework we use when a founder or CTO asks what to build now.

Pre-seed to seed (under ~20 people, one core database). Use incremental batch, hourly or daily. Keep a weekly full-table comparison of row counts and key totals between Postgres and BigQuery to catch deleted-row drift. Spend your effort on modelling and metric definitions, not infrastructure.

Seed to Series A (20–60 people, money or orders in the database). Keep batch for most tables, and add CDC only for the tables where deletes, status transitions or history matter: ledger entries, payments, orders, subscriptions. A hybrid is almost always cheaper and easier to reason about than CDC everywhere.

Series A to Series B (60–150 people, multiple operational consumers). CDC across core transactional tables becomes justified, because reconciliation, treasury, fraud and customer operations all need fresher and more complete data. Set max_staleness per table based on the actual consumer, not a global default. Add replication lag, row-count parity and uniqueness checks to your monitoring.

Three questions settle most debates:

1. Does anyone delete rows in this table, and does it matter? If yes, you need CDC or a soft-delete convention.
2. Does the business care about intermediate states? Authorisation to settlement timing, fulfilment stages and subscription changes all need history.
3. What is the cost of data being 24 hours old? If the honest answer is nothing, stay on batch.

On a payments reconciliation project, a process that took 30 to 50 minutes to run was rebuilt as an automated SQL pipeline that now completes in under 3 seconds. That was only possible because the underlying replication gave the models trustworthy, complete source rows. Fast models on incomplete data just produce wrong answers faster.

How Should You Model Replicated Postgres Data in dbt?

Whichever pattern you choose, never point dashboards at raw replicated tables. Use a layered approach:

• Raw layer: exactly what the replication tool lands, untouched. For CDC, this may include change type and sequence columns.
• Staging layer: one model per source table, doing renaming, type casting, timezone normalisation and de-duplication. Build in the soft-delete or deleted-flag logic here, once, so every downstream model inherits it.
• Mart layer: business-defined entities and metrics in a governed semantic layer.

Be deliberate about materialisation. Staging models built as plain views on top of CDC tables can re-trigger expensive work each time they are queried, a trap we describe in dbt models reading BigQuery views. For large tables, an incremental model keyed on the primary key and the change sequence is usually the better choice.

Finally, test the replication, not just the models. Four tests catch most problems: row-count parity with source, uniqueness of primary keys, freshness on each source, and not-null on the keys. In one engagement, migrating calculations out of spreadsheets and into tested dbt models eliminated a recurring class of manual errors and cut weekly maintenance by 30 minutes. The same discipline applied at the replication boundary stops bad data entering the warehouse in the first place.

Frequently Asked Questions

Q: What is the best way to replicate Postgres to BigQuery?

A: For most early-stage companies, scheduled incremental batch loads are the best starting point because they are simple and cheap. Add CDC, through Datastream or a managed ELT tool, for the specific tables where deletes, status history or low latency matter.

Q: Does Datastream capture deletes from PostgreSQL?

A: Yes. Datastream reads changes through PostgreSQL logical decoding, so inserts, updates and deletes are all captured. For merge-based BigQuery destinations, tables need a usable primary key, and replica identity must be configured correctly or the stream can fail.

Q: How much does Postgres to BigQuery CDC cost?

A: It has three parts: the replication service (Datastream bills per GiB processed, and Google says processed bytes can be 2 to 5 times the source size), any managed ELT fees, and BigQuery compute for the background merge jobs. The merge compute is often the least visible, and it scales with table size and how fresh you require the data to be.

Q: What is max_staleness in BigQuery CDC?

A: It is a table option that sets how stale query results are allowed to be. BigQuery applies streamed changes at least once within that interval, trading some freshness for lower query cost and latency. A tighter setting means fresher data but potentially much higher merge cost.

Q: Do I need CDC if I only want dashboards in BigQuery?

A: Usually not at first. If your dashboards show current-state metrics refreshed daily or hourly and nobody deletes the underlying rows, incremental batch is enough. You need CDC once deleted or changing records start to produce numbers that disagree with the source system.

Conclusion

The challenge most growth-stage teams face is not moving rows from Postgres to BigQuery. It is moving them in a way that stays accurate, affordable and trusted as the company scales from one database to a dozen operational consumers. At Fintel Analytics, we have helped fintech, payments and e-commerce teams design Postgres to BigQuery replication layers that pair the right pattern per table with dbt staging models, automated parity checks and cost governance, from initial audit through to production. If your warehouse totals have ever disagreed with your production database, or your BigQuery bill is climbing for reasons nobody can explain, that is a fixable problem, and fixing it early costs far less than explaining it to an investor or auditor later.