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
.
Prerequisites
Configuration
- run
postgres
indocker
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 thepostgres
won't be started (although container will be created).
- go into container command prompt via interactive mode
docker exec -it test-postgres /bin/bash
- edit
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
andpg_stat_statements.track
properties.
- connect to
postgres
shell viapsql
psql -U postgres postgres
Where
-U postgres
(default user) is username andpostgres
is a database name.Connection can be also established via any database tool like dbeaver (free), DataGrip (paid), etc.
- create an extension via
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
.
Playground
Connect to the database. We'll be using psql
from within a docker
container.
- go into container bash again
docker exec -it test-postgres /bin/bash
- then
psql
psql -U postgres postgres
- run some
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!
Top comments (0)