[Part 2] Plotting a 100k-Point Scatter Plot with a Custom Grafana Panel — 3 Pitfalls Revealed After Building It

Existing Grafana panels could not render a 100k-point scatter plot or retrieve accurate data, necessitating the development of a custom plugin. This section introduces three major issues discovered during the process and their solutions.

1. Rendering Performance Optimization SVG suffered from freezing due to DOM layout calculations with over 50,000 elements, so Canvas was used. Separating the point canvas and overlay canvas prevented lag during dragging, and grouping by color and shape combinations reduced the number of fillStyle switches. Additionally, the Y-axis upper limit was clamped to p99 x 1.1 to prevent axis expansion caused by long-tail response times.

2. Query Design and Server-Side Aggregation The issue of unusable indexes due to a mismatch between the X-axis (end time) and the sort key (start time) was resolved by materializing a MATERIALIZED COLUMN (ts_end). To reduce excessive point transmission, GROUP BY aggregation was performed on the server side per pixel grid (tx, ty). Local measurements showed an 8.5x reduction in data rows and a decrease in query time from 78ms to 28ms.

3. Incremental Lookup and Caching To prevent data loss due to non-monotonic event times, an incremental lookup approach using the load time (inserted_at) as a cursor was applied. By receiving snapshot and delta data and leveraging ClickHouse query cache, response time improved from 33ms to 6ms after the initial query. A lower bound was set on the update cycle to control load, and rollup tables were used for data older than 2 hours to improve efficiency.