Postgres to BigQuery: how to load tables incrementally without breaking the warehouse

How do you connect Postgres to BigQuery?

You connect Postgres to BigQuery by running an extract process that reads from Postgres over the standard wire protocol and writes into BigQuery via load jobs or the Storage Write API. There is no native BigQuery driver that reads Postgres tables directly, so something has to sit in the middle: a managed connector, a scheduled script, or a streaming CDC pipeline.

Practically, the connection has three parts. First, network access: a read replica endpoint, an allowlisted IP range or a private connection, and a dedicated Postgres role with SELECT on the tables you replicate (plus REPLICATION if you go the CDC route). Second, a Google Cloud service account with bigquery.dataEditor on the destination dataset and, if you stage through Cloud Storage, object write access on a bucket. Third, the transfer itself, which should read from a replica rather than the primary so a heavy backfill does not compete with production traffic.

The managed path is the Postgres to BigQuery integration, which handles the watermark, the staging table, the MERGE, and the retries. If Snowflake is your warehouse instead, the same pattern applies through the Postgres to Snowflake connector.

What is the best way to sync Postgres to BigQuery in real time?

Log-based CDC using Postgres logical replication is the only approach that gets you true near real time. A replication slot streams changes from the write-ahead log through pgoutput or wal2json, so inserts, updates, and deletes arrive within seconds without repeatedly querying the source. Everything else is polling, and polling has a floor set by your schedule.

That said, most analytics teams do not need seconds. Here is the honest comparison of the three approaches.

Dimension	Full reload	Incremental watermark	Log-based CDC
Latency	Hours to daily	Minutes	Seconds
Warehouse cost	Highest (rewrites everything)	Low, scales with change volume	Low, but frequent small merges add up
Load on primary	Heavy full scans	Light with an index on the cursor	Light CPU, but a slot holds WAL
Handles deletes	Yes, implicitly	No, unless you soft-delete	Yes, natively
Complexity	Trivial	Moderate	High (slots, snapshots, failover)

Full dumps (pg_dump or COPY to Cloud Storage, then a BigQuery load job) are genuinely the right call for small, slow-moving reference tables: a few hundred thousand rows of countries, plans, or feature flags. Rewriting those nightly costs almost nothing and removes an entire class of bugs. The mistake is applying that pattern to a 200 million row events table.

Watermark loads are the default for the middle ground. You keep a high-water mark of the last updated_at you saw, query WHERE updated_at > :last_watermark, and merge the result. Two rules make it reliable: put a real index on the cursor column, and overlap the window slightly (re-pull the last few minutes) so rows committed out of clock order are not skipped. Since the MERGE is idempotent, re-reading a few rows costs nothing.

CDC earns its complexity when you have hard deletes you must reflect, when the source has no trustworthy updated_at, or when a downstream system genuinely needs sub-minute data. The cost is operational: replication slots need monitoring, initial snapshots need coordinating with the stream, and a failover to a new primary is a real event.

How do Postgres data types map to BigQuery types?

Postgres integers map to INT64, floats to FLOAT64, text to STRING, and timestamptz to TIMESTAMP while plain timestamp maps to DATETIME. The two that need care are numeric, which may exceed BigQuery's NUMERIC precision, and array columns, which become REPEATED fields rather than a scalar.

Postgres type	BigQuery type	Notes
smallint, integer, bigint	INT64	All widths collapse to a single 64-bit type
numeric, decimal	NUMERIC or BIGNUMERIC	NUMERIC holds 38 digits of precision with scale 9. Anything with more scale, or unconstrained numeric, needs BIGNUMERIC or a STRING passthrough
real, double precision	FLOAT64	Never use for money. Round-trip drift is real
boolean	BOOL	Postgres NULL stays NULL, not false
text, varchar, char	STRING	BigQuery does not enforce length, so varchar(50) constraints stop being checked
bytea	BYTES	Watch the hex vs escape output format on extract
date	DATE	Direct match
timestamp (without tz)	DATETIME	No zone attached. If your app writes local time here, you inherit the ambiguity
timestamptz	TIMESTAMP	Store UTC. BigQuery TIMESTAMP is an absolute point in time and renders in UTC by default
json, jsonb	JSON or STRING	JSON allows dot-path queries without parsing. STRING is safer if the shape is chaotic and you want raw fidelity
uuid	STRING	No native UUID type. Normalize casing so joins match
integer[], text[]	REPEATED INT64 / STRING	BigQuery arrays cannot contain NULL elements. Filter or substitute during load
enum	STRING	New enum values arrive silently, so validate downstream
interval	INTERVAL or INT64 seconds	Storing total seconds is usually easier to aggregate

The numeric case deserves one more sentence because it silently truncates. Postgres allows numeric with no precision declared, which accepts effectively unlimited digits. If a row carries a value that overflows the BigQuery NUMERIC scale, you either get a load failure (good) or a rounded value (bad, depending on your loader's settings). Declare BIGNUMERIC for financial columns where you are not certain of the bounds.

How do you handle deletes when replicating Postgres to BigQuery?

Watermark loads cannot detect a hard delete, because the row is simply gone and no updated_at changes. The three fixes are: soft-delete in Postgres with a deleted_at column that the watermark picks up, run log-based CDC which emits delete events from the WAL, or periodically reconcile primary keys between source and target and remove the orphans.

Soft deletes are the cheapest and most common answer. If the application sets deleted_at instead of issuing a DELETE, the deletion becomes just another update and rides the existing pipeline. Your BigQuery views then filter WHERE deleted_at IS NULL, and you keep the history for free.

Updates are handled by MERGE against a staging table. The pattern that survives retries is dedupe-then-merge: because a batch can contain several versions of the same row, first collapse the staging set with ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) and keep rank 1, then MERGE on the primary key. This is what makes a load idempotent. Re-running the same batch after a network timeout produces the same target state instead of duplicate rows, which matters because retries are normal, not exceptional.

Do not merge on a natural key you do not control. If the source primary key is a composite or gets recycled, generate a stable surrogate during extraction and merge on that.

How much does it cost to replicate Postgres to BigQuery?

BigQuery charges on two axes: storage for the bytes you keep, and compute for the bytes your queries scan (or slot time, on capacity pricing). Replication itself is cheap. What drives the bill is table design, because an unpartitioned table forces every query to scan the whole thing, and a nightly full reload rewrites storage you already had.

Partitioning is the single highest-leverage decision. Partition fact tables by a date column the analysts actually filter on (order date, event date) or by ingestion time if there is no natural candidate. A dashboard that asks for the last 30 days then reads 30 partitions instead of four years of history. Layer clustering on top for the columns that appear in WHERE and JOIN clauses, typically customer ID, tenant ID, or status, and BigQuery prunes blocks within each partition.

Full reloads compound the problem in a way that is easy to miss. Each reload writes a fresh copy of every row, which resets the clock on long-term storage discounts and pushes churn through the merge. Incremental loads touch only the partitions that changed. On the pipeline side, our pricing is flat by plan (Starter $49, Growth $149, Scale $399, Enterprise custom) rather than metered per row, so a busy month does not produce a surprise. See pricing, or the deeper breakdown in what data integration actually costs.

What breaks in production, and how do you catch it?

Four failure modes account for most incidents: schema drift when someone adds or retypes a column in Postgres, replication slot bloat when a CDC consumer stalls, timezone drift from mixing timestamp and timestamptz, and a load that stops succeeding without anyone noticing. The last one is the most damaging because the dashboard keeps rendering.

Schema drift is the routine one. A developer adds referral_source to users on Tuesday and your pipeline either ignores it or fails hard. Prefer a loader that adds new nullable columns automatically and alerts on incompatible changes, like an integer becoming text, which needs a human decision.

Replication slot bloat is the one that takes down the database. An inactive slot tells Postgres to retain WAL segments indefinitely, so if your CDC reader dies over a long weekend the primary's disk fills and writes stop. Set max_slot_wal_keep_size, alert on pg_replication_slots lag well before it is critical, and drop slots you have abandoned. Long-running transactions cause a related problem: an open transaction blocks vacuum and holds the WAL horizon in place.

For silent stops, alert on absence rather than errors. A rule like "the last successful run of this table was more than 90 minutes ago" catches the case where the job never started, which an error-only alert never sees. Teams running many tables often add a layer to monitor freshness, volume, and schema changes across the warehouse, so an anomaly surfaces before a stakeholder finds it in a report. At minimum, keep a per-run log with row counts and a watermark value you can inspect when the numbers look wrong.

If you are still deciding where transformation should live, the tradeoffs are in ETL vs ELT, and the broader pipeline approach is covered on the ETL solutions page. Once the modeled tables live in the warehouse, many teams push a slice of them back into an application database so features read them fast, which is the reverse of this pipeline and is covered in serving warehouse data back to Postgres. To see the mapping and schedule for your own tables, start a demo.