Performance Tuning

Buffer Pool Tuning
WAL Tuning
Parallel Query
SIMD Acceleration
JIT Compilation
io_uring (Linux)
Index Selection
Query Result Cache
Plan Cache
Connection Pooling
Columnar Storage
Monitoring

Buffer Pool Tuning

The LIRS buffer pool is the single most impactful tuning lever. Set it to 25–50% of available RAM for OLTP workloads; up to 75% for read-heavy analytics.

bash — Buffer pool configuration

# Set buffer pool size (default: 64 MB)
./bin/absdb-server --buffer-pool-mb 4096

# Embedded / edge (Raspberry Pi, IoT)
./bin/absdb-lite --buffer-pool-mb 32

# Adjust HIR ratio (default 2%)
# Lower ratio = more LIR hot pages = better for OLTP
# Higher ratio = more resident HIR = better for scans
./bin/absdb-server --buffer-pool-mb 4096 --lirs-hir-ratio 0.01

# Check current buffer pool hit rate
SELECT * FROM absdb_stats WHERE key LIKE 'bufpool_%';

Flag	Default	Description
`--buffer-pool-mb`	64	Buffer pool size in megabytes
`--lirs-hir-ratio`	0.02	Fraction of pool reserved for HIR pages
`--page-size`	4096	Default page size in bytes (4K/64K/2M)

WAL Tuning

WAL group-commit batches multiple transactions into a single fsync, dramatically increasing INSERT throughput at the cost of a small latency increase (typically < 1 ms).

bash — WAL configuration

# Default: 64 records per group-commit batch
./bin/absdb-server --wal-group-commit 64

# Maximum durability (1 fsync per transaction — lowest throughput)
./bin/absdb-server --wal-group-commit 1

# Maximum throughput (batch 256 records — ~1ms durability window)
./bin/absdb-server --wal-group-commit 256

# Async WAL (no fsync — fastest, risk of data loss on crash)
./bin/absdb-server --wal-sync-mode async

Sync Mode	Durability	Throughput	Use Case
`sync` (default)	Full (ACID)	Moderate	Production OLTP
`group`	Group window	High	High-throughput ingestion
`async`	Best-effort	Maximum	Dev / analytics / cache

Parallel Query

Parallel query is automatically engaged when the optimizer estimates more than parallel_min_rows rows will be scanned. The thread pool uses work-stealing deques for load balancing.

bash — Parallel query configuration

# Worker threads (default: logical CPU count, max 64)
./bin/absdb-server --parallel-workers 16

# Minimum rows to engage parallel scan (default: 10000)
./bin/absdb-server --parallel-min-rows 10000

# Disable parallel query (single-threaded)
./bin/absdb-server --parallel-workers 1

# Force parallel in a specific query
SELECT /*+PARALLEL(8)*/ count(*) FROM large_table;

Parallel operations: scan (B+Tree leaf range split), hash join (N-bucket partition), aggregation (per-worker partial → coordinator merge). A tournament tree merges sorted streams at the Gather node.

SIMD Acceleration

SIMD is automatically detected and enabled at startup. No manual configuration is needed. To check which level is active:

bash — Check SIMD level

SELECT * FROM absdb_stats WHERE key = 'simd_level';
-- Returns: scalar | sse42 | avx2 | avx512 | neon | sve2

# Benchmark SIMD scan performance
make test-perf
# → Outputs M elements/sec for each kernel

SIMD kernels accelerate: equality and range column scans, INT64 aggregation SUM, BM25 token matching, and FP32/FP64 vector distance (L2 and cosine). On AVX2 hardware, scan throughput is typically 2,300–4,400 M elements/sec.

JIT Compilation

The JIT compiler emits native x86-64 or ARM64 machine code for WHERE-clause predicates. JIT is enabled by default and reduces predicate evaluation overhead by 3–5×.

bash — JIT configuration

# JIT enabled by default
./bin/absdb-server

# Disable JIT (interpreter fallback — useful for debugging)
./bin/absdb-server --no-jit

# JIT statistics
SELECT * FROM absdb_stats WHERE key LIKE 'jit_%';
-- jit_enabled, jit_compilations, jit_cache_hits, jit_cache_misses

io_uring (Linux)

On Linux kernels ≥ 5.1, Absolute DB uses io_uring for asynchronous I/O (via direct syscall — no liburing dependency). This enables batched submission of up to 64 read + 64 write SQEs per tick, reducing context-switch overhead significantly.

bash — I/O backend selection

# io_uring is auto-detected and used when available (Linux 5.1+)
./bin/absdb-server

# Force direct I/O without io_uring
./bin/absdb-server --io-backend direct

# Force POSIX pread/pwrite (most portable)
./bin/absdb-server --io-backend posix

# Check active I/O backend
SELECT * FROM absdb_stats WHERE key = 'io_backend';
-- Returns: io_uring | direct | posix

Index Selection

The cost-based optimizer selects indexes using selectivity estimation. Hash indexes provide O(1) lookups for equality predicates. The ML auto-indexing advisor monitors the last 10,000 slow queries and recommends indexes where speedup > 5× and index size < 1 GB.

sql — Index advisor

-- View advisor recommendations
SELECT * FROM absdb_advisor_recommendations;

-- Example output:
-- table=orders  col=customer_id  type=btree  speedup=12.3x  size_mb=48
-- table=events  col=created_at   type=brin   speedup=8.1x   size_mb=2

-- Apply a recommendation (non-blocking background build)
CREATE INDEX CONCURRENTLY idx_orders_cust ON orders(customer_id);

-- Explicit index type selection
CREATE INDEX idx_users_hash ON users USING HASH(email);     -- O(1) equality
CREATE INDEX idx_events_brin ON events USING BRIN(ts);      -- tiny, monotonic
CREATE INDEX idx_docs_gin  ON docs USING GIN(body);         -- full-text / JSONB

Query Result Cache

Individual SELECT results can be cached with a per-query TTL. The cache holds up to 1,024 entries at 1 MB each and is invalidated automatically when any referenced table is modified.

sql — Query result cache

-- Cache result for 60 seconds
SELECT /*+CACHE(ttl=60)*/ region, sum(revenue)
FROM sales
GROUP BY region;

-- Cache with table-dependency invalidation (auto-default)
SELECT /*+CACHE(ttl=300)*/ * FROM product_catalog WHERE active = true;

-- Inspect cache entries
SELECT * FROM absdb_query_cache_stats;

-- Manually flush cache
CALL absdb_cache_flush();

Prepared Statement Plan Cache

The plan cache stores up to 1,024 compiled query plans keyed by hash of the statement text. Plans are automatically invalidated on DDL changes (ALTER TABLE, CREATE INDEX, etc.). This reduces hot-path latency from ~85 µs (cold SQL parse) to ~2 µs (cached plan).

sql — Plan cache

-- Prepare a statement (plan compiled and cached)
PREPARE get_order (INTEGER) AS
    SELECT * FROM orders WHERE id = $1;

-- Subsequent calls use the cached plan (~2 µs)
EXECUTE get_order(1001);
EXECUTE get_order(1002);

-- View plan cache contents
SELECT * FROM absdb_plan_cache;

-- Manually evict a plan
DEALLOCATE get_order;

Connection Pooling

Absolute DB supports two pooling modes via the built-in connection manager. Transaction mode reuses connections between transactions; session mode maintains a dedicated connection per client session.

Mode	Use Case	Overhead
Transaction	High-concurrency web apps (> 1,000 clients)	Minimal (~5 µs per borrow)
Session	Apps using session-scoped state (temp tables, advisory locks)	Low

bash — Connection pool configuration

# Maximum physical connections (default: 500)
./bin/absdb-server --max-connections 2000

# Enable virtual connection multiplexing (up to 65536 virtual clients)
./bin/absdb-server --virtual-connections 65536

# Pool mode: transaction (default) or session
./bin/absdb-server --pool-mode transaction

Columnar Storage for Analytics

For analytical workloads, switching to PAX columnar storage can improve scan performance by 10–50× due to zone-map predicate pushdown and late materialisation. Use the USING COLUMNAR table option or the /*+COLUMNAR*/ query hint.

sql — Columnar optimisation

-- Convert existing table to columnar storage
ALTER TABLE metrics SET USING COLUMNAR;

-- Per-query columnar hint
SELECT /*+COLUMNAR*/ date_trunc('hour', ts), avg(value)
FROM metrics
WHERE ts > now() - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1;

-- HTAP: automatic row/columnar routing (no hint needed)
-- Point lookup → row-store; full scan → columnar; planner decides
SELECT * FROM orders WHERE id = 42;           -- → row-store
SELECT region, sum(total) FROM orders;        -- → columnar

Monitoring & Observability

Absolute DB exposes Prometheus metrics at /metrics, OpenTelemetry OTLP traces, and internal virtual tables for live monitoring.

sql — Live monitoring queries

-- Active queries (running right now)
SELECT query, duration_ms, state
FROM absdb_active_queries
ORDER BY duration_ms DESC;

-- Slow query log (queries > 1ms)
SELECT query, avg_ms, calls, last_called
FROM absdb_stats
WHERE key = 'slow_queries'
ORDER BY avg_ms DESC
LIMIT 20;

-- Comprehensive server stats
SELECT * FROM absdb_stats;

-- Buffer pool efficiency
SELECT * FROM absdb_stats
WHERE key IN ('bufpool_hits', 'bufpool_misses', 'bufpool_hit_ratio');

-- Index usage stats
SELECT * FROM absdb_index_stats
ORDER BY scans DESC;

bash — Prometheus scrape

# Prometheus metrics endpoint
curl http://localhost:8080/metrics

# Key metrics exposed:
# absdb_queries_total
# absdb_query_duration_seconds (histogram)
# absdb_bufpool_hit_ratio
# absdb_wal_writes_total
# absdb_active_connections
# absdb_insert_rate
# absdb_index_scans_total

Continue Reading

Indexing Query Optimization Architecture Overview

Ready to run Absolute DB?

~154 KB binary · zero external dependencies · 2,737 tests passing · SQL:2023 100%

Download Free → View Pricing All Docs

Contents