PostgreSQL 只读从库上创建索引

说明

以下操作没有特殊说明均在从库中执行。

会话级别复制角色

DEFAULT：标准行为，所有写操作记录到WAL日志中并复制到从库。
REPLICA：在从库中使用，禁止普通事务中的写操作，但DDL操作可以通过。
ORIGIN：使用于触发器，处理数据变更事件

show session_replication_role;

允许临时写操作

CREATE INDEX CONCURRENTLY允许索引在不锁定表的情况下创建，从而不会影响读取操作。

SET session_replication_role = 'replica';
CREATE INDEX CONCURRENTLY your_index_name ON your_table_name (your_column_name);
SET session_replication_role = 'origin';

原理

利用了Replication Role的机制在从库上创建了索引，不会在WAL（Write-Ahead Logging）日志中记录这项变更。

查看主从状态

在主库上执行

SELECT application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn 
FROM pg_stat_replication;

在从库上执行

SELECT pg_last_wal_replay_lsn();

删除索引

可以包裹在事务中。

注意：CREATE INDEX CONCURRENTLY不能包裹在事务中。

BEGIN;

-- 临时禁用只读限制
SET session_replication_role = 'replica';

-- 删除索引
DROP INDEX your_index_name;

-- 恢复默认设置
SET session_replication_role = 'origin';

COMMIT;

更多文章

PostgreSQL 16 编译安装：https://www.zhangbj.com/p/1699.html

PostgreSQL 搭建主从同步实现读写分离：https://www.zhangbj.com/p/1795.html

PostgreSQL 搭建级联从库：https://www.zhangbj.com/p/1796.html

PostgreSQL 参数调整作为 OLAP 统计数据库：https://www.zhangbj.com/p/1797.html