What a JSONB form engine cost us
Notes on backing a multi-tenant form engine with PostgreSQL JSONB — where it bought configuration without deployments, and where it quietly bought a sequential scan.
JSONB is sold as the escape hatch from migrations. That is true right up to the point where a column you never indexed becomes the column every request filters on.
Where it earned its place
Product shapes that genuinely varied per tenant. Storing them as columns meant a
migration every time a customer asked for one more field, and a table that was
mostly nulls. One jsonb column collapsed that.
Where it cost
Two things surprised me:
- A
->>filter without a matching expression index is a sequential scan, and it stays cheap until the table is large enough that it is not. jsonb_setrewrites the whole document. Under write contention, that shows up as bloat long before it shows up as latency.
What I would do again
Index the access path, not the column. Keep anything you filter or sort on in real columns, and let JSONB hold the parts you only ever read back whole.