Postgres performance within Docker

[ilana7]

Hi Bret, I am not in devops/engineering and I'm desperately in need of some help with something that may be very trivial to you. We have a large-ish Postgres database (1.5Gb dumped) which we are running in a Docker container which was set up for us by a previous 3rd party provider. But the performance of our queries seem to be more than 3 times slower within the container. I have been testing a specific query on my machine, so same technical specs, exact same database, same query, same result when running an EXPLAIN - just much slower in the container. I've Googled and asked on Stackoverflow and applied PGTune recommendations without much success.

I'll attach our docker-compose.yml file and a list of my changes to our postgresql.conf below. Please let me know if there's any other info I can give you.

I would SO appreciate a little bit of your time and any advice you could give.

docker-compose.yml

version: '3.7'

volumes:
  postgres:

services:
  postgres:
    build:
      context: ./
      dockerfile: ./repo/postgres.docker
      shm_size: '2gb'
    shm_size: '2gb'
    environment:
      POSTGRES_USER: "postgres"
      POSTGRES_PASSWORD: "w@gw00rD"
      PGDATA: "/data/postgres"
    volumes:
      - postgres:/data/postgres
      - ./postgresbackups:/var/postgresbackups
      - ./repo/postgres_init.sql:/docker-entrypoint-initdb.d/postgres_init.sql
      - ./repo/postgresql.conf:/etc/postgresql/postgresql.conf
    ports:
      - "15432:5432"
    restart: unless-stopped

postgresql.conf changes

max_connections = 200
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
min_wal_size = 1GB
max_wal_size = 4GB

[BretFisher]

Hmm, not sure I'm much help in psql tuning. I just follow internet guides for conf changes.

On your local machine, the query will be slower because it's running in a VM (Docker Desktop), which is always a bit slower than the host OS, particularly on macOS.

You can't really compare performance of running in Docker Desktop vs a server. What you can do, is compare one query to another on the same host after you've optimized db indexes and .conf settings.

On the server side, there wouldn't be any change in performance inside a container vs. outside a container because a Linux container is just running on the host OS Kernel with the same CPU/memory, and File I/O specs. DB query speed is often about these concerns:

1. Fastest SSD's you can buy and store the db file on.
2. Optimize postgres indexes for your query.
3. Increase cache and memory settings.

Notice, on the server, none of those are related to it being a container. If you were running a single postgres db on a Linux VM host OS, and then you moved postgres on that server into a single container, I wouldn't expect performance to change.

[ilana7]

Thanks so much for getting back to me Brett - what you're saying makes a lot of sense!! I hadn't considered Mac OS to be a separate factor influencing query speed within a Docker container.

I'm going to follow your advice and compare performance for the two scenarios on the server itself.

Thanks again, you're a rock star!!!!!!!!!