Postgres (Database Server)

I have become a bit of a Postgres fan and now much prefer it to MySQL.
Here is my Postgres server setup. The server stores data for various apps in my homelab.
For easy administration, I also use a pgAdmin server.

docker-compose.yml

I use a Prometheus exporter to monitor my database schemas. I have slightly customized the Postgres image.
If you do not need any specific Postgres customizations, you can use the ready-made image directly (see the commented-out line).

services:
  postgres:
    container_name: postgres
    #image: postgres:16
    build: ./docker
    #network_mode: host
    ports:
      - 5432:5432
    command:
      - postgres

      # See https://www.youtube.com/watch?v=pvPkLTobK0c

      - -c
      - shared_buffers=4GB

      - -c
      - max_connections=200

      - -c
      - work_mem=10MB

      - -c
      - maintenance_work_mem=500MB

      - -c
      - wal_buffers=32MB

      - -c
      - effective_cache_size=5GB

      - -c
      - checkpoint_timeout=600

      # SSD
      - -c
      - random_page_cost=1.1

      - -c
      - shared_preload_libraries=pg_stat_statements,vector

      - -c
      - ssl=on

      - -c
      - ssl_cert_file=/certs/postgres.muench.lan.crt

      - -c
      - ssl_key_file=/certs/postgres.muench.lan.key
    restart: unless-stopped
    env_file:
      - postgres.env
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./certs:/certs
  
  postgres-exporter:
    container_name: postgres-exporter
    image: prometheuscommunity/postgres-exporter
    restart: unless-stopped
    volumes:
      - ./exporter/queries.yaml:/queries.yaml
    env_file:
      - exporter.env
    ports:
      - "9187:9187"
    networks:
      - default
    depends_on:
      - postgres
    
volumes:
  postgres-data:

postgres.env

Superuser:

POSTGRES_USER=postgres
POSTGRES_PASSWORD=xxxxxxxxxxxxxxxx

docker/Dockerfile

I use the pgvector (vector database) and PostGIS (geospatial data) extensions.
That is why I build my own Docker image with both extensions installed.

ARG POSTGIS_MAJOR=3
ARG PG_MAJOR=16
ARG PG_VECTOR_VERSION=0.8.0

FROM postgres:$PG_MAJOR

# Set ARGs for build
ARG POSTGIS_MAJOR
ARG PG_MAJOR
ARG PG_VECTOR_VERSION

# Install runtime and build dependencies
RUN apt-get update && apt-get install -y \
    postgresql-16-postgis-3 \
    postgresql-16-pgvector && \
    rm -rf /var/lib/apt/lists/*

postgres.conf

I adapted my settings for use in an LXC container. The container runs in a Proxmox cluster. I allocated 10 GB of RAM to it. These settings are tailored to my needs and must be adjusted for your setup.

# Memory settings
shared_buffers = 2GB              # 20% of RAM
work_mem = 16MB                   # Memory for sorting operations
maintenance_work_mem = 512MB      # Memory for maintenance tasks like vacuum

# CPU settings
max_parallel_workers_per_gather = 2 # Number of parallel workers per query
max_parallel_workers = 4            # Max parallel workers for entire instance
max_worker_processes = 8            # Max worker processes (should match cores + parallel workers)

# WAL settings
wal_buffers = 16MB                 # Buffer size for WAL (can be auto-tuned)
checkpoint_completion_target = 0.7 # Checkpoint frequency
effective_io_concurrency = 200     # I/O concurrency for SSDs

# Disk settings
random_page_cost = 1.1             # Set lower for SSDs, higher for spinning disks
effective_cache_size = 1GB         # Approx 70% of RAM, including OS cache

# Autovacuum settings
autovacuum_max_workers = 3         # Number of autovacuum workers
autovacuum_naptime = 1min          # How often autovacuum runs

# Connection settings
max_connections = 100              # Adjust based on expected traffic; lower for LXC