Understanding of Jsonb Performance
summary
Benchmarks show Jsonb cost depends on size, nesting and storage layout not just “it's JSON”; tests across 1 to 1,000,000 rows and concurrent writers reveal extraction and subscripting are cheap on small root fields but degrade as nesting grows, and 2 KB page threshold causes on‑access decompression and big jumps in latency (examples around 500+ ms read spikes). Operator timings favor exists/contains fastest while match and deep path access cost much more; TOAST, chunking, pageinspect and a streaming extension are used to measure and mitigate real update and read slowdowns.
AI-written summary · about
chapters (13)
- 0:00JSONB performance challenges
- 5:02JSONB popularity in PostgreSQL
- 9:35Nested JSON performance impact
- 15:54Extract vs subscripting in JSONB
- 20:12JSONB operators comparison
- 25:19TOAST compression for large JSON
- 31:39TOAST partial update pitfalls
- 36:02JSONB sorting and access patterns
- 40:53JSON format conversion costs
- 43:17Relational vs JSONB optimization
- 46:52Updating JSON arrays efficiently
- 47:48Streaming JSON with TOAST
- 50:05Community benchmarks
related talks
53mNoSQL Best Practices for PostgreSQL
42mNoSQL as Not Only SQL
35mLeveraging PostgreSQL 17's JSONB for Enhanced Data Modeling
53mPostgres vs Mongo
40mFast Access To Your Complex Data #bbuzz
40mThings your explain plan is not telling you
51mDealing with JSON in the relational world
05mPostgreSQL JSON JSONB Making the Right Choice for Your Application