Partitioning Fuse Tables for Faster Pruning and Predictable Data Layout

PARTITION BY physically separates a Fuse table into partitions. TiDB Cloud Lake keeps blocks and segments from different partition values separate, records the partition values in metadata, and prunes partitions that cannot match a query predicate.

Use partitioning when large tables are commonly filtered by time ranges, tenant or region identifiers, or another stable dimension. For high-cardinality keys, use BUCKET() to create a fixed number of hash partitions.

What Problem Does It Solve?

Partitioning vs. Clustering

PARTITION BY and CLUSTER BY are complementary:

You can use both on one table. The partition key does not become part of the user-visible cluster key:

CREATE TABLE events (
    event_time TIMESTAMP,
    tenant_id  BIGINT,
    payload    VARIANT
)
PARTITION BY (DATE_TRUNC(day, event_time))
CLUSTER BY (tenant_id, event_time);

Quick Setup

Create a table partitioned by day:

CREATE TABLE events (
    event_id   BIGINT,
    event_time TIMESTAMP,
    event_type STRING
)
PARTITION BY (DATE_TRUNC(day, event_time));

All subsequent writes honor the partition boundary:

INSERT INTO events VALUES
    (1, '2026-07-08 10:00:00', 'login'),
    (2, '2026-07-08 18:30:00', 'purchase'),
    (3, '2026-07-09 09:15:00', 'logout');

A time predicate can then prune unrelated day partitions:

EXPLAIN
SELECT *
FROM events
WHERE event_time >= TIMESTAMP '2026-07-08'
  AND event_time <  TIMESTAMP '2026-07-09';

In the EXPLAIN output, compare partitions total with partitions scanned and check the range pruning statistics. Fewer scanned partitions indicate that partition pruning was applied.

Partitioning Strategies

This section describes different strategies for partitioning Fuse tables in TiDB Cloud Lake.

Time-Based Partitioning

Apply a deterministic expression to a timestamp or date column:

-- Daily partitions
PARTITION BY (DATE_TRUNC(day, event_time))

-- Monthly partitions
PARTITION BY (DATE_TRUNC(month, event_time))

Choose a granularity that matches common filters while keeping each partition large enough to avoid fragmentation. Daily partitions usually suit high-volume event data; monthly partitions often work better for lower-volume historical data.

Identity Partitioning

Use the column directly when it already has a small, stable set of values:

CREATE TABLE regional_sales (
    region     STRING,
    order_id   BIGINT,
    order_date DATE,
    amount     DECIMAL(18, 2)
)
PARTITION BY (region);

Good identity keys include region, country, tenant tier, or another bounded business dimension. Avoid partitioning directly by unique IDs.

Hash Partitioning with BUCKET()

BUCKET(<count>, <column>) deterministically maps supported values to an unsigned partition number in the range 0 through <count> - 1. The bucket count must be a constant integer from 1 through 4294967295.

CREATE TABLE customer_events (
    customer_id BIGINT,
    event_time  TIMESTAMP,
    payload     VARIANT
)
PARTITION BY (BUCKET(32, customer_id));

Bucket partitioning is useful when equality filters target a high-cardinality column but creating one physical partition per distinct value would be impractical.

Supported BUCKET() value types are integers, strings, dates, and timestamps. See BUCKET() for details.

Composite Partitioning

List multiple expressions to form a composite partition key:

CREATE TABLE tenant_events (
    tenant_id  BIGINT,
    event_time TIMESTAMP,
    payload    VARIANT
)
PARTITION BY (
    DATE_TRUNC(month, event_time),
    BUCKET(16, tenant_id)
);

Each partition expression must reference exactly one source column. To partition by two columns, use separate expressions as shown above; an expression such as tenant_id + another_id is not valid.

Hash-Distributed Writes

For distributed or highly parallel ingestion into partitioned tables, set WRITE_DISTRIBUTION_MODE = 'hash':

CREATE TABLE customer_events (
    customer_id BIGINT,
    event_time  TIMESTAMP,
    payload     VARIANT
)
PARTITION BY (BUCKET(32, customer_id))
WRITE_DISTRIBUTION_MODE = 'hash';

This mode hashes the evaluated partition key and routes rows with the same partition value to the same writer before local sorting. It can reduce small-block fragmentation when several nodes or pipelines write the same partitions concurrently.

WRITE_DISTRIBUTION_MODE accepts:

• 'none' (default): Do not redistribute rows before writing.
• 'hash': Redistribute rows by the partition key before writing. This value requires PARTITION BY.

Hash redistribution adds a network shuffle. Enable it for distributed or parallel loads where improved block layout outweighs the shuffle cost; it may not benefit small, single-writer inserts.

INSERT INTO events SELECT ... FROM source ORDER BY DATE_TRUNC(day, event_time);

Adding a Partition Key to an Existing Table

ALTER TABLE ... PARTITION BY changes only the table definition. Existing data is not redistributed, and OPTIMIZE TABLE or RECLUSTER does not redistribute it later. Only data written after the change is partitioned.

Empty table: add the key, then load.

ALTER TABLE events PARTITION BY (DATE_TRUNC(month, event_time));
INSERT INTO events SELECT * FROM source;

Table with data: add the key, then rewrite the table. Keep the ORDER BY; without it, the rewrite writes very small blocks and is slow.

ALTER TABLE events PARTITION BY (DATE_TRUNC(month, event_time));
INSERT OVERWRITE events SELECT * FROM events ORDER BY DATE_TRUNC(month, event_time);

Change the key: drop it, add the new one, then rewrite the table.

ALTER TABLE events DROP PARTITION KEY;
ALTER TABLE events PARTITION BY (DATE_TRUNC(day, event_time));
INSERT OVERWRITE events SELECT * FROM events ORDER BY DATE_TRUNC(day, event_time);

DROP PARTITION KEY does not rewrite data. It stops partition pruning and resets WRITE_DISTRIBUTION_MODE to 'none'.

Updating the Partition Key Value of a Row

UPDATE and MERGE INTO can change the partition key value of a row. The row moves to the partition of its new value. No extra action is needed.

-- Moves the row from the September partition to the October partition.
UPDATE events SET event_time = '2026-10-05 12:00:00' WHERE event_id = 42;

Moving a row rewrites it, so it costs more than an in-place update.

Expression Rules and Table Changes

For Fuse table partition expressions:

• Each expression must be deterministic and reference exactly one source column.
• Use multiple expressions—not one multi-column expression—for composite partitioning.
• BUCKET() requires a constant bucket count from 1 through 4294967295.
• Columns referenced by the partition key cannot be dropped or have their data types changed.
• Renaming a referenced column updates the stored partition expression.
• The internal partition_by metadata cannot be changed with SET OPTIONS.
• PARTITION BY is supported for Fuse tables. Other engines may implement different partitioning syntax and semantics.

Best Practices