Leveraging PostgreSQL 17's JSONB for Enhanced Data Modeling
summary
PostgreSQL 17 adds concrete JSONB features: JSONB binary storage lets reads avoid per-row JSON parsing (example dataset: five million rows generated with a Python faker), yielding a full-table key lookup in ~0.5s versus ~2.88s for JSON and ~1s for plain text. Indexing with a GIN index and pending list drops some queries to sub-millisecond but worsens writes — updates run ~1.1s without index, ~9ms with the GIN index, ~77ms if fastupdate is disabled. NewJSON_TABLE lateral join and JSON path operators materialize nested arrays into columns and work in milliseconds.
AI-written summary · about
chapters (12)
- 0:00Introduction
- 2:37JSON in PostgreSQL history
- 5:54JSON vs JSONB storage
- 9:27Benchmarking JSONB
- 13:46GIN indexes for JSONB
- 17:21JSON path queries
- 20:31JSON table for virtual tables
- 22:40PostgreSQL vs MongoDB
- 27:02Why PostgreSQL for JSON
- 29:12Considerations and limitations
- 29:53PG Day Napoli 2025
- 30:36Q&A
related talks
05mPostgreSQL JSON JSONB Making the Right Choice for Your Application
42mNoSQL as Not Only SQL
48mPostgres and Json - How to get the best of them?
49mShould I use JSON in PostgreSQL?
14mJSON data structure – the hidden gem inside your database engine
25mJSON in PostgreSQL - evil data type or just needs to be tamed?
48mThe road to new SQL/JSON features
46mUse PostgreSQL, not Rails, to make Rails Faster!