Hello everyone,

we have upgraded our DB host to a larger SKU now to better deal with the performance bottlenecks we've been seeing.
We already optimized a number of additional queries, but the overall state of DB optimization for PieFed still has lots of room for improvement. For now, we hope that this will result in a decent overall improvement for query performance, and we'll continue to tackle individual slower queries.

The DB server specs have been increased from 3 core, 4GB memory to 4 core, 8GB memory, which should allow the database to keep a lot more data in memory, which reduces the amount of data that needs to be read from disk.

One of the main performance bottlenecks we're currently looking into is the API method for listing posts, which can take multiple seconds in some cases. If you're experiencing slowness in other areas, please let us know in a comment. We're checking the logs from time to time to identify other struggling queries, but it can sometimes take extra time to track down where the queries were coming from, if the logs are older than 15 minutes.

We'll also be looking into upgrading the database from PostgreSQL version 17 to version 18 soon, which also might improve performance a bit.

What kind of optimizations are you doing? Just adding more indexes or tweaking the settings or what?

@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.

Thanks for the info!

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.

Great work as always. The unsung hero of the threadiverse