SQL-Like Log Search: Syntax, Indexing and Performance, Explained

Searching logs is the single most frequent operation in debugging, yet many teams never get it right — queries are either slow or gated behind a steep syntax. This article walks through Observe's SQL-like log search: what it supports, how it stays fast, and which ready-made queries you can copy.

Why we dropped the Lucene syntax

Half the time you spend querying logs goes into recalling the syntax, not the query itself. Lucene's grammar, Kibana's query DSL, each cloud vendor's proprietary dialect — they all differ. The same query, "error logs from the last hour", looks like level:ERROR AND @timestamp:[now-1h TO now] in one tool and a nested JSON bool filter in another. Every project switch means relearning the syntax, new hires need a dedicated onboarding session, and mid-incident is the worst possible time to be reading docs.

Observe collapses log search down to SQL. If you can write SELECT ... WHERE ..., you can query logs. Developers, testers and operators get productive on day one. The reasoning behind this design is simple: log search should be a tool everyone on the team can reach for, not a craft owned by the few people who happen to remember the dialect.

Supported syntax and functions

The SQL-like search covers the clauses that handle over 90% of debugging scenarios:

SELECT level, count(*) AS cnt
FROM logs
WHERE service = 'order-service'
  AND level IN ('ERROR', 'WARN')
  AND ts > now() - 30m
GROUP BY level
ORDER BY cnt DESC
LIMIT 20

• WHERE: equality, ranges, IN, LIKE, regular expressions
• GROUP BY with aggregates: count, avg, max, min, percentile
• Time functions: now(), date_trunc, time arithmetic
• ORDER BY / LIMIT: ordering and pagination

Field names line up with the structured fields your collector already emits. level, service, trace_id and ts are queryable out of the box — no Grok patterns to maintain, no schema migration when a service starts emitting a new field. The percentile aggregate deserves a special mention: percentile(duration, 99) gives you P99 latency in one statement instead of exporting samples to a spreadsheet first. And if you don't feel like writing SQL at all, type "show me the errors from order-service in the last half hour" and the query is generated for you — with the SQL shown alongside, so you learn as you go.

Indexing and the performance trade-off

When log volume is high, indexing strategy drives both query speed and storage cost, and the two pull against each other. Index too much and your storage bill balloons; index too little and every query degrades into a full scan. By default Observe indexes the high-frequency filter fields — service, level, trace_id, host — runs full-text search over the message body, and tiers hot and cold data separately so the last few days answer instantly while older data stays cheap. Deciding which fields to index is driven by your actual query habits: index the fields that keep appearing in WHERE clauses, and leave the occasional ones to full-text search.

The practical rule is: narrow with an indexed field first, then let full-text finish the job. Searching service = 'order-service' is fast because it hits an index; scanning every message for a substring is what you do only once the range is already small. One concrete tip: make trace_id a high-cardinality indexed field. During trace debugging, filtering by trace_id can be an order of magnitude faster than a full-text scan, because the index points you straight at the handful of lines that belong to one request.

Three ready-to-use query templates

Find the error spike:

SELECT date_trunc('5m', ts) AS bucket, count(*) AS cnt
FROM logs WHERE level = 'ERROR' AND ts > now() - 1h
GROUP BY bucket ORDER BY bucket

Top N slow requests:

SELECT path, avg(duration) AS avg_ms, count(*) AS cnt
FROM logs WHERE service = 'api-gateway' AND ts > now() - 1h
GROUP BY path ORDER BY avg_ms DESC LIMIT 10

Pull every log for one trace:

SELECT * FROM logs WHERE trace_id = 'a1b2c3d4e5f6' ORDER BY ts

Save the queries you run often as templates, and during an incident you get results in one click instead of rewriting them from memory. Templates can be shared across the whole team, and you can attach one to an alert so the on-call engineer opens the notification and lands straight on the live view. Over time that library becomes your team's own troubleshooting playbook — the patterns that keep recurring at your company, already written down.