We run Postgres for a product with millions of users. Along the way I've broken it in every possible way. Here are the lessons I wish I'd known on day one.

Autovacuum is not optional

You can ignore autovacuum for a while. You cannot ignore it forever. Dead tuples accumulate. Query plans go bad. Eventually a query that used to take 10ms takes 3 seconds and nobody knows why.

Tune autovacuum earlier than you think. autovacuum_vacuum_scale_factor = 0.05 on big tables is a good default.

Connection pooling is not optional

Postgres connections are expensive. Every connection holds memory and a worker process. You will run out.

Use PgBouncer or equivalent. Set pool size conservatively. Your app might want 500 connections; Postgres can happily handle 50 if you pool properly.

Long-running transactions are silent killers

A transaction that's been open for 2 hours prevents vacuum from cleaning tuples newer than its start time. Your table bloats. Your queries slow down.…