Make slow queries visible: enable the slow log and set the threshold
The tuning precondition is not guessing which query matters but objectively pulling out which ones are slow. PostgreSQL's log_min_duration_statement prints any statement running beyond the threshold into the log; combine with log_duration to also catch long but under-threshold statements.
On production, never set the threshold to 0 (that floods the log). Start at 2000ms, and after tuning inch it down to a sane level. Apply via ALTER SYSTEM for persistence or a per-session SET for ad-hoc monitoring.
ALTER SYSTEM SET log_min_duration_statement = 2000;
ALTER SYSTEM SET log_duration = on;
SELECT pg_reload_conf();
# Note the log directory once and read it
SHOW log_directory;
-- Monitor only this connection per session
SET log_min_duration_statement = 500;Read EXPLAIN: Seq Scan vs Index Scan and the rows estimate
Given a slow query, read the plan with EXPLAIN (ANALYZE, BUFFERS). One figure matters most: how far the rows estimate strays from the actual returned rows. If the planner guessed 100 rows but reality is 100k, stats are stale and VACUUM ANALYZE or a manual ANALYZE may transform the plan dramatically.
When a Seq Scan appears on a large table, judge by selectivity: a query returning north of ~10% of the table is sometimes better as a Seq Scan, where adding an index is pointless; below that fraction a Seq Scan is a real defect worth an index. That sentence alone steers most decisions in practice.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE buyer_id = 42 AND created_at > now() - interval '7 days';
-- Compare estimated vs actual rows
-- If stats are stale:
ANALYZE orders;
CREATE INDEX idx_orders_buyer_created ON orders(buyer_id, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE buyer_id = 42 AND created_at > now() - interval '7 days';Connection pool: raising max_connections is not a free lunch
Blindly raising max_connections to a few thousand is a classic error: every connection reserves process stack and memory, threads pile up on locks, and throughput collapses instead of climbing. Each PostgreSQL connection being its own process makes the hit heavier than expected.
The real answer is funneling DB-bound connections into a pool (PgBouncer) while keeping DB-side max_connections at a sane value (say 100–300). To judge overload, look at pilled-up "idle in transaction" states and lock-waiting sessions in pg_stat_activity. A reasonable guard below.
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY 2 DESC;
-- Spot stuck import transactions
SELECT pid, now()-query_start AS age, state, left(query,60) FROM pg_stat_activity WHERE state IN (\'active\',\'idle in transaction\') ORDER BY age DESC LIMIT 10;
-- Fix the connection pool instead of hard-scaling the database
ALTER SYSTEM SET max_connections = 300;
SELECT pg_reload_conf();Mistake vs fix: work_mem maxed out causing cascading slowness
Setting work_mem straight to 2GB to speed up a sort can backfire: with 30 concurrent sessions sorting, the 2GB each session claims becomes 60GB and the DB may swap into paralysis. A "single-parameter speedup" that is not checked across concurrency can drag down the whole cluster.
The sound approach routes sort/index work to its own lane (maintenance_work_mem reserves headroom for VACUUM/rebuilds) or raises work_mem per application session, never globally and blindly. Below, the wrong and right setups plus how to verify memory safety.
# Anti-pattern: set a huge work_mem globally
# work_mem = 2GB
# Fix: scope it per session/operation
work_mem = 64MB
maintenance_work_mem = 1GB
-- Raise it a bit for specific large-sort sessions
SET work_mem = '256MB';
-- Verify no swap eviction
SELECT name, setting FROM pg_settings WHERE name IN ('work_mem','maintenance_work_mem');
SELECT pg_size_pretty(shared_buffers) FROM pg_settings WHERE name='shared_buffers';