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.…