pg_stat_statements is useful to gather performance information about queries so lets add it to our dockerized postgres database.

Using docker-compose

Using docker-compose we just need to add the following docker-compose.yaml:

docker-compose.yml
version: '3.5'

services:
  postgres:
    container_name: postgres_container
    image: postgres
    ports:
      - "5432:5432"
    volumes:
      - /var/lib/postgresql/data
    # used for query profiling, deactivate for enhanced performance
    command: postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all -c max_connections=200
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: thepassword

We simply start our Postgres database with docker-compose:

docker-compose up

Now we need to run the following statement as superuser to activate the performance logging. Afterwards we’re able to query the view pg_stat_statements for information.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Testing if we are successful:

SELECT calls, query FROM pg_stat_statements LIMIT 1;

Using Docker

docker run --rm -p5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres --name postgres_container -c "shared_preload_libraries='pg_stat_statements'"

Querying the View

Now we may gather some insights about our SQL statement’s performance e.g. like this:

Sample pg_stat_statements query
SELECT query, calls, blk_read_time,rows,mean_exec_time, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

Resetting Data

The stored analytics information may be cleared by running the following SQL statement:

Reset data
SELECT pg_stat_statements_reset();

Resources