postgresql.conf

# postgres/postgresql.conf
#
# PostgreSQL 18 tuning for Tracker Tracker (Docker, single-user)

# ── Connections ──────────────────────────────────
max_connections = 20

# ── Memory ───────────────────────────────────────
# Tuned for Docker container with 512MB-1GB available
shared_buffers = 128MB
effective_cache_size = 256MB
work_mem = 4MB
maintenance_work_mem = 64MB

# ── PG18: Async I/O ─────────────────────────────
# 'worker' is the safe default for all platforms.
# Linux 5.1+ hosts can switch to 'io_uring' for better throughput.
io_method = worker

# ── I/O Concurrency ─────────────────────────────
# PG18 default is platform-dependent (often 1). Set explicitly for SSD/NVMe.
effective_io_concurrency = 16
maintenance_io_concurrency = 16

# ── WAL ──────────────────────────────────────────
wal_buffers = 4MB
min_wal_size = 80MB
max_wal_size = 1GB

# ── Query Planner (PG18 optimizations) ──────────
# Both are PG18 defaults; set explicitly for documentation.
enable_self_join_elimination = on
enable_distinct_reordering = on

# ── Autovacuum ───────────────────────────────────
# Aggressive thresholds for snapshot tables with heavy write churn + bulk deletes.
# Keeps planner stats fresh and prevents dead tuple bloat after pruning cycles.
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.005

# ── Checkpoints ──────────────────────────────────
checkpoint_completion_target = 0.9

# ── I/O Timing ───────────────────────────────────
# Enables duration tracking in pg_stat_io. Near-zero overhead on modern Linux.
track_io_timing = on

# ── Logging ──────────────────────────────────────
log_min_duration_statement = 1000
# Only fires for NOWAIT lock acquisitions; harmless if unused.
log_lock_failures = on

# ── TOAST Compression ────────────────────────────
# LZ4 is 60-70% faster than default PGLZ for large values.
# Ideal for the cachedTorrents column (1-5 MB JSON per client).
# NOTE: Requires PG compiled with --with-lz4. If PG fails to start,
# comment this line out (falls back to pglz).
default_toast_compression = 'lz4'

# ── Network ──────────────────────────────────────
listen_addresses = '*'