Group Tables

TransactionsDDLRoutingSharding

Stable On by default · production-ready

What it is

A group table is a single logical table defined over several child tables. You read and write it as one object; underneath, the engine decides which child each row belongs to. A ROUTE clause routes inserts to the right child by their column values, and updates against the parent are propagated to the correct child automatically.

Group tables are writeable in both directions. When you UPDATE a parent group, the engine resolves exactly which child rows must change and applies the change atomically. If an update is ambiguous — for example, “set the average to 20” can’t be distributed unambiguously across rows — it is rejected with a clear SQL error instead of corrupting data.

Why it matters

Group tables remove a class of plumbing from your application: sharding decisions move into the schema, rollups become writeable, and related tables can be physically clustered so frequent joins run measurably faster.

• Writeable parents — update a parent row and the change propagates to the leaf table automatically.
• Declarative routing — route rows to children with a ROUTE clause instead of application code.
• Physical colocation — tables joined together can be clustered, making those joins measurably faster.

flowchart TB
  classDef parent fill:#1f2640,stroke:#7c5cff,color:#e8eaf0
  classDef child fill:#1f2640,stroke:#00d4ff,color:#e8eaf0
  classDef route fill:#1f2640,stroke:#4ade80,color:#4ade80

  W[UPDATE on parent group]:::parent --> L[Routing engine<br/>routes & propagates]:::route
  L -- safe --> C1[Child table A]:::child
  L -- safe --> C2[Child table B]:::child
  L -- ambiguous --> ERR[Update rejected<br/>with a clear error]:::route

How you use it

Declare a group table much like a regular table, plus the children it covers and any routing or colocation hints. From then on, applications read and write the group as a single object.

-- Define a group table over per-region order tables, with
-- declarative routing and a physical colocation hint.
CREATE GROUP TABLE orders (
  id        BIGINT,
  country   TEXT,
  amount    DECIMAL(12, 2),
  placed_at TIMESTAMP
)
CHILDREN (orders_in, orders_us, orders_eu)
ROUTE (
  WHEN country = 'IN' THEN orders_in,
  WHEN country = 'US' THEN orders_us,
  ELSE orders_eu
)
COLOCATE WITH customers BY (country);

-- Insert through the group — the row lands in the right child.
INSERT INTO orders VALUES (1, 'IN', 499.00, now());

-- Update through the group — the change reaches the correct child.
UPDATE orders SET amount = 529.00 WHERE id = 1;

Reads against orders transparently union the children with pushdown filters; writes are routed by the ROUTE rules. Any update whose target is not uniquely defined is rejected.

Background compaction (HTAP)

When the mixed transactional + analytical (HTAP) mode is enabled with enable_htap = true, writes land first in a fast mutable tier, and a background process promotes them into the immutable analytical tier. Queries read both tiers, so a row is visible immediately after it is written and gets the analytical layout once it has been compacted — you don’t trigger or wait for the promotion. HTAP is off by default; see the configuration matrix for the flag.

Notes & caveats

Tip

Use COLOCATE WITH on tables that are joined together frequently — those joins run measurably faster.

Ambiguous updates are refused, not guessed

The engine rejects — rather than silently approximating — updates whose target child is not uniquely defined. Treat the resulting error as a schema issue to fix, not noise to suppress.

Transaction Isolation →

All child updates from one parent write land in a single atomic commit.

Scaling & Concurrency →

Children scale and split independently as they grow.

Storage →

The columnar storage that colocation hints arrange data within.

Configuration & Availability →

The enable_htap flag and its default.