Scaling PostgreSQL for Growing Startups
PostgreSQL will take you further than most teams expect. The mistakes that force people to migrate off it usually have nothing to do with PostgreSQL — they have to do with how it was used. A few habits, adopted early, push the ceiling out by years.
Index for the queries you actually run
Run EXPLAIN ANALYZE on your slowest endpoints before you tune anything. The bottleneck is almost never where intuition says it is. A missing index on a foreign key, a sequential scan over a million rows for a count, an OR clause that defeats a composite index — these are the recurring offenders.
Partial and expression indexes are underused. If 95% of queries filter on `status = "active"`, a partial index on that subset is dramatically smaller and faster than a full index.
Connection pooling earlier than you think
Postgres uses a process-per-connection model. Each connection costs real memory, and the planner overhead does not scale linearly. PgBouncer in front of the database — even at small scale — pays for itself the first time a traffic spike would have exhausted connections.
Transaction-mode pooling is the right default for most web apps. Session-mode is only needed when you rely on session-local state like prepared statements or `SET LOCAL` outside transactions.
Read replicas before sharding
A streaming replica is a one-line config change and absorbs most of the read traffic you will ever generate. Route analytics queries, exports, and background jobs to the replica before you consider anything more exotic.
Sharding is a one-way door. Most apps that shard discover, painfully, that they did not need to. Exhaust vertical scaling, replication, and partitioning first.
Vacuum is not optional
Autovacuum exists for a reason. If you have a write-heavy table and tuned-down autovacuum thresholds, you are accruing bloat that will eventually force a maintenance window. The cheapest scaling work you will ever do is making autovacuum more aggressive on hot tables.
Monitor table and index bloat the same way you monitor disk and CPU. A graph that climbs slowly for six months is the warning you get to avoid an outage.