In this blog post, the author discusses how their data team managed to run the infrastructure of their energy trading company using only two PostgreSQL database instances. They highlight the importance of optimizing queries, tuning indexes, partitioning, tuning the database server, and using read-only replicas to eliminate performance bottlenecks. The author emphasizes the need to identify inefficient queries through monitoring and to understand queries using EXPLAIN ANALYZE. They provide examples and explanations of how to optimize queries, including suboptimal join strategies, unused or missing indexes, costly sorting and aggregation operations, and filter inefficiencies. The article concludes by stating that with the right knowledge and tools, developers and database administrators can ensure optimal database operation.
https://robertrode.com/2023/11/15/operating-an-entire-company-on-a-minimal-two-core-postgresql-instance-query-optimization-insights-part-1.html