JSONB is the escape hatch I reach for when the schema isn't settled yet. It let us ship a product before every attribute deserved a column, and it absorbed integrations, feature flags, and customer-specific metadata without a migration each time.
The trouble started, as it always does, when the escape hatch became the main hallway. A document column we treated as a convenience was suddenly in the hot path of half our queries, and the database let us know.
Index for the query you actually run
My first mistake was reaching for a default GIN index and assuming it would cover everything. A plain GIN index on a JSONB column supports containment (the @> operator) well, but my real queries were looking up a single key's value, and those weren't using it the way I expected.
The fix was matching the index to the access pattern. For key/value lookups, the jsonb_path_ops GIN variant is smaller and faster for containment, and for a specific hot key an expression index on just that key beats indexing the whole document.
-- Hot lookups on one key: index just that key
CREATE INDEX orders_status_idx ON orders ((data->>'status'));
-- Containment queries across keys: smaller, faster GIN
CREATE INDEX orders_data_gin ON orders USING gin (data jsonb_path_ops);Wide documents drag TOAST behind them
The slow endpoint that finally got my attention wasn't doing anything exotic. It selected the whole row, including a fat JSONB document that had grown past the point where PostgreSQL stores it out of line in TOAST. Every returned row meant an extra fetch and a decompression.
I stopped selecting the document when I didn't need it. A list view that shows a status and a date has no business pulling a 30KB payload for every row. That one change did more than any index.
Know when it should have been a column
JSONB is great until an attribute becomes load-bearing. The moment a key is in every WHERE clause, sorted on, and joined against, it has earned a real column with a real type and a real index. Promoting hot keys out of the document is not a failure of JSONB; it's using it for what it's good at and moving on when the requirements harden.
- Match the index to the query: expression index for hot keys, jsonb_path_ops for containment.
- Don't SELECT the document on paths that don't render it.
- Promote load-bearing keys to typed columns.
- Watch TOAST size on JSONB-heavy tables.
What I keep coming back to
None of this is clever. Name the failure mode, capture the before number, change one thing, and compare the same signal afterward. The boring version of database work is the version that actually holds up at 2 AM.