Just Use Postgres: Queues, Search, Vectors, and the Case Against Ten Databases

Every few months we inherit a codebase with a familiar shape: a Postgres database for the "real" data, Redis for caching and sessions, RabbitMQ for background jobs, Elasticsearch for search, MongoDB for the parts someone didn't want to write a schema for, and — since the AI wave — Pinecone for embeddings. Six data systems. One product. Usually fewer than a thousand daily active users.

None of those choices was crazy in isolation. Each tool is genuinely good at its job. But the aggregate is a tax that small teams pay every single day: six things to provision, monitor, back up, secure, upgrade, and explain to the next engineer. Six failure modes. Five places where data is a stale copy of the truth that lives in the sixth.

The counter-position has hardened into something close to consensus among people who run systems for a living: start with Postgres for everything, and add a specialized system only when you can articulate — with numbers — why Postgres is no longer enough. This post is our working version of that argument. Not "Postgres is magic," but the specific mechanics: how to run queues, search, vectors, pub/sub, documents, and multi-tenancy in one database, where each approach genuinely holds up, and where it genuinely doesn't.

The complexity tax of polyglot persistence

"Use the best tool for the job" is good advice about tools and bad advice about systems. Every additional datastore costs you in four compounding ways:

1. Operations. Another service to provision, patch, monitor, alert on, and restore at 3 a.m. Managed offerings soften this but don't eliminate it — you still own the failure mode.
2. Consistency. The moment data lives in two systems, you own the synchronization. The search index lags the database. The cache serves a deleted record. The queue processes a job for a row that was rolled back. Whole categories of bugs exist only because of the seam between stores.
3. Transactions — or the lack of them. Postgres gives you an underrated superpower: your job enqueue, your document write, and your business-logic update can commit atomically or not at all. With RabbitMQ next to Postgres, "write the order, then publish the event" is a distributed-systems problem (the outbox pattern exists precisely to paper over it). With the queue in Postgres, it's one COMMIT.
4. Cognition. Every system has its own query language, client library, security model, and folklore. A team of four that's fluent in one database beats a team of four that's mediocre in six.

The complexity tax is not hypothetical. It's the pager, the onboarding doc, and the incident where the cache and the database disagreed about who was an admin.

So: how far does one Postgres actually go?

Job queues: SKIP LOCKED did the hard part

The classic objection to database-backed queues was lock contention — ten workers all fighting to claim the same head-of-queue row. FOR UPDATE SKIP LOCKED (in Postgres since 9.5, so: everywhere) dissolves the problem. A worker locks the rows it claims; other workers skip locked rows instead of blocking on them.

A minimal but production-shaped jobs table:

CREATE TABLE jobs (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  queue      text        NOT NULL DEFAULT 'default',
  payload    jsonb       NOT NULL,
  status     text        NOT NULL DEFAULT 'pending',  -- pending | running | done | failed
  attempts   int         NOT NULL DEFAULT 0,
  run_at     timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Partial index: only pending work is ever scanned for claims.
CREATE INDEX jobs_claim_idx ON jobs (queue, run_at)
  WHERE status = 'pending';

And the claim query — the whole trick in twelve lines:

WITH claimed AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND queue = 'default'
    AND run_at <= now()
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running', attempts = attempts + 1
FROM claimed
WHERE j.id = claimed.id
RETURNING j.id, j.payload;

Each worker atomically claims up to ten jobs nobody else holds. No advisory-lock gymnastics, no polling stampede, and — the part Redis-backed queues can't offer — you can enqueue a job inside the same transaction as the write that caused it. If the transaction rolls back, the job never existed. The outbox pattern, for free.

Don't hand-roll this in production, though; the failure handling (retries with backoff, dead-lettering, expiry, cron-style scheduling) is where the real work is. The ecosystem has matured nicely: pg-boss and Graphile Worker in Node, Oban in Elixir, River in Go, Solid Queue in Rails, good_job for Ruby. All of them are "just tables" underneath — which means your jobs are queryable with SQL and included in your backups.

When a real broker earns its place: sustained throughput in the many-thousands-of-jobs-per-second range, fan-out to many heterogeneous consumers, cross-language routing topologies, or replayable event streams (that last one is Kafka's job, and Postgres is not Kafka). Below that, a queue table is simpler and more correct.

Full-text search: tsvector gets you further than you think

Postgres has had real full-text search — stemming, ranking, phrase queries, multiple languages — for nearly two decades. The modern setup is a generated column plus a GIN index, so the search vector maintains itself:

ALTER TABLE articles
  ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

CREATE INDEX articles_search_idx ON articles USING gin (search);

Querying, with websearch_to_tsquery so users can type Google-ish syntax ("exact phrase", -excluded, or) without you writing a parser:

SELECT id, title,
       ts_rank(search, query) AS rank
FROM articles,
     websearch_to_tsquery('english', 'postgres skip locked -mysql') AS query
WHERE search @@ query
ORDER BY rank DESC
LIMIT 20;

Add the pg_trgm extension and a trigram GIN index for fuzzy matching and typo tolerance on names and titles. For most products — admin panels, docs sites, SaaS dashboards, marketplaces under a few million rows — this is honestly the whole feature, shipped in an afternoon, always in sync with the source data because it is the source data.

When Elasticsearch or Typesense earns its place: search is the product. You need sophisticated relevance tuning (BM25 variants, per-field boosting matrices, learning-to-rank), faceted navigation over dozens of attributes at high cardinality, instant-as-you-type across tens of millions of documents, or linguistic features Postgres's dictionaries don't cover. Typesense and Meilisearch are the pragmatic middle tier — dramatically less operational weight than Elasticsearch — but even they are a second system with a sync pipeline. Make them prove they're worth it against a query you've actually failed to make fast in Postgres.

Vector search: pgvector is the default now

The 2023-era reflex — "embeddings, therefore a dedicated vector database" — has aged badly. pgvector with HNSW indexes handles the retrieval workloads most products actually have, and it lives next to the data you're going to filter, join, and permission-check against anyway.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  doc_id    bigint NOT NULL REFERENCES documents(id),
  content   text   NOT NULL,
  embedding vector(1536) NOT NULL
);

CREATE INDEX chunks_embedding_idx
  ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Nearest-neighbor query, with the <=> cosine-distance operator:

SELECT id, content, embedding <=> $1 AS distance
FROM chunks
WHERE doc_id = ANY($2)          -- pre-filter by permissioned docs
ORDER BY embedding <=> $1
LIMIT 10;

That WHERE clause is the quiet killer argument. In a standalone vector DB, "only search documents this tenant can see" means either post-filtering (and hoping enough results survive) or replicating your permissions model into the vector store's metadata. In Postgres it's a join — and it composes with row-level security (below). Hybrid search is similarly unexciting: run the tsvector query and the vector query, merge with reciprocal rank fusion in a CTE or in application code. No sync pipeline, no second source of truth for your chunks.

Real limits, honestly: HNSW index builds are memory- and time-hungry on large tables (raise maintenance_work_mem, build concurrently). The standard vector type caps indexed dimensions at 2,000 — fine for common embedding models, and halfvec extends the ceiling while halving storage. Recall under heavy filtering needs care (recent pgvector releases added iterative index scans specifically for this). And past roughly the hundred-million-vector scale, or when you need sharded ANN with sub-10ms p99 as the core product, dedicated engines (or purpose-built extensions like VectorChord) are having a different conversation. Most teams reaching for Pinecone have four million vectors and would never have noticed the difference.

Pub/sub: LISTEN/NOTIFY, with eyes open

Postgres ships a pub/sub primitive:

-- session A
LISTEN job_created;

-- session B, inside a transaction
NOTIFY job_created, '42';
COMMIT;  -- notification is delivered only on commit

Delivery-on-commit is a feature — listeners never hear about rolled-back work. Graphile Worker uses exactly this to get near-instant job pickup without tight polling.

But treat it as a doorbell, not a mailbox. The gotchas are real:

• No persistence. If no one is listening when you notify — or a listener is disconnected during a deploy — the message is gone forever. Never send state through NOTIFY; send "something changed, go query."
• Payload cap of roughly 8,000 bytes. Send IDs, not documents.
• Pooler-hostile. LISTEN is session state, so it doesn't survive transaction-mode PgBouncer. Listeners need a dedicated direct connection.
• A global notification queue inside Postgres means very high NOTIFY volume across many committing sessions can serialize on shared locks. It's a signaling mechanism, not an event bus.

The robust pattern is LISTEN/NOTIFY plus a table: writes insert rows (durable), NOTIFY wakes the consumers (fast), consumers read the table (correct), and a slow poll backstops missed signals. When you need replay, consumer groups, or retention — that's Kafka/NATS territory, and no amount of Postgres enthusiasm changes that.

JSONB: the document store you already have

JSONB gives you MongoDB's flexibility inside a transactional database with real joins. Binary storage, rich operators, and GIN indexing:

CREATE INDEX events_props_idx ON events USING gin (properties jsonb_path_ops);

-- Containment queries hit the index:
SELECT * FROM events
WHERE properties @> '{"plan": "pro", "source": "referral"}';

-- Hot key? Promote it to a typed expression index:
CREATE INDEX events_plan_idx ON events ((properties->>'plan'));

jsonb_path_ops produces smaller, faster GIN indexes when you only need containment (@>), which is most of the time.

Our rule: JSONB is for data that is genuinely schemaless or externally shaped — webhook payloads, third-party API responses, user-defined custom fields, event properties, feature-flag blobs. The moment a key is queried in every request, filtered in the UI, or joined against, it wants to be a column: typed, constrained, statistics-friendly to the planner, cheap to index. "We'll figure out the schema later" via JSONB is how you end up rediscovering, in production, why schemas exist. Relational modeling still wins for anything with relationships — which is nearly everything that matters in your app.

Caching: Postgres is faster than your intuition says

The reflexive "we need Redis for caching" often predates measuring anything. A primary-key lookup on a table hot in shared_buffers is sub-millisecond. If your "cache" exists because a query is slow, the first question is why the query is slow — an index, a materialized view, or a denormalized column usually beats a cache and removes an invalidation problem.

For genuine cache tables (computed results, rate-limit counters, sessions), UNLOGGED tables skip write-ahead logging for a substantial write-throughput win, at the cost of being truncated after a crash — exactly a cache's contract:

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

When Redis earns its place: hundreds of thousands of ops/sec against a working set that fits in RAM; sub-millisecond p99 as a hard requirement; the actual data structures — sorted-set leaderboards, HyperLogLog, precise sliding-window rate limiting at the edge. Those are real. "We cache some API responses for 60 seconds" is not; that's a table, or honestly just HTTP caching.

Multi-tenancy: row-level security

Every multi-tenant SaaS has one bug class it fears most: tenant A seeing tenant B's data because one query in ten thousand forgot the WHERE tenant_id =. Row-level security moves that guarantee from "every developer, every query, forever" into the database:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- applies to the table owner too

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

The application sets the tenant once per transaction:

BEGIN;
SET LOCAL app.current_tenant = 'b6f8…';
-- every query in this transaction is invisibly scoped
COMMIT;

SET LOCAL scopes the setting to the transaction, which makes it safe under connection pooling. RLS is also what makes Supabase's client-direct model viable, and it composes with everything above — your vector search and your full-text search are automatically tenant-scoped too. The costs: policies are per-query planner work (keep them simple and indexed — that tenant_id needs an index regardless), and debugging "why is this row invisible" takes a mindset adjustment. Worth it long before you think.

The managed landscape: Postgres as a platform

Part of why "just use Postgres" is now easy advice is that running Postgres well became a product category:

The branching model deserves emphasis: databases-as-cattle for dev/test workflows was the last major ergonomic gap versus "just code," and it's closed. Meanwhile core Postgres keeps compounding — recent major releases brought asynchronous I/O groundwork and steady parallelism and partitioning improvements — and the extension ecosystem (pgvector, PostGIS, pg_trgm, timescaledb, pg_partman) is the real moat.

Scaling, and the honest ceiling

The boring scaling playbook covers a very long runway:

1. One bigger box. Vertical scaling is unfashionable and extremely effective. A modern large instance with NVMe storage handles tens of thousands of transactions per second. Most startups never outgrow step one.
2. Connection pooling. Postgres connections are expensive; serverless functions multiply them. PgBouncer (or your provider's built-in pooler) in transaction mode is near-mandatory. Know the trade: session-level features — LISTEN, session-scoped SET, advisory locks — need dedicated connections.
3. Read replicas for read-heavy fan-out, with the app aware of replication lag (read-your-own-writes goes to the primary).
4. Declarative partitioning for the tables that actually grow without bound — events, logs, jobs history — pruning by time and making retention a DROP TABLE instead of a mega-DELETE.

The ceiling is real, though, and pretending otherwise discredits the whole argument. Postgres is fundamentally a single-primary system: write throughput beyond one machine means sharding (Citus and friends help, but you've left "just Postgres"). Genuine analytics — wide scans, aggregations over billions of rows — wants a columnar engine (ClickHouse, DuckDB over Parquet, a warehouse); run OLAP on your OLTP primary and both will suffer. Replayable event streams want Kafka. Multi-region active-active writes want a system designed for that from birth. And extreme cases of each workload above — search-as-the-product, hundred-million-scale ANN, million-ops-per-second caching — were flagged in their sections.

The point was never that specialized systems are bad. It's that they're specialized — and you should buy the specialization when you have the specialty, not on day one because a conference talk had impressive numbers.

A decision framework

When someone proposes adding a datastore, we make the proposal answer four questions:

1. What's the Postgres version of this, concretely? A queue table, a tsvector column, pgvector, JSONB. If it hasn't been sketched, the comparison is vibes.
2. What number says Postgres fails? Jobs/sec, p99 latency, document count, vector count — measured or credibly projected for the next 12–18 months, not the ten-year dream.
3. What's the full cost of the second system? Provisioning, monitoring, backups, the sync pipeline, the consistency bugs, the onboarding page. Price the seam, not just the service.
4. Is this reversible? A queue table can be swapped for SQS behind an interface in a week. Un-adopting a database you've built features around takes a year. Asymmetric costs deserve asymmetric caution.

Run honestly, this framework usually returns the same answer for a small team: not "Postgres forever" — "Postgres until it isn't, and you'll know, because you'll have a number."

Takeaways

• Every additional datastore is an ops burden, a consistency seam, and a lost transaction boundary. The default number of databases for a new product is one.
• FOR UPDATE SKIP LOCKED makes Postgres a legitimate job queue; libraries like pg-boss and Graphile Worker productionize it, and transactional enqueue is something Redis-backed queues can't give you.
• tsvector + GIN (with websearch_to_tsquery and pg_trgm) covers most product search. Reach for Elasticsearch/Typesense when search is the product, not when it's a feature.
• pgvector with HNSW is the default for embeddings — filtering and permissions become joins instead of a metadata-sync problem. Its limits (index build cost, dimension caps, filtered-recall care, extreme scale) are real but far away for most teams.
• LISTEN/NOTIFY is a doorbell, not a mailbox: pair it with a table, keep payloads to IDs, and remember it doesn't survive transaction pooling.
• JSONB for genuinely schemaless data; columns for anything queried, joined, or constrained. Schema-later is schema-never.
• Measure before adding Redis: an indexed hot-cache table (or UNLOGGED table) is often fast enough, with no invalidation seam.
• Row-level security turns tenant isolation from a per-query convention into a database guarantee — and it scopes your search and vector queries too.
• Scale in order: bigger box → pooling (PgBouncer, transaction mode) → read replicas → partitioning. Shard, or add specialists, when a measured number forces it.
• The honest ceiling exists: multi-node write throughput, columnar analytics, replayable streams, and multi-region active-active are not Postgres jobs. Buy specialization when you have the specialty.