postgresql.conf

# G-NAF Address Autocomplete — PostgreSQL Tuning
# Optimized for: read-heavy (>99.9% reads), large MV + covering btree + GIN indexes,
# SSD storage, 14GB Docker container, Bun/Elysia client (10-pool).
#
# Default Postgres config is tuned for mixed write/read workloads on HDDs.
# This workload is different: after the initial PSV load, the service is
# effectively read-only with 10-50 concurrent users. The materialized view
# + 6 indexes total ~12GB. Queries are simple btree/GIN index scans with
# ORDER BY + LIMIT — JIT overhead is pure waste.

# ============================================================================
# Memory (target: 14GB container)
# ============================================================================
shared_buffers = '2GB'                # 2GB — caches the hot tier 0/0b/0c/1
                                       # btree index pages. COPY FROM STDIN
                                       # keeps loader peak memory at ~900MB,
                                       # so 2GB leaves 13GB for OS file cache
                                       # + other containers.
                                       #
                                       # Benchmark (1000 queries, 31 patterns,
                                       # with warmup): p50 ~57ms, p95 ~76ms.
                                       # The gap to the <50ms target is the
                                       # cold-cache penalty: the first ~20-30
                                       # benchmark queries hit index pages not
                                       # yet loaded. Increasing shared_buffers
                                       # past 3GB showed diminishing returns
                                       # (2→3GB saved ~10ms, 3→4GB was noise).

work_mem = '8MB'                      # Per-operation sort memory. Loader does no sorts
                                       # or hashes, so 8MB is plenty. Reducing from 32MB
                                       # cuts peak memory by ~24MB per concurrent query,
                                       # which adds up across parallel workers.

maintenance_work_mem = '4GB'          # For REFRESH MATERIALIZED VIEW + CREATE INDEX.
                                       # The GIN trigram index build benefits most from
                                       # more memory (default 64MB would take hours).
                                       # 4GB cuts index build time nearly in half vs
                                       # 2GB. Only allocated during index builds /
                                       # VACUUM, not during normal queries.

effective_cache_size = '12GB'         # 75% of 16GB. Informs the planner about
                                       # OS-level file cache. Higher = planner prefers
                                       # index scans over seq scans. Correct for this
                                       # workload.

autovacuum_work_mem = '256MB'         # Autovacuum must process the MV + indexes.
                                       # Reduced from 1GB to 256MB. During the
                                       # initial load, autovacuum on staging tables
                                       # would compete for memory; we disable
                                       # autovacuum for the loader's tables anyway.
                                       # 256MB is enough for post-load MV analyze.

wal_buffers = '8MB'                   # Reduced from 16MB. With 1 worker and
                                       # transaction-wrapped inserts, WAL write
                                       # frequency is low; 8MB is sufficient.

# ============================================================================
# I/O (NVMe SSD)
# ============================================================================
random_page_cost = 1.0                # NVMe SSD random reads ≈ sequential.
                                      # Default 4.0 is for HDDs and would make the
                                      # planner avoid Index-Only Scans.
                                      # 1.0 tells the planner: "index scan is cheap."

effective_io_concurrency = 200        # NVMe SSDs handle 200+ concurrent I/Os.
                                      # Default 1.0 is for HDDs.
                                      # Higher = planner more likely to use bitmap
                                      # scans for GIN index lookups.

maintenance_io_concurrency = 200      # Parallel I/O for REFRESH MV + VACUUM.
                                      # Speeds up index builds and post-load cleanup.

# ============================================================================
# WAL (write-ahead log — important during initial load)
# ============================================================================
wal_level = replica                    # Required for REFRESH MATERIALIZED VIEW
                                       # CONCURRENTLY. Cannot use 'minimal'.

max_wal_size = '8GB'                   # Default 1GB is too small for a 16.9M-row
                                       # load with GIN indexes. Frequent checkpoints
                                       # with 1GB would halve the loader's throughput.
                                       # 8GB allows checkpoints every ~40 min during
                                       # initial load — effectively 1-2 checkpoints
                                       # total for the entire REFRESH MV.

min_wal_size = '1GB'                   # Minimum WAL to retain. Helps with crash
                                       # recovery speed at the cost of disk space.

checkpoint_timeout = '1h'             # Default 5min causes excessive checkpoints
                                       # during the 10-min parallel load.
                                       # 1h means at most 1-2 checkpoints during
                                       # the entire load + MV REFRESH (which takes
                                       # ~12 min). Each checkpoint pauses writes
                                       # for ~10s, so fewer = faster load.

checkpoint_completion_target = 0.9     # Spread checkpoint writes over 90% of the
                                       # checkpoint interval. Reduces I/O spikes.

# ============================================================================
# Query planner (tuned for simple index lookups, not analytical queries)
# ============================================================================
jit = off                              # CRITICAL: JIT compiles query plans to
                                       # machine code. For simple index lookups with
                                       # LIMIT (our Tier 0/1 queries), JIT adds
                                       # 3-5ms of compilation overhead per query —
                                       # more than the actual query execution time.
                                       # Only helps for complex analytical queries
                                       # scanning millions of rows.

default_statistics_target = 500        # Higher = better estimates for GIN trigram
                                       # similarity threshold. The planner needs
                                       # accurate row estimates to pick bitmap vs
                                       # index-only scans. Default is 100.

max_parallel_workers_per_gather = 4    # Allow parallel bitmap heap scans on the
                                       # GIN trigram index (Tier 2 queries) AND
                                       # parallel JOIN evaluation during MV REFRESH.
                                       # With 4 workers, Tier 2 queries are faster
                                       # AND the MV definition's 4-table JOIN runs
                                       # in parallel during REFRESH.

max_parallel_workers = 8               # System-wide parallel worker limit.
                                       # 8 supports up to 4 maintenance workers
                                       # (parallel index builds) + 2 per-gather
                                       # workers for query parallelism.

max_parallel_maintenance_workers = 4   # Parallel workers for CREATE INDEX /
                                       # REFRESH MV index maintenance. Default 2
                                       # builds indexes serially. With 4 workers,
                                       # a 2GB btree index build drops from ~45s
                                       # to ~15s. Critical for the 9 MV indexes.

parallel_tuple_cost = 0.01             # Lower than default (0.1) because our
                                       # parallel workers share the same buffer pool.
                                       # Encourages parallel index scans.

parallel_setup_cost = 100              # Default 1000. Lower because our queries
                                       # are simple (1 table, index scan, LIMIT).
                                       # Encourages parallelism for larger queries.

# ============================================================================
# Memory pages
# ============================================================================
huge_pages = off                       # Don't try to use 2MB huge pages. macOS
                                       # doesn't support them well and Postgres
                                       # falls back to 4KB pages anyway, but the
                                       # failed attempt wastes a few MB of overhead.
                                       # On Linux production, switch to 'try'.

temp_buffers = '8MB'                   # Default 8MB. Explicit to prevent
                                       # accidental bumping. Temp tables aren't
                                       # used by the loader.

# ============================================================================
# Connections (limited — this is a single-service deployment)
# ============================================================================
listen_addresses = '*'                 # CRITICAL: Docker port mapping requires
                                      # Postgres to listen on all interfaces
                                      # inside the container, not just localhost.
                                      # Default is 'localhost' which would reject
                                      # all external connections silently.

max_connections = 50                   # Loader needs 9 workers + api pool (20) +
                                        # orchestrator (1) = ~30 connections. 50 gives
                                        # headroom during deploy (old API still running).
                                        # Each idle connection costs ~2MB; 50 = 100MB.

# ============================================================================
# Background writer (keep buffers clean for read-heavy workload)
# ============================================================================
bgwriter_delay = '100ms'               # Default 200ms. More frequent scans mean
                                       # cleaner buffers and faster index scans.

bgwriter_lru_maxpages = 200            # Write up to 200 dirty buffers per scan.
                                       # Helps keep shared_buffers clean during
                                       # the load phase.

bgwriter_lru_multiplier = 4.0          # Aggressively clean buffers ahead of demand.

# ============================================================================
# GIN indexes (critical for trigram + tsvector performance)
# ============================================================================
gin_pending_list_limit = '64MB'        # GIN pending list threshold. During the
                                       # 12-min MV REFRESH, GIN trigram entries
                                       # go into a pending list first, then flush
                                       # to the main index when the limit is hit.
                                       # 8MB caused ~50 flushes for 16M rows.
                                       # 64MB cuts flushes to ~7, saving ~5min of
                                       # REFRESH time. Queries that hit the pending
                                       # list are slightly slower (linear scan of
                                       # pending entries) but the trade-off is worth
                                       # it for the 5x faster REFRESH.

# ============================================================================
# Logging and monitoring
# ============================================================================
log_min_duration_statement = 50       # Log any query taking >50ms (our p95 target).
                                       # Set to -1 in production to suppress, but
                                       # useful during development.

log_checkpoints = on                   # Monitor checkpoint frequency during load.
                                       # Frequent checkpoints = need larger max_wal_size.

log_autovacuum_min_duration = 0        # Log all autovacuum operations on the MV.
                                       # Important: the MV's statistics change after
                                       # REFRESH, triggering autovacuum analyze.

log_line_prefix = '%t [%p] %u@%d '    # Timestamp + PID + user + database.

track_io_timing = on                   # EXPLAIN (ANALYZE, BUFFERS) shows real I/O
                                       # timing. Essential for debugging slow queries.

# ============================================================================
# LC_COLLATE / C locale (speed up text_pattern_ops indexes)
# ============================================================================
# IMPORTANT: The database should be created with 'C' or 'POSIX' locale to
# maximize performance of text_pattern_ops indexes used in Tier 0b and Tier 1.
# If created with en_US.UTF-8, the text_pattern_ops indexes will still work but
# may be ~10-20% slower for prefix matching.
#
# To use: CREATE DATABASE gnaf WITH LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;