Re:Microsoft open sources PostgreSQL extensions to muscle in...
by mplanchard on Tuesday February 11, 2025 @10:12AM
(#43013689)
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.
[ Parent
| on HN ]
Re:Microsoft open sources PostgreSQL extensions to muscle in...
by GordonS on Tuesday February 11, 2025 @10:28AM
(#43013908)
> 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?
Re:Microsoft open sources PostgreSQL extensions to muscle in...
by mplanchard on Tuesday February 11, 2025 @11:47AM
(#43014987)
This 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/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.
Re:Microsoft open sources PostgreSQL extensions to muscle in...
by GordonS on Wednesday February 12, 2025 @10:46AM
(#43026491)
This is absolute gold, thanks!
Re:Microsoft open sources PostgreSQL extensions to muscle in...
by joaohaas on Tuesday February 11, 2025 @10:37AM
(#43014039)
A quick search brought this up: https://www.louisemeta.com/blog/indexes-gin-algorithms/
I think it illustrates the issue he's mentioning well enough.