PostgreSQL as a Key-Value Store

PostgreSQL ships no built-in Redis-style server: there is no GET/SET protocol, no in-memory data-structure engine, no per-key TTL. What it does have is three solid ways to model key-value data inside a relational database — and for a large class of "cache-adjacent" workloads, that is enough to avoid running a second system.

Three ways to do KV in PostgreSQL

hstore: the classic key-value type

hstore is a contrib extension that stores a set of key/value pairs inside a single value — both keys and values are text. It comes with a compact operator set (-> to fetch a key, ? to test existence, @> for containment) and can be indexed with GIN or GiST so that key lookups don't scan the table:

CREATE EXTENSION hstore;

CREATE TABLE session_attrs (
  session_id uuid PRIMARY KEY,
  attrs      hstore NOT NULL
);

CREATE INDEX ON session_attrs USING gin (attrs);

-- fetch one key
SELECT attrs -> 'theme' FROM session_attrs WHERE session_id = $1;

-- rows where a key exists / matches
SELECT * FROM session_attrs WHERE attrs ? 'theme';
SELECT * FROM session_attrs WHERE attrs @> 'theme => "dark"';

hstore predates jsonb and remains fine for flat string maps. It has no nesting, no numbers, no booleans — everything is text.

jsonb: documents you can index

For anything nested, jsonb is the modern answer. Stored in a decomposed binary form, it supports containment (@>), existence (?, ?|, ?&), and path operators, all backed by GIN indexes:

CREATE TABLE kv_docs (
  key  text PRIMARY KEY,
  doc  jsonb NOT NULL
);

-- jsonb_path_ops: smaller index, supports @> (and @?, @@) — the common case for KV
CREATE INDEX ON kv_docs USING gin (doc jsonb_path_ops);

SELECT * FROM kv_docs WHERE doc @> '{"user_id": 42}';

The default jsonb_ops GIN opclass indexes every key and value and supports more operators; jsonb_path_ops indexes only value hashes per path and is typically several times smaller — for pure key-value access patterns it is the right default. The full indexing trade-off is covered in JSONB storage and search.

A plain two-column table, optionally UNLOGGED

For the purest KV shape — one key, one value, nothing else — a regular table is often the best tool. B-tree point lookups on a primary key are the most optimized path PostgreSQL has:

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

Marking the table UNLOGGED gives you honest cache semantics: rows are not written to the WAL, so writes are substantially cheaper, but the table is automatically truncated after a crash or unclean shutdown, and its contents are not replicated to standby servers. That is exactly the durability trade-off a cache wants — fast writes, disposable data — as long as every row can be rebuilt from the authoritative source.

What PostgreSQL does not give a cache workload

Three gaps matter before you declare Redis redundant.

No built-in TTL or eviction. PostgreSQL has no per-key expiration and no maxmemory-style eviction policies; rows live until you delete them. The standard substitutes are a scheduled sweep (pg_cron — see the examples below) or time-window partitioning where you DROP expired partitions instead of running a mass DELETE. Both work, but expiry granularity is "next sweep interval", not millisecond-exact, and nothing evicts under memory pressure.

No Redis data structures. Lists, sets, sorted sets, streams, bitmaps, HyperLogLog — the operations that make Redis more than a hash map (LPUSH, ZADD, XADD, atomic increments on members) have no direct equivalent. You can approximate counters with UPDATE ... RETURNING and queues with SELECT ... FOR UPDATE SKIP LOCKED, but a leaderboard or a fan-out stream in SQL is reimplementation, not configuration.

LISTEN/NOTIFY is not Redis pub/sub. PostgreSQL's LISTEN/NOTIFY delivers notifications only when the sending transaction commits, caps payloads at under 8000 bytes, and keeps no history — a client that is not connected at that moment misses the event entirely. It is a wake-up signal ("something changed, go re-read"), not a message broker.

The honest performance picture

Redis and Valkey serve single-key operations from memory in sub-millisecond time — that is their entire design center. PostgreSQL pays for SQL parsing, planning, and (on logged tables) WAL on every statement, so each point read or write carries more overhead even when the data sits in shared buffers. In return you get one less component to deploy, replicate, secure, and page someone for — and your "cache" can join, constrain, and transact with the rest of your data.

A practical decision checklist:

PostgreSQL is enough when:

• You already run PostgreSQL and the KV workload is moderate — sessions, feature flags, app-level cache entries, job metadata.
• Cached rows must stay transactionally consistent with durable tables (write both in one commit).
• You want to query the "cache" with SQL, not just fetch by key.
• Eviction can be lazy: a sweep every few minutes is acceptable.

Use a real Redis/Valkey when:

• The latency budget is sub-millisecond at high ops/sec on hot keys.
• You need automatic TTL with eviction under memory pressure (maxmemory policies).
• The workload is built on Redis data structures — rate limiters, leaderboards, streams, fan-out queues.
• The cache is a shared, high-traffic tier in front of the database, where its whole job is absorbing reads PostgreSQL should never see.

For the licensing context around that choice (Redis's 2024 license change, the Valkey fork), see the comparison FAQ.

Working examples

A minimal cache table with lazy expiry, an upsert, and a pg_cron sweep. pg_cron is a cron-based job scheduler that runs inside PostgreSQL as an extension:

CREATE EXTENSION pg_cron;

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

CREATE INDEX ON cache_entries (expires_at);

-- upsert with a 1-hour TTL
INSERT INTO cache_entries (key, value, expires_at)
VALUES ($1, $2, now() + interval '1 hour')
ON CONFLICT (key) DO UPDATE
  SET value = EXCLUDED.value,
      expires_at = EXCLUDED.expires_at;

-- read: treat expired rows as misses
SELECT value FROM cache_entries
WHERE key = $1 AND expires_at > now();

-- sweep expired rows every 5 minutes
SELECT cron.schedule(
  'expire-cache',
  '*/5 * * * *',
  $$DELETE FROM cache_entries WHERE expires_at < now()$$
);

Note the pattern: expiry is enforced on read (expires_at > now()) and pg_cron is only the garbage collector. That way a delayed or failed sweep causes stale disk usage, never stale reads.

Related pages

• JSONB storage and search — operators, GIN opclasses, and indexing depth for the jsonb route
• PostgreSQL extension ecosystem — where hstore and pg_cron sit in the extension landscape
• Database comparison FAQ — PostgreSQL vs Redis/Valkey and the other usual questions