This is a brief retro on some downtime we experienced at dev.to in the past day.
I posted this in the process of investigating the issue:
Rhymes was on the right track with this response.
What Heroku plan is dev.to on? It looks like it has something to do with Heroku's Postgres balancer.
Have you tried investigating with
SELECT * FROM pg_stat_activity when that happens?
Here are the details of the table: postgresql.org/docs/10/static/moni...
Even though I guess that if you run out of connections there's no time to investigate...
The problem ended up being that we were periodically surpassing our maximum connections on our managed Postgres plan. The difficulty in getting to the bottom of this was that it would peak and then get better on its own, and in the meantime we were looking at our query efficiency, and missing some of the signals.
Most of our numbers associated with our current plan were safely below our limits, and by the time we checked on current connections, it didn't seem like the problem at the time.
Going forward, we need to get better at monitoring and keeping a good understanding of the various bottlenecks of our services. This was the case of setting and forgetting something that worked for a while, and once the issues started popping up, we were caught off guard by some red herrings.
Each of these experiences is a massive learning opportunity and I'm much more confident in my and my team's grip on these modest database scaling issue's we've faced.
I say modest because this was a pretty simple situation at our scale, we just needed to identify the problem. A bad outcome would be to think that we need to do anything special with our data. There's no sharding or special database administration in our future, just an eye towards better system monitoring.
Soft skills are as critical as technical skills for a software engineer. No one works in isolation. Each person has to deal with teammates, colleagues, managers, etc. Therefore team interpersonal skills are essential too. Soft skills include things like good communication, honesty, teamwork, integrity, organization, empathy, etc.