Scaling PostgreSQL to 50 million rows without breaking a sweat

PostgreSQL is an incredible database. It can handle a lot more than most startups ever throw at it, as long as you understand what you're asking it to do. We learned that lesson the hard way.

Last November, our tasks table crossed 20 million rows. Nothing dramatic happened — queries were fast, writes were fine. But we had a sequential scan hiding in a reporting query that nobody had noticed. At 20M rows, it took 4 seconds. At 40M rows three months later, it took 11 seconds. At 2am on a Tuesday, it took our API down.

The immediate fix was an index. But the real work was understanding why the query planner had stopped using our existing indexes. The answer: statistics staleness. ANALYZE hadn't run in 6 days because our autovacuum settings were tuned for smaller tables.

We fixed the vacuum settings, added PgBouncer for connection pooling (we were opening a new connection per request in some API routes — embarrassing), and introduced table partitioning on our highest-volume tables by created_at month. Query times dropped 60%.

The deeper lesson: at startup scale, Postgres will forgive almost anything. At growth scale, every assumption you made gets stress-tested. Build your observability before you need it — slow query logs, pg_stat_statements, and a dashboard that tells you when autovacuum last ran.