Skip to content

.tip

· one of the small things

An UNLOGGED table is a cache that speaks SQL

If you want a cache and you already have Postgres, an UNLOGGED table skips the write-ahead log. Writes run close to memory speed, and after a crash the table is empty, which is exactly how a cache should behave.

CREATE UNLOGGED TABLE cache (
  key text PRIMARY KEY, value jsonb NOT NULL, expires_at timestamptz NOT NULL
);
CREATE INDEX ON cache (expires_at);

A cleanup job deletes expired rows. Reads use the connection the request already holds, so there's no second pool to run out of.

postgresqlcaching

Longer version: the post this came from.