1 min read208 words

CREATE INDEX CONCURRENTLY 实战

CREATE INDEX CONCURRENTLY 是生产环境最常用的安全变更手段之一。普通 CREATE INDEX 阻塞所有写入，而 CONCURRENTLY 允许表在建索引期间正常读写——代价是建索引时间更长、有更多限制需要了解。

CONCURRENTLY 的工作原理

普通 CREATE INDEX：
1. 获取 SHARE 锁（阻塞所有 INSERT/UPDATE/DELETE）
2. 扫描表，构建索引
3. 释放锁
→ 整个过程业务无法写入
CREATE INDEX CONCURRENTLY：
1. 获取 SHARE UPDATE EXCLUSIVE 锁（仅阻塞 VACUUM 等维护操作）
2. 第一遍：扫描表，构建初始索引版本
3. 等待所有并发事务完成（看两个快照）
4. 第二遍：扫描并合并第一遍之后的变化
5. 标记索引为有效
→ 整个过程业务可以正常读写（但索引构建时间 2-3 倍于普通方式）

基础用法

-- ✅ 生产环境标准姿势
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders (user_id, status);
-- 验证索引创建成功
SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE tablename = 'orders' AND indexname = 'idx_orders_user_status';
-- 监控建索引进度（PostgreSQL 12+）
SELECT
phase,
blocks_done,
blocks_total,
ROUND(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS progress_pct,
tuples_done,
tuples_total
FROM pg_stat_progress_create_index
WHERE relid = 'orders'::regclass;

重要限制与注意事项

1. 不能在事务块中使用

-- ❌ 报错：
BEGIN;
CREATE INDEX CONCURRENTLY idx_test ON orders (created_at);
-- ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
-- ✅ 正确：在事务外单独执行
CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at);

2. 中断后会留下 INVALID 索引

-- 如果 CONCURRENTLY 过程中被中断（Ctrl+C、网络断开、服务器重启）
-- 会留下一个标记为 INVALID 的索引，它：
-- - 不会被查询使用（但存在）
-- - 仍然消耗写入性能（所有 INSERT/UPDATE 仍维护它）
-- - 需要手动清理
-- 查找 INVALID 索引
SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_indexes pi
JOIN pg_class pc ON pc.relname = pi.indexname
JOIN pg_index pix ON pix.indexrelid = pc.oid
WHERE NOT pix.indisvalid;
-- 删除无效索引（也要用 CONCURRENTLY）
DROP INDEX CONCURRENTLY idx_orders_created;
-- 重新创建
CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at);

3. 可能被长事务阻塞

-- CONCURRENTLY 的第二阶段需要等待所有并发事务完成
-- 如果有长事务（如大批量操作），CONCURRENTLY 会一直等待
-- 监控：如果 pg_stat_progress_create_index 中 phase 卡在
-- "waiting for writers before build" 超过几分钟
-- 检查是否有长事务阻塞
SELECT pid, now() - xact_start AS age, state, LEFT(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND now() - xact_start > INTERVAL '5 minutes'
ORDER BY age DESC;

替换现有索引

-- 场景：需要修改索引（如改变列顺序、添加 INCLUDE 列）
-- 策略：先创建新索引，验证后删除旧索引
-- 步骤 1：创建新索引（不影响现有索引使用）
CREATE INDEX CONCURRENTLY idx_orders_user_status_v2
ON orders (user_id, status)
INCLUDE (total_amount, created_at);  -- 新增覆盖索引列
-- 步骤 2：验证新索引
EXPLAIN ANALYZE
SELECT user_id, status, total_amount, created_at
FROM orders WHERE user_id = 101;
-- 确认使用了新索引
-- 步骤 3：删除旧索引（也用 CONCURRENTLY）
DROP INDEX CONCURRENTLY idx_orders_user_status;
-- 步骤 4：重命名新索引（可选）
ALTER INDEX idx_orders_user_status_v2 RENAME TO idx_orders_user_status;

唯一索引和约束

-- CONCURRENTLY 也支持 UNIQUE 索引
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique
ON users (email);
-- 如果想把它转为正式的 UNIQUE 约束：
ALTER TABLE users
ADD CONSTRAINT users_email_unique
UNIQUE USING INDEX idx_users_email_unique;
-- 这个操作非常快（直接使用已有索引，只需短暂 ACCESS EXCLUSIVE 锁）

分区表上的索引

-- 对分区表主表创建索引，会自动对所有分区也创建索引
-- PostgreSQL 11+ 支持 CONCURRENTLY 对分区表
CREATE INDEX CONCURRENTLY idx_orders_all_created
ON orders_partitioned (created_at);
-- 如果分区很多，也可以单独对各分区创建索引
CREATE INDEX CONCURRENTLY idx_orders_2024_created
ON orders_2024 (created_at);
CREATE INDEX CONCURRENTLY idx_orders_2023_created
ON orders_2023 (created_at);

监控和自动化脚本

#!/bin/bash
# safe_create_index.sh — 安全创建索引并监控进度
INDEX_SQL="$1"  # 传入 CREATE INDEX CONCURRENTLY ... SQL
# 检查是否有 INVALID 索引（清理残留）
echo "检查现有 INVALID 索引..."
psql -c "
SELECT indexname FROM pg_indexes pi
JOIN pg_class pc ON pc.relname = pi.indexname
JOIN pg_index pix ON pix.indexrelid = pc.oid
WHERE NOT pix.indisvalid;
"
# 执行创建索引（后台运行）
echo "开始创建索引..."
psql -c "${INDEX_SQL}" &
INDEX_PID=$!
# 每 10 秒打印一次进度
while kill -0 $INDEX_PID 2>/dev/null; do
psql -c "
SELECT phase, blocks_done, blocks_total,
ROUND(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;
" 2>/dev/null
sleep 10
done
wait $INDEX_PID
EXIT_CODE=$?
if [ $EXIT_CODE -eq 0 ]; then
echo "✅ 索引创建成功"
else
echo "❌ 索引创建失败，检查 INVALID 索引"
fi

常见场景速查

-- 1. 给热表加复合索引（最常见）
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders (user_id, status)
WHERE status != 'deleted';  -- 部分索引，更小更快
-- 2. 给 JSONB 列加 GIN 索引
CREATE INDEX CONCURRENTLY idx_events_payload_gin
ON events USING GIN (payload jsonb_path_ops);
-- 3. 给经常 ORDER BY 的列加降序索引
CREATE INDEX CONCURRENTLY idx_posts_created_desc
ON posts (created_at DESC);
-- 4. 函数索引（不区分大小写搜索）
CREATE INDEX CONCURRENTLY idx_users_email_lower
ON users (LOWER(email));
-- 5. 覆盖索引（减少回表）
CREATE INDEX CONCURRENTLY idx_orders_user_covering
ON orders (user_id)
INCLUDE (status, total_amount, created_at);

下一节：Migration 版本管理——CREATE INDEX CONCURRENTLY 需要在 Migration 文件中特殊处理（disable_ddl_transaction）。这一节讲如何在 Rails/Django/Golang 的 Migration 系统中安全管理 PostgreSQL 的 Schema 变更历史。