/projects/postgres-query-optimization/
PostgreSQL Query Optimization
A benchmark query set that took 39 minutes unindexed isn't usable.
- PostgreSQL
- SQL
- database indexing
- schema design
- query optimization
Overview
A database-engineering follow-up to the Twitter MapReduce project, built for a database systems course: loading roughly 31 million tweets spanning 10 days into PostgreSQL and optimizing schema and indexing so a slow benchmark query set runs in seconds instead of tens of minutes.
Problem
Against a denormalized schema with no indexes, the assignment's benchmark queries over ~31 million tweet records took on the order of 39 minutes combined — far too slow for interactive use. A normalized, unindexed version of the same benchmark took about 2 minutes 5 seconds, still too slow.
Approach
I compared normalized and denormalized schema designs for the same tweet data and evaluated the query-performance tradeoff between them, then designed a targeted indexing strategy for the query patterns the benchmark actually exercises, rather than indexing every column.
Technical Implementation
PostgreSQL, using a combination of three B-tree indexes and one GIN index matching the reference solution, chosen for the specific filter and join patterns in the benchmark queries. Denormalizing traded disk space for query speed: the denormalized database uses about 75 GB on disk versus about 25 GB normalized, against 17 GB of raw source data.
Evaluation & Results
With indexes in place, the same 5-query benchmark ran in about 1.87 seconds total on the denormalized schema and about 0.70 seconds total on the normalized schema — down from roughly 39 minutes (denormalized) and 2 minutes 5 seconds (normalized) unindexed. All 5 queries passed their correctness checks in both configurations.
What I Learned
Schema design and indexing are not independent decisions: denormalizing traded disk space for query speed and changed which indexes were worth building. The biggest wins came from matching index type (B-tree vs. GIN) to the actual query pattern rather than indexing by default.