When you’re a Postgres performance tuner, tuning starts in one of two ways:
- Someone brings you a query and asks you to make it go faster, or
- Someone brings you a server and asks you to make it go faster
With the first one, the answer is fairly universal: get the explain plan, read it to find the query’s issues, and then tune the query or indexes to make it go faster. There are plenty of guides out there about how to do that, and while they may have different approaches, they’re all kinda using the same data.
But when someone brings you a server, not so much.
The reason is that Postgres is a lean, mean, database serving machine that doesn’t come with a lot of performance diagnostics built in and turned on.
Why doesn’t Postgres come with a lot of bells and whistles in the box?
Postgres is built and maintained by a group of independent volunteers all over the world. They have day jobs at different companies which may use Postgres for different purposes. Here are the current list of Postgres committers – the people who have access to actually push things into Postgres itself.
It’s not a lot of people – as of this writing, it’s just 31 people.
Because they’re all volunteers, and because the code development, testing, documentation, and support are all volunteers too, that means Postgres tends to keep things as lean as possible in the core database itself.
Plus, Postgres releases on a pretty frequent, reliable schedule. New major versions (like 16, 17, 18) come out every year, typically in the third quarter. Minor releases come out much more frequently. Compare that to any commercial database, and it’s practically a breakneck speed! That’s fast development for something that’s done entirely by volunteers (granted, some people make a living working on Postgres code), especially for a critical product like a database server where you really don’t want any bugs.
To keep things moving and keep them reliable, the Postgres committers tend to keep Postgres core itself very minimal.
Bells and whistles start as Postgres extensions.
If you want additional bells and whistles, you’re encouraged to code those as extensions rather than building them into the core product itself. Anyone can build an extension for any purpose. Examples of extensions include:
It’s up to you to choose which extensions you want to use, and that’s also something you have to think about when you’re doing database development. Your app may need some kinds of features, like geospatial data, that simply aren’t built into Postgres by default. That’s totally okay! It’s common in the Postgres world to add extensions to your server.
However, the more extensions you add:
- The more performance overhead you may have
- The more space diagnostic data can take up
- The more you’ll have to learn how to configure the extensions
- The more problems you can hit with interoperability: not all extensions can coexist
- The more research you’ll have to do to make sure that particular extension is even allowed in your hosting environment
- The more updates you’ll have to do to your server as the extension is updated (and you’d better hope that it’s actually updated, because some extensions are abandoned, which would be really bad if your code depended on that extension)
It’s a balancing act. You probably want a lot of features that are provided by extensions, but you also don’t wanna spend a ton of time researching, learning, configuring, and troubleshooting those extensions. There is no one right sweet spot for everybody.
Some companies package their own opinionated and supported version of Postgres. It may include their preferred extensions, plus extensions they wrote themselves. Some of those extensions may be open source, or they may cost extra. Examples include EnterpriseDB and Postgres.app, and a cloud example is the pgactive extension developed by Amazon and offered as part of their RDS Postgres PaaS.
Performance monitoring is done via extensions too.
When you’re doing performance monitoring and tuning, here are just some of the many extension options:
Every extension:
- Gathers different data
- Is affected differently by different hosting platforms (like if Postgres is being hosted in Kubernetes)
- Is configured differently
- Renders that diagnostic data in different ways
Plus, hosting vendors like AWS, Microsoft Azure, and Google all add their own proprietary code as extensions to do performance monitoring as well, surfacing that information in their cloud portals.
So even though you have a pair of seemingly simple questions:
- What’s this server’s biggest bottleneck right now?
- What queries are causing that bottleneck?
The ways that you find the answers are entirely dependent on which extensions you’ve got installed.
Side note: this is one of the reasons why my Postgres fundamentals training classes don’t require or suggest extensions. I don’t want to have to deal with attendees who have a variety of cloud and local hosting environments, each of which can have their own gotchas and instructions when installing extensions. I don’t wanna have one set of instructions for AWS RDS users, another for Azure Database for PostgreSQL Flexible Server users, another for VM users, another for Kubernetes users, etc. Down the road, this is something I’m going to have to tackle when I start building Mastering Postgres performance classes because I just don’t think it’s possible to do a mastering-level job without adding extensions to your server.
That’s why there’s no one server tuning guide.
The process really is different at every company, depending on which extensions they’ve got installed and which Postgres hosting method they’re using (like AWS, Azure, Google, Neon, VMs, Paas, or on-premises.)
In a perfect world, you’re working in a large, established company with a team of other database pros who have already hand-selected the right extensions for them, and have a set process on how they do troubleshooting.
In a less-perfect world, your server maybe has some extensions on it (either that your predecessor added, or that your cloud provider added), and you’ll just need to read the documentation for those extensions in order to get up to speed.
In the worst world, it’s only you, and your server doesn’t have any extensions at all. I wish I could point you to a central extension repository where extensions were grouped together by category, assigned ratings by your peers, and explained which extensions work well together (and which ones have interoperability problems.)
Unfortunately, that just doesn’t exist.
As of this writing, there is no central Postgres extension repository that the community has agreed on. That’s something the developers talked about at PGConf.dev 2024, but I wouldn’t expect a solution to that in the next couple/few years. For now at least, you’re on your own to find a set of extensions that works well for your performance troubleshooting needs.