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.

Prerequisites

• Docker

Configuration

• run 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).

• 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 and pg_stat_statements.track properties.

• connect to postgres shell via psql

psql -U postgres postgres

Where -U postgres (default user) is username and postgres 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.

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;

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!