4 ms·
You do not want to do this, I assure you. The TOAST / DE-TOAST overhead alone would wreck performance, plus OP said it was update-heavy; Postgres can’t update a
by sgarland 2y ago
You do not want to do this, I assure you. The TOAST / DE-TOAST overhead alone would wreck performance, plus OP said it was update-heavy; Postgres can’t update a JSON object on disk, it just rewrites the entire blob.
- syncsynchalt 2y ago> Postgres can’t update a JSON object on disk, it just rewrites the entire blob. Postgres does this for _all_ row updates; it must, for transaction isolation to work.
- hashhar 2y agoI'm not sure, there's also HOT-updates (heap-only tuples). It's an optimization where data in modified in place if none of the modified columns are part of an index and maybe other conditions but I don't remember it all too well.
- syncsynchalt 2y agoDid not know this; thank you!
- pphysch 2y agoI strongly disagree. This is an excellent application for JSONB. The user outlined that the records are for historical compliance purposes and therefore aren't frequently updated after the initial phase. A schema of `party1 JSONB, party2 JSONB, ...`, or even `parties JSONB` would likely be far more maintainable and sensible than the "table-as-spreadsheet" approach.