skaidb v0.320.9

Table placement, partitions and transactions

Part of Running a skaidb cluster.

Per-table replication factors and pins, partition co-location with distribute_by, the single-shard and cross-partition transactions built on it, and how foreign keys are enforced across members.

Per-table placement (RF overrides & pins)

The cluster RF is the default, not the law. Any table can override it at CREATE or later:

CREATE TABLE metrics_cold (PRIMARY KEY (id)) WITH (replication = 1)
CREATE TABLE hot_config  (PRIMARY KEY (k))  WITH (nodes = ['skai2'])
ALTER  TABLE metrics_cold SET (replication = 2)   -- online transition
ALTER  TABLE hot_config   SET (nodes = ['skai3']) -- online pin move
  • replication = n — ring placement at a per-table copy count (n >= member count behaves as a full copy, like cluster-wide RF does). Quorums for that table's reads and writes derive from its replica count, not the cluster default.

    Upgrade order matters. A node running a build from before per-table placement ignores the catalog's replication/nodes fields (unknown fields deserialize to the default) and places those rows at the CLUSTER RF instead. A mixed-version cluster therefore disagrees about which nodes own a row: writes land on one replica set and reads look on another, so a read can legitimately miss a row that was acked. Finish rolling every member onto the feature version before the first WITH (replication = …) / WITH (nodes = …) DDL — the same constraint class as any placement-affecting format change. Rolling back a member below the feature version after such a table exists has the same effect and needs the table reverted to cluster RF first.

  • nodes = ['<alias-or-id>', ...] — the whole table lives on exactly those members (aliases resolve to stable internode ids at DDL time; renames never move data). Every pin holds every row; a non-pin coordinator routes reads and writes to the pins. Pins are a durability trade the operator owns: a pinned node down means quorum errors for that table until it returns, and ALTER CLUSTER REMOVE NODE refuses to remove a pinned member until it is re-pinned away. Mutually exclusive with replication.
  • Changing placement is online. The ALTER opens a dual-placement window: the table's old and new placement are both live, and every read/write addresses the union — the per-table twin of the membership-change dual ring — so quorum reads stay correct while new owners are still empty. A background driver (the sorted union's first member) repairs until every member has completed a full anti-entropy pass that began after the change, then finalizes automatically. SHOW TABLES shows transition = true (the UI data tab shows → moving) while the window is open. One transition per table at a time; if the driver node is down the window just stays open — safe, merely wider than needed — and the operator escape is REPAIR CLUSTER followed by ALTER TABLE t SET (placement_finalized = true). After finalize, RECLAIM trims the copies the new placement no longer owns.
  • GLOBAL-index entry tables follow their base table's placement; system tables refuse placement options; sharded tables (RF below member count) are scatter-pulled and merged by witness nodes, so mirrors stay complete for any placement.

Partition co-location (distribute_by)

By default a row's ring position hashes its whole primary key, so two rows sharing a key prefix (a tenant and its records) land on unrelated replica sets once RF is below the member count. distribute_by places the table by the encoded prefix of its leading primary-key columns instead:

CREATE TABLE events (PRIMARY KEY (tenant_id, id))
WITH (replication = 1, distribute_by = (tenant_id))
  • Every row with the same distribute_by values lands on the same replica set — the whole partition is co-located. (Reads are served from any coordinator exactly as before; co-location changes where rows live, not how queries are planned.)
  • The column list must be a leading prefix of the PRIMARY KEY and is fixed at CREATE (ALTER refuses — changing it would re-home every row; recreate and copy instead).
  • Placement is the only thing that changes: quorums, repair, rebalancing and resharding all follow the prefix, and at RF ≥ member count the behavior is indistinguishable from whole-key placement.
  • Write partition columns with one consistent type: values that encode differently (1 vs 1.0) are different prefixes and land on different owner sets.
  • One skew trade-off to own: a partition is only as spread as its prefix cardinality — a single huge tenant concentrates on one replica set.
  • Search index scatter fast paths (sharded aggregations/top-k) decline on distribute_by tables and fall back to the row gather — correct, just not the sharded shortcut.

Single-shard transactions (BEGIN ATOMIC PARTITION)

Co-location is what makes multi-row atomicity affordable: because a whole partition shares one replica set, a transaction over it needs one consensus round, not two-phase commit. Over a binary-driver connection:

BEGIN ATOMIC PARTITION (tenant_id = 't1');
INSERT INTO org (tenant_id, id, name) VALUES ('t1', 1, 'acme');
INSERT INTO settings (tenant_id, id, theme) VALUES ('t1', 1, 'dark');
COMMIT;
  • Statements inside may touch only rows whose distribution columns equal the pinned literals — across any number of tables distributed by those columns. A statement that would touch another partition errors; the transaction stays open.
  • Writes buffer in the session (reads see the session's own overlay; other sessions see nothing until COMMIT).
  • COMMIT serializes on the partition through the same per-key consensus round the linearizable-UPDATE path uses, taken on the partition's row in a hidden per-database __txnlog__ manifest table whose committed value embeds the write set — that round is the atomic point. The rows then apply as ordinary replicated writes at the manifest entry's timestamp; if the coordinator dies mid-apply, every replica's sweeper completes the entry (recover-forward, idempotent), so a commit is never partial. The coordinator takes its entry's stamp (and, across partitions, the decision) from the value its round committed, never from a read-back: once the round commits, COMMIT applies and answers success. nothing was applied — retry is said only when the round itself was lost.
  • Isolation is atomicity only: all-or-nothing per commit, last-writer-wins between concurrent transactions and against non-transactional writes — the embedded engine's semantics, made cluster-safe. There is no read-set validation and no interactive locking.
  • UNIQUE indexes are enforced: COMMIT claims every value the write set introduces through the same single-key reservation round the plain write path uses — before the partition round, so a duplicate (against a committed row, a concurrent plain write, a rival transaction, or another row of the same write set) fails the whole transaction with nothing applied — and releases the values it retires after the apply.
  • Refused inside a transaction: DDL, USE, time-series writes, and writes to tables with GLOBAL indexes, value-TOAST (those write legs live on other replica sets) or a FOREIGN KEY on either side (the buffered apply cannot check the other side). The buffered write set is capped at 8 MB.

Cross-partition transactions (BEGIN ATOMIC)

A bare BEGIN ATOMIC opens a transaction with no pin: its statements may touch rows of any row tables — several partitions of distribute_by tables, plain replicated tables, any mix — with the same buffered overlay, the same all-or-nothing COMMIT, and the same refusals (DDL, USE, time-series writes, GLOBAL indexes, value-TOAST, foreign keys). UNIQUE indexes are enforced the same way. What changes is the commit:

BEGIN ATOMIC;
INSERT INTO org (tenant, id, name) VALUES ('t1', 1, 'acme');
INSERT INTO org (tenant, id, name) VALUES ('t2', 1, 'globex');
INSERT INTO audit (id, what) VALUES (1, 'two orgs');   -- a plain table
COMMIT;
  • The write set is grouped by placement group — the distribute_by prefix of a distributed table's rows, the whole key of a plain table's row — each of which lives on one replica set with its own manifest row. A transaction whose writes fall in one group commits in the single manifest round of the pinned form; more than one takes two-phase commit: every group's manifest first takes a prepared entry carrying that group's writes (one consensus round per group), then one consensus round on the transaction's decision row records commit — that round is the atomic point — and the rows apply at the shared stamp, flipping the entries to applied.
  • Recovery is the sweeper's, on every replica: a prepared entry is applied once its decision says commit and retired when it says abort; an entry still undecided a minute after its stamp has the sweeper record abort on the dead coordinator's behalf (presumed abort — a coordinator that decides late finds the abort and reports the transaction as retryable). A committed decision is therefore always carried out and an undecided prepare never applies.
  • Cost: one consensus round per touched placement group plus one for the decision, so a transaction over many plain-table rows pays a round per row. Isolation is atomicity only, as for the pinned form.

Foreign keys on a cluster

A FOREIGN KEY is enforced by the coordinator, wherever the rows live: a child write reads its parent at QUORUM before writing, a parent delete or key change reads the referencing children (through the constraint's supporting index) and applies the ON DELETE / ON UPDATE action to them as ordinary replicated writes. ADD CONSTRAINT and VALIDATE CONSTRAINT stream every child row cluster-wide and refuse the statement — nothing applied on any node — on the first violation; NOT VALID skips that scan. The constraints travel with the schema: a member that missed the DDL adopts them through anti-entropy schema sync, with the same last-writer-wins stamps as tables and indexes.

Statements racing on opposite sides of a constraint are serialized per referenced value through a hidden replicated guard table (__fkref__<child>__<constraint>, placed and replicated like the parent, one row per referenced tuple, updated by CAS). A child write claims the tuples it references before it probes the parent and releases them once its rows are acked; a parent delete or key change fences the tuple, waits for the claims in flight to drain, waits for the child's index maintenance to settle on every member, and only then runs its RESTRICT probe or CASCADE / SET NULL fan-out, lowering the fence when the statement completes. Child writes arriving behind a fence wait for it (long cascades refresh their fence every page). The outcome is the standalone one: a child insert racing a parent delete either lands before the delete and is seen by it, or is refused after it. Guard rows are transient — a statement that dies mid-flight (a coordinator crash) leaves a claim or a fence that the other side resets after a 10 s grace period; a child write whose put stalls longer than that between its claim and its release (a partition during the quorum round-trip) is the one shape the guard does not cover. The two counters skaidb_cluster_fk_guard_waits_total and skaidb_cluster_fk_guard_stale_resets_total say how often writers waited and how often dead state was reset; a steady rate of stale resets on a healthy cluster is a bug report. Two statements cascading into each other's tables in opposite orders can wait on each other's fences; the statement deadline (storage.statement_timeout_secs) resolves that, as it does a CAS livelock.

Rows the guard does not see: ADD CONSTRAINT / VALIDATE CONSTRAINT scan existing children without fencing, so a child insert racing the validation of a not-yet-valid constraint is checked by the insert alone; writes that bypass the executor — internode repair, resync and PITR replay — reproduce rows as they were written and do not re-check the constraint. VALIDATE CONSTRAINT finds any orphan either leaves behind.