skip to content

Multi-Model Workloads

PostgreSQL can absorb document, search, vector and messaging workloads through JSONB, full-text search, pgvector and LISTEN/NOTIFY. Interviewers probe when that beats running a second database.

part ofPostgreSQLread it →
on this pageshow

explore

questions

23

In PostgreSQL, what is the difference between the json and jsonb column types, and which one should a new column normally use?

level: juniorimportance: must knowfreq 78%

answer

  1. exact text versus parsed binary
  2. reparse on every access
  3. whitespace, key order, duplicate keys
  4. operators and equality on one only
  5. last duplicate key wins

basics

~20 s

json stores the exact input text and reparses it on every access; jsonb stores a parsed binary form that drops whitespace, key order and duplicate keys but is faster to query, comparable and indexable. New columns should normally use jsonb.

solid answer

~40 s

Both types reject malformed JSON on input. `json` keeps an exact copy of the text, so whitespace, key order and duplicate keys survive, and every function call parses it again. `jsonb` parses once into a binary structure: whitespace is dropped, keys are stored in jsonb's own order (shorter keys first), and for a duplicated key only the last value is kept. Numbers become `numeric`, so out-of-range numbers are rejected and `1.230e-5` prints as `0.00001230`. Only `jsonb` has the containment and existence operators (`@>`, `?`), the jsonpath match operators, an equality operator and index support; `json` has no `=` at all, so `SELECT DISTINCT` or `GROUP BY` on it fails. Use `jsonb` unless the original text must be kept byte for byte, such as a raw webhook body kept for audit.

code

sql · 9 lines
sql
CREATE TABLE webhook_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  received_at timestamptz NOT NULL DEFAULT now(),
  raw_body json NOT NULL,      -- exact text, kept for signature checks
  body jsonb NOT NULL          -- parsed copy for queries
);

SELECT DISTINCT body FROM webhook_log;      -- works
SELECT DISTINCT raw_body FROM webhook_log;  -- ERROR: could not identify an equality operator for type json

go deeper

for a junior

Recall that json keeps the text exactly and jsonb stores a parsed form, and that jsonb is the default choice. Name one detail jsonb drops, such as key order.

for a middle

Explain the concrete consequences: the last duplicate key wins, numbers are stored as numeric, nothing is reparsed, and json lacks an equality operator, so DISTINCT and GROUP BY fail on it.

for a senior

Show when the raw text is the record, such as signed webhook bodies, how you keep a verbatim copy beside a jsonb working copy, and what converting a live column costs.

for a principal

Frame the choice as where work is paid: jsonb normalises at write time so every reader gets cheap, comparable values, while json defers parsing to every read.

## Two JSON types, one validation step PostgreSQL has two data types for JSON documents. Both check on input that the value is valid JSON, so a malformed document is rejected by either one. The difference is **what is stored**: - **`json`** stores an exact copy of the input text. Every function or operator that reads it must parse that text again. - **`jsonb`** parses the text once, on input, and stores a decomposed **binary** structure. Input is slightly slower because of the conversion; every later read is faster because nothing has to be reparsed. The manual's advice is direct: most applications should store JSON as `jsonb` unless they have specialised needs, such as legacy assumptions about the order of object keys. ## What jsonb does not keep Because `jsonb` stores a parsed structure rather than text, it discards details that JSON treats as insignificant: | Input detail | `json` | `jsonb` | |---|---|---| | Whitespace between tokens | kept | dropped | | Order of keys in an object | kept | not kept; keys are stored in jsonb's own order, shorter keys first | | Duplicate keys | all kept; processing functions use the last | only the last value is kept | | Number spelling | kept as typed | stored as `numeric`; `1.230e-5` prints as `0.00001230`, trailing zeros kept | | Numbers outside `numeric`'s range | accepted | rejected | | Unicode escape for the NUL character | accepted (syntax check only) | rejected | For a survey-builder that stores each submission's answers, none of these losses matters: the application reads answers by key name, never by position, and a respondent's whitespace carries no meaning. ## What jsonb gains - **Containment and existence operators.** `@>`, `<@`, `?`, `?|` and `?&` exist only for `jsonb`; the manual notes there is no parallel set of facilities for `json`. - **jsonpath match operators.** `@?` and `@@` take a `jsonb` left operand. - **Modification functions and operators.** `jsonb_set`, `jsonb_insert`, `||`, `-` and `#-` work on `jsonb`. - **Equality and ordering.** `jsonb` has `=`, `<>` and a B-tree ordering, so `SELECT DISTINCT`, `GROUP BY`, `UNION` and unique indexes work. `json` has no equality operator: `SELECT DISTINCT payload` on a `json` column fails with `could not identify an equality operator for type json`. - **Index support.** `jsonb` columns can carry GIN indexes as well as B-tree and hash indexes; choosing a GIN operator class is an indexing topic of its own. - **Faster processing.** Extracting a key from `jsonb` walks a binary structure; from `json` it reparses the whole text. Both types share the extraction operators `->`, `->>`, `#>` and `#>>`, and each casts to the other. ## Choosing for a new column 1. Default to `jsonb` for anything you will query, filter, index, compare or update. 2. Choose `json` only when the stored text itself is the record: a raw webhook body kept byte for byte for audit or signature verification, where key order and duplicate keys must survive. 3. When you need both, keep the raw text in a `json` (or `text`) column and a parsed `jsonb` copy beside it, or parse at read time if the raw copy is rarely queried. Converting an existing column with `ALTER TABLE ... ALTER COLUMN payload TYPE jsonb USING payload::jsonb` parses every row and rewrites the table; a single row with an out-of-range number fails the whole statement, and duplicate keys and key order are gone afterwards. ## Traps candidates fall into - Believing `jsonb` keeps the first of two duplicate keys: it keeps the **last**, and the `json` processing functions also treat the last as operative. - Believing `json` skips validation: both types reject malformed input. - Comparing documents as text: two `json` values that differ only in whitespace are different text, while their `jsonb` forms compare equal. - Expecting `jsonb` to preserve the key order a client sent: if order matters, model it as an array. ```sql SELECT '{"b": 1, "a": 2, "a": 3}'::json AS as_json, '{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb; -- as_json : {"b": 1, "a": 2, "a": 3} -- as_jsonb: {"a": 3, "b": 1} ```

  • Why does '{"a": 1}'::jsonb = '{ "a" : 1 }'::jsonb return true, while the same comparison on json fails?
    `jsonb` stores the parsed structure, so whitespace is gone and both sides are the same value; `jsonb` has an `=` operator that compares structures. `json` has no equality operator at all, so `json = json` fails with `operator does not exist`. Cast to `jsonb` first if you must compare `json` values.
  • What can go wrong when you convert a large json column to jsonb in place?
    `ALTER COLUMN ... TYPE jsonb USING payload::jsonb` parses every row and rewrites the table and its indexes, holding a lock that blocks other access for the duration. One row with a number outside `numeric`'s range, or a NUL-character escape, aborts the whole statement. Afterwards duplicate keys and the original key order are gone, so keep the raw text elsewhere if anything depends on it.

json is a scanned photocopy of a paper survey: perfectly faithful, but anyone who needs one answer must read the whole page again. jsonb is the clerk's transcription into labelled fields: the handwriting and the order the boxes were filled in are gone, but any answer can be looked up at once.

  • json skips validation, so it accepts malformed documents
  • jsonb keeps the first value when a key is duplicated
  • jsonb preserves the key order the client sent
  • The @> containment operator works on json columns too
  • json is faster to query because it is stored as plain text
open as a page

In PostgreSQL full-text search, what are tsvector and tsquery, and what does the @@ operator check between them?

level: juniorimportance: must knowfreq 62%

basics

~20 s

A tsvector is a document reduced to sorted, normalised lexemes with their positions; a tsquery is normalised lexemes joined by &, |, ! and <->. The @@ operator returns true when the tsvector satisfies the tsquery's expression.

open as a page

In pgvector, what do the <->, <=> and <#> operators compute, and why must an index's operator class match the operator?

level: juniorimportance: must knowfreq 62%

basics

~20 s

pgvector's <-> is L2 distance, <=> cosine distance and <#> negative inner product. An HNSW or IVFFlat index is built with one operator class, such as vector_cosine_ops, and serves only queries ordering by that class's operator.

open as a page

In PostgreSQL, what do the jsonb operators ->, ->> and @> return, and when should a WHERE clause use @> instead of ->>?

level: middleimportance: must knowfreq 68%

basics

~20 s

The -> operator returns jsonb, ->> returns text, and @> returns a boolean saying whether the left document contains the right one's keys, values and structure. Use @> for type-aware matches on nested values or array members; ->> to read one scalar.

open as a page

In PostgreSQL, when is a NOTIFY issued inside a transaction delivered to listeners, and what happens to it if that transaction rolls back?

level: middleimportance: must knowfreq 58%

basics

~20 s

A NOTIFY is held until its transaction commits and only then delivered; if the transaction rolls back, nothing is ever sent. A listener that is inside its own transaction receives the notification only after that transaction ends.

open as a page

In PostgreSQL, a cache-invalidation listener reconnects after a one-minute outage; which NOTIFY signals sent meanwhile does it receive, and how should it resynchronise?

level: middleimportance: must knowfreq 54%

basics

~20 s

None: PostgreSQL delivers a notification only to sessions listening when it commits and never stores it for anyone else. On reconnect the listener must run LISTEN, commit, then reload or re-validate its cache from the tables.

open as a page

With PostgreSQL's pg_trgm extension, how do similarity() and the % operator match misspelled search terms, and what sets the cut-off?

level: middleimportance: must knowfreq 52%

basics

~10 s

pg_trgm splits strings into lower-cased three-character trigrams and similarity() returns shared trigrams divided by all distinct trigrams, from 0 to 1. The % operator is true when that score reaches pg_trgm.similarity_threshold, default 0.3.

open as a page

In pgvector, when does a nearest-neighbour query use an HNSW or IVFFlat index, and how do its results differ from a scan's?

level: middleimportance: must knowfreq 55%

basics

~20 s

Without an index, pgvector searches exactly, with perfect recall. An HNSW or IVFFlat index is used only for ORDER BY a matching distance operator, ascending, plus LIMIT, and its results are approximate: true neighbours can be missed and fewer rows returned.

open as a page

In PostgreSQL, what do LISTEN, NOTIFY and pg_notify() do, and what does a listening client receive with each notification?

level: juniorimportance: should knowfreq 46%

basics

~20 s

LISTEN registers a session on a named channel; NOTIFY or pg_notify(channel, payload) signals every session listening on it in the same database. Each notification carries the channel name, a payload under 8000 bytes and the sender's backend PID.

open as a page

In PostgreSQL, how do jsonpath queries through jsonb_path_query, @? and @@ work, and what changes between lax and strict mode?

level: middleimportance: should knowfreq 35%

basics

~20 s

A jsonpath such as $.ratings[*] ? (@.score < 3) selects items from a jsonb document: jsonb_path_query returns them as rows, @? tests whether any exist, and @@ evaluates a predicate. Lax mode, the default, unwraps arrays and hides structural errors; strict raises them.

open as a page

In PostgreSQL, how do jsonb_set, ||, - and jsonb_insert change part of a jsonb document, and what happens when jsonb_set gets SQL NULL?

level: middleimportance: should knowfreq 45%

basics

~20 s

Each returns a new document: jsonb_set replaces or adds the value at a path, || merges top-level keys, - removes keys or elements, and jsonb_insert inserts. jsonb_set is STRICT, so a SQL NULL new value makes the whole result NULL.

open as a page

In PostgreSQL full-text search, what does a text search configuration control, and how do you add unaccent so 'parametres' matches 'paramètres'?

level: middleimportance: should knowfreq 33%

basics

~20 s

A text search configuration binds a parser to a dictionary list per token type, which turns tokens into lexemes. Copy one such as french and ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem, so accents are stripped before stemming.

open as a page

In PostgreSQL, why should a help-centre search box pass user input to websearch_to_tsquery rather than to_tsquery, and what syntax does it accept?

level: middleimportance: should knowfreq 46%

basics

~20 s

to_tsquery expects tsquery operator syntax and raises a syntax error on ordinary input such as two bare words. websearch_to_tsquery never raises: plain words are ANDed, quoted text becomes a phrase, 'or' becomes OR and a dash becomes NOT.

open as a page

In a PostgreSQL survey-builder, reports filter and sort on a few jsonb answer keys; what signs say a key should become a real column, and how do you promote it?

level: seniorimportance: should knowfreq 55%

basics

~20 s

Promote a key that nearly every row has and that queries filter, join, sort or constrain on: the planner keeps no per-key statistics, jsonb enforces no type, NOT NULL or foreign key, and every change rewrites the whole document.

open as a page

In PostgreSQL, the log warns 'NOTIFY queue is 50% full' while pricing updates keep notifying; what is filling the queue, and what happens at 100%?

level: seniorimportance: should knowfreq 31%

basics

~20 s

The queue frees an entry only after every listener reads past it, so one listener stuck in an open transaction pins it. When full, each notifying transaction fails at commit with 'too many notifications in the NOTIFY queue'.

open as a page

For a PostgreSQL 18 help-centre search, how do you store a weighted tsvector, then rank with ts_rank_cd and highlight with ts_headline without slowing every query?

level: seniorimportance: should knowfreq 41%

basics

~10 s

Concatenate setweight(to_tsvector('english', title), 'A') with lower-weighted summary and body in a generated column marked STORED, since 18's default VIRTUAL cannot be indexed. Rank matches with ts_rank_cd, then call ts_headline only on the final page.

open as a page

When does PostgreSQL's built-in full-text and trigram search stop being enough, so that you would move a help-centre search to a dedicated search engine?

level: seniorimportance: should knowfreq 38%

basics

~20 s

Stay in PostgreSQL while keyword matching, field weights and modest corpus size are enough. Move when you need corpus-aware relevance (ts_rank has no IDF or BM25), facets, typo tolerance and language analysis at scale, or search load isolated from the database.

open as a page

With a pgvector HNSW index, a ticket search filtered by account_id returns 3 rows for LIMIT 10; why, and what does pgvector 0.8 add to fix it?

level: seniorimportance: should knowfreq 46%

basics

~20 s

pgvector applies a WHERE filter after the HNSW scan, so only the account's share of the 40 hnsw.ef_search candidates survives. Version 0.8 adds hnsw.iterative_scan, which keeps scanning until enough rows match or hnsw.max_scan_tuples is reached.

open as a page

In pgvector, when would you pick IVFFlat over HNSW, and why does an IVFFlat index built on an empty table have poor recall?

level: seniorimportance: should knowfreq 38%

basics

~20 s

pgvector's HNSW has the better speed-recall tradeoff but builds slower and uses more memory; IVFFlat builds faster and smaller. IVFFlat runs k-means on existing rows at build time, so an index built on an empty table has meaningless lists and poor recall.

open as a page

A PostgreSQL 18 survey report averages JSON_VALUE(answers, '$.nps' RETURNING int) and the number looks wrong; what is JSON_VALUE doing with bad answers, and how do you expose them?

level: seniorimportance: nice to knowfreq 22%

basics

~20 s

JSON_VALUE defaults to NULL ON EMPTY and NULL ON ERROR, so a missing nps, a JSON null and an unconvertible value like "ten" all become SQL NULL, which AVG silently skips. Add ERROR ON EMPTY and ERROR ON ERROR, or count the NULLs.

open as a page

Why can adding pg_notify() to every write transaction on a busy PostgreSQL primary cap write throughput, and how do you keep the notification cost down?

level: seniorimportance: nice to knowfreq 21%

basics

~20 s

Each transaction that notifies takes one server-wide lock before committing and holds it until the commit completes, so notifying commits run one at a time. Notify only when listeners care, and send fewer, coarser signals per transaction.

open as a page

In pgvector, how do you index 3,072-dimension embeddings when an HNSW index on a vector column refuses more than 2,000 dimensions?

level: seniorimportance: nice to knowfreq 27%

basics

~20 s

pgvector's vector type stores up to 16,000 dimensions but HNSW and IVFFlat index at most 2,000. Index a halfvec instead, which allows 4,000, either as the column type or as an expression index on embedding::halfvec(3072), and query with the same cast.

open as a page