PostgreSQL powers some of the world's busiest applications — Instagram, Reddit, GitHub — but poor query patterns, missing indexes, and inadequate connection management cause most of the database performance problems we see in production codebases. This guide provides a systematic approach to diagnosing and resolving PostgreSQL performance bottlenecks, with real examples and the exact queries used to identify issues.
Diagnosing Performance Problems: The Right Tools
You cannot fix what you cannot measure. The first step is establishing visibility into what your database is actually doing, which queries are slow, and where they are spending time.
- EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON): The complete picture of query execution
- pg_stat_statements: Aggregate slow query statistics across all executions
- auto_explain extension: Automatically log plans for queries exceeding a threshold
- pgBadger: Parse PostgreSQL logs and generate query performance reports
- look at Seq Scan nodes on large tables — almost always indicates a missing index
- Rows estimate vs actual: Large divergences indicate stale statistics, run ANALYZE
Indexing Strategies That Actually Work
Indexes are the most impactful single optimization in PostgreSQL. But wrong index choices can slow down writes without helping reads. Understanding the trade-offs is critical.
- B-tree: Default, optimal for equality and range queries on most data types
- GIN: Full-text search (tsvector), JSONB containment (@>), array overlap operators
- BRIN: Extremely compact index for naturally ordered columns (timestamps, serial IDs)
- Partial indexes: WHERE status = 'pending' — index only the rows queries actually touch
- Covering indexes: INCLUDE (col1, col2) to satisfy queries without heap access
- pg_indexes_usage: Find unused indexes consuming write overhead without helping reads
Connection Pooling and High-Concurrency Patterns
PostgreSQL has a connection overhead of ~5–10MB per connection. At 1000 concurrent web workers each holding a connection, you have exhausted most of a server's memory before processing a single query.
- PgBouncer in transaction mode: 10,000+ web connections → 20 PostgreSQL connections
- Connection pool sizing: cores × 2 + spindle count is the empirical sweet spot
- Supabase PgBouncer: Managed connection pooling if using Supabase
- Statement-level vs transaction-level pooling: Transaction mode breaks prepared statements
- Read replicas: Route reporting queries and analytics to replicas, writes to primary
- pg_advisory_locks: Distributed locking without the overhead of table-level locks
Conclusion
PostgreSQL performance problems are almost always solvable without switching databases or throwing more hardware at the problem. The overwhelming majority of slow applications we diagnose have missing indexes, N+1 query patterns from ORMs, and connection pool exhaustion as their root causes. Sensussoft's backend engineering team conducts database performance reviews and optimization engagements for applications at any scale, from early-stage startups experiencing their first performance wall to high-traffic platforms processing millions of queries per hour.
About Raj Patel
Raj Patel is a technology expert at Sensussoft with extensive experience in web development. They specialize in helping organizations leverage cutting-edge technologies to solve complex business challenges.
