If you are running big applications on top of PostgreSQL or having a lot of queries your performance might be impacted. By default the default PostgreSQL configuration is to say the least, less than ideal. Same is true for MariaDB but that is out of scope now.
Performance fine tuning your server configuration is key for better overall improvements and making the best use of your hardware! Increasing several key configuration settings in PostgreSQL can already make a huge impact! In the guide below I explain which configuration you need to pay extra attention to and also advise to increase huge pages under Linux when you’re using PostgreSQL or MariaDB for that matter.
Please follow the latest PostgreSQL configuration I shared here for Mbin specifically, but can be applied to any application using a PostgreSQL DB: https://github.com/MbinOrg/mbin/blob/main/docs/admin_guide.md#postgresql-configuration
For more information on fine tuning MariaDB, Nginx, PHP or other Linux kernel configurations, I advise you to checkout my snippets on my GitLab instance: https://gitlab.melroy.org/-/snippets/609, https://gitlab.melroy.org/-/snippets/92, https://gitlab.melroy.org/-/snippets/87
It’s not just the size of the tarball or binary. It’s also about how it scales in terms of performance when you have 400 users per hour on your server. Mbin is using PostgreSQL, can you imagine if Lemmy would SQLite… your page will never load… haha
Lemmy can easily work with SQLite3. The bottleneck in web services is not data fetching, it is network hops. The transmission takes most amount of time. SQLite is perfect for Lemmy!
For very big services like YouTube, it will fail! The only advantage of PgSQL is it has a client server model which decreases overhead.