Menu

JSONB vs columns in PostgreSQL, measured on 2 million rows
📰

JSONB vs columns in PostgreSQL, measured on 2 million rows

DEV Community: postgres·ImportStatic·11 days ago
#IkEsngfh
#devtopostgresql#devto#dev#status#index
Long article (1626 words) — no AI summary yet.
Reading 0:00
15s threshold

Originally published on ImportStatic .

Storing rows as a jsonb document in PostgreSQL instead of typed columns costs a lot in storage and updates and little in indexed lookups, and a GIN index beats a single-column btree when a query combines keys. Those are the results of loading the same 2 million orders into both layouts on PostgreSQL 18.6 and comparing storage, filters, aggregates, indexes, planner estimates and updates. The question came from a Hacker News thread about shredding JSON columns , which asked how much difference remains in Postgres once the JSON is properly indexed.

The planner's row estimates differ too. For status = 'pending' on the column table it estimates 487,533 rows; for doc->>'status' = 'pending' it estimates 10,000; 500,624 rows match.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.