Skip to main content

Tuning and Observability

By the end of this page you will know the order in which to tune ScramDB, and for each lever, how to tell whether the change actually did anything, rather than guessing.

ScramDB ships with sane defaults: auto-sized memory, auto-detected worker count, JIT on, statistics collected automatically. Most deployments never need to touch a single config key. This section exists for the workloads that do: memory-bound analytics on constrained hardware, latency-sensitive OLTP, or large batch jobs where GPU offload pays off.

Tune in this order. Each one moves the needle less than the one before it.

1. Memory sizing​

The buffer pool (how much of the working set stays resident in RAM) is the single biggest lever ScramDB exposes. Undersize it and every query that should be a memory hit turns into a disk read; oversize it past available RAM and the process risks memory pressure. See Memory for the full field list.

How to know it worked: there is no buffer-pool hit-ratio metric on /metrics today, so verification is by observation, not by a dedicated counter. Run the same query twice back to back:

psql -h 127.0.0.1 -p 5432 -c '\timing on' -c "SELECT count(*) FROM orders WHERE status = 'shipped';"
psql -h 127.0.0.1 -p 5432 -c '\timing on' -c "SELECT count(*) FROM orders WHERE status = 'shipped';"

If the second run is noticeably faster, the working set stayed resident. You can also watch process RSS (ps, docker stats) against your configured buffer_pool_size_bytes to confirm the pool is actually being used.

2. Parallelism​

[execution] workers defaults to "auto", meaning every core ScramDB detects. Tuning it down leaves CPU headroom for co-located processes; tuning it up past the physical (or cgroup-limited) core count has no effect, the resolver hard-caps it. See Parallelism.

How to know it worked: compare wall-clock latency of a CPU-bound query (a large hash join or aggregate) at different workers values with \timing, or watch top/htop while the query runs to confirm the configured count is actually saturated.

3. JIT​

[general] jit_enabled is true by default. A query starts on the bytecode interpreter and transitions to compiled machine code once the JIT finishes compiling in the background, never blocking on it. The only knob worth touching is the compiled-code cache size ([jit] compiled_cache_memory / compiled_cache_disk) on a workload with enough distinct query shapes to thrash the 512MB/1GB defaults. See Query Performance.

How to know it worked: scrape /metrics, note scramdb_chunks_jit_total and scramdb_chunks_vm_total, run the query several times past its first (compiling) run, scrape again. A growing scramdb_chunks_jit_total with scramdb_chunks_vm_total no longer climbing means JIT engaged.

4. Statistics freshness​

Auto-analyze runs on a 120-second check interval and a 10% row-count-drift trigger by default, and also fires immediately after a bulk load (COPY, large INSERT). If a plan looks wrong right after a large load that hasn't tripped the auto trigger yet, run ANALYZE TABLE <name> directly. See Query Performance.

How to know it worked: compare EXPLAIN SELECT ... output (estimated row counts) before and after ANALYZE TABLE.

5. GPU​

GPU offload is opt-in in effect: it requires supported hardware to be present, falls back to CPU with no config error otherwise, and only pays off on batches at or above the 50,000-row size floor. Forcing GPU dispatch on a small dataset can measurably lose to CPU, this is not a default-on win for every workload. See GPU Acceleration.

How to know it worked: the startup log line names the detected device; per-query dispatch decisions are visible in the debug log. There is no /metrics counter for GPU today, so log lines are the verification path.

6. Storage and segment knobs​

segment_max_rows, flush_threshold_rows, prefetch_depth, and prefetch_queue_depth are already tuned for the common case. They are the lowest-payoff lever on this list, worth touching only for a specific, observed bottleneck, for example raising prefetch_depth on a sequential-scan-heavy, I/O-bound workload. See Memory and Query Performance.

The general verification recipe​

Every section above reduces to the same two tools:

  • psql's \timing on for wall-clock latency, the ground truth for "did this help."
  • The /metrics scrape-delta pattern: take a counter reading, do work, take another reading, subtract. This proves JIT/VM engagement precisely. It does not exist for buffer-pool hit ratio, per-worker utilization, or GPU dispatch, those are verified by timing or logs instead. See Observability for the full metric list.

What's in this section​

  1. Memory: buffer pool, execution memory, and per-operator budgets.
  2. Parallelism: workers, cores, and how NUMA placement is actually controlled.
  3. Query Performance: JIT, statistics, indexes, partitioning, and zone/segment skipping.
  4. GPU Acceleration: when GPU is used, when it falls back, and how to verify which path ran.
  5. Storage Tiering: the buffer pool as the hot-tier lever, WAL archival as the off-box cost lever, and what "cold" tiering does not do yet.
  6. Observability: the metrics endpoint, health endpoint, and log levels and format.