JSONB in PostgreSQL: flexible schemas and performant queries

Ever needed to add a field to a relational database just for one client, ending up with twenty sparse columns half of which are null? It happens when data doesn't follow a fixed shape. E-commerce products with varying attributes, heterogeneous event logs, user metadata that changes without notice — the real world doesn't fit into a rigid schema. We, at Meteora Web, have been through it dozens of times: managing warehouses with different seasons, discounts that come and go, and ad conversions with fields named differently by every platform.

PostgreSQL offers a solution that is not a compromise: JSONB. A binary format for JSON data that integrates with the relational engine, supports constraints, transactions, and advanced indexing. No extra NoSQL database, no double truth. In this guide we'll cover how to use JSONB for flexible schemas without sacrificing performance. Real code, real queries, no slideware.

Why JSONB instead of plain TEXT or JSON

PostgreSQL has two types for JSON data: json and jsonb. The difference is far from academic. json stores the exact text, including spaces and key order. jsonb converts it to a decomposed binary representation: keys are sorted and duplicates removed. This means:

• Faster queries: no need to re-parse on every access.
• You can create indexes (GIN, GiST, and B-tree on expressions).
• Native operators like @>, ?, ?|, ?& for structure queries.
• Consumes more disk space (roughly 10-20% more than the corresponding text), but the performance gain is worth it for any realistic workload.

Rule of thumb: always use jsonb unless you need to preserve exact key order (rare cases like signature hashes).

Creating and populating a table with JSONB

Let's start with a concrete example. Imagine an application managing contracts for different clients: each contract has fixed fields (client, date) and a variable section (custom clauses, attached files, renewal dates).

CREATE TABLE contracts (
  id SERIAL PRIMARY KEY,
  client_id INTEGER NOT NULL REFERENCES clients(id),
  date_signed DATE NOT NULL,
  variable_data JSONB NOT NULL DEFAULT '{}'
);

-- Insert a contract with variable clauses
INSERT INTO contracts (client_id, date_signed, variable_data)
VALUES (
  42,
  '2026-03-01',
  '{ "type": "annual", "auto_renew": true, "loyalty_discount": 15, "attachments": ["terms_and_conditions.pdf", "privacy_notice.pdf"] }'
);

Notice the JSONB value contains mixed types (boolean, number, array). Postgres handles them without issue. No migration, no extra columns.

Essential operators for querying JSONB

Here are the operators we use every day:

• -> (text) → returns the field as JSON (null, object, array).
• ->> (text) → returns the field as text (string).
• #> (text[]) → nested path, returns JSON.
• #>> (text[]) → nested path, returns text.
• @> → checks if a JSONB document contains another (substructure).
• ? → key exists at the first level.
• ?| → at least one of the keys exists.
• ?& → all keys exist.

Some queries on our example:

-- Contracts with auto renew
SELECT * FROM contracts
WHERE variable_data -> 'auto_renew' = 'true'::jsonb;

-- Contracts with loyalty discount greater than 10
SELECT * FROM contracts
WHERE (variable_data ->> 'loyalty_discount')::numeric > 10;

-- Contracts that contain the attachment 'terms_and_conditions.pdf'
SELECT * FROM contracts
WHERE variable_data @> '{ "attachments": ["terms_and_conditions.pdf"] }'::jsonb;

Be careful: @> checks for the exact substructure, not a single occurrence in an array. To search inside arrays use jsonb_array_elements() or a GIN index with jsonb_path_ops.

Indexing JSONB to avoid pain

Without an index, every JSONB query triggers a sequential scan. With thousands of rows, the database becomes a mattress. Postgres offers two GIN index types for JSONB:

• Default (jsonb_ops): indexes every key, value, and path. Supports @>, ?, ?|, ?&.
• jsonb_path_ops: indexes only the paths as compressed B-trees. 2-5 times faster for @> queries, but doesn't support key existence operators (?).

-- GIN jsonb_ops index (default)
CREATE INDEX idx_contracts_variable_jsonb ON contracts USING GIN (variable_data);

-- GIN jsonb_path_ops index (faster for @>)
CREATE INDEX idx_contracts_variable_path ON contracts USING GIN (variable_data jsonb_path_ops);

If your queries use both @> and ?, keep the default. If you only search for substructures (typical when every document has a well-defined but variable shape), jsonb_path_ops is the right choice.

Manipulating JSONB: updating and modifying

Even with a flexible schema, sometimes you need to update a single field without rewriting the entire document. Postgres provides functions like jsonb_set() and jsonb_insert().

-- Update the loyalty_discount value
UPDATE contracts
SET variable_data = jsonb_set(
  variable_data,
  '{loyalty_discount}',
  '20'::jsonb,
  true  -- create key if missing
)
WHERE id = 1;

-- Append a new attachment
UPDATE contracts
SET variable_data = jsonb_set(
  variable_data,
  '{attachments}',
  variable_data -> 'attachments' || '"new_document.pdf"'::jsonb,
  true
)
WHERE id = 1;

Warning: every modification to a JSONB field rewrites the entire value. For large documents with frequent updates, consider normalizing the variable part into separate rows in an contract_attributes table.

Common pitfalls with JSONB

1. Treating JSONB as a pure document database

JSONB is not MongoDB. It doesn't support native joins across documents, no cross-document atomicity, and complex queries on nested arrays can be slow even with indexes. We've seen projects where everything was dumped into a single JSONB field, only to rewrite the application six months later because performance degraded. Use JSONB for data that changes shape, not for relationships.

2. Ignoring data types in queries

When you use ->> you always get text. If you compare against a number, you must cast. Forgetting this leads to lexicographic comparisons.

-- WRONG: loyalty_discount as text
WHERE variable_data ->> 'loyalty_discount' > '10'
-- Returns '9' if present? Yes! Because '9' > '10' in string order.

-- CORRECT: explicit cast
WHERE (variable_data ->> 'loyalty_discount')::numeric > 10

3. Creating indexes on fields not used in queries

A GIN index on JSONB is useless if your queries only filter on relational columns. Always measure with EXPLAIN ANALYZE before and after.

When to stick with a plain relational column?

JSONB is great, but it's not the answer to everything. Don't use it for:

• Data with a fixed and stable structure (e.g., name, surname, email).
• Fields needed as foreign keys or uniqueness constraints (use a separate column).
• Attributes frequently queried as the main filter (better to have a B-tree indexed column).

The rule we follow: relational for canonical entities, JSONB for exceptions and dynamic configuration. A product has name and price in fixed columns (for aggregations and joins), and extra attributes (color, cut, materials) in JSONB.

In a nutshell — What to do now

1. Analyze your variable data: are there fields that vary per record type? If yes, move them to a JSONB column.
2. Create a GIN index right after deploy. Prefer jsonb_path_ops if you use @>.
3. Write queries with explicit casts for numbers, and always run EXPLAIN ANALYZE to verify index usage.
4. Don't normalize everything into JSONB: keep relational columns for structured and indexed data.
5. Design for access patterns: if you update a single JSONB field frequently, consider a separate key-value table.

JSONB is a powerful tool when used with discipline. We've integrated it into platforms managing catalogs of thousands of products with category-specific attributes, and it works. As we like to say: a website is measured in revenue, not compliments. A database that scales without pain is part of that revenue. If you want to dive deeper, the official PostgreSQL documentation on JSON types is the bible. And if you have a project where JSONB might be the right fit, let's talk.