Why Performance Tuning Matters
PostgreSQL performance tuning is the practice of using EXPLAIN plans, statistics, and configuration parameters to make queries run faster and use resources efficiently. A database that is fast at 10,000 rows can grind to a halt at 10 million rows if indexes, memory settings, and query plans were never revisited, so tuning is an ongoing discipline rather than a one-time setup step.
Cricket analogy: Just as a bowling attack that worked in domestic cricket needs recalibration for international pitches with different bounce and pace, a schema tuned for a small dataset needs re-tuning as data volume grows into production scale.
Reading EXPLAIN and EXPLAIN ANALYZE
EXPLAIN shows the planner's chosen execution plan without running the query, while EXPLAIN (ANALYZE, BUFFERS) actually executes the query and reports real row counts, timing, and buffer hits versus disk reads. The single most useful signal is comparing 'estimated rows' to 'actual rows' in each plan node: a large mismatch usually means outdated table statistics, which you fix by running ANALYZE or by increasing the statistics target on skewed columns.
Cricket analogy: Comparing a bowler's pre-match analytics on a batter's weaknesses to what actually happens during the innings is like comparing EXPLAIN's estimated rows to EXPLAIN ANALYZE's actual rows — a mismatch means the scouting report was stale.
Indexing Strategy
Indexes speed up reads but slow down writes and consume disk, so the goal is to index columns used in WHERE, JOIN, and ORDER BY clauses without over-indexing. B-tree indexes handle equality and range queries well, GIN indexes are suited for JSONB and full-text search, and partial indexes (e.g., WHERE status = 'active') dramatically shrink index size when queries only ever filter on a small subset of rows.
Cricket analogy: A fielding captain places fielders only where the batter is statistically likely to hit the ball rather than covering every blade of grass, just as a partial index only covers the subset of rows queries actually target.
Run ANALYZE (or let autovacuum's analyze worker run) after large data loads — stale statistics are the single most common cause of a planner choosing a bad plan.
Key Configuration Parameters
shared_buffers controls how much memory PostgreSQL dedicates to caching data pages (commonly 25% of system RAM), work_mem controls memory available per sort or hash operation within a query, and effective_cache_size tells the planner how much OS-level caching to assume is available, influencing whether it favors index scans over sequential scans. Setting work_mem too high across many concurrent connections can exhaust server memory, since each sort or hash node in a plan may allocate its own work_mem allotment.
Cricket analogy: Allocating training ground time between the whole squad's fitness session and each individual batter's net practice is like balancing shared_buffers for the whole cluster against work_mem for each query's operations.
-- Inspect a slow query's real execution behavior
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;
-- Add a partial index that matches the actual filter
CREATE INDEX CONCURRENTLY idx_orders_pending_created
ON orders (created_at DESC)
WHERE status = 'pending';
-- Refresh planner statistics after a bulk load
ANALYZE orders;Never run CREATE INDEX without CONCURRENTLY on a production table during business hours — the plain form takes an ACCESS EXCLUSIVE lock that blocks all reads and writes until the index build finishes.
- EXPLAIN (ANALYZE, BUFFERS) reveals real timings, row counts, and buffer hit ratios, not just the planner's guess.
- A large gap between estimated and actual rows usually means statistics are stale — run ANALYZE.
- B-tree indexes suit equality/range queries; GIN suits JSONB and full-text search; partial indexes shrink index size for narrow query filters.
- shared_buffers caches data pages cluster-wide; work_mem is allocated per sort/hash operation and multiplies with concurrent connections.
- effective_cache_size informs the planner's assumption about OS cache size, influencing index-scan vs sequential-scan choices.
- Always use CREATE INDEX CONCURRENTLY in production to avoid blocking locks.
- Tuning is iterative: measure with EXPLAIN ANALYZE, change one variable, re-measure.
Practice what you learned
1. What does EXPLAIN (ANALYZE) do differently from plain EXPLAIN?
2. A large mismatch between estimated and actual row counts in an EXPLAIN plan most commonly indicates:
3. Which index type is best suited for querying JSONB columns?
4. Why should CREATE INDEX CONCURRENTLY be used in production?
5. What risk does setting work_mem very high system-wide introduce?
Was this page helpful?
You May Also Like
PostgreSQL Quick Reference
A condensed reference of essential PostgreSQL commands, psql shortcuts, and system catalog queries for day-to-day work.
PostgreSQL vs Other Databases
Compare PostgreSQL against MySQL, MongoDB, and other popular databases to understand where it excels and where alternatives may fit better.
PostgreSQL Backup and Restore
Understand logical and physical backup strategies in PostgreSQL, including pg_dump, pg_basebackup, and point-in-time recovery.