PostgreSQLjsonbginfull-text searchtsvectorextensions

JSONB, Full-Text Search and Extensions

JSONB gives you a document store inside a relational one, full-text search gives you a credible search engine, and extensions like pgvector, PostGIS and pg_stat_statements are why "just use Postgres" is so often the right answer.

Interview question See how this works internally →

Progress

JSONB: when and how

A jsonb column stores a parsed, binary JSON document. Query inside it with ->, ->>, @> (contains), ? (has key), and jsonb_path_query. A GIN index on the column makes containment queries fast; an expression index on (data->>'tenant_id') makes a specific path as fast as a real column.

Use it for genuinely variable data: per-integration settings, event payloads whose shape you do not control, user-defined attributes. Do not use it for data you know the shape of — columns are typed, constrained, smaller, faster and visible to the planner. The failure mode is a data jsonb column that slowly becomes the whole schema, unindexed, unvalidated and unjoinable. Promote a key to a column the moment you filter or join on it regularly.

Indexed JSONB

1CREATE TABLE events (id bigint PRIMARY KEY, payload jsonb NOT NULL);
2CREATE INDEX events_payload ON events USING gin (payload);            -- containment on anything
3CREATE INDEX events_tenant ON events ((payload->>'tenant_id'));       -- one hot path
4 
5SELECT * FROM events WHERE payload @> '{"type": "order_paid"}';       -- uses GIN
6SELECT * FROM events WHERE payload->>'tenant_id' = '42';              -- uses the expression index

Full-text search

to_tsvector('english', body) tokenises, stems and strips stop words; to_tsquery('index & scan') is a boolean query over those tokens; @@ matches; ts_rank scores. Store the tsvector in a generated column and index it with GIN, and you have search with stemming, ranking, phrase queries and multiple languages, transactionally consistent with the rest of your data, at zero extra infrastructure.

What it does not do as well as a search engine: typo tolerance (add pg_trgm for that), faceting at scale, relevance tuning, very large corpora. The line is roughly: under a few million documents with straightforward queries, Postgres full-text is enough and one fewer system. Beyond that, or when search *is* the product, see SQL vs NoSQL: Choosing a Data Model.

Search in four lines

1ALTER TABLE articles ADD COLUMN search tsvector
2  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;
3CREATE INDEX articles_search ON articles USING gin (search);
4 
5SELECT title, ts_rank(search, q) AS rank
6FROM articles, to_tsquery('english', 'index & (scan | seek)') q
7WHERE search @@ q ORDER BY rank DESC LIMIT 10;

Extensions worth knowing

pg_stat_statements: per-query statistics — the first thing to enable on any instance. pgvector: embedding storage and ANN indexes, see Vector Search: Embeddings, Similarity and ANN. PostGIS: geospatial types, indexes and functions that make Postgres a serious GIS. pg_trgm: trigram similarity for fuzzy matching and LIKE '%x%' indexing. pg_partman: partition management. timescaledb: time-series chunking and compression on top of ordinary tables. postgres_fdw: query another Postgres as if it were local.

The pattern: PostgreSQL absorbs adjacent workloads — documents, search, vectors, time series, geo — well enough that the second system is rarely justified until the workload is measurably beyond what one instance can do. That is the real meaning of "just use Postgres".

Key points

JSONB for variable-shape data, GIN or expression-indexed; promote hot keys to columns.
Full-text search with a stored tsvector and a GIN index covers most search needs under a few million documents.
Extensions turn Postgres into a vector store, a GIS, a time-series database — usually well enough.

When to use — and when not

Use it when

Variable payloads, search over your own tables, embeddings next to the rows they describe.

Avoid it when

JSONB for known, relational data. Full-text for a search product with relevance tuning at scale.

Failure modes

A jsonb column that becomes the schema.
Unindexed JSONB paths in WHERE clauses.
Search on a text column with LIKE '%x%' and no trigram index.

See how this works internally →

Descend one layer: the same topic explained from the machinery up.

How PostgreSQL lays out heap pages and TOASTs large values

↓ Database Internals → PostgreSQL Internals: Heap, Tuples, Shared Buffers, WAL, VACUUM

← PostgreSQL: Tables, Types and the Catalog PostgreSQL in Production: Connections, VACUUM, Partitioning, Replication →