3 ms·
Often true, but updates of JSON documents in postgres or inserts into nested arrays or objects are much, much slower, and involve locking the entire row. I thin
by mplanchard 2y ago
Often true, but updates of JSON documents in postgres or inserts into nested arrays or objects are much, much slower, and involve locking the entire row. I think if your use case is insert/read only, it works well, though at scale even that can become an issue due to the GIN fastupdate index logic, which can lead to the occasional 20-30 second insert as the buffer gets flushed and the index updated.
- GordonS 2y ago> it works well, though at scale even that can become an issue due to the GIN fastupdate index logic, which can lead to the occasional 20-30 second insert as the buffer gets flushed and the index updated. Hmm, interesting! I've been having some intermittent issues recently with a Postgres table that uses JSONB, where everything seems to lock up for several minutes at a time, with no useful logs as to why. Do you have any more info about the GIN issue, and how it can be verified as being a problem please?
- joaohaas 2y agoA quick search brought this up: https://www.louisemeta.com/blog/indexes-gin-algorithms/ https://www.louisemeta.com/blog/indexes-gin-algorithms/ I think it illustrates the issue he's mentioning well enough.
- mplanchard 2y agoThis gitlab issue where they disabled it on one of their tables helped me a lot with understanding and debugging the problem: https://gitlab.com/gitlab-com/gl-infra/production/-/issues/7840 https://gitlab.com/gitlab-com/gl-infra/production/-/issues/7... It’s not a super well-documented feature in postgres, so I also wound up doing some digging in the mailing lists, but I was really appreciative of all the detail in that GL issue and the associated links.
- GordonS 2y agoThis is absolute gold, thanks!