Free PostgreSQL Performance Monitoring with pgNow

I’ve been putting together a new PostgreSQL session called “Performance Monitoring for the Absolute Beginner.” There are several ways to get an understanding of how well your queries are running in PostgreSQL, but, frankly, all of them are a bit of a pain to someone coming from the land of Extended Events (ah, my one true love). Because of this, I saw it as an opportunity to help those just getting going in PostgreSQL. I’ll be presenting it for the first time at Postgres Conference in Orlando on March 19, 2025. Come on by.

Anyhoo, wouldn’t it be nice to maybe have a shortcut, an easier way to look at this information?

Well, there is. Redgate has been working on a completely free tool for leveraging just this sort of data called pgNow. Go here to check it out yourself, but I’ll do a quick run through here.

pgNow

If you follow the link, you can get the executable and install it on your machine. I’m not going to insult you by walking you through that process. When you first open the software, it wants you to connect to a server. Note the link at the top right that outlines the necessary permissions. And no, you don’t have to be a superuser to access this data:

Fill that out as appropriate for the cluster you want to observe and click Connect. You’ll then be presented with this page:

There’s a lot in this little package, but I’m just going to show off one thing today: ‘Workload Analysis.’ This is going to use pg_stat_statements (one of the topics I’ll be covering in my session) to look at your queries and their impact on the system. Now, let’s say we’re connecting to a brand new system. By default, pg_stat_statements is disabled. pgNow is aware of this and will immediately let you know what you have to do (also covered in my session):

OK, that’s a lot of red. Really, all you have to do is follow the steps as outlined. Yeah, you will have to restart PostgreSQL to enable this, but that’s how you get access to pg_stat_statements. Locally, I’m running PostgreSQL in a Docker container. So I need to do this to get in and edit the config file:

## open bash within the container
docker exec -it PerfTuning "bash";
## in bash, edit the config file
vi ~/pgdata/data/postgresql.conf

Once in there, I just use ‘/shared_preload_libraries’ to find the appropriate setting. And, as noted, you can adjust the pg_stat_statements.track setting as well. Add the values you like and save the configuration file. Then, because it’s a container, I can’t just restart the service, I have to stop it, which stops the container, then restart the container. You could just restart the service if you were elsewhere:

## restarting the cluster is required
pg_ctl stop
## start the container
docker start PerfTuning

Oh, be sure you close pgNow before you restart the cluster underneath it. It will not be happy with you. Ask me how I know.

Now, when I go back in, connect to the cluster, and take a look at ‘Workload analysis’, I’ll see the following (your view may vary depending queries in the system, etc.):

I’ve sorted my queries by Execution Time, and you can see a rather nasty little query I wrote that took 3.7 seconds to run. You can click on the plus sign next to the query to get more information:

You can see the full query and more details about it. Yeah, not filled in at the moment. This is a tool under very active development. I’m told day-to-day right now it’s different. But, you can see the utility there already. You can readily sample the queries in a given system. Sort by what interests you, from the number of executions to the amount of time spent writing data. From there, you can see more details. You can even pick and choose the columns on display:

Overall, this is meant to be a way to get a snapshot in time of the behavior of a given cluster. So far, seems to be doing a nice job of it.

Conclusion

I can’t emphasize enough, this is an early peek at the behaviors that are getting built into pgNow. I also want to emphasize that this is, and will stay, a completely free piece of software. Please, check it out for yourself. Let us know what you think.

And for a lot more discussion on how to get measures of query performance out of PostgreSQL, please join me in Orlando. I’m also submitting this talk to other events, so you might be able to find me elsewhere.