PostgreSQL 在线创建索引

PostgreSQL 在线创建索引

PostgreSQL 提供了在线创建索引的能力，可以在不阻塞或最小化阻塞生产环境操作的情况下创建索引。以下是几种主要的在线创建索引方法：

1 使用 CONCURRENTLY 选项（最常用方法）

这是 PostgreSQL 提供的标准在线创建索引方式，允许在索引创建期间表仍然可以读写。

基本语法

CREATE INDEX CONCURRENTLY index_name ON table_name(column_name);

特点

• 不会获取表的排他锁
• 创建过程分为两个阶段：
  1. 第一次表扫描：创建索引结构
  2. 第二次表扫描：捕获第一次扫描后变更的数据
• 比普通索引创建慢约2-3倍
• 可能失败但不会破坏表

示例

-- 在大型用户表上在线创建索引
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- 创建多列索引
CREATE INDEX CONCURRENTLY idx_orders_date_status ON orders(order_date, status);

2 结合并行创建（PostgreSQL 11+）

可以同时使用 CONCURRENTLY 和并行选项来加速大型索引的创建：

CREATE INDEX CONCURRENTLY idx_large_table_column ON large_table(column)
WITH (parallel_workers = 4);

3 在线创建索引的注意事项

优点

• 几乎不影响生产环境的读写操作
• 不需要维护窗口
• 特别适合7×24小时运行的系统

限制

• 不能用于唯一索引（CONCURRENTLY 不能保证唯一性）
• 不能用于表达式索引的某些复杂情况
• 不能与 CREATE TABLE … INCLUDING INDEXES 一起使用
• 创建过程中如果失败会留下"invalid"索引，需要手动清理

失败处理

-- 检查无效索引
SELECT * FROM pg_indexes WHERE schemaname = 'public' AND indexname LIKE '%invalid%';

-- 删除无效索引
DROP INDEX CONCURRENTLY IF EXISTS index_name;

4 替代方案：部分索引

对于只需要索引部分数据的场景，可以创建部分索引减少影响：

CREATE INDEX CONCURRENTLY idx_active_users ON users(email) 
WHERE status = 'active';

5 监控索引创建进度

PostgreSQL 提供了索引创建进度的监控视图：

-- 查看所有正在创建的索引进度
SELECT * FROM pg_stat_progress_create_index;

-- 详细监控查询
SELECT 
  p.pid, p.datname, p.usename, p.client_addr,
  p.query_start, now() - p.query_start AS duration,
  i.schemaname, i.indexrelname, i.command,
  i.phase, i.blocks_total, i.blocks_done,
  round(100 * i.blocks_done / NULLIF(i.blocks_total, 0), 2) AS progress_pct
FROM pg_stat_progress_create_index i
JOIN pg_stat_activity p ON i.pid = p.pid;

6 最佳实践

1. 选择合适的时间：

   • 即使在线创建，也建议在业务低峰期进行
   • 大型表索引创建仍会消耗较多资源

2. 资源准备：

   -- 临时增加维护工作内存
   SET maintenance_work_mem = '1GB';
   
   -- 对于特别大的表
   SET maintenance_work_mem = '4GB';
   

3. 验证索引：

   -- 创建后验证索引是否有效
   SELECT indisvalid FROM pg_index WHERE indexrelid = 'index_name'::regclass;
   
   -- 如果为f，需要重建
   REINDEX INDEX CONCURRENTLY index_name;
   

4. 对于超大表的策略：

   • 考虑分批处理（如按分区创建）
   • 使用pg_repack等工具重组表后创建

通过合理使用这些方法，PostgreSQL DBA可以在不影响生产系统的情况下安全地创建所需索引。

更详细的内容请查看官方文档：

https://www.postgresql.org/docs/17/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

谨记：心存敬畏，行有所止。