Query Logs with SQL: Treating Massive Logs as Data Tables

When something breaks, the first instinct is to search logs: type a keyword, pick a time range, page through results. That flow is fine at low volume. At scale it hits a wall.

• A keyword returns tens of thousands of lines, and you’re stuck paging through them instead of getting an answer like “how many hosts logged the same error.”
• Filtering by three conditions at once (error code + service + latency) means stacking keywords, and it’s never clear whether the logic is AND or OR.
• Aggregations like “error rate per minute” or “slow requests by region” simply can’t be done—you have to export the logs and compute them elsewhere.
• Statistics like the 99th-percentile latency have no meaning in a keyword search at all.

The root cause: logs are being treated as text, not data. Full-text search is great at finding, but it can’t answer “how many,” “how it’s distributed,” or “what the trend is.”

Observe makes log search SQL-based for exactly this reason—so logs can be what they actually are: timestamped, structured records you query like a table.

Field parsing: the prerequisite for SQL

SQL over logs only works if logs have fields. The Observe collector does two things at ingestion:

1. Automatic parsing: JSON logs are split into fields, so {"level":"ERROR","service":"pay","cost_ms":1523} becomes level, service, and cost_ms.
2. Custom parsing: for plain-text logs, configure a grok/regex template to pull key segments out into fields. Take a typical Nginx access line:

172.16.0.10 - - [12/Sep/2026:10:23:41 +0800] "GET /api/order?id=123 HTTP/1.1" 500 812

One grok template extracts client_ip, method, uri, status, and body_bytes, after which you can aggregate by status or uri.

Once fields exist, a search turns from ERROR AND pay AND cost_ms > 1000 into:

SELECT host, COUNT(*) AS cnt
FROM logs
WHERE level = 'ERROR'
  AND service = 'pay'
  AND cost_ms > 1000
  AND ts BETWEEN '2026-09-12 10:00:00' AND '2026-09-12 11:00:00'
GROUP BY host
ORDER BY cnt DESC

Query patterns worth keeping

A few templates that cover most on-call scenarios.

Error rate trend

SELECT DATE_FORMAT(ts, '%Y-%m-%d %H:%i') AS minute,
       SUM(level = 'ERROR') / COUNT(*) AS err_rate
FROM logs
WHERE service = 'order' AND ts > NOW() - INTERVAL 1 HOUR
GROUP BY minute

Top 10 slow requests

SELECT trace_id, uri, cost_ms
FROM logs
WHERE cost_ms > 2000
ORDER BY cost_ms DESC
LIMIT 10

Per-region stats

SELECT region, COUNT(*) AS total, AVG(cost_ms) AS avg_cost
FROM logs
WHERE ts > NOW() - INTERVAL 1 DAY
GROUP BY region

Latency percentiles (finding the long tail)

SELECT region,
       PERCENTILE(cost_ms, 0.5) AS p50,
       PERCENTILE(cost_ms, 0.99) AS p99
FROM logs
WHERE service = 'pay' AND ts > NOW() - INTERVAL 1 HOUR
GROUP BY region

Queries can be saved as views and attached to alert rules for one-click reuse. Common queries become shared views the whole team calls up instead of rewriting every time.

How performance holds up

The biggest worry with SQL over logs is whether aggregation will drag the system down. Observe handles this in three layers.

1. Time partitioning: logs are stored in hourly/daily shards. Queries only scan the shards that match the time range; the ts predicate is pushed down as partition pruning.
2. Columnar storage + compression: fields are stored column-by-column, so aggregation only reads the columns it needs. A COUNT(*) never decompresses full rows.
3. Query budgets: every query has a cost limit. If it times out or exceeds memory, it’s interrupted with a message instead of taking down the cluster.

In practice, on a billion-row store, a single-table GROUP BY aggregation runs with a P95 under 2 seconds. A full-table scan with no time filter is blocked by the optimizer with a “please add a time range” warning.

Wired into alerts and tracing

SQL search isn’t an island. It connects to two other capabilities.

• Save a query as an alert, turning its WHERE clause into a trigger condition. Thresholds, silences, and notification channels reuse the existing alerting system.
• Logs that carry a trace_id link straight into the trace view, so you can see the full call chain around that line.

A few usage tips

• Build fields before writing SQL: without fields, SQL can only LIKE against raw text—slow and useless. Spending half an hour extracting key fields at onboarding pays off later.
• Aggregate before pulling detail: for “where are the errors” questions, prefer GROUP BY results over dumping tens of thousands of raw lines.
• Always scope by time: set a time window on every query—it’s both a performance requirement and protection against stale data misleading you.
• Standardize field names: agree on names like level, service, and cost_ms across the team, or every service will emit different fields and cross-service queries become impossible. Define a field dictionary at onboarding and extract against it.

For the people on call, the value is simple: troubleshooting goes from “searching for a log line” to “asking a question and getting an answer.”