-
[OPIK-6747] [BE] perf: defer wide-column reads past pagination in traces/spans list queries (#6930)
frostbyte_neo 发布于
2026-06-08 10:27:13 +00:00 - [OPIK-6747] [BE] perf: defer wide-column reads past pagination in traces/spans list queries
The list-by-project query (SELECT_BY_PROJECT_ID in TraceDAO and SpanDAO)
materialized all wide text columns (input, output, metadata, input_slim,
output_slim) for the ENTIRE filtered set in the *_deduped CTE, then applied
the page LIMIT/OFFSET only one CTE later in *_final. On large projects this
reads millions of rows and GiBs of column data to return a ~100-row page,
spiking ClickHouse memory (multiple GiB per query, tens of GiB on the largest
projects) and triggering OOM alerts; large projects can exceed the per-query
memory limit and fail outright (error 241).Fix: the *_deduped / *_final CTEs now carry only the columns needed to filter,
sort and dedup. The wide display columns (input, output, metadata,
truncated_input, truncated_output) are re-read from the base table via a new
page_wide CTE restricted to the paginated id set (id IN (SELECT id FROM
*_final)), preserving ReplacingMergeTree dedup (same ORDER BY + LIMIT 1 BY id).
input_slim/output_slim are dropped entirely (unused by this query's output).A sort_needs_wide flag (SortableFields.sortsByWideTextColumn) keeps
input/output/metadata in the pre-pagination scan only when the sort references
them, so sorting by those columns still works; the flag is set by a shared
FilterUtils.addSortNeedsWideFlag helper used by all renderers (paginated +
stream paths). page_wide and its join are omitted when input/output/metadata
are all excluded.Tests: regression test for sorting by a wide field while excluding that same
field, plus a unit test for SortableFields.sortsByWideTextColumn covering base
and dynamic-path (input.x) detection.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-6747] Collapse traces/spans list query to a single deduped scan
The two-phase list query previously referenced the paginated set twice — once
as the final FROM and once inside page_wide'sid IN (...). Because ClickHouse
inlines CTEs, this re-evaluated the whole deduped/filtered scan a second time
(extra read I/O on wide-column filter/sort paths).Restructure into page_ids (deduped/ordered/limited ids — referenced once) ->
page_wide (full page rows read once for those ids) -> final SELECT reading
from page_wide directly. Each CTE is now referenced exactly once, so the
filtered scan runs a single time. Wide columns are still read only for the
~page-size rows.Applied to both TraceDAO and SpanDAO (preserving the stream vs non-stream
dedup/sort keys for spans). No behavioral change — validated by
GetTracesByProjectResourceTest, TracesResourceTest, and SpansResourceTest.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-6747] [BE] Address review: full-content + paginated sort/exclude tests, @NonNull, query javadoc
Addresses review feedback on the deferred-wide-column sort/exclude query:
- Traces & spans: parameterized sort x exclude full-content tests, plus
paginated null/sparse all-pages tests that prove the page_ids pre-filter
carries the sort key (a single full page can't catch a broken pre-filter). - FilterUtils.addSortNeedsWideFlag: @NonNull on the template arg.
- SELECT_BY_PROJECT_ID: two-phase (page_ids/page_wide, deferred wide columns)
design javadoc on SpanDAO and TraceDAO. - SortableFields: WIDE_TEXT_FIELDS comment -> javadoc.
- Remove SortableFieldsTest: the util is now covered by the black-box tests.
Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-6747] [BE] Address review round 2: consolidate sort/exclude tests, tidy query comments
- SpanDAO/TraceDAO: drop the inline two-phase SQL comments (the rationale
already lives in the SELECT_BY_PROJECT_ID javadoc). - Tests: replace the overlapping whenSortingAndExcludingField + null/sparse
paginated tests with a single focused, deterministic, paginated
whenPaginatingSortedAndExcludingField (spans + traces). It sorts by a field
while excluding it, walks every page, and asserts the FULL page content vs
the expected sorted slice (not ids). No nulls, no AtomicInteger. - Move the per-page fetch helper into TraceResourceClient#getTracesByPage.
Verified over real ClickHouse; mutation-checked (dropping sort_fields from
page_ids fails the paginated tests).Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-6747] [BE] Address review: defensively dedup EXCEPT column lists
The two-phase query could emit EXCEPT lists that ClickHouse only tolerates
leniently: the same wide column in two EXCEPT clauses (when !sort_needs_wide
and that column is also excluded), and the final SELECT EXCEPT'ing
truncated_input/truncated_output even when truncate=false (those columns are
not in page_wide). A stricter CH EXCEPT impl would fail-compile the whole
list endpoint.Dedup, both DAOs (spans_deduped/traces_deduped, page_wide, final SELECT):
- gate the deferred wide-column drops on <if(!exclude_*)> so they don't
overlap the exclude_fields EXCEPT, - drop the redundant wide-column conditionals from page_wide (exclude_fields
already covers them), - gate input/output/metadata in the final SELECT on <if(!exclude_)> and
truncated_ on <if(truncate)>.
SQL EXCEPT is a read optimization; response-level exclusion is unchanged.
Regression-verified: sort (all fields), exclude (all fields), image
truncation, and pagination — 72 span + 73 trace tests green.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-6747] [BE] Address review: gate truncated_* EXCEPT on field exclusion
The final SELECT EXCEPT'd truncated_input/truncated_output under <if(truncate)>
unconditionally, but page_wide only projects them when the matching wide field
is NOT excluded. So truncate=true + exclude input/output EXCEPT'd a column that
page_wide had dropped (ClickHouse tolerates it today, but it's fragile on a
stricter CH). Gate them on <if(!exclude_*)> to mirror page_wide, in both DAOs.Add whenTruncatingAndExcludingWideField guard tests (spans + traces) as a
regression lock for the truncate+exclude combination. Correctness of the
rendered EXCEPT lists was verified against ClickHouse query_log.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-6747] [DOCS] Add backend skill guidance for sorting/pagination/exclusion SQL
Codify the lessons from the deferred-wide-column work so future query changes
follow the same bar:- clickhouse.md: the two-phase page_ids/page_wide invariants (pre-filter carries
the sort key; final SELECT re-sorts; spans and traces change together). - testing.md: sorting/pagination/field-exclusion SQL changes must be covered by
full-page-content tests (not ids), custom sort_fields, the sort x exclude
combination, and pagination — for both spans and traces.
Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-6747] [BE] Address review: full actual-vs-expected assertion in truncate+exclude guard tests
The whenTruncatingAndExcludingWideField guard tests only spot-checked input()/
output(). Assert the full page content instead (assertSpan / assertTraces):
build expected = created row with the excluded wide field nulled, using plain
JSON so truncate is a no-op and the expectation is exact.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- test(backend): add filter+sort+exclude multi-page regression for deferred wide columns
Covers the OPIK-6747 production scenario end-to-end on both spans and traces:
filter (input CONTAINS), custom sort (input ASC), deferred wide-column exclusion
(output), paged. Each page is asserted byte-for-byte against an independent
Java-side filter+sort reference and against the single full page, with full row
content. Non-matching rows must be filtered out and excluded from the total.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- docs(backend): capture ClickHouse test-reactor collision gotcha in testing skill
Running two ClickHouse-migrating resource test classes (spans + traces) in one
mvn reactor fails with REPLICA_ALREADY_EXISTS; document the separate-invocation
fix and the @Nested/parameterized surefire selector tips alongside it.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
Co-authored-by: Claude Opus 4.8 (1M context) noreply@anthropic.com
下载附件