PostgreSQL 索引维护

索引维护是PostgreSQL数据库运维的重要组成部分，直接影响数据库的查询性能和写入性能。随着数据的不断插入、更新和删除，索引会产生膨胀、碎片化等问题，需要定期进行维护。本文将详细介绍索引维护的各个方面，包括索引监控、索引膨胀处理、索引重建、索引优化等内容。

索引维护的重要性

索引维护的必要性

索引膨胀：频繁的更新和删除操作会导致索引产生空洞，占用额外的磁盘空间
性能下降：膨胀的索引会增加I/O开销，降低查询性能
写入性能影响：维护膨胀的索引需要更多的资源，影响写入性能
统计信息不准确：过时的统计信息会导致查询优化器选择低效的执行计划
锁竞争：严重膨胀的索引在维护时会产生更长时间的锁竞争

索引维护的目标

保持索引高效：确保索引能够快速定位数据
控制索引大小：避免索引过度膨胀，节省磁盘空间
优化写入性能：减少索引维护对写入操作的影响
提高查询性能：确保查询优化器能够选择最优的执行计划
降低维护成本：减少索引维护所需的时间和资源

索引监控

1. 索引使用情况监控

定期监控索引的使用情况，识别未使用或使用频率低的索引，及时清理不必要的索引。

sql

-- 查看索引使用情况
SELECT 
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS index_scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan * 100.0 / NULLIF(total_scans, 0) AS index_scan_ratio
FROM (
  SELECT 
    s.schemaname,
    s.relname,
    s.indexrelname,
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch,
    s.indexrelid,
    (s.idx_scan + t.seq_scan) AS total_scans
  FROM pg_stat_user_indexes s
  JOIN pg_stat_user_tables t ON s.schemaname = t.schemaname AND s.relname = t.relname
) AS subquery
ORDER BY index_scans ASC;

-- 查找从未使用的索引
SELECT 
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
  AND NOT indisunique  -- 排除唯一索引
ORDER BY pg_relation_size(indexrelid) DESC;

2. 索引大小监控

监控索引大小的变化，识别异常增长的索引，及时进行维护。

sql

-- 查看索引大小
SELECT 
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  pg_size_pretty(pg_relation_size(indrelid)) AS table_size,
  round(pg_relation_size(indexrelid) * 100.0 / pg_relation_size(indrelid), 2) AS index_to_table_ratio
FROM pg_stat_user_indexes 
ORDER BY pg_relation_size(indexrelid) DESC;

-- 监控索引大小变化（需要pg_stat_monitor扩展）
-- 注意：pg_stat_monitor是PostgreSQL 13+的扩展，需要单独安装

3. 索引膨胀监控

使用pgstattuple或pg_relation_size等方法监控索引膨胀情况。

sql

-- 使用pgstattuple监控索引膨胀
SELECT 
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  round(stat_size / 1024.0 / 1024.0, 2) AS stat_mb,
  round(real_size / 1024.0 / 1024.0, 2) AS real_mb,
  round((stat_size - real_size) / 1024.0 / 1024.0, 2) AS bloat_mb,
  round((stat_size - real_size) * 100.0 / stat_size, 2) AS bloat_ratio
FROM pg_stat_user_indexes i
JOIN LATERAL pgstatindex(i.indexrelid) s ON true
ORDER BY bloat_ratio DESC;

-- 估算索引膨胀（不使用扩展）
SELECT 
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  pg_size_pretty( 
    (SELECT SUM(pg_column_size(index_col)) FROM (
      SELECT DISTINCT ON (index_col) index_col 
      FROM (SELECT (col1, col2) AS index_col FROM table_name) AS t
    ) AS subquery 
  )) AS estimated_data_size,
  -- 注意：上述查询需要根据实际索引列调整
  round(
    (pg_relation_size(indexrelid) - 
     (SELECT SUM(pg_column_size(index_col)) FROM (
       SELECT DISTINCT ON (index_col) index_col 
       FROM (SELECT (col1, col2) AS index_col FROM table_name) AS t
     ) AS subquery 
    )) * 100.0 / pg_relation_size(indexrelid), 2
  ) AS estimated_bloat_ratio
FROM pg_stat_user_indexes 
WHERE schemaname = 'public' AND relname = 'table_name';

4. 版本特定的监控视图

PostgreSQL 13+：pg_stat_progress_create_index

sql

-- 查看正在创建的索引进度
SELECT 
  pid,
  datname,
  relid::regclass AS relation,
  indexrelid::regclass AS index,
  command,
  phase,
  lockers_total,
  lockers_done,
  current_locker_pid,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done,
  partitions_total,
  partitions_done
FROM pg_stat_progress_create_index;

PostgreSQL 14+：增强的pg_stat_user_indexes

sql

-- 查看索引的维护统计信息
SELECT 
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  idx_insert_count,
  idx_delete_count,
  idx_update_count,
  idx_recent_bloom_filters,
  idx_bloom_filter_usages,
  idx_bloom_filter_hits
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

索引膨胀处理

1. 索引膨胀的原因

频繁的UPDATE/DELETE操作：产生大量的索引项标记为删除
长时间运行的事务：阻止VACUUM清理索引中的无效项
VACUUM配置不当：autovacuum参数设置不合理，导致索引清理不及时
索引设计问题：不合适的索引类型或索引列选择

2. 索引膨胀的危害

查询性能下降：索引扫描需要读取更多的页面
磁盘空间浪费：膨胀的索引占用额外的磁盘空间
维护成本增加：索引维护需要更多的时间和资源
写入性能影响：更新膨胀的索引需要更多的I/O操作

3. 索引膨胀的处理方法

方法1：REINDEX

REINDEX是处理索引膨胀最常用的方法，它会重建索引，消除索引膨胀。

sql

-- 重建单个索引
REINDEX INDEX public.idx_users_email;

-- 重建表的所有索引
REINDEX TABLE public.users;

-- 在线重建索引（PostgreSQL 12+），允许并发读写
REINDEX INDEX CONCURRENTLY public.idx_users_email;

-- 重建数据库的所有索引
REINDEX DATABASE mydb;

-- 重建系统索引
REINDEX SYSTEM mydb;

REINDEX CONCURRENTLY的优点：

允许并发读写操作
不会阻塞表的正常访问
适合生产环境使用

REINDEX CONCURRENTLY的注意事项：

执行时间较长
需要额外的磁盘空间
可能会产生临时索引
需要执行两次全表扫描

方法2：DROP + CREATE索引

对于不支持REINDEX CONCURRENTLY的旧版本，可以使用DROP + CREATE索引的方式，但会产生表级锁。

sql

-- 删除并重建索引（会阻塞写入操作）
DROP INDEX public.idx_users_email;
CREATE INDEX public.idx_users_email ON public.users(email);

-- 对于唯一索引，需要先删除约束
ALTER TABLE public.users DROP CONSTRAINT users_pkey;
ALTER TABLE public.users ADD PRIMARY KEY (id);

方法3：CLUSTER

CLUSTER命令会根据指定的索引对表进行重新排序，同时也会重建索引，消除索引膨胀。

sql

-- 按索引对表进行重新排序，同时重建索引
CLUSTER public.users USING public.idx_users_email;

-- 更新统计信息
ANALYZE public.users;

CLUSTER的注意事项：

会持有表级排他锁，阻塞所有读写操作
需要大量的磁盘空间
执行时间较长
适合在业务低峰期使用

索引重建策略

1. 定期重建计划

根据索引的使用情况和膨胀程度，制定合理的索引重建计划：

索引类型	建议重建频率	重建方式
B-tree	每3-6个月	REINDEX CONCURRENTLY
GIN	每1-3个月	REINDEX CONCURRENTLY
GiST	每2-4个月	REINDEX CONCURRENTLY
BRIN	每6-12个月	REINDEX
Hash	每6-12个月	REINDEX

2. 基于条件的重建

根据索引的膨胀程度和使用情况，制定基于条件的重建策略：

sql

-- 生成需要重建的索引列表（膨胀率>50%或大小>10GB）
SELECT 
  format('REINDEX INDEX CONCURRENTLY %I.%I;', schemaname, indexrelname) AS reindex_command
FROM pg_stat_user_indexes i
JOIN LATERAL pgstatindex(i.indexrelid) s ON true
WHERE (s.stat_size - s.real_size) * 100.0 / s.stat_size > 50 
   OR pg_relation_size(i.indexrelid) > 10 * 1024 * 1024 * 1024
ORDER BY (s.stat_size - s.real_size) * 100.0 / s.stat_size DESC;

3. 在线重建索引的最佳实践

使用REINDEX CONCURRENTLY

sql

-- 在线重建索引的完整流程

-- 1. 创建新索引
CREATE INDEX CONCURRENTLY public.idx_users_email_new ON public.users(email);

-- 2. 验证新索引是否有效
SELECT 
  indexrelname, 
  idx_scan 
FROM pg_stat_user_indexes 
WHERE relname = 'users' AND indexrelname = 'idx_users_email_new';

-- 3. 删除旧索引
DROP INDEX CONCURRENTLY public.idx_users_email;

-- 4. 重命名新索引
ALTER INDEX public.idx_users_email_new RENAME TO idx_users_email;

-- 5. 更新统计信息
ANALYZE public.users;

监控REINDEX进度

sql

-- 查看正在运行的REINDEX进度（PostgreSQL 13+）
SELECT 
  pid,
  datname,
  query,
  now() - query_start AS duration,
  state
FROM pg_stat_activity 
WHERE query LIKE '%REINDEX%' AND state = 'active';

4. 版本特定的重建特性

PostgreSQL 14+：REINDEX TABLE CONCURRENTLY

sql

-- 在线重建表的所有索引
REINDEX TABLE CONCURRENTLY public.users;

PostgreSQL 15+：增强的REINDEX性能

PostgreSQL 15改进了REINDEX的性能，特别是对于大型索引的重建。

索引优化

1. 移除未使用的索引

定期清理未使用或使用频率低的索引，减少索引维护成本。

sql

-- 查找30天内未使用的索引
SELECT 
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS index_scans,
  now() - stats_reset AS stats_age
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
  AND NOT indisunique
  AND stats_reset < now() - interval '30 days'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 生成删除未使用索引的脚本
SELECT 
  format('DROP INDEX CONCURRENTLY %I.%I;', schemaname, indexrelname) AS drop_command
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
  AND NOT indisunique
  AND stats_reset < now() - interval '30 days'
  AND pg_relation_size(indexrelid) > 1024 * 1024 * 10; -- 仅删除大于10MB的索引

2. 合并重复或冗余索引

识别并合并重复或冗余的索引，减少索引维护成本。

sql

-- 查找可能的重复或冗余索引
SELECT 
  i1.schemaname,
  i1.relname AS table_name,
  i1.indexrelname AS index1_name,
  i2.indexrelname AS index2_name,
  i1.indexdef AS index1_def,
  i2.indexdef AS index2_def,
  pg_size_pretty(pg_relation_size(i1.indexrelid)) AS index1_size,
  pg_size_pretty(pg_relation_size(i2.indexrelid)) AS index2_size
FROM pg_stat_user_indexes i1
JOIN pg_stat_user_indexes i2 ON i1.schemaname = i2.schemaname AND i1.relname = i2.relname
WHERE i1.indexrelid < i2.indexrelid
  AND (i1.indexdef LIKE i2.indexdef || '%' OR i2.indexdef LIKE i1.indexdef || '%')
ORDER BY pg_relation_size(i1.indexrelid) + pg_relation_size(i2.indexrelid) DESC;

3. 优化索引设计

根据查询模式和数据分布，优化索引设计，提高索引效率。

sql

-- 查看查询执行计划，分析索引使用情况
EXPLAIN ANALYZE 
SELECT * FROM public.users WHERE email = 'john.doe@example.com';

-- 查看慢查询日志，识别需要优化的查询
-- 配置慢查询日志
ALTER SYSTEM SET log_min_duration_statement = '100ms';
SELECT pg_reload_conf();

4. 调整索引填充因子

根据表的更新频率，调整索引的填充因子，减少索引膨胀。

sql

-- 查看当前填充因子设置
SELECT 
  relname,
  reloptions
FROM pg_class 
WHERE relkind = 'i' AND relname LIKE 'idx_%';

-- 创建索引时设置填充因子
CREATE INDEX idx_users_email ON public.users(email) WITH (fillfactor = 70);

-- 修改现有索引的填充因子
ALTER INDEX public.idx_users_email SET (fillfactor = 70);
-- 需要重建索引才能生效
REINDEX INDEX CONCURRENTLY public.idx_users_email;

填充因子建议值：

静态表（很少更新）：90-100
动态表（频繁更新）：70-80
频繁插入的表：80-90

索引维护的最佳实践

1. 选择合适的维护时间

业务低峰期：选择系统负载低的时间段进行索引维护
维护窗口：为索引维护预留足够的时间窗口
分批处理：将大型索引维护任务拆分为多个小任务，分散系统负载

2. 监控维护过程

实时监控：在执行索引维护任务时，实时监控系统负载、I/O使用率、CPU使用率等指标
日志记录：记录索引维护的开始时间、结束时间、执行结果等信息
性能对比：维护前后对比查询性能，验证维护效果

3. 备份与回滚

备份：在执行重要的索引维护任务前，确保有最新的数据库备份
回滚计划：制定详细的回滚计划，以便在出现问题时快速恢复
测试环境验证：在测试环境验证索引维护脚本的正确性和性能影响

4. 自动化维护

使用自动化工具或脚本进行索引维护，提高维护效率和可靠性。

bash

#!/bin/bash

# 索引维护自动化脚本

DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="mydb"
DB_USER="postgres"
LOG_FILE="/var/log/postgresql/index_maintenance.log"

# 记录日志
log() {
  echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
}

log "开始执行索引维护任务"

# 生成需要重建的索引列表
INDEXES=$(psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c "
SELECT 
  format('REINDEX INDEX CONCURRENTLY %I.%I;', schemaname, indexrelname)
FROM pg_stat_user_indexes i
JOIN LATERAL pgstatindex(i.indexrelid) s ON true
WHERE (s.stat_size - s.real_size) * 100.0 / s.stat_size > 50 
   AND pg_relation_size(i.indexrelid) > 1024 * 1024 * 10
ORDER BY (s.stat_size - s.real_size) * 100.0 / s.stat_size DESC;
")

# 执行索引重建
if [ -n "$INDEXES" ]; then
  log "需要重建的索引数量: $(echo "$INDEXES" | wc -l)"
  echo "$INDEXES" | psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -U $DB_USER
  log "索引重建完成"
else
  log "没有需要重建的索引"
fi

# 清理未使用的索引
UNUSED_INDEXES=$(psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c "
SELECT 
  format('DROP INDEX CONCURRENTLY %I.%I;', schemaname, indexrelname)
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
  AND NOT indisunique
  AND stats_reset < now() - interval '60 days'
  AND pg_relation_size(indexrelid) > 1024 * 1024 * 50;
")

if [ -n "$UNUSED_INDEXES" ]; then
  log "需要清理的未使用索引数量: $(echo "$UNUSED_INDEXES" | wc -l)"
  echo "$UNUSED_INDEXES" | psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -U $DB_USER
  log "未使用索引清理完成"
else
  log "没有需要清理的未使用索引"
fi

log "索引维护任务执行完毕"

生产环境案例分析

案例1：电商系统订单表索引维护

问题：订单表的索引膨胀率超过150%，查询性能下降，写入延迟增加。

分析过程：

使用pgstatindex检测索引膨胀，发现多个索引的膨胀率超过150%
查看索引使用情况，发现所有索引都被频繁使用
分析表的更新模式，发现订单表存在大量的UPDATE操作

解决方案：

调整索引的填充因子从默认的90降低到70
使用REINDEX CONCURRENTLY在线重建所有膨胀的索引
优化VACUUM配置，提高autovacuum的清理频率
实施定期索引维护计划，每3个月重建一次索引

效果：

索引大小减少了60%
查询响应时间缩短了40%
写入延迟降低了30%
磁盘空间节省了约50GB

案例2：社交媒体用户表索引优化

问题：用户表有20多个索引，导致写入性能差，维护成本高。

分析过程：

查看索引使用情况，发现有8个索引在过去30天内从未被使用
识别出3组重复或冗余的索引
分析查询模式，发现部分索引设计不合理

解决方案：

删除8个未使用的索引
合并3组重复或冗余的索引
优化2个设计不合理的索引
实施索引监控机制，定期清理不必要的索引

效果：

索引数量从22个减少到11个
写入性能提高了50%
索引维护时间减少了60%
磁盘空间节省了约20GB

版本差异注意事项

PostgreSQL 12+：引入了REINDEX CONCURRENTLY，支持在线重建索引
PostgreSQL 13+：增强了索引监控视图，提供了更多的索引维护统计信息
PostgreSQL 14+：支持REINDEX TABLE CONCURRENTLY，简化了表级索引重建
PostgreSQL 15+：改进了REINDEX的性能，特别是对于大型索引
PostgreSQL 16+：增强了索引的并行构建能力，提高了索引创建和重建的速度

总结

索引维护是PostgreSQL数据库运维的重要组成部分，直接影响数据库的性能和稳定性。通过定期监控索引使用情况、及时处理索引膨胀、合理重建索引、优化索引设计等措施，可以保持索引的高效运行，提高数据库的整体性能。

在实际运维中，DBA应该根据数据库的实际情况，制定个性化的索引维护策略，结合自动化工具和脚本，提高维护效率和可靠性。同时，要关注PostgreSQL版本的新特性，利用新版本提供的增强功能，不断优化索引维护流程。

有效的索引维护可以：

提高查询性能
优化写入性能
节省磁盘空间
降低维护成本
提高系统稳定性

通过持续的索引维护，可以确保PostgreSQL数据库在生产环境中始终保持高效运行，为业务提供可靠的支持。

PostgreSQL 索引维护 ​

索引维护的重要性 ​

索引维护的必要性 ​

索引维护的目标 ​

索引监控 ​

1. 索引使用情况监控 ​

2. 索引大小监控 ​

3. 索引膨胀监控 ​

4. 版本特定的监控视图 ​

PostgreSQL 13+：pg_stat_progress_create_index ​

PostgreSQL 14+：增强的pg_stat_user_indexes ​

索引膨胀处理 ​

1. 索引膨胀的原因 ​

2. 索引膨胀的危害 ​

3. 索引膨胀的处理方法 ​

方法1：REINDEX ​

方法2：DROP + CREATE索引 ​

方法3：CLUSTER ​

索引重建策略 ​

1. 定期重建计划 ​

2. 基于条件的重建 ​

3. 在线重建索引的最佳实践 ​

使用REINDEX CONCURRENTLY ​

监控REINDEX进度 ​

4. 版本特定的重建特性 ​

PostgreSQL 14+：REINDEX TABLE CONCURRENTLY ​

PostgreSQL 15+：增强的REINDEX性能 ​

索引优化 ​

1. 移除未使用的索引 ​

2. 合并重复或冗余索引 ​

3. 优化索引设计 ​

4. 调整索引填充因子 ​

索引维护的最佳实践 ​

1. 选择合适的维护时间 ​

2. 监控维护过程 ​

3. 备份与回滚 ​

4. 自动化维护 ​

生产环境案例分析 ​

案例1：电商系统订单表索引维护 ​

案例2：社交媒体用户表索引优化 ​

版本差异注意事项 ​

总结 ​