PostgreSQL performance

Performance

Restrict resources

From OS

Use docker quota

• RAM
• CPU
• I/O is not available (unfortunately, as database performance rely on I/O)

Total RAM

services
  database:
    image: bitnami/postgresql
    deploy:
       resources:
          limits:
             cpus: '1'
             memory: 512m    

Shared memory (/dev/shm, used for IPC)

What does actually do ? https://docs.docker.com/compose/compose-file/compose-file-v3/#shm_size

services
  database:
    image: bitnami/postgresql
    shm_size: 256m    

Check using inspect

docker inspect databse | grep -i shm

Memory usage

Check cache https://stackoverflow.com/questions/1216660/see-and-clear-postgres-caches-buffers

Clear OS cache

echo 3 | sudo tee /proc/sys/vm/drop_caches

Reduce cache

The next best thing seems to be: Actual max RAM = shared_buffers + (temp_buffers + work_mem) * max_connections

https://stackoverflow.com/questions/28844170/how-to-limit-the-memory-that-is-available-for-postgresql-server

Restrict connexion max_connections (minimum 2, 1 for Postgresql maintenance and 1 for actual queries).

Restrict memory usage for

• cache, using shared_buffers
• sorting, using work_mem
• temporary tables, using temp_buffers
• autovacuum, using maintenance_work_mem

You can do this in postgresql.conf

shared_buffers = 2GB

Which you expose this way

services
  db-ptm-integration:
    image: bitnami/postgresql
    volumes:
      - ./postgresql-configuration:/bitnami/postgresql/conf

Or using parameter (works with alpine, to test here...)

services
  db-ptm-integration:
    image: bitnami/postgresql
    command: postgres
                -c shared_buffers=256m

better: configuration file extra https://stackoverflow.com/questions/76149706/bitnami-postgresql-replaces-postgresql-conf-after-restarting-docker