PostgreSQL type decision
PostgreSQL JSONB vs JSON
Choose between jsonb and json in PostgreSQL based on indexing, write behavior, key order, and whether the original JSON text must be preserved.
Use jsonb for application data you will query. Use json only when preserving the original JSON text, including whitespace, key order, or duplicate keys, is part of the requirement.
- jsonb
- Queryable documents, containment filters, GIN indexes
- json
- Raw payload preservation and write-once pass-through data
A concrete schema
create table events (
id bigint generated always as identity primary key,
payload jsonb not null
);
create index events_payload_gin on events using gin (payload);
select *
from events
where payload @> '{"type":"invoice.paid"}';When jsonb is the better fit
Queryable documents, containment filters, GIN indexes.
When json is the better fit
Raw payload preservation and write-once pass-through data.
The tradeoff that matters
jsonb parses the input into a decomposed representation, so inserts do a little more work. In return, reads can use operators and indexes effectively. json stores the submitted text and reparses it during processing.
A migration note
Changing a column type in a live database can rewrite the table, rebuild indexes, or break application assumptions. Inspect dependent foreign keys and run the conversion against production-sized data before scheduling the migration.
Questions that come up
Is jsonb always better than json?
No. The choice follows the meaning of the data and the queries you need to run. A blanket rule hides the one requirement that should decide the column.
Can I change the type later?
Usually, but the migration may require an explicit USING expression and can lock or rewrite a large table. Test the exact ALTER TABLE statement on a recent copy first.
Where can I try the schema?
Use the PostgresGUI SQL editor for syntax work or the schema designer to model the table and export PostgreSQL SQL.