Postgres Performance Troubleshooting – An Example

Recently we encountered some severe performance problems in one of our projects. Some SQL queries were very slow.

A certain query took over 6 seconds to finish in Azure’s Postgres service and about 350ms locally on my laptop. Both are unacceptable.

I don’t know much more than the postgres basics and the project was in all parts relevant here more or less new to me. But let me show you how I approach the unknown, what I learned and how I solved it.

By the way: The issue is a prime example of not spending more time on optimisation than what feels necessary at the time. So even though the initial code looks a bit dumb in hindsight I still consider this ok as the application was fast enough for over 2 years in production and as we later learned the issue was caused by some up to that point unprecedented data.

Now, let’s dive in!

Continue reading