JSONB, full-text, and semantic retrieval

PostgreSQL can hold relational data and JSON documents, perform lexical full-text search, and add vector retrieval through an extension. Co-location does not mean every concern belongs in one column.

When JSONB fits

Good fits: metadata from heterogeneous sources, optional attributes that change infrequently, and integration payloads that must preserve their original shape.

Poor fits: primary and foreign keys, money, authorization boundaries, and core fields used constantly for joins, ordering, or aggregation.

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku text NOT NULL UNIQUE,
  name text NOT NULL,
  attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
  CHECK (jsonb_typeof(attributes) = 'object')
);

INSERT INTO products (sku, name, attributes)
VALUES ('KB-01', 'Keyboard', '{"layout":"75%","wireless":true}');

SELECT id, name
FROM products
WHERE attributes @> '{"wireless":true}';

JSONB indexes

CREATE INDEX products_attributes_gin
ON products USING gin (attributes);

The default GIN operator class supports several key and containment operations. If the workload is almost entirely @>, jsonb_path_ops is often smaller but supports a narrower operator set. Compare with real queries and distributions.

A frequently queried attribute can use an expression index—or graduate into a normal column:

CREATE INDEX products_layout_idx
ON products ((attributes ->> 'layout'));

Built-in full-text search

ALTER TABLE products ADD COLUMN search_document tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(attributes::text, '')), 'B')
  ) STORED;

CREATE INDEX products_search_gin
ON products USING gin (search_document);

SELECT id, name,
       ts_rank(search_document, websearch_to_tsquery('simple', $1)) AS rank
FROM products
WHERE search_document @@ websearch_to_tsquery('simple', $1)
ORDER BY rank DESC
LIMIT 20;

The built-in simple configuration does not fully solve Chinese tokenization. Production Chinese search needs a dedicated segmentation extension, application-side tokenization, or an external search system.

Semantic retrieval with pgvector

Vectors are not a PostgreSQL core type. A common approach is the independent pgvector extension:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document_chunks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id bigint NOT NULL,
  content text NOT NULL,
  embedding vector(1536) NOT NULL,
  embedding_model text NOT NULL
);

Dimensions must match the model. Do not mix incomparable embedding models in one index. Store model name, chunking version, and source location so retrieval can be rebuilt and audited.

Start with Install pgvector for a working environment, then read pgvector production practices before adding approximate indexes.