如何快速读取PostgreSQL只读副本所有数据块以适配RDS蓝绿部署？

针对Amazon RDS蓝绿部署的懒加载加速需求，无需通过网络传输大量数据，可利用PostgreSQL内置的pg_prewarm函数在只读副本服务器端直接加载所有表和索引的数据块到共享缓存，完全规避本地网络带宽限制，且符合只读副本的操作限制。

核心方法：使用pg_prewarm #

pg_prewarm是PostgreSQL专门用于将关系数据加载到内存缓存的只读函数，仅在数据库服务器端执行，不会向本地PC传输任何实际数据，完美匹配需求。它可以指定加载目标关系（表或索引）的所有数据块，且支持并行执行以最大化硬件利用率。

基本用法 #

针对单个表及其索引执行预加载：

-- 加载指定表的所有数据块
SELECT pg_prewarm('public.your_table_name', 'buffer', 'all');

-- 加载指定索引的所有数据块
SELECT pg_prewarm('public.your_table_pkey', 'buffer', 'all');

参数说明：

• 第一个参数：关系的全限定名（模式名.关系名）
• 第二个参数：buffer表示加载到数据库共享缓存（正是蓝绿部署需要的目标）
• 第三个参数：all表示加载该关系的所有数据块

批量处理所有表和索引 #

通过系统表生成所有需要预加载的关系的执行语句，避免手动逐个操作：

SELECT format('SELECT pg_prewarm(''%I.%I'', ''buffer'', ''all'');', schemaname, relname)
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE relkind IN ('r', 'i') -- 'r'代表普通表，'i'代表索引
  AND n.nspname NOT IN ('pg_catalog', 'information_schema'); -- 排除系统库

将查询结果导出为SQL脚本，即可批量执行。

并行执行以最大化速度 #

和你在主库使用的策略一致，启动数据库服务器CPU核心数2倍左右的会话，将批量SQL脚本拆分后分配到不同会话并行执行。例如：

• 使用psql打开多个终端会话，每个会话执行一部分预加载语句
• 借助批量执行工具（如pg_parallel）自动分配任务到多个并行连接

验证预加载效果 #

执行完成后，可通过以下方式验证所有数据块已被加载：

-- 查看表的缓存命中情况（hit_rate接近100%表示已加载）
SELECT 
  schemaname, relname,
  round(100 * (heap_blks_hit / (heap_blks_hit + heap_blks_read)::numeric), 2) AS hit_rate
FROM pg_stat_user_tables;

-- 查看索引的缓存命中情况
SELECT 
  schemaname, relname,
  round(100 * (idx_blks_hit / (idx_blks_hit + idx_blks_read)::numeric), 2) AS hit_rate
FROM pg_stat_user_indexes;

注意事项 #

1. 权限要求：在RDS只读副本上，需使用具有超级权限的账号（如默认的postgres用户），该账号默认拥有pg_prewarm函数的执行权限。
2. 版本支持：pg_prewarm从PostgreSQL 9.4开始提供，RDS当前支持的所有PostgreSQL版本均已包含该函数。
3. 无数据修改：该操作仅为只读缓存加载，不会修改任何数据，完全符合只读副本的操作限制。

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