堆膨胀与 Vacuum

概念定义

PostgreSQL 的更新与删除会留下死版本，空间不会立即归还。vacuum 负责清扫：更新空闲空间映射与可见性映射。清扫跟不上时死版本累积：表文件变大、扫描变长、缓冲缓存被尸块塞满——这就是膨胀。

工作机制

autovacuum 按“死元组阈值加活跃行比例”逐表触发。长事务持有的快照是 vacuum 必须尊重的边界——它们还能看到的死元组动不得。VACUUM FULL 与 pg_repack 通过重建物理回收空间，但锁的代价各不相同。

使用场景

对死元组比例与事务号回卷年龄提前告警；批量删除后安排一次 vacuum；更新密集的队列表值得把逐表 autovacuum 比例因子调得远低于默认。

常见误区

vacuum 只是把页内空间标记可复用，不会把磁盘还给操作系统。膨胀不只是浪费磁盘：每次扫描都在读死行、每个索引都被稀释、index-only scan 退化成逐行回堆。膨胀还会复合——膨胀的索引摊到更多页，让下一轮 vacuum 更长；回卷保护拖到最后会反过来饿死正常流量。

代码示例

-- B-tree index for equality and range queries
CREATE INDEX idx_products_category_price
    ON products (category_id, unit_price);

-- Partial index: only index active products
CREATE INDEX idx_products_active
    ON products (product_name)
    WHERE discontinued_date IS NULL;

-- Expression index for case-insensitive searches
CREATE INDEX idx_customers_email_lower
    ON customers (LOWER(email));

-- Unique index for constraint enforcement
CREATE UNIQUE INDEX idx_orders_customer_date
    ON orders (customer_id, order_date)
    WHERE status != 'cancelled';

-- Covering index (INCLUDE) for index-only scans
CREATE INDEX idx_order_items_covering
    ON order_items (order_id)
    INCLUDE (product_id, quantity, unit_price);

-- Concurrent index creation (no table locks)
CREATE INDEX CONCURRENTLY idx_products_name
    ON products (product_name);

-- BUFFERS 选项显示磁盘与缓存命中，真实成本的核心
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= '2026-10-01'
ORDER BY o.created_at DESC
LIMIT 50;

-- 检查估算偏差：预估行数与实际行数差距大时统计信息过期
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 统计信息刷新后重测
ANALYZE orders;
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 验证新索引被采用：Index Scan / Index Only Scan
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status);

EXPLAIN (ANALYZE)
SELECT id FROM orders
WHERE customer_id = 42 AND status = 'paid';