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();
# 注册一次日志目录并读
SHOW log_directory;
-- 按会话只监本连接
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';
-- 看 rows 预估 vs actual
-- 若 stats 过期：
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;
-- 识别卡住的 import 事务
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;
-- 通知连接池而不是硬扩数据库
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.
# 错误示范：全局设超大 work_mem
# work_mem = 2GB
# 修复对照：按会话/作业细分
work_mem = 64MB
maintenance_work_mem = 1GB
-- 特定大排序会话单独抬一点
SET work_mem = '256MB';
-- 验证没有 swap 挤出引用计数
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';