How to Stream Data from Postgres to BigQuery in Real Time

A food delivery app runs on Postgres in Cloud SQL. Its ops team watches a BigQuery dashboard of late orders by city and uses it to send couriers to neighborhoods that fall behind during the dinner rush.

The dashboard is fed by an hourly batch job. So when Brooklyn deliveries start running 20 minutes late at 6:05 p.m., the team finds out at 7:00 p.m., after the rush is over.

This guide replaces that job with change data capture (CDC): the Postgres setup, how changes merge into BigQuery, what breaks, and how to check the data. It assumes you know SQL and have admin access to Postgres.

Artie is a fully managed CDC platform that captures row-level database changes and maintains destination tables in Snowflake, BigQuery, Redshift, Databricks, and other supported destinations. Artie delivers sub-minute latency for supported managed CDC workloads; observed freshness depends on the source, destination, table mode, and workload.

Key Takeaways

• Batch jobs cap freshness at the schedule interval, and updated_at polling never sees hard deletes.
• Streaming replication reads the Postgres write-ahead log through a replication slot, buffers changes, and merges them into BigQuery in small batches.
• Tables whose updates and deletes get published need a usable replica identity, usually a primary key. Without one, Postgres rejects those updates and deletes on the source.
• Most production incidents trace back to slot growth, schema changes, BigQuery DML limits, unchanged TOAST values, or failovers.

Why Batch Loads from Postgres to BigQuery Are Not Enough

The hourly job probably runs something like this:

SELECT *
FROM orders
WHERE updated_at > :last_run_at;

It breaks in three ways.

Freshness is capped by the schedule. Data can be an hour old. Running every five minutes helps, but each run still queries production.

Deletes disappear. When the app deletes an order after a failed payment, there's no row left for the query to find. The order stays in BigQuery and inflates the open-order count until someone runs a full reload.

updated_at is only as reliable as the code that sets it. A bulk fix from a migration script, or a write path that forgets to bump the timestamp, means those rows never get picked up.

Latency matters when someone acts on the data within the hour, like dispatching couriers or flagging fraud. If the dashboard gets read once each morning, a nightly batch is fine.

How a Real-Time Postgres to BigQuery Pipeline Actually Works

Before Postgres changes any data, it writes the change to the write-ahead log (WAL), an append-only record used for crash recovery. Logical decoding turns WAL records into row-level events: this row was inserted, updated, or deleted, with these values. It underpins Postgres logical replication. Physical streaming replication, the kind behind most read replicas, is a separate mechanism that copies WAL at the storage level.

Here's what happens when a courier picks up order 48213.

1. Postgres writes the change to the WAL. The app runs UPDATE orders SET status = 'picked_up' WHERE id = 48213, and the change gets a log sequence number (LSN), its position in the WAL.

2. A replication slot hands the change to the pipeline. The slot tracks the last LSN the consumer confirmed and stops Postgres from deleting WAL the consumer hasn't read. The pgoutput plugin, built into Postgres 10 and later, decodes the change.

3. The pipeline buffers events outside Postgres. The reader confirms positions back to the slot quickly and stores events in a durable buffer, usually Kafka. If BigQuery is slow, events wait in the buffer instead of piling up as WAL on your database. We explain why we built Artie this way in Not All CDC Pipelines Are Created Equal.

4. Changes land in a staging table, then get merged. The pipeline collects a few seconds of changes, loads them into a staging table, and applies them with one MERGE. Here's a simplified version. commit_lsn, event_index, and op are illustrative metadata columns, not any specific tool's schema:

MERGE `delivery_app.orders` AS target
USING (
  -- Keep only the final event per order. Changes in one transaction can
  -- share a commit LSN, so the event index breaks ties.
  SELECT * EXCEPT (row_num)
  FROM (
    SELECT *,
           ROW_NUMBER() OVER (
             PARTITION BY id
             ORDER BY commit_lsn DESC, event_index DESC
           ) AS row_num
    FROM `delivery_app.orders_staging`
  )
  WHERE row_num = 1
) AS source
ON target.id = source.id
WHEN MATCHED AND source.op = 'd' THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET status = source.status,
             courier_id = source.courier_id,
             updated_at = source.updated_at
WHEN NOT MATCHED AND source.op != 'd' THEN
  INSERT (id, status, courier_id, updated_at)
  VALUES (source.id, source.status, source.courier_id, source.updated_at);

If order 48213 changed three times in the batch, only the final state gets written. And deletes become real deletes.

The Postgres Setup You Need Before You Can Stream to BigQuery

Logical decoding turned on. On Cloud SQL, set the cloudsql.logical_decoding flag to on. On Amazon RDS, set rds.logical_replication to 1. On self-managed Postgres, set wal_level to logical. All three need a restart. Skip it and slot creation fails, which at least is loud.

The instance also needs room for the pipeline's connection and slot, so check max_wal_senders and max_replication_slots. On RDS, enabling rds.logical_replication adjusts related settings for you. On Cloud SQL, the flag turns on Google's managed logical decoding configuration.

A replication role and a publication. A publication defines which tables get streamed.

SHOW wal_level;  -- should return 'logical'

-- Placeholder password: replace before running
CREATE ROLE cdc_reader WITH LOGIN REPLICATION PASSWORD 'change-me';
GRANT SELECT ON orders, order_items TO cdc_reader;

CREATE PUBLICATION cdc_publication FOR TABLE orders, order_items;

Required privileges vary by provider, so check your provider's docs for the exact grants.

A usable replica identity for published updates and deletes. The replica identity tells Postgres which columns identify a row in update and delete events. Inserts don't need one. By default it's the primary key. If a table in a publication that publishes updates and deletes has no usable replica identity, Postgres rejects every UPDATE and DELETE on it. Your app's writes fail, not just the pipeline. Before adding tables to the publication, check the ones you plan to publish:

SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname IN ('orders', 'order_items')
  AND (
    c.relreplident = 'n'
    OR (c.relreplident = 'd' AND NOT EXISTS (
      SELECT 1 FROM pg_index i
      WHERE i.indrelid = c.oid AND i.indisprimary
    ))
  );

Any table it returns needs a primary key or ALTER TABLE ... REPLICA IDENTITY FULL. FULL works but logs every column's old value on each change, so WAL volume goes up.

A cap on how much WAL a slot can hold. On Postgres 13 and later, max_slot_wal_keep_size limits WAL retained per slot. Without it, a stalled pipeline can fill the disk and take the database down. With it, Postgres invalidates the slot past the limit, and you have to recreate replication and usually re-backfill. That's a tradeoff, but a lost slot is recoverable and a full production disk isn't. Our Postgres Replication Slot 101 post covers slot sizing and cleanup in more depth.

The Biggest Failure Modes in Postgres to BigQuery Pipelines

The slot falls behind. If the consumer goes down, WAL piles up behind the slot. The sneakier version: the replicated database gets few writes, but other databases on the same instance are busy. WAL grows while the consumer never gets an event it can confirm. One fix is a heartbeat: a small scheduled write that keeps the slot moving.

Schema changes arrive without warning. ALTER TABLE isn't its own event in the logical replication stream, so the pipeline only learns about a new column when the next row change carries it. Adding a nullable column in BigQuery is easy. Changing a type is harder, because BigQuery allows only a short list of in-place type changes, like INT64 to NUMERIC.

BigQuery's DML limits. BigQuery runs at most two UPDATE, DELETE, or MERGE statements at once per table and queues up to 20 more. Past that, statements fail. A pipeline issuing one MERGE per change hits that wall on the first busy Friday night. Batch your merges, and partition large tables so each merge scans less.

Unchanged TOAST values turn into nulls. TOAST is how Postgres stores large values, like a long delivery_notes JSON blob, outside the main row. With the default replica identity, an update that doesn't touch that column leaves its value out of the event. A naive pipeline writes null over the real value. Nothing errors, and the data is wrong.

Failover drops the slot. Before Postgres 17, logical slots live only on the primary. Promote a replica and the new primary has no slot, so changes made during the switch never arrive and you need a backfill. Postgres 17 added failover-enabled slots that can sync to a standby, which reduces the problem when your setup supports them. You still need a tested reconnect and recovery plan.

We've had to handle every one of these at Artie. In Artie's Kafka-backed setup, Reader consumes Postgres logical decoding changes and publishes them to Kafka. Transfer then writes each flush to a BigQuery staging table and merges it into the target. A failed merge doesn't advance Transfer's position. Because the merge and the position commit are separate steps, a retry can replay a batch that already merged, and the merge is built to tolerate that.

Artie also monitors replication-slot retained WAL and supports heartbeats. It keeps Postgres's unchanged-TOAST placeholder from overwriting a value already in BigQuery, though full reconstruction depends on Postgres supplying the old row or BigQuery already having the value. On Postgres 17 and later, it can create failover-enabled slots when configured (existing slots aren't converted).

It's not magic, though. Artie can add supported new columns to BigQuery, subject to permissions, BigQuery limits, and type compatibility. Since DDL isn't a logical decoding event, a new column shows up when the next row change includes it, so a rarely updated table can trail its own schema. If you'd rather spend your time on the dashboard than the pipeline, start a pipeline or book a demo.

How to Validate That Your Postgres Data Is Arriving Correctly

A real-time data pipeline can report green for weeks while rows go missing. Run these checks after setup and on a schedule.

Compare a closed window. Use yesterday so in-flight changes don't skew results. These queries assume created_at is timestamptz in Postgres and lands as TIMESTAMP in BigQuery (a DATETIME column needs different syntax). Extra rows in BigQuery usually mean missed deletes. Matching counts with different sums mean lost updates.

-- Postgres
SET TIME ZONE 'UTC';
SELECT COUNT(*) AS order_count, SUM(total_cents) AS gross_cents
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '1 day'
  AND created_at < CURRENT_DATE;

-- BigQuery
SELECT COUNT(*) AS order_count, SUM(total_cents) AS gross_cents
FROM `delivery_app.orders`
WHERE created_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND created_at < TIMESTAMP(CURRENT_DATE());

Compare null rates on large columns. Count rows where delivery_notes IS NULL in both systems for the same window. A gap points to the TOAST problem.

Measure freshness against the source. Compare MAX(updated_at) in both systems. Comparing to the clock makes quiet tables look stale. Freshness doesn't prove every change arrived, so keep the closed-window checks too.

Alert on slot lag. wal_status shows lost if the slot was invalidated. Wire this into whatever already pages your team.

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

FAQ

What is the difference between streaming and batch replication?

Batch replication copies data on a schedule, by querying for changed rows or reloading tables, so freshness depends on the interval. Streaming replication reads each committed change from the database log and applies it continuously. Batch is simpler and often cheaper for append-only tables read once a day. Streaming fits when people act on data within minutes, or when deletes need to show up downstream.

Does Postgres need a specific version for CDC to work?

Logical decoding arrived in Postgres 9.4, and the built-in pgoutput plugin in Postgres 10. Versions 9.4 through 12 are past end of life, so plan on 13 or later. Postgres 13 added max_slot_wal_keep_size, 16 allowed logical slots to be created on standbys, and 17 added failover-enabled slots. Provider support and required flags vary between Cloud SQL, RDS, and Aurora.

How does Artie handle deletes from Postgres in BigQuery?

By default, Artie hard-deletes the row in BigQuery during the merge, so the table matches Postgres. You can enable soft deletes instead, which keeps the row and sets __artie_delete to true, useful for auditing cancelled orders. You can also skip deletes, or turn on history mode, which records every insert, update, and delete in a separate history table.

Can you stream Postgres to BigQuery without a managed tool?

Yes. A common open source setup is Debezium reading the replication slot, Kafka as the buffer, and a BigQuery sink connector writing to the warehouse. It works, but you own every failure mode in this post: slot monitoring, heartbeats, merge logic, DML limits, TOAST handling, schema changes, and backfills. The ongoing on-call time is usually what teams underestimate.

What happens to the pipeline if Postgres goes down?

If the slot remains valid, the pipeline reconnects and resumes from its persisted position. Postgres logical slots are crash-safe, but consumers must tolerate replay of recently emitted changes after a crash. Failover is different. Before Postgres 17, logical slots aren't copied to replicas, so promoting one leaves the pipeline without a slot and needing a backfill. Postgres 17 failover-enabled slots reduce that risk when configured, but test your failover runbook end to end.