背景

1、产品的问题点

• PG 只读实例不支持写操作

2、问题点背后涉及的技术原理

• PG 通过流复制支持物理的standby节点, standby节点通过接收主库wal日志, 实时回放block增量, 实现同步.
• 由于PG主库和从库物理一致, 所以这个standby节点是只读的, 不支持写操作. 例如不支持创建临时表, 不支持写unlogged table. 不支持写数据等.

3、这个问题将影响哪些行业以及业务场景

• 分析业务场景

4、会导致什么问题?

• 分析业务通常计算量和IO的消耗量都很大, 所以一般会把分析逻辑放在standby库执行, 但是分析逻辑通常比较复杂, 例如把逻辑封装到函数中, 计算会有中间结果产生, 用中间结果再进行后续计算.
• 因为standby库不支持写操作(包括写临时表), 使得中间结果没地方存放, 导致业务不得不放弃使用standby进行带中间结果的复杂逻辑分析.

5、业务上应该如何避免这个坑

• 不将计算逻辑放到函数中, 而是通过业务的SQL调用来完成, 并且将中间结果写入到主库, 主库再同步到只读库, 同步完再进行下一步操作
• 使用数组代替临时表存储中间结果.

6、业务上避免这个坑牺牲了什么, 会引入什么新的问题

• 中间结果数据写入主库, 再同步有几个问题

• • 浪费主库存储资源
  • 浪费了3次传输中间结果的网络资源(读取、写入、同步)
  • 同步有延迟, 使得整个分析过程变长

• 使用数组代替临时表的方案也有问题, 每个数组有1GB上限, 而且逻辑变得更复杂, 不适合新手, 门槛较高.

7、数据库未来产品迭代如何修复这个坑

• 希望只读实例能支持临时表的定义和写入, 可以考虑临时表的定义不放在pg_catalog, 如果要放在pg_catalog也可以(这个可以在主库操作, 同步到只读库, 元数据定义的量还是比较小的, 不过还要考虑统计信息等, 也会导致上下不一致, 可以考虑放弃stat或者使用本地化stat)
• 希望最终统计结果可以写主库(也就是说写入操作自动路由到主库操作, 不需要通过业务导一次. 例如fdw借口?)
• 《PostgreSQL readonly standby 只读库如何写数据? - DDL DML 操作透明传输到主库》