PostgreSQL JSONB vs Normalized Columns for Evolving API Data

Sooner or later, an API stores data whose shape keeps changing: webhook payloads, user preferences, integration settings, event metadata. PostgreSQL's JSONB type makes it tempting to dump all of it into one column and move on. Sometimes that is exactly right, and sometimes it creates a table that is slow to query and impossible to constrain. The useful question is not which option is better in general, but which fields deserve to be real columns.

What JSONB Does Well

JSONB stores a parsed binary representation of a JSON document, which allows indexing and efficient operators. It shines when the structure is genuinely variable, when different records carry different keys, or when you store data from a third party whose schema you do not control. Adding a new field requires no migration, which is valuable when a payload format changes weekly.

It also works well for data that is mostly written once and read as a whole. If your application loads the entire document, hands it to a client, and rarely filters on individual keys, a JSONB column is simple and fast enough. Keeping such a blob out of the main schema can even make the core tables easier to understand.

CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id  bigint NOT NULL,
    type       text   NOT NULL,
    payload    jsonb  NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Where JSONB Starts to Hurt

The first problem is that the database cannot enforce much about what is inside. Foreign keys cannot point at a value inside a JSON document, NOT NULL and CHECK constraints only apply to the column as a whole unless you write expressions, and JSON has no date type, so timestamps are strings that nothing validates. Data quality becomes the application's job, and mistakes become permanent rows.

The second problem is the query planner. PostgreSQL collects statistics per column, but it does not collect them for individual keys inside a JSONB value. For a filter such as payload->>'status' = 'failed', the planner has to fall back on generic selectivity estimates, which can be far from reality and lead to poor plan choices on large tables. An expression index on that exact expression gives the planner real statistics for it after an ANALYZE, which is one reason expression indexes are so useful.

The third problem is update cost. PostgreSQL's MVCC writes a new version of the row on every update, and a large JSONB value is stored out of line (TOAST) and rewritten as a whole when you change a single key. Frequently updating one small field inside a large document produces far more write amplification than updating a narrow column. If a field changes often, it belongs in its own column.

Indexing JSONB Without Overdoing It

A GIN index supports containment and key-existence queries across the whole document. The default operator class supports more operators, while jsonb_path_ops is smaller and faster for containment queries with the @> operator but supports fewer operators. Choose based on the queries you actually run, and confirm with EXPLAIN on realistic data.

-- Containment queries over the whole document
CREATE INDEX events_payload_gin
    ON events USING gin (payload jsonb_path_ops);

SELECT id FROM events
WHERE payload @> '{"status": "failed"}';

-- A single hot key: a B-tree expression index
CREATE INDEX events_status_idx
    ON events ((payload->>'status'));

A broad GIN index is convenient but expensive to maintain on write-heavy tables, and it will not help range queries or sorting on a single key. If you query one or two keys constantly, an expression index or a real column is usually a better fit than indexing everything.

The Hybrid Pattern

For most APIs, the best design is a mix. Keep identifiers, foreign keys, timestamps, status fields, and anything you filter, sort, or join on as real columns. Keep the long tail of optional or provider-specific attributes in a JSONB column. When a key inside the JSON becomes important, promote it: add a column, backfill it, and start writing both until the old path can be removed.

If you need the value as a column immediately without changing writers, a generated column can extract it from the document. Generated columns are available in modern PostgreSQL versions (stored generated columns arrived in version 12), but check the documentation for your version for restrictions.

ALTER TABLE events
    ADD COLUMN status text
    GENERATED ALWAYS AS (payload->>'status') STORED;

CREATE INDEX events_status_col_idx ON events (status);

This gives you real statistics, ordinary indexing, and a column you can constrain, while the source of truth remains the document.

Frequently Asked Questions

Is JSONB slower than normal columns?

For filtering and updating individual fields, usually yes, because of missing per-key statistics, larger rows, and whole-value rewrites. For reading an entire document, the difference is often negligible.

Can I enforce a schema on a JSONB column?

You can add CHECK constraints that use JSON operators and functions, which covers simple rules such as required keys. Complex validation is typically done in the application, and some teams use extensions for JSON schema checks.

Should I store money or timestamps in JSONB?

Prefer real columns with proper types. JSON numbers and strings carry no precision or timezone guarantees, which makes these values easy to corrupt silently.

How do I migrate a JSONB key to a column?

Add the column, backfill in batches to avoid long locks, update writers to populate both, switch reads to the column, and then stop writing the key into the document.

Summary

Use JSONB for variable, rarely filtered, mostly read-whole data, and use real columns for anything you query, constrain, join, or update often. Add expression or GIN indexes only for the access patterns you can name, and promote hot keys to columns as they prove their value. Always check operator class and generated column details against the PostgreSQL documentation for your version.