Introduction

As part of the community, the most significant aspect of maintaining or using open-source (OSS) tools or services is: How can tools or services meet the needs of industries or individuals? At some point, some OSS maintainers choose to make rolling changes as much and as soon as possible.

Being fast means they break and fix some features more often. But what about the users, the adopters of their tools? That’s why some services have a timeline for launching a new version of their tools or when to stop supporting the old version.

Once the new version is rolling out, users should pay attention to what is changing, what is no longer supported, or what new cool features they ship with it.

And here is the problem. Users do not always have the time or capability to upgrade their service right after the tools are updated or a new version is released. That’s where my stories with upgrading PostgreSQL begin.

Decisions Before Upgrading PostgreSQL

My team and I work with many databases dominated by PostgreSQL. At first, everything works perfectly: fast, clean, and no bottleneck. But everything changed after the tech winter hit Indonesia’s tech startup industries. ¹ Here, cost efficiency for cloud infrastructure became our first priority in every OKR each quarter. As a DevOps engineer, I look at it as a blessing in disguise because at this point, we realized we had been burning a significant amount of money, including our database instances.

Long story short, we cut out a big amount of infrastructure costs by downgrading many instances and resizing our Kubernetes node pool to the bottom line of our services’ needs. It felt refreshing and fun, but here come new issues. It seems like our software engineers have become comfortable throwing money to fix performance issues instead of addressing them. Personally, I’m not blaming them because they have many targets to meet, and that makes sense.

So, we began to experience short downtime every day after downgrading our database and realized that our database instances became the bottleneck for our whole services. Here we go, a journey to analyze it all finally comes. Here is a shortlist of what we did to analyze our databases:

1. Ensure our monitoring is performing well

We already have included monitoring services for instances from the provider, but again, we are cutting infrastructure costs right now. So, we installed Prometheus inside our services and monitored it more often, adding some alerting to our Slack. And that helped us a lot.

2. Keep our eye on high-cost queries

Besides installing monitoring services, we also set up our slow log and pg_activity tools to identify slow queries or queries with issues. Most of the problematic queries come from old developed systems that are not updated and have become tech debt. They consist of not indexed columns, strange stored procedures, and queries with bad filtering or conditional parameters. And we make sure always use explain analyze statement for our query before ship it to production

3. Optimize our PostgreSQL config

And we got stuck. So, we used the pgtuner cool tools to fix our configuration regarding our version, specs, and our operations. In fact, it helped a lot, but sadly, it’s not the end.

Turns Out We Need One More Solution for Our Issues: Upgrade PostgreSQL

Since our PostgreSQL version is left behind and almost not supported, it turns out there is a performance degradation between our databases, our services, and the library used by our software engineers, becoming a bottleneck. To be honest, it has become a significant issue for some services and is very annoying.

So we start to research about it and find a better way to upgrade our database version with minimum downtime and keep an eye to our downtime budget carefully and voila, we upgrade it from version 10 to 15 with few hundred gigabytes of storage thanks to pg_upgrade built in tools. But I got you one better, it’s only getting started and we need upgrade several db with more volume of data. When this article writed i still working on our downtime and upgrade plan lol.

Key take aways for Should you upgrade your postgres

• it’s better to read changelog and make sure you really need to do that
• There is so many soltion other than upgrade database version
• Do your own research on your Database behaviour
• Don’t upgrade your database if you don’t need it

And that’s all for now. Next i will come back with technical guide for upgrading postgresql. See u next time