Postgres as Your Queue, Cache, Search and Vector Store

Q: Can Postgres really replace Redis, Elasticsearch and a vector database?

For most products, below a scale ceiling that is higher than people expect, yes — and it is usually the correct architectural call rather than a compromise. A single PostgreSQL instance can run a durable job queue using SELECT FOR UPDATE SKIP LOCKED, an SQS-like message queue via the PGMQ extension, full-text search with pg_search’s BM25 relevance, vector similarity search with pgvector, a cache using unlogged tables, in-process pub/sub with LISTEN/NOTIFY, and a document store with JSONB and GIN indexes. The important caveat is that a dedicated system will always beat Postgres at its single specialised job — Redis is faster for pure caching, Elasticsearch scales full-text further, a dedicated ANN store handles more vectors. The reason to consolidate anyway is not raw performance but drastically fewer moving parts: one database to back up, secure, monitor, upgrade and reason about, instead of five systems each with its own client library, failure mode, security surface and bill. You replace those systems when a measured limit for that specific workload demands it, not on day one and not on faith.

Q: What is the real advantage of consolidating onto one database?

The advantage is twofold: fewer operational surfaces, and transactional consistency across roles that a polyglot stack cannot provide. On the operational side, every additional data system is a standing burden — another thing to back up and restore, another set of credentials and network paths to secure, another client to keep patched, another failure mode to understand at 3am, and another line on the bill. Collapsing five lightly-loaded systems into one well-run Postgres removes four of each of those. On the consistency side, because the queue, the cache, the search index and the relational data all live in the same database, they participate in the same transactions. When you enqueue a job with SKIP LOCKED in the same transaction that updates a row, the job cannot exist for a row that rolled back — the enqueue and the update commit or abort together. A separate Redis queue or external broker simply cannot give you that guarantee; you end up writing compensating logic to handle the cases where the two systems disagree. Consolidation turns a class of distributed-systems bugs into a non-problem, which for a small team is often worth more than the raw throughput a specialised system would add.

Q: When should I NOT use Postgres for a queue, cache or search?

Each role has a distinct exit signal, and the engineering skill is knowing it before you hit it. For a job queue on SKIP LOCKED, Postgres is comfortable into the low thousands of jobs per second; you move to a dedicated broker when you need sustained very high throughput or heavy fan-out to many independent consumers, or when the queue table’s write churn starts thrashing autovacuum and stealing I/O from your OLTP traffic. For caching with unlogged tables, Postgres is fine when the cache can share the database’s durability story and your hit-rate and latency needs are modest; you add Redis for a specific path when you need sub-millisecond p99 at high QPS or sophisticated eviction policies. For full-text search, pg_search’s BM25 is genuinely Elastic-quality for most corpora; you move to Elasticsearch or OpenSearch at billions of documents, complex analyzer pipelines, or cluster-scale query fan-out. For vector search, pgvector is excellent to roughly single-digit-million vectors with SQL filtering, and you move to a dedicated ANN store at hundreds of millions of vectors or when you need fine ANN recall tuning. The wrong answer in every case is to push past the signal dogmatically because "one database is elegant" — elegance that misses the SLA is a failure.

Q: How does SKIP LOCKED make Postgres a good job queue?

SELECT FOR UPDATE SKIP LOCKED is the mechanism that lets many workers pull from the same queue table concurrently without blocking each other or double-processing a job. When a worker runs a SELECT that locks the next available row FOR UPDATE with SKIP LOCKED, Postgres hands it a row that no other worker currently holds a lock on and skips over rows that are already locked, so two workers polling simultaneously each get a different job rather than contending on the same one. Because this all happens inside a normal database transaction, the dequeue, the work’s side effects on other tables, and the mark-as-done can commit atomically — if the worker crashes mid-job the transaction rolls back and the job becomes available again, giving you at-least-once processing for free. That transactional coupling is the feature a separate queue cannot match: your business writes and your queue state are always consistent because they are the same commit. The honest limits are that this is polling-based, so very high throughput means tuning poll frequency and batch size, and that a high-churn queue table needs partitioning and autovacuum attention to avoid bloat. For the throughput most applications actually have, it is simpler and safer than standing up a separate broker.

Q: Is pgvector good enough for production vector search?

For the corpus sizes most products actually have, yes. pgvector is a mature, widely-deployed extension that adds vector similarity search to Postgres with both IVFFlat and HNSW approximate-nearest-neighbour indexes, exact search when you need it, and a halfvec half-precision type that roughly halves the working set and lets you index higher dimensionalities. The decisive advantage over a standalone vector database is not speed at the extreme but that your vectors live next to your relational data, so you can filter a similarity search with ordinary SQL WHERE clauses on the same row — by tenant, by permission, by recency — in one query, with one consistency model, one backup and one security boundary. A dedicated ANN store forces you to either duplicate that metadata or run a two-system join, and to keep two systems consistent. pgvector is comfortable to roughly single-digit-million vectors on a well-provisioned node; the exit signal is hundreds of millions of vectors, or a need for ANN recall tuning and index-build parallelism beyond what a shared OLTP node can spare. Below that threshold, adding a separate vector database is usually solving a scaling problem you do not have while taking on an operational and consistency cost you definitely do.

Q: What changed in PostgreSQL 18 that matters for consolidation?

PostgreSQL 18, released on 25 September 2025, is significant for the consolidation case because its headline feature directly targets the scan-heavy, I/O-bound workloads that piling many roles onto one node tends to produce. The new asynchronous I/O subsystem allows Postgres to issue multiple I/O requests concurrently rather than waiting on each in turn, and the project reports up to roughly 3× improvement when reading from storage for operations like sequential scans, bitmap heap scans and vacuum. That directly lifts the ceiling on a consolidated node, where full-text reindexing, vector index builds and analytical scans all compete for storage bandwidth alongside OLTP traffic. Other 18 features help too: skip-scan support lets multicolumn B-tree indexes be used in more cases, uuidv7() generates timestamp-ordered identifiers that index better than random UUIDs, virtual generated columns compute on read, and faster major-version upgrades with retained optimizer statistics reduce the disruption of staying current. None of these turn Postgres into a system that scales infinitely, but together they push the point at which you must split a workload off meaningfully further out, which is exactly the margin a consolidation strategy trades on.

Q: Is running everything on one Postgres a single point of failure?

It concentrates risk, and that is a real consideration you manage rather than a reason to avoid consolidation. The counter-argument is that five systems are five single points of failure, each with its own probability of an outage, its own on-call runbook and its own blast radius, and the combined probability that something in a five-system stack is broken at any given moment is higher than for one well-run database. Consolidation trades many partial failure modes for one well-understood one, and Postgres has decades of mature tooling for making that one robust: streaming replication with fast failover, point-in-time recovery, synchronous replicas for zero-data-loss, and read replicas to shed read load. The disciplines that matter are treating the database as tier-zero infrastructure — tested backups, a rehearsed failover, connection pooling so a connection storm cannot take it down, and workload isolation so a heavy vector rebuild cannot starve OLTP latency. If two workloads have genuinely different availability or latency SLAs, you can still keep them on separate Postgres instances rather than separate technologies, preserving the operational simplicity of one system to know deeply while separating the blast radius. The single-point-of-failure objection is valid but is answered by operational maturity, not by adding more heterogeneous systems.

Q: How do I decide between Postgres-for-everything and a polyglot stack?

Default to Postgres and make every additional system earn its place with a measured limit, rather than defaulting to polyglot and hoping you need all of it. The decision is per-workload, not all-or-nothing: instrument each role — queue throughput and autovacuum pressure, cache hit rate and p99 latency, search corpus size and query complexity, vector count and recall requirements — and split a workload off only when its own metric crosses the exit signal for that role. This gives you a stack that is the fewest systems that meet the SLA, which for a small or mid-sized product is almost never the five-system default. The two failure modes to avoid sit at opposite ends. One is the polyglot-by-default reflex: standing up Redis, Elasticsearch, a broker and a hosted vector DB at prototype scale, giving four engineers five operational surfaces to run a problem one node could handle. The other is dogmatic over-consolidation: refusing to add Redis for a genuinely latency-critical path because one database is elegant, and missing the SLA as a result. Both are avoided by the same discipline — decide on evidence, re-evaluate as load grows since the right stack at ten thousand users can be wrong at ten million, and treat "add a system" as a decision with a permanent operational cost attached rather than a free default.

Satyam Kumar

返回博客

ai-architecture

Postgres as the Entire Stack: Queue, Cache, Search, Vector — and Where It Stops

By Satyam KumarAugust 13, 20269 min read

postgresql postgres 18 pgvector pg_search pgmq skip locked database consolidation polyglot persistence redis alternative elasticsearch alternative vector database message queue full-text search data architecture backend architecture 2026

Postgres as the Entire Stack: Queue, Cache, Search, Vector — and Where It Stops

Frequently Asked Questions

分享这篇文章

Twitter LinkedIn WhatsApp

Satyam Kumar

Founder & AI Architect, AppScale LLP

人工智能和云架构师。帮助团队构建可扩展到数百万的系统。

LinkedIn GitHub

Postgres as the Entire Stack: Queue, Cache, Search, Vector — and Where It Stops

Frequently Asked Questions

分享这篇文章

Comments

Leave a comment