Should I use JSON in PostgreSQL?
summary
Conclusion: PostgreSQL works well with JSON when design splits stable columns (id, foreign keys) from dynamic fields stored as JSONB; the demo builds bands, albums and reviews tables with JSONB payloads, adds primary/foreign keys, creates a unique expression index on a JSONB key, and enforces types via CHECK casting to double precision — all queries and indexes run in the demo and return correct results. Performance notes: JSONB updates rewrite the value (large blobs can be expensive) and PostgreSQL 16 adds improved JSON/SQL checks. use JSONB not JSON B-tree for scalars, GIN for arrays, pg_trgm for text validate with CHECK casting or indexed expression
AI-written summary · about
chapters (13)
- 0:00Introduction
- 1:17Why JSON
- 1:33Schema Freedom
- 4:52JSON Object
- 5:42Metal Bands
- 8:28Splitting JSON Objects
- 9:22Review
- 11:53Paths
- 12:51Updating JSON values
- 22:14Search
- 28:22Index
- 33:27SQL Json
- 40:47Questions
related talks
05mPostgreSQL JSON JSONB Making the Right Choice for Your Application
48mPostgres and Json - How to get the best of them?
25mJSON in PostgreSQL - evil data type or just needs to be tamed?
35mLeveraging PostgreSQL 17's JSONB for Enhanced Data Modeling
14mJSON data structure – the hidden gem inside your database engine
42mNoSQL as Not Only SQL
50mUsing JSON with Postgres 9 3
23mEven JSONB In Postgres Needs Schemas