PostgreSQL 例行索引重建指南

概述

索引重建是 PostgreSQL 数据库维护的重要组成部分。随着时间推移，频繁的数据更新、删除操作会导致索引产生空间碎片化和性能下降。例行索引重建通过 REINDEX 命令可以有效解决这些问题，恢复索引的最佳性能。

为什么需要索引重建

索引膨胀问题

在高频更新的数据库环境中，索引会面临以下问题：

B树索引的空间回收机制

INFO

PostgreSQL 中的 B树索引在删除数据后会回收完全为空的页面，但对于部分空闲的页面则无法有效回收，这就导致了空间碎片化问题。

索引膨胀的具体场景

场景一：大量数据删除后的碎片化

假设我们有一个用户活动记录表，定期删除旧数据：

sql

-- 创建测试表和索引
CREATE TABLE user_activities (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    activity_date DATE,
    activity_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建复合索引
CREATE INDEX idx_user_activities_date_type 
ON user_activities(user_id, activity_date, activity_type);

-- 插入大量测试数据（100万条记录）
INSERT INTO user_activities (user_id, activity_date, activity_type)
SELECT 
    (random() * 10000)::INTEGER,  -- 随机用户ID
    CURRENT_DATE - (random() * 365)::INTEGER,  -- 过去一年的随机日期
    CASE (random() * 3)::INTEGER 
        WHEN 0 THEN 'login'
        WHEN 1 THEN 'purchase'
        ELSE 'browse'
    END
FROM generate_series(1, 1000000);

监控索引大小变化

在删除数据前后检查索引大小：

sql

-- 查看索引大小的函数
CREATE OR REPLACE FUNCTION check_index_size(index_name TEXT)
RETURNS TABLE(
    index_name TEXT,
    size_mb NUMERIC,
    pages_count BIGINT,
    avg_leaf_density NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        $1::TEXT,
        pg_size_pretty(pg_relation_size($1))::TEXT::NUMERIC,
        (pg_relation_size($1) / 8192)::BIGINT,  -- 页面数量（8KB每页）
        NULL::NUMERIC  -- 简化版本，实际可通过 pageinspect 扩展获取
    ;
END;
$$ LANGUAGE plpgsql;

-- 删除前检查索引大小
SELECT * FROM check_index_size('idx_user_activities_date_type');

预期输出：

index_name                    | size_mb | pages_count
------------------------------|---------|------------
idx_user_activities_date_type |   45.2  |    5789

执行大量删除操作

sql

-- 删除6个月前的数据（约50%的数据）
DELETE FROM user_activities 
WHERE activity_date < CURRENT_DATE - INTERVAL '6 months';

-- 更新表统计信息
ANALYZE user_activities;

-- 再次检查索引大小
SELECT * FROM check_index_size('idx_user_activities_date_type');

删除后的输出：

index_name                    | size_mb | pages_count
------------------------------|---------|------------
idx_user_activities_date_type |   42.8  |    5478

WARNING

可以看到，虽然删除了约50%的数据，但索引大小只减少了很少，这就是典型的索引膨胀现象。

REINDEX 命令详解

基本语法和选项

sql

-- 重建单个索引
REINDEX INDEX index_name;

-- 重建表的所有索引
REINDEX TABLE table_name;

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

-- 并发重建索引（推荐）
REINDEX INDEX CONCURRENTLY index_name;

-- 重建时启用详细输出
REINDEX (VERBOSE) INDEX index_name;

并发重建 vs 常规重建对比

特性	常规 REINDEX	REINDEX CONCURRENTLY
锁类型	ACCESS EXCLUSIVE	SHARE UPDATE EXCLUSIVE
是否阻塞读操作	是	否
是否阻塞写操作	是	部分（INSERT/UPDATE/DELETE可正常进行）
执行时间	较快	较慢（约2-3倍时间）
内存使用	较少	较多
推荐场景	维护窗口期	生产环境在线重建

实际重建操作示例

sql

-- 方法1：常规重建（需要维护窗口）
BEGIN;
-- 在事务中重建，确保一致性
REINDEX INDEX idx_user_activities_date_type;
COMMIT;

-- 方法2：并发重建（推荐用于生产环境）
REINDEX INDEX CONCURRENTLY idx_user_activities_date_type;

-- 验证重建效果
SELECT * FROM check_index_size('idx_user_activities_date_type');

重建后的输出：

index_name                    | size_mb | pages_count
------------------------------|---------|------------
idx_user_activities_date_type |   22.1  |    2831

TIP

重建后索引大小显著减少，空间利用率得到提升，查询性能也会相应改善。

监控索引膨胀的方法

创建索引膨胀监控视图

sql

-- 创建索引膨胀监控视图
CREATE OR REPLACE VIEW index_bloat_analysis AS
WITH index_stats AS (
    SELECT 
        schemaname,
        tablename,
        indexname,
        idx_tup_read,    -- 索引读取次数
        idx_tup_fetch,   -- 通过索引获取的行数
        pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
        pg_relation_size(indexrelid) as index_size_bytes
    FROM pg_stat_user_indexes
)
SELECT 
    schemaname,
    tablename,
    indexname,
    index_size,
    idx_tup_read,
    idx_tup_fetch,
    CASE 
        WHEN idx_tup_read > 0 
        THEN round((idx_tup_fetch::numeric / idx_tup_read * 100), 2)
        ELSE 0 
    END as efficiency_ratio,
    CASE 
        WHEN idx_tup_read > 10000 AND 
             (idx_tup_fetch::numeric / idx_tup_read) < 0.95
        THEN '需要重建'
        WHEN index_size_bytes > 100 * 1024 * 1024  -- 100MB以上
        THEN '建议监控'
        ELSE '正常'
    END as recommendation
FROM index_stats
WHERE idx_tup_read > 0
ORDER BY index_size_bytes DESC;

-- 查看分析结果
SELECT * FROM index_bloat_analysis;

示例输出：

schemaname | tablename      | indexname                    | index_size | efficiency_ratio | recommendation
-----------|----------------|------------------------------|------------|------------------|---------------
public     | user_activities| idx_user_activities_date_type| 42 MB      | 87.32           | 需要重建
public     | orders         | idx_orders_customer_date     | 156 MB     | 96.78           | 建议监控
public     | products       | idx_products_category        | 23 MB      | 99.12           | 正常

自动化索引重建策略

创建索引重建存储过程

sql

-- 创建自动索引重建存储过程
CREATE OR REPLACE FUNCTION auto_reindex_maintenance(
    p_schema_name TEXT DEFAULT 'public',
    p_size_threshold_mb INTEGER DEFAULT 100,
    p_efficiency_threshold NUMERIC DEFAULT 0.90,
    p_dry_run BOOLEAN DEFAULT TRUE
) 
RETURNS TABLE(
    action_taken TEXT,
    index_name TEXT,
    table_name TEXT,
    old_size TEXT,
    new_size TEXT,
    execution_time INTERVAL
) AS $$
DECLARE
    r RECORD;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
    old_size_bytes BIGINT;
    new_size_bytes BIGINT;
BEGIN
    -- 循环处理需要重建的索引
    FOR r IN 
        SELECT 
            i.schemaname,
            i.tablename,
            i.indexname,
            pg_relation_size(i.indexrelid) as size_bytes,
            CASE 
                WHEN s.idx_tup_read > 0 
                THEN s.idx_tup_fetch::numeric / s.idx_tup_read
                ELSE 1 
            END as efficiency
        FROM pg_stat_user_indexes s
        JOIN pg_indexes i ON i.indexname = s.indexname
        WHERE i.schemaname = p_schema_name
        AND pg_relation_size(s.indexrelid) > p_size_threshold_mb * 1024 * 1024
        AND (
            CASE 
                WHEN s.idx_tup_read > 0 
                THEN s.idx_tup_fetch::numeric / s.idx_tup_read
                ELSE 1 
            END
        ) < p_efficiency_threshold
    LOOP
        start_time := clock_timestamp();
        old_size_bytes := r.size_bytes;
        
        IF NOT p_dry_run THEN
            -- 执行并发重建
            EXECUTE format('REINDEX INDEX CONCURRENTLY %I.%I', 
                          r.schemaname, r.indexname);
        END IF;
        
        end_time := clock_timestamp();
        
        -- 获取重建后的大小
        IF NOT p_dry_run THEN
            SELECT pg_relation_size(indexrelid) INTO new_size_bytes
            FROM pg_stat_user_indexes 
            WHERE schemaname = r.schemaname AND indexname = r.indexname;
        ELSE
            new_size_bytes := old_size_bytes;  -- 干运行模式
        END IF;
        
        -- 返回结果
        action_taken := CASE WHEN p_dry_run THEN 'DRY_RUN' ELSE 'REINDEXED' END;
        index_name := r.indexname;
        table_name := r.tablename;
        old_size := pg_size_pretty(old_size_bytes);
        new_size := pg_size_pretty(new_size_bytes);
        execution_time := end_time - start_time;
        
        RETURN NEXT;
    END LOOP;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- 执行干运行测试
SELECT * FROM auto_reindex_maintenance(
    p_schema_name := 'public',
    p_size_threshold_mb := 50,
    p_efficiency_threshold := 0.90,
    p_dry_run := TRUE
);

定期维护脚本

bash

#!/bin/bash
# reindex_maintenance.sh - PostgreSQL 索引维护脚本

# 数据库连接参数
DB_HOST="${DB_HOST:-localhost}"
DB_PORT="${DB_PORT:-5432}"
DB_NAME="${DB_NAME:-your_database}"
DB_USER="${DB_USER:-postgres}"

# 维护参数
SIZE_THRESHOLD=100  # MB
EFFICIENCY_THRESHOLD=0.90
LOG_FILE="/var/log/postgresql/reindex_maintenance.log"

# 记录日志函数
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# 执行索引维护
perform_maintenance() {
    log_message "开始索引维护检查..."
    
    # 执行维护存储过程
    psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" \
         -c "SELECT * FROM auto_reindex_maintenance(
                p_size_threshold_mb := $SIZE_THRESHOLD,
                p_efficiency_threshold := $EFFICIENCY_THRESHOLD,
                p_dry_run := FALSE
             );" \
         -t -A -F'|' >> "$LOG_FILE" 2>&1
    
    if [ $? -eq 0 ]; then
        log_message "索引维护完成"
    else
        log_message "索引维护失败，请检查日志"
        return 1
    fi
}

# 主执行逻辑
main() {
    log_message "启动 PostgreSQL 索引维护脚本"
    
    # 检查数据库连接
    if ! psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -c '\q' 2>/dev/null; then
        log_message "无法连接到数据库，请检查连接参数"
        exit 1
    fi
    
    # 执行维护
    perform_maintenance
    
    log_message "维护脚本执行完成"
}

# 运行主函数
main "$@"

性能影响和最佳实践

重建时间估算

不同大小索引的重建时间参考：

索引大小	常规重建时间	并发重建时间	推荐执行时间
< 100MB	10-30秒	30-90秒	任何时间
100MB-1GB	1-5分钟	3-15分钟	低峰期
1GB-10GB	5-30分钟	15-90分钟	维护窗口
> 10GB	30分钟+	90分钟+	计划维护

监控重建进度

sql

-- 监控当前正在执行的重建操作
SELECT 
    pid,
    usename,
    application_name,
    state,
    query_start,
    now() - query_start as duration,
    substring(query, 1, 100) as current_query
FROM pg_stat_activity 
WHERE query ILIKE '%reindex%' 
AND state = 'active';

-- 监控锁等待情况
SELECT 
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
    AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted AND blocking_locks.granted;

最佳实践建议

重建时机选择

低峰期执行：选择数据库访问量最小的时间段
分批执行：大型索引分批重建，避免长时间锁定
监控资源：确保有足够的磁盘空间和内存
备份策略：重建前确保有可用的备份

注意事项

并发重建限制：同时只能有一个 REINDEX CONCURRENTLY 操作
磁盘空间：重建过程中需要额外的磁盘空间
内存消耗：大索引重建会消耗大量内存
事务日志：重建操作会产生大量 WAL 日志

非B树索引的特殊考虑

GIN 索引重建

sql

-- 创建GIN索引示例（全文搜索）
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    tags TEXT[],
    search_vector tsvector
);

-- 创建GIN索引
CREATE INDEX idx_documents_search ON documents USING gin(search_vector);
CREATE INDEX idx_documents_tags ON documents USING gin(tags);

-- 监控GIN索引大小
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE indexname LIKE 'idx_documents_%';

-- GIN索引重建
REINDEX INDEX CONCURRENTLY idx_documents_search;

GIST 索引重建

sql

-- 创建GIST索引示例（地理位置）
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coordinates geometry(POINT, 4326)
);

-- 创建GIST索引
CREATE INDEX idx_locations_geo ON locations USING gist(coordinates);

-- 重建GIST索引
REINDEX INDEX CONCURRENTLY idx_locations_geo;

总结

例行索引重建是 PostgreSQL 数据库维护的重要环节。通过合理的监控策略和自动化工具，可以：

及时发现索引膨胀问题
选择合适的时机进行重建操作
最小化业务影响，保持系统稳定运行
提升查询性能，优化资源利用率

定期的索引维护不仅能够回收存储空间，更重要的是保持数据库的最佳性能状态，为业务系统提供稳定可靠的数据服务。

PostgreSQL 例行索引重建指南 ​

概述 ​

为什么需要索引重建 ​

索引膨胀问题 ​

B树索引的空间回收机制 ​

索引膨胀的具体场景 ​

场景一：大量数据删除后的碎片化 ​

监控索引大小变化 ​

执行大量删除操作 ​

REINDEX 命令详解 ​

基本语法和选项 ​

并发重建 vs 常规重建对比 ​

实际重建操作示例 ​

监控索引膨胀的方法 ​

创建索引膨胀监控视图 ​

自动化索引重建策略 ​

创建索引重建存储过程 ​

定期维护脚本 ​

性能影响和最佳实践 ​

重建时间估算 ​

监控重建进度 ​

最佳实践建议 ​

非B树索引的特殊考虑 ​

GIN 索引重建 ​

GIST 索引重建 ​

总结 ​