How I Finally Got JSONB Right in PostgreSQL

I avoided JSONB for years ( like, actual years ). Not because I didn't know it existed, but because every time I reached for it, someone would say "just use a separate table" and I'd back off. Turns out, they were wrong. Not always wrong, but wrong enough that I wasted a lot of time normalising data that should have stayed as JSON.

Here's what I wish I'd known from day one.

Why JSONB ( And Not JSON )

Code on screen showing database query — The moment you realise JSONB indexes are actually fast. ( Yes, really. )

PostgreSQL has two JSON types: json and jsonb. Use jsonb. Always. The plain json type stores text, which means it parses every single time you query it. JSONB stores a binary representation, so reads are fast and you can index it.

The storage overhead is marginal. The performance gain is not.

When JSONB Actually Makes Sense

I use JSONB in three specific situations:

1. Schema-less data from external APIs

When you're ingesting data from third-party APIs that change their schema without warning ( looking at you, every payment provider ever ). You don't want a migration every time Stripe adds a field. Store the payload in JSONB, extract what you need with generated columns.

2. Configuration and settings

User preferences, feature flags, per-tenant configs. These are write-rarely, read-often, and each record has a different shape. A settings table with 50 nullable columns is worse than a JSONB column. Fight me.

3. Event / log data

Audit logs, analytics events, error reports. Each one has a different structure. You need the common fields indexed ( timestamp, user_id, event_type ) and the rest can live in JSONB.

Indexing JSONB ( The Part Everyone Gets Wrong )

GIN index. That's it. That's the section.

Alright, slightly more detail. Here's the pattern I use:

-- GIN index for @> contains queries and ? key existence
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- GIN with jsonb_path_ops for specific path queries
CREATE INDEX idx_events_path ON events USING GIN (payload jsonb_path_ops);

-- For queries on a specific nested key, use a generated column + btree
ALTER TABLE events ADD COLUMN event_source TEXT
  GENERATED ALWAYS AS (payload->>'source') STORED;

CREATE INDEX idx_events_source ON events (event_source);

The jsonb_path_ops variant is smaller and faster for exact path queries. The default GIN operator class supports more query types but is bigger. I default to jsonb_path_ops unless I need ? or ?|> operators.

Querying JSONB Without Losing Your Mind

The operators I actually use day to day:

-- Get a value as text
SELECT payload->>'email' FROM users;

-- Get a value as JSONB ( keeps type )
SELECT payload->'address'->>'city' FROM users;

-- Contains operator ( uses GIN index )
SELECT * FROM events WHERE payload @> '{"type": "click"}';

-- Key exists
SELECT * FROM events WHERE payload ? 'error_code';

-- Filter on nested array
SELECT * FROM orders
WHERE items @> '[{"sku": "ABC123"}]';

The -> vs ->> distinction trips up everyone at first. -> returns JSONB ( preserves type ), ->> returns text. Chain -> for nesting, end with ->> when you want a string out. That's it.

Generated Columns Are the Cheat Code

Here's the pattern that finally made JSONB click for me: store everything in JSONB, but extract the fields you query on into generated columns.

CREATE TABLE api_responses (
    id BIGSERIAL PRIMARY KEY,
    endpoint TEXT NOT NULL,
    payload JSONB NOT NULL,

    -- Extracted fields for indexing
    status_code INT GENERATED ALWAYS AS ((payload->>'status')::int) STORED,
    created_at TIMESTAMPTZ GENERATED ALWAYS AS ((payload->>'timestamp')::timestamptz) STORED
);

-- Index the generated columns ( way faster than JSONB path queries )
CREATE INDEX idx_api_status ON api_responses (status_code);
CREATE INDEX idx_api_created ON api_responses (created_at);

Now you get the flexibility of JSONB and the query performance of regular columns. Best of both worlds. No triggers, no application-level syncing, no stale data. PostgreSQL handles it.

Updating JSONB Without Replacing the Whole Thing

The || operator merges JSONB objects. The - operator removes keys. This is how you update without reading the whole thing first:

-- Add or update a key
UPDATE users
SET payload = payload || '{"last_login": "2026-08-06"}'
WHERE id = 42;

-- Remove a key
UPDATE users
SET payload = payload - 'temp_field'
WHERE id = 42;

-- Update nested value
UPDATE users
SET payload = jsonb_set(payload, '{address,city}', '"Montevideo"')
WHERE id = 42;

-- Remove from nested path
UPDATE users
SET payload = payload #- '{address,floor}'
WHERE id = 42;

Stop doing the read-modify-write dance in your application. Let PostgreSQL handle it.

Performance Reality Check

JSONB is slower than regular columns. Duh. But "slower" means different things:

- Point lookups with GIN: ~2-5ms on a million-row table. Fine. - Sequential scan with no index: don't do this on large tables. - jsonb_path_ops index size: roughly 30-50% of table size. Manageable. - Default GIN index: bigger. Only use if you need key-existence queries.

The real killer is updating JSONB on large rows. Each update rewrites the entire JSONB value. If your payload is 50KB and you update one field, PostgreSQL writes 50KB. Use generated columns for hot fields and keep your JSONB payloads lean.

Conclusion

JSONB is not a replacement for proper schema design. It's a tool for data that genuinely doesn't have a fixed shape. Use it for API payloads, configs, and events. Index it with GIN. Extract hot fields into generated columns. Don't overthink it.

I've been running JSONB columns in production for two years now. Zero regrets. The migration from that horrific 47-column settings table was the best schema change I've ever made.