Postgres Features That Replace Extra Services
See how Postgres features like JSONB, full-text search, LISTEN/NOTIFY, SKIP LOCKED, unlogged tables, and pgvector can replace extra services—and when to scale out.
Before you add Redis, a queue broker, a search engine, or a document database, check whether a built-in Postgres feature already covers the job well enough at your scale.
The usual pattern goes like this: one feature needs a cache, another needs background jobs, and the architecture diagram picks up three new boxes, each with its own deploy, backups, and failure modes. Some of those boxes are worth it. Many are not yet.
This article covers six Postgres features that can stand in for a separate service. Each section gives you a small SQL example and says plainly where the dedicated service still does better. If you need a refresher on schema design, indexing, and pooling first, start with these Postgres best practices for modern web apps.
Key Takeaways
- A GIN index on a
jsonbcolumn makes@>containment queries fast, and thejsonb_path_opsoperator class gives a containment-focused index that supports fewer operators than the defaultjsonb_ops. LISTEN/NOTIFYdoes not store messages: a notification goes out only when the sending transaction commits, and if no session is listening at that moment, it is gone.FOR UPDATE SKIP LOCKEDis a row-locking clause, not a filter: each worker locks the job it selects and skips rows another transaction already holds, instead of waiting for them.- Postgres truncates an
UNLOGGEDtable after a crash and does not copy it to standbys, so use it only for data you can rebuild. - Start with the Postgres feature, measure it under real load, and add a dedicated service once you can name the specific limit you hit.
When to Just Use Postgres: A Summary
“Just use Postgres” holds up for small and medium apps when each feature covers the workload you actually have. The table maps each service to its Postgres counterpart and to the signal that it’s time to move on.
| Instead of | Postgres feature | Good enough for | Add the service when |
|---|---|---|---|
| Document database | jsonb + GIN | Flexible attributes, containment queries | Very high write volume, built-in sharding |
| Search engine | tsvector + GIN | Keyword search with ranking | Typo tolerance, facets, relevance tuning |
| Pub/sub broker | LISTEN/NOTIFY | Cache invalidation, waking workers | You need durable or replayable messages |
| Job queue | FOR UPDATE SKIP LOCKED | Background jobs, moderate volume | Fan-out, replay, very high throughput |
| Cache | UNLOGGED table | Rebuildable, expiring data | You need in-memory latency or data structures |
| Vector database | pgvector | Embeddings next to relational rows | Vector workloads outgrow one node |
JSONB and GIN Instead of a Document Database
A jsonb column with a GIN index lets Postgres store schemaless documents and answer containment queries (“does this document include these key/value pairs?”) through the index. The jsonb indexing docs describe two operator classes. The default is jsonb_ops. The alternative, jsonb_path_ops, supports fewer operators but is a good fit when your queries only use containment.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL
);
-- jsonb_path_ops supports fewer operators than the default jsonb_ops
CREATE INDEX events_payload_idx ON events USING gin (payload jsonb_path_ops);
EXPLAIN
SELECT id FROM events
WHERE payload @> '{"type": "signup", "plan": "pro"}';
Before you choose jsonb_path_ops, check which operators your queries use. Use a dedicated document store instead when write volume gets very high or you need built-in sharding across nodes.
Full-Text Search With tsvector and GIN
Postgres full-text search turns text into normalized lexemes (a tsvector) and matches them against a parsed query (a tsquery). A STORED generated tsvector column keeps the search document in sync with its source columns on every insert and update, with no trigger to maintain. The expression has to name a configuration such as 'english'. As the text search tables docs explain, indexed text search expressions can’t depend on default_text_search_config.
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);
SELECT id, title, ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;
STORED is required here because the generated tsvector column is indexed. Since PostgreSQL 18, generated columns default to virtual, which means they are computed when read. The text search controls handle the rest: websearch_to_tsquery accepts the kind of text users type into a search box, setweight labels title lexemes A and body lexemes B, and ts_rank scores matches using those weights.
Postgres full-text search matches stemmed words, not misspellings. The pg_trgm module adds similarity matching on character trigrams through the % operator and a gin_trgm_ops index, which covers part of the gap. Synonyms are possible with synonym and thesaurus dictionaries, but those need configuration files on the server. Choose a dedicated search engine when you need faceting, solid typo tolerance, or relevance tuning without editing SQL.
LISTEN/NOTIFY Instead of a Pub/Sub Broker
LISTEN/NOTIFY is a lightweight signal channel between database sessions. It works well for cache invalidation and for waking idle workers. It does not store messages. According to the NOTIFY reference:
- A notification is delivered only when the sending transaction commits.
- Sessions that aren’t listening at that moment never receive it.
- By default the payload must be shorter than 8000 bytes.
-- Listener session (kept open)
LISTEN cache_invalidation;
-- Sender, inside any transaction
SELECT pg_notify('cache_invalidation', 'product:42');
With NOTIFY, send an identifier rather than the whole row. That keeps the payload under the size cap, and the receiver reads fresh data anyway. A LISTEN session has to stay connected, so it doesn’t belong in a serverless route handler. Run the listener in a long-lived Node worker instead. PgBouncer’s transaction pooling mode does not support LISTEN, so connect the listener directly or through a session-mode pool. Choose a real broker when messages must survive restarts, be replayed, or reach consumers that are offline.
A Job Queue With SKIP LOCKED
FOR UPDATE SKIP LOCKED is a row-locking clause, not a filter. Each worker locks the job it selects and skips rows another transaction has already locked, instead of waiting for them. The locking clause docs note that this gives an inconsistent view of the data by design, which suits queue-like tables. For the concepts around workers and retries, see job queues explained.
CREATE TABLE jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
kind text NOT NULL,
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'queued',
created_at timestamptz NOT NULL DEFAULT now(),
finished_at timestamptz
);
CREATE INDEX jobs_queued_idx ON jobs (id) WHERE status = 'queued';
BEGIN;
SELECT id, kind, payload
FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- application runs the job using the returned id
UPDATE jobs SET status = 'done', finished_at = now() WHERE id = $1;
COMMIT;
If the worker crashes before COMMIT, the transaction rolls back, the lock is released, and the job becomes available again. The partial index keeps the claim query small as finished jobs pile up. The catch is that this SKIP LOCKED version holds a transaction open for the whole job. For long jobs, claim the row by setting status = 'running' with a timestamp, commit, and finish in a second transaction. You then need a sweeper to reset stale running rows. Choose a dedicated queue when you need fan-out to many consumers, message replay, or very high throughput.
UNLOGGED Tables for Cache-Like Data
An UNLOGGED table skips the write-ahead log, so writes to it cost less. The CREATE TABLE docs list the trade-off: Postgres truncates an unlogged table after a crash or unclean shutdown and does not replicate it to standbys. That makes an UNLOGGED table suitable only for data you can rebuild from another source.
CREATE UNLOGGED TABLE cache_entries (
key text PRIMARY KEY,
value jsonb NOT NULL,
expires_at timestamptz NOT NULL
);
CREATE INDEX cache_entries_expires_idx ON cache_entries (expires_at);
-- Write
INSERT INTO cache_entries (key, value, expires_at)
VALUES ($1, $2, now() + interval '10 minutes')
ON CONFLICT (key) DO UPDATE
SET value = EXCLUDED.value, expires_at = EXCLUDED.expires_at;
-- Read
SELECT value FROM cache_entries WHERE key = $1 AND expires_at > now();
-- Cleanup, run on a schedule
DELETE FROM cache_entries WHERE expires_at < now();
The ON CONFLICT upsert refreshes an existing key in one statement. The read checks expires_at because cleanup runs periodically, so expired rows can linger between runs. Trigger the cleanup from your app’s scheduler. Redis is the better choice when you need sub-millisecond latency or native data structures such as sorted sets and counters. How key-value databases work covers why.
pgvector Instead of a Separate Vector Database
pgvector adds a vector column type and similarity search to Postgres, so embeddings can sit next to the rows they describe. Choose a dedicated vector database when your vector workloads outgrow one node. Implementing vector search in Postgres walks through the setup.
A Rule of Thumb for Adding Services
Treat each Postgres feature here as the default and a dedicated service as an upgrade you justify with evidence:
- Build the feature in Postgres first.
- Measure it under real load with
EXPLAIN ANALYZEandpg_stat_statements. - Add the service once you can name the limit you hit: notifications that must survive restarts, search that needs typo tolerance, cache latency, or write volume.
As a next step, take the service you were about to add, find its row in the summary table, and build the Postgres version first. If it falls short, your measurements will show exactly what the new service needs to fix.
FAQs
Should I use json or jsonb for document data in Postgres?
Use jsonb for anything you query. The json type stores an exact text copy of the input, keeping whitespace, key order, and duplicate keys, and reparses it on every access. The jsonb type stores a decomposed binary form, drops whitespace, does not preserve key order, and keeps only the last value of a duplicate key. GIN indexes and the @> containment operator work with jsonb.
Does an UNLOGGED table lose its data on a normal Postgres restart?
No. Postgres truncates an unlogged table only after a crash or unclean shutdown. After a clean shutdown and restart, its contents persist. You can convert an existing table with ALTER TABLE name SET LOGGED or SET UNLOGGED. Switching to LOGGED rewrites the table and writes its full contents to the WAL, which can be expensive on large tables.
Does Postgres deliver duplicate NOTIFY messages sent in the same transaction?
No. If one transaction signals the same channel more than once with an identical payload, listeners receive only one notification. Notifications with distinct payloads are always delivered separately, and notifications from different transactions are never folded together. Apart from dropped duplicates, notifications from one transaction arrive in the order they were sent, so repeated invalidation signals for the same key are safe to emit from triggers.
Can LISTEN/NOTIFY replace polling in a SKIP LOCKED job queue?
Partly. A trigger on the jobs table can call pg_notify after each insert, and because notifications go out only on commit, workers never wake for an insert that rolled back. Woken workers then run the SKIP LOCKED claim query. Keep a slow polling interval as a fallback, because a worker that is disconnected when the notification fires never receives it.
Gain Debugging Superpowers
Unleash the power of session replay to reproduce bugs, track slowdowns and uncover frustrations in your app. Get complete visibility into your frontend with OpenReplay — the most advanced open-source session replay tool for developers.
Star on GitHub12k