Postgres pg_stat_statement setup via docker
Tags: postgres, sql
Published: 2021-06-29 17:44:03

---
title: "Postgres pg_stat_statement setup via docker"
published: true
description: "pg_stat_statement setup guide"
tags: ["postgres", "sql"]
canonical_url: https://semuserable.com/posts/postgres_pg_stat_statement/
---

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](https://www.postgresql.org/docs/9.4/pgstatstatements.html) tool. I suggest to read about it on the official site.

Here, we'll setup it for local development via `docker`.

## Prerequisites

* [Docker](https://www.docker.com/)

## Configuration

* run `postgres` in `docker`

```sh
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

```sh
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 `&&`).

```bash
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](https://www.postgresql.org/docs/9.4/pgstatstatements.html#AEN130206) for `pg_stat_statements.max` and `pg_stat_statements.track` properties.

* connect to `postgres` shell via `psql`

```bash
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](https://dbeaver.io/) (free), [DataGrip](https://www.jetbrains.com/datagrip/) (paid), etc.

* create an extension via `SQL`

```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
```sh
docker exec -it test-postgres /bin/bash
```

* then `psql`

```sh
psql -U postgres postgres
```

* run some `SQL` several times.

```sql
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. 

```sql
CREATE DATABASE moviesdb;
```

Query `pg_stat_statements`.

```sql
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!