Log analysis: search the message and GROUP BY the fields in one query

Log analysis is searching application logs for the events behind an incident and counting them by service and time. SereneDB stores OpenTelemetry log records in a search table: one SQL query matches a phrase, a pattern or a trace ID in the message and groups the matches.

Get Started OpenTelemetry docs →

curl https://install.serenedb.com | sh

otel_logs · 12 recordsmatchgroup

What is log analysis

How do you perform log analysis? Five questions over twelve log lines

Log analysis turns raw log lines into answers: which errors, in which service, how often and why. To analyze logs you search the text, read it the way the index splits it, collapse repeats into patterns, pull fields out of the line, and follow one request across services. Pick a question to run it over twelve OpenTelemetry log records; every result here ran on SereneDB 26.11.2.

incident lab · otel_logs

log stream · 2026-11-032 lines · 2 rows

09:14:02.000INFOgatewayGET /api/v2/orders/8812 200 41ms
09:14:05.000INFOcheckoutorder 8812 created for user 4821
09:14:07.120ERRORpaymentsupstream connect error: connection reset by peer (10.0.3.17:5432)match
09:14:07.480ERRORcheckoutpayment failed for order 8812: upstream timeout after 3000ms
09:14:08.000WARNgatewayPOST /api/v2/checkout 502 3012ms
09:14:11.000INFOauthhealthcheck ok: connection reset count 0-healthcheck
09:14:15.200ERRORpaymentsupstream connect error: connection reset by peer (10.0.3.17:5432)match
09:14:15.610ERRORcheckoutpayment failed for order 8817: upstream timeout after 3000ms
09:14:16.000WARNgatewayPOST /api/v2/checkout 502 3008ms
09:14:20.000WARNauthtoken refresh slow: took=1840ms user=4821
09:15:02.000ERRORpaymentsjava.net.SocketTimeoutException: Read timed out at io.grpc.ClientCalls.blockingUnaryCall
09:15:04.000INFOgatewayGET /healthz 200 2ms

Phrase

sql

SELECT "timestamp", service_name, body
FROM otel_logs
WHERE body @@ websearch_to_tsquery('"connection reset" -healthcheck')
ORDER BY "timestamp";

result · 2 rows

timestamp	service_name	body
2026-11-03 09:14:07.12	payments	upstream connect error: connection reset by peer (10.0.3.17:5432)
2026-11-03 09:14:15.2	payments	upstream connect error: connection reset by peer (10.0.3.17:5432)

Three lines hold the phrase connection reset, words adjacent and in order. The minus drops the auth healthcheck line, so two remain.

Tokens

line 11, as the index splits it

java.net.sockettimeoutexception read timed out at io.grpc.clientcalls.blockingunarycall

query

sql

SELECT ts_lexize('otel_body_dict',
  'java.net.SocketTimeoutException: Read timed out at io.grpc.ClientCalls.blockingUnaryCall');

SELECT "timestamp"::TIME AS t, service_name, body
FROM otel_logs
WHERE body @@ 'timeout'
ORDER BY "timestamp";

result · 2 rows

t	service_name	body
09:14:07.48	checkout	payment failed for order 8812: upstream timeout after 3000ms
09:14:15.61	checkout	payment failed for order 8817: upstream timeout after 3000ms

sql

SELECT ts_lexize('otel_body_dict',
  'java.net.SocketTimeoutException: Read timed out at io.grpc.ClientCalls.blockingUnaryCall');

SELECT "timestamp"::TIME AS t, service_name, body
FROM otel_logs
WHERE body @@ ts_like('%timeout%')
ORDER BY "timestamp";

result · 3 rows

t	service_name	body
09:14:07.48	checkout	payment failed for order 8812: upstream timeout after 3000ms
09:14:15.61	checkout	payment failed for order 8817: upstream timeout after 3000ms
09:15:02	payments	java.net.SocketTimeoutException: Read timed out at io.grpc.ClientCalls.blockingUnaryCall

The log body dictionary keeps dotted names, IPs and durations whole, so java.net.SocketTimeoutException is one token. A word query for timeout misses it; ts_like('%timeout%') matches inside the token, and ts_starts_with('io.grpc') finds the gRPC frame.

Patterns

sql

SELECT regexp_replace(body, '[0-9]+', '<n>', 'g') AS pattern,
       count(*) AS n, min("timestamp")::TIME AS first_seen
FROM otel_logs
WHERE severity_text IN ('WARN', 'ERROR')
GROUP BY pattern ORDER BY n DESC, first_seen;

result · 5 rows

pattern	n	first_seen
upstream connect error: connection reset by peer (<n>.<n>.<n>.<n>:<n>)	2	09:14:07.12
payment failed for order <n>: upstream timeout after <n>ms	2	09:14:07.48
POST /api/v<n>/checkout <n> <n>ms	2	09:14:08
token refresh slow: took=<n>ms user=<n>	1	09:14:20
java.net.SocketTimeoutException: Read timed out at io.grpc.ClientCalls.blockingUnaryCall	1	09:15:02

Masking the numbers turns eight warnings and errors into five patterns. Three come in pairs: one line for each of the two failed checkouts.

Fields

sql

SELECT r.path, r.status, count(*) AS requests, max(r.ms::INT) AS worst_ms
FROM (SELECT regexp_extract(body, '^(GET|POST) (\S+) (\d{3}) (\d+)ms$',
                            ['method', 'path', 'status', 'ms']) AS r
      FROM otel_logs WHERE service_name = 'gateway')
GROUP BY ALL ORDER BY ALL;

result · 3 rows

path	status	requests	worst_ms
/api/v2/checkout	502	2	3012
/api/v2/orders/8812	200	1	41
/healthz	200	1	2

regexp_extract with a list of names returns a struct whose fields group like columns. Both 502s took over three seconds. A status sent as an OTel attribute is a column away: log_attributes ->> 'http.status_code'.

Trace

sql

SELECT "timestamp", service_name, severity_text, body
FROM otel_logs
WHERE trace_id @@ ts_starts_with('4bf92f35')
ORDER BY "timestamp";

result · 3 rows

timestamp	service_name	severity_text	body
2026-11-03 09:14:07.12	payments	ERROR	upstream connect error: connection reset by peer (10.0.3.17:5432)
2026-11-03 09:14:07.48	checkout	ERROR	payment failed for order 8812: upstream timeout after 3000ms
2026-11-03 09:14:08	gateway	WARN	POST /api/v2/checkout 502 3012ms

Eight characters pasted from a dashboard are enough: trace_id is indexed as one exact term, so a prefix finds it. Payments failed first; checkout gave up 360 ms later and the gateway answered 502 at 880 ms.

Checklist

What a log analysis tool needs, and how SereneDB does it in SQL

A log analysis tool has to collect the logs, search their text, keep identifiers intact, filter and group on fields, follow a trace and expire old rows. Most lists of log analysis tools compare products; these are the jobs, each with the SereneDB feature that does it and a link to its docs.

Need	How SereneDB does it	Looks like
Collect logs	An OTLP/HTTP listener, an Elasticsearch-style _bulk endpoint, INSERT and COPY over the PostgreSQL wire protocol, and read_json for files. Docs →	`POST /v1/logs`
Search the message	A phrase, a minus word, prefixes, regular expressions and LIKE patterns on tokens, with @@. Docs →	`websearch_to_tsquery('"connection reset" -healthcheck')`
Keep identifiers whole	Word-boundary splitting with no stemming: IPs, dotted names and durations stay single tokens. Docs →	`ts_lexize('otel_body_dict', …)`
Filter on fields	Exact indexed service, severity and trace id; JSON attributes with ->>, indexable as expressions. Docs →	`(log_attributes ->> 'http.status_code') = '502'`
Count and group	GROUP BY, time_bucket, regexp_replace and regexp_extract in the statement that searches. Docs →	`GROUP BY time_bucket(INTERVAL '1 minute', "timestamp")`
Correlate	trace_id is indexed in otel_logs and otel_traces; an id or its prefix finds a whole request. Docs →	`trace_id @@ ts_starts_with('4bf92f35')`
Retain	A scheduled job deletes the rows older than the period. Docs →	`CREATE JOB … AS DELETE …`
Use your tools	psql, Grafana's PostgreSQL data source and PostgreSQL drivers. Docs →	`psql -h localhost -p 7890 -U postgres`

Architecture

Log records land in a search table; one index covers the message, the fields and the trace ID

Logs arrive as OTLP/HTTP exports, as Elasticsearch-style _bulk requests, or over the PostgreSQL wire protocol. They land in a search table whose inverted index covers the timestamp, the analyzed body and the exact fields, and every question is a SELECT. Hover a stage, or a lane inside the index.

01 · ingest

OTLP/HTTP

otel listener :4318

01 · ingest

_bulk

es listener :9200

01 · ingest

INSERT · COPY

PostgreSQL wire :7890

02 · store

otel_logs

search table created at startup

searchable at the next refresh, 1 s by default

03 · inverted index

otel_logs_idx

body otel_body_dicttext

service_name, severity_textexact

trace_idexact

"timestamp"range

04 · search

phrase · pattern · prefix

05 · group

GROUP BY

time_bucket regexp_extract

06 · schedule

CREATE JOB

retention

07 · clients

PostgreSQL clients

psql · Grafana drivers

> ingest · otel

An OTLP/HTTP listener in the server

Point an OTel SDK or a collector's otlphttp exporter at it. The otel listener serves /v1/logs, /v1/traces and /v1/metrics, writes each export in one transaction, and creates the seven otel_* tables at startup. The docs recommend a collector's batch processor in front, so that exports arrive as few large requests.

shell

serened ./data \

--listen 'postgres://0.0.0.0:7890,http://0.0.0.0:4318?api=otel'

# OpenTelemetry tables created in postgres.public: otel_logs, otel_traces, …

> ingest · elasticsearch

Elasticsearch-style _bulk

An es listener answers a subset of the Elasticsearch REST API. NDJSON posted to _bulk lands in a table in the es schema, which SQL reads like any other. Create the index with its mapping first; fields are flat, and documents are create-only.

shell

serened ./data --listen 'postgres://0.0.0.0:7890,http://0.0.0.0:4318?api=otel,http://0.0.0.0:9200?api=es'

curl -u postgres:<password> -X PUT http://localhost:9200/app-logs \

-H 'Content-Type: application/json' -d '{"mappings": {"properties": {

"@timestamp": {"type": "date"}, "log": {"type": "text"},

"service": {"type": "keyword"}, "level": {"type": "keyword"}}}}'

curl -u postgres:<password> -X POST 'http://localhost:9200/app-logs/_bulk?refresh=true' \

-H 'Content-Type: application/x-ndjson' --data-binary @- <<'EOF'

{"create": {}}

{"@timestamp": "2026-11-03T09:14:07.120Z", "log": "upstream connect error: connection reset by peer (10.0.3.17:5432)", "service": "payments", "level": "ERROR", "kubernetes": {"pod_name": "payments-7f9c4d-x2lq", "namespace_name": "shop"}}

{"create": {}}

{"@timestamp": "2026-11-03T09:14:08Z", "log": "POST /api/v2/checkout 502 3012ms", "service": "gateway", "level": "WARN"}

{"create": {}}

{"@timestamp": "2026-11-03T09:14:11Z", "log": "healthcheck ok: connection reset count 0", "service": "auth", "level": "INFO"}

EOF

> stage 02 · search table

A search table

otel_logs is created WITH (storage = 'search'). A row becomes visible to every query at the next refresh, every second by default; VACUUM (REFRESH_TABLE) publishes it at once. The real-time analytics page covers the timing.

sql

VACUUM (REFRESH_TABLE) otel_logs;

SELECT count(*) FROM otel_logs;

-- 12

> stage 03 · inverted index

Log text keeps its identifiers

otel_body_dict splits on word boundaries and lowercases, with no stemming, so an address or a dotted class name is one token. A column with no dictionary holds each value as one term: = and @@ are index lookups, and ts_dict_agg counts the values from the term dictionary. trace_id is an exact term in otel_logs and in otel_traces, and the timestamp is indexed for ranges.

sql · hover a lane

-- otel_logs_idx, created with the table at startup:

-- body otel_body_dict

-- severity_text, severity_number, service_name, event_name

-- trace_id

-- "timestamp"

SELECT ts_lexize('otel_body_dict',

'upstream connect error: connection reset by peer (10.0.3.17:5432)');

-- {upstream,connect,error,connection,reset,by,peer,10.0.3.17,5432}

SELECT unnest(ts_dict_agg(severity_text)) AS severity,

unnest(ts_dict_count(severity_text)) AS n

FROM otel_logs;

-- INFO 4 · WARN 3 · ERROR 5, in no fixed order

> ingest · postgresql wire

INSERT and COPY over the PostgreSQL wire

Any PostgreSQL driver writes rows with INSERT, as step 2's SQL form does, or streams them with COPY … FROM STDIN. These six sshd lines answer COPY 6; the security example below reads them.

sql · text format, tab-separated

COPY otel_logs ("timestamp", severity_text, severity_number, service_name, body) FROM STDIN;

2026-11-03 09:20:01 WARN 13 sshd Failed password for invalid user admin from 203.0.113.7 port 52144 ssh2

2026-11-03 09:20:03 WARN 13 sshd Failed password for invalid user oracle from 203.0.113.7 port 52150 ssh2

2026-11-03 09:20:04 WARN 13 sshd Failed password for root from 198.51.100.23 port 40022 ssh2

2026-11-03 09:20:06 WARN 13 sshd Failed password for invalid user test from 203.0.113.7 port 52163 ssh2

2026-11-03 09:20:09 INFO 9 sshd Accepted publickey for deploy from 192.0.2.10 port 61022 ssh2

2026-11-03 09:20:12 WARN 13 sshd Failed password for invalid user admin from 203.0.113.9 port 33010 ssh2

> stage 04 · search

Log search with @@

websearch_to_tsquery takes quoted phrases and minus words. ts_regexp, ts_like and ts_starts_with test whole tokens, so ts_regexp('5[0-9][0-9]') finds the 502s but not port 5432, and a time window is an index predicate too. The full-text search page covers BM25 and analyzers in general.

sql

SELECT "timestamp", service_name, body

FROM otel_logs

WHERE body @@ 'upstream'

AND "timestamp" @@ ts_between(TIMESTAMP_NS '2026-11-03 09:14:00',

TIMESTAMP_NS '2026-11-03 09:15:00', true, false)

ORDER BY "timestamp" DESC LIMIT 3;

-- 09:14:15.61 checkout · 09:14:15.2 payments · 09:14:07.48 checkout

SELECT "timestamp"::TIME AS t, body

FROM otel_logs

WHERE body @@ ts_regexp('5[0-9][0-9]')

ORDER BY "timestamp";

-- 09:14:08 POST /api/v2/checkout 502 3012ms

-- 09:14:16 POST /api/v2/checkout 502 3008ms

> stage 05 · group

GROUP BY on the same rows

The rows a search returns group like any table: time_bucket for minutes, regexp_replace for patterns and regexp_extract for fields. This is step 4.

sql

SELECT time_bucket(INTERVAL '1 minute', "timestamp") AS minute,

service_name, count(*) AS errors

FROM otel_logs

WHERE severity_text = 'ERROR'

GROUP BY ALL ORDER BY ALL;

> stage 06 · schedule

Retention on a schedule

One CREATE JOB per table deletes the rows older than its period, in the form the OpenTelemetry docs give. EVERY runs on a calendar grid in UTC: this job at the start of every hour.

sql

CREATE JOB otel_logs_retention EVERY 1 HOUR AS

DELETE FROM otel_logs

WHERE "timestamp" @@ ts_lt(now()::TIMESTAMP_NS - INTERVAL '30 days');

EXECUTE JOB otel_logs_retention;

SELECT job_name, last_status FROM sdb_jobs();

-- otel_logs_retention | success

> stage 07 · clients

Any PostgreSQL client

psql, Grafana's PostgreSQL data source and the PostgreSQL drivers read plain rows on port 7890, and Grafana panels query it in SQL.

shell

psql -h localhost -p 7890 -U postgres

What log text needs from an index

otel_logs_idx

tokens

Identifiers stay whole

IPs and dotted names are single tokens, so a prefix finds a whole subnet: lines 3 and 7.

body @@ ts_starts_with('10.0.3.')

attributes

Attributes at index speed

A hot attribute path becomes an index expression in your own otel_logs_idx, and a term filter: lines 5 and 9.

(log_attributes ->> 'http.status_code') @@ '502'

substrings

Substrings, like grep -F

Sparse n-grams index any fragment, across token boundaries too: took=1840 finds line 10.

line @@ ts_all(ts_lexize('line_grams_q', 'took=1840'))

searchbench

A billion log rows

SearchBench, 92 queries over 1B OpenTelemetry log rows: all 92 under the 60 s cap, median 31.0 ms.

otel_logs_1b · 26.09.1

SearchBench on otel_logs_1b →

Build it

Tutorial: analyze OpenTelemetry logs in SQL, in five steps

From an empty server to one request traced across three services: start the OTLP listener, export twelve log records, then search them, count them and follow one trace. Every command and result below ran on SereneDB 26.11.2 on 2026-11-04.

step 01 / 05 · shell

docker run -d --name serenedb -p 7890:7890 -p 4318:4318 \
  -e POSTGRES_PASSWORD=<password> \
  --entrypoint serened serenedb/serenedb:26.11.2 /var/lib/serenedb \
  --listen='postgres://0.0.0.0:7890,http://0.0.0.0:4318?api=otel'

# on Linux, the serened binary instead:
# serened ./data --listen 'postgres://0.0.0.0:7890,http://0.0.0.0:4318?api=otel'

The log lists the seven otel_* tables it created and both listeners.

step 02 / 05 · shell

shell

curl -u postgres:<password> -H 'Content-Type: application/json' \
  --data @logs.json http://localhost:4318/v1/logs
# {}

psql -h localhost -p 7890 -U postgres -c 'VACUUM (REFRESH_TABLE) otel_logs' \
  -c 'SELECT count(*) FROM otel_logs'

{} and 12. Each export is one transaction; VACUUM (REFRESH_TABLE) publishes the rows now instead of at the next refresh. With the binary on the same host, postgres has no password on loopback: pass -u postgres: with nothing after the colon.

SQL

INSERT INTO otel_logs ("timestamp", trace_id, severity_text, severity_number,
                       service_name, body, log_attributes) VALUES
('2026-11-03 09:14:02',     NULL, 'INFO',   9, 'gateway',  'GET /api/v2/orders/8812 200 41ms', '{"http.status_code": 200}'),
('2026-11-03 09:14:05',     NULL, 'INFO',   9, 'checkout', 'order 8812 created for user 4821', '{}'),
('2026-11-03 09:14:07.120', '4bf92f3577b34da6a3ce929d0e0e4736', 'ERROR', 17, 'payments', 'upstream connect error: connection reset by peer (10.0.3.17:5432)', '{}'),
('2026-11-03 09:14:07.480', '4bf92f3577b34da6a3ce929d0e0e4736', 'ERROR', 17, 'checkout', 'payment failed for order 8812: upstream timeout after 3000ms', '{}'),
('2026-11-03 09:14:08',     '4bf92f3577b34da6a3ce929d0e0e4736', 'WARN',  13, 'gateway',  'POST /api/v2/checkout 502 3012ms', '{"http.status_code": 502}'),
('2026-11-03 09:14:11',     NULL, 'INFO',   9, 'auth',     'healthcheck ok: connection reset count 0', '{}'),
('2026-11-03 09:14:15.200', '0af7651916cd43dd8448eb211c80319c', 'ERROR', 17, 'payments', 'upstream connect error: connection reset by peer (10.0.3.17:5432)', '{}'),
('2026-11-03 09:14:15.610', '0af7651916cd43dd8448eb211c80319c', 'ERROR', 17, 'checkout', 'payment failed for order 8817: upstream timeout after 3000ms', '{}'),
('2026-11-03 09:14:16',     '0af7651916cd43dd8448eb211c80319c', 'WARN',  13, 'gateway',  'POST /api/v2/checkout 502 3008ms', '{"http.status_code": 502}'),
('2026-11-03 09:14:20',     NULL, 'WARN',  13, 'auth',     'token refresh slow: took=1840ms user=4821', '{}'),
('2026-11-03 09:15:02',     NULL, 'ERROR', 17, 'payments', 'java.net.SocketTimeoutException: Read timed out at io.grpc.ClientCalls.blockingUnaryCall', '{}'),
('2026-11-03 09:15:04',     NULL, 'INFO',   9, 'gateway',  'GET /healthz 200 2ms', '{"http.status_code": 200}');

VACUUM (REFRESH_TABLE) otel_logs;
SELECT count(*) FROM otel_logs;

INSERT 0 12, then 12. Steps 3 to 5 return the same rows as after the export.

step 03 / 05 · sql

SELECT "timestamp", service_name, body
FROM otel_logs
WHERE body @@ websearch_to_tsquery('"connection reset" -healthcheck')
ORDER BY "timestamp";

Two rows, both payments. The healthcheck line also holds connection reset; the minus drops it.

step 04 / 05 · sql

SELECT time_bucket(INTERVAL '1 minute', "timestamp") AS minute,
       service_name, count(*) AS errors
FROM otel_logs
WHERE severity_text = 'ERROR'
GROUP BY ALL ORDER BY ALL;

3 rows: 09:14 checkout 2 and payments 2, then 09:15 payments 1. The Java timeout came after the checkout errors stopped.

step 05 / 05 · sql

SELECT "timestamp", service_name, severity_text, body
FROM otel_logs
WHERE trace_id @@ '4bf92f3577b34da6a3ce929d0e0e4736'
ORDER BY "timestamp";

CREATE JOB otel_logs_retention EVERY 1 DAY AS
    DELETE FROM otel_logs
    WHERE "timestamp" @@ ts_lt(now()::TIMESTAMP_NS - INTERVAL '30 days');

Payments, checkout, gateway, within 880 ms. The job runs at midnight UTC; EXECUTE JOB runs it once now.

Use cases

What teams build on log analysis

Keep the shipper, query the logs in SQL

Log pipelines that post NDJSON to Elasticsearch's _bulk endpoint can post to SereneDB's es listener. Each index is a table in the es schema: match and match_phrase queries keep working over the REST API, and SQL groups the same documents, nested fields from _source included.

built with

_bulkes."app-logs"match_phrase

Full-text search →

example · the index in SQL

SELECT service, _source::JSON -> 'kubernetes' ->> 'pod_name' AS pod,
       count(*) AS hits
FROM es."app-logs$text"
WHERE log @@ websearch_to_tsquery('"connection reset" -healthcheck')
GROUP BY ALL;

result

service	pod	hits
payments	payments-7f9c4d-x2lq	1

The three documents from the _bulk stage above. The kubernetes object is not in the mapping, so it stays in _source and is read with ->.

JSON log files, loaded in one statement

Container runtimes and many services write one JSON object per line. read_json reads a glob of files into a search table, the keys become columns, and the message gets the same word-boundary analysis as OTel log bodies.

built with

read_jsonstorage = 'search'log_words

Data lake search →

example · files → table → search

CREATE TEXT SEARCH DICTIONARY log_words AS
    split_text(case := 'lower', break := 'alpha') WITH (frequency, position, norm);
CREATE TABLE app_logs WITH (storage = 'search') AS
    SELECT ts::TIMESTAMP AS ts, level, service, msg, peer, trace_id
    FROM read_json('/var/log/app/*.ndjson');
CREATE INDEX app_logs_idx ON app_logs
    USING inverted (ts, level, service, trace_id, msg log_words);
VACUUM (REFRESH_TABLE) app_logs;

SELECT ts, service, msg, peer FROM app_logs
WHERE msg @@ ts_phrase('connection reset') AND level = 'error';

result

ts	service	msg	peer
2026-11-03 09:14:07.12	payments	upstream connect error: connection reset by peer	10.0.3.17:5432

One file of three NDJSON lines; the error line matches. For files that stay in S3, see data lake search.

Failed logins by source IP

sshd writes the user and the address into free text. A phrase search finds the failures, regexp_extract pulls the address out of the line, and HAVING keeps the sources that tried more than once, while the lines are still arriving.

built with

ts_phraseregexp_extractHAVING

Real-time analytics →

example · repeat offenders

SELECT regexp_extract(body, 'from (\S+) port', 1) AS source_ip,
       count(*) AS failures,
       list_sort(list(DISTINCT
         regexp_extract(body, 'for (?:invalid user )?(\S+) from', 1))) AS users
FROM otel_logs
WHERE service_name = 'sshd' AND body @@ ts_phrase('failed password')
GROUP BY source_ip
HAVING count(*) >= 2
ORDER BY failures DESC;

result

source_ip	failures	users
203.0.113.7	3	{admin,oracle,test}

The six sshd lines from the COPY stage above: 198.51.100.23 and 203.0.113.9 failed once each. The addresses come from the documentation ranges.

FAQ

Frequently asked questions

Go to docs →

Log analysis is reading the logs that applications and systems write to find out what happened: searching log lines for errors, counting them by service and time, and following one request across services. Log management collects and keeps the logs; log analysis asks them questions. In SereneDB it is SQL over a log table, where the message is full-text searchable and the fields are columns.

Search the message for the symptom, filter by service, severity and time, group what matches to see where and when it happens, collapse repeating lines into patterns, and follow a trace id to the first failure. Each step is one SQL statement in the incident lab above.

The one that searches log text and counts it in the same step, reads logs where your shippers already send them, and stays fast at your volume; the checklist above lists the jobs. SereneDB matches a phrase, a pattern or a trace id with @@ and groups the matches with GROUP BY in one SQL statement. On SearchBench, 92 queries over a billion OpenTelemetry log rows across 11 engines in 17 configurations, SereneDB 26.09.1 answered all 92 within the 60-second cap, with a 31.0 ms median.

Send structured records with a service name, a severity and a trace id; keep the raw message searchable without stemming, so identifiers survive; index the fields you filter on; set a retention period per table; and query with the same SQL your dashboards use. The checklist above maps each one to SereneDB.

SereneDB is open source under the Apache-2.0 license, with its source on GitHub, and its Docker image runs with one command; the walkthrough above goes from that command to a traced request in five steps.

Start the server with an otel listener and point an OTel SDK or a collector's otlphttp exporter at it. Records land in otel_logs, created at startup, one transaction per export, and are searchable at the next refresh, which the real-time analytics page explains.

trace_id is an indexed exact column in otel_logs and in otel_traces: WHERE trace_id @@ '…' returns a request's log lines in time order, and the same id finds its spans. A prefix works when you only have the first characters.

Yes. websearch_to_tsquery takes quoted phrases and minus words; ts_regexp, ts_like and ts_starts_with match patterns against whole tokens; &&, || and !! combine them. Because patterns test tokens, ts_regexp('5[0-9][0-9]') matches status 502 but not port 5432. The full-text functions list every operator.

Inside a token, ts_like('%timeout%') already does it. For fragments that cross token boundaries, such as took=1840, index the text with a sparse n-gram dictionary: the index finds the candidate rows and LIKE confirms the match. The sparse n-grams post explains how.

Any log that is text plus fields: application logs, web and API access logs, auth logs such as sshd, and the system logs a shipper forwards. Each becomes rows in a table, so the same SQL searches and counts them all.

Ingestion that posts NDJSON to _bulk can post to SereneDB's es listener, and Query DSL match, match_phrase, term, range and bool keep working over the REST API. Create each index with its mapping first: fields are flat and documents are create-only. SQL then reads the same index; the Elasticsearch API docs list what is covered. SereneDB vs Elasticsearch and SereneDB vs OpenSearch map Query DSL to SQL.

> get started

Get started with SereneDB

Start the OTLP listener, export your logs, and search and count them in SQL from any PostgreSQL client.

Get Started OpenTelemetry docs →

> Search

> Analytics & data

> AI & agents

> Search engines

> Postgres & OLAP

> Vector

Log analysis: search the message and GROUP BY the fields in one query

How do you perform log analysis? Five questions over twelve log lines

What a log analysis tool needs, and how SereneDB does it in SQL

Log records land in a search table; one index covers the message, the fields and the trace ID

An OTLP/HTTP listener in the server

Elasticsearch-style _bulk

A search table

Log text keeps its identifiers

INSERT and COPY over the PostgreSQL wire

Log search with @@

GROUP BY on the same rows

Retention on a schedule

Any PostgreSQL client

What log text needs from an index

Tutorial: analyze OpenTelemetry logs in SQL, in five steps

What teams build on log analysis

Keep the shipper, query the logs in SQL

JSON log files, loaded in one statement

Failed logins by source IP

Frequently asked questions

Get started with SereneDB

Log analysis: search the message and GROUP BY the fields in one query

How do you perform log analysis? Five questions over twelve log lines

What a log analysis tool needs, and how SereneDB does it in SQL

Log records land in a search table; one index covers the message, the fields and the trace ID

An OTLP/HTTP listener in the server

Elasticsearch-style _bulk

A search table

Log text keeps its identifiers

INSERT and COPY over the PostgreSQL wire

Log search with @@

GROUP BY on the same rows

Retention on a schedule

Any PostgreSQL client

What log text needs from an index

Tutorial: analyze OpenTelemetry logs in SQL, in five steps

What teams build on log analysis

Keep the shipper, query the logs in SQL

JSON log files, loaded in one statement

Failed logins by source IP

Frequently asked questions

What is log analysis?

How do you perform log analysis?

Which tool is best for log analysis?

What are the best practices for log analysis?

Is SereneDB an open-source log analysis tool?

How do OpenTelemetry logs get into SereneDB?

How do I correlate logs with traces?

Can I search log messages by phrase, regex or wildcard?

Can I search for any substring, like grep -F?

What kinds of logs can I analyze this way?

Can I keep my Elasticsearch log pipeline?

Get started with SereneDB