PostgreSQL 600万行processed表：索引、分区及性能优化咨询

一、索引策略选择 #

首先注意到表中已存在word字段的唯一约束uk_tb03fca6mojpw7wogvaqvwprw，PostgreSQL会自动为该约束创建唯一索引，足以支撑单字段word的精确查询。

是否需要多字段组合索引，完全取决于你的业务查询模式：

• 如果大部分查询仅基于word单字段（如SELECT * FROM processed WHERE word = 'example'），现有唯一索引已足够，无需额外添加多字段索引——盲目加组合索引会增加写入（INSERT/UPDATE/DELETE）时的索引维护开销，反而拖慢性能。
• 如果存在固定的组合查询场景（如频繁执行SELECT score, is_domain_available FROM processed WHERE word = 'example' AND is_domain_available = true），可以针对性创建组合索引。注意组合索引的字段顺序：将选择性最高的字段（这里word是唯一值，选择性最高）放在最前面，比如：

  CREATE INDEX idx_processed_word_available ON processed (word, is_domain_available);
  

• 若查询只需返回部分字段，可创建覆盖索引避免回表，进一步提升效率：

  CREATE INDEX idx_processed_word_includes ON processed (word) INCLUDE (score, is_domain_available);
  

二、分区方案推荐 #

600万行数据在PostgreSQL中属于中等规模，若当前性能可满足需求，可暂不分区；若后续数据持续增长（预计突破千万级），或查询存在明显的过滤规律，推荐以下两种分区方式：

1. 范围分区（按created_at） #

如果数据是按时间递增写入，且查询经常按时间范围过滤（如“查询近30天的数据”“归档去年的历史数据”），范围分区是最优选择：

• 可以将数据按时间切分为多个分区（如按月份、季度），查询时仅扫描目标时间范围的分区，大幅减少IO开销；
• 旧数据分区可单独迁移到存储成本更低的介质，或直接删除，运维更灵活。

创建示例（按月份分区）：

-- 先删除原表的主键约束（分区表要求主键包含分区键）
ALTER TABLE processed DROP CONSTRAINT processed_pkey;
-- 创建分区父表
CREATE TABLE processed_partitioned (
    id bigint NOT NULL,
    created_at timestamp without time zone,
    word character varying(200) COLLATE pg_catalog."default",
    score double precision,
    updated_at timestamp without time zone,
    is_domain_available boolean,
    CONSTRAINT processed_partitioned_pkey PRIMARY KEY (id, created_at),
    CONSTRAINT uk_processed_partitioned_word UNIQUE (word)
) PARTITION BY RANGE (created_at);

-- 创建具体分区（示例：2024年1月分区）
CREATE TABLE processed_202401 PARTITION OF processed_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

2. 哈希分区（按word） #

如果查询多为基于word的随机查询，无明显时间规律，哈希分区可将数据均匀分布到多个分区，降低单个分区的大小，提升查询和写入的并行性：

• 哈希分区会根据word的哈希值将数据分配到指定数量的分区，每个分区数据量大致均衡；
• 适合高并发的随机查询场景，能利用PostgreSQL的并行查询能力提升效率。

创建示例（分为4个分区）：

-- 删除原主键约束
ALTER TABLE processed DROP CONSTRAINT processed_pkey;
-- 创建分区父表
CREATE TABLE processed_partitioned (
    id bigint NOT NULL,
    created_at timestamp without time zone,
    word character varying(200) COLLATE pg_catalog."default",
    score double precision,
    updated_at timestamp without time zone,
    is_domain_available boolean,
    CONSTRAINT processed_partitioned_pkey PRIMARY KEY (id, word),
    CONSTRAINT uk_processed_partitioned_word UNIQUE (word)
) PARTITION BY HASH (word);

-- 创建4个哈希分区
CREATE TABLE processed_hash_0 PARTITION OF processed_partitioned FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE processed_hash_1 PARTITION OF processed_partitioned FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE processed_hash_2 PARTITION OF processed_partitioned FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE processed_hash_3 PARTITION OF processed_partitioned FOR VALUES WITH (MODULUS 4, REMAINDER 3);

三、其他性能优化手段（含压缩） #

1. 表级压缩 #

PostgreSQL支持多种压缩算法，适合读写比低、数据重复率高的场景（如is_domain_available这类布尔字段重复率极高，压缩效果显著）：

• 对现有表开启压缩（需执行VACUUM FULL生效，注意锁表，建议低峰期操作）：

  ALTER TABLE processed SET (compression = 'pg_zlib'); -- 高压缩比，适合归档数据
  -- 或使用pg_lz4（更快的压缩速度，适合读写较频繁的场景）
  -- ALTER TABLE processed SET (compression = 'pg_lz4');
  VACUUM FULL processed;
  

• 新建表时直接指定压缩：

  CREATE TABLE IF NOT EXISTS public.processed
  (
      -- 表结构省略
  ) WITH (compression = 'pg_zlib');
  

2. 定期维护 #

• 执行VACUUM ANALYZE processed：定期清理死元组，更新表的统计信息，让查询优化器生成更高效的执行计划；
• 对于大表，可考虑使用VACUUM FULL整理表碎片，但需注意该操作会锁表，必须在业务低峰期执行。

3. 配置参数调优 #

• 提高work_mem：增大排序、哈希操作的内存阈值，减少磁盘临时文件的使用（如SET work_mem = '64MB'，需根据服务器内存调整）；
• 调整maintenance_work_mem：提升索引创建、真空操作的效率（如SET maintenance_work_mem = '256MB'）；
• 开启并行查询：设置max_parallel_workers_per_gather = 4（根据CPU核心数调整），让复杂查询利用多核并行执行。

4. 查询优化 #

• 避免使用SELECT *：仅查询需要的字段，减少数据传输和IO开销；
• 对复杂查询执行EXPLAIN ANALYZE，分析执行计划，针对性优化索引或SQL语句。

内容的提问来源于stack exchange，提问作者Peter Penzov