"Just use Postgres" has been a slogan for a decade, and it's always been a bit annoying, because the people saying it often haven't run the thing under load and are repeating it as a personality trait. I want to say it as someone who has, with the numbers, and with the exact conditions under which I'd stop.
The product is a B2B tool with around 40,000 daily active users, a few hundred tenants, and a handful of background jobs that do the real work. The infrastructure is a Next.js app, a worker service, and one Postgres 18 instance with a replica. There's no Redis and no Kafka. There's no Elasticsearch, no Pinecone and no dedicated job queue either. Each of those exists as a table and an index in the same database that holds the customers.
We decided that deliberately at the start, and looked at it again at every point where it might have turned out wrong. So far it hasn't.
The queue
Background jobs go in a table. A worker claims a batch with FOR UPDATE SKIP LOCKED, processes it and marks it done, all in one transaction.1
The job is enqueued in the same transaction as the business change that caused it, so there's never a moment where the order exists and the job doesn't, or the other way round. If the worker fails halfway through, the transaction rolls back, the row goes back to unclaimed, and the next worker picks it up. Retries, backoff and dead lettering are a few columns and a WHERE clause. At peak this product runs around 200 jobs a second, on a table that's 90 million rows right now with the old ones partitioned off by month, and the claim query takes under a millisecond.
What people expect to be the problem, contention on the queue table, isn't one. SKIP LOCKED was built for exactly this, and the workers are partitioned by a modulo on the id. The real cost is that a job holding a transaction open for a long time holds a connection, and connections are the scarcest thing Postgres has. So jobs that call slow external APIs claim the row and commit, make the call outside any transaction, and write the result in a second transaction. That's the one rule.
The cache
The cache is an UNLOGGED table with a key, a jsonb value and an expiry, plus a partial index on the expiry so the cleanup job can find dead rows quickly.2 Writes get about as fast as a memory store, and the table comes back empty after a crash, which is the right behaviour for a cache.
A read is a primary key lookup, a few hundred microseconds including the network. That's two or three times slower than Redis on the same network. But it runs on the connection the request already holds, so there's no second connection pool, no second way to fail and no second thing to monitor. The hit rate on the hot paths is around 94 percent, and the gap between 200 microseconds and 80 has never shown up in a p99.
I'd move this to Redis if the working set stopped fitting in shared buffers, because then the "cache" is reading from disk and the whole idea falls apart. Right now the working set is 3 GB against 16 GB of shared buffers, and that number is on a dashboard.
Search
Full text search is the built in tsvector with a GIN index, and vector search for the semantic features is pgvector with an HNSW index. For the hybrid case the two are combined in one query with reciprocal rank fusion.3 In short, it's about 40 lines of SQL, and it beats vector only search on any corpus with proper nouns in it.
The document corpus is about 2 million chunks, and the hybrid query comes back in 30 to 60 milliseconds at p99. A dedicated search engine would probably be three times faster. But this updates in the same transaction as the document it indexes, so search is never stale and there's no indexing pipeline to run. And when the product was two years younger and had no search at all, adding it was a migration, not a new piece of infrastructure.
Query latency isn't what would make me move this out. Index build time is. An HNSW index on 2 million vectors rebuilds in about 20 minutes with the parallel builder, and at 20 million it would take hours. If the corpus grows tenfold, the index needs its own machine, and that's a different product from this one.
The scheduler
Cron-style jobs use pg_cron, an extension that runs inside the database and schedules SQL. A job that needs application code enqueues a row in the queue table on a schedule, and the workers pick it up. There's no separate scheduler process to keep alive, no clock skew between scheduler and database, and the schedule lives in a table you can query and change with an UPDATE.
Analytics
Product analytics events go into a partitioned table, one partition per day, with a BRIN index on the timestamp since the data is append only and time ordered. A query over the last 30 days scans 30 partitions and uses the BRIN to skip most blocks. This is the piece closest to its limit, and the first one I'd move.
Analytics queries are big scans, and big scans fight the transactional workload for buffer cache and I/O. Postgres 18's asynchronous I/O made this much better.4 It's still a workload that wants columnar storage and doesn't care about transactions, running on a system built for the opposite.
The signal here is clear. When an analytics query shows up in pg_stat_activity at the same moment as a p99 spike on the API, they're fighting, and it's time. That's happened twice. Both times the fix was moving the heavy query to the replica, which is the first step out, and a cheap one. The second step is DuckDB reading the partitions directly, which is planned for the autumn. The third would be a real warehouse, and I don't think this product will ever need one.
What it costs, and what it saves
The instance is 8 vCPU and 32 GB, with a replica the same size, and it costs a few hundred dollars a month. Going by the quotes I collected while re-examining this, the same setup with a managed Redis, a managed search cluster, a managed queue and a managed warehouse would cost roughly five times that, before any engineering time.
The engineering time is the bigger number. One database means one backup, one restore procedure, one set of credentials, one thing to upgrade, one connection pool and one place to look when something's slow. Every piece of infrastructure you add brings a new way to fail that interacts with the existing ones, and those interactions are where incidents come from. A queue that's a table can't get out of sync with the database, because it is the database.
The three signals
I said I'd be specific about when to stop, so here they are. Each one is a number on a dashboard, not a feeling.
Connections. Postgres processes are expensive and the pool has a limit. Once the app, the workers, the cache reads and the search queries together need more than one instance can serve through the pooler, the first thing to move is whatever holds connections longest, which is usually the queue's slow jobs. We're at about 40 percent of the pooler's capacity at peak.
Buffer cache. Once the working set of any one piece (the cache table, the search index, the hot partitions) grows too big to fit in shared buffers next to the others, that piece is on disk and wants its own memory. The one to watch is the cache table, at 3 GB of 16.
Interference. When a heavy query from one workload lands in the same window as a latency spike in another, they're competing, and the heavy one moves to the replica first and out of the database second. Analytics is the one that does this.
None of the three has crossed the line yet. When one does, one piece moves and the rest stay. That's what "just use Postgres" actually means to me. You can still add things, but every addition has to earn its place with a number, and that number is usually a lot further off than the architecture diagrams make it look.
Originally published at zeybek.dev.
-
I've written about this pattern at length because I built an extension around it, but the reasons it works fit in a paragraph. ↩
-
Unlogged means it skips the write-ahead log. ↩
-
I wrote about that pattern separately. ↩
-
The same 30 day query that took 6 seconds on 17 takes about 2.5 on 18 with
io_method = worker, because the sequential reads are now issued ahead of the consumer. ↩
Top comments (0)