GREAT NEWS about Lemmy Server Performance, another major SQL mistake has been discovered today: every single comment & post create (INSERT) is updating ~1700 rows in the site_aggregates table
GREAT NEWS about Lemmy Server Performance, another major SQL mistake has been discovered today: every single comment & post create (INSERT) is updating ~1700 rows in the site_aggregates table
Details here: https://github.com/LemmyNet/lemmy/issues/3165
This will VASTLY decrease the server load of I/O for PostgreSQL, as this mistaken code is doing writes of ~1700 rows (each known Lemmy instance in the database) on every single comment & post creation. This creates record-locking issues given it is writes, which are harsh on the system. Once this is fixed, some site operators will be able to downgrade their hardware! ;)
You're viewing a single thread.
This is fascinating
My biggest takeaway from reading through the GitHub comments though is that it seems like no one actually knows where much of the SQL comes from? As in it's possible that the bug in question is just one manifestation of old, handwritten Postres code that may or may not be optimized (Or even logical?).
I don't mean this in a critical way, as things like this are bound to happen in an open-source, federated world. However, I would think a comprehensive audit of the Lemmy Postgres triggers, queries, etc could potentially save us all from some future headaches.