pg_prewarm on worker nodes

I’d like to load all the distributed tables on worker nodes to the memory with pg_prewarm. But the coordinator node doesn’t propagate a query, 'SELECT pg_prewarm', to the worker nodes. So, I wrote a simple procedure to execute pg_prewarm on worker nodes.

On coordinator node:

CREATE OR REPLACE PROCEDURE pg_prewarm_all() 
AS $$
DECLARE
  cur1 CURSOR FOR
	SELECT shard_name FROM citus_shards;
	shard_name_rec RECORD;
BEGIN
  OPEN cur1;
  LOOP
    FETCH cur1 INTO shard_name_rec;
      EXIT WHEN NOT FOUND;
    RAISE INFO '%', shard_name_rec.shard_name;
    PERFORM run_command_on_workers($cmd$ 
      SELECT pg_prewarm('%', 'buffer');
      $cmd$), shard_name_rec.shard_name;
  END LOOP;
  CLOSE cur1;
END;
$$
LANGUAGE plpgsql;

citus=> call pg_prewarm_all();
INFO:  github_events_102008
INFO:  github_events_102009
INFO:  github_events_102010
...snipped...
INFO:  github_users_102069
INFO:  github_users_102070
INFO:  github_users_102071
CALL

Check pg_buffercache on worker node:

citus=> SELECT c.relname, count(*) AS buffers
        FROM pg_buffercache b INNER JOIN pg_class c
        ON b.relfilenode = pg_relation_filenode(c.oid) AND
           b.reldatabase IN (0, (SELECT oid FROM pg_database
                                 WHERE datname = current_database()))
        GROUP BY c.relname
        ORDER BY 2 DESC
        LIMIT 10;
       relname        | buffers 
----------------------+---------
 payload_index_102008 |    1099
 payload_index_102038 |    1076
 payload_index_102036 |    1070
 payload_index_102018 |    1069
 payload_index_102016 |    1008
 payload_index_102022 |     988
 payload_index_102032 |     967
 payload_index_102034 |     937
 payload_index_102020 |     922
 payload_index_102030 |     920
(10 rows)

関連