JoinKeys

QueryIndexingJoinsSchema

Stable On by default · production-ready

What it is

A JoinKey is a column you define from one or more other columns. When you declare one, Aetherius computes a single compact token from those columns for every row — automatically, as data is written — and stores it in a hidden column. You then join on that one token instead of matching several columns by hand.

It exists for one job: to make a multi-column relationship behave like a simple single-column foreign key, so it can ride the Graph Index navigational fast-path.

CREATE TABLE users (
  id         BIGINT,
  tenant_id  BIGINT,
  role_id    BIGINT,
  DEFINE JOINKEY tenant_role AS (tenant_id, role_id)
);

That’s the whole declaration. Every row now carries a tenant_role token derived from its (tenant_id, role_id) pair, and two rows get the same token exactly when both columns match.

Why it matters

• Multi-tenant & composite joins get the fast-path. Relationships keyed on (tenant_id, id) or (region, customer_id) are everywhere in real schemas. Join on a JoinKey and a CREATE GRAPH INDEX over it delivers the same navigational serve you’d get from a plain integer foreign key.
• Write the join once, not everywhere. Instead of repeating ON a.tenant_id = b.tenant_id AND a.role_id = b.role_id in every query, you join on one column. Less to get wrong, easier to read.
• Computed for you at ingest. You never populate or maintain the token — it is materialized as rows are written, and recomputed automatically if you update a source column.
• Invisible by default. A JoinKey is hidden from SELECT *, so it never bloats result sets or wire traffic. You only see it when you ask for it by name.
• Add it to tables that already have data. ALTER TABLE … ADD DEFINE JOINKEY backfills existing rows without rewriting your storage.

flowchart LR
  classDef col  fill:#1f2640,stroke:#7c5cff,color:#e8eaf0
  classDef key  fill:#1f2640,stroke:#00d4ff,color:#e8eaf0
  classDef fast fill:#1f2640,stroke:#4ade80,color:#4ade80

  A["tenant_id + role_id"]:::col --> K["JoinKey token<br/>(one column)"]:::key
  K --> G["Graph Index over the token"]:::key
  G --> F["Navigational serve<br/>on a multi-column relationship"]:::fast

Create a JoinKey

On a new table

Add a DEFINE JOINKEY clause inside CREATE TABLE. It lists the source columns, in order:

CREATE TABLE orders (
  id          BIGINT,
  region_id   BIGINT,
  customer_id BIGINT,
  total       BIGINT,
  DEFINE JOINKEY region_customer AS (region_id, customer_id)
);

A single-column JoinKey is just the one-element case — useful when you want a stable token name to index against:

DEFINE JOINKEY by_customer AS (customer_id)

On an existing table

Use ALTER TABLE to add a JoinKey to a table that is already full of data. The token is backfilled for the existing rows and computed inline for new writes — no downtime, no manual rebuild:

ALTER TABLE orders
  ADD DEFINE JOINKEY region_customer AS (region_id, customer_id);

Rules

• Source columns must be an integer axis — BIGINT or TIMESTAMP.
• List the columns in the same order on both sides of a relationship you intend to join ((region_id, customer_id) on one table pairs with (region_id, customer_id) on the other).
• Source columns must exist and be distinct within the list.
• Give it a name that’s unique on the table. A leading __join_ prefix is a common convention, but any valid identifier works.

Use a JoinKey

Read it explicitly

SELECT * skips a JoinKey. Ask for it by name to see the token:

SELECT id, region_customer FROM orders WHERE id = 42;

Join on it

Two tables that both define a JoinKey over the same source columns can join on the single token instead of matching every column:

CREATE TABLE fulfilments (
  id          BIGINT,
  region_id   BIGINT,
  customer_id BIGINT,
  DEFINE JOINKEY region_customer AS (region_id, customer_id)
);

-- One-column join across a two-column relationship:
SELECT o.id, f.id
FROM   orders o
JOIN   fulfilments f
  ON   o.region_customer = f.region_customer;

Index it for the fast-path

This is where JoinKeys pay off. Declare a Graph Index over the token columns, and the multi-column relationship gets the full navigational serve — a selective lookup answered without scanning either table:

CREATE GRAPH INDEX region_orders ON LINKS (
  customers(region_customer) -> orders(region_customer) CARDINALITY '1:N'
);

-- Served navigationally — the (region, customer) relationship on the fast-path:
SELECT c.name, o.id, o.total
FROM   customers c
JOIN   orders o ON c.region_customer = o.region_customer
WHERE  c.region_customer = <token>;

Composite links vs. JoinKeys

A CREATE GRAPH INDEX link can name several columns directly — for example orders(region_id, customer_id) -> …. That is validated and stored, but the navigational fast-path is reserved for single-column links today. To accelerate a multi-column relationship now, define a JoinKey over those columns and index the single token. It’s the supported, fast route.

Good to know

• Tokens stay in sync. Update a source column and the JoinKey is recomputed for that row on write — you never refresh it yourself.
• NULL in a source yields a null token, matching normal SQL join semantics (a null key doesn’t match). Prefer NOT NULL source columns for a JoinKey you’ll join on.
• It survives restart. The definition and the materialized tokens persist; after a restart the column is there and correct.
• Different token, different name. Two JoinKeys over the same columns on different tables must share the source-column order to join, but each table names its own JoinKey.

Graph Index →

The fast-path a JoinKey unlocks for multi-column relationships.

Advanced Indexing (PTI) →

The broader CREATE INDEX surface for single-table lookups and covering aggregates.

Schema as Code →

Version and apply table definitions, including JoinKeys, as code.

Graph-Relational Duality →

Query the same related data as tables or as a graph.