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
What about it :P? SQLite is also nice... But I rather go to PostgreSQL on production systems.