PostgreSQL jsonb: operators, GIN indexes and limits

Use jsonb, not json, and keep it for data whose shape genuinely varies: webhook payloads, per-customer settings, product attributes that differ by category. Query it with ->> to pull a field out as text, @> to ask whether a document contains a fragment, and index it with a GIN index using jsonb_path_ops if you mostly use @>. The rest of this guide is the detail that decides whether that choice ages well - and the cases where a boring column would have been better.

The short version of the warning: jsonb is a good second tool and a poor first one. Anything you filter on, join on, sort on or need to be unique belongs in a real column. The document column is for the long tail of fields you read back whole and rarely search.

json versus jsonb#

PostgreSQL has two JSON types and they behave differently. json stores the exact text you gave it and re-parses it on every access. jsonb parses once, stores a decomposed binary form, and supports indexing. For nearly everything you want jsonb.

What you give up by choosing jsonb:

• Whitespace and key order are not kept. {"b": 1, "a": 2} comes back as {"a": 2, "b": 1}, with keys sorted by length and then alphabetically.
• Duplicate keys collapse. If the same key appears twice, the last value wins.
• Inserts are slightly slower, because of the conversion.

The only reasons to pick json are needing the original text byte for byte (an audit log of exactly what a third party sent) or never reading the value at all. Even then, storing the raw body in a text column is usually clearer about intent.

Numbers deserve a mention. jsonb stores numbers as PostgreSQL numeric, so large integers and decimals survive intact, which is better than JavaScript can manage. The catch is on the way out: a client that parses the result into a double loses precision above 2^53, and that is the client's doing, not the database's.

A column is declared like any other:

CREATE TABLE app.events (  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  tenant_id   bigint      NOT NULL,  kind        text        NOT NULL,  created_at  timestamptz NOT NULL DEFAULT now(),  payload     jsonb       NOT NULL DEFAULT '{}');INSERT INTO app.events (tenant_id, kind, payload) VALUES  (7, 'signup',  '{"plan":"pro","source":"ad","tags":["beta","eu"],"user":{"id":41,"email":"a@example.com"}}'),  (7, 'invoice', '{"total":19.90,"currency":"USD","paid":false}');

Notice what the table does: tenant_id, kind and created_at are columns because every query uses them, and only the variable part is in payload. That split is the whole design.

Operators you will use every day#

There are two families: operators that extract a value, and operators that test one. They are easy to confuse because they look alike.

The arrow with one head keeps the value as jsonb, so you can keep chaining. The arrow with two heads gives you plain text, which is what you want at the end of a chain or in a WHERE clause. An integer on the right indexes into an array, counting from zero, and a negative one counts from the end.

SELECT payload -> 'user' ->> 'email'      AS email,       payload -> 'tags' ->> 0            AS first_tag,       (payload #>> '{user,id}')::bigint  AS user_idFROM app.eventsWHERE kind = 'signup';

Two behaviours catch people. First, a missing key gives SQL NULL, not an error, which is convenient and also hides typos: payload ->> 'plna' quietly returns nothing. Second, a JSON null and a missing key are different things. '{"a": null}'::jsonb -> 'a' returns a jsonb value null, which is not SQL NULL; ->> on it does return SQL NULL. If your logic cares about the difference, test with the ? operator below.

PostgreSQL 14 added subscripting, so payload['user']['email'] works too and reads more naturally in an UPDATE. It returns jsonb, like ->.

Testing operators

These return a boolean and are what you use in WHERE.

Containment is the one to learn properly, because it is the operator the indexes are built around:

-- Events whose payload includes this fragmentSELECT id FROM app.events WHERE payload @> '{"plan":"pro"}';-- Nested: the fragment must have the same structureSELECT id FROM app.events WHERE payload @> '{"user":{"id":41}}';-- Arrays: contains the element, in any positionSELECT id FROM app.events WHERE payload @> '{"tags":["beta"]}';-- A key exists, whatever its valueSELECT id FROM app.events WHERE payload ? 'currency';

Containment compares structure, not text. The fragment {"tags":["beta"]} matches a document with ["eu","beta","x"], because array order and extra elements do not matter. It does not match a document where "beta" is nested one level deeper. And the ? operator only looks at top-level keys; it will not find email inside user. The question mark can also collide with parameter placeholders in some drivers - the Java JDBC driver is the well-known case - in which case the function forms jsonb_exists, jsonb_exists_any and jsonb_exists_all are the workaround.

Modifying a document#

Changing one field is where jsonb feels least like a database column. There is no in-place edit; you build a new value.

-- Add or overwrite top-level keys (right side wins)UPDATE app.events SET payload = payload || '{"paid":true}' WHERE id = 2;-- Set a nested path; the last argument creates the key if it is missingUPDATE app.eventsSET payload = jsonb_set(payload, '{user,email}', '"new@example.com"', true)WHERE id = 1;-- Remove a key, or a pathUPDATE app.events SET payload = payload - 'source' WHERE id = 1;UPDATE app.events SET payload = payload #- '{user,email}' WHERE id = 1;

The new value in jsonb_set must itself be valid jsonb, which is why the string above has two sets of quotes: the inner ones make it a JSON string. Forgetting that is the most common error with this function. || is shallow - it replaces a whole nested object rather than merging into it - so for deep merges you need jsonb_set or application code.

Build documents from rows with jsonb_build_object and jsonb_agg, and unpack them the other way with set-returning functions:

-- One row per tagSELECT e.id, t.tagFROM app.events e,     jsonb_array_elements_text(e.payload -> 'tags') AS t(tag)WHERE e.kind = 'signup';-- One row per top-level keySELECT key, value FROM app.events e, jsonb_each(e.payload) WHERE e.id = 2;-- Build a document from a querySELECT jsonb_build_object('tenant', tenant_id, 'events', jsonb_agg(kind))FROM app.events GROUP BY tenant_id;

For anything more structured, jsonpath (jsonb_path_query, @?, @@) lets you write filters such as $.items[*] ? (@.qty > 5) in one expression. It is powerful and worth knowing exists, but readable only to people who have used it, so reach for it when -> chains become unwieldy rather than by default. PostgreSQL 17 also added SQL-standard JSON_TABLE, which turns a document into rows in one clause; check your server version before depending on it.

Indexing jsonb with GIN#

Without an index, every payload @> ... filter reads the whole table. A GIN (generalised inverted) index fixes that by indexing the keys and values inside the document, the same way a book index maps words to pages. If you want the general picture first, PostgreSQL indexes: types, order and cost covers where GIN sits among the index types.

There are two operator classes, and the choice matters:

-- Default: also answers key-existence queriesCREATE INDEX events_payload_gin ON app.events USING gin (payload);-- Smaller and quicker, containment and jsonpath onlyCREATE INDEX events_payload_path ON app.events USING gin (payload jsonb_path_ops);

The practical rule: if your queries are all @>, use jsonb_path_ops. It hashes the whole path to each value rather than indexing keys and values separately, so it is noticeably smaller and handles a containment on a common key better. If you need ? to ask whether a key exists, you need the default class. Mixing both is possible, but two GIN indexes on one column is a large cost for a convenience.

Check that the planner actually picks it up:

EXPLAIN (ANALYZE, BUFFERS)SELECT id FROM app.events WHERE payload @> '{"plan":"pro"}';

You want a Bitmap Index Scan on the GIN index feeding a Bitmap Heap Scan. GIN always produces bitmap scans, never plain index scans. EXPLAIN ANALYZE on a slow query reads these plans line by line.

What a GIN index does not do

This is where most disappointment comes from.

• It does not help `->>` comparisons. WHERE payload ->> 'plan' = 'pro' cannot use the GIN index above, because it is not a containment test. Rewrite it as payload @> '{"plan":"pro"}', or build an expression index (next section).
• It does not help ranges or sorting. There is no way to ask a GIN index for "total greater than 100" or ORDER BY a field.
• It is slow to write. GIN uses a pending list (fastupdate) to batch inserts, which smooths the average but means occasional insertions pay for a flush, and vacuum has more work. A table with heavy insert traffic and a large GIN index shows it in write latency.
• It can be big. Several times the size of an equivalent B-tree is normal. On a small server that competes with the cache for the same memory as your hot data.
• The planner has no statistics about the inside of a document. For @> it guesses selectivity, so estimates are often wrong, and a wrong estimate can pick a bad plan. This is a known limitation, not a configuration mistake.

A GIN index on jsonb is a general-purpose tool that answers "find documents containing X" well. For a field you filter or sort on constantly, you want something sharper.

Expression indexes for the fields you really query#

When one or two fields carry most of the traffic, index those fields directly with a B-tree on an expression. It is smaller, faster to maintain, supports ranges and sorting, and gives the planner real statistics.

-- Equality on a text fieldCREATE INDEX events_plan ON app.events ((payload ->> 'plan'));-- Range on a numeric field: cast inside the indexCREATE INDEX events_total ON app.events (((payload ->> 'total')::numeric))  WHERE kind = 'invoice';-- Unique within a tenantCREATE UNIQUE INDEX events_user_once ON app.events (tenant_id, (payload #>> '{user,id}'))  WHERE kind = 'signup';

The double parentheses are required: the outer pair is the index column list, the inner pair marks it as an expression. The query must repeat the expression exactly, including the cast. If the index is on ((payload ->> 'total')::numeric), then WHERE payload ->> 'total' > '100' compares text and uses nothing. This is the same trap described for ordinary functions in the indexes guide, and the partial WHERE kind = 'invoice' there shrinks the index to the rows that have the field at all.

Expression indexes pair well with a CHECK constraint so that the type you cast to is actually guaranteed:

ALTER TABLE app.events  ADD CONSTRAINT invoice_total_numeric  CHECK (kind <> 'invoice' OR jsonb_typeof(payload -> 'total') = 'number');

Without it, a single row with "total": "n/a" makes the cast fail, and it fails in the middle of your query, not at insert time. Constraints are the nearest you get to a schema inside a document, and they are cheap.

Promoting a field to a real column#

Once a field is in every query, stop extracting it. PostgreSQL 12 and later can keep a real column in step with the document:

ALTER TABLE app.events  ADD COLUMN plan text GENERATED ALWAYS AS (payload ->> 'plan') STORED;CREATE INDEX events_plan_col ON app.events (plan);

The column is computed on write, stored on disk and indexed like any other, and it appears in SELECT *. You cannot write to it directly; you change the document and it follows. On an existing large table, adding a stored generated column rewrites the table, so treat it as a migration with a lock, not a casual statement - schema migrations without downtime covers the safe sequence.

The alternative is a plain column populated by the application or a backfill, with the document keeping a copy until you remove it. That is more work and more flexible: you can change types, add NOT NULL, add a foreign key. Generated columns cannot be foreign keys.

MySQL users will recognise the pattern - it is how MySQL indexes JSON at all, and MySQL JSON columns walks through the equivalent. In PostgreSQL it is optional, because expression indexes exist, but the generated column makes the schema honest about which fields matter.

Performance costs you should know about#

jsonb has a property that surprises people the first time they update a busy table: any change to a document rewrites the whole value. PostgreSQL never edits part of a jsonb. Setting one boolean inside a 40 KB document writes a new 40 KB version of the row, and the old version waits for vacuum.

Storage works like this:

• Values over roughly 2 KB are compressed and, if still large, moved out of the row into a TOAST table. A query that reads one field from a large document still has to fetch and decompress the whole thing.
• Updating a toasted jsonb writes a new toasted value. The old one stays until vacuum removes it.
• Updating any column in the row, including one unrelated to the document, can also rewrite the row; heap-only updates are possible only when no indexed column changed.

The consequences, in order of how often they bite:

1. Hot counters inside a document are a bad idea. jsonb_set(payload, '{views}', ...) on every page view generates bloat out of proportion to the information stored. Keep counters in their own integer column or table.
2. Large documents that are updated often belong in several rows. Split a 200 KB blob that changes by one field per request into rows by sub-entity.
3. Wide documents hurt reads that need only one field. A narrow table with the commonly read fields as columns, and the rest in payload, outperforms a table that is all payload.
4. Vacuum has more to do. See vacuum and bloat for how to see whether dead tuples are accumulating faster than autovacuum clears them; update-heavy jsonb tables are a common source.

The hard limit is 1 GB per value, which you should never approach. The practical one is much lower: past a few hundred kilobytes a document is a file, and a file store is the right home for it.

When jsonb is the wrong tool#

The honest test: if you can write down the fields, their types and which are required, you have a schema, and PostgreSQL's tables are the best schema tool you have. jsonb is wrong in these cases.

• Relational data in disguise. A jsonb array of order lines inside orders cannot have a foreign key to products, cannot be joined without unnesting, and cannot enforce that a product exists. Use a child table.
• Fields you filter, sort or aggregate on. A column gets statistics, a B-tree, a type and constraints. A path inside a document gets none of these without extra work, and SUM((payload ->> 'total')::numeric) over millions of rows is slower than SUM(total).
• Anything that must be unique or required. NOT NULL, UNIQUE, REFERENCES and CHECK on a column are one line. On a field in a document they are expression indexes and hand-written checks, and the application will forget one.
• Frequently updated small values. Counters, flags, timestamps and status fields that change on most requests pay the rewrite cost described above for no benefit.
• Large binary or blob-like content. Base64 images in JSON are three-quarters of a megabyte of wasted parsing. Put the file in object storage and the URL in the row.
• A whole app with no schema. If the table is one id and one data column, you have built a document database inside a relational one, with worse tooling than a document database and none of the guarantees of a relational one. That can be a deliberate choice; PostgreSQL or MongoDB lays out when the other engine is the better fit, and MongoDB indexes and schema design shows what the alternative looks like.

And the cases where it is the right tool:

• Payloads from third parties whose shape you do not control, stored as received and read mostly whole.
• User-defined fields or per-tenant settings, where the keys are different for every row.
• Sparse attributes: a catalogue where each category has a dozen properties and no two categories share them.
• Event and audit records written once and read for debugging, with the stable fields (tenant_id, kind, created_at) as ordinary columns.
• Prototypes, with a plan to promote the fields that survive into columns.

The habit that works: begin with columns for everything you know, add one jsonb column named for what it holds (attributes, metadata, raw_payload), and move a field out of it the day it appears in a WHERE clause twice.

Running jsonb on a small server#

On a small instance the costs above show up as memory and disk, not CPU. A GIN index plus TOAST plus bloat from updates can double a table's footprint, and the extra pages push your working set out of shared_buffers. Check sizes before and after adding an index:

SELECT pg_size_pretty(pg_table_size('app.events'))   AS table_and_toast,       pg_size_pretty(pg_indexes_size('app.events')) AS indexes;SELECT relname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scanFROM pg_stat_user_indexes WHERE relid = 'app.events'::regclass;

Build indexes on a populated table with CREATE INDEX CONCURRENTLY so writes are not blocked, and raise maintenance_work_mem for the session first - GIN builds are memory-hungry. If you keep both a default GIN index and expression indexes, check idx_scan after a week and drop whichever the planner ignores. PostgreSQL tuning for small servers covers the memory settings that decide whether any of this stays in cache, and PostgreSQL with pg_stat_statements tells you which jsonb queries are actually costing you before you add an index for the wrong one.

On RE:NODE, a PostgreSQL database server is its own plan with its own credentials, so building and testing an index on a copy costs a small plan rather than risk to a live one. Take a backup before a large index build or a table-rewriting migration; slots are on the plan and the guide to databases and backups shows the steps.

FAQ#

Should I use json or jsonb in PostgreSQL?

jsonb, almost always. It is indexable, faster to query and strips duplicate keys. Use json only if you must preserve the exact original text, including whitespace and key order.

Why is my jsonb query not using the GIN index?

Most often because the query uses ->> or -> with = instead of @>. A GIN index answers containment and key-existence tests, not extracted-value comparisons. Rewrite with @>, or create a B-tree expression index on the exact expression in the query.

jsonb_ops or jsonb_path_ops?

jsonb_path_ops if you only use @> and jsonpath operators: it is smaller and usually faster. Choose the default jsonb_ops if you also need ?, ?| or ?& to test for keys.

Is jsonb slower than normal columns?

For the fields you filter and aggregate on, yes: no per-field statistics, larger rows, and every update rewrites the whole value. For storing and returning a document whole, the difference is small. The cost is in what you ask of it.

Can I put a foreign key or unique constraint on a field inside jsonb?

Not directly. A unique expression index gives you uniqueness, and a CHECK constraint can validate shape. A foreign key needs a real column, so extract the field with a generated column or move it into the table proper.

How big can a jsonb value be?

The limit is 1 GB, but performance degrades long before that. Values over about 2 KB are compressed or moved to TOAST storage, and any update rewrites the whole value. Keep frequently updated documents small, and store files in object storage.