Postgres for Everything: Queues, Search, Vectors and More

Most startups run five pieces of infrastructure to do what one Postgres instance handles fine: Redis for caching and queues, Elasticsearch for search, Pinecone for vectors, RabbitMQ for messaging, and Postgres for the "real" data. Each extra system is another failure mode, another consistency gap, another thing to monitor at 3 AM. The boring alternative usually wins, and this post shows you the mechanisms, the configs, and the exact tripwires that tell you when to stop.

The real cost of a five-database stack

Every additional stateful system in your architecture costs you three things, and none of them show up in the pricing calculator.

• Consistency. The moment your job queue lives outside your database, "insert the order and enqueue the confirmation email" becomes a distributed transaction. You will either drop jobs or double send them. The outbox pattern exists precisely to paper over this, and it is real engineering effort.
• Operations. Each system needs backups, upgrades, failover plans, monitoring dashboards, and someone who understands its failure modes. A team of four does not have that someone for five systems.
• Cognition. Every service adds a client library, a retry policy, a serialization format, and a mental model. That is surface area for bugs, and for security holes, a theme we cover in API security best practices.

Postgres will not beat a specialized system at its own game at scale. It does not have to. It has to be good enough at the scale you actually run, which for most products is thousands of jobs per minute, single digit millions of documents, and a few million vectors. It is.

Job queues: SKIP LOCKED is the whole trick

The classic objection to database queues was lock contention: ten workers all grab the same row, nine wait. Postgres solved this in 9.5 with FOR UPDATE SKIP LOCKED. A worker locks the rows it claims, and every other worker silently skips them instead of blocking.

WITH job AS (
  SELECT id FROM jobs
  WHERE status = 'pending' AND run_at <= now()
  ORDER BY priority DESC, run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs SET status = 'running', started_at = now()
FROM job WHERE jobs.id = job.id
RETURNING jobs.*;

That single statement is an atomic claim. If the worker dies mid job, the transaction rolls back or a sweeper resets stale running rows. Because the queue lives in the same database as your data, enqueueing a job inside a business transaction is just another insert. Commit them together or not at all. No outbox, no dual writes.

You do not even need to build this yourself. Solid libraries exist per ecosystem: Graphile Worker for Node, Oban for Elixir, GoodJob and Solid Queue for Rails, River for Go, pgboss for serverless-ish Node setups. In our experience these comfortably handle thousands of jobs per minute on a modest instance.

Two operational notes, because this is where teams get burned:

• High churn queue tables generate dead tuples fast. Set aggressive per table autovacuum settings, something like autovacuum_vacuum_scale_factor = 0.01, or the table bloats and claim queries slow down.
• Keep the jobs table lean. Move completed jobs to an archive table or delete them on a schedule with pg_cron. A queue table with 50 million done rows is a self inflicted wound.

Pub/sub: LISTEN and NOTIFY, with honest caveats

NOTIFY fires a message on commit, LISTEN receives it on any connected session. It is perfect for "wake up, there is work" signals that let queue workers poll lazily instead of hammering the table.

The caveats matter. Notifications are fire and forget: if no one is listening, the message is gone. Payloads cap at about 8 KB. Listeners hold a dedicated connection, which fights with connection poolers in transaction mode. So use NOTIFY as a doorbell, never as the source of truth. The truth is the row in the jobs table; the notification just saves you a polling interval. If you need durable fan out to many consumers with replay, that is a genuine event streaming problem, and we walk through when that jump is justified in event-driven architecture.

Full text search: further than you think

Postgres has had real full text search for two decades: tsvector for indexed documents, tsquery for queries, GIN indexes to make it fast, ts_rank for relevance, and dictionaries for stemming in dozens of languages, French and Arabic included, which matters to the multilingual products we build in Tunisia.

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

CREATE INDEX articles_search_idx ON articles USING GIN (search);

SELECT id, title, ts_rank(search, query) AS rank
FROM articles, websearch_to_tsquery('french', 'migration cloud') query
WHERE search @@ query
ORDER BY rank DESC LIMIT 20;

websearch_to_tsquery accepts Google style input, quotes and minus signs included. Add pg_trgm with a GIN trigram index and you get typo tolerant fuzzy matching and fast ILIKE '%term%' queries on top. For faceted search, facets are just GROUP BY counts, and they are transactionally consistent with the documents, something Elasticsearch cannot promise without careful reindexing pipelines.

Where Postgres search genuinely loses: sophisticated relevance tuning (BM25 style scoring, per field boosting matrices, learning to rank), heavy aggregation analytics over tens of millions of documents, and search as the primary workload of the product. If search is your product, take Elasticsearch, Typesense or Meilisearch. If search is a feature, Postgres is usually the right call, and you skip an entire synchronization pipeline whose failure mode is silently stale results.

Vectors: pgvector is the default now

Semantic search and RAG pushed everyone toward dedicated vector databases around 2023. The pendulum has swung back. pgvector gives you a vector column type, cosine and L2 distance operators, and two index types. The decision between them is the part that matters:

• IVFFlat: faster to build, smaller, but recall depends on how well your data matches the clustering, and you must rebuild lists as data grows.
• HNSW: slower to build and bigger in memory, but better recall and stable query latency as the table grows. Default to HNSW.

CREATE TABLE chunks (
  id bigserial PRIMARY KEY,
  document_id bigint REFERENCES documents(id),
  content text,
  embedding vector(1536)
);

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

SET hnsw.ef_search = 100;
SELECT content FROM chunks
ORDER BY embedding <=> $1 LIMIT 10;

The killer feature is not the index. It is that your vectors live next to your relational data, so "top 10 chunks by similarity, but only from documents this tenant owns, joined with document metadata" is one SQL query with your existing row level security applied. In a dedicated vector store, that filter is a second system to keep in sync and a common source of multi tenant data leaks. Watch memory: HNSW indexes want to fit in RAM, so a few million 1536 dimension vectors need real instance sizing, and half precision quantization in recent pgvector versions helps. We go deeper on the decision in choosing a vector database and on the surrounding pipeline in RAG systems explained.

The rest of the toolbox

• JSON documents. jsonb with GIN indexes covers most "we need MongoDB" arguments. Store flexible attributes in a jsonb column, keep the fields you query and join on as real typed columns. Schema on write for what matters, schema on read for the rest.
• Scheduled jobs. pg_cron runs SQL on a schedule inside the database: nightly archival, materialized view refreshes, queue cleanup. One less place to configure cron.
• Caching. An UNLOGGED table skips WAL writes and survives as a fast, truncate on crash key value store. It will not match Redis on raw latency, but it removes a network hop worth of architecture for session and computed value caching.
• Analytics. Materialized views for dashboards, pg_stat_statements for finding the queries that hurt. When analytical scans start fighting your transactional workload, a read replica dedicated to reporting is the first move, not a warehouse.

Where this strategy breaks

Honesty is the point of this framework, so here are the real tripwires:

• Write heavy queues beyond roughly 5,000 to 10,000 jobs per minute sustained, in our experience, start to demand vacuum babysitting that a purpose built broker does not. Kafka style replayable streams are simply a different data structure; Postgres is not a log.
• Sub millisecond cache reads at high concurrency. Redis exists for a reason. Postgres round trips are fast, but not that fast.
• Search as the product, with relevance tuning as a competitive advantage.
• Connection pressure. Every trick above multiplies connections. PgBouncer in transaction mode is mandatory hygiene, and LISTEN needs session mode connections handled separately.
• One shared blast radius. If the queue melts down, it can starve the OLTP workload. Mitigate with separate instances per concern (still just Postgres, twice) before reaching for new technology. That is also the moment to reread database scaling patterns.

A decision checklist before you add infrastructure

1. Write down the actual numbers. Jobs per minute, documents indexed, vectors stored, p95 latency target. Not the numbers you hope for at Series C.
2. Prototype it in Postgres first. SKIP LOCKED queue, tsvector search, pgvector index. This is usually a day or two of work.
3. Load test at 10x your current volume, with autovacuum monitored and pg_stat_statements on. Find the ceiling empirically.
4. Define the exit tripwire. "We move search to Typesense when the corpus passes 20 million documents or relevance tuning becomes a roadmap item." Written down, agreed, revisited quarterly.
5. Only then provision the new system, with the migration path you already understand because the data model lived in SQL.

This is not dogma, it is sequencing. Specialized systems are phase two tools. Buying them in phase one means paying phase two costs with phase zero revenue.

How Innovation T can help

Innovation T is a software and cloud engineering studio in Sousse, Tunisia, and this consolidation play is bread and butter for us: we design Postgres backed queues, search, and RAG pipelines for clients across Europe and North Africa, tune the autovacuum and indexing details that make them boring in production, and define the tripwires that tell you when a specialized system has earned its place.

If your architecture diagram has more databases than engineers, we should talk. See what we build on our services page, or contact us for a blunt review of your stack.