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';Stats and bloat: two hidden reasons a valid index is ignored
Sometimes an index exists yet EXPLAIN still picks a Seq Scan. Besides the selectivity rule, two hidden causes come first: stale statistics, and table/index bloat (pile-up of dead tuples). Bloat inflates the page count the planner sees, so it decides a full scan is cheaper.
The fix is ongoing maintenance: autovacuum is on by default, but frequently-updated tables can outpace it. Confirm autovacuum is working by checking pg_stat_user_tables last_autovacuum and vacuum_count; tune per-table autovacuum_vacuum_scale_factor and watch index bloat.
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_analyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
-- 强制做一次标志性维护
VACUUM (ANALYZE, VERBOSE) orders;
-- 对高更新表放大 vacuum 触发
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);Tuning first tier: shared_buffers, work_mem, and effective_cache_size
PostgreSQL defaults tilt conservative, tuned for a generic small box. Three parameters give the easiest wins: shared_buffers near ~25% of physical RAM (capped sanely), work_mem controlling sorts and hashes (counted per session, so too big explodes under concurrency), and effective_cache_size telling the planner how much filesystem cache to trust (about 75% of RAM).
Size them with a reproducible formula rather than by gut feel—here is an entry formula by GB of RAM. Before editing confirm the postgresql.conf path, verify the live value with SHOW, and note that server-class params need a restart while session-scoped ones reload dynamically.
# 8C/16G 机器的示例后设
shared_buffers = 4GB # 16GB * 25%
work_mem = 64MB # 每条排序/哈希作业最多
effective_cache_size = 12GB # 16GB * 75%
maintenance_work_mem = 1GB # 供 VACUUM/重建索引
# 查生效值
SHOW shared_buffers; SHOW work_mem; SHOW effective_cache_size;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';