How we optimized PostgreSQL queries 100x

A bunch of nice tips and tricks that can be used to optimize Postgres

There are numerous posts about PostgreSQL optimization: the rabbit hole is deep. When I began coding the analytics backend several years ago, I already had experience with other DBs such as MySQL and SQL Server. Still, I never had to focus on the performance so much. The previous projects I worked on either weren’t strict about the processing time (DS/ML) or didn’t involve crunching many rows at once (regular web apps).

…

Like with the optimization of the Python code of the API server, there wasn’t a magic spell that solved all our SQL performance problems. Nevertheless, I will mention a few sound ideas that made a difference for Athenian; I hope some of them may help the reader. Of course, I will not reveal sacred secrets here; I wish I had read something like the following when we started.