35
Postgres pg_stat_statement setup via docker
Sometimes we want to track and analyze
SQL
in postgres
. It may be that you use some fancy ORM
and all SQL
is abstracted away, but then you begin to notice some performance drops and you're starting to suspect that the SQL
you expected to be run "very efficiently", in fact performs poorly.That's how I've got acquainted with pg_stat_statements tool. I suggest to read about it on the official site.
Here, we'll setup it for local development via
docker
.postgres
in docker
docker run --name test-postgres -p 5432:5432 -e POSTGRES_PASSWORD=secretpass -d postgres
Make sure the port 5432
is not occupied by any process, otherwise the postgres
won't be started (although container will be created).
docker exec -it test-postgres /bin/bash
postgresql.conf
In order to enable
pg_stat_statements
functionality we need to edit some settings in postgres
config file. Execute the following commands one by one (or via &&
).echo "shared_preload_libraries = 'pg_stat_statements'" >> $PGDATA/postgresql.conf
echo "pg_stat_statements.max = 10000" >> $PGDATA/postgresql.conf
echo "pg_stat_statements.track = all" >> $PGDATA/postgresql.conf
Check if config is updated by running cat $PGDATA/postgresql.conf
. New lines will be at the end.
Check F.29.3. Configuration Parameters for pg_stat_statements.max
and pg_stat_statements.track
properties.
postgres
shell via psql
psql -U postgres postgres
SQL
CREATE EXTENSION pg_stat_statements;
We've finished with the configuration and ready to start using the tool. We need to restart a container for changes to take an effect.
Run
exit
to leave postgres
shell, then exit
to leave container shell. Then start postgres
again - docker start test-progres
.Connect to the database. We'll be using
psql
from within a docker
container.docker exec -it test-postgres /bin/bash
psql
psql -U postgres postgres
SQL
several times.
SELECT * FROM pg_stat_statements;
You will see something like this.
userid | dbid | query | calls | total_time |
---|---|---|---|---|
10 | 13408 | SELECT * FROM pg_stat_statements | 1 | 0.1371 |
This is an excerpt, another columns don't really matter right now.
The most interesting columns are
query
, calls
, total_time
(in milliseconds).Let's create
moviesdb
database first and then call the previous SQL
again.CREATE DATABASE moviesdb;
Query
pg_stat_statements
.SELECT * FROM pg_stat_statements;
userid | dbid | query | calls | total_time |
---|---|---|---|---|
10 | 13408 | SELECT * FROM pg_stat_statements | 2 | 0.412 |
10 | 13408 | CREATE DATABASE moviesdb | 1 | 334.0824 |
We just touched a tip of an iceberg. There are numerous ways to query the table and get various insights. Try it out for yourself!
35