A deep look at OBSERVE's SQL-style log search: field indexing, aggregation functions, trace correlation, and how it differs from keyword search—plus where its costs and limits live.
Grep-style keyword search is fine when log volume is small. At hundreds of gigabytes per day across thousands of rotating containers, it breaks down in three ways: a keyword match returns tens of thousands of lines with no way to filter by field; there is no way to aggregate; and every query re-scans raw text, so latency climbs with volume. What operators actually want is "group error-level logs by service and count them for the last hour", or "list every log tied to a traceId in time order". That is a query, not a search. Say a release just went out and the error rate spiked: with keyword search you would grep "error" across every service and page through thousands of lines to find the culprit; with SQL you write one GROUP BY and get the answer as a table in under a second.
OBSERVE's search layer ships with a built-in SQL-like query engine. Logs are parsed into fields at ingest time: structured logs are split by key automatically, and unstructured logs are matched against regex templates you define. Search therefore becomes an index lookup over fields rather than line-by-line matching of raw text.
Queries support SELECT / WHERE / GROUP BY / ORDER BY / LIMIT, plus the aggregate functions count, sum, avg, percentile, and histogram. A few high-frequency patterns:
-- Count error logs by service for the last hour
SELECT service, count(*) AS cnt
FROM logs
WHERE level = 'error' AND time > now() - 1h
GROUP BY service ORDER BY cnt DESC
-- P99 latency of an endpoint in 1-minute buckets
SELECT histogram(time, 1m) AS bucket, percentile(latency, 99)
FROM logs WHERE api = '/order/create'
GROUP BY bucketWHERE conditions are pushed down to index lookups, and only the matching time shards get scanned—typically one to two orders of magnitude faster than a full grep over the same window. Field values get inverted indexes by default; numeric fields additionally get columnar storage, so aggregations run as columnar scans instead of walking every row.
Once logs carry traceId and spanId, the engine supports cross-table joins. To debug a slow request, take the traceId from the trace view and pull every span's log lines in a single statement:
SELECT time, service, span_id, message
FROM logs WHERE trace_id = 'e8f2...' ORDER BY timeA traditional log system would need several searches and manual stitching to do this. One statement gets it done, which turns incident triage from "flipping through a dozen pages of logs" into "one query, one ordered result set". The same pattern works for correlating logs with deploy events or with specific users.
SQL search is not a free lunch. Indexing every field inflates storage and ingest cost, so only common fields (level, service, trace_id, timestamp) are indexed by default; the rest can be enabled per field in the schema. Fuzzy matching with LIKE '%xxx%' still degrades to a scan regardless of indexes—for broad fuzzy queries, prefer the scheduled aggregations inside alert rules, which run periodically and cache results. Aggregation results also cap at 10,000 rows by default; when you hit the ceiling, tighten the WHERE clause instead of paging.
The short version: treat it like a database, but know which queries are expensive. Push the expensive ones to scheduled jobs, and keep interactive queries narrow in range and short in time window. That discipline is what keeps the search layer fast under load.