DEV Community

loading...

Postgres pg_stat_statement setup via docker

semuserable
Originally published at semuserable.com ・2 min read

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 in docker
docker run --name test-postgres -p 5432:5432 -e POSTGRES_PASSWORD=secretpass -d postgres
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • then psql
psql -U postgres postgres
Enter fullscreen mode Exit fullscreen mode
  • run some SQL several times.
SELECT * FROM pg_stat_statements;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Query pg_stat_statements.

SELECT * FROM pg_stat_statements;
Enter fullscreen mode Exit fullscreen mode
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!

Discussion (0)