Postgres already does what you are about to install

There is a moment in almost every project when somebody says “we need Redis for the queue”. Then “Elasticsearch for search”. Then a vector database. Each one solves a real problem, and each one adds a service that has to be deployed, monitored, backed up, upgraded and kept consistent with the data that already lives somewhere else.

Many of those needs are covered by Postgres, which is already installed and which you already have backups of. This article shows the concrete SQL for four cases, and where the real limit of each one is.

A job queue, with SKIP LOCKED

The most common case. The table:

CREATE TABLE jobs (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  type       text        NOT NULL,
  payload    jsonb       NOT NULL,
  status     text        NOT NULL DEFAULT 'pending',
  attempts   int         NOT NULL DEFAULT 0,
  run_at     timestamptz NOT NULL DEFAULT now(),
  error      text
);

-- Partial index: it only indexes what is pending, which is all that gets
-- queried. When a job completes, it leaves the index on its own.
CREATE INDEX jobs_pending
  ON jobs (run_at)
  WHERE status = 'pending';

And the part that matters, taking the next job:

UPDATE jobs
SET    status = 'processing',
       attempts = attempts + 1
WHERE  id = (
         SELECT id
         FROM   jobs
         WHERE  status = 'pending'
           AND  run_at <= now()
         ORDER  BY run_at
         FOR UPDATE SKIP LOCKED     -- ← this is the whole thing
         LIMIT  1
       )
RETURNING *;

FOR UPDATE SKIP LOCKED is the instruction that makes this viable. Without it, ten worker processes compete for the same row: nine wait for the first one to finish its transaction, and the queue processes one job at a time. With it, each process that finds a locked row skips it and takes the next one. All ten work in parallel without coordinating with each other.

Retries with growing backoff are one line:

UPDATE jobs
SET    status = 'pending',
       run_at = now() + (interval '1 minute' * power(2, attempts)),
       error = $2
WHERE  id = $1;

And unlike an external queue, enqueuing the job and changing your data happen in the same transaction. If the operation fails, the job is not left in the queue. With Redis or RabbitMQ that has to be solved separately, and it is a well known source of inconsistency.

Full-text search

Postgres has a search engine with stemming built in. The modern way to use it is with a generated column, which maintains itself:

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

CREATE INDEX articles_search ON articles USING GIN (search);

setweight marks how important each part is: a match in the title counts for more than one in the body. And 'english' turns on the language’s stemming, so searching for “query” finds “queries” and “querying”.

For the query itself, use websearch_to_tsquery and not to_tsquery:

SELECT   title,
         ts_rank(search, q) AS relevance,
         ts_headline('english', body, q) AS snippet
FROM     articles, websearch_to_tsquery('english', $1) q
WHERE    search @@ q
ORDER    BY relevance DESC
LIMIT    20;

websearch_to_tsquery accepts what people actually type: quotes for exact phrases, or, and -word to exclude. to_tsquery demands operator syntax and throws an error on malformed input, which with text coming from a user is all the time.

ts_headline returns the snippet with the terms highlighted, which is what you show in the results.

Cache, with unlogged tables

For computed values that can afford to be lost:

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

CREATE INDEX ON cache (expires_at);

UNLOGGED is the key word. A table like this does not write to the write-ahead log, which is the most expensive part of every write. In exchange, it is emptied if the server crashes. For a cache that is not a limitation: it is exactly the correct behaviour.

Reading and writing:

-- Read, ignoring anything expired
SELECT value FROM cache WHERE key = $1 AND expires_at > now();

-- Write or replace
INSERT INTO cache (key, value, expires_at)
VALUES ($1, $2, now() + interval '15 minutes')
ON CONFLICT (key) DO UPDATE
  SET value = excluded.value, expires_at = excluded.expires_at;

Cleanup goes in a periodic job: DELETE FROM cache WHERE expires_at < now();

Events between processes

When one process needs to tell another that something happened, without polling the database every second:

-- The listener
LISTEN new_orders;

-- The notifier, inside the same transaction that made the change
NOTIFY new_orders, '{"order_id": 4821}';

The notification is only delivered if the transaction commits, which removes the case of announcing something that was later rolled back.

Combined with the queue above, worker processes stop querying every second and react to the notification instead. The baseline load on the database drops noticeably in systems with many idle workers.

Semantic search

This is the newest one. PostgreSQL 18 brought the vector type and the HNSW and IVFFlat indexes into the engine; on earlier versions you had to install the pgvector extension.

It consists of turning each text into a list of numbers representing its meaning — an embedding — and searching for the closest ones:

CREATE TABLE documents (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  project_id bigint NOT NULL REFERENCES projects(id),
  body       text NOT NULL,
  embedding  vector(1536) NOT NULL
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

And here is the advantage over a separate vector database:

SELECT   d.body, d.embedding <=> $1 AS distance
FROM     documents d
JOIN     projects p ON p.id = d.project_id
WHERE    p.customer_id = $2             -- permissions, with ordinary keys
  AND    d.created_at > $3
ORDER    BY distance
LIMIT    10;

The relational filter and the similarity search happen in the same query. With a separate service, this is two round trips and a join in the application code: fetch the hundred most similar, filter by permissions, and hope ten are left.

Where the limit is

This is not “Postgres for everything forever”. The points where it stops being enough:

The queue, once you pass a few thousand sustained jobs per second, or when you need several independent consumers reading the same stream. That is where Kafka or something like it belongs, because that is what they are built for.

The cache, when you need sub-millisecond reads under heavy concurrency. Postgres has to go through its transaction layer even on an unlogged table; Redis lives in memory and does not.

Search, when you need typo correction, configurable synonyms or complex aggregations over the results.

Vectors, once you pass a few tens of millions and need to shard the index across several machines.

The rule I use: do not add a service until you can name the number that forces you to. If you have not measured it, you do not need it yet — and every service you add is one more thing that can fail at three in the morning.