PostgreSQL JSONB in Practice: Balancing Relational and Document Data

Anyone who builds platform products has probably struggled with a request like this: "We need a few additional fields in user profiles." Product wants to add "industry" today, "company size" tomorrow, and an array of tags the day after that. Should you run ALTER TABLE users ADD COLUMN ... every time? The table keeps accumulating columns until the schema becomes unwieldy. Six months later, nobody remembers which fields still contain data or who owns them.

Moving directly to MongoDB means maintaining another component: another backup process, another permission system, and another cross-database consistency problem. After writing to PG, you still have to synchronize the data, and transaction boundaries become ambiguous. Paying that much architectural complexity merely for "flexible fields" is not worthwhile.

PostgreSQL's jsonb was created for precisely this middle ground: one column can hold a variable structure and still be indexed for efficient queries. Within one database, you get both strong relational constraints and document flexibility. This article explains JSONB storage, access, queries, and indexing, along with the most important question: when should you use it, and when should you not?

1. The awkwardness of semi-structured data: painful ALTERs and too many components

Putting variable fields into a purely relational table has several serious drawbacks:

1. DDL is not free. Although adding a nullable column has no longer rewritten the entire table since 9.6, once a table accumulates dozens or hundreds of columns, wide rows from SELECT *, statistics, ORM mappings, and migration scripts all become a nightmare. Removing a field is even harder because you must first confirm that no dependencies, views, or indexes use it.
2. Sparse data wastes space and creates semantic confusion. Suppose you have one hundred tenants, each with five custom fields of its own. Should you create 500 columns? For each tenant, 95% of those columns would be NULL, making the schema completely unreadable.
3. Nested structures are difficult to represent. A "user preferences" object may contain notification.email.enabled and notification.push.frequency. A relational design must either split it across several tables, producing painful JOINs, or flatten it into ugly names such as notif_email_enabled.

Why not put everything in a document database? Because the core data in most systems, including orders, accounts, and payments, has strong relationships and consistency requirements. Moving that data into Mongo would be a step backward. The ideal approach is to keep core relationships in relational tables, store variable structures in jsonb columns, and let both coexist in the same table.

2. JSONB vs. JSON: one letter, a world of difference

PostgreSQL provides both json and jsonb. A beginner's first question is which one to choose. The answer is almost always jsonb.

• json stores the input text exactly as provided, much like text with JSON validation. Writes are fast because PostgreSQL does not parse the input, but every query must parse it again, and it cannot be indexed efficiently.
• jsonb parses input into a binary representation, or parse tree, when writing. It discards insignificant whitespace, does not preserve duplicate keys because only the last value remains, and does not retain the original key order. Writes are slightly slower because parsing is required, but queries are fast and the data can be indexed, which matters far more.

In one sentence: json means "read back exactly what you stored," while jsonb means "store structure, not text."

-- 同名键的差别(官方文档经典示例)
SELECT '{"bar":"x", "bar":"y"}'::json;   -- {"bar":"x","bar":"y"}  原样保留
SELECT '{"bar":"x", "bar":"y"}'::jsonb;  -- {"bar": "y"}           只留最后一个

In practice, always use jsonb for new projects. There is only one case for json: you use PG purely as an audit-log repository, write data without querying internal fields, and occasionally return the entire value unchanged. Saving the parsing overhead has some value there. In every other case, jsonb is decisively better.

3. Accessing values: -> returns jsonb, while ->> returns text

You must understand these two operators first because every subsequent query builds on them.

• ->: returns jsonb, so you can continue traversing the structure
• ->>: returns text, so you have reached a leaf and obtained a normal string

CREATE TABLE users (
  id   bigserial PRIMARY KEY,
  name text,
  meta jsonb
);

INSERT INTO users(name, meta) VALUES
  ('Alice', '{"age":30, "tags":["pg","go"], "addr":{"city":"上海"}}');

-- -> 取 jsonb:还能继续往下钻
SELECT meta -> 'addr' -> 'city' FROM users;   -- "上海"(带引号的 jsonb string)

-- ->> 取 text:拿去比较、拼接、展示
SELECT (meta -> 'addr') ->> 'city' FROM users; -- 上海(纯文本,无引号)

Rule of thumb: use -> when you need to keep traversing a path, and ->> when you need a value for comparison or output. A common beginner mistake is to use ->, get '"上海"' with quotation marks, and compare it with = '上海'. It will never match.

For deep paths, use #> or #>> to retrieve the value directly:

SELECT meta #>> '{addr,city}' FROM users;  -- 上海,等价于 (meta->'addr')->>'city'

4. Queries and conditions: @>, ?, and path matching

Retrieving values is not enough. In business applications, 90% of requirements involve filtering by fields inside JSON. For jsonb, = means complete equality. Filtering internal fields requires specialized operators.

1. @> (containment) is the most commonly used conditional operator. The jsonb value on the left "contains" the jsonb structure on the right.

-- meta 里有 "age":30
SELECT * FROM users WHERE meta @> '{"age":30}';

-- tags 数组里包含 "go"
SELECT * FROM users WHERE meta @> '{"tags":["go"]}';

-- 嵌套:addr.city = 上海
SELECT * FROM users WHERE meta @> '{"addr":{"city":"上海"}}';

The semantics of @> are that every key-value pair on the right appears on the left. For arrays, this means containing elements; for objects, it means recursive containment. Order does not matter, making it ideal for tag matching and attribute filtering.

2. ? (key existence) checks whether a top-level key exists.

SELECT * FROM users WHERE meta ? 'age';                      -- 有 age 键
SELECT * FROM users WHERE meta ?| array['vip','corp'];       -- 任一存在
SELECT * FROM users WHERE meta ?& array['vip','corp'];       -- 全部存在

Note that ? checks only top-level keys and cannot traverse nested structures. To inspect a nested object, combine it with ->: meta -> 'addr' ? 'city'.

3. SQL/JSON paths (jsonb_path_*) were introduced in PG 12+. jsonpath offers the greatest expressive power and supports conditional expressions.

-- 找年龄大于 25 的用户,$ 是根变量,@ 是当前匹配元素
SELECT * FROM users WHERE jsonb_path_exists(meta, '$.age ? (@ > 25)');

The key point is that these operators perform a full-table scan by default. PostgreSQL does not know which value inside jsonb should use an index. To make these queries fast, you must use the GIN indexes from the next section, and the query operator must match the operators supported by the index. If you use @>, pair it with GIN.

5. GIN indexes: take JSONB queries from seconds to milliseconds

Without an index, a jsonb query must parse and match the jsonb value in every row. On a table with millions of rows, this can saturate the CPU. GIN, or an inverted index, is essential to jsonb performance.

The most straightforward definition is:

CREATE INDEX idx_users_meta ON users USING GIN(meta);

The default GIN index supports the @>, ?, ?|, and ?& operators. It is somewhat large because it creates inverted entries for every key and value, but it provides the broadest coverage.

If queries use only @>, choose the smaller jsonb_path_ops operator class. The index may shrink by half or even to a fraction of its original size, but it supports only @> and jsonpath:

CREATE INDEX idx_users_meta_path ON users USING GIN(meta jsonb_path_ops);

-- 这个查询能命中上面的索引
SELECT * FROM users WHERE meta @> '{"tags":["go"]}';

Practical recommendation: use the default GIN index if you query both key existence (?) and containment (@>). If you only filter with @>, which is most common for attribute and tag matching, use jsonb_path_ops; the index is smaller, writes are faster, and queries are just as fast.

An expression index is appropriate when you care about only one fixed path. If you frequently filter or sort by meta ->> 'city', create a B-tree index directly on that expression:

CREATE INDEX idx_users_city ON users((meta ->> 'city'));

SELECT * FROM users WHERE meta ->> 'city' = '上海';  -- 走 B-tree,还能排序

This is more precise than indexing the entire column with GIN. The decision is whether you need to query arbitrary keys, which calls for GIN, or only a few fixed keys, which calls for expression indexes.

Another powerful engineering tool is a generated column. Materialize a frequently used path as an ordinary column, then apply normal indexes and constraints:

ALTER TABLE users ADD COLUMN city text
  GENERATED ALWAYS AS (meta ->> 'city') STORED;

CREATE INDEX idx_users_city ON users(city);

Whenever jsonb is written, city is synchronized automatically. Reads treat it like a normal column, so indexes, constraints, and JOINs all work. This is the best bridge between jsonb flexibility and relational strength.

6. When to use JSONB

Understanding how to use JSONB is important, but knowing when to use it matters even more. JSONB is the right tool for these scenarios:

1. Variable structures and custom fields. Examples include tenant-specific extension attributes in multi-tenant SaaS, dynamic fields in form engines, and content metadata in a CMS. The structure varies by tenant or template, and forcing it into a relational schema creates the unwieldy design described earlier.
2. Event, audit, and analytics payloads. Analytics fields change frequently: utm_source is added today and device_id tomorrow. Put the variable portion in jsonb while keeping core fields such as ID, timestamp, and user in relational columns. The data remains easy to query and index.
3. Nested configuration objects. Feature-flag configuration, dashboard layouts, and workflow definitions are naturally tree-shaped. Flattening them into relational tables makes them harder to express.
4. Values that are read and written as a whole, with internal fields queried infrequently. For example, you may store an entire API request body for auditing and usually query only external fields such as time and user. The internal jsonb rarely participates in filtering. Even text could work here, but jsonb provides insurance in case you need to query inside it later.

The decision rule in one sentence: use JSONB when the structure changes, internal fields are not primary query keys, and whole-value reads and writes are more common than field-level operations.

7. When not to use it: model the data relationally

JSONB is not a silver bullet. You will regret forcing it into these scenarios:

1. Fields are fixed and central to querying or sorting. Order amounts, statuses, and timestamps belong in independent columns. Putting them in jsonb means giving up B-tree sorting, range scans, and statistics.
2. You need JOINs and aggregations. SUM, GROUP BY, and JOIN can do little with internal jsonb fields. You must first materialize them with ->>, and the optimizer cannot help much. Reporting and reconciliation fields must be relational columns.
3. You need strong consistency constraints. Foreign keys, CHECK, UNIQUE, and NOT NULL do not apply inside jsonb. The database cannot guarantee that every user's age is a positive integer. If a field requires constraints, extract it.
4. Internal fields are updated frequently. A jsonb update rewrites the entire column by creating a new MVCC version, even when only one key changes. Frequently updating one internal value is slow and creates bloat. Such a field must become an independent column so it can use HOT updates.

A practical boundary is: whenever an internal field appears in WHERE, ORDER BY, JOIN, or GROUP BY, seriously consider extracting it, either into an ordinary column or a generated column. Keeping it in jsonb merely for flexibility trades away query performance and constraints.

8. Antipattern: do not use JSONB as if it were MongoDB

The most dangerous design is: jsonb is so flexible that you define the entire table as id + data jsonb. A users table contains only an ID and one large jsonb value holding every other field.

This turns PostgreSQL into "schemaless Mongo," only worse: you lose Mongo's mature document-query toolchain while also losing relational constraints, JOINs, and foreign keys. You get the advantages of neither side.

The consequences are predictable:

• Nobody knows which keys actually exist inside jsonb because there is no schema documentation or column-name enforcement
• A key can be written with the wrong type, such as "age":"三十", without a database error, causing queries to fail silently
• You cannot add a unique constraint or foreign key to an internal field
• Query performance depends entirely on GIN, without the fine-grained per-column indexes available to relational tables
• Six months later, whoever inherits the code cannot tell which keys are referenced and must grep through it line by line

The correct mental model is that jsonb supplements relational tables; it does not replace them. The relationships of core entities such as users, orders, and accounts should remain stable, while variable attributes such as configuration, metadata, and extension fields attach flexibly through jsonb. Within a table, relational columns form the skeleton, while jsonb columns provide flexible extensions. That is the purpose of PostgreSQL's hybrid document-relational capabilities.

In one sentence: JSONB solves the problem of a small group of fields whose structure changes; it is not an excuse to eliminate the schema from an entire table.

Summary

1. Always use jsonb, not json, in new projects: it uses binary storage, supports indexing, and queries quickly. The only cost is an extra parse during writes.
2. Understand the two operators first: -> returns jsonb so traversal can continue, while ->> returns text for comparison. Use the wrong one and your condition will never match.
3. Use @> for containment and ? for key existence when filtering internal fields. These operators perform full-table scans by default and require GIN indexes for high performance.
4. GIN is essential to jsonb performance: use default GIN for general queries and jsonb_path_ops when you use only @>, because it is smaller. For fixed paths, use expression indexes or generated columns, which also support constraints and JOINs.
5. The usage boundary is crucial: use JSONB when structures change, values are read and written as a whole, and internal fields are queried infrequently. When fields are fixed or require sorting, JOINs, aggregation, or constraints, model them as relational columns.

Key takeaway: JSONB gives relational tables a flexible extension; it does not turn them into schemaless Mongo. It adds flexibility, but it cannot replace discipline.