Data · 04 / 04 · PostgreSQL · SQL · MySQL
Postgres Tuning on 38M Taxi Trips
Twelve analytical questions over every 2023 NYC taxi trip, taken from a 488 ms median to 19 ms with exactly the same answers.
- PostgreSQL 16
- MySQL 8.4
- SQL
- Python
- Docker
Why I built this
As tables grow, analysts' queries slow down and dashboards start timing out. The usual fix is a bigger, pricier server. Knowing how partitioning, the right indexes and precomputed views work can make the same hardware answer in milliseconds.
How much faster can the same queries get on the same machine, without changing a single answer? Everyone says “just add an index”. I wanted to read the query plans on 38 million real rows and see which changes actually matter, and what each one costs.
What I built
Every yellow-taxi trip of 2023 (38 million rows, 4.5 GB) loaded into PostgreSQL, and twelve questions an analyst would really ask.
Each question ran against a plain table, then a tuned design with partitions, BRIN and covering indexes and materialized views, measured with EXPLAIN (ANALYZE, BUFFERS) and checked to return exactly the same rows. Then the same suite ran on MySQL.
How it works
Every plan is committed to the repo, so each speed-up can be traced to the exact change in the plan.
What made it fast
One week of March: 580k pages read down to 12k.
Midtown to JFK for a full year: 356 ms to 7.7 ms, index-only, 0 heap fetches.
Revenue per month for the year: 4,959 ms to 25 ms.
Exact tip-percentage quartiles: 2,412 ms to 10.8 ms, no 7.9M-row sort.
Measured
- Median query
- Baseline 488 ms, tuned 19.4 ms (25×), MySQL 8.4 47.2 ms
- Whole suite
- Baseline 19.0 s, tuned 0.45 s (42×), MySQL 2.16 s
- Cost
- 3.4 GB of indexes and a 44 s concurrent view refresh, documented
- Tests
- 41 tests, 95% coverage, on a committed 41k-trip sample