Deep EVM #27：PostgreSQL大规模性能——索引、VACUUM和查询优化

索引策略

PostgreSQL支持多种索引类型，每种适用于不同场景：

B-tree索引

默认索引类型，适合等值和范围查询：

CREATE INDEX idx_users_email ON users (email);

GIN索引

适合JSONB和全文搜索：

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

部分索引

只索引满足条件的行，节省空间和提升性能：

CREATE INDEX idx_active_users ON users (email) WHERE active = true;

VACUUM

PostgreSQL的MVCC机制产生死行。VACUUM回收这些空间：

• VACUUM — 标记死行空间可复用
• VACUUM FULL — 完全重写表，回收所有空间（锁表）
• autovacuum — 自动运行的后台VACUUM

调优autovacuum

ALTER TABLE large_table SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 默认0.2
    autovacuum_analyze_scale_factor = 0.005
);

查询优化

使用EXPLAIN ANALYZE分析查询计划：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

关注：

• Seq Scan vs Index Scan
• 估算行数 vs 实际行数
• 排序和哈希操作

总结

PostgreSQL在大规模下的性能取决于正确的索引策略、积极的VACUUM维护和基于查询计划的优化。这三者缺一不可。