4 ms·
Sounds like it's rife to convert to JSON fields maybe?
by mkesper 2y ago
Sounds like it's rife to convert to JSON fields maybe?
- magicalhippo 2y agoIf we could limit us to only PostgreSQL we could perhaps do that for new modules. Sadly we have some customers which want to use their own MSSQL database. Though we'll see what the future holds. PostgreSQL is interesting due to cost and features, and many of the large customers are shifting their POV on how they want to run things, almost doing 180's in some cases.
- sgarland 2y agoYou 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.