ETL PIPELINE + PG vs BQ
The same 5.74M rows live in two systems now. A Python pipeline using server-side cursors extracted everything from PostgreSQL at 56K rows/second, flattened JSONB columns and point geometries, then loaded it all into BigQuery in 102 seconds. Running the same five analytical queries on both systems reveals a split personality: PostgreSQL with indexes returns a single flight's revenue in 2.6ms, while BigQuery needs 800ms just to spin up the job. But flip to a full-table revenue scan and BigQuery's columnar engine finishes before PostgreSQL is halfway through its sequential read.
0
Rows migrated
0
Tables
0
Duration (seconds)
0
Throughput (rows/s)
Pipeline Architecture
EXTRACT
PostgreSQL 16
Server-side cursor, 50K batch size, ~56K rows/s
TRANSFORM
Python / Pandas
JSONB flattening, point parsing, UTC normalization
LOAD
BigQuery
google-cloud-bigquery SDK, WRITE_TRUNCATE, autodetect
5.74M rows | 8 tables | 102 seconds
Performance: PostgreSQL vs BigQuery
Same queries, same dataset. PG: Docker (1 CPU, 512MB). BQ: on-demand US region.
| Query | PG (raw) | PG (indexed) | BigQuery | BQ Scanned |
|---|---|---|---|---|
| Route delay analysis | 292ms | 111ms | ~1.5s / ~0.5s cached | ~4MB |
| Revenue by fare class | 1,635ms | ~400ms | ~1.2s | ~25MB |
| Single flight revenue | 1,283ms | 2.6ms | ~0.8s | ~25MB |
| Flights from SVO | 33.9ms | 2.6ms | ~0.5s | ~3MB |
| Materialized view query | 174ms | 0.13ms | N/A | N/A |
PG WINS: POINT LOOKUPS
With proper indexes, single-row lookups take 2.6ms. BigQuery minimum is ~500ms due to job scheduling overhead, 200x slower for this pattern.
BQ WINS: FULL SCANS AT SCALE
For analytical queries scanning millions of rows, BigQuery columnar storage and massive parallelism keep times flat. PG times grow linearly with data.
SQL Syntax: PG vs BQ
| Concept | PostgreSQL | BigQuery |
|---|---|---|
| Conditional count | COUNT(*) FILTER (WHERE ...) | COUNTIF(...) |
| Time interval | INTERVAL '15 min' | INTERVAL 15 MINUTE |
| Epoch extraction | EXTRACT(EPOCH FROM (ts1 - ts2)) | TIMESTAMP_DIFF(ts1, ts2, SECOND) |
| JSONB access | column->>'key' | JSON_VALUE(col, '$.key') |
| Timezone conversion | timezone('UTC', ts) | Not needed (always UTC) |
| Median | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) | APPROX_QUANTILES(col, 2)[OFFSET(1)] |
| LATERAL join | JOIN LATERAL (...) ON TRUE | Not supported; use correlated subquery |
| Window functions | Full support | Full support (identical syntax) |
Architecture Comparison
| Dimension | PostgreSQL | BigQuery |
|---|---|---|
| Storage model | Row-oriented (heap) | Columnar (Capacitor) |
| Query execution | Single-node, multi-process | Massively parallel (Dremel) |
| Indexing | B-tree, GIN, GiST, BRIN, partial, expression | Partition pruning, clustering, search indexes |
| Transactions | Full ACID, MVCC | Snapshot isolation, no row-level locks |
| Schema changes | ALTER TABLE (may lock) | ALTER TABLE (instant, metadata-only) |
| Vacuuming | Required (dead tuples from MVCC) | Not applicable (append-only storage) |
| Connections | Per-client process (max_connections) | Serverless (no connection management) |
| Replication | Streaming + logical replication | Automatic (multi-region optional) |