Logs as Tables

engineering
observability
duckdb
infrastructure
Author

Mike McCourt

Published

August 20, 2026

How structured logs and DuckDB unlock OLAP without infrastructure.

Logs are usually treated as text.

They are tailed, grepped, and occasionally piped into increasingly elaborate stacks of collectors, indexers, shippers, and dashboards. But analyzing text is hard and, once traffic grows, the default answer often becomes: deploy more infrastructure. This approach can be surprisingly expensive if not managed carefully.

But we don’t see logs as text. We learned from telemere to see them as a time-series of structured events. Each event is a row; each measured attribute is a column. As time flows forward, events come in and patterns emerge.

In other words, we see logging fundamentally as a data science problem. We have found that, if you log this way from the start, you don’t need much in the way of infrastructure to analyze them.

Logs as Rows

For example, take a look at how we manage our Nginx access log. Instead of writing human-oriented text logs, we configure Nginx to emit JSON Lines: one structured event per line. A simplified version of our format looks like this:

log_format json_log escape=json
  '{'
    '"source":"nginx",'
    '"time":$msec,'                               # seconds.millis since epoch (number)
    '"time_iso":"$time_iso8601",'                 # ISO-8601 wall clock (string)
    '"request_id":"$request_id",'                 # unique ID per request (string)
    '"resp_time":$request_time,'                  # total time (seconds.millis)

    '"remote_addr":"$remote_addr",'

    '"method":"$request_method",'
    '"uri":"$request_uri",'
    '"status":$status,'                           # number
    '"host":"$host",'

    '"upstream_status":"$upstream_status",'       # string (nullable)
    '"upstream_resp_time":"$upstream_response_time"' # string (seconds.millis)
  '}';

We install that as 00-json_log.conf to ensure it is available to each of our Nginx configuration files:

sudo install -D -m 0644 "conf/json_log.conf" "$(NGXCONFD)/00-json_log.conf"

Admittedly, it is a little ugly to specify JSON inside an Nginx configuration file… the nested quoting is hard on the eyes.

However, once you specify this format, each line is valid JSON, each field has a predictable type, and the schema is consistent from one line to the next. That changes everything. Because once your logs are structured, the logs become a table.

One File, One Binary

DuckDB can read JSON files directly, and it’s stunningly fast:

SELECT *
FROM read_json_auto('nginx.access.log');

Because we are suckers for performance, we run a small periodic daemon to ingest the log and batch-append to a partitioned parquet file. This way, our analysis queries run against an optimized data table. However, DuckDB is fast enough that this is probably unnecessary; you could skip ingestion and simply query the log file.

Now the log file behaves like any other analytical dataset. This gives us all the log analysis we need:

p95 latency by endpoint

SELECT
  split_part(uri, '?', 1)          AS path, -- remove query params
  approx_quantile(resp_time, 0.95) AS p95_latency,
  COUNT(1)                         AS request_count
FROM read_json_auto('nginx.access.log')
GROUP BY path
HAVING request_count > 10  -- filter out low-traffic noise
ORDER BY p95_latency DESC
LIMIT 10;

Status code distribution

SELECT
  status,
  COUNT(1) AS requests,
  ROUND(COUNT(1) * 100.0 / SUM(COUNT(1)) OVER (), 2) AS percentage
FROM read_json_auto('nginx.access.log')
GROUP BY status
ORDER BY requests DESC;

Upstream failure rate per minute for the last 10 minutes

WITH logs AS (
  SELECT
    to_timestamp(time) AS ts,
    -- Extract the final status in the chain if there were retries (e.g., "502, 504")
    TRY_CAST(list_extract(string_split(upstream_status, ', '), -1) AS INTEGER) AS upstream_status_i
  FROM read_json_auto('nginx.access.log')
  -- Push down the filter using the numeric epoch time before parsing anything
  WHERE time >= epoch(now()) - 600
)
SELECT
  date_trunc('minute', ts) AS minute,
  COUNT(*) FILTER (WHERE upstream_status_i >= 500)::DOUBLE
    / NULLIF(COUNT(*) FILTER (WHERE upstream_status_i IS NOT NULL), 0) AS upstream_error_rate,
  COUNT(*) FILTER (WHERE upstream_status_i IS NOT NULL) AS upstream_requests,
  COUNT(*) FILTER (WHERE upstream_status_i >= 500) AS upstream_5xx
FROM logs
GROUP BY minute
ORDER BY minute DESC;

Slowest routes (median response time)

SELECT
  split_part(uri, '?', 1) AS path,
  median(resp_time) AS median_latency,
  approx_quantile(resp_time, 0.99) AS p99_latency,
  COUNT(*) AS request_count
FROM read_json_auto('nginx.access.log')
GROUP BY path
HAVING request_count > 10
ORDER BY median_latency DESC
LIMIT 10;

Bots and scanners

Our server runs behind Cloudflare; any valid traffic should hit our domain name. Requests coming directly to our server’s IP address are attempting to bypass Cloudflare. This is the “background radiation” of the internet.

To see exactly which exploits are trending right now, we can group the requests by URI and count the unique IP addresses attempting them.

SELECT
  uri,
  COUNT(*) AS probe_count,
  COUNT(DISTINCT remote_addr) AS unique_scanners
FROM read_json_auto('nginx.access.log')
WHERE host NOT LIKE '%sturdystatistics.com%'
  AND host IS NOT NULL
GROUP BY uri
ORDER BY unique_scanners DESC
LIMIT 15;

Some bots do a single drive-by scan; others will hammer our IP for days trying different permutations of exploits. We can aggregate the most aggressive actors:

SELECT
  remote_addr,
  COUNT(*)            AS total_probes,
  COUNT(DISTINCT uri) AS unique_paths_tried,
  MIN(time_iso)       AS first_seen,
  MAX(time_iso)       AS last_seen
FROM read_json_auto('nginx.access.log')
WHERE host NOT LIKE '%sturdystatistics.com%'
GROUP BY remote_addr
HAVING total_probes > 10
ORDER BY total_probes DESC
LIMIT 10;

Of course, it’s best to configure Nginx and your firewall to drop these requests at the boundary, so they never reach the application layer. If you haven’t done so, this exercise will make you want to!

Process the entire history

In these example queries, we process a single file: FROM read_json_auto('nginx.access.log').

If logrotate rotates your Nginx logs (e. g., nginx.access.log, nginx.access.log-20260219.gz, nginx.access.log-20260220.gz, etc.), DuckDB can effortlessly query across all of them at once, including the compressed ones. Simply use a glob pattern like this to query weeks of history instantly:

FROM read_json_auto('/var/log/nginx/*access.log*')

No Infrastructure Required

This is OLAP, on a file. There is no:

  • Elasticsearch cluster
  • Kafka pipeline
  • Logstash configuration
  • Kubernetes deployment
  • SaaS observability bill

All we need is a clean schema, a flat file, and DuckDB.

And because DuckDB is columnar and vectorized, even fairly large log files remain fast to query on a single machine. It provides production observability and infrastructure independence without introducing operational surface area.

For many startups – especially in early stages – this should be more than enough.

The Broader Pattern

The point I’m trying to make is more about structure in general than about logging in particular. If you impose structure early and select the right tool, analysis becomes cheap, and questions become easy to answer. This approach lets you meet your operational needs while maintaining cost discipline and without incurring a technical maintenance burden.

We apply this same principle throughout our system:

  • In using make to express deploys as dependency graphs.
  • In using systemd sandboxing to declare containment boundaries.
  • In preferring SQL over ad-hoc scripts.
  • In avoiding black-box systems when an explicit abstraction is more appropriate.

Sometimes, a single well-chosen tool is better than an entire stack.