Buffer cache prewarm

Warm pgresp tables into memory after restart with pg_prewarm and autoprewarm.

After a Postgres restart, its in-memory cache of table data starts empty. Redis-style GET traffic then pays a cold-start cost until recently used keys are read back from disk. PostRESP uses PostgreSQL’s built-in pg_prewarm extension to load pgresp.* tables (and their indexes) into that cache sooner.

Prewarm does not change steady-state latency once data is already cached. It shortens the post-restart ramp-up. It is complementary to the SQL / RESP path — not a substitute for it.

pg_prewarm ships with a normal PostgreSQL install (including the official Postgres Docker images). You enable it with CREATE EXTENSION and, for automatic restore across restarts, by listing it in shared_preload_libraries.

Default (Docker / compose)

make postgres-up and the compose stack enable prewarm by default:

1. pg_prewarm is listed in shared_preload_libraries (with pg_cron and pg_resp_gw_host).
2. pg_prewarm.autoprewarm is on (PostgreSQL’s default when the library is loaded; compose also sets it explicitly).
3. Init creates CREATE EXTENSION pg_prewarm alongside the PostRESP extensions.

With autoprewarm on, Postgres periodically records which table pages were in memory (default interval 300s) and reloads that set after the next restart. That restores the previous hot set without a manual warm step.

Disable autoprewarm

Autoprewarm is a Postgres server setting — not a gateway JSON or env field.

PG_PREWARM_AUTOPREWARM=off make postgres-up

# Extension installs / custom Postgres
shared_preload_libraries = 'pg_cron,pg_resp_gw_host,pg_prewarm'
pg_prewarm.autoprewarm = off

Leave pg_prewarm in shared_preload_libraries if you still want the SQL pg_prewarm() function and pgresp.prewarm(); only the background save / reload worker follows autoprewarm. Omitting pg_prewarm from preload turns off that automatic path (you can still CREATE EXTENSION pg_prewarm and warm tables manually).

Manual prewarm

Force a full warm of every pgresp table and index:

SELECT pgresp.prewarm();  -- total pages loaded into Postgres memory

Or call pg_prewarm directly:

SELECT pg_prewarm('pgresp.strings');
-- All tables + indexes in schema pgresp:
SELECT pg_prewarm(c.oid)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'pgresp'
  AND c.relkind IN ('r', 'i');

Load modes (buffer is preferred):

Use manual prewarm when autoprewarm is off, after restoring a database dump, or when you want an immediate full warm instead of waiting for the next save interval.

Extension installs

On an existing Postgres (not the compose image):

1. Confirm CREATE EXTENSION pg_prewarm works (it should on a standard PostgreSQL install).
2. Append pg_prewarm to shared_preload_libraries (do not replace existing entries), set pg_prewarm.autoprewarm = on unless you opt out, and restart.
3. CREATE EXTENSION pg_prewarm; in the product database, then CREATE EXTENSION pg_resp; / pg_resp_gw_host as usual.

See Postgres Extensions.

Operational notes

1. Size Postgres shared memory (shared_buffers) for the working set you care about. Prewarmed pages can still be evicted if other data competes for the same cache.
2. UNLOGGED vs LOGGED — prewarm helps after a clean restart when tables still exist on disk. After a crash, UNLOGGED tables may be empty (nothing useful to warm). Prefer logged when crash recovery and cache restore both matter.
3. TTL churn — expired keys and vacuum free space over time; keep autovacuum healthy on aggressive TTL workloads.
4. Replication — each Postgres node has its own memory cache. Standbys need their own prewarm/autoprewarm after promotion or restart.
5. What is cached — prewarm loads table pages from disk into memory. A GET still has to look up the key and read the row; it just avoids waiting on disk for those pages.