TopolearnMost production database problems are a few bad queries, missing indexes or misconfigured connections, found by measurement.
Tune in order of leverage, and measure before changing anything.
1. Find what costs the most. Enable the pg_stat_statements extension and sort by total_exec_time. A 2 ms query run 50,000 times a minute often matters more than a 3 second report run hourly. log_min_duration_statement logs individual slow statements.
2. Fix the query. EXPLAIN (ANALYZE, BUFFERS) the top offenders. The usual findings:
- missing indexes, especially on foreign keys and on the leading columns of common filters;
- N+1 queries: a loop issuing one query per row from application code. Replace with a join or
WHERE id = ANY($1); - deep
OFFSETpagination: switch to keyset pagination; - functions on indexed columns, implicit casts, or
ORconditions that prevent index use; - misestimates:
ANALYZE, extended statistics.
3. Fix the access pattern. Batch inserts (multi-row INSERT or COPY, which is fastest for bulk loads). Keep transactions short. Cache or precompute expensive aggregates in materialized views.
4. Tune the server. Typical starting points: shared_buffers about 25% of RAM; effective_cache_size 50-75% of RAM (a planner hint, allocates nothing); random_page_cost around 1.1 on SSDs. work_mem is allocated per sort or hash node, per query, so a high global value multiplied by hundreds of connections exhausts memory; raise it per session or per role for heavy reports instead.
5. Manage connections. Each PostgreSQL connection is a separate process using memory. Hundreds of mostly idle connections hurt; use a pooler such as PgBouncer and keep the pool near a small multiple of CPU cores.
6. Keep maintenance healthy. On big, busy tables, lower autovacuum's scale factor so it runs before bloat builds up. Set statement_timeout and idle_in_transaction_session_timeout as guard rails.
Example
-- needs shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls,
round(total_exec_time) AS total_ms,
round(mean_exec_time, 2) AS mean_ms,
left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
-- N+1 fix: one round trip instead of one per customer
SELECT customer_id, id, total FROM orders WHERE customer_id = ANY('{1,2,3}'::bigint[]);
-- More memory for one heavy report only (inside its transaction)
BEGIN; SET LOCAL work_mem = '256MB'; /* report query */ COMMIT;
-- Vacuum a large, hot table earlier (default scale factor is 0.2)
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);
-- Guard rails
ALTER ROLE web_app SET statement_timeout = '5s';
ALTER ROLE web_app SET idle_in_transaction_session_timeout = '60s';Remember
- Measure with pg_stat_statements; optimise by total time, not just slowest single run
- Most wins come from indexes, removing N+1 queries and better pagination
- work_mem applies per sort/hash node per query; raise it locally, not globally
- Pool connections and keep autovacuum ahead of bloat
Watch out for
- Raising work_mem globally to fix one report and running the server out of memory under load.