Here is the architecture diagram for a product with roughly zero users:
Postgres ← the actual data
Redis ← cache, and also the queue, and also rate limits
SQS / Kafka ← the real queue, for when Redis isn't durable enough
Pinecone ← embeddings
Elasticsearch ← search
A cron box ← nightly jobs, currently a $5 VPS nobody has SSH'd into since April
Six systems. Six sets of credentials, six failure modes, six things to upgrade, six bills, six places for state to disagree with the other five. A distributed systems problem, chosen voluntarily, before the first paying customer.
The Go gateway behind Xenition has no Redis in go.mod. Jobs are claimed with SKIP LOCKED, search runs on tsvector, and a large share of the schema is jsonb. Not because Postgres is magic — because every extra system has to earn its place by solving a problem we actually have, and at our scale most of them can't clear the bar.
This isn't "Postgres scales forever." It doesn't. It's that the point where it stops is much further out than the diagram above assumes, and everything before that point is operational cost you're paying for scale you don't have.
Below: six things people add a service for, the SQL that does them instead, and — the section that matters most — when you should genuinely leave.
1. A job queue: FOR UPDATE SKIP LOCKED
This is the one that surprises people, so it goes first.
SKIP LOCKED landed in Postgres 9.5. It makes the classic "many workers, one table, nobody takes the same row twice" problem trivially correct — a reader skips rows another transaction has locked instead of blocking on them.
CREATE TABLE jobs (
id bigserial PRIMARY KEY,
kind text NOT NULL,
payload jsonb NOT NULL,
run_after timestamptz NOT NULL DEFAULT now(),
attempts int NOT NULL DEFAULT 0,
max_attempts int NOT NULL DEFAULT 5,
locked_until timestamptz,
status text NOT NULL DEFAULT 'pending'
);
-- The index that makes the claim fast. Partial: pending rows only, so it
-- stays small even when the table holds millions of finished jobs.
CREATE INDEX jobs_claim_idx ON jobs (run_after)
WHERE status = 'pending';
The claim, which is the whole trick:
UPDATE jobs SET
status = 'running',
attempts = attempts + 1,
locked_until = now() + interval '5 minutes'
WHERE id IN (
SELECT id FROM jobs
WHERE status = 'pending'
AND run_after <= now()
ORDER BY run_after
LIMIT 10
FOR UPDATE SKIP LOCKED -- ← the entire feature
)
RETURNING id, kind, payload;
Run twenty workers against that. None of them will hand you the same job twice, and none will block waiting on another. No broker, no consumer groups, no offset management.
You get the rest of a real queue for the price of ordinary SQL:
-- Retry with exponential backoff
UPDATE jobs SET status = 'pending',
run_after = now() + (interval '10 seconds' * power(2, attempts))
WHERE id = $1 AND attempts < max_attempts;
-- Dead letter
UPDATE jobs SET status = 'dead' WHERE id = $1 AND attempts >= max_attempts;
-- Reap workers that died holding a lock
UPDATE jobs SET status = 'pending'
WHERE status = 'running' AND locked_until < now();
That last query is your visibility timeout — the thing SQS charges for. Four lines, on a timer.
The part nobody mentions, and it's the real argument:
BEGIN;
INSERT INTO invoices (...) VALUES (...);
INSERT INTO jobs (kind, payload) VALUES ('send_invoice_email', ...);
COMMIT;
The row and the job commit together, or neither does. With an external broker this is the dual-write problem, and the industry's answer is the transactional outbox pattern — a table, a relay process, a whole named design pattern — whose entire purpose is to buy back the atomicity you gave away by putting the queue somewhere else. If the queue lives in the database, you never gave it away. There is nothing to buy back.
Throughput: a modest Postgres handles thousands of claims per second this way. Know your actual number before assuming you're above it.
Two of these run in the Xenition gateway right now: outbound webhook delivery for the marketplace, and scheduled social posts. The comment sitting above that second query says exactly what the feature buys — "SKIP LOCKED so two gateways cannot double-post." That is the entire coordination story between instances. There is no second sentence, no leader election, no lease.
2. Pub/sub: LISTEN / NOTIFY
Need to tell every app server something changed — invalidate a cache, push an SSE event, refresh a dashboard?
-- Fires when the transaction commits, not before. This is the good part.
SELECT pg_notify('artifact_changed', json_build_object('id', 42)::text);
conn.Exec(ctx, "LISTEN artifact_changed")
for {
n, err := conn.WaitForNotification(ctx)
// ...
}
Three caveats, because this one is genuinely limited and you should know before building on it:
- Not durable. A disconnected listener misses the message entirely. There is no replay.
- 8000 byte payload limit.
- Delivered after commit — which is exactly what you want, and better than most external brokers manage without effort.
The pattern that makes it safe: notify an id, not a payload. The listener reads the row itself. A missed notification then degrades into staleness your next poll or reconnect fixes, instead of a permanently lost update. If you need durable fan-out with replay, that's a real Kafka use case — but be sure you need replay, not just delivery.
3. Cache: it's a table
Yes, really, for a large class of caching:
CREATE UNLOGGED TABLE cache ( -- UNLOGGED: no WAL, much faster writes,
key text PRIMARY KEY, -- contents lost on crash. It's a cache.
value jsonb NOT NULL,
expires_at timestamptz NOT NULL
);
INSERT INTO cache (key, value, expires_at) VALUES ($1, $2, now() + $3)
ON CONFLICT (key) DO UPDATE
SET value = EXCLUDED.value, expires_at = EXCLUDED.expires_at;
DELETE FROM cache WHERE expires_at < now(); -- on a timer
An indexed primary-key lookup on a warm table costs a fraction of a millisecond. If your alternative is a network hop to Redis, the gap is smaller than intuition suggests.
Redis genuinely wins when you're doing 50k+ ops/sec of pure key-value work, when you want its data structures (sorted sets for leaderboards, streams, HyperLogLog), or when you're deliberately keeping load off a database that is the bottleneck. Those are real reasons. "It's the caching layer, that's just what you use" is not one — adding Redis to relieve a database under no pressure adds a failure mode and removes nothing.
4. Rate limiting
A counter and an upsert:
CREATE TABLE rate_limits (
subject text NOT NULL, -- user id, ip, api key
window_start timestamptz NOT NULL,
count int NOT NULL DEFAULT 0,
PRIMARY KEY (subject, window_start)
);
INSERT INTO rate_limits (subject, window_start, count)
VALUES ($1, date_trunc('minute', now()), 1)
ON CONFLICT (subject, window_start)
DO UPDATE SET count = rate_limits.count + 1
RETURNING count;
One round trip, atomic, returns the new count so you decide immediately. Honest caveat: a single very hot subject serialises on one row, and at extreme rates you want a sliding window or token bucket rather than fixed buckets. For per-user API limits on a normal product this is enough — and it has the property Redis-based limiters famously don't: it's consistent with the rest of your data, so "did we charge them for this request" and "did we count this request" cannot disagree.
5. Vector search: pgvector
CREATE EXTENSION vector;
ALTER TABLE documents ADD COLUMN embedding vector(1536);
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
SELECT id, title, content
FROM documents
WHERE workspace_id = $1 -- ← the thing dedicated vector DBs
AND deleted_at IS NULL -- make you fight for
ORDER BY embedding <=> $2
LIMIT 10;
Look at that WHERE clause, because it's the entire argument.
Embeddings are never the whole query in a real product. It's always this user's documents, not deleted, in this workspace, maybe from the last 90 days. In a separate vector store, each of those filters is either a metadata field you must duplicate and keep in sync, or a post-filter that ruins your LIMIT — you asked for 10, filtering leaves 2, now you're re-querying with a bigger limit and guessing.
And the sync problem is permanent. Two systems, one source of truth, no shared transaction. A document deleted here and still present there is a data leak, not a stale cache.
With pgvector the embedding is a column on the row. Deleting the row deletes the embedding, in the same transaction, forever. That's the feature.
Dedicated vector databases earn their keep at scale — hundreds of millions of vectors, heavy filtered ANN, sharding across nodes. Below that, you're operating a second database to avoid a column.
6. Full-text search, and cron
Search:
ALTER TABLE documents ADD COLUMN search tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED;
CREATE INDEX documents_search_idx ON documents USING gin (search);
SELECT id, ts_rank(search, q) AS rank
FROM documents, websearch_to_tsquery('english', $1) q
WHERE search @@ q ORDER BY rank DESC LIMIT 20;
GENERATED ALWAYS AS ... STORED means the column maintains itself — no trigger to write, no update to forget. And websearch_to_tsquery accepts what users actually type, quoted phrases and -exclusions included.
Elasticsearch still wins for fuzzy matching, sophisticated analyzers, faceting at scale, and typo tolerance. It does not win for "let people find their own documents."
Concretely, on our side: team-channel message search and the support inbox both run on tsvector. More interesting is the app builder — Xenition generates working apps for users, and search ships to the generated app as a Postgres full-text scan over that app's own schema. No external engine to provision, per app, forever. That's the multiplier people miss: with search in the database, per-tenant search is an index and a WHERE clause. With a search cluster, every tenant is an operations decision.
Cron: pg_cron if your host offers it — most managed Postgres does now:
SELECT cron.schedule('reap-jobs', '* * * * *',
$$UPDATE jobs SET status='pending'
WHERE status='running' AND locked_until < now()$$);
If not, run the loop in your app and guard it with an advisory lock so only one instance acts:
SELECT pg_try_advisory_lock(hashtext('nightly-rollup'));
That's leader election in one line. No ZooKeeper, no etcd, no lease renewal — and the lock releases automatically when the connection dies, which is precisely the failure mode you'd otherwise handle by hand.
When you should actually leave
The credibility section. Every one of these is a real reason, and if you hit one, go:
| Signal | Move to |
|---|---|
| Queue sustained above ~10k jobs/sec, or you need replay, or multiple independent consumer groups | Kafka / NATS |
| Cache above ~50k ops/sec, or you want sorted sets / streams | Redis |
| Hundreds of millions of vectors, or heavy filtered ANN across shards | A dedicated vector DB |
| Fuzzy matching, typo tolerance, heavy faceting, multi-language analyzers | Elasticsearch / Typesense |
| Analytical scans over billions of rows fighting your OLTP traffic | A column store — ClickHouse, DuckDB, a warehouse |
| Your write master is genuinely saturated and read replicas are exhausted | Shard, or split the workload out |
Note what all six have in common: each is a measurement, not a vibe. "We might need it later" is not on the list. Neither is "this is the standard architecture."
The ordering matters too. Adding a service is a one-way door in practice — once two systems hold state, everything downstream inherits the consistency problem permanently. Extracting later is a migration. Adding early is a tax you pay every day. Do it when the number says so.
The actual thesis
It isn't that Postgres is secretly six products. It's this:
Every system you add is a state boundary, and every state boundary is a place where your data can be two different things at once.
The queue that fired for a row that rolled back. The vector still returned for a document you deleted. The cache that says the user is on the paid plan and the database that says they cancelled. None of those bugs exist inside a single transaction. All of them are the routine, expected cost of running six systems — outbox patterns, CDC pipelines and reconciliation jobs are all standard machinery for managing a problem you chose to have.
Start with one database. Add the second system when a measurement, not an architecture diagram, tells you to. You will be astonished how long that takes, and how much time you get back in the meantime.
For us that's a Go gateway and one Postgres: SKIP LOCKED for jobs, tsvector for search, SSE straight off the same connection pool for realtime, and jsonb in the several hundred places where the shape genuinely varies. Xenition is a full AI workspace — documents, spreadsheets, decks, boards, team chat, an app builder — and none of that has yet produced a number that says add Redis. The day one of the thresholds above trips, we'll add exactly the thing it points at. Not before, and not the other five along with it.
-- Everything above, in one line of philosophy:
BEGIN;
-- the row, the job, the embedding, the counter, the search index
COMMIT;
Top comments (0)