Skip to content

Latest commit

 

History

History

postgres-vs-bigquery

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

README.md

Postgres against BigQuery on the same orders table

This experiment loads the same orders table into Postgres and BigQuery at 5, 10 and 20 million rows. It times six queries on each engine: two lookups, two full-table aggregations and two single-row writes. For BigQuery it also records the dry-run estimate, the bytes billed and the cost at list price.

The data

One table, orders(order_id, customer_id, created_at, status, amount). Every column is an integer formula of the row number, so both engines generate the same rows inside the engine and no file is exported or uploaded. The formulas are in sql/postgres/build.sql and sql/bigquery/build.sql.

  • 1 million customers, each with the same number of orders.
  • created_at spreads the orders evenly over 2024 and 2025, in order_id order.
  • status is 90% delivered, 6% shipped, 3% cancelled and 1% refunded.
  • amount runs from 1.00 to 499.99.

After each build, the tool compares a checksum (row count, sums of customer_id, amount and created_at seconds, and the refunded count) across the engines and stops if they differ. The results are in results/checksums.csv.

The setup

Postgres 18.6 runs in Docker from compose.yaml, with the image pinned by digest. Settings: shared_buffers=4GB, work_mem=64MB, max_parallel_workers_per_gather=4, effective_cache_size=8GB, synchronous_commit=on. The table has a primary key on order_id, an index on (customer_id, created_at), and expression statistics on date_trunc('month', created_at). Without those statistics the planner expects one group per row for the monthly query and runs it in a single process; with them it runs a parallel hash aggregate (403 ms down to 82 ms at 2 million rows, plans in results/plans/).

BigQuery uses on-demand pricing in the US multi-region. The table is clustered by customer_id and not partitioned. Every query runs with the query cache off and a 5 GiB maximum_bytes_billed cap. The dataset's tables expire after 14 days.

Each query runs once as a warm-up and then 7 measured times, each with a different random id where it takes one. The ids come from a fixed seed, so both engines get the same ids. The reported value is the median of the 7 measured runs, with the minimum and maximum next to it.

Postgres time is measured by the Python client on the same laptop as the database, so it includes the driver but no network. BigQuery reports two times: server_ms is the job's start to end from its metadata, and wall_ms is what the client waited, including the round trips from the client's location to the US region.

Run it

You need Docker, uv, and a Google Cloud project with BigQuery enabled and application default credentials.

export OLTP_OLAP_BIGQUERY_PROJECT=<your-project-id>
uv run oltp-olap all                  # build, run and chart 5, 10 and 20 million rows
uv run oltp-olap all --rows 2000000   # a quick trial
uv run oltp-olap teardown             # remove the Postgres container and volume and the BigQuery dataset

At 20 million rows Postgres needs about 2.6 GB of disk, and the BigQuery table is about 1 GB. The run on 26 October 2026 billed 28.8 GiB of BigQuery queries, which is inside the monthly free tier and $0.18 at list price without it.

Results

  • results/measurements.csv: every run, warm-ups included (run 0).
  • results/summary.csv: medians per size, engine and query, with bytes billed and cost per query.
  • results/builds.csv: build time and table size per engine and size.
  • results/environment.json: the settings that were in effect.
  • results/latency.png: the chart.
  • results/plans/: EXPLAIN (ANALYZE, BUFFERS) output behind the plan notes below, captured by hand after the run.

Plan changes to know about

For the top 10 customers query, Postgres picked different plans at different sizes. At 5 and 10 million rows it read the (customer_id, created_at) index in order and aggregated group by group, which visits the heap in random order (2.30 s and 5.37 s). At 20 million rows it switched to a parallel hash aggregate over a sequential scan that spills to disk (2.97 s). Raising work_mem to 256MB at 20 million rows made it pick a single-process hash aggregate that was no faster (3.39 s against 3.38 s under EXPLAIN ANALYZE), so the run keeps 64MB. The EXPLAIN (ANALYZE, BUFFERS) output for each of these is in results/plans/.

What this setup does not show

  • One client at a time. There is no concurrency, which is where Postgres write throughput and BigQuery slot limits and DML queueing both matter.
  • A table that fits in memory. Postgres reads come from RAM after the warm-up, and a table larger than RAM would slow its aggregations.
  • One laptop against a managed service. A Postgres server with more cores, or a column store extension, would change the aggregation numbers.
  • BigQuery's other write paths. The Storage Write API and streaming inserts are built for high-volume ingestion; this measures DML statements only.
  • 20 million rows is small for a warehouse. The disk on the machine that ran it set that limit.