Migrating from PostgreSQL to BigQuery

Nobody moves their application database to BigQuery. It has no primary key enforcement, no row-level UPDATE performance to speak of, and query latency measured in seconds. What people actually do -- and what this guide covers -- is offload analytics: PostgreSQL stays the system of record, and its data gets replicated into BigQuery where full-table scans over billions of rows are the normal case instead of an incident.

That framing decides your architecture. If you only need BigQuery to see PostgreSQL data occasionally, you may not need a migration at all: federated queries via EXTERNAL_QUERY() let BigQuery run SQL against a Cloud SQL or AlloyDB instance directly. It's slow and limited, but for a handful of lookup tables it beats building a pipeline. For everything else, you're choosing between a one-time batch load and continuous CDC.

Schema translation

BigQuery's type system is small and its DDL has no indexes, no enforced primary/unique/foreign keys, and no sequences. The mappings below follow what Google's own Datastream service does (verified against the official type-mapping docs, July 2026) -- a sensible default even for hand-built pipelines:

What replaces indexes: partitioning (usually on a date/timestamp column) plus clustering (up to 4 columns). Get these right at table creation -- they're what keeps both latency and cost down:

CREATE TABLE analytics.orders (
  id INT64,
  customer_id INT64,
  amount NUMERIC,
  created_at TIMESTAMP
)
PARTITION BY DATE(created_at)
CLUSTER BY customer_id;

One-time batch load

The path is PostgreSQL → files → Cloud Storage → bq load. CSV works, but if you can produce Parquet or Avro, do it -- types travel with the file, there's no NULL-vs-empty-string ambiguity, and loads are faster. A minimal CSV version:

psql "$SOURCE_URL" -c "\copy (SELECT * FROM orders) TO 'orders.csv' WITH (FORMAT csv, HEADER false, NULL '\\N')"
gsutil cp orders.csv gs://my-migration-bucket/
 
bq load --source_format=CSV --null_marker='\N' \
  analytics.orders gs://my-migration-bucket/orders.csv \
  ./orders_schema.json

Loading into BigQuery is free (it uses shared slot capacity, not your query bytes), so there's no cost penalty for reloading a table while you iterate. Two CSV gotchas: BigQuery's CSV parser defaults --null_marker to empty string, silently turning your \N markers into literal text if you forget the flag, and embedded newlines in text columns require --allow_quoted_newlines.

Continuous replication: Datastream

Datastream is Google's managed CDC service: serverless, log-based via the standard pgoutput logical-decoding plugin, PostgreSQL 10+ (self-hosted, Cloud SQL, AlloyDB, RDS, Aurora). Setup is source profile → destination profile → stream; the backfill copies history and the stream applies changes with a configurable staleness limit that directly trades freshness against cost.

Source-side prep is ordinary logical replication:

-- wal_level = logical on the source, then:
CREATE PUBLICATION ds_pub FOR TABLE orders, customers;
SELECT pg_create_logical_replication_slot('ds_slot', 'pgoutput');

The traps, all verified against Google's docs and issue tracker:

• REPLICA IDENTITY FULL poisons tables with jsonb or array columns. Datastream treats the replica-identity columns as the BigQuery primary key, and with FULL that means every column -- jsonb and array columns can't be key parts, so the table fails with BIGQUERY_UNSUPPORTED_TYPE_FOR_PRIMARY_KEY. Give such tables a real primary key or a unique index instead.
• One slot per stream = head-of-line blocking. A large transaction on one high-churn table delays every other table in the stream. Google's own best practice: split high-volume and low-volume tables into separate streams.
• The abandoned-slot WAL bomb. A replication slot with no consumer retains WAL indefinitely and will fill the source disk. If you stop a stream permanently, drop its slot.
• Datastream writes merge-mode tables by default (upserts based on the key); append-only mode exists if you want the raw change history.

Third-party alternatives (Fivetran, Airbyte, Estuary, Debezium + Dataflow) ride the same logical-decoding mechanics; the slot warnings apply to all of them.

Query dialect deltas

BigQuery's GoogleSQL is close enough to lull you and different enough to bite:

• No ILIKE. Use LOWER(col) LIKE LOWER(pattern) or REGEXP_CONTAINS(col, '(?i)pattern').
• No DISTINCT ON. Use QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1 -- arguably nicer once you adjust.
• generate_series → GENERATE_ARRAY/GENERATE_DATE_ARRAY + UNNEST.
• Quoting flips: backticks for identifiers, single quotes only for strings. :: casts become CAST(x AS INT64) (or SAFE_CAST, which returns NULL instead of erroring -- there's no PostgreSQL equivalent and you'll come to like it).
• jsonb operators (->, ->>, @>) become JSON_QUERY/JSON_VALUE or dot paths with lax semantics.
• Every query scans whole columns: SELECT * on a wide table costs real money under on-demand pricing. The LIMIT 10 habit doesn't help -- bytes scanned are decided by columns and partitions, not rows returned.

That last point is the real migration: you're moving from a latency mindset to a bytes-scanned mindset. Partition filters and column pruning replace index tuning. Set maximum_bytes_billed on ad-hoc queries while the team recalibrates.

Validation

Same aggregates, both engines, diff the output:

SELECT COUNT(*), SUM(amount), MIN(created_at), MAX(created_at)
FROM orders;

Watch timestamp semantics -- PostgreSQL timestamp (no zone) values pass through as UTC in BigQuery, so a source column that actually held local times will shift your MIN/MAX. For CDC, re-check after cutover traffic and confirm the stream's freshness matches what you configured. Mako connects to PostgreSQL and BigQuery side-by-side, which makes the run-identical-queries loop fast.

Common Mistakes

For the underlying platform comparison, see BigQuery vs PostgreSQL. Related how-tos: importing CSV to BigQuery, connecting to BigQuery from Python, and connecting to PostgreSQL from Python.

Mako connects to both PostgreSQL and BigQuery with AI-powered autocomplete -- useful for running the same validation queries against both engines during a migration. Try it free at mako.ai.