Nov 04, 2026

DuckDB Query Metrics and Monitoring

DuckDB

DuckDB exposes a set of query metrics through its built-in profiler. These metrics trace every stage of query execution — from parsing and planning through operator execution to I/O and memory allocation. Understanding what each metric measures, how they relate to each other, and what ratios to watch gives you a diagnostic framework for identifying bottlenecks without guessing. This post covers the eight core metrics, the additional phase metrics, and how to use them together.

Enabling Metrics

DuckDB provides two ways to enable profiling:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- Enable all metrics at once
PRAGMA enable_profiling = 'json';
PRAGMA profiling_mode = 'detailed';

-- Or enable specific metrics
PRAGMA configure_profiling = '{
  "CPU_TIME":                   "true",
  "LATENCY":                    "true",
  "CUMULATIVE_ROWS_SCANNED":    "true",
  "CUMULATIVE_CARDINALITY":     "true",
  "TOTAL_BYTES_READ":           "true",
  "TOTAL_MEMORY_ALLOCATED":     "true",
  "SYSTEM_PEAK_BUFFER_MEMORY":  "true",
  "SYSTEM_PEAK_TEMP_DIR_SIZE":  "true"
}';

The profiler writes JSON after every query. Point it to a file, then read the results:

1
2
3
4
5
6
7
8
9
10

PRAGMA profiling_output = '/tmp/metrics.json';

SELECT sensor_id, AVG(temperature), COUNT(*)
FROM measurements
WHERE temperature > 50
GROUP BY sensor_id
ORDER BY COUNT(*) DESC
LIMIT 10;

SELECT * FROM read_json('/tmp/metrics.json');

The Core Eight Metrics

latency and cpu_time

latency is wall-clock time from query start to finish, in seconds. It includes everything — parsing, planning, optimization, and execution.

cpu_time is the sum of wall-clock time spent inside physical operators only. It excludes parsing, planning, and optimization overhead.

The relationship between these two reveals the query’s character:

Ratio Meaning
cpu_time / latency > 1.0 Effective parallelism — multiple threads each contribute operator time, so the sum exceeds wall-clock
cpu_time / latency close to 1.0 Single-threaded or perfectly serialized execution
cpu_time / latency < 1.0 Overhead dominates — parsing, planning, I/O wait, or thread scheduling

For a trivial query like SELECT 1 + 1, latency is small but cpu_time is near zero. The gap is fixed overhead. For a heavy parallel aggregation on millions of rows, cpu_time can exceed latency because it sums across all threads.

cumulative_rows_scanned and cumulative_cardinality

cumulative_rows_scanned counts the total number of rows read from storage across all scan operators. Filtered-out rows are still counted — they were scanned.

cumulative_cardinality counts the total number of intermediate rows produced across all operators in the plan. Every operator’s output contributes — not just scans.

Comparing the two reveals data amplification or reduction:

1
2
3
4
5
6
7
8
9
10
11
12

-- Setup
CREATE TABLE orders AS
    SELECT i AS order_id, (i % 10000) AS customer_id, random() * 500 AS amount
    FROM range(0, 1000000) t(i);

CREATE TABLE customers AS
    SELECT i AS customer_id, 'cust_' || i AS name
    FROM range(0, 10000) t(i);

-- A well-filtered join: cardinality ≈ rows_scanned
PRAGMA profiling_output = '/tmp/join_metrics.json';
SELECT count(*) FROM orders JOIN customers USING (customer_id);

If cardinality >> rows_scanned, the query plan is amplifying data (cross joins, many-to-many joins). If cardinality << rows_scanned, filters are effective.

A WHERE clause does not reduce rows_scanned — all rows must be scanned to evaluate the filter. But it does reduce cardinality because fewer rows flow through downstream operators.

total_bytes_read

Total bytes read from the filesystem during the query. DuckDB intercepts all file reads at the FileHandle level, so this captures reads from any filesystem — local disk, S3, httpfs, Azure.

Because DuckDB uses columnar storage, reading fewer columns from a wide table significantly reduces bytes_read while rows_scanned stays the same:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE wide AS
    SELECT i AS id,
           random() AS c1, random() AS c2, random() AS c3, random() AS c4,
           random() AS c5, random() AS c6, random() AS c7, random() AS c8,
           repeat('payload-', 20) AS text_col
    FROM range(0, 1000000) t(i);

-- Reading all columns
PRAGMA profiling_output = '/tmp/wide_all.json';
SELECT * FROM wide LIMIT 100000;

-- Reading one column
PRAGMA profiling_output = '/tmp/wide_one.json';
SELECT c1 FROM wide LIMIT 100000;

The second query reads far fewer bytes from storage. This is why columnar databases excel at analytical queries that touch few columns from wide tables.

total_memory_allocated, system_peak_buffer_memory, and system_peak_temp_dir_size

total_memory_allocated is the cumulative bytes allocated through the buffer manager during the query. This is a running total, not a high-water mark.

system_peak_buffer_memory is the highest instantaneous buffer pool usage observed during the query. It is sampled after every operator batch, so the true peak could be slightly higher between samples.

system_peak_temp_dir_size is the highest instantaneous size of on-disk temporary files. If this is non-zero, the query spilled to disk.

These three metrics together tell you about memory pressure:

1
2
3
4
5
6
7
8
9
10
11

-- Streaming aggregation: low peak memory (one accumulator)
PRAGMA profiling_output = '/tmp/mem_stream.json';
SELECT sum(val) FROM big;

-- Hash aggregation: moderate peak memory (hash table per group)
PRAGMA profiling_output = '/tmp/mem_hash.json';
SELECT group_id, sum(val) FROM big GROUP BY group_id;

-- ORDER BY: highest peak memory (full materialization)
PRAGMA profiling_output = '/tmp/mem_sort.json';
SELECT * FROM big ORDER BY val;

Streaming operators (filter, projection, simple aggregates) are narrow — they process data in batches without accumulation. Blocking operators (sort, hash join, hash group by) are wide — they must hold state proportional to the input or number of groups.

When system_peak_buffer_memory approaches memory_limit, the query was close to spilling. When system_peak_temp_dir_size is non-zero, it already did.

Additional Metrics

Phase Timers

The profiler breaks down overhead into parser, planner, and optimizer phases:

  • parser.total_time — time spent parsing SQL into an AST. Almost never significant.
  • planner.total_time — time generating the logical plan from the AST.
  • planner.binding_time — time resolving names and types against the catalog. This is a subset of planner time.
  • optimizer.total_time — time applying rule-based and cost-based transformations. Becomes significant for multi-way joins where the join order optimizer explores a factorial search space.

Write-Path Metrics

  • blocked_thread_time — time threads spent waiting for a scheduling slot in the parallel executor. High values mean thread over-provisioning.
  • total_bytes_written — total bytes written to the filesystem. For write queries (INSERT, COPY TO), this tells you how much data was produced.
  • write_to_wal_latency — time spent writing to the Write-Ahead Log during transaction commit.
  • checkpoint_latency — time spent running a checkpoint, flushing dirty data from memory to the database file.
  • commit_local_storage_latency — time spent converting transaction-local changes to persistent format. Runs before the WAL write.

Diagnosing a Slow Query

The metrics form a diagnostic checklist:

Question Check
Is it I/O bound? cpu_time / latency ratio — low ratio means I/O or overhead dominates
Is it reading too much? rows_scanned vs expected rows, bytes_read
Is it spilling? peak_temp_dir_size > 0
Memory pressure? peak_buffer_memory near memory_limit
Data amplification? cardinality / rows_scanned ratio
Thread contention? blocked_thread_time

Apply fixes in order and re-profile after each to see which metrics improve:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SET memory_limit = '100MB';

-- Baseline: slow query
PRAGMA profiling_output = '/tmp/slow.json';
SELECT * FROM big ORDER BY val;

-- Fix 1: column pruning (reduces bytes_read, peak_buffer_memory)
PRAGMA profiling_output = '/tmp/fix1.json';
SELECT id, val FROM big ORDER BY val;

-- Fix 2: add filter (reduces cardinality, less data to sort)
PRAGMA profiling_output = '/tmp/fix2.json';
SELECT id, val FROM big WHERE group_id < 100 ORDER BY val;

-- Fix 3: add LIMIT (reduces output, but ORDER BY still prevents scan pushdown)
PRAGMA profiling_output = '/tmp/fix3.json';
SELECT id, val FROM big WHERE group_id < 100 ORDER BY val LIMIT 1000;

Column pruning typically has the biggest impact on bytes_read. Filtering reduces cardinality and sort work. LIMIT only reduces rows_scanned when there is no blocking operator (ORDER BY, GROUP BY) between the scan and the limit — verify with the rows_scanned metric.

The Full Decomposition

For any query, latency should approximately equal the sum of its components:

1
2
3
4
5
6

latency ≈ parser.total_time
        + planner.total_time
        + optimizer.total_time
        + cpu_time
        + blocked_thread_time
        + unaccounted (scheduling, result materialization, overhead)

For write queries, add commit_local_storage_latency and write_to_wal_latency.

Use jq to extract the decomposition:

1
2
3
4
5
6
7
8
9
10
11
12

jq '{
  latency:     .latency,
  parser:      .parser_total_time,
  planner:     .planner_total_time,
  binding:     .planner_binding_time,
  optimizer:   .optimizer_total_time,
  cpu_time:    .cpu_time,
  blocked:     .blocked_thread_time,
  unaccounted: (.latency - .cpu_time - .blocked_thread_time
               - .parser_total_time - .planner_total_time
               - .optimizer_total_time)
}' /tmp/metrics.json

Typical profiles by query type:

Query type Dominant component
Read query cpu_time (70-95% of latency)
Write query cpu_time + commit + wal_write
Tiny query Unaccounted overhead (fixed cost exceeds variable cost)
Remote storage (DuckLake) cpu_time is a small fraction; binding and I/O wait dominate

Once you know which component dominates, you know where to optimize. High cpu_time means optimize the query plan. High blocked_thread_time means reduce thread count. High planner.binding_time means simplify schema or co-locate metadata. Non-zero peak_temp_dir_size means increase memory or reduce data volume.

Categories
Android android Angular angular AWS aws Azure azure Bootstrap bootstrap C Language c language Chrome chrome CSharp csharp Cypress cypress DBeaver dbeaver Docker docker DotNetCore dotnetcore DuckDB duckdb Ethereum ethereum Figma figma Flask flask FSharp fsharp Gimp gimp Git git GitLab gitlab Go go GraphQL graphql HashiCorp hashicorp iOS ios Jekyll jekyll Jenkins jenkins JavaScript js Kafka kafka Let's Encrypt letsencrypt Mathematics maths Metamask metamask Mobx mobx MySQL mysql NGINX nginx NGRX ngrx NPM npm OAS 3.0 oas OIDC oidc Microsoft Orleans orleans PostgreSQL postgres postman postman PrimeNG primeng prometheus prometheus PlantUML puml Python python Racket racket React react Solidity solidity Splunk splunk SQLAlchemy sqlalchemy SQLite sqlite Typescript typescript Ubuntu ubuntu Unicode unicode VSCode vscode Xamarin xamarin Oh My Zsh zsh