概念定义
两次 checkpoint 之间，数据库把变更写 WAL、把页弄脏在内存里。checkpoint 时刻全部脏页落盘、WAL 标记恢复起点。恢复只重放 checkpoint 之后的 WAL——因此间隔同时决定重放时长与刷写模式。
工作机制
PostgreSQL 的 checkpoint_completion_target 把刷写摊到间隔的一定比例内，接近 0.9 的值让 I/O 平坦但需要足够时间预算。MySQL InnoDB 按最老修改年龄刷页，innodb_io_capacity 是节流旋钮。过于频繁的 checkpoint 会对反复变脏的页造成反复驱逐的 I/O 浪费。
使用场景
与 checkpoint 周期对齐的周期性延迟尖峰是典型信号。把 pg_stat_bgwriter 或 InnoDB 刷写统计与 checkpoint 时间对齐后，拉长间隔或提高完成目标把曲线压平。
常见误区
checkpoint 不是备份，而是 WAL 流中的崩溃恢复边界。过长的间隔换来的是更长恢复与更大 WAL 保留；checkpoint 后首次修改触发的整页写会放大 WAL 体积——间隔越长此项越省、恢复越久。复制延迟也必须纳入考量：备机回放要跟得上间隔产出的 WAL。一次只动一个旋钮，观察延迟分位数而非均值。
代码示例
-- 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';