PostgreSQL Performance at Scale

Postgres is not slow by default; it is easy to use in a way that becomes slow under load. Most of the performance work we do falls into three buckets: indexes that match how the app actually queries, connection pooling so you do not open a new server process for every request, and read replicas when the database CPU is mostly serving reports and dashboards instead of writes.

Indexing with evidence

B-tree indexes (the default) fit equality filters, ranges, and many ORDER BY clauses. Add indexes for columns that show up in WHERE, JOIN, and sort clauses you care about. Partial indexes help when you always filter the same way, for example active rows only, so the index stays smaller.

Over-indexing hurts writes and vacuum work. Use pg_stat_statements or your host’s slow query log to see what actually runs in production, then use EXPLAIN ANALYZE to confirm a new index is used. Guessing from local dev data rarely matches prod.

Connection pooling

Each Postgres connection costs memory on the server. App servers that open a connection per request will flatten a small database fast. Put PgBouncer or your cloud’s pooler in front of the database so the app can use many short logical connections while Postgres keeps a smaller pool of real ones. Pick transaction vs session pooling based on whether you rely on prepared statements or session-level settings.

Read replicas

When reads dominate (analytics, search-heavy pages), point read-only traffic at a replica. Remember replication lag: if someone writes and immediately reloads, they might need to read from the primary for a moment. Use the primary for writes and for reads that must be strictly fresh.

Takeaway

Measure real queries, index to match, pool connections, add replicas when reads are the bottleneck. Those three moves cover most “Postgres is slow” tickets we see.

---

Cogent Softwares, Backend and data layer development.