Open-Source OLAP Database: Columnar SQL Analytics Beside Search
An OLAP database answers analytical questions (totals, percentiles, subtotals, top-N) by scanning a few columns across many rows. SereneDB runs them in SQL on columnar data, in the same engine as full-text and vector search.
Apache-2.0, PostgreSQL wire protocol, one binary. A vectorized engine scans row groups in parallel, and zonemaps skip those a filter rules out.
What Is an OLAP Database?
OLAP, online analytical processing, is the workload behind dashboards, reports and ad-hoc analysis: read many rows, touch a handful of columns, aggregate them by dimensions such as region, service or day. An OLAP database is built for that shape: a GROUP BY over millions of rows, not an UPDATE of one row by key.
Three techniques do most of the work: columnar storage, so a scan reads only the columns it names; vectorized execution over batches of values; and min-max metadata that lets a filter skip whole blocks. What OLAP cubes once did is now mostly SQL: ROLLUP, CUBE and GROUPING SETS.
avg(latency_ms) row store → reads every field column store → reads one column
Why an OLAP Database Stores Columns: OLAP vs OLTP
Both speak SQL; the query shape differs, and so does the storage.
Transactions on whole rows
Online transaction processing: many short reads and writes of single rows by key, like placing an order. Row-oriented storage keeps a row's fields together, so one row is one fetch. PostgreSQL is the familiar example.
Scans over a few columns
Online analytical processing: fewer, larger queries that scan millions of rows, read three or four columns, then group and aggregate. Data arrives in bulk, rarely updated row by row.
How the two meet
The OLTP database stays the system of record; the OLAP side gets a copy through ETL or CDC. SereneDB can also query data where it sits: Parquet, CSV and JSON in object storage, or a remote PostgreSQL that CREATE SERVER names, queried as server.schema.table.
What Is a Columnar Database?
A columnar, or column-oriented, database stores each column's values together instead of each row's. The OLAP advantages follow, and so does the cost: adding or deleting one row touches every column.
Only the columns you name
avg(latency_ms) over a seven-column table reads one column. Parquet is columnar too, and SereneDB pushes filters and projections into Parquet scans.
One type, side by side
Values of one type compress well. SereneDB compresses persistent tables at checkpoint with lightweight methods such as bitpacking, and PRAGMA storage_info shows what each column segment uses.
Zonemaps
SereneDB keeps min-max zonemaps automatically: ts >= '2026-09-01' skips every row group whose range ends earlier. The more ordered the column, the more it skips.
How SereneDB Executes OLAP Queries
Tables are split into row groups of 122,880 rows by default, each column in its own segments with min-max stats; the vectorized executor runs row groups in parallel and spills to disk when a GROUP BY, join, sort or window outgrows memory.
Analytical SQL
FILTER, GROUPING SETS, ROLLUP and CUBE, window functions with QUALIFY, PIVOT, and aggregates from median to approx_count_distinct. A cost-based optimizer orders joins from table statistics, and ASOF, LATERAL, semi and anti joins are in the dialect.
CREATE TABLE teams (service VARCHAR, team VARCHAR);
INSERT INTO teams VALUES ('checkout', 'payments'), ('search', 'discovery');
SELECT t.team,
count(*) AS requests,
count(*) FILTER (WHERE r.status >= 500) AS errors
FROM requests r
JOIN teams t USING (service)
GROUP BY ALL
ORDER BY errors DESC;OLAP next to search, one statement
An inverted index sits beside the columnar data: a full-text predicate picks the rows, GROUP BY aggregates them. Single-column grouping sets over NOT NULL keyword columns are counted from its term dictionary. The index is eventually consistent, refreshed every second by default.
SELECT region, service, count(*) AS timeouts
FROM requests_idx
WHERE message @@ 'timeout'
GROUP BY GROUPING SETS ((region), (service));Step-by-Step: OLAP Queries in SQL in 5 Minutes
Create a fact table and load it
Eight HTTP requests. For real data, CREATE TABLE … AS SELECT from read_parquet('s3://…').
CREATE TABLE requests (id INTEGER PRIMARY KEY, ts TIMESTAMP,
region VARCHAR NOT NULL, service VARCHAR NOT NULL,
status INTEGER, latency_ms DOUBLE, message VARCHAR);
INSERT INTO requests VALUES
(1, '2026-09-01 10:00', 'eu', 'checkout', 200, 120, 'ok'),
(2, '2026-09-01 10:01', 'eu', 'checkout', 504, 950, 'gateway timeout'),
(3, '2026-09-01 10:02', 'eu', 'search', 200, 45, 'ok'),
(4, '2026-09-01 10:03', 'us', 'checkout', 200, 140, 'ok'),
(5, '2026-09-01 10:04', 'us', 'checkout', 503, 1200, 'service unavailable'),
(6, '2026-09-01 10:05', 'us', 'search', 200, 60, 'ok'),
(7, '2026-09-01 10:06', 'us', 'search', 504, 800, 'upstream timeout'),
(8, '2026-09-01 10:07', 'us', 'checkout', 200, 110, 'ok');Aggregate with FILTER
One pass, three measures: checkout 5 requests, 2 errors, 140 ms median; search 3, 1 and 60 ms.
SELECT service,
count(*) AS requests,
count(*) FILTER (WHERE status >= 500) AS errors,
median(latency_ms) AS median_ms
FROM requests
GROUP BY service
ORDER BY service;Subtotals with ROLLUP
Drill-down rows per region and service, a subtotal per region (eu 3, us 5) and a grand total: 8 requests, 3 errors.
SELECT region, service, count(*) AS requests,
count(*) FILTER (WHERE status >= 500) AS errors
FROM requests
GROUP BY ROLLUP (region, service)
ORDER BY region NULLS LAST, service NULLS LAST;Top-N per group with a window function
QUALIFY filters on the window without a subquery: checkout is slowest in both regions, at 950 and 1200 ms.
SELECT region, service, max(latency_ms) AS worst_ms
FROM requests
GROUP BY region, service
QUALIFY rank() OVER (PARTITION BY region ORDER BY max(latency_ms) DESC) = 1
ORDER BY region;Search, then aggregate
The @@ match finds the two timeouts and GROUP BY runs over just those: checkout 950 ms, search 800 ms.
CREATE TEXT SEARCH DICTIONARY log_dict AS
split_text(case := 'lower');
CREATE INDEX requests_idx ON requests
USING inverted (id, region, service, message log_dict);
VACUUM (REFRESH_TABLE) requests;
SELECT service, count(*) AS timeouts, max(latency_ms) AS worst_ms
FROM requests_idx
WHERE message @@ 'timeout'
GROUP BY service
ORDER BY service;Use Cases for an OLAP Database
Dashboards over PostgreSQL wire
Grafana charts SereneDB through its built-in PostgreSQL data source, in raw SQL. ROLLUP returns detail rows and totals in one query.
Log and trace analytics
OpenTelemetry arrives over OTLP/HTTP into search tables: full-text over log bodies, latency percentiles per service and error rates from spans.
Ad-hoc Parquet analysis
Query Parquet where it sits with filters pushed into the scan; load it when joins need better statistics or queries repeat. Indexing it in place: search over a data lake.
OLAP Database Examples
Grouped by how they run, each in its own documentation's words.
Embedded in your app
DuckDB is built to run embedded in a host process, for analytical workloads, under the MIT license.
Clusters for real-time analytics
ClickHouse is a column-oriented SQL DBMS for OLAP, open source and in the cloud. Apache Druid is a real-time analytics database; Apache Pinot a real-time distributed OLAP datastore.
When a Distributed OLAP Engine Is the Better Pick
For fresh data, see the real-time analytics page.
Data outgrows one machine
ClickHouse shards data too large for a single server and replicates it, and typical Druid deployments span tens to hundreds of servers. SereneDB's docs describe one binary on a single node.
You want rollups at ingest
Druid can roll up rows at ingestion, trading individual events for fewer rows. SereneDB's ROLLUP and CUBE aggregate raw rows at query time; there is no MATERIALIZED VIEW.
You need a managed service or OLAP benchmarks
Snowflake and BigQuery run for you; SereneDB's docs describe no managed cloud. SearchBench, our full-system benchmark, is 92 search, aggregation and join queries over OpenTelemetry logs, not ClickBench or TPC-H.
Frequently Asked Questions
One built for analytical queries that scan and aggregate many rows over a few columns, such as p95 latency by service. Most store data by column and execute vectorized; SereneDB does both.
Neither: SQL is the language both use. OLAP and OLTP name workloads; storage and execution decide which one an engine serves well.
Most modern ones are: ClickHouse, Snowflake and BigQuery document columnar storage, and SereneDB stores tables column by column in row groups.
Not natively. PostgreSQL stores a table as pages of whole rows; heap is its default table access method. SereneDB uses PostgreSQL's parser and wire protocol but is a different, columnar engine, and not every feature carries over: there is no tsvector.
The best analytical database fits where the data lives. Inside an application: an embedded engine. Many nodes: ClickHouse, Druid or Pinot. No servers: a cloud warehouse. Analytics over tables you also search, on one node: SereneDB.
Yes: Apache-2.0, with the source on GitHub. Its docs call it a search-OLAP database: search and analytical queries over the same data, in one engine.
Modern ones mostly use SQL; MDX belongs to cube servers. In SereneDB a roll-up is GROUP BY ROLLUP, all dimension combinations GROUP BY CUBE, pivoting PIVOT.
Get Started with SereneDB
One binary, one table, five steps from rows to subtotals, top-N and a search-filtered aggregate.