Enable pg_prewarm in GCP Cloud SQL (postgres)

ref

gcp cloud sql postgress extensions doc

pg_prewarm
pg_buffercache

setup

check current version and extensions

select version();
/dx;

create pg_prewarm and pg_buffercache

# db
CREATE EXTENSION pg_prewarm;
CREATE EXTENSION pg_buffercache;

add all the relations

add all the relation in public and relation kind is r or i.

CREATE OR REPLACE FUNCTION pg_prewarm_all()
RETURNS text AS $BODY$
DECLARE
sql text;
relnames text;

BEGIN

FOR relnames IN
  SELECT relname::text FROM pg_class
  WHERE
    relnamespace = (
      SELECT oid FROM pg_catalog.pg_namespace
      WHERE
        nspname = 'public'
    )
  AND 
    relkind IN('r','i')

LOOP
  sql := 'SELECT pg_prewarm(''' || relnames || ''',''buffer'')';
  EXECUTE sql;
END LOOP;

RETURN 'OK';

END $BODY$
LANGUAGE plpgsql;

check

# pg_prewarm
SELECT * FROM pg_prewarm

# pg_buffercache
 SELECT count(*)
 FROM
 pg_buffercache
 WHERE
 Relfilenode = (
 SELECT
 relfilenode
 FROM pg_class
 WHERE relname = 'xxxxx');
 
* replace xxxxx with relation name