SereneDB
964

OLAP database: roll up, slice and pivot in plain SQL

An OLAP database answers questions that scan many rows and few columns. SereneDB stores tables column by column in row groups of 122,880 rows, skips row groups with min-max zonemaps, and runs GROUP BY, ROLLUP, CUBE, PIVOT and window functions in PostgreSQL-compatible SQL over tables and Parquet files.

Get StartedAnalytical SQL docs →
curl https://install.serenedb.com | sh
columnar scan · 1M rowsreadskipped
An animation of three queries over http_requests, 1,000,000 generated requests in nine row groups of up to 122,880 rows. The first counts requests per region from 2026-09-01: it reads only the ts and region columns, the min-max zonemaps show that row groups 0 to 6 end before 09-01, so it scans 2 of 9 row groups and returns eu 53,600 and us 53,600. The second runs the same filter over a Parquet copy of the table: read_parquet reads service and latency_ms, skips row groups 0 to 6 by the file's statistics and returns checkout 71,466 requests with a 500 ms median and search 35,734 with 147 ms. The third rolls up requests and errors per region over all nine row groups: eu 500,000 and 10,205 errors, us 500,000 and 10,204, total 1,000,000 and 20,409.
OLTP vs OLAP

What is an OLAP database?

An OLAP (online analytical processing) database is built for queries that scan many rows, read a few columns and aggregate them, such as errors per service or median latency per region. OLTP, online transaction processing, is the opposite: many small reads and writes of whole rows. A row store keeps each row's fields together; a column store such as SereneDB keeps each column together, so an aggregate reads only the columns it names. Build a query over the eight requests from the walkthrough below; every result here matches SereneDB 26.11.2.

slice · where
roll up · group by
measures
requests · column segments3 of 7 columns read
The eight requests, with the columns this query reads marked
idtsregionservicestatuslatency_msmessage
110:00eucheckout200120ok
210:01eucheckout504950gateway timeout
310:02eusearch20045ok
410:03uscheckout200140ok
510:04uscheckout5031200service unavailable
610:05ussearch20060ok
710:06ussearch504800upstream timeout
810:07uscheckout200110ok
row store reads56 values
column store reads24 values
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;
The query's result
servicerequestserrorsmedian_ms
checkout52140
search3160
No filter: every row is read, but only from the columns the query names.
count(*) with no filter reads no column at all: EXPLAIN shows no table scan.
A slice fixes one dimension value: region = 'eu' keeps three of the eight requests. The scan still reads only region and the columns the query names.
status >= 500 keeps the three errors. The scan reads status and the columns the query names; the other columns are not read.
ROLLUP returns the detail rows per region and service, a subtotal per region and a grand total, in one query. A NULL key (—) marks a subtotal row.
OLTP and OLAP side by side
AspectOLTPOLAP
Query shapeOne row, or a few, by keyMany rows, a few columns
StorageEach row’s fields togetherEach column’s values together
Typical statementINSERT, UPDATE … WHERE id = 42GROUP BY, ROLLUP, window functions
What makes it fastAn index on the keyReading fewer columns, skipping row groups
ExamplePlace an orderErrors per service per day
Architecture

How an OLAP query runs in SereneDB

SereneDB stores each table column by column in row groups of 122,880 rows, each column compressed on its own. The cost-based optimizer orders joins from table statistics; the vectorized executor scans row groups in parallel, skips those whose min-max range rules a filter out, and spills grouping, joins, sorts and windows to disk when they outgrow memory. Hover a stage, or a structure inside the executor.

01 · client
PostgreSQL wire protocol
psql · drivers Grafana
02 · planner
Cost-based optimizer
statistics · join order pushdown
03 · executor
Columnar row groups
zonemapsskip row groups
column segmentsread named
row groupsin parallel
vectors of 2,048 values · spills to disk
04 · aggregate
GROUP BY · ROLLUP
CUBE · GROUPING SETS PIVOT
05 · window
OVER · QUALIFY
rank · moving average
06 · join
JOIN · ASOF · LATERAL
semi · anti · USING
07 · result
Rows or files
dashboards COPY TO Parquet
> stage 01 · client

Any Postgres client

psql, PostgreSQL drivers and Grafana’s built-in PostgreSQL data source connect over the PostgreSQL wire protocol, on port 7890 by default. Grafana panels are raw SQL.

shell
psql -h localhost -p 7890 -U postgres
> stage 02 · planner

A cost-based optimizer

Join order comes from table statistics, and ANALYZE recomputes them. The filters and the columns a query names are pushed into the scan, for tables and Parquet files alike: EXPLAIN shows both inside READ_PARQUET.

sql
COPY requests TO '/tmp/requests.parquet' (FORMAT parquet);
EXPLAIN SELECT service, median(latency_ms)
FROM read_parquet('/tmp/requests.parquet')
WHERE ts >= '2026-09-01 10:04'
GROUP BY service;
-- READ_PARQUET · Projections: service, latency_ms
-- Column Filter: ts >= '2026-09-01 10:04:00'
> stage 03 · executor

Columnar storage, skipped row groups

Each column sits in its own compressed segments per row group, with a codec chosen per column. A filter on ts skips every row group whose min-max zonemap ends before the cut-off. Row groups are also the unit of parallelism: a query on k threads needs at least k × 122,880 rows to keep them all busy. Grouping, joins, sorts and windows spill to disk when they outgrow memory.

sql · hover a line
CREATE TABLE http_requests AS
SELECT i AS id, TIMESTAMP '2026-08-01' + to_seconds(i * 3) AS ts,
CASE i % 2 WHEN 0 THEN 'eu' ELSE 'us' END AS region,
CASE i % 3 WHEN 0 THEN 'search' ELSE 'checkout' END AS service,
CASE WHEN i % 49 = 0 THEN 504 ELSE 200 END AS status,
CASE i % 3 WHEN 0 THEN i % 300 ELSE i % 1000 END::DOUBLE AS latency_ms,
'ok' AS message
FROM range(1000000) t(i);
CHECKPOINT;
-- zonemaps: 2 of 9 row groups scanned
EXPLAIN ANALYZE SELECT region, count(*) FROM http_requests
WHERE ts >= '2026-09-01' GROUP BY region;
-- Row Groups Scanned: 2 / 9
-- segments: one codec per column
SELECT DISTINCT column_name, compression
FROM pragma_storage_info('http_requests')
WHERE segment_type <> 'VALIDITY' ORDER BY ALL;
-- id BitPacking · latency_ms ALP · region DICT_FSST · status RLE
-- parallel: one row group per thread
SET threads = 4;
> stage 04 · aggregate

What cubes did, now in SQL

ROLLUP returns the detail rows per region and service, a subtotal per region (eu 3, us 5) and a grand total: 8 requests, 3 errors. CUBE adds every combination of the listed dimensions, and GROUPING SETS any chosen set.

sql
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;
> stage 05 · window

QUALIFY filters on the window

No subquery: QUALIFY keeps the top-ranked service per region, and checkout is the slowest in both, at 950 ms and 1,200 ms. Window frames give moving averages and running totals over the same rows.

sql
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;
> stage 06 · join

ASOF joins: which version was live

ASOF matches each request to the latest deploy of its service at or before it: requests 1 and 2 ran on v41, requests 4, 5 and 8 on v42, the search requests on v17. LATERAL, semi and anti joins are in the dialect too, ordered by the optimizer.

sql
CREATE TABLE deploys (service VARCHAR, deployed_at TIMESTAMP, version VARCHAR);
INSERT INTO deploys VALUES
('checkout', '2026-09-01 09:00', 'v41'),
('checkout', '2026-09-01 10:03', 'v42'),
('search', '2026-09-01 08:00', 'v17');
SELECT r.id, r.service, d.version
FROM requests r
ASOF JOIN deploys d
ON r.service = d.service AND r.ts >= d.deployed_at
ORDER BY r.id;
> stage 07 · result

Results to clients or to files

Rows go back over the wire to dashboards and reports, or to Parquet with COPY: here, the seven rows of a ROLLUP.

sql
COPY (SELECT region, service, count(*) AS requests
FROM requests
GROUP BY ROLLUP (region, service))
TO '/tmp/rollup.parquet' (FORMAT parquet);
-- COPY 7
A diagram in seven stages. A PostgreSQL client such as psql, a driver or Grafana sends SQL over the wire protocol. The cost-based optimizer orders joins from table statistics and pushes filters and column lists into the scan. The executor reads columnar row groups: zonemaps skip row groups whose min-max range rules a filter out, column segments are read only for the columns the query names, and row groups run in parallel. Aggregation (GROUP BY, ROLLUP, CUBE, GROUPING SETS, PIVOT), window functions with QUALIFY and joins (ASOF, LATERAL, semi, anti) produce rows for the client or a Parquet file through COPY.

Cube operations, in SQL

ROLLUP · FILTER · PIVOT · CUBE
roll up · drill down
GROUP BY ROLLUP

Detail rows, subtotals and a grand total in one query; drill down by adding a column to the list.

GROUP BY ROLLUP (region, service)
slice · dice
WHERE and FILTER

Fix a dimension with WHERE, or count a slice next to the total with FILTER.

count(*) FILTER (WHERE status >= 500)
pivot
PIVOT

Turn a dimension’s values into columns: eu and us side by side, checkout 2 and 3, search 1 and 2.

PIVOT requests ON region USING count(*) GROUP BY service
every combination
GROUP BY CUBE

All groupings of the listed dimensions, nine rows here; GROUPING() tells the levels apart.

GROUP BY CUBE (region, service)
Build it

From rows to roll-ups in five statements

One table of eight HTTP requests: measures with FILTER, a ROLLUP, a join to a dimension table and a top-N per group. Every statement and result below ran on SereneDB 26.11.2 on 2026-11-03.

step 01 / 05 · sql
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');
INSERT 0 8. For Parquet files: CREATE TABLE requests AS SELECT * FROM read_parquet('/tmp/requests/*.parquet');
step 02 / 05 · sql
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;
checkout: 5 requests, 2 errors, 140 ms median; search: 3, 1 and 60 ms.
step 03 / 05 · sql
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;
eu 3 requests and 1 error, us 5 and 2, total 8 and 3. A NULL key marks a subtotal row.
step 04 / 05 · sql
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;
payments: 5 requests, 2 errors; discovery: 3 and 1.
step 05 / 05 · sql
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;
checkout is the slowest service in both regions: 950 ms in eu, 1,200 ms in us.
Use cases

What teams build on it

Dashboards and reports in raw SQL

Grafana charts SereneDB through its built-in PostgreSQL data source, with panels written in raw SQL. One ROLLUP query returns the detail rows, the subtotal per region and the grand total a report table needs.

SQL features
GrafanaROLLUPFILTER
Real-time analytics →
example · rollup
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;
result
regionservicerequestserrors
eucheckout21
eusearch10
eu—31
uscheckout31
ussearch21
us—52
——83
A NULL key, shown as —, marks a subtotal row.

Parquet where it sits

read_parquet queries Parquet files on disk or in S3, and pushes the filters and the named columns into the scan; the file’s row-group statistics skip whole row groups, as the table’s zonemaps do. Load the files with CREATE TABLE … AS SELECT when the same data is queried again and again, or when many joins need SereneDB’s own table statistics.

SQL features
read_parquetCOPYpushdown
Search over a data lake →
example · pushdown
SELECT service, count(*) AS requests, median(latency_ms) AS median_ms
FROM read_parquet('/tmp/requests.parquet')
WHERE ts >= '2026-09-01 10:04'
GROUP BY service
ORDER BY service;
result
servicerequestsmedian_ms
checkout2655
search2430
The file is step 1’s table, written by stage 02’s COPY. EXPLAIN shows only service and latency_ms projected, with the ts filter inside READ_PARQUET.

Analyze your Postgres tables

ATTACH a PostgreSQL database with TYPE postgres, and its tables are queryable next to local ones. Run ROLLUP and window functions over them as they are, or copy them into a columnar table with CREATE TABLE … AS SELECT.

SQL features
ATTACHTYPE postgresCTAS
Zero-ETL search →
example · attach
ATTACH 'host=pg.internal dbname=app user=analytics'
    AS app (TYPE postgres, READ_ONLY);

SELECT region, count(*) AS requests,
       count(*) FILTER (WHERE status >= 500) AS errors
FROM app.public.requests
GROUP BY ROLLUP (region)
ORDER BY region NULLS LAST;
result
regionrequestserrors
eu31
us52
—83
Run against PostgreSQL 17.11 holding step 1’s eight rows. READ_ONLY refuses writes to the attached database.
FAQ

Frequently asked questions

Go to docs →

An OLAP (online analytical processing) database is built for queries that scan many rows, read a few columns and aggregate them, such as errors per service per day. Most store data by column and process it in batches of values, so a query reads only the columns it names. SereneDB stores tables in columnar row groups and answers these queries in PostgreSQL-compatible SQL.

Neither: SQL is the language both kinds of system speak. OLTP and OLAP name workloads, many small transactions on single rows versus large aggregations over many rows, and the storage and execution engine decide which one a database serves. In SereneDB the OLAP side is plain SQL: GROUP BY, ROLLUP, CUBE, window functions and joins.

PostgreSQL stores each table as 8 kB pages of whole rows (PostgreSQL 18 documentation, read on 2026-11-03), the layout OLTP needs, so a scan over two columns still reads pages that hold every column. SereneDB keeps the PostgreSQL wire protocol and SQL, so psql, drivers and Grafana’s PostgreSQL data source connect unchanged, and stores tables by column. It can also ATTACH a PostgreSQL database and aggregate its tables.

SQL on columnar engines. Instead of a cube built ahead of time, GROUP BY ROLLUP returns subtotals and a grand total, CUBE every combination of the listed dimensions, GROUPING SETS any chosen set, and PIVOT turns a dimension into columns, all at query time. In SereneDB they run on stored tables or straight on Parquet files.

MOLAP keeps pre-aggregated multidimensional cubes; ROLAP runs analytical queries as SQL over relational tables; HOLAP mixes the two. SereneDB works the relational way, on tables stored column by column and aggregated when the query runs, with nothing to pre-build.

Parquet is a columnar file format made for OLAP reads: row groups, one column chunk per column, and per-column statistics. SereneDB queries Parquet in place with read_parquet, pushing filters and the named columns into the scan, or loads it into a table with CREATE TABLE … AS SELECT.

The one that answers your queries where your data already is, in SQL your tools already speak. SereneDB runs analytical SQL over its own tables, Parquet files and attached PostgreSQL databases, through PostgreSQL clients. On SearchBench's September 2026 run (1 billion OpenTelemetry log rows, 92 search and analytics queries, 11 engines in 17 configurations, hot runs), SereneDB 26.09.1 was the only one to finish all 92 within the 60 s cap, with the lowest geometric mean: 1.11× the fastest result per query. It was also the fastest on 8 of the 9 join queries. SereneDB vs ClickHouse and SereneDB vs Tiger Data go through the differences feature by feature.

Yes: SereneDB is licensed under the Apache License 2.0, and its source is on GitHub at serenedb/serenedb. Its docs call it a search-OLAP database: full-text search, vector search and analytical queries over the same data, in one engine.

A data warehouse is where an organization keeps integrated, historical data for analysis; an OLAP database is the engine that answers analytical queries over such data. SereneDB is the engine: it queries its own columnar tables, Parquet files on disk or in object storage, and attached PostgreSQL databases.

> get started

Get started with SereneDB

Install SereneDB, load a table or point read_parquet at your files, and run ROLLUP, PIVOT and window functions in the SQL your Postgres tools already speak.

Get StartedRead the docs →