TiFlash: optimize full-table MAX(CHAR_LENGTH) scans on string columns

[JaySon-Huang]

Problem

Production workloads include full-table scans that compute MAX(CHAR_LENGTH(col)) on many string columns of a large table. In a few seconds TiFlash can read tens to hundreds of gigabytes, saturate cloud-disk read bandwidth, and stall other reads and writes on the same nodes.

Typical SQL:

SELECT
  MAX(CHAR_LENGTH(c1))  AS c1,
  MAX(CHAR_LENGTH(c2))  AS c2,
  MAX(CHAR_LENGTH(c3))  AS c3,
  -- ... a dozen or more string columns
  MAX(CHAR_LENGTH(c16)) AS c16
FROM t;

The same pattern appears on other large tables, with anywhere from a few to more than ten columns. The intent is always “the maximum character length of each string column”.

Query characteristics

Aspect	Observation
Semantics	Max character length per column; often used to size VARCHAR for downstream schemas
Access path	TiFlash MPP full table scan, no WHERE / LIMIT
Column types	All strings; a dozen-plus columns scanned together
Plan	TableFullScan → Projection(char_length × N) → HashAgg(max × N)
Cadence	Typically once per table, not a steady OLAP load
Resources	Per-request Read RU often exceeds (10^6), peak over (7 \times 10^6)

Observed scale of one run:

• About 1.8 billion rows, 16 columns, ~373 GB
• TableScan ~7 s; 7 TiFlash nodes (example: 32C / 256 GB, cloud-disk baseline read bandwidth 1250 MB/s)
• ~7.5 GB/s decompressed scan per node, above the instance’s disk read cap

Approximate per-node MPP stats:

Operator	Rows	Outbound
TableScan	~260 million	~66 GB strings
Projection	~260 million	~38 GB Int64
HashAgg	1	144 B

Why this SQL is expensive

The bottleneck is reading string columns from disk, not the aggregation.

1. Every value must be read; packs cannot be skipped
   There is no filter, so late materialization and RS / MinMax do not help. MinMax stores lexicographic min/max of string values, which is unrelated to length, so packs that cannot refresh the global max still cannot be skipped.

2. CHAR_LENGTH must scan UTF-8 payload
   TiFlash stores strings in DMFile as two streams: StringSizes (per-row length) and chars (payload).

   • LENGTH() uses offsets only; the function already ignores data.
   • CHAR_LENGTH() (utf8mb4) goes through lengthUTF8 and counts code points; TableScan still reads both streams.

3. Columnar layout does not help this query
   Columnar storage avoids reading unused columns, but the selected columns are the fat string columns. Scanning a dozen of them in parallel saturates disk bandwidth.

4. Large intermediates
   Projection materializes ~1.8 billion rows × 16 Int64s (~38 GB per node) before MAX. It pipelines with Scan, so wall time is still IO-bound, but extra CPU and memory are spent.

5. Instance bandwidth is saturated
   373 GB / 7 nodes / ~7 s ≈ 7.5 GB/s decompressed per node. Disk baseline is 1.25 GB/s; even compressed reads overshoot, and other IO on the node (including wait-index) jitters.

Workarounds (application side, brief)

Approach	Notes
Decide whether a true global max is required	Sampling or scanning hot partitions is often enough to size downstream VARCHAR
Rewrite SQL: fewer columns / batches	Similar total IO, lower peak bandwidth
Resource Control	Put these scans in one resource group and throttle. The query does not get faster, but the disk is less likely to saturate
Do not rely on adding a STORED generated column later	TiDB currently rejects ALTER TABLE ADD COLUMN ... STORED (error 3106). VIRTUAL can be added, but TiFlash still expands it and reads the base strings
Materialize length only if the query is repeated	Define INT AS (CHAR_LENGTH(c1)) STORED at CREATE TABLE, or maintain a regular INT column on the write path, and query MAX(length_col). The optimizer will not rewrite MAX(CHAR_LENGTH(c1)) to the generated column

For a one-shot probe: prefer sampling / splitting columns / throttling. Do not change the table just to run the query once.

Proposed engine optimizations

Ordered by payoff for this SQL.

1. Pack-level max_len skip (highest priority)

Today
MinMaxIndex stores lexicographic string ranges. MAX(CHAR_LENGTH(col)) cannot skip packs from that.

Proposal
On pack write, also record max_byte_len / max_char_len (cost similar to existing MinMax). While scanning, keep a running max:

• If pack_max_len <= running_max → skip the whole pack (including compressed chars)
• Otherwise read the pack and update the running max

Effect
After a few packs that contain longer strings, most remaining packs can be skipped. The more concentrated the length distribution, the more is skipped. This is the main way to turn a hundreds-of-GB full read into “pack metadata + a few packs”.

Notes

• Keep CHAR_LENGTH and LENGTH stats separate (UTF-8 character count ≠ byte length), or store both.
• Delta / memtable still need a full scan or equivalent stats.
• Needs a new pack-stat format and a compatible reader.

2. Read StringSizes only, skip chars

Today
LengthImpl already uses offsets only; TableScan still reads both sizes and chars.

Proposal
Push “length only, no payload” down to the DMFile reader:

• LENGTH(col): read StringSizes (and the nullmap), skip chars.
• CHAR_LENGTH(col): utf8mb4 still needs chars by default; if a pack has an all_ascii flag (no high-bit bytes), ASCII packs can also read sizes only.

Effect
In the observation above, payload dominates the ~66 GB outbound per node; sizes are ~4 B/row. Reading sizes only can drop IO by an order of magnitude for those columns. This is general, not limited to MAX().

Notes
Scan / Projection must declare “this column needs length, not payload”. CHAR_LENGTH on non-ASCII packs must fall back to reading chars.

3. Fold CHAR_LENGTH into HashAgg

Today
The plan is Scan → Projection(char_length × N) → Agg(max × N). Projection emits Int64 columns for every row.

Proposal
Recognize MAX(CHAR_LENGTH(col)) / MAX(LENGTH(col)) and keep N running maxes inside Agg, without materializing all intermediate rows. Better: TableScan emits length columns directly and Agg only computes max.

Effect
Saves CPU / memory (~38 GB Projection outbound per node in the observation). Wall time barely changes when already IO-bound. Treat this as a companion to (1) / (2), not the fix for disk saturation.

4. Persist length in column metadata or a hidden column (optional)

Write length alongside the string (hidden column, or actually store a STORED generated column in TiFlash). MAX(length_col) becomes an integer-column scan, often an order of magnitude smaller after compression.

Large surface: replication decode, schema, file format, and alignment with TiDB generated-column semantics. TiDB cannot add STORED generated columns via ALTER TABLE; TiFlash inserts only a placeholder for VIRTUAL columns and still reads the base strings. After pack-level max_len skip, per-row length persistence is not required for this SQL; keep it as a longer-term, broader capability.

Not recommended as the primary engine fix

• Resource Control: protects the cluster; does not reduce IO of a single query.
• Rewriting to LENGTH: close to CHAR_LENGTH on ASCII data, but TableScan still reads chars today, so only CPU is saved until (2) lands.
• Splitting into several queries with fewer columns: lowers peak, increases total time; a scheduling tactic.

Suggested order

1. Pack-level max_len skip
2. Sizes-only scan
3. Fold Projection into Agg as a companion

[JaySon-Huang]

Follow-up feature requests:

• Pack-level max_len skip: TiFlash: pack-level max_len skip for MAX(CHAR_LENGTH) / MAX(LENGTH) #11050
• Sizes-only scan (StringSizes, skip chars): TiFlash: read only StringSizes for LENGTH / CHAR_LENGTH scans #11051

[JaySon-Huang]

Similar optimizations in DuckDB and OceanBase — a preliminary analysis

A follow-up comparison of the proposed engine optimizations above against how DuckDB and OceanBase (open-source columnar/HTAP engines with similar pack-based storage) handle the same class of queries. This is a source-level analysis (DuckDB master, OceanBase 4.x).

TL;DR: Both engines already have value-level min/max pack skipping (zone maps / skip index), which helps MAX(col) / filtered scans but — exactly like TiFlash — neither has a length-level skip stat for MAX(CHAR_LENGTH(col)). The interesting differences:

• DuckDB already stores per-segment string-length statistics (max_string_length, total_string_length, has_unicode), but nothing wires them into aggregate execution — the "data foundation" for proposal 1 exists and is exact.
• OceanBase already computes aggregates from the skip index without reading data blocks (MAX/MIN/SUM/COUNT(*) fold per micro-block), but only for column values, not for LENGTH/CHAR_LENGTH — it has proposal 3's machinery but no length stat to feed it.

---

Proposal 1: Pack-level max_len skip

DuckDB — partially there (stats stored, skip logic missing).

Per-segment string statistics (duckdb/src/include/duckdb/storage/statistics/string_stats.hpp):

struct StringStatsData {
    ...
    bool has_max_string_length;      // max string length is known
    uint32_t max_string_length;      // max length in bytes
    bool has_min_string_length;
    uint32_t min_string_length;      // min length in bytes
    bool has_total_string_length;
    uint64_t total_string_length;    // summed length of all strings
    bool has_unicode;                // whether the segment may contain multi-byte chars
    ...
};

These are maintained exactly at append/checkpoint time (duckdb/src/storage/statistics/stats_writer.hpp). The optimizer has a pass that answers aggregates from table statistics — StatisticsPropagator::TryExecuteAggregates (duckdb/src/optimizer/statistics/operator/propagate_aggregate.cpp) — but it only recognizes MIN/MAX over a bare column (using the column's own lexicographic min/max, exact-stats only) and COUNT(*) (from per-row-group row counts). TryGetMinMaxColumnInfo unwraps casts and projections but not length()/char_length(). So MAX(CHAR_LENGTH(col)) does not trigger the stats path, and max_string_length is currently used only for compression decisions (e.g. whether strings can be stored inline).

Practical takeaway: the pack metadata TiFlash proposes to add is already maintained per segment in DuckDB. Wiring it into the aggregate executor is comparatively cheap.

OceanBase — not present.

Skip index types (oceanbase/src/storage/blocksstable/index_block/ob_index_block_util.h):

enum ObSkipIndexType : uint8_t { MIN_MAX, BLOOM_FILTER, NGRAM_BLOOM_FILTER, MAX_TYPE };
// pre-aggregated columns stored per micro block:
enum ObSkipIndexColType : uint8_t {
    SK_IDX_MIN, SK_IDX_MAX, SK_IDX_NULL_COUNT, SK_IDX_SUM,
    SK_IDX_BM25_MAX_SCORE_TOKEN_FREQ, SK_IDX_BM25_MAX_SCORE_DOC_LEN, ...
};

There is no general max_char_len/max_byte_len skip stat. The only length-like pre-aggregate is the BM25 document length, scoped to full-text scoring (SK_IDX_BM25_MAX_SCORE_DOC_LEN). Adding a max_len stat would also have to respect the fixed MAX_AGG_COLUMN_PER_ROW = 6 per micro-block row.

Note: OceanBase's MIN_MAX skip index is used to skip micro blocks for value filters (via ObSSTableIndexFilter / ObSkipIndexFilterExecutor), so it behaves like TiFlash's current MinMax — useless for length.

Proposal 2: Read sizes only, skip payload

DuckDB — partially there (no separate streams, but has_unicode per segment).

DuckDB does not store strings as separate StringSizes + chars streams; strings are string_t values (≤12 bytes inline, heap pointer otherwise) inside the column's (possibly compressed) blocks. However, each segment tracks has_unicode (maintained via Utf8Proc::Analyze at write time). In principle an ASCII-only segment lets CHAR_LENGTH skip UTF-8 decoding and use the byte length; the flag is not currently consulted by the length execution path. LENGTH()/octet_length in DuckDB can be answered from offsets without touching payload — a "sizes-only" notion exists implicitly.

OceanBase — partially there (index-level, not a general length-only scan).

The pushdown-aggregate path already avoids reading data blocks: ObPushdownAggregate::can_use_index_info → read_agg_datum folds MIN/MAX/SUM/COUNT from the micro-block index (see next section). That is "no data read" but only for value aggregates, not a general sizes-only scan for length(). The BM25 doc-length pre-aggregate is index-level as well.

Proposal 3: Fold CHAR_LENGTH into the aggregate

DuckDB — not present. The plan stays Scan → Projection(char_length × N) → Agg(max × N); the intermediate Int64 columns are materialized.

OceanBase — present, but for value aggregates. Aggregates are pushed down into storage and folded per micro block without reading data:

oceanbase/src/storage/access/ob_pushdown_aggregate.cpp:

int ObAggCell::can_use_index_info(const ObMicroIndexInfo &index_info, const bool is_cg, bool &can_agg) {
    if (!ignore_eval_index_info_ && index_info.has_agg_data() && can_use_index_info()) {
        // read SK_IDX_MIN / SK_IDX_MAX / SK_IDX_SUM / SK_IDX_NULL_COUNT for the block,
        // then fold into the running aggregate — no data block read
    }
}

MAX(col) uses SK_IDX_MAX, SUM(col) uses SK_IDX_SUM, COUNT(*) uses SK_IDX_NULL_COUNT, and the machinery guards against string prefix inaccuracy on multi-byte charsets (SAFE_MBCHARSET_PREFIX_MAX_LEN). So OceanBase already implements "aggregate answered from skip index without data reads" — exactly the target of proposals 1 + 3 — but for column values, not LENGTH/CHAR_LENGTH.

Proposal 4: Persist length in metadata / hidden column

• DuckDB: not present (no persisted per-row length; per-segment total_string_length is the closest thing).
• OceanBase: only for full-text — the inverted index maintains a document length (DOC_LENGTH_IDX in ob_index_block_aggregator.h); no general column-level persisted length.

---

Summary table

Proposed optimization	DuckDB	OceanBase
1. Pack-level max_len skip	⚠️ Per-segment max_string_length/total_string_length/has_unicode already stored & exact; not wired to aggregate skip	❌ No length skip stat; only value MIN_MAX; BM25 doc length is FTS-only
2. Sizes-only scan (skip payload)	⚠️ has_unicode per segment exists; not consulted by length	⚠️ Aggregate path avoids data reads; not a general length-only scan
3. Fold CHAR_LENGTH into Agg	❌ Not folded	✅ Aggregates computed from skip index without reading data — for values only
4. Persist length in metadata	❌ None	⚠️ Inverted-index doc length only (FTS)

What this suggests for TiFlash

• Adding a pack-level max_len/max_char_len stat (proposal 1) is orthogonal to the existing MinMax index and would immediately unlock running-max pack skipping, just as it would in OceanBase.
• OceanBase's ObAggCell::eval_index_info pattern shows the target shape for proposal 3: once a length stat exists per pack, MAX(CHAR_LENGTH(col)) can fold pack metadata directly, combining 1 and 3 with a single new pre-aggregate.
• The per-segment string statistics already maintained in DuckDB confirm the metadata cost of proposal 1 is modest (a few integers per segment) and can be kept exact at write/checkpoint time.