Migrating from Postgres to BigQuery

Postgres is a fantastic transactional database. It's also the one a lot of teams accidentally turn into their analytics warehouse — until the reporting queries start locking up the app, the read replica can't keep up, and someone's writing a 400-line CTE against production at 2am.

That's the signal to move analytics off Postgres and onto a real warehouse. If you're on Google Cloud, BigQuery is the natural target. This is how the move works — and where Postgres habits will trip you up.

Why split analytics off Postgres

• Stop analytics from fighting the app. Heavy aggregate queries on a transactional Postgres contend with the OLTP workload. In BigQuery they run on separate, serverless compute.
• Columnar, at scale. Postgres is row-oriented; scanning billions of rows for a SUM is not what it's built for. BigQuery's columnar storage is.
• No read replicas to babysit. The "just add a replica for reporting" pattern hits a ceiling. BigQuery scales without you managing anything.
• A clean transformation layer with dbt instead of views stacked on views inside the OLTP database.

To be clear: this is usually not a rip-and-replace. Postgres stays as the application database. You're adding BigQuery as the analytics destination and moving reporting there.

Getting the data across: Datastream

The cleanest path on GCP is Datastream, Google's serverless CDC service, which has first-party PostgreSQL → BigQuery support. It reads the Postgres write-ahead log (via logical replication / a replication slot) and streams changes into BigQuery continuously.

Setup essentials on the Postgres side:

• Set wal_level = logical.
• Create a publication and a replication slot for Datastream.
• Grant the replication role.

Datastream handles the initial backfill and then keeps BigQuery current. The alternatives — Fivetran or Airbyte — make sense if you're consolidating SaaS sources alongside Postgres in the same warehouse; both also do Postgres log-based CDC.

The data-type mapping

Postgres → BigQuery is friendlier than Oracle, but a few types need care:

Two Postgres-specific things bite people:

• jsonb. BigQuery has a real JSON type now, which is great — but the access functions (JSON_VALUE, JSON_QUERY) differ from Postgres's -> / ->>. Every place your app queried JSON needs rewriting.
• Arrays. Postgres arrays map to BigQuery ARRAY, but the semantics around NULL and UNNEST differ. Validate anything array-heavy.

No indexes, no enforced keys

Like every BigQuery migration: there are no indexes and no enforced primary keys. Your Postgres b-tree indexes don't come along. Performance comes from:

• Partitioning — typically on a timestamp column (or ingestion time).
• Clustering — up to four columns BigQuery uses to prune and co-locate.

Redesign your hottest tables around a partition key and cluster keys instead of porting indexes. Uniqueness that was a Postgres constraint becomes a dbt test.

Transformation in dbt

Raw CDC tables land in BigQuery, register as dbt sources, and you build layered models on top — including partition/cluster config where the old indexes used to be:

-- models/staging/stg_events.sql
{{ config(
    materialized='table',
    partition_by={'field': 'event_ts', 'data_type': 'timestamp', 'granularity': 'day'},
    cluster_by=['user_id']
) }}
 
SELECT
    event_id,
    user_id,
    CAST(created_at AS TIMESTAMP)          AS event_ts,   -- was timestamptz
    JSON_VALUE(payload, '$.source')        AS source,      -- was jsonb ->>
    props                                                   -- was int[], now ARRAY<INT64>
FROM {{ source('app_postgres', 'raw_events') }}
WHERE event_id IS NOT NULL

models:
  - name: stg_events
    columns:
      - name: event_id
        tests: [unique, not_null]

The reporting logic that used to be Postgres views stacked on views moves into tested, version-controlled dbt models — and stops competing with your application for connections.

Parallel validation: the non-negotiable step

Run reporting against both Postgres and BigQuery for two to four weeks. Compare daily:

• Row counts on the tables that feed dashboards.
• Aggregate values — the JSON and array conversions are the usual culprits when they drift.
• The same dashboards on both.

Match for a week, then repoint BI to BigQuery and retire the reporting queries against Postgres.

What changes

Timeline

For a mid-size Postgres analytics workload:

Faster than an Oracle migration — there's no PL/SQL to rebuild — as long as the JSON and array logic is well understood.

Proof it works

BigQuery + dbt is the stack we build on most. Finsights runs multi-tenant embedded analytics for 80+ tenants on it — sub-second at p95, 90% of manual reporting gone. Boldspace processes 50K+ reviews on the same foundation with onboarding cut from six weeks to one. Moving analytics off Postgres lands you on exactly this kind of separated, serverless stack.

Analytics queries choking your Postgres app database? Book a discovery call and we'll scope the split. Related: Oracle → BigQuery and the legacy stack migration playbook.