JSONB or regular columns? Storing flexible data in Postgres
Short answer: Use regular columns for anything your app filters, sorts, joins, validates or reports on, and keep JSONB for genuinely variable data, such as product attributes that differ by category, form answers, or raw payloads from another service. JSONB is flexible and can be indexed, but it gives up the database's type checks, constraints and simple queries. A common, safe pattern is a normal table with a JSONB column for the extras, plus real columns for the fields that matter.
Why the question comes up
Business apps keep meeting data that doesn't fit a fixed shape. A shop sells shoes with sizes and widths, lamps with bulb types and wattages, and gift cards with neither. A booking form gains a new question every few months. An integration sends a webhook payload with dozens of fields, only three of which you use.
Adding a column for every possible attribute gets messy fast. Putting everything into one JSON blob feels simple. Both extremes cause problems later, so it's worth knowing what each really costs.
What JSONB is
PostgreSQL has two JSON types. Its documentation explains the difference: json stores an exact copy of the input text, which must be reparsed on every use, while jsonb stores data in a decomposed binary format that's slightly slower to write but significantly faster to process. jsonb doesn't preserve details like whitespace or key order, and it keeps only the last value for duplicate keys. For app data, jsonb is almost always the one you want.
JSONB can also be indexed. The documentation describes GIN indexes for efficiently searching keys or key-value pairs inside many JSONB documents, with a default operator class and a more compact jsonb_path_ops class that supports containment queries like @> but not key-exists operators.
What you give up with JSONB
Flexibility has a price, and it's paid later:
- Types. A column declared
numericrefuses text. Inside JSONB,"price": "12,50"and"price": 12.5can both be stored, and your app has to cope. - Constraints.
NOT NULL, foreign keys and unique constraints work naturally on columns. In JSONB, you'd need check constraints or app-level validation. - Readable queries.
WHERE status = 'paid'is clearer thanWHERE data->>'status' = 'paid', and reporting tools handle columns far better. - Statistics. The query planner knows a lot about a column's values and much less about values buried in JSON, so it can choose poorer plans.
- Renames. Renaming a key means updating every row's JSON, not one
ALTER TABLE.
None of this rules JSONB out. It means JSONB should hold data where those benefits aren't needed.
A practical rule of thumb
Ask of each field: will the app filter, sort, join, total or enforce rules on it?
| Field | Example | Store as |
|---|---|---|
| Core identity and status | Order ID, customer ID, status, total | Columns |
| Fields used in reports | Created date, region, channel | Columns |
| Category-specific attributes | Shoe width, bulb type | JSONB |
| Custom form answers | "How did you hear about us?" | JSONB |
| Third-party payloads | Full webhook body, kept for reference | JSONB |
| Settings with many optional keys | Notification preferences | Either; JSONB is often simpler |
When a JSONB field starts appearing in filters and reports regularly, promote it to a column.
The hybrid pattern
Most apps end up with tables like this:
CREATE TABLE products (
id bigint PRIMARY KEY,
sku text NOT NULL UNIQUE,
name text NOT NULL,
category_id bigint NOT NULL REFERENCES categories(id),
price numeric(10,2) NOT NULL,
attributes jsonb NOT NULL DEFAULT '{}'
);
Everything the shop logic depends on is a real column with types and constraints. The attributes column holds whatever differs by category. If you need to search attributes, add a GIN index on it.
Promoting a JSONB field later
When one attribute becomes important, say colour is now a filter on every category page, you have options:
- Add a real column, back-fill it from the JSON and update the app to write both until the old key can be removed.
- Use a generated column, which PostgreSQL computes from the JSON automatically, giving you a normal column to index and query without changing how data is written.
- Add an expression index on the specific key, for example on
(attributes->>'colour'), if you only need fast lookups.
Promoting gradually like this is far easier than designing everything perfectly up front.
Validate JSONB in the app
Because the database won't check the shape of JSONB for you by default, validate it where it's written:
- Define a schema for each kind of JSON document, per product category or form type, and check it before saving.
- Store numbers as numbers and dates in one agreed format.
- Keep key names consistent, such as all lowercase with underscores.
For critical rules, you can add CHECK constraints that test for required keys, but app-level validation is usually easier to maintain.
When not to use JSONB at all
Avoid JSONB as a substitute for proper tables when data has its own life: order line items, addresses a customer reuses, or anything other records refer to. Those belong in their own tables with foreign keys. A list inside JSON can't be referenced, constrained or updated item by item without rewriting the whole document.
A worked example
Consider a hypothetical Bristol homeware shop whose developer stored entire products, including price and stock level, in one JSONB column. Discounts occasionally failed because some prices had been saved as text, and stock reports were slow and awkward to write. The team moved price, stock, SKU and category into typed columns with constraints, kept category-specific attributes in JSONB with a GIN index, and added a generated column for colour once it became a filter. Reports became plain SQL, and the text-price bug became impossible.
FAQ
Is JSONB slower than columns?
For simple filters and sorts on known fields, columns are usually faster and easier to index well. JSONB with the right index can be fast for containment searches.
Can I put a foreign key inside JSONB?
Not directly. If a value refers to another table, it should be a column.
Should I use a document database instead?
Only if most of your data is truly document-shaped. Many business apps are mostly relational with some flexible parts, which PostgreSQL handles well.
If your app's data model has become a tangle of columns or one big JSON blob, ask us to review your schema.