P 10 Chart Downsampling

P-10: SQL-side downsampling for charts vs client-side

Context: A line chart over a large result can't draw every row: ECharts would have to keep and paint a million points, and every hover would walk them. Since the charts milestone (#127), the engine reduces a line chart to at most 1,000 points (LINE_POINTS) before anything reaches the main thread. It splits the x range into 1,000 equal buckets and aggregates y in each. A common alternative is to fetch the raw series and reduce it in the browser with LTTB (largest-triangle-three-buckets), which keeps the points that shape the line, such as single spikes, instead of averaging them away. This experiment measures what that costs.

Hypothesis: A line chart of a million-point series stays smooth (drawn without long frames, and responsive while hovered) only if it is downsampled in the engine. Moving the raw series to the main thread and running LTTB there adds a long frame and slows the first draw.

Change: #31.

• Flag: ?downsample=client in the workbench's URL switches line charts to client-side downsampling. The page reads it once. Without it, or with any other value, charts downsample in SQL as before.
• Client path: lineSeriesSql() in src/features/charts/chart-sql.ts returns x and y of every row that has both, ordered by x, capped at 1,000,000 rows (RAW_LINE_POINTS). ChartPanel reads the two Arrow columns as typed arrays and reduces them to 1,000 points with lttb() (src/features/charts/lttb.ts, a pure function with unit tests). Each row is a point, so the aggregate (avg, sum, …) doesn't apply. A line chart of count, which has no y, still downsamples in SQL.
• Measure: blinkql:chart-render (src/lib/user-timing.ts) runs from choosing a chart's settings until the frame after ECharts has drawn it. It includes the chart's query, any downsampling on the main thread and the drawing.

Method:

• Scenarios: chart-line-1m-sql and chart-line-1m-client, added for this experiment (bench/scenarios/chart-line.ts). Each iteration opens the workbench, with ?downsample=client for the client variant, and runs SELECT range AS x, sin(range / 1000.0) + random() * 0.1 AS y FROM range(1000000). It then opens the Chart view and picks avg of y on the bar chart. It switches to Line and records chartRenderMs and the longest animation frame until then (chartLongestFrameMs). Finally it moves the pointer across the chart for 2 seconds, which moves the tooltip and axis pointer, and records the time between requestAnimationFrame callbacks.
• Build: main at f20efd4 with this change uncommitted, so the result files are flagged dirty. Both variants are the same build; only the URL differs. It was served by vite preview with production headers.
• Interleaved, one session: pnpm bench --scenario chart-line-1m-sql --scenario chart-line-1m-client, run twice: sql, client, sql, client, within about 12 minutes.
• Iterations: 3 warm-up and 10 measured iterations per round, two rounds per variant: 20 measured iterations each. Median and p95 are computed over the 20 pooled samples.
• Environment: the reference laptop from P-01 (Intel Core i5-8250U, 24 GB, Windows 11 Pro 10.0.26300), with other apps open. Chromium 156, headless. Nothing else heavy ran during the benchmark, but the second round was slower than the first for both variants (see Limitations).

All times are in milliseconds, given as median / p95.

Drawing and hovering a million-point line chart

Metric	SQL (default)	Client (LTTB)	Change (median)
chartRenderMs	560 / 817	567 / 1,017	+1%
chartLongestFrameMs	0 / 3	77 / 149	from none to 77
hoverFrameMedianMs	23.4 / 53.7	16.7 / 53.7
hoverFrameP95Ms	55.1 / 80.0	53.4 / 66.8
hoverSlowFrames (count, > 25 ms)	30 / 37	32.5 / 39
hoverLongestFrameMs	76.7 / 95.9	69.3 / 105.9

Range (min to max)	SQL	Client
chartRenderMs	404 to 925	400 to 1,067
chartLongestFrameMs	0 to 55	54 to 222

Per round, chartRenderMs medians were 488 and 680 ms (SQL) and 448 and 721 ms (client).

Findings

1. Both strategies draw the chart in about the same time. The medians are 560 and 567 ms, and each strategy was faster in one round. The SQL query buckets a million rows and returns 1,000. The client query only orders rows that are already ordered and returns all of them, about 16 MB of Arrow, which is fast to transfer. The engine's extra work and the transfer cost about the same.
2. Client-side downsampling blocks the main thread; SQL-side doesn't. With SQL, no frame took 50 ms or more in 19 of 20 iterations. With LTTB, every iteration had a long frame: 77 ms median, 149 ms p95, up to 222 ms. Reading two million values out of Arrow and running LTTB over them happens in one task, so the page doesn't respond to input during it.
3. Hovering is no different. Both strategies hand ECharts 1,000 points, so the drawn chart is the same size and the tooltip costs the same. The frame-time differences are within the run-to-run noise. About 30 frames in 2 seconds were slower than 25 ms in both, which is the tooltip and axis pointer redrawing, not the downsampling.
4. The hypothesis held for responsiveness, not for speed. Client-side downsampling isn't slower to the first draw, but it adds a long frame that SQL-side downsampling avoids, and it gains nothing once the chart is drawn.

Trade-offs:

• Averaging hides spikes. SQL buckets average y, so a single outlier in a bucket of 1,000 rows is flattened. LTTB keeps it. A chart whose purpose is spotting rare spikes would draw them better with LTTB, or with a min and max per bucket in SQL, which keeps spikes without moving the raw rows.
• The client path is capped. It fetches at most 1,000,000 rows, so on a larger result it charts only the first million by x. SQL-side bucketing covers every row of any size of result.
• Memory. The client path holds the raw series on the main thread while it reduces it; SQL-side keeps it in the engine's worker.

Limitations:

• One series, Chromium only. A smooth sine with noise, x already ordered and evenly spaced. An unordered or unevenly spaced x would make the client query sort, and buckets of unequal counts would change the SQL query's cost.
• Hover frame rate is approximate. The pointer is moved by Playwright about every 10 ms, so input arrives less evenly than from a real mouse, and headless Chromium draws at 75 Hz (13.3 ms a frame).
• Background load. Run on the working laptop with other apps open. Both variants were about 40% slower in the second round than in the first, so the pooled p95s are wide. The long-frame difference held in every iteration of both rounds.

Evidence:

• Raw results in bench/results: 2026-10-25T14-00-46Z-f20efd4.json (round 1) and 2026-10-25T14-06-33Z-f20efd4.json (round 2), each with both variants.
• To reproduce: run pnpm bench --scenario chart-line-1m-sql --scenario chart-line-1m-client. To try the client path by hand, open /app/?downsample=client.

Conclusion: Keep SQL-side downsampling as the default. It draws a million-point line chart as fast as client-side LTTB without blocking the main thread, it isn't capped at a million rows, and hovering is the same either way. The ?downsample=client flag stays so the comparison can be rerun. If spikes need to survive, a min and max per bucket in SQL is the next thing to try.