It's weird to do performance analysis of a database you run on your laptop. When testing some app, your local instance probably has 1/1000 the amount of realistic data compared to a production server. Or, you're running a bunch of end-to-end integration tests whose PostgreSQL performance doesn't make sense to measure.
Anyway, if you are doing some performance testing of an app that uses PostgreSQL one great tool to use is pghero. I use it for my side-projects and it gives me such nice insights into slow queries that I'm willing to live with the cost that it is to run it on a production database.
This is more of a brain dump of how I run it locally:
First, you need to edit your
postgresql.conf. Even if you used Homebrew to install it, it's not clear where the right config file is. Start
psql (on any database) and type this to find out which file is the one:
$ psql kintobench kintobench=# show config_file; config_file ----------------------------------------- /usr/local/var/postgres/postgresql.conf (1 row)
/usr/local/var/postgres/postgresql.conf and add the following lines:
# Peterbe: From Pghero's configuration help. shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all
Now, to restart the server use:
▶ brew services restart postgresql Stopping `postgresql`... (might take a while) ==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql) ==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
The next thing you need is
pghero itself and it's easy to run in docker. So to start, you need Docker for mac installed. You also need to know the database URL. Here's how I ran it:
docker run -ti -e DATABASE_URL=postgres://peterbe:@host.docker.internal:5432/kintobench -p 8080:8080 ankane/pghero
Note the trick of
peterbe:@host.docker.internal because I don't use a password but inside the Docker container it doesn't know my terminal username. And the
host.docker.internal is so the Docker container can reach the PostgreSQL installed on the host.
Once that starts up you can go to
http://localhost:8080 in a browser and see a listing of all the cumulatively slowest queries. There are other cool features in
pghero too that you can immediately benefit from such as hints about unused/redundent database indices.
Hope it helps!