0Pricing
PostgreSQL Performance & Query Optimization · 课时

何时应将 JSONB 规范化为普通列

识别适合将 JSONB 字段提升为实际列、从而获得性能优势的访问模式。

何时应将 JSONB 规范化为普通列 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

JSONB Is Great Until It Isn't

JSONB is wonderful for flexible, schema-less data. But not every field belongs inside the blob. Some fields are accessed so often, filtered so hard, or joined so frequently that keeping them buried in JSONB actively hurts performance.

This lesson is about a single design decision: when do you promote a JSONB field to a real column?

  • A real column has a fixed type, can be NOT NULL, and indexes cheaply.
  • A JSONB field is dynamic, but every read pays a parse/extract cost and indexing it is heavier.

The goal isn't "JSONB bad, columns good" — it's matching the access pattern to the right storage.

What Promoting Actually Means

"Normalizing out of JSONB" means taking a value that currently lives inside the data JSONB column and storing it as its own typed column instead.

You can keep the JSONB for the long tail of rare attributes, and pull out only the hot fields.

-- Before: everything lives in JSONB
CREATE TABLE events (
    id       bigserial PRIMARY KEY,
    data     jsonb NOT NULL
);

-- After: hot fields promoted, rest stays flexible
CREATE TABLE events (
    id          bigserial PRIMARY KEY,
    user_id     bigint NOT NULL,
    event_type  text   NOT NULL,
    created_at  timestamptz NOT NULL,
    data        jsonb NOT NULL  -- the long tail
);

Signal 1: You Filter On It Constantly

The strongest signal to promote a field is a WHERE clause that hits it on nearly every query.

Filtering inside JSONB forces an extraction expression like data->>'status'. That works, but it returns text, needs casting, and a plain B-tree index on the table won't cover it unless you build an expression index.

If the filter is core to your workload, a real typed column with an ordinary index is simpler and faster.

-- Buried in JSONB: needs an expression index to be fast
EXPLAIN ANALYZE
SELECT * FROM events
WHERE data->>'status' = 'active';

-- Expression index that makes the above usable
CREATE INDEX idx_events_status
    ON events ((data->>'status'));

Expression Index vs. Real Column

An expression index on (data->>'status') can match the exact filter, but it has sharp edges:

  • The query predicate must match the indexed expression character for character — data->>'status' indexed won't help data#>>'{status}'.
  • Values come back as text; range and numeric filters need explicit casts that must also match the index.
  • You maintain one index per extracted field, each re-parsing the JSON on write.

A promoted column sidesteps all of this: standard typing, standard indexes, standard planner statistics.

Signal 2: You Need Range or Sort Performance

Numbers and timestamps are common victims. Inside JSONB they're stored as text-ish values, so range scans and ORDER BY need a typed expression index just to behave.

If you sort or range-filter on a field — pagination by created_at, price ranges, amounts — promote it to a real timestamptz / numeric column. The planner gets accurate stats and a clean B-tree.

-- Range + sort on a JSONB number is awkward and cast-heavy
SELECT *
FROM orders
WHERE (data->>'amount')::numeric > 100
ORDER BY (data->>'created_at')::timestamptz DESC
LIMIT 20;

-- With promoted columns it's a plain, index-friendly query
SELECT *
FROM orders
WHERE amount > 100
ORDER BY created_at DESC
LIMIT 20;

Signal 3: You Join Or Group On It

Foreign keys and grouping keys should almost never live in JSONB.

  • You cannot declare a real FOREIGN KEY constraint on data->>'user_id' — referential integrity is lost.
  • Joining on an extracted text value blocks hash/merge join optimizations and forces casts.
  • GROUP BY data->>'category' can't use column statistics well, hurting aggregate plans.

If a field connects rows together, make it a first-class typed column.

-- Fragile: no FK, cast on every join, poor stats
SELECT u.name, count(*)
FROM events e
JOIN users u ON u.id = (e.data->>'user_id')::bigint
GROUP BY u.name;

-- Promoted user_id: real FK, clean join, real stats
-- ALTER TABLE events ADD COLUMN user_id bigint REFERENCES users(id);

When JSONB Should Stay JSONB

Promotion isn't free, so keep fields in JSONB when:

  • They're sparse — present on only a small fraction of rows (promoting creates a mostly-NULL column).
  • They're rarely filtered — read back as a whole document, never used in WHERE/JOIN.
  • They're unpredictable — keys vary per tenant or per event type and you can't enumerate them.
  • They form a nested structure you fetch as one unit (e.g. a settings object).

This long tail is exactly what JSONB was designed for. Don't flatten it just because you flattened the hot fields.

GIN: The Other Option

Before promoting, ask whether a GIN index on the JSONB already solves it. GIN shines for containment and key-existence queries across many unpredictable keys.

GIN is great when you query many different JSON keys ad hoc. It's overkill (and write-heavy) when one or two specific fields drive every query — that's the promotion case.

-- GIN supports @>, ?, ?| ?& on the whole document
CREATE INDEX idx_events_data_gin
    ON events USING gin (data);

-- Containment query the GIN index can serve
SELECT * FROM events
WHERE data @> '{"status": "active"}';

-- jsonb_path_ops: smaller/faster, supports only @>
CREATE INDEX idx_events_data_pathops
    ON events USING gin (data jsonb_path_ops);

Decision Heuristic

A quick rule of thumb for each field:

  • Hot + selective + typed (filtered, sorted, joined, FK) → promote to a real column.
  • Ad-hoc across many keys → keep in JSONB, add a GIN index.
  • Sparse / read-as-document / rarely queried → keep in JSONB, no extra index.

Most real schemas end up hybrid: a handful of promoted columns plus a JSONB column for everything else.

Migrating a Field Out, Safely

To promote an existing JSONB field, backfill it into a new column, then index it. Doing it in steps keeps locks short and lets you validate the data first.

Note the cast: JSONB text must be coerced to the target type, and you should decide what to do with rows missing the key (here they become NULL).

ALTER TABLE events ADD COLUMN created_at timestamptz;

UPDATE events
SET created_at = (data->>'created_at')::timestamptz
WHERE created_at IS NULL
  AND data ? 'created_at';

CREATE INDEX idx_events_created_at ON events (created_at);

Keep Them In Sync (Or Drop The Duplicate)

After promoting, you have two choices: remove the field from the JSONB so there's a single source of truth, or keep both and guarantee they agree.

If you keep both, a generated column is the cleanest: it's derived from JSONB automatically and can't drift.

-- Option A: drop the duplicated key from the blob
UPDATE events
SET data = data - 'created_at';

-- Option B: a STORED generated column stays in sync by design
ALTER TABLE events
    ADD COLUMN status text
    GENERATED ALWAYS AS (data->>'status') STORED;

CREATE INDEX idx_events_status_gen ON events (status);

Quick Check

Which field is the strongest candidate to normalize OUT of a JSONB data column?

Recap

Promote a JSONB field to a real column when its access pattern demands it:

  • Filtered constantly → typed column + ordinary index beats an expression index.
  • Range-scanned or sorted → real numeric/timestamptz gives clean B-trees and accurate stats.
  • Joined / grouped / FK → only a real column supports foreign keys and good join plans.

Keep fields in JSONB when they're sparse, ad-hoc across many keys, or read as a document — add a GIN index if you need containment search. The winning design is usually hybrid: a few promoted hot columns plus a JSONB column for the long tail. When you do promote, backfill carefully and either drop the duplicate key or use a STORED generated column so the two never drift.

常见问题解答

「何时应将 JSONB 规范化为普通列」课时是免费的吗？

是的 — 「何时应将 JSONB 规范化为普通列」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「何时应将 JSONB 规范化为普通列」这节课中我会学到什么？

识别适合将 JSONB 字段提升为实际列、从而获得性能优势的访问模式。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。

「何时应将 JSONB 规范化为普通列」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. JSONB 运算符与包含查询
  2. JSONB 上的 GIN 索引与表达式索引对比
  3. 使用 JSONPath 查询 JSONB
  4. 何时应将 JSONB 规范化为普通列
← 返回 PostgreSQL Performance & Query Optimization