Postgres as a Key-Value Store: Pick What You Can Afford to Lose

It usually starts with a rate limiter. Or sessions. Or a table of idempotency keys that someone decides is basically a cache. A few sprints later there’s a Redis cluster on the architecture diagram, a second set of credentials in the secrets manager, and a brand-new way to get paged at 3 a.m.

If you already run Postgres, you may not have needed any of it. Postgres makes a very good key-value store, and the schema takes five minutes. The interesting part is a question almost nobody asks on purpose: when the server dies, which of your writes do you actually need back?

Postgres lets you answer that separately for every kind of data, and the answers aren’t priced the way you’d guess. I measured all three on a fresh PostgreSQL 18 on my laptop:

Look at the middle row. Asynchronous commit lands within a couple of microseconds of an unlogged table, keeps your replicas and backups, and after a crash comes back with everything except the last fraction of a second. It’s the most useful durability setting that most teams have never turned on.

This post is the design I’d ship: one table, five queries, how to choose a durability level for each kind of data, and the maintenance that keeps it fast a week after launch. Every diagram is interactive, so poke at them.

The table

CREATE TABLE kv (
    key        text COLLATE "C" PRIMARY KEY,
    value      bytea       NOT NULL,
    expires_at timestamptz NOT NULL
) WITH (
    fillfactor = 70,
    autovacuum_vacuum_scale_factor = 0.01
);

Three columns and two settings. It’s small, but every line is there for a reason:

• COLLATE "C" compares keys byte by byte. That’s cheaper than a language-aware collation, and it’s what lets a prefix query like LIKE 'user:42:%' walk the primary key index instead of reading the whole table.
• bytea values. A key-value store stores bytes; serialization is the application’s job. Keep values under about 2 kB and they live in the table itself. Anything wider gets compressed and moved out of line into TOAST storage, which adds work to every read and write.
• expires_at NOT NULL. Every key gets an expiry, and keys that should live forever get 'infinity'. With no NULLs, the queries below never have to think about three-valued logic.
• No index on expires_at. This looks like an oversight. It’s the most important decision in the design, and the section on row versions shows why.
• fillfactor = 70 leaves 30% of every page empty, so rewritten rows have somewhere to go.
• autovacuum_vacuum_scale_factor = 0.01 vacuums once 1% of rows have changed, instead of waiting for the default 20%.

Five queries

Read. The query checks expiry itself, against the database’s clock:

SELECT value
FROM kv
WHERE key = $1
  AND expires_at > now();

Write. A single upsert. The TTL arrives as seconds and the database turns it into a deadline, so application servers never stamp expiries with their own clocks. Two servers whose clocks drift apart can’t disagree about whether a key exists.

INSERT INTO kv (key, value, expires_at)
VALUES ($1, $2, now() + make_interval(secs => $3))
ON CONFLICT (key) DO UPDATE
SET value      = EXCLUDED.value,
    expires_at = EXCLUDED.expires_at;

Write if absent. For locks, leases, and idempotency keys. There’s a trap here. An expired row still owns its key until something deletes it, so the obvious ON CONFLICT DO NOTHING refuses to take over a key that every read says is gone. Treat an expired row as absent instead:

INSERT INTO kv (key, value, expires_at)
VALUES ($1, $2, now() + make_interval(secs => $3))
ON CONFLICT (key) DO UPDATE
SET value      = EXCLUDED.value,
    expires_at = EXCLUDED.expires_at
WHERE kv.expires_at <= now()
RETURNING true AS acquired;

If a live row holds the key, nothing changes and nothing comes back. If a row comes back, the key is yours.

Count. Rate limits want an integer, not bytes, so counters get their own table and a bigint. One statement does fixed-window counting:

CREATE TABLE counters (
    key        text COLLATE "C" PRIMARY KEY,
    count      bigint      NOT NULL,
    expires_at timestamptz NOT NULL
) WITH (fillfactor = 70);

INSERT INTO counters (key, count, expires_at)
VALUES ($1, 1, now() + make_interval(secs => $2))
ON CONFLICT (key) DO UPDATE
SET count = CASE WHEN counters.expires_at <= now()
                 THEN 1
                 ELSE counters.count + 1 END,
    expires_at = CASE WHEN counters.expires_at <= now()
                      THEN EXCLUDED.expires_at
                      ELSE counters.expires_at END
RETURNING count;

When the window has ended, the counter starts again at 1 with a fresh deadline. Otherwise it goes up by one and keeps the deadline it had. Compare what comes back with your limit. Two requests racing for the same key queue up on the row, so no increment is ever lost.

Sweep. Reads already skip expired rows, so deleting them is about disk space, not correctness. Delete in small batches so no transaction holds its locks for long:

DELETE FROM kv
WHERE ctid IN (
    SELECT ctid
    FROM kv
    WHERE expires_at <= now()
    LIMIT 5000
);

Run it every minute from pg_cron or your job runner, and keep going while it deletes a full batch. With no index on expires_at, the inner query reads the whole table. For a table that fits in memory, one pass a minute is cheap, and it’s the price of the HOT updates coming up later.

Three ways to be durable

Before Postgres tells you a write happened, it writes a receipt. The receipt is a record in the write-ahead log, the WAL, and Postgres waits until it’s safely on disk before it answers. The table itself gets updated in memory and written out later, at a checkpoint.

That receipt is the whole trick behind crash safety. If the power dies a millisecond after your commit, recovery reads the receipts and replays them. It’s also where a small write spends most of its time, waiting for the disk to confirm.

The two faster options each drop a piece of that ritual. Asynchronous commit still writes the receipt; it just stops waiting for the ink to dry. An unlogged table stops writing receipts at all. Pick one and follow a single write through Postgres:

And here’s what those pieces cost. Reads don’t move, because a read never needed a receipt. Writes get cheaper exactly in proportion to how slow your disk flush is:

The first view flatters the regular table. By default, Postgres on macOS doesn’t force the drive to empty its own write cache at commit. Flip the toggle to fsync_writethrough, which does, and a regular write jumps to 4.3 ms while the other two sit at about 40 µs, because neither waits on the disk. A production server with power-loss-protected NVMe lands somewhere between those two views. Asynchronous commit and unlogged tables barely care which.

Asynchronous commit: fast, and it comes back after a crash

Asynchronous commit isn’t a kind of table. It’s a setting you turn on for the writes that can afford it:

-- For one transaction
BEGIN;
SET LOCAL synchronous_commit = off;
-- write keys
COMMIT;

-- Or for every connection a dedicated role opens
ALTER ROLE kv_writer SET synchronous_commit = off;

Postgres answers as soon as the receipt is written, and a background process, the WAL writer, flushes it a moment later. The documentation is careful about what that risks: “data loss, not data corruption.” After a crash, Postgres replays every receipt that reached the disk and comes back consistent. The only casualties are commits from the final moments before the crash, at most three times wal_writer_delay. At the default, that’s 600 ms.

Everything else keeps working. The receipts still exist, so the rows replicate, land in your backups, and survive point-in-time recovery. You can even mix modes: an order committed normally and a session committed asynchronously can live in the same database, or the same table.

Unlogged tables: fast, and gone after a crash

Same table, one extra keyword:

CREATE UNLOGGED TABLE kv_cache (
    key        text COLLATE "C" PRIMARY KEY,
    value      bytea       NOT NULL,
    expires_at timestamptz NOT NULL
) WITH (
    fillfactor = 70,
    autovacuum_vacuum_scale_factor = 0.01
);

An unlogged table writes no receipts for its rows. Latency barely improves over asynchronous commit; volume is where it wins. Over 100,000 upserts, the regular and asynchronous tables each wrote 560 to 575 bytes of WAL per upsert, counting the full-page images Postgres adds after each checkpoint. The unlogged table wrote 40, essentially the commit record and nothing else. That’s less disk I/O, less replication traffic, and smaller WAL archives.

Without receipts, though, Postgres has no way to prove after a crash that any of those rows existed, so it doesn’t pretend. The documentation is blunt: an unlogged table “is automatically truncated after a crash or unclean shutdown.” Each one carries an empty copy of itself, the initialization fork, and recovery simply copies it over the real thing. The table doesn’t come back stale. It comes back empty, indexes and all.

A clean shutdown is fine. Postgres checkpoints on the way down, writes out every page, unlogged ones included, and the rows are there when it restarts. Replicas are another story. A streaming replica is built entirely from receipts, so it knows the table exists and holds none of its rows. Fail over, and the new primary has an empty table. Physical backups copy only the empty initialization fork. pg_dump does include the rows by default, unless you run it against a standby.

Write a few rows, then break things:

Choosing

Choose per kind of data, never per database. The test is always the same: if this vanished right now, what would happen?

• Regular table when it’s the only copy: feature flags, configuration, idempotency records you’ve promised to honor.
• Asynchronous commit when losing the last half-second is fine but losing everything isn’t: sessions, rate-limit counters, user preferences, retryable job state.
• Unlogged table when you could rebuild all of it from scratch and it’s written so often that WAL volume hurts: computed results, rendered fragments, fan-out caches.

Go carefully with that last one. A cache that empties during an incident sends every request straight to whatever it was protecting, at the exact moment that system can least take it. If a cold start could knock something over, asynchronous commit is the safer choice.

The cost that shows up next week

Whatever durability you pick, Postgres never overwrites a row in place. An update writes a fresh copy of the row and marks the old one dead, and VACUUM cleans up later. A key-value store is almost nothing but updates to the same rows, and a busy rate-limit counter can be rewritten thousands of times a minute. Dead copies pile up fast.

The thing that keeps this cheap is the heap-only tuple update, HOT for short. When an update changes no indexed column and the new copy fits on the same page, Postgres leaves every index alone and simply chains the new copy to the old one. Later reads and writes can clear the dead copies off that page without waiting for VACUUM. That’s what the fillfactor of 70 is for. It keeps room on every page for the next copy.

And that’s why there’s no index on expires_at. Every write in this design refreshes the TTL, so every write changes expires_at. Index that column, and not a single update can be HOT. Each one has to tell every index on the table where the row went. Overwrite a key and see for yourself:

The same thing happens at full scale. Across more than eight million upserts to the kv table in my benchmarks, all but two were HOT. Adding one index on expires_at took that to 0 out of 371,048, and the indexes grew from 6 MB to 21 MB in fifteen seconds.

So watch one number in production:

SELECT n_live_tup,
       n_dead_tup,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_update_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'kv';

A HOT percentage in the high nineties means updates are staying put. If it starts to fall, something indexed is changing or the pages have run out of room.

Before you ship it

• Give asynchronous writes their own role. ALTER ROLE ... SET synchronous_commit = off on a role with its own connection pool is much harder to get wrong than remembering SET LOCAL in every code path.
• Pool connections and batch reads. Every call is a round trip, and over a real network the round trip, not the commit, is most of your latency. Fetch many keys at once with WHERE key = ANY($1).
• Keep unlogged tables on the primary. Replicas have their definitions and none of their rows. If your application sends reads to replicas, carve these tables out.
• Plan for a cold start. After a failover, unlogged tables are empty and every key misses at once. Collapse concurrent misses for the same key into one recomputation, and add jitter to TTLs so keys written together don’t expire together.
• Put the table on a dashboard. HOT percentage, dead rows, and table size belong right next to your hit rate.

What about Redis?

Redis isn’t in the chart, on purpose. Its benchmark tool and pgbench time requests differently enough that one axis would say more about the tools than the servers. In an earlier comparison that drove both through the same client, using pgkv, a small Rust library I wrote around this idea, Redis was about three times faster per call.

That’s exactly what you’d expect from an in-memory server that doesn’t parse SQL, plan queries, or keep row versions. Whether it matters depends on the rest of the request. A session lookup inside a request that already spends 20 ms elsewhere won’t notice the difference. A counter checked on every request at very high volume might. Reach for Redis when you need that last factor of three, its data structures, or throughput well beyond what one Postgres primary can spare. Stay with Postgres when one fewer stateful system is worth more.

Either way, the question from the top still stands. Every key-value store makes a promise about what survives a crash. Postgres just asks you to make that promise yourself, one kind of data at a time.

\set k random(1, 100000)
INSERT INTO kv (key, value, expires_at)
VALUES ('user:' || :k, convert_to(repeat('x', 256), 'UTF8'),
        now() + make_interval(secs => 300))
ON CONFLICT (key) DO UPDATE
SET value      = EXCLUDED.value,
    expires_at = EXCLUDED.expires_at;

# Regular table and unlogged table
pgbench -n -M prepared -c 1 -j 1 -T 15 -f set.sql
# Asynchronous commit, against the regular table
PGOPTIONS='-c synchronous_commit=off' pgbench -n -M prepared -c 1 -j 1 -T 15 -f set.sql