Enterprise Performance

Query Optimizer

Absolute DB uses a cost-based query optimizer that evaluates multiple execution plans and selects the lowest-cost plan before executing any query. The optimizer estimates row counts, I/O cost, and CPU cost for each candidate plan using table statistics and index selectivity.

Key optimizer capabilities

• Index selection — chooses between B+Tree, Hash, BRIN, GIN, HNSW, and partial indexes based on predicate selectivity; Hash indexes give O(1) for equality predicates.
• Join ordering — dynamic programming over join trees; switches to greedy for > 8 tables.
• Predicate pushdown — moves WHERE filters as close to storage as possible, including across C-RAID storage nodes.
• Adaptive re-optimisation — if actual row counts diverge > 10× from estimates mid-execution, the optimizer switches to a better plan without restarting the query.
• Store selection (HTAP) — automatically routes point lookups to the row store and full-table scans to the PAX columnar store.
• Parallel cost model — estimates speedup from parallelism and enables parallel plans only when the benefit justifies the coordination overhead.

EXPLAIN output

-- Inspect the chosen plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- With actual timing
EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 1000;

The optimizer's statistics are refreshed automatically after bulk writes and can be triggered manually:

ANALYZE orders;          -- refresh statistics for one table
ANALYZE;                 -- refresh all tables

Parallel Query Execution

Absolute DB's parallel query engine uses a work-stealing thread pool of up to 64 workers (Enterprise Edition). Parallel scan, parallel hash join, parallel aggregation, and N-way merge gather are all supported.

parallel_workers        = 64    # max worker threads (Enterprise: 64, SME: 4)
parallel_min_rows       = 10000 # minimum estimated rows to parallelise
parallel_cost_threshold = 1000  # planner cost threshold
max_parallel_degree     = 16    # max workers for a single query

-- Force parallel scan
SET parallel_workers = 16;
EXPLAIN ANALYZE SELECT SUM(amount) FROM orders WHERE status = 'completed';
-- Parallel Seq Scan on orders (workers: 16)
-- Workers Launched: 16
-- -> Partial Aggregate (workers: 16)

-- Per-query hint
SELECT /*+ PARALLEL(orders 8) */ SUM(amount) FROM orders;

SIMD Vectorised Execution

All scan kernels, aggregations, and vector distance functions are implemented with SIMD intrinsics. The runtime selects the best instruction set via CPUID (x86) or getauxval(AT_HWCAP) (ARM) — with a scalar fallback for every kernel, ensuring correctness everywhere.

Kernels

SIMD kernels are selected automatically at startup based on CPU capabilities. Operations accelerated include column scan filters, vector distance calculations (L2, cosine), aggregation, and string search. Processes 8 integers or 8 floats per cycle on AVX2, scaling up to 16 on AVX-512.

SIMD acceleration is active in all editions — no configuration required.

JIT Query Compilation

Absolute DB emits native machine code for WHERE clause predicates directly into an executable memory buffer. Compiled predicates are cached by hash-keyed plan cache.

jit_enabled     = true       # enable JIT compilation
jit_min_cost    = 100        # minimum planner cost to JIT-compile
jit_inline_cost = 500        # cost threshold for inlining subexpressions

HTAP Dual-Store Engine

The HTAP engine maintains a row store and a columnar PAX store simultaneously, synchronised via a 4,096-entry lock-free zero-copy replication ring. The query planner automatically selects the optimal store per query — no ETL pipeline required.

-- Enable HTAP on a table
CREATE TABLE orders (
  id         BIGINT PRIMARY KEY,
  customer_id BIGINT,
  amount     DECIMAL(12,2),
  status     TEXT,
  created_at TIMESTAMP
) WITH (htap = true);  -- both stores maintained automatically

-- Point lookup uses row store (O(1) B+Tree)
SELECT * FROM orders WHERE id = 99887766;

-- Analytical scan uses columnar PAX store (zone maps, SIMD)
SELECT status, SUM(amount) FROM orders GROUP BY status;

-- Replication lag
SELECT * FROM absdb_htap_repl_lag;  -- typically < 5 ms

Query Result Cache

The query result cache caches complete result sets for identical SQL text + bind parameters. Results are invalidated automatically on INSERT, UPDATE, DELETE, or DDL on any referenced table.

qcache_enabled    = true
qcache_size_mb    = 256       # total cache memory
qcache_max_result = 1048576   # max result size cached (bytes, default 1 MB)
qcache_ttl        = 60        # maximum result age (seconds, 0 = no TTL)

-- Per-query cache control
SELECT /*+ CACHE(ttl=300) */ COUNT(*) FROM products WHERE category = 'electronics';
SELECT /*+ NO_CACHE */ * FROM live_prices WHERE symbol = 'AAPL';

-- Cache statistics
SELECT * FROM absdb_qcache_stats;
-- hits | misses | evictions | size_mb | hit_ratio
-- 4821 | 312    | 88        | 41      | 93.9%

Prepared Statement Plan Cache

The plan cache stores compiled query plans for PREPAREd statements. Cached plans reduce SQL path latency from ~85 µs to ~2 µs for warm queries. Plans are invalidated automatically on DDL changes to referenced tables.

-- Prepare a statement (plan compiled + cached)
PREPARE get_order(BIGINT) AS
  SELECT id, amount, status FROM orders WHERE id = $1;

-- Execute (uses cached plan, ~2 µs)
EXECUTE get_order(99887766);

-- Deallocate
DEALLOCATE get_order;

-- View cache
SELECT * FROM absdb_plan_cache;
-- name       | uses  | avg_time_us | created_at
-- get_order  | 18422 | 1.9         | 2026-04-05 09:00:00

PAX Columnar Storage

PAX (Partition Attributes aXis) columnar pages store all values for a column contiguously within a 64 KB page. Zone maps (per-column min/max in the page header) allow entire pages to be skipped without reading a single data byte.

-- Create a columnar table (analytics workload)
CREATE TABLE events (
  ts        TIMESTAMP,
  user_id   BIGINT,
  event     TEXT,
  value     REAL
) WITH (storage = columnar, page_size = 65536);

-- EXPLAIN shows zone-map predicate pushdown
EXPLAIN SELECT COUNT(*) FROM events WHERE ts > '2026-01-01';
-- PAX Columnar Scan on events
--   Zone Map Filter: (ts > '2026-01-01')
--   Pages skipped by zone map: 14,832 / 15,000 (98.9%)

Compression

Gorilla Time-Series Compression

The Gorilla compression algorithm uses XOR delta encoding for floating-point time-series values, achieving up to 10:1 compression on typical sensor and metrics data.

-- Enable Gorilla compression on a hypertable
CREATE TABLE metrics (
  ts     TIMESTAMP NOT NULL,
  sensor TEXT,
  value  REAL
) WITH (
  hypertable  = true,
  chunk_interval = '1 hour',
  compression = gorilla
);

-- Compression statistics
SELECT * FROM absdb_compression_stats WHERE table_name = 'metrics';
-- rows | compressed_bytes | raw_bytes | ratio
-- 10M  | 24 MB            | 240 MB    | 10:1

Advisory Locks

Advisory locks provide application-level cooperative locking compatible with Flyway, Liquibase, Alembic, and golang-migrate. Up to 65,536 concurrent advisory locks are supported.

-- Session-scoped exclusive lock
SELECT pg_advisory_lock(42);
-- ... critical section ...
SELECT pg_advisory_unlock(42);

-- Transaction-scoped (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(42);
-- ... work ...
COMMIT;

-- Non-blocking try
SELECT pg_advisory_try_lock(42);  -- returns true/false

-- Shared lock (multiple readers)
SELECT pg_advisory_lock_shared(42);
SELECT pg_advisory_unlock_shared(42);

LISTEN / NOTIFY

The LISTEN/NOTIFY implementation is compatible with the PostgreSQL wire protocol NotificationResponse ('A' message). Any PostgreSQL client library that uses LISTEN/NOTIFY works unchanged — Rails ActionCable, Django Channels, and every PG real-time app.

-- Publisher
NOTIFY order_events, '{"order_id": 99887766, "status": "paid"}';

-- Subscriber (in a separate session)
LISTEN order_events;
-- Asynchronous notification received:
--   channel=order_events  payload={"order_id":99887766,"status":"paid"}
--   pid=14421

UNLISTEN order_events;  -- unsubscribe

COPY Binary Protocol

The binary COPY format is compatible with the PostgreSQL binary COPY protocol, enabling bulk loads at up to 3× the speed of CSV COPY by eliminating text parsing overhead.

-- Binary bulk import (3x faster than CSV)
COPY orders FROM '/tmp/orders.bin' WITH (FORMAT binary);

-- Binary export
COPY orders TO '/tmp/orders.bin' WITH (FORMAT binary);

-- COPY from STDIN (PostgreSQL wire compatible)
COPY orders (id, customer_id, amount, status) FROM STDIN WITH (FORMAT binary);

-- CSV still supported
COPY orders FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');