PostgreSQL 12副本如何像MySQL一样创建额外索引？

首先明确核心限制：

PostgreSQL 默认的物理流复制备节点是完全只读、和主库块级对齐的，所有存储结构变更只能回放自主库产生的WAL日志，因此无法在物理备节点上独立创建主库不存在的索引，这一点和MySQL的副本逻辑有本质区别。

如果要实现和MySQL类似的、在副本侧独立建索引不增加主库开销的需求，可以选择以下三种方案：

方案1：使用逻辑复制备节点（最适配业务读优化场景） #

• PostgreSQL 12原生支持逻辑复制，逻辑备节点不需要和主库保持块级一致，仅回放数据层面的增删改操作，允许你在备节点独立创建额外索引、自定义约束甚至新增业务需要的冗余字段
• 操作流程：
  1. 调整主库参数wal_level = logical，重启主库生效
  2. 在主库创建全表发布：CREATE PUBLICATION pub_full FOR ALL TABLES;
  3. 在备节点创建订阅，同步全量+增量数据：CREATE SUBSCRIPTION sub_full CONNECTION 'dbname=你的库名 host=主库IP port=主库端口 user=复制账号 password=复制密码' PUBLICATION pub_full;
  4. 待数据同步完成后，直接在逻辑备节点执行CREATE INDEX语句创建需要的索引即可，这些索引只会存在于备节点，不会同步到主库，也不会增加主库的写入开销
• 注意事项：逻辑复制默认不会同步主库的DDL操作，主库的表结构变更需要你手动在备节点同步适配

方案2：物理备节点断流升主（仅适用离线分析场景） #

• 如果你只需要对某一时刻的快照数据做批量查询，不需要后续增量同步，可以直接将物理备节点提升为独立主节点，断开和原主库的复制关系，之后就可以自由创建索引
• 缺点是提升后的节点不再和原主库保持数据同步，仅适合对数据实时性要求极低的离线分析场景

方案3：商业发行版兼容 #

• 部分PostgreSQL商业发行版原生支持在物理只读副本上创建独立索引，比如EDB Postgres Advanced Server，不需要修改复制架构，不过需要支付商业授权费用。

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