Table Partitioning

SchemaStoragePerformance

Beta Works · surface still evolving

What it is

Partitioning tells AetheriusDB how to group a table’s rows physically. Once the rows that belong together are stored together, a query that names a group can skip everything else without reading it.

Three schemes are available, and they answer different questions:

Scheme	Declares	Best for
HASH	A fixed fan-out over one column	Even spread; no hot partition
RANGE	Explicit ascending bounds on one column	Time buckets, ID ranges, retention
TOPOLOGY	A multi-column business key, no bounds; fields may be hashed or ORDERED	Multi-tenant, hierarchical and time-bucketed data

TOPOLOGY is the one to reach for by default. HASH and RANGE require you to predict the shape of your data at CREATE TABLE time; TOPOLOGY derives the physical grouping from the data itself.

Why it matters

A partitioned table answers a filtered query by reading a fraction of itself. The saving compounds with the three layers AetheriusDB applies in order:

1. Partition prune — drop whole ranges of chunks on the declared key.
2. Zonemap skip — drop individual chunks whose (min,max) cannot match.
3. Clustered seek — inside a surviving chunk, binary-search rather than scan.

The third layer only exists for clustered tables (below), and it is what turns a point lookup from linear into logarithmic work.

How you use it

HASH — fixed fan-out

CREATE TABLE events (id BIGINT, payload TEXT)
PARTITION BY HASH (id) PARTITIONS 8;

The key column must be an integer. PARTITIONS n is required — a hash scheme without a fan-out has nothing to route with.

RANGE — explicit bounds

CREATE TABLE sales (sold_at BIGINT, amount BIGINT)
PARTITION BY RANGE (sold_at) (
  PARTITION q1 VALUES LESS THAN (20260401),
  PARTITION q2 VALUES LESS THAN (20260701),
  PARTITION rest VALUES LESS THAN (MAXVALUE)
);

Bounds are upper-exclusive and must ascend. The trailing MAXVALUE partition is optional but recommended: without it, a row above the last bound is rejected at insert. That is deliberate — a misrouted row is invisible to every pruned query, which is strictly worse than a failed insert.

TOPOLOGY — a business key

CREATE TABLE usage (tenant TEXT, region TEXT, recorded_at BIGINT, units BIGINT)
PARTITION BY TOPOLOGY (tenant, region);

TOPOLOGY declares no partition count and no bounds. Chunks are data-driven: AetheriusDB adds a hidden _aether_topo_key column, packs a hash of each declared column into its own bit-field, and sorts rows by that key as it seals chunks. Pruning is then an ordinary (min,max) range test on one column — no separate index to build, and nothing to keep in step.

Column order is load-bearing. Fields are packed high-to-low in declaration order, so a query supplying a prefix of the columns prunes exactly:

-- Prunes on the high bits: reads only this tenant's chunks.
SELECT sum(units) FROM usage WHERE tenant = 'acme';

-- Prunes further: tenant AND region.
SELECT sum(units) FROM usage WHERE tenant = 'acme' AND region = 'eu';

-- Does NOT prune: region alone constrains a middle slice of the key,
-- which is not a contiguous range.
SELECT sum(units) FROM usage WHERE region = 'eu';

Put the column you filter on most often first.

The hidden key column never appears in SELECT * and cannot be written to directly.

ORDERED fields — pruning a range, not just an equality

By default a topology column is hashed, which prunes equality and prefix probes but not ranges: a hash destroys order, so event_at > x says nothing about where the row sits in the key. Declare a column ORDERED and its value order survives into the key, so a range predicate becomes a contiguous key range and the same zonemap drops the cubes outside it.

CREATE TABLE usage (tenant TEXT, event_at TIMESTAMP, units BIGINT)
PARTITION BY TOPOLOGY (tenant, event_at ORDERED);

-- Now prunes on BOTH: tenant pins the high field, the range bounds the next.
SELECT sum(units) FROM usage
WHERE tenant = 'acme' AND event_at BETWEEN '2026-03-01' AND '2026-03-31';

This is what makes month- or day-shaped access work without a partition per month. New periods need no DDL — they are simply new key values, as topology already handles — and because time is in the key, compaction re-sorts by it, so pruning quality recovers automatically after out-of-order or backfilled loads.

Three rules worth knowing:

• Order the columns hashed-first. (tenant, event_at ORDERED) distributes across tenants and orders within each. Reversed, all recent writes land in one key range — a single hot region.
• A range ends the prefix. Once a field is bounded rather than pinned, the fields below it span their whole domain, so a third column after an ordered range does not narrow anything further.
• ORDERED is integer, timestamp or boolean only. Text is refused, deliberately: 'MAR-2026' sorts before 'MAY-2025', so lexicographic order is not the order you mean. For an ordered text column, declare SORTED DOMAIN (...) on it instead — that ranks a closed set of values and lets BETWEEN rewrite to an integer range.

HASHED may be written explicitly, and omitting the modifier means HASHED, so existing PARTITION BY TOPOLOGY (a, b) declarations are unchanged.

The bucket width is a consequence you cannot see

An ordered field holds the top bits of the value, so one bucket spans a fixed slice of the column’s own domain — and the field width is currently split evenly across your key columns rather than declared. For nanosecond timestamps the buckets are coarse; for second- or day-resolution columns they are far finer than you need. Pruning is correct either way (a coarse bucket keeps chunks it did not need, never drops one it did), but the selectivity you get depends on your column’s unit.

Clustered composite keys

Beta Works · surface still evolving

When a TOPOLOGY table also declares a single-column integer primary key, AetheriusDB folds the primary key into the same hidden key column:

CREATE TABLE orders (
  order_id BIGINT,
  tenant   TEXT,
  amount   BIGINT,
  PRIMARY KEY (order_id)
)
PARTITION BY TOPOLOGY (tenant);

 63                            24 23              0
┌────────────────────────────────┬─────────────────┐
│ partition hash — 40 bits       │ primary key 24b │
└────────────────────────────────┴─────────────────┘

Rows are then ordered partition-major, primary-key-minor. That single ordering does two jobs: chunks still prune by tenant, and within a chunk the key column is sorted by primary key — so a point lookup is a binary search over a stripe that already exists.

-- Resolved by binary search on the clustered key, not a scan.
SELECT * FROM orders WHERE order_id = 90210;

No index is created, declared, or maintained for this. The ordering is the index.

Nothing about the declaration changes — you write an ordinary PRIMARY KEY and PARTITION BY TOPOLOGY, and the clustered layout follows. Composite primary keys and non-integer primary keys keep the plain topology layout, because the low field is an order-preserving truncation of one integer and there is no order-preserving truncation of a tuple or of text.

Why truncation, and what it costs

The primary key occupies 24 bits, so keys are compared on their low bits first. That makes the search a candidate range, not an answer — the real predicate still decides which row matches. A collision costs a few extra rows examined, never a wrong result.

Compaction and space reclamation

Freshly ingested chunks overlap: each batch sorts itself, so many chunks span much of the key space and prune poorly. The topology compactor merges overlapping chunks and re-sorts them, which is what makes pruning sharp over time. Compaction never changes an answer — it is a physical reorganisation.

Compacted-away chunks leave dead bytes on disk. The vacuum rewrites a container keeping only live chunks, atomically.

These are host-driven, not automatic

The compaction sweep and the vacuum are exposed as runtime APIs (compaction_sweep_once, spawn_compaction_ticker, vacuum_once). The shipped aetheriusd server does not start them on its own today, and there is no SQL statement for either. An embedding application must drive them. If you are running the standard server, treat pruning quality as a function of your ingest order until this is wired up.

Bulk loads and source tables

Partitioning is enforced at the storage seal path, so every ingest route stamps and clusters identically: INSERT, COPY … FROM, CREATE TABLE … AS SELECT, and directory-watched source tables. A partitioned bulk load takes the row-conversion path (somewhat slower than the raw columnar fast path) in exchange for cubes that actually prune.

CREATE SOURCE TABLE events (tenant BIGINT, v BIGINT)
FROM '/data/events' FORMAT 'csv' WITH (header = 'false')
PARTITION BY TOPOLOGY (tenant);

The PARTITION BY clause trails the WITH block and accepts every scheme a regular CREATE TABLE does. The declaration and the loaded rows survive a restart. Known gap: under the default HTAP configuration, post-restart reads currently route around the partition pruner — results stay correct, cubes are not yet skipped (tracked in tests/source_table_partition_by_e2e.rs).

What is not supported yet

Being explicit about the edges, so you do not design around something that is not there:

• Partitioning an existing table. PARTITION BY is accepted only at CREATE TABLE. There is no ALTER TABLE … PARTITION BY.
• PARTITION BY LIST. It parses, and is then rejected with a diagnostic — accepting it would create a table that partitions nothing.
• Per-partition storage tiers. You cannot site one partition in L1 and another in L4. Residency is decided by the memory budget per chunk. See Multi-Tier Residency.
• Sub-partitions. There is no nested partition syntax. TOPOLOGY’s multi-column key gives hierarchical pruning — a prefix query prunes on the high bits exactly as a nested scheme would — without a second metadata layer.
• Per-partition local indexes. Secondary indexes are table-wide. The clustered key above is the only partition-local access path.

Limits

• At most 6 topology columns. The key is 64 bits and every column needs a usable field; past six the fields are too narrow to separate values well.
• Topology fields are currently split evenly across the available bits. If one column has far higher cardinality than another (100,000 tenants against 6 regions), the low-cardinality column is over-provisioned and the high-cardinality one collides more often. Collisions cost co-location — two business keys share a chunk — never correctness.
• HASH and RANGE take exactly one column, and it must be an integer. TOPOLOGY accepts text and integer columns alike.

Relational Data Model →

Tables, columns, keys and constraints.

Memory Compaction →

The background reclamation partitioned tables rely on.

Multi-Tier Residency →

How chunks move between memory tiers.

Advanced Indexing (PTI) →

Secondary indexes, which are table-wide rather than per partition.

EXPLAIN →

Check which access path a query actually chose.