JSON in PostgreSQL - evil data type or just needs to be tamed?
summary
Shows concrete fixes: extract IDs into columns so foreign keys work, add a unique index on JSON->>'id', and pull Frequently Updated Fields (likes/shares) out of big JSON to avoid MVCC bloat (1MB tuple grows to 4MB after repeated updates). Recommends indexing with GIN inverted indexes for arrays, pg_trgm trigrams for long text, and B-tree for scalar text; demonstrates casting + CHECKs to enforce types, and explains TOAST behavior and optional partitioning by JSON-derived country values.
AI-written summary · about
chapters (12)
- 0:00Music & introduction
- 1:12The power of schema freedom
- 2:49Metal music use case with JSON objects
- 3:48Designing tables with JSONB columns
- 5:37Extracting ids for relational integrity
- 7:46Hybrid schema design with metal album reviews
- 9:04Indexing JSON for performance gains
- 13:09Trigram (pg_trgm) indexing for text search
- 15:06Enforcing types and constraints in JSON
- 19:13Schema changes and update tradeoffs
- 21:12Optimizing updates with “frequently updated fields”
- 23:21Key takeaways and design principles
related talks
49mShould I use JSON in PostgreSQL?
14mJSON data structure – the hidden gem inside your database engine
05mPostgreSQL JSON JSONB Making the Right Choice for Your Application
57mUnderstanding Advanced Datatypes in PostgreSQL
48mPostgres and Json - How to get the best of them?
35mLeveraging PostgreSQL 17's JSONB for Enhanced Data Modeling
50mUsing JSON with Postgres 9 3
42mNoSQL as Not Only SQL