¶Analytics (OLAP)

ElyraSQL runs analytical queries — large aggregations and scans — through a dedicated engine designed for two properties that matter at scale: bounded memory and multi-core parallelism. This page explains how it works, when it kicks in, and how to get the most from it.

For the SQL syntax of aggregate functions and GROUP BY, see Aggregation. This page is about the execution engine behind them.

¶What "OLAP" means here

ElyraSQL is a single-file, row-oriented database that serves both transactional (OLTP) and analytical (OLAP) workloads from the same data — no separate warehouse, no ETL. The analytical path is a parallel, streaming group aggregator: it reads the table in batches, aggregates each batch on a worker thread, and merges partial results.

¶When the OLAP path is used

Any SELECT that contains an aggregate function (COUNT, SUM, AVG, MIN, MAX) or a GROUP BY is executed by the analytical engine:

SELECT COUNT(*) FROM events;
SELECT region, SUM(amount) FROM sales GROUP BY region;
SELECT COUNT(DISTINCT user_id) FROM sessions;

The planner then picks one of two strategies:

-- index-driven: reads only the matching rows, then aggregates
SELECT SUM(amount) FROM sales WHERE region = 'north';
SELECT COUNT(*)   FROM sales WHERE ts >= '2024-01-01';   -- range via index

-- full parallel streaming aggregation
SELECT region, COUNT(*), SUM(amount) FROM sales GROUP BY region;

¶Execution model

¶Streaming, bounded memory

The table is scanned in fixed-size batches. Each batch updates a set of group accumulators; the rows themselves are then discarded. Memory is therefore proportional to the number of distinct groups, not the number of rows.

• SELECT COUNT(*) FROM t keeps a single counter — constant memory over any table size.
• SELECT k, SUM(v) FROM t GROUP BY k keeps one accumulator per distinct k.

Aggregating a billion-row table does not load a billion rows into memory.

¶Parallelism

Batches are dispatched to worker threads (one per CPU by default). Each worker builds a partial aggregator; partials are then merged into the final result. Merging is exact and function-aware:

Because reads use MVCC snapshots, workers scan concurrently without contention.

¶Index-aware aggregation

When the WHERE clause is a selective equality or range on a primary-key or indexed column, the engine retrieves just the matching rows through the index (a batched multi-get) and aggregates those — avoiding a full scan entirely.

-- with an index on region, this touches only 'north' rows
CREATE INDEX sales_region ON sales (region);
SELECT AVG(amount) FROM sales WHERE region = 'north';

¶Worked example

CREATE TABLE sales (id BIGINT PRIMARY KEY, region TEXT, amount BIGINT);
-- ... load 1,000,000 rows ...

SELECT region, COUNT(*) AS n, SUM(amount) AS total, MAX(amount) AS top
FROM sales
GROUP BY region
ORDER BY total DESC;

What happens:

1. The scan streams sales in batches across all CPU cores.
2. Each worker accumulates per-region counters, sums, and running maxima.
3. Partials merge into one result (one row per region).
4. ORDER BY total DESC sorts the small grouped result.

On a developer laptop this aggregates 1,000,000 rows into ~1,000 groups in roughly 100 ms, with working memory bounded by the group count. See Performance for more numbers.

¶Decimal and typed aggregation

Aggregates respect the value types:

• SUM over DECIMAL is exact (no float rounding).
• MIN/MAX work across DATE, DATETIME, TIME, DECIMAL, text, and numbers using the same ordering as ORDER BY.
• AVG returns a floating-point result.

SELECT MIN(order_date), MAX(order_date), SUM(price) FROM orders;

¶Getting the best performance

• Index the filter column. A selective WHERE on an indexed column turns a full scan into an index read.
• Aggregate at the source. Push GROUP BY/aggregates into the query rather than pulling rows to the client.
• Fewer groups = less memory. High-cardinality GROUP BY (e.g. by a unique id) keeps one accumulator per group; prefer grouping on lower-cardinality columns where possible.
• Reads scale out. Concurrent analytical queries each get their own MVCC snapshot and run in parallel with writers and with each other.

¶Tuning for analytics (opt-in)

The defaults are safe and fast; these knobs trade memory or a bounded crash-loss window for more throughput on analytical workloads (see Configuration for details):

• ELYRASQL_AGG_WORKERS — aggregation parallelism (default min(cores, 4)).
• ELYRASQL_COLUMN_CACHE_MB — cache a table's numeric columns in memory so repeated unfiltered aggregations skip the scan (default off).
• ELYRASQL_ZONE_MAPS — per-chunk min/max so filtered aggregations skip blocks that cannot match; best for data with locality such as time series (default off).
• ELYRASQL_SYNC=normal — relaxed commit durability for much faster small-batch ingestion (bounded crash-loss window; never corrupts).

¶Limitations and tradeoffs

• Row storage with columnar execution. Data is stored row-oriented (one file, MVCC), but scalar and single-numeric-column GROUP BY aggregations run a vectorized path that decodes only the needed columns into contiguous f64 arrays; an optional in-memory columnar cache and zone-map data-skipping accelerate repeated and filtered aggregations. It is not a persisted columnar store (no on-disk column segments).
• GROUP BY spills, ORDER BY spills. High-cardinality GROUP BY aggregates in memory and spills only overflow groups to disk; ORDER BY uses an external merge sort past ELYRASQL_SORT_MAX_ROWS.
• Single-column index paths. Index-driven aggregation uses single-column equality/range; composite-key ranges fall back to a scan.
• No window functions or ROLLUP/CUBE yet (HAVING is supported).

A persisted columnar store is a possible future direction.