PieFed.World Announcements

@MrKaplan@piefed.world was in the matrix chat the other day talking about the changes. I think rimu already upstreamed most of them into the project (codeberg). It primarily consisted of creating indices to speed up queries and to do COUNT queries in a better way (codeberg).

DB optimization remains an ever-ongoing thing, and sometimes things like the ORM used (sqlalchemy) kind of gets in the way of crafting efficient queries. So, as more and more people are making piefed accounts, and as talented sysadmins like on piefed.world are running instances, we are learning where things can get better.

mostly indexes and in some cases changing queries.

there are lots of queries in piefed that are not using indexes and some that are just unoptimized sql statements. for example, there are a lot of like '%foo%' queries but there are no GIN indexes to support those queries.

unfortunately it's a hassle to manage custom indexes as they can't be part of the code migrations without fucking other things up, so for the time being i'm just keeping track of them by having a dedicated prefix for the index names and updating them in our DB manually. once/if they're upstreamed i'm removing our custom ones again, but it's not possible for us to track them in code.

some of the code changes for sql statements were upstreamed, but rimu didn't want to incorporate all of them without evidence of performance impact. it took me a bit to make sense of the more complex statements, but i don't have time or motivation to demonstrate performance impact for them, so i'll just keep the remainder in our fork. e.g. 1bf4d8409b vs c41d47850e.