PostgreSQL to BigQuery: 2 Ways to Sync (and Control the Cost)

If your app runs on PostgreSQL and your analytics live in Google BigQuery, you've hit the same wall everyone does. You need to keep the two in sync without slow nightly exports, scripts that break on a schema change, or a BigQuery bill that creeps up every month.

The cleanest way to sync PostgreSQL to BigQuery is log-based change data capture (CDC). It streams each insert, update, and delete straight from PostgreSQL's write-ahead log into BigQuery as it happens, instead of re-exporting whole tables on a timer. This keeps BigQuery current and barely touches your source database.

But here's the part almost every guide skips: with BigQuery, real-time isn't free. How often data lands in the warehouse drives how much you pay in compute. The tools that just promise "real-time CDC" quietly leave you to discover that at 3 a.m. when the bill spikes. Estuary is built differently. It decouples continuous capture from scheduled delivery, so you capture every change from Postgres once and then decide, per destination, how fresh BigQuery needs to be. That means near real-time when it matters and throttled overnight when it doesn't. That single dial, freshness versus cost, is what makes a Postgres to BigQuery pipeline actually sustainable, and it's what the rest of this guide shows you how to control.

This guide covers two ways to build it:

• Method 1: Estuary (recommended). A real-time CDC pipeline you can stand up in a few minutes, with automatic schema handling, exactly-once delivery, and a sync frequency you tune to balance latency against cost.
• Method 2: Manual migration with Google Cloud. Export from PostgreSQL, stage the file in Cloud Storage, and load it into BigQuery. Fine for a one-time move, but manual and quick to fall out of date.

👉 Just want the setup? Skip to the methods.

We'll also cover why PostgreSQL struggles as a warehouse, how BigQuery compares for analytics, and how to keep the pipeline both real-time and cheap.

Can You Use PostgreSQL as a Data Warehouse?

Yes, PostgreSQL works as a data warehouse for small datasets and light reporting. But it's a row based OLTP database built for transactions, not large analytical scans. As data grows, aggregations slow down, analytics start competing with your production traffic, and you can't scale storage and compute separately. That's why most teams keep PostgreSQL for their app and sync it to BigQuery for analytics, which is a serverless, columnar warehouse built for exactly that workload.

When to move: stay on PostgreSQL while data is small and reporting is occasional. Move to BigQuery once analytical queries slow your app, dashboards feel sluggish, or you outgrow a single instance. The rest of this guide shows you how.

Methods to Sync PostgreSQL to BigQuery

There are two practical ways to move data from PostgreSQL to BigQuery. The right one depends on whether you need continuous, real-time sync or just an occasional batch load.

Method 1: Estuary (recommended)

A real-time data movement platform that builds a pipeline from PostgreSQL to BigQuery using change data capture (CDC). Estuary captures every insert, update, and delete from your database and streams it to BigQuery on a sync schedule you control, so you balance freshness against BigQuery compute cost. It handles schema changes and backfills automatically, and you can set it up through the UI or manage it as code. Best for production pipelines that need to stay current.

Method 2: Manual migration via Google Cloud

Export your PostgreSQL data to a file, upload it to Google Cloud Storage, and load it into BigQuery. This works for a one-time migration or an occasional batch refresh, but it doesn't stay in sync on its own and needs manual work each time the data changes.

Need a pipeline that stays current without babysitting it? Start with Method 1. Estuary is the faster, lower maintenance path.

Both methods are covered step by step below. If you want the full connector details as you follow along, see Estuary's PostgreSQL capture connector and BigQuery materialization connector docs.

Method 1: How to Sync PostgreSQL to BigQuery With Estuary

Estuary captures changes from PostgreSQL using log-based CDC, stores them as a durable collection, and materializes them into BigQuery on a sync schedule you control. Capture runs continuously, so nothing is lost between syncs, and you decide how often data lands in BigQuery to balance freshness against warehouse cost. Setup takes a few minutes, and you can drive it from the dashboard or manage it as code.

Prerequisites

• An Estuary account. Sign up for the free tier at dashboard.estuary.dev.
• A PostgreSQL database you can enable logical replication on (self-hosted, Amazon RDS or Aurora, Google Cloud SQL, Neon, Supabase, and others are all supported).
• A BigQuery dataset, which is the equivalent of a database in BigQuery.

Step 1: Prepare PostgreSQL for CDC

Estuary reads changes from PostgreSQL's write-ahead log, so the database needs logical replication turned on and a user it can connect as. On a self-hosted instance, connect and run:

sql
CREATE USER flow_capture WITH PASSWORD 'secret' REPLICATION;
GRANT pg_read_all_data TO flow_capture;  -- PostgreSQL 14+

CREATE TABLE IF NOT EXISTS public.flow_watermarks (slot TEXT PRIMARY KEY, watermark TEXT);
GRANT ALL PRIVILEGES ON TABLE public.flow_watermarks TO flow_capture;

CREATE PUBLICATION flow_publication;
ALTER PUBLICATION flow_publication SET (publish_via_partition_root = true);
ALTER PUBLICATION flow_publication ADD TABLE public.flow_watermarks, your_table_1, your_table_2;

ALTER SYSTEM SET wal_level = logical;

Then restart PostgreSQL so the wal_level change takes effect. A few notes:

• The watermarks table is a small scratch table Estuary writes to during backfills to keep them accurate.
• The publication is the set of tables Estuary will capture. List only the tables you want to sync.
• Managed databases like RDS, Cloud SQL, and Neon enable logical replication through a parameter group or a console setting instead of ALTER SYSTEM. Follow the exact steps for your platform in the PostgreSQL capture connector docs.

Step 2: Prepare BigQuery

Estuary writes to BigQuery through a Google Cloud service account and stages data in a Cloud Storage bucket along the way.

1. In the Google Cloud console, create a service account and grant it these roles:
   • roles/bigquery.dataEditor
   • roles/bigquery.jobUser
   • roles/bigquery.readSessionUser
   • roles/storage.objectAdmin
2. Authenticate one of two ways. Either create and download a JSON key for the service account, or use Google Cloud IAM for keyless authentication, which avoids storing a long-lived key.
3. Create a Google Cloud Storage bucket for staging. It must be in the same region as your BigQuery dataset.

Step 3: Capture your PostgreSQL data

Now connect Estuary to PostgreSQL. Estuary will discover your tables and stream every change into a collection.

1. Go to the Sources page and click New Capture.
2. Search for PostgreSQL and choose the connector that matches your setup. The standard PostgreSQL connector is real-time; there are dedicated options for Amazon RDS, Google Cloud SQL, and Neon, plus a batch connector for databases that can't use logical replication.

3. Under Capture Details, give the capture a name and pick the data plane (region) you want it to run in.
4. Under Endpoint Config, fill in what you set up in Step 1:
   1. Server Address in host:port format
   2. User, which is flow_capture
   3. Database, which is usually the default postgres
   4. Authentication: enter the password, or switch to the AWS, Google Cloud, or Azure IAM tab for keyless auth

5. Click Next. Estuary tests the connection and discovers the tables in your publication. Select the tables you want to sync.
6. Click Save and Publish. Estuary backfills the existing rows and then keeps streaming new changes.

You can leave the advanced settings alone for a standard sync. They're there when you need them, for things like read-only capture, custom publication or slot names, and per-table backfill control.

Step 4: Materialize the data to BigQuery

With changes flowing into a collection, the last step is to send them to BigQuery.

1. Go to the Destinations page and click New Materialization.
2. Search for Google BigQuery and click Materialization on the connector.
3. Under Materialization Details, name it and choose a data plane.
4. Under Endpoint Config, enter:
   1. Project ID: the Google Cloud project that owns your dataset
   2. Region: the shared region for your bucket and dataset
   3. Dataset: your target BigQuery dataset
   4. Bucket: the staging bucket from Step 2
5. Under Authentication, upload your Service Account JSON or switch to the GCP IAM tab for keyless auth.
6. Open Sync Schedule and set your sync frequency. This is the freshness versus cost dial: leave it near real-time when data needs to be fresh, or lengthen it to cut BigQuery compute. If you don't set it, the default is 30 minutes.
7. Under Source Collections, link your PostgreSQL capture or add its collections directly, then choose which tables to materialize.
8. Click Save and Publish.

That's it. Estuary backfills your PostgreSQL tables into BigQuery and then keeps them current on the schedule you set. For deeper reference, see the guide to creating a data flow, the PostgreSQL capture connector, and the BigQuery materialization connector.

Why teams use Estuary for this pipeline

• Continuous log-based CDC, so BigQuery reflects PostgreSQL changes without re-scanning tables.
• A sync schedule you control, so you decide the tradeoff between data freshness and BigQuery compute cost.
• Automatic backfills and schema handling, including new columns and type changes, without breaking the pipeline.
• Exactly-once delivery for consistent, accurate data in BigQuery.
• Keyless IAM authentication for both PostgreSQL and BigQuery, so you don't have to manage long-lived keys.
• The same pipeline can fan out to more destinations later without re-extracting from PostgreSQL.

Method 2: How to Move PostgreSQL to BigQuery Manually

If you only need a one-time move or an occasional refresh, you can load PostgreSQL data into BigQuery by hand using Google Cloud tools. Choose this method if you need a single migration or an occasional manual refresh, not a pipeline that stays in sync. You export the data to a file, stage it in Cloud Storage, and load it into a BigQuery table. It works, but it's a point-in-time copy: it doesn't stay current, and you repeat the whole process every time the data changes.

Step 1: Export the PostgreSQL data to CSV

Use the COPY command to write a table to a CSV file:

sql
COPY my_table TO '/tmp/my_table.csv' WITH CSV HEADER;

For a filtered or joined export,COPYalso accepts a query:

sql
COPY (SELECT * FROM my_table WHERE updated_at > '2026-01-01') TO '/tmp/my_table.csv' WITH CSV HEADER;

Step 2: Upload the file to Cloud Storage

Move the CSV to a Google Cloud Storage bucket with gcloud storage:

bash
gcloud storage cp /tmp/my_table.csv gs://your-bucket-name/

Step 3: Load the file into BigQuery

1. In the Google Cloud console, open BigQuery.
2. Select your dataset, then create a table with Create table.
3. Set Source to Google Cloud Storage and select your CSV file from the bucket.
4. Choose the destination dataset and table name.
5. Set the schema to auto-detect, or define the columns yourself for tighter control.
6. Add partitioning or clustering if you want it.
7. Under write preference, choose Append to add to an existing table or Overwrite to replace it.
8. Click Create table.

Step 4 (optional): Schedule periodic refreshes

To keep the data semi-current, you can automate a recurring load with BigQuery scheduled queries or a Cloud Storage transfer. This runs on a fixed interval, so BigQuery is only ever as fresh as your last batch.

Where this method falls short

The manual path is fine for a migration or a rarely-changing table, but it adds up quickly as a routine:

• It's a snapshot, not a sync. Inserts, updates, and deletes after the export don't reach BigQuery until you run it again.
• You handle schema drift yourself. A new or changed column in PostgreSQL means fixing the load by hand.
• Batch loads get expensive and slow at scale. Re-exporting and reloading whole tables repeatedly wastes compute and time.
• There's no built-in recovery. A failed load in the middle of the process is on you to detect and redo.

If you need BigQuery to stay current on its own, Method 1 with change data capture is the more reliable long-term choice.

Method 1 vs. Method 2: which should you use?

For a pipeline that needs to stay current, use Method 1. For a single migration or a rare manual pull, Method 2 is enough.

How to Keep Your PostgreSQL to BigQuery Pipeline Real-Time and Cheap

With BigQuery, real-time has a cost, and how you tune your pipeline decides how much you pay. The two levers that matter are how often data lands in BigQuery (sync frequency) and how it's written (standard versus delta updates). Estuary exposes both, so you can run a pipeline that's as fresh as you need without paying for freshness you don't.

Here's the key idea most guides miss: with Estuary, capture and delivery are separate. The PostgreSQL capture streams every change continuously into a durable collection, no matter how you configure BigQuery. The materialization then writes those changes to BigQuery on a schedule you set. So you never lose data between syncs, and you control cost at the destination without touching the capture.

Lever 1: Sync frequency

Sync frequency is how often Estuary writes accumulated changes to BigQuery. Set it close to real-time when data needs to be current, or lengthen it to reduce BigQuery compute. If you don't set it, the default is 30 minutes.

You can also set a fast-sync window, so the pipeline runs near real-time during business hours when people are watching dashboards, and slows down overnight when nobody is. That alone can cut warehouse cost without hurting the freshness anyone actually notices.

Lever 2: Standard vs. delta updates

This is the bigger cost lever, and it's specific to how BigQuery bills. BigQuery charges query compute by the bytes a query scans, and loading data is effectively free. That distinction is everything here.

• Standard (merge) updates keep one row per key in BigQuery. On every sync, Estuary runs a MERGE that scans the destination table to apply changes. On large tables at high frequency, those repeated scans are usually the biggest driver of BigQuery cost.
• Delta updates append raw change records to the table without scanning it first, so you avoid the merge query cost entirely. The tradeoff is that the table becomes an append-only change log with multiple rows per key, which you reduce to the latest state downstream with a view or a scheduled query.

A practical pattern: keep smaller tables on standard updates so they stay query-ready, and switch your largest, highest-volume tables to delta updates to kill the scan cost where it actually hurts. You set this per table.

Watch the BigQuery quota at very high frequency

BigQuery limits how many load jobs a single table can take per day. A very aggressive sync frequency on a busy table can hit that limit, and the materialization will fail until the quota resets. Estuary's docs recommend a sync frequency of 5 minutes or longer for most workloads, and delta updates avoid the load-job quota on high-volume tables entirely. It's the kind of detail that decides whether a pipeline is stable in production, and it's easy to get wrong if you just set everything to real-time and hope.

One more thing: backfills aren't throttled

When a pipeline first starts, or when you add a table, Estuary backfills the existing data as fast as it can regardless of your sync frequency, then settles into the schedule you set for ongoing changes. So a longer sync frequency saves you money on steady-state streaming without dragging out the initial load.

Put together, these controls are what make a PostgreSQL to BigQuery pipeline sustainable. You get real-time when it matters, batch economics when it doesn't, and one place to tune the tradeoff instead of rebuilding the pipeline every time your needs change.

Conclusion: The Best Way to Sync PostgreSQL to BigQuery

Syncing PostgreSQL to BigQuery comes down to how current you need the data. For a one-time migration or an occasional pull, the manual Google Cloud path works. For anything ongoing, log-based change data capture with Estuary is the more reliable choice, because it keeps BigQuery in sync automatically instead of asking you to rerun an export every time the data changes.

What sets Estuary apart for this pipeline is control. Capture runs continuously, so no change is lost, while you decide how often data lands in BigQuery and how it's written. That means you can run near real-time when it matters, dial back to batch economics when it doesn't, and manage the freshness versus cost tradeoff from one place instead of rebuilding the pipeline. Add automatic backfills, schema handling, and exactly-once delivery, and you get a pipeline that stays current and stays affordable.

If you're building something that needs to stay live, start with Estuary.

Get started

Build your PostgreSQL to BigQuery pipeline for free. Sign up, connect your database and warehouse, and you'll be syncing in minutes.

Related articles: 

• Postgres to Snowflake
• PostgreSQL to Databricks
• PostgreSQL to Iceberg
• PostgreSQL to SQL Server