# =============================================================================
# pg_trickle Docker image — default PostgreSQL & pg_trickle configuration
#
# These settings are appended to postgresql.conf.sample at image build time,
# so they apply to every cluster created from this image.
#
# Designed to:
#   • Run the Getting Started guide out of the box (no extra config needed)
#   • Scale gracefully to much larger databases (raise the memory values)
#
# Override any setting at container startup without rebuilding:
#
#   docker run -e POSTGRES_PASSWORD=pw \
#     pgtrickle/pg_trickle:latest \
#     -c shared_buffers=2GB -c work_mem=64MB
#
# Or supply a custom postgresql.conf volume:
#
#   docker run -e POSTGRES_PASSWORD=pw \
#     -v /path/to/my/postgresql.conf:/etc/postgresql/postgresql.conf \
#     pgtrickle/pg_trickle:latest \
#     -c config_file=/etc/postgresql/postgresql.conf
# =============================================================================

# ── Required for pg_trickle ──────────────────────────────────────────────────
shared_preload_libraries = 'pg_trickle'

# WAL-based CDC (recommended): enables pg_trickle to auto-transition from
# lightweight row triggers to near-zero-overhead WAL capture.
# Set pg_trickle.cdc_mode = 'trigger' to stay on triggers permanently.
wal_level = logical

# One replication slot per WAL-CDC stream table plus headroom for replicas.
# Raise if you have many stream tables using CDC or physical/logical replicas.
max_replication_slots = 10

# Worker budget: 1 launcher + 1 scheduler per database + parallel refresh workers
# + autovacuum + parallel query. For a single-database deployment with 4 parallel
# refresh workers: 1 + 1 + 4 + 3 + 2 = 11 minimum. 32 is a comfortable default.
# Raise to 64+ for multi-database clusters or heavy parallel refresh workloads.
max_worker_processes = 32

# ── Memory ───────────────────────────────────────────────────────────────────
# shared_buffers: PostgreSQL's main buffer cache.
# Rule of thumb: 25% of available RAM.
# 256 MB is correct for the Getting Started guide; raise for production.
# Examples: 2GB for an 8-GB host, 8GB for a 32-GB host.
shared_buffers = 256MB

# work_mem: per-sort / per-hash-join memory budget.
# pg_trickle overrides this locally for large deltas via merge_work_mem_mb.
# Raise to 32–64MB for complex queries on large tables.
work_mem = 16MB

maintenance_work_mem = 64MB

# Planner hint: estimated total memory available for caching pages.
# Set to ~75% of available RAM for best query plans.
# Does not allocate memory — only affects the planner's cost model.
effective_cache_size = 1GB

# ── WAL & checkpoints ────────────────────────────────────────────────────────
wal_buffers = 16MB

# Spread checkpoint I/O evenly over 90% of the checkpoint interval to
# reduce I/O spikes.
checkpoint_completion_target = 0.9

# Maximum WAL accumulation between checkpoints. Raise to 2–4GB for
# write-heavy workloads to reduce checkpoint frequency.
max_wal_size = 1GB
min_wal_size = 128MB

# ── Connections & planner ────────────────────────────────────────────────────
max_connections = 100

# random_page_cost tuned for SSD / container storage.
# Lower values favor index scans over sequential scans.
# Use 4.0 for spinning-disk storage.
random_page_cost = 1.1

# ── pg_trickle — core ────────────────────────────────────────────────────────
pg_trickle.enabled = true

# 'auto' uses triggers initially then transparently transitions to WAL-based
# CDC once the WAL decoder catches up. This is the recommended setting when
# wal_level = logical.  Use 'trigger' to stay on triggers permanently.
pg_trickle.cdc_mode = 'auto'

# ── pg_trickle — scheduling ──────────────────────────────────────────────────
# Wake the scheduler on NOTIFY from CDC triggers for sub-50ms median latency.
# Falls back to scheduler_interval_ms polling when no NOTIFYs arrive.
pg_trickle.event_driven_wake = true

# Coalesce rapid-fire NOTIFYs from burst writes into a single wake.
# 20ms balances latency against unnecessary wake-ups. Raise to 50–100ms
# for high-throughput batch workloads.
pg_trickle.wake_debounce_ms = 20

# Poll-based fallback interval (rarely fires when event_driven_wake = true).
pg_trickle.scheduler_interval_ms = 500

# Minimum and default refresh intervals. 1s is appropriate for real-time
# dashboards and OLTP. Raise to 5–30s for heavy analytical workloads.
pg_trickle.min_schedule_seconds = 1
pg_trickle.default_schedule_seconds = 1

# Mark a stream table ERROR after this many consecutive refresh failures.
pg_trickle.max_consecutive_errors = 3

# Automatically lengthen the refresh schedule (up to 8×) when a stream table
# consistently takes longer than its budget. Resets on the first on-time
# completion. Leave enabled unless you are deliberately running at the limit.
pg_trickle.auto_backoff = true

# ── pg_trickle — refresh performance ────────────────────────────────────────
# Inject SET LOCAL planner hints (disable nestloop, raise work_mem) for
# medium-to-large deltas to reduce P95 latency spikes.
pg_trickle.merge_planner_hints = true

# work_mem allocated inside the MERGE transaction when delta > 10,000 rows.
# 64MB handles workloads up to ~500K-row deltas without disk spills.
# Raise to 128–256MB for large analytical workloads.
pg_trickle.merge_work_mem_mb = 64

# Use TRUNCATE (O(1)) instead of per-row DELETE to clear consumed change
# buffers. Slightly faster but acquires an AccessExclusiveLock briefly.
pg_trickle.cleanup_use_truncate = true

# Cache MERGE plans after ~5 executions to save 1–2ms parse/plan overhead
# per refresh cycle.
pg_trickle.use_prepared_statements = true

# Fall back from DIFFERENTIAL to FULL refresh when the pending change buffer
# exceeds 15% of the source table's row count. FULL refresh is cheaper at
# very high change rates because it avoids per-row JSONB parsing overhead.
pg_trickle.differential_max_change_ratio = 0.15

# ── pg_trickle — parallel refresh ───────────────────────────────────────────
# Dispatch independent stream tables to parallel background workers.
# Requires sufficient max_worker_processes headroom (see above).
pg_trickle.parallel_refresh_mode = 'on'

# Cluster-wide cap on concurrently active refresh workers.
# Raise in proportion to max_worker_processes when you have many stream tables.
pg_trickle.max_dynamic_refresh_workers = 4

# Per-database dispatch cap. In a single-database deployment this effectively
# equals max_dynamic_refresh_workers.
pg_trickle.max_concurrent_refreshes = 4

# ── pg_trickle — guardrails ──────────────────────────────────────────────────
# Compact change buffers (eliminate net-zero INSERT+DELETE pairs) when they
# exceed this many rows. Reduces delta scan cost by 50–90% for high-churn tables.
pg_trickle.compact_threshold = 100000

# Emit a BufferGrowthWarning alert when any source table's change buffer
# exceeds this row count. Good signal for "stream table falling behind".
pg_trickle.buffer_alert_threshold = 1000000

# WAL slot lag thresholds. Raise slot_lag_warning_threshold_mb to 256–512MB
# if your workload intentionally retains more WAL (e.g., replica lag tolerance).
pg_trickle.slot_lag_warning_threshold_mb = 100
pg_trickle.slot_lag_critical_threshold_mb = 1024
