Tables and queries in a cluster

A cluster runs ordinary Postgres SQL. What changes is where each statement runs, and that follows from one choice per table: whether it is distributed, and by which column.

Choosing a distribution key

Pick the column your queries filter on most, the one most of your work belongs to: usually the account, tenant, customer or device id. Rows with the same value always live together on one node.

A good key:

• Appears in most queries and writes, so they go to one node. where account_id = $1 is the shape a cluster runs fastest.
• Is shared by the tables you join. An account's orders, invoices and events, all distributed by account_id, are colocated, so a join between them on account_id stays on one node.
• Has many values, spread fairly evenly. Ten thousand accounts spread well over eight nodes; ten regions do not. One value much bigger than the rest can be pinned to a node of its own.
• Never changes for a row. An UPDATE cannot change a row's key; delete the row and insert it again.

Supported key types are the integer types, text and varchar (UTF-8 databases with deterministic collations), uuid, bytea (Postgres 18 nodes), and the other types Lepis hashes as Postgres does. The key may not be NULL.

The rules a distributed table follows

• A primary key or unique constraint must include the distribution key. Each node can only check its own rows. primary key (account_id, order_id), not primary key (order_id).
• A foreign key may point at a colocated table (on the key) or a reference table, never at a table that lives on another node.
• Generate ids with uuidv7() or a sequence striped by node, never a plain serial or identity: sequences are per node, so two nodes would hand out the same values.
• Small tables that distributed tables join to should be reference tables, copied to every node. A join with a reference table is always local. A write to one is made on every node at once.

Where a statement runs

Statement	Where it runs
Names its key: where account_id = 42, = $1, in (1, 2), insert … values with the key	the node that owns that key
Touches only global tables	the home node
A join of colocated tables on their key, with any reference tables	one node when the key is named; otherwise every node, merged
A read that names no key	every node, and the answers merged
insert … values or copy … from stdin with rows for several nodes	each row to its node, committed on all of them or none
update or delete that names no key	every node, committed on all of them or none
A transaction	the node it started on, until it touches a second, then all of them, committed through two-phase commit
listen, notify, show	the home node, or the node of the transaction it is in
set	replayed on every node the session reaches; set local on the transaction's node

Reads across nodes merge ORDER BY, LIMIT and OFFSET, count, sum, min, max, avg and the other plain aggregates, GROUP BY, HAVING and DISTINCT. Each node is asked only for the rows it owns, so a range in the middle of a move is never counted twice. A read across nodes sees each node at its own moment, not one snapshot of all of them; within one node it is full Postgres.

EXPLAIN runs where its statement would; for a read across nodes, in text format, each node explains its part.

What is refused, and what to do instead

Lepis refuses what it cannot run correctly, before anything is sent, with SQLSTATE 0A000, a message saying why and a hint saying what to do. It never guesses. The ones you are most likely to meet:

Refused	Instead
A join of distributed tables that are not colocated, or not joined on the key	join colocated tables on their key, or make the smaller table a reference table
A global table combined with a distributed one	make the global table a reference table, or query it separately
insert … select across nodes	read the rows, then write them with insert … values or copy
An UPDATE that changes the key	delete the row and insert it with the new key
SERIALIZABLE in a transaction across nodes	REPEATABLE READ or READ COMMITTED, or keep the transaction to one node
Savepoints, PREPARE TRANSACTION or COMMIT AND CHAIN in a transaction across nodes	keep that transaction to one node's keys
DISTINCT ON, window functions, or GROUP BY on an output alias across nodes	name the key so it runs on one node, or compute it in the application
Fetching a read across nodes a few rows at a time	read it whole, or page it with LIMIT and a key
A key compared with a value of another type	compare it with a value of the key's type, or bind it in text format

Roles and row-level security

Each node session runs as the client's own role, so grants and row-level security work as on one Postgres, checked by the node that holds the row. In SnoutData Cloud roles are kept the same on every node for you. Running Lepis yourself, see roles.