PieFed.World further DB optimizations, DB host specs increased

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.