Postgres Tuning

Postgres Tuning

bitmagnet runs a write-heavy workload (continuous DHT crawling + classification queue). The included compose sets Postgres performance parameters via the command: block — no custom image needed.

---

Sizing by hardware

Hardware	shared_buffers	work_mem	maintenance_work_mem	max_wal_size
Desktop / NUC (16GB+)	1024MB	32MB	512MB	3GB
Desktop / NUC (8GB)	512MB	8MB	64MB	1GB
Raspberry Pi (4GB)	256MB	16MB	128MB	1GB

---

Compose example (8GB host)

postgres:
  image: postgres:16-alpine
  command:
    - postgres
    - -c
    - shared_buffers=512MB
    - -c
    - effective_cache_size=1536MB
    - -c
    - work_mem=8MB
    - -c
    - maintenance_work_mem=64MB
    - -c
    - max_connections=50
    - -c
    - wal_buffers=16MB
    - -c
    - checkpoint_completion_target=0.9
    - -c
    - max_wal_size=1GB
    - -c
    - random_page_cost=1.1
    - -c
    - autovacuum_vacuum_scale_factor=0.05
    - -c
    - autovacuum_analyze_scale_factor=0.025
    - -c
    - autovacuum_vacuum_cost_delay=2ms
    - -c
    - autovacuum_naptime=20s
    - -c
    - autovacuum_max_workers=2

---

Autovacuum tuning

The aggressive autovacuum settings above are important for bitmagnet's workload. The queue_jobs table sees constant inserts and deletes as the classifier processes torrents. Without tuned autovacuum, dead rows accumulate and degrade query performance.

---

Healthcheck

Always use service_healthy in depends_on, not service_started. Without a healthcheck, Docker considers Postgres "started" the moment the container process launches — before it's accepting connections.

healthcheck:
  test: ["CMD-SHELL", "pg_isready"]
  interval: 10s
  timeout: 5s
  retries: 5
  start_period: 20s

---

SSD recommended

The random_page_cost=1.1 setting assumes SSD storage. If running on spinning disk, change to random_page_cost=4.0 (the Postgres default).