Real-time analytics: search and GROUP BY rows seconds after a write
SereneDB is a real-time search analytics database. A committed row is visible to the next query on its table, and the inverted index publishes it at the next refresh, every second by default. One SQL statement then searches the fresh rows and aggregates them, over the PostgreSQL wire protocol.
curl https://install.serenedb.com | shHow fresh is the data? Visible on commit, searchable on the next refresh
A nightly load decides how stale every dashboard is, and a shorter schedule only narrows the gap. In SereneDB a committed row is visible to the next query on its table. The inverted index publishes it at the next refresh, every 1000 ms by default, so a search predicate sees it a moment later. Insert a timeout and watch when each path can see it.
SELECT count(*) FROM events… FROM events_idx WHERE message @@ 'timeout' GROUP BY serviceSELECT count(*) FROM wh.events| ts | service | level | latency_ms | message | status |
|---|---|---|---|---|---|
| 12:01:40 | auth | INFO | 25 | token refreshed | searchable |
| 12:01:15 | search | WARN | 480 | upstream timeout, served stale | searchable |
| 12:01:02 | checkout | ERROR | 1210 | payment gateway timeout after retry | searchable |
| 12:00:48 | search | INFO | 18 | query served | searchable |
| 12:00:31 | checkout | ERROR | 950 | payment gateway timeout | searchable |
| 12:00:05 | checkout | INFO | 42 | order placed | searchable |
Search tables (storage = 'search'), such as the OpenTelemetry tables, show a new row to every query, count(*) included, at the refresh.
From write to dashboard: commit, refresh, query
Events arrive over the PostgreSQL wire protocol, as OTLP/HTTP exports, or through a subscription to a Postgres publication. They land in a table with an inverted index beside it, and every question, search or aggregate, is a plain SELECT. Hover a stage, or a lane inside the index.
Stream batches with COPY … FROM STDIN
Every PostgreSQL driver speaks the COPY protocol. In the text and binary formats SereneDB parses the rows on several threads while the client is still sending them, so a stream consumer writes its batches this way. The two rows below answer COPY 2.
INSERT for small writes
Six rows stand in for a feed in the walkthrough below. A committed INSERT is visible to the next query on the table.
An OTLP/HTTP listener in the server
An otel listener serves the OTLP/HTTP export endpoints for logs, traces and metrics itself. They land in seven otel_* search tables created at startup, and each export is written in one transaction. Exporters authenticate with HTTP Basic as a SereneDB role that has INSERT on those tables.
A subscription to a Postgres publication
The published tables must exist in SereneDB under the same names. CREATE SUBSCRIPTION copies their rows, then applies every committed INSERT, UPDATE and DELETE from the publisher, and the inverted index publishes them at its next refresh. The publisher runs PostgreSQL 15 or later with wal_level = logical.
The first clock: commit
A committed transaction is visible to every transaction that starts after it: SereneDB guarantees snapshot isolation. A query on the table sees every committed row, so a count or a GROUP BY over events needs no refresh.
The second clock: refresh
The inverted index is eventually consistent: a background refresh publishes new rows every refresh_interval, 1000 ms by default. Lower it for fresher search, raise it to cut overhead on write-heavy tables, or run VACUUM (REFRESH_TABLE) to publish now. INCLUDE stores latency_ms in the index, so a search returns it without a lookup in the table.
A search predicate on fresh rows
A full-text @@ predicate reads the index, which holds the rows the last refresh published: two timeouts in checkout, the worst at 1210 ms, and one in search. When order matters, BM25 ranks the matches: for 'payment timeout', rows 2, 4 and 5 score 0.783, 0.615 and 0.277.
GROUP BY in the same statement
time_bucket, count(*) FILTER and quantile_cont run over the fresh rows: in the first minute checkout logged two events, one error and a p95 of 904.6 ms. Rollups, grouping sets and columnar scans over large tables are on the OLAP database page.
Window functions over the same rows
A three-row moving average per service, with a window function: checkout climbs 42, 496, 734 ms. Add a WHERE on ts to keep it to the range a chart shows.
Grafana over raw SQL
Grafana connects through its built-in PostgreSQL data source. Panels are raw SQL, so one panel mixes a full-text filter and a time bucket. Grafana's $__timeFilter(ts) macro expands to a BETWEEN like this one: 1 timeout at 12:00, 2 at 12:01.
Rollups and retention on a schedule
CREATE JOB runs SQL inside the server on a calendar grid in UTC. One job rebuilds a per-minute error table in one transaction, for panels that read precomputed numbers; another deletes rows past their retention.
Watch the refresh backlog
sdb_metrics · num_buffered_docsRegular tables, snapshot isolation.
1000 ms by default. Lower it for fresher search; 0 turns automatic refresh off.
After a bulk load, instead of waiting for the interval.
VACUUM (REFRESH_TABLE) events;If it stays high, or avg_commit_time_ms climbs, maintenance is behind the write rate. See sdb_metrics.
SELECT c.relname, m.value AS buffered FROM sdb_metrics m JOIN pg_class c ON c.oid = m.relation_id WHERE m.metric = 'num_buffered_docs';Tutorial: a search-filtered GROUP BY over fresh rows
From an empty database to a GROUP BY over rows the index published a moment ago: one dictionary, one table, one index and two jobs. Every statement and result below ran on SereneDB 26.11.2 on 2026-11-03.
CREATE TEXT SEARCH DICTIONARY logs_dict AS split_text(case := 'lower') WITH (frequency, position, norm); CREATE TABLE events ( id BIGINT PRIMARY KEY, ts TIMESTAMP, service VARCHAR, level VARCHAR, latency_ms INTEGER, message VARCHAR ); CREATE INDEX events_idx ON events USING inverted (id, ts, service, level, message logs_dict) INCLUDE (latency_ms);
INSERT INTO events VALUES (1, '2026-11-03 12:00:05', 'checkout', 'INFO', 42, 'order placed'), (2, '2026-11-03 12:00:31', 'checkout', 'ERROR', 950, 'payment gateway timeout'), (3, '2026-11-03 12:00:48', 'search', 'INFO', 18, 'query served'), (4, '2026-11-03 12:01:02', 'checkout', 'ERROR', 1210, 'payment gateway timeout after retry'), (5, '2026-11-03 12:01:15', 'search', 'WARN', 480, 'upstream timeout, served stale'), (6, '2026-11-03 12:01:40', 'auth', 'INFO', 25, 'token refreshed');
SELECT time_bucket(INTERVAL '1 minute', ts) AS minute, service, count(*) AS events, count(*) FILTER (WHERE level = 'ERROR') AS errors, quantile_cont(latency_ms, 0.95) AS p95_ms FROM events GROUP BY ALL ORDER BY minute, service;
VACUUM (REFRESH_TABLE) events; -- or wait for refresh_interval SELECT time_bucket(INTERVAL '1 minute', ts) AS minute, service, count(*) AS timeouts, max(latency_ms) AS worst_ms FROM events_idx WHERE message @@ 'timeout' GROUP BY ALL ORDER BY minute, service;
CREATE TABLE errors_per_minute ( minute TIMESTAMP, service VARCHAR, errors BIGINT ); CREATE JOB errors_rollup EVERY 1 MINUTE AS $$ BEGIN; DELETE FROM errors_per_minute; INSERT INTO errors_per_minute SELECT time_bucket(INTERVAL '1 minute', ts), service, count(*) FROM events WHERE level = 'ERROR' GROUP BY ALL; COMMIT; $$; CREATE JOB events_retention EVERY 1 HOUR AS DELETE FROM events WHERE ts < now() - INTERVAL '30 days'; EXECUTE JOB errors_rollup; SELECT * FROM errors_per_minute ORDER BY minute, service;
What teams build on real-time analytics
Observability on OpenTelemetry
Point an OTel SDK or a collector's otlphttp exporter at SereneDB. Logs, traces and metrics land in otel_* search tables; log bodies are searchable with BM25, and timeouts per service is one GROUP BY.
SELECT service_name, count(*) AS timeouts FROM otel_logs WHERE body @@ 'timeout' GROUP BY service_name ORDER BY service_name;
| service_name | timeouts |
|---|---|
| auth | 1 |
| checkout | 1 |
Live dashboards in Grafana
Grafana connects through its built-in PostgreSQL data source. Panels are raw SQL, so one panel mixes a full-text filter, a time bucket and a percentile over rows written seconds ago, and Grafana's $__timeFilter(ts) becomes the BETWEEN.
SELECT time_bucket(INTERVAL '1 minute', ts) AS time, service, count(*) AS events, count(*) FILTER (WHERE level = 'ERROR') AS errors, quantile_cont(latency_ms, 0.95) AS p95_ms FROM events WHERE ts BETWEEN '2026-11-03T12:00:00Z' AND '2026-11-03T12:05:00Z' GROUP BY ALL ORDER BY 1, 2;
| time | service | events | errors | p95_ms |
|---|---|---|---|---|
| 12:00 | checkout | 2 | 1 | 904.6 |
| 12:00 | search | 1 | 0 | 18 |
| 12:01 | auth | 1 | 0 | 25 |
| 12:01 | checkout | 1 | 1 | 1210 |
| 12:01 | search | 1 | 0 | 480 |
Postgres rows, searchable seconds later
Subscribe to a publication on your PostgreSQL database. SereneDB copies the published tables, applies each committed change, and its index makes the new rows searchable at the next refresh, while the application keeps writing to Postgres.
Zero-ETL search →SELECT region, count(*) AS timeouts, sum(amount) AS at_risk FROM orders_idx WHERE note @@ 'timeout' GROUP BY region ORDER BY region;
| region | timeouts | at_risk |
|---|---|---|
| eu | 2 | 215.50 |
| us | 1 | 60.00 |
A real-time analytics database makes new data queryable seconds after it arrives and answers analytical queries over it fast enough for dashboards and alerts to show the present, not the last batch load. In SereneDB a committed row is visible to the next query on its table, and one SQL statement can search the fresh rows and aggregate them.
On a regular table an aggregate sees the row as soon as the transaction commits. A search predicate, and every query on a search table such as the OpenTelemetry tables, sees it after the next refresh: refresh_interval is 1000 ms by default and is set per index, or per search table. VACUUM (REFRESH_TABLE) publishes pending rows at once.
Judge candidates on four things you can test with your own data: how soon a write is queryable, how events get in, whether one query can both search and aggregate, and how fast that query is at your volume. On SearchBench (1 billion OpenTelemetry log rows, 11 engines in 17 configurations, hot runs), SereneDB 26.09.1 was the only configuration to answer all 92 queries within the 60 s cap, with a median of 31.0 ms, and the fastest of the 17 on 12 of the 16 queries that fetch the latest matches in a time window. SereneDB vs ClickHouse and SereneDB vs Tiger Data make that comparison engine by engine.
A warehouse loaded by batch ETL answers as of its last load; a real-time analytics database answers over rows committed seconds ago. SereneDB also queries Parquet, CSV and JSON files in object storage with the same SQL, so history in a data lake and today's events share one dialect: see search over a data lake.
Error rate per service per minute while an incident is still open, p95 latency on the panel an on-call engineer is watching, or the orders that hit a payment timeout in the last five minutes. Each is one SELECT over the events table, with a full-text filter where the signal sits in message text.
Not for freshness. Aggregates run at query time, so a dashboard read covers every row committed before it, and every row the index has published when it filters with a search predicate. When a panel should read precomputed numbers instead, a CREATE JOB keeps a rollup table current on a schedule, every minute for example, and another job deletes rows past their retention.
Over the PostgreSQL wire protocol with INSERT, or with COPY … FROM STDIN, which is how a Kafka or other stream consumer writes its batches; as OTLP/HTTP exports to the built-in otel listener; as change data from PostgreSQL through a subscription to a Postgres publication; or as Elasticsearch-style _bulk requests to the es listener. Each path lands in a table that SQL queries directly.
SereneDB speaks the PostgreSQL wire protocol, so psql, PostgreSQL drivers and Grafana's PostgreSQL data source connect to it. It can also subscribe to a publication on your Postgres database and make those rows searchable seconds after they commit there. For BM25 search over data you keep in Postgres, see Postgres full-text search.
Get started with SereneDB
Write each event once, to one table, and search and aggregate it a moment later in the same SQL, over the PostgreSQL wire protocol.