skaidb v0.339.4

skaidb query syntax

The SQL surface skaidb accepts. It's a subset of SQL — one statement per call (joins, UNNEST, subqueries, CTEs, UNION, aggregates, window functions, prepared statements and transactions are supported; the command index lists every statement, and Not part of the SQL surface what is not). Rows are schema-less documents keyed by a declared primary key; any field not present reads as NULL.

Maintenance: this document is the source of truth for the query language. Whenever the parser/grammar changes — a new statement, clause, operator, literal form, function, or type — update this file in the same change. Each statement has one section — its synopsis, its notes, then See also links — and one entry in the command index; a new statement gets both.

Statements

Every statement skaidb accepts, grouped by what it works on. Each section opens with the statement's synopsis, then its notes, then See also links to the statements it works with. Expressions, the lexical rules and types follow the statements.

In a synopsis, [ … ] is optional, { a | b } picks one, ... (or …) repeats what precedes it, <name> is a placeholder, and -- starts a comment.

A <table> reference may be qualified by a database: <database> . <table> (e.g. shop.orders). An unqualified table resolves against the connection's current database (see CREATE DATABASE).

Tables

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] <table> (PRIMARY KEY (<col> [, <col> ...])
       [, <col> [NOT NULL] [DEFAULT <expr>] [CHECK (<expr>)]]  -- column item (no type: see Column constraints)
       [, <col> {SERIAL | BIGSERIAL | GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(<seq-option> ...)]}
              [NOT NULL] [CHECK (<expr>)]]           -- auto-increment column (owned sequence)
       [, <col> GENERATED ALWAYS AS (<expr>) {STORED | VIRTUAL} [NOT NULL] [CHECK (<expr>)]]
                                                     -- generated column (NOT NULL: STORED only)
       [, [CONSTRAINT <name>] CHECK (<expr>)]
       [, [CONSTRAINT <name>] EXCLUDE [USING <method>] (<col> WITH { = | && | <> } [, ...]) [WHERE (<predicate>)]]   -- ranges are [lo, hi) arrays
       [, [CONSTRAINT <name>] FOREIGN KEY (<col> [, ...]) REFERENCES <parent> (<col> [, ...])
            [ON DELETE {RESTRICT | NO ACTION | CASCADE | SET NULL}]
            [ON UPDATE {RESTRICT | NO ACTION | CASCADE | SET NULL}] ...])
       [WITH (ttl = <duration>,               -- rows expire after this age
              memory = <bool>,                -- RAM-only: no WAL, empty after a restart
              witness = <bool>,               -- mirror to witness nodes (default true)
              insert_conflict = 'replace' | 'error', -- plain INSERT of an existing
                                              -- key: replace it (default) or fail
              replication = <n>,              -- per-table RF override
              nodes = ['<alias-or-id>', ...],  -- pin the whole table to these members
              toast_threshold = <bytes>,      -- store big fields out-of-line (value-TOAST)
              partition_by = 'range(<col>, <interval>)', -- range partitioning (see Range partitioning)
              cluster_by = (<col> [, ...]),   -- physical sort + identity (see Clustering)
              distribute_by = (<col> [, ...]))] -- place by this leading PK prefix:
                                              -- rows sharing it share a replica set
                                              -- (fixed at CREATE)

CREATE TABLE declares the primary key and constraints — there is no typed column list; documents are schema-less. A composite PK lists several columns. A column item (name NOT NULL DEFAULT 'x' CHECK (…)) constrains a field rows may carry, never its type or existence. A type after the column name is accepted only when it names an enum or domain declared with CREATE TYPE … AS ENUM or CREATE DOMAIN (name TEXT NOT NULL is refused: type 'TEXT' does not exist) — see Column constraints.

The WITH (…) options each have their own notes: ttl (Row expiry), memory (Memory tables), witness (Witness mirroring), insert_conflict (Existing primary keys), replication and nodes (Per-table placement), toast_threshold (Value TOAST), partition_by (Range partitioning), cluster_by (Clustering) and distribute_by (CLUSTER_PLACEMENT.md). So do the column items — constraints, generated columns, SERIAL and identity columns, enum types and domains — and the table-level FOREIGN KEY and EXCLUDE items.

Memory tables

WITH (memory = true) makes the table RAM-only: no write-ahead log fsyncs, never flushed to disk, empty after a restart, and skipped by repair/reshard data motion — for short-lived bounded data (node stats, caches); pair with a ttl. Indexes on memory tables are not supported.

Row expiry (TTL)

WITH (ttl = <duration>) makes rows expire: a row older than the TTL (measured from its write's HLC timestamp) becomes invisible to every read and is physically dropped at the next compaction. TTL is a read-visibility rule applied uniformly on every replica (the stamped data still replicates, so expiry converges regardless of which node serves the read). Useful for caches, sessions, and rolling event windows.

TTL is live-tunable: ALTER TABLE t SET (ttl = 30d) applies to reads immediately — shortening it can expire existing rows at once, and because expiry is lazy, widening or clearing it (ttl = 0) un-expires rows compaction hasn't physically reclaimed yet.

Visibility is immediate; space comes back shortly after. Flush is size-triggered, so a low-write expiring table would otherwise hold every expired row in memory until it happened to cross the flush threshold — a background sweep flushes and compacts such tables every storage.ttl_reclaim_interval_secs (900 by default, 0 disables). Size an expiring table for its TTL plus about one sweep interval, not for its TTL exactly. A reclaimed row's index entries (secondary, geo, full-text, vector) are removed with it by the background maintenance tick; an expired row not yet reclaimed keeps them (reads skip it).

See also ALTER TABLE · DROP TABLE · CREATE TABLE … AS · SHOW CREATE TABLE · DESCRIBE · CREATE TIMESERIES TABLE

Column constraints

NOT NULL, DEFAULT <expr> and CHECK (<expr>) are declared per column in CREATE TABLE (qty NOT NULL DEFAULT 1 CHECK (qty >= 0), the three in any order), as table-level [CONSTRAINT <name>] CHECK (<expr>) items, or later with ALTER TABLE … ADD [CONSTRAINT <name>] CHECK (…) [NOT VALID] and ALTER TABLE … ALTER [COLUMN] <col> SET | DROP NOT NULL | DEFAULT. A column is a top-level field or a dotted path (meta.lang); the store stays schema-less, so an item says nothing about the field's type or existence.

NOT NULL requires the column to be present and non-NULL in every row that lands — a schema-less row that never mentions the field counts as NULL — so INSERT INTO t (id) … on a table with name NOT NULL is refused. SET NOT NULL scans the existing rows first and refuses when any violates; there is no NOT VALID form (use CHECK (c IS NOT NULL) NOT VALID).

DEFAULT is applied on INSERT only, to a column the statement's column list does not name (an explicit NULL is named and gets no default), and before the key is computed; never on UPDATE. The expression is evaluated per row against an empty row — now() and nextval(…) are per-row — so it may not reference columns, aggregates, windows or parameters (refused at DDL time), and an unknown function is refused at DDL time too.

CHECK is evaluated against the final row: false fails the write, true or NULL (an unknown, e.g. an absent column) passes — SQL semantics; a non-boolean result is a type error. Like DEFAULTs, CHECKs may not use aggregates, windows or parameters.

NOT VALID leaves existing rows unchecked until VALIDATE CONSTRAINT finds them clean; new writes are always enforced. UPDATE is gated on each computed row, and an INSERT … ON CONFLICT DO UPDATE checks the proposed row before conflict resolution and then the merged row (Postgres semantics: an upsert that omits a NOT NULL column fails even when the row already exists). A violating statement writes nothing (every row is checked before the first is written).

Errors: not null violation: column "c" of table "t" (condition NOT_NULL_VIOLATION) and check violation: constraint "n" of table "t": <col> = <value>, … (condition CHECK_VIOLATION), both in the Constraint error class.

Names: a column-level CHECK is chk_<table>_<col>, an unnamed table-level one chk_<table> then chk_<table>_<n>; CHECKs and FOREIGN KEYs share one namespace per table, and VALIDATE / DROP CONSTRAINT find either. RENAME COLUMN rewrites column items; a CHECK whose expression references the column refuses the rename (drop and re-add it).

SHOW CONSTRAINTS [ON <table>] lists every constraint of every kind in one view (constraint, table, type, columns, valid, definition; the name and columns cells are NULL where they do not apply), SHOW CREATE TABLE prints the column items and valid CHECKs inline and NOT VALID CHECKs as trailing ALTER TABLE … ADD CONSTRAINT … NOT VALID rows.

On a cluster enforcement is identical on every coordinator; ADD CHECK, VALIDATE CONSTRAINT and SET NOT NULL scan every member's rows at QUORUM before the DDL broadcasts, and the constraints travel with schema sync. Partition children enforce the parent's constraints.

See also CREATE TABLE · ALTER TABLE · Enum types and domains · Generated columns · SHOW CONSTRAINTS

Generated columns

A column item c GENERATED ALWAYS AS (<expr>) STORED (total GENERATED ALWAYS AS (qty * price) STORED) is computed from the row that is about to land — after DEFAULTs, after an UPDATE's assignments, after the ON CONFLICT DO UPDATE merge — and overwrites whatever the column held, on every write path (INSERT, UPDATE, upsert, the cluster's per-key rounds); RETURNING sees the value, indexes and filters treat it as an ordinary stored field. A NULL result is stored as NULL (a NOT NULL on the column then refuses the write) and an evaluation error fails the write.

c GENERATED ALWAYS AS (<expr>) VIRTUAL stores nothing: the expression is evaluated wherever the column is read — the item list (bare, aliased, inside an expression, and through *, which lists the virtual columns after the stored fields), WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT, aggregates and window functions, either side of a join (a.total, or bare — the first source in FROM order that declares it), views and subqueries over it, RETURNING, and an UPDATE's or DELETE's WHERE and assignment expressions. Every reference becomes the expression before the statement is planned, so a filter on the column plans like a filter on the expression, an index on the column is an expression index over its expression (CREATE INDEX i ON t (total) is … ON t ((qty * price)) in SHOW INDEXES and schema sync, and serves WHERE total = 5; an expression component or partial-index predicate reading the column expands the same way; a multikey [] on it is refused), a CHECK reading it judges the expression, and every write strips any field of that name from the landing row. A computed column has no sub-fields (total.x is refused), cannot be NOT NULL (nothing is stored to check) and cannot be a NEAREST column.

Explicit writes to either kind are refused: INSERT naming the column (cannot insert a non-DEFAULT value into column "c" of "t": it is a generated column; the DEFAULT element is allowed and OVERRIDING SYSTEM VALUE does not apply), UPDATE … SET c and ON CONFLICT DO UPDATE SET c (column "c" of "t" can only be updated to DEFAULT: it is a generated column).

The expression may read the row's other columns (dotted paths too) — other generated columns included, STORED or VIRTUAL: STORED columns are computed in dependency order whatever their declaration order, a VIRTUAL column expands through the VIRTUAL columns it reads, and a cycle (a reads b reads a, through any length) is refused at CREATE TABLE, ADD GENERATED and SET EXPRESSION, naming it (generated column expressions form a cycle: a -> b -> a) — and nothing else: no aggregates, window functions or bind parameters (as CHECK), no nextval / setval, no now(), no excluded., not itself. A generated column cannot be a PRIMARY KEY, distribute_by, cluster_by or partition column, and carries no DEFAULT or identity; NOT NULL (STORED only) and CHECK combine freely.

ALTER TABLE t ALTER COLUMN c ADD GENERATED ALWAYS AS (<expr>) STORED makes an existing column generated and rewrites every row (through the ordinary UPDATE path — cluster-wide from the coordinator that ran the DDL; a row the recomputed value fails a constraint on fails the ALTER after the schema change, so the next write judges it again); … ADD GENERATED ALWAYS AS (<expr>) VIRTUAL touches no row and is refused while any row carries the column (column "c" of "t" holds a value in an existing row; checked cluster-wide before the broadcast, by a read) or the column is NOT NULL; … SET EXPRESSION AS (<expr>) replaces the expression, the column keeping its kind (refused on a column that is not generated; a STORED column's rows are rewritten, a VIRTUAL column's are not); … DROP EXPRESSION [IF EXISTS] makes the column ordinary — a STORED column's values stay in place, a VIRTUAL column then reads NULL, since nothing was stored. A generated column may read a column that later loses its own expression: it keeps reading the values.

RENAME COLUMN is refused while a generated expression references the column; renaming the generated column itself keeps its expression. SHOW CONSTRAINTS reports one GENERATED row per generated column whose definition is the column item, … STORED or … VIRTUAL (a NOT NULL on it is its own row); SHOW CREATE TABLE prints the item inline; DESCRIBE lists every generated column with generated stored / generated virtual in its key cell (an index over a VIRTUAL column's expression is attributed to the column). The expression and kind travel with schema sync. A witness mirror replays VIRTUAL columns (no row is touched, so the mirror computes the same values from the primary's rows) and never STORED ones — see CLUSTER_WITNESSES.md.

skaidbsh export writes a STORED column's values as data and DROP EXPRESSION IF EXISTS + ADD GENERATED … (with the kind) into constraints.sql; import drops the column from the rows and the ADD recomputes a STORED one. Not offered: generated key columns.

See also CREATE TABLE · ALTER TABLE · Partial and expression indexes · Column constraints · DESCRIBE

FOREIGN KEY

FOREIGN KEY (<cols>) REFERENCES <parent> (<cols>) — declared inline in CREATE TABLE after the primary key, or added later with ALTER TABLE … ADD [CONSTRAINT <name>] FOREIGN KEY … — makes the child's column tuple point at an existing parent row. The referenced columns must be the parent's primary key (its full key, including any cluster_by columns) or exactly the columns of one of its UNIQUE indexes; arity and order must match.

A child row whose tuple has no parent is refused with a foreign key violation error (procedures catch it as FOREIGN_KEY_VIOLATION) and the whole statement writes nothing. A tuple with any NULL or absent column is unconstrained (MATCH SIMPLE).

The parent side acts per clause: RESTRICT / NO ACTION (the default) refuses deleting a referenced parent row or changing its referenced tuple; CASCADE deletes the children with the parent, or rewrites their tuple to the parent's new one; SET NULL nulls the children's columns. Actions chain through several levels, a violation anywhere down the chain fails the whole statement, and a chain deeper than 16 levels is refused. Self-references are allowed — a tree inserted in one multi-row INSERT satisfies itself whatever the row order.

Both tables must be regular row tables in the same database: the parent cannot be a time-series, memory, TTL or range-partitioned table, the child cannot be memory or partitioned, and constraint columns are top-level fields. The default constraint name is fk_<table>_<col>[_<col>…].

The child's probes need a path to its rows by the constraint's columns: the child's own primary key when the columns are that key (SHOW FOREIGN KEYS then reports the support as (primary key) and no index is created), else an existing local index whose leading paths are the columns is reused, otherwise <constraint>_idx is created with the constraint (and dropped with it); an index a constraint depends on — on either side — cannot be dropped, nor can a parent table while another table references it.

ADD CONSTRAINT checks every existing row unless NOT VALID is given, in which case only new writes are enforced until VALIDATE CONSTRAINT finds the data clean and marks it valid. RENAME TO / RENAME COLUMN rewrite the definitions on both sides. Inside a BEGIN … COMMIT transaction the checks see the transaction's own writes.

On a cluster the constraint is enforced cluster-wide: the coordinator checks the parent (or the referencing children) at QUORUM wherever the rows live, then writes, and ADD CONSTRAINT / VALIDATE CONSTRAINT scan every member's rows before the DDL broadcasts — a stray child anywhere fails the statement and nothing is applied. A member that missed the DDL picks the constraint up through schema sync. A violating statement writes nothing on a cluster either (every row is checked before the first is written).

The rows of a passing statement land in batches on a table that is a child only (no table references it, no EXCLUDE), its parent probes batched ahead of them — a few multi-key reads per statement rather than one round trip per row, and the same for ADD CONSTRAINT / VALIDATE CONSTRAINT; an UPDATE of such a table that assigns none of its constraint columns writes like any table's. On a table that is also a parent (or carries an EXCLUDE) the rows land one at a time, so a mid-statement failure of another kind can leave the earlier rows written. Tables on either side of a foreign key are refused inside BEGIN ATOMIC PARTITION.

Concurrent statements on opposite sides of a constraint (a child insert against a parent delete) are serialized per referenced value through a hidden guard table, so the outcome matches a single writer's; a coordinator that dies mid-statement leaves guard state that ages out after 10 s (see docs/CLUSTER_PLACEMENT.md).

See also CREATE TABLE · ALTER TABLE · UNIQUE indexes · EXCLUDE constraints · SHOW FOREIGN KEYS · CLUSTER_PLACEMENT.md

EXCLUDE constraints

Generalized uniqueness over ranges: CONSTRAINT no_double_booking EXCLUDE (room WITH =, during WITH &&) refuses a row whose = elements equal another row's, whose && elements all overlap it and whose <> elements all differ from it (exclusion violation: constraint "…" of table "…" — … conflicts with row …).

There are no range types: a range is a two-element array [lo, hi) ([10, 20], ['2026-01-01', '2026-02-01'], [NULL, 5]), and && is an ordinary operator usable anywhere.

At least one WITH = element and one WITH && or WITH <> element are required (plain equality is a UNIQUE index); <> says the rows sharing the = tuple must agree on that column (EXCLUDE (project WITH =, owner WITH <>) = one owner per project).

A row with a NULL, absent or non-range element is exempt, like NULLs under UNIQUE, and so is a row outside the optional WHERE (<predicate>) (… WHERE (cancelled = false): a cancelled booking neither conflicts nor is conflicted with; the predicate follows the CHECK rules and NULL counts as outside).

USING gist / USING <method> is accepted and ignored — the access method Postgres tools emit. Rows of one INSERT are checked against each other too.

Enforced on the FOREIGN KEY write path: the rows sharing the = tuple are probed through a supporting index over the = columns (an auto <constraint>_idx, or an existing index — dropped with the constraint like a FOREIGN KEY's), and on a cluster two coordinators racing the same = tuple serialize through the constraint's guard table (__fkref__<table>__<name>, the FOREIGN KEY fence), so the second writer probes after the first row landed; the probe itself reads at the session consistency.

ALTER TABLE … ADD … EXCLUDE validates existing rows (cluster-wide, before the broadcast) unless NOT VALID, which enforces new writes only until ALTER TABLE … VALIDATE CONSTRAINT scans them (under the scan budget, cluster-wide) and marks it valid — the valid column of SHOW CONSTRAINTS; DROP CONSTRAINT removes it, SHOW CONSTRAINTS lists it as EXCLUDE, and it replays through SHOW CREATE TABLE, schema sync and the dump as ALTER TABLE … ADD CONSTRAINT … EXCLUDE (…) [WHERE (…)] [NOT VALID].

A witness mirrors it NOT VALID like a FOREIGN KEY or CHECK. Not offered: operators beyond =, && and <>, DEFERRABLE.

See also CREATE TABLE · ALTER TABLE · FOREIGN KEY · UNIQUE indexes · Operators

Clustering

WITH (cluster_by = (<col> [, ...])) stores rows sorted by the clustering values, and folds them into row identity: a row is identified by (cluster_by…, PRIMARY KEY), exactly like Cassandra's clustering columns. What that buys and costs:

  • Range predicates on a clustering prefix seek instead of scanning (WHERE ts >= a AND ts < b on cluster_by = (ts) reads only the matching key range — the scan budget then counts only those rows), and ORDER BY a clustering prefix walks in stored order.
  • GROUP BY a clustering prefix streams: when the GROUP BY columns are (any order of) a leading prefix of cluster_by, and every aggregate folds (COUNT/SUM/AVG/MIN/MAX, plain args), the groups are merged straight off the presorted key walk — a k-way merge over the partition children, replicas and shards that holds ONE open group instead of a group map, so the result's cardinality never touches the byte budget. ORDER BY a leading run of those columns (ascending) is the walk's own order and stops at OFFSET + LIMIT; an ORDER BY on an aggregate re-sorts the streamed groups (a bounded top-k under a LIMIT). The generic fold answers instead when the filter is served by a secondary index, a point read or a geo index (the merge needs a key-order walk), inside an open transaction, during a cluster_by adoption, and on a cluster gather over partition children whose partition column is not the clustering head (see CLUSTER_CONSISTENCY.md). EXPLAIN reports the decision in its group aspect.
  • Same-tuple re-ingest is idempotent: replaying a row with the same clustering values and key overwrites in place — the at-least-once pipeline case. A row with the same PRIMARY KEY but a different clustering value is a new row by definition.
  • An UPDATE that changes a clustering value moves the row (put-new-then-delete-old — a failure between the two leaves a recoverable duplicate, never a lost row). Clustering columns must be present and non-null on every write, cannot be renamed, and cannot overlap the PRIMARY KEY.
  • A bare-PK lookup (no clustering bound) is a scan by design — declare a secondary index on the key column where needle lookups matter. cluster_by cannot combine with distribute_by or toast_threshold. Composes with partition_by (put the partition column at the clustering head). Declared at CREATE, or adopted later with ALTER TABLE t SET (cluster_by = (c1 [, c2 …])) — an ONLINE re-key on a standalone server, an embedded engine, or a cluster when the table is a full copy on every member (replication factor at least the member count, no pins; a sharded table refuses — the new key would move rows between members): the table's rows move under a legacy child while a fresh engine takes the new layout, reads union both, writes land in the new layout and retire the old copy, and a background drain re-keys the rest (inline for an embedded engine). On a cluster, reads serve from the legacy child until the cutover, every write lands in both layouts, the member owning the table's name copies the merged legacy view into the new layout with each row's original stamp, and a replicated drop of the legacy child completes the swap everywhere; rows missing a clustering column stay in legacy, served but never re-keyed. Refused with partition_by, distribute_by, toast_threshold, memory, any index, streams / triggers or a FOREIGN KEY on either side — recreate those after the drain finishes (SHOW PARTITIONS / the log report it).

See also CREATE TABLE · ALTER TABLE · Range partitioning · Concurrency and atomicity · CREATE ROLLUP

Range partitioning

SHOW PARTITIONS <table>                 -- a range-partitioned table's partitions:
                                        -- start/end, rows, disk bytes, serving

WITH (partition_by = 'range(<col>, <interval>)') splits a regular table into non-overlapping ranges of <col> (integer or timestamp values, typically epoch-ms; <interval> is a duration literal: 15min, 1h, 1d, 1w). Rows land in a hidden per-interval partition, created lazily on first write; every row must carry the partition column (a row without it is refused). What it buys:

  • Partition elimination: a WHERE bound on the partition column scans only the overlapping partitions — scan_row_budget then counts rows after pruning, so ranged reads on large event tables stay under the guardrail instead of tripping it.
  • O(1) retention: with ttl, expired partitions drop whole (a metadata + directory delete — no per-row tombstones, no compaction debt). Retention is partition-granular: a partition serves reads until its entire range is older than the ttl, so rows can outlive a pure row-ttl by up to one interval; a fully-expired partition never serves reads even before the physical drop.
  • No out-of-order horizon: a late row lands in the partition its column value names, however late it arrives.
  • Per-child parallel folds: an aggregate / streamed projection over several surviving partitions folds them on parallel workers (children dealt into contiguous groups, merged in child order — the result is exactly the serial walk's), and a filtered count(*) a secondary index covers is the sum of the children's index counts (the parent's index fans to one physical index per child), decoding no rows.

Duplicates on the partition column are first-class (append semantics); identity stays with the PRIMARY KEY. The partition column is part of row placement: keep it immutable per key — rewriting a key with a different partition value files the new version in the new partition and leaves the old one behind (like a re-key).

On a cluster, the parent's DDL replicates and every member creates the same per-interval children lazily as writes apply (deterministic names — no extra coordination); retention runs as replicated DROP TABLE DDL on a background cadence, and reads hide fully-expired partitions regardless.

Indexes work: CREATE INDEX ON <parent> keeps a template on the parent and fans a physical index to every partition (new partitions inherit automatically; the needle path — trace_id-style equality lookups — prunes through them); DROP INDEX fans the removal, and a partition's drop cleans its indexes. Search, vector and geo indexes fan the same way (CREATE SEARCH INDEX / CREATE VECTOR INDEX … DIM n / CREATE GEO INDEX on the parent): a MATCH() query ranks across the partitions' indexes with merged BM25 statistics (one corpus-wide IDF, not one per partition), NEAREST merges the per-partition neighbours by distance, a geo predicate prunes through each partition's index, ALTER … INDEX SET and DROP fan, and SHOW INDEXES lists the parent's index once. Search aggregation and fast-field-sorted pushdowns decline on a partitioned parent (the row path answers the same query exactly).

Introspection: SHOW PARTITIONS <table> lists each partition's range, live rows, disk bytes and whether reads still serve it; EXPLAIN SELECT … reports partitions_total / partitions_scanned / partitions_pruned.

Current restrictions (refused loudly, not half-applied): no UNIQUE or global indexes (uniqueness would only hold per partition), no streams or triggers on a partitioned table; no memory, toast_threshold or distribute_by combinations; not usable inside BEGIN ATOMIC PARTITION; AFTER keyset paging pages with a WHERE range instead.

Pick the interval so the retention window holds tens of partitions (e.g. 1d over a 90-day ttl).

Adopting on an existing table: ALTER TABLE <t> SET (partition_by = 'range(<col>, <interval>)') moves a table to partitioning without re-ingest — the current data becomes a hidden legacy partition (a directory rename, no copy) and a background drain relocates rows into their range partitions with their original versions; reads stay complete throughout (children + legacy), writes during the drain land in their range partition and retire the pre-adoption copy, and the legacy partition drops once empty. On clusters the adoption is replicated and fully online: reads serve from the legacy partition until the cutover, every write lands in both the legacy partition and its range partition, the member owning the table's name copies the merged legacy view into the range partitions with each row's original stamp, and a replicated drop of the legacy partition cuts over; a standalone server is online too — the ALTER returns once the layout is switched and a drainer thread pages the legacy partition away (brief write locks per page); only an embedded engine completes the move within the ALTER statement. Rows missing the partition column cannot be placed: they stay in the legacy partition (still served, listed by SHOW PARTITIONS with NULL bounds) until an UPDATE gives them the column. The table must first shed its indexes, streams and triggers (recreate indexes after — they then fan per partition).

See also CREATE TABLE · ALTER TABLE · Clustering · CREATE ROLLUP · Row expiry (TTL)

Value TOAST

ALTER TABLE <t> SET (toast_threshold = <bytes>) enables value-TOAST: on every subsequent write, a top-level field whose encoded value is at least the threshold is stored out-of-line in a hidden replicated companion table and the row keeps a small marker. Scans that do not project the field skip the marker instead of reading, decrypting and decompressing the payload — the win for wide rows (multi-KB JSON blobs) under narrow dashboards/aggregations. Reads that DO want the field resolve it transparently (one extra point read per row) — a WHERE, ORDER BY … LIMIT or DISTINCT on a toasted field included, and a search hit comes back with its toasted fields; filtering on a toasted field costs that point read for every row examined, so keep frequent filters on inline or indexed fields. Applies to NEW writes only; existing rows stay inline until rewritten. Fields covered by any index (secondary, global, search, vector, geo) and primary-key columns never toast — and creating a new index on a possibly-toasted field is refused (set toast_threshold = 0 and rewrite rows first). 0 disables future splits; already-toasted rows remain readable.

See also CREATE TABLE · ALTER TABLE · CREATE INDEX

Witness mirroring

ALTER TABLE <t> SET (witness = <bool>) toggles witness mirroring for an existing table. WITH (witness = false) at CREATE (or the ALTER form) excludes the table from witness pulls: witnesses skip it entirely (rows already mirrored are kept but stop updating), and the table stops holding back tombstone GC for lagging witnesses. Toggling back to true re-includes it on the next full sweep. System/registry tables (witnesses, drivers, node_aliases, ...) refuse placement/witness options — every node consults them locally.

See also CREATE TABLE · ALTER TABLE · CLUSTER_WITNESSES.md

Per-table placement

replication = <n> overrides the cluster replication factor for one table (n above the member count = full copy, the same semantics cluster-wide RF has); nodes = ['<alias-or-id>', ...] pins the WHOLE table to an explicit member set (entries accept node aliases as sugar, resolved to stable ids at DDL time; a reference that is not a current member is refused, and witnesses can never be pinned) — every pin holds every row, quorum math counts the pins, and a non-pin coordinator routes reads and writes to them. The two are mutually exclusive. Pins are a deliberate durability trade: a pinned node down means quorum errors for that table until it returns, and ALTER CLUSTER REMOVE NODE refuses to remove a pinned member (re-pin first with ALTER TABLE t SET (nodes = [...])). GLOBAL-index entry tables follow their base table's placement.

Placement transitions

ALTER TABLE t SET (replication = n) / SET (nodes = [...]) change placement ONLINE. The old placement is kept alongside the new one and every read/write addresses the UNION of both (the per-table twin of the membership change's dual-ring window), so quorum reads stay correct while new owners are still empty. A background driver (the sorted union's first member) runs repair until every member has completed a full anti-entropy pass that started after the change, then finalizes automatically; SHOW TABLES shows transition = true while the window is open. One transition per table at a time. If the driver is down the window just stays open (safe, wider than needed) — the operator escape is REPAIR CLUSTER followed by ALTER TABLE t SET (placement_finalized = true). After finalize, RECLAIM trims copies the new placement no longer owns. Shrinking a pin set needs no window (remaining pins already hold full copies) and applies immediately.

See also CREATE TABLE · ALTER TABLE · SHOW TABLES · Cluster membership and repair · CLUSTER_PLACEMENT.md

ALTER TABLE

ALTER  TABLE <table> RENAME TO <new_table>
ALTER  TABLE <table> RENAME COLUMN <from> TO <to>
ALTER  TABLE <table> ADD [CONSTRAINT <name>] FOREIGN KEY (<col> [, ...])
       REFERENCES <parent> (<col> [, ...]) [ON DELETE <action>] [ON UPDATE <action>]
       [NOT VALID]                           -- skip checking existing rows
ALTER  TABLE <table> ADD [CONSTRAINT <name>] CHECK (<expr>) [NOT VALID]
ALTER  TABLE <table> ADD [CONSTRAINT <name>] EXCLUDE [USING <method>] (<col> WITH { = | && | <> } [, ...])
       [WHERE (<predicate>)] [NOT VALID]     -- checks existing rows unless NOT VALID
ALTER  TABLE <table> VALIDATE CONSTRAINT <name>   -- FOREIGN KEY, CHECK or EXCLUDE
ALTER  TABLE <table> DROP CONSTRAINT [IF EXISTS] <name>
ALTER  TABLE <table> ALTER [COLUMN] <col> SET NOT NULL     -- checks existing rows first
ALTER  TABLE <table> ALTER [COLUMN] <col> DROP NOT NULL
ALTER  TABLE <table> ALTER [COLUMN] <col> SET DEFAULT <expr>
ALTER  TABLE <table> ALTER [COLUMN] <col> DROP DEFAULT
ALTER  TABLE <table> ALTER [COLUMN] <col> [SET DATA] TYPE <type>   -- bind to an enum type / domain; checks existing rows
ALTER  TABLE <table> ALTER [COLUMN] <col> ADD GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY
       [(<seq-option> ...)]                  -- existing rows must already have the column
ALTER  TABLE <table> ALTER [COLUMN] <col> DROP IDENTITY [IF EXISTS]
ALTER  TABLE <table> ALTER [COLUMN] <col> ADD GENERATED ALWAYS AS (<expr>) STORED   -- rewrites every row
ALTER  TABLE <table> ALTER [COLUMN] <col> ADD GENERATED ALWAYS AS (<expr>) VIRTUAL  -- touches no row
ALTER  TABLE <table> ALTER [COLUMN] <col> SET EXPRESSION AS (<expr>)   -- keeps the kind; STORED rewrites every row
ALTER  TABLE <table> ALTER [COLUMN] <col> DROP EXPRESSION [IF EXISTS]  -- STORED values stay as data
ALTER TABLE <table> SET (ttl = <dur>          -- live TTL change (0 clears)
                       | witness = <bool>     -- toggle witness mirroring
                       | insert_conflict = 'replace' | 'error' -- plain-INSERT conflict rule
                       | replication = <n>    -- online placement transition
                       | nodes = ['<ref>',..] -- online pin change
                       | toast_threshold = <bytes> -- value-TOAST on new writes (0 = off)
                       | placement_finalized = true)  -- operator escape hatch
ALTER TABLE <table> SET (cluster_by = (<col> [, ...]))   -- adopt a clustering key online (standalone, embedded, or a full-copy table on a cluster)
ALTER TABLE <table> SET (partition_by = 'range(<col>, <interval>)')  -- adopt range partitioning online
ALTER TABLE <ts-table> SET (retention = <dur> -- live retention change (0 clears)
                          | ooo = <dur>)      -- live out-of-order window change

ALTER TABLE … RENAME TO renames a table (moving its on-disk data and repointing its indexes); RENAME COLUMN from TO to rewrites that field in every row (recomputing the primary key if it is a key column) and rebuilds any index that referenced it. The store is schema-less, so there is no ADD/DROP COLUMN — a field simply exists in the rows that set it.

The constraint forms are described under Column constraints, FOREIGN KEY and EXCLUDE; ALTER COLUMN … TYPE binds an enum type or domain; the identity and generated forms are under SERIAL and identity columns and Generated columns. The SET (…) options: ttl (Row expiry), witness (Witness mirroring), insert_conflict (Existing primary keys), replication, nodes and placement_finalized (Per-table placement), toast_threshold (Value TOAST), cluster_by (Clustering), partition_by (Range partitioning), and a time-series table's retention and ooo (CREATE TIMESERIES TABLE).

See also CREATE TABLE · DROP TABLE · SHOW CREATE TABLE

DROP TABLE

DROP   TABLE [IF EXISTS] <table>
       -- cascades to every derived index on the table (secondary, search, vector)

A table a view reads, or a parent another table's foreign key references, cannot be dropped — see CREATE VIEW and FOREIGN KEY. Dropping a range-partitioned table drops its partitions; dropping a rollup's source drops the rollup (CREATE ROLLUP).

See also CREATE TABLE · DROP DATABASE

CREATE TABLE AS

CREATE TABLE [IF NOT EXISTS] <table> (PRIMARY KEY (<col> [, ...]) [, <item> ...])
       [WITH (...)] AS <select> [WITH [NO] DATA]

CREATE TABLE t (PRIMARY KEY (id) [, items]) [WITH (…)] AS <select> [WITH [NO] DATA] creates the table exactly as the plain CREATE TABLE would (the key is required — there are no column types to infer one from) and fills it with INSERT INTO t SELECT …. INSERT INTO t [(cols)] <select> runs the query in full first — views, CTEs, subqueries, joins, under the session's scan budgets like any read — then inserts its rows in chunks of 500 through the ordinary INSERT path (DEFAULTs, generated columns, constraints, ON CONFLICT, RETURNING, a view target all apply); with no column list the query's output names are the columns, with one the counts must match. Because the query finishes before the first row is written, INSERT INTO t SELECT … FROM t sees only the rows that existed. A population that fails (a scan budget, a constraint) drops the new table again; CREATE TABLE IF NOT EXISTS … AS on an existing table leaves its rows alone; WITH NO DATA creates it empty. On a cluster the table DDL broadcasts and the coordinator runs the fill (the query gathers there, the rows route to their replicas at the session's write consistency). RBAC: CREATE (or INSERT) on the target and SELECT on every table the query reads.

See also CREATE TABLE · INSERT · CREATE MATERIALIZED VIEW

Sequences and types

CREATE SEQUENCE

-- Sequences (database-scoped counters; <seq-option> is any of
--   INCREMENT [BY] <n> | MINVALUE <n> | NO MINVALUE | MAXVALUE <n> | NO MAXVALUE
--   | START [WITH] <n> | CACHE <n> | CYCLE | NO CYCLE)
CREATE SEQUENCE [IF NOT EXISTS] <name> [<seq-option> ...] [OWNED BY <table>.<col> | OWNED BY NONE]
ALTER  SEQUENCE [IF EXISTS] <name> [<seq-option> ...] [OWNED BY ...] [RESTART [WITH <n>]]
DROP   SEQUENCE [IF EXISTS] <name> [, <name> ...]
SELECT nextval('<seq>')                      -- also in a DEFAULT and an INSERT's VALUES
SELECT setval('<seq>', <n> [, <is_called>])  -- FROM-less SELECT only
SELECT currval('<seq>')  /  SELECT lastval() -- this connection's last nextval; any expression

A sequence is a database-scoped counter: CREATE SEQUENCE s [INCREMENT BY n] [MINVALUE n | NO MINVALUE] [MAXVALUE n | NO MAXVALUE] [START WITH n] [CACHE n] [CYCLE | NO CYCLE] [OWNED BY t.c] (defaults as Postgres: increment 1, min 1 / max i64 max for an ascending sequence, min i64 min / max −1 for a descending one, start = min (max when descending), CACHE 1, NO CYCLE).

nextval('s') hands out the next value — s is resolved in the current database, or write db.s — and is allowed in a column DEFAULT, in an INSERT's VALUES and in a FROM-less SELECT; anywhere else (a projection over rows, WHERE, CHECK, a procedure expression) is refused with nextval() is allowed in a DEFAULT, an INSERT's VALUES and a FROM-less SELECT.

setval('s', n [, is_called]) (FROM-less SELECT only) sets the counter: with is_called true (the default) the next call returns n + increment, with false it returns n; ALTER SEQUENCE s RESTART [WITH n] is setval(n, false) as DDL — the next call returns n (default: the START value).

Reaching MAXVALUE (or MINVALUE when descending) on a NO CYCLE sequence fails the statement with nextval: reached maximum value of sequence "s" (n); CYCLE wraps around to the other bound.

CACHE n leases n values per counter write and hands them out from memory: on a cluster every coordinator holds its own block, so values stay unique and dense per coordinator but interleave across coordinators, and a setval / RESTART only invalidates the blocks of the node that ran it — the others hand theirs out to the end (Postgres sessions with CACHE > 1 behave the same). With the default CACHE 1 every call is one counter round (a local write on standalone, a linearizable CAS on a cluster), values are dense across the whole cluster, and a failed statement still consumes its value — Postgres semantics: a sequence is never rolled back.

SERIAL and identity columns

SERIAL / BIGSERIAL on a column item (id SERIAL, the two are synonyms — values are i64) creates the sequence <table>_<col>_seq owned by the column and makes the column GENERATED BY DEFAULT AS IDENTITY: NOT NULL with a nextval default, so INSERT INTO t (name) VALUES ('x') keys the row by the next value (a DEFAULT on a primary-key column is applied before the key is computed). GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(seq-options)] is the same with explicit options (id GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 10)).

BY DEFAULT lets an INSERT supply its own value (the counter does not move); ALWAYS refuses a supplied value on INSERT (column "id" of "t" can only be updated to DEFAULT: it is an identity column defined as GENERATED ALWAYS) — except under INSERT INTO t (…) OVERRIDING SYSTEM VALUE VALUES (…) (bulk loads, imports) — and refuses the column in UPDATE … SET and ON CONFLICT DO UPDATE SET outright.

An identity column may not also carry a DEFAULT. Writing the word DEFAULT as a VALUES element (INSERT INTO t (id, name) VALUES (DEFAULT, 'x')) un-names the column for that row so its default applies.

ALTER TABLE t ALTER COLUMN c ADD GENERATED … AS IDENTITY [(…)] adopts an existing column (every row must already have it, as for SET NOT NULL; refused on a column that has a DEFAULT or is already an identity column); … DROP IDENTITY [IF EXISTS] makes it an ordinary column again and drops the owned sequence.

The owned sequence is dropped with its table, keeps its name across RENAME and shows in SHOW SEQUENCES as owned_by "t.c"; DROP SEQUENCE s is refused while a column DEFAULT calls it (sequence 's' is used by the DEFAULT of t.c — drop the default first), and DROP DATABASE drops the database's sequences.

currval and lastval

currval('s') is the last value nextval('s') handed out on THIS connection and lastval() the last value any nextval did, whichever sequence — including the identity values an INSERT generated (currval('users_id_seq') after INSERT INTO users (name) …) and a setval (which defines them at the value set). Before the connection's first nextval of that sequence either is refused with currval of sequence "s" is not yet defined in this session / lastval is not yet defined in this session — as in Postgres.

The state is per connection (a driver session, skaidbsh, the MCP server's connection), never shared, and a stateless request (REST, the Elasticsearch surface, the UI) starts empty: there currval is defined only within the request that called nextval. On a cluster the value is the one the coordinator you are connected to handed out. Both calls are allowed in any expression (a projection over rows, WHERE, SET, VALUES, RETURNING); currval('db.s') addresses another database's sequence like nextval does, and within one FROM-less SELECT a currval after a nextval reads the value just handed out.

Listing and exporting sequences

SHOW SEQUENCES lists sequence, increment, min_value, max_value, start, cache, cycle, owned_by, last_value — last_value is the highest value leased so far (NULL before the first nextval; with CACHE > 1 it is the block's end, as Postgres), read at QUORUM on a cluster. SHOW CONSTRAINTS reports an identity column as one IDENTITY row whose definition is the column item; SHOW CREATE TABLE prints the item inline (the owned sequence is implied); a free-standing sequence a DEFAULT calls must exist before the table is replayed. Sequence definitions travel with schema sync; the counter is one row of the hidden per-database table __seq__, replicated at the database's factor, so it survives any node.

skaidbsh export writes free-standing sequences into schema.sql, identity columns and setval(…) for every counter into constraints.sql (replayed after the data), and import loads GENERATED ALWAYS tables with OVERRIDING SYSTEM VALUE. Not offered: typed sequences (AS smallint), SMALLSERIAL.

See also CREATE TABLE · ALTER TABLE · Scalar functions · RETURNING · SHOW SEQUENCES

Enum types and domains

-- Enum types and domains (named constraint bundles a column item names)
CREATE TYPE [IF NOT EXISTS] <name> AS ENUM ('<v>' [, ...])   -- the order is the type's order
ALTER  TYPE <name> ADD VALUE [IF NOT EXISTS] '<v>' [BEFORE '<w>' | AFTER '<w>']
DROP   TYPE [IF EXISTS] <name> [, ...]
CREATE DOMAIN [IF NOT EXISTS] <name> [AS] [NOT NULL] [DEFAULT <expr>] [[CONSTRAINT <n>] CHECK (<expr over VALUE>)] [...]
ALTER  DOMAIN <name> SET DEFAULT <expr> | DROP DEFAULT
ALTER  DOMAIN <name> ADD [CONSTRAINT <n>] CHECK (<expr over VALUE>) [NOT VALID]   -- checks the bound columns' rows
ALTER  DOMAIN <name> DROP CONSTRAINT [IF EXISTS] <n>
ALTER  DOMAIN <name> RENAME TO <new_name>
DROP   DOMAIN [IF EXISTS] <name> [, ...]
-- and in CREATE TABLE a column item may name one: <col> <type> [NOT NULL] [DEFAULT ...] ...

The store has no column types, so CREATE TYPE mood AS ENUM ('happy', 'sad') and CREATE DOMAIN positive AS NOT NULL DEFAULT 1 CHECK (VALUE > 0) are named constraint bundles: a column item may name one (m mood, qty positive, alongside its own NOT NULL / DEFAULT / CHECK), and CREATE TABLE expands it into ordinary constraints — an enum into a column CHECK (m IN ('happy', 'sad')) named <col>_<type>, a domain into its NOT NULL / DEFAULT / CHECKs with VALUE replaced by the column, each CHECK named <col>_<check> (the CHECK written in CREATE DOMAIN without a CONSTRAINT <n> is named after the domain, so qty positive yields qty_positive) — which SHOW CONSTRAINTS lists like any other.

The column stays bound to the type: ALTER TABLE … ALTER [COLUMN] <col> [SET DATA] TYPE <type> binds an existing column the same way (the derived constraints are added and existing rows checked, cluster-wide on a cluster), the binding replays through SHOW CREATE TABLE and schema sync as that statement, and DROP TYPE / DROP DOMAIN clear it.

Enums order by their declaration: on a bound column <, <=, >, >=, BETWEEN and ORDER BY compare the labels' positions in the CREATE TYPE list rather than their text (a label outside the enum compares as unknown; = and IN compare the text; min/max and an index range over the column still see text).

ALTER TYPE mood ADD VALUE 'angry' [BEFORE | AFTER 'sad'] grows the enum at that position (last by default) and rewrites every column CHECK derived from it (found by name and exact expression); DROP TYPE is refused while such a CHECK exists (drop the constraint first).

ALTER DOMAIN reaches every bound column: SET DEFAULT / DROP DEFAULT replace the DEFAULT a column took from the domain (a DEFAULT written on the column itself wins, as at CREATE TABLE); ADD [CONSTRAINT <n>] CHECK (…) adds <col>_<n> to each bound column, checking their existing rows first unless NOT VALID (then ALTER TABLE … VALIDATE CONSTRAINT <col>_<n> per table); DROP CONSTRAINT <n> takes those derived CHECKs off; RENAME TO re-points the bindings. DROP DOMAIN leaves the constraints it produced in place.

Types are database-scoped (db.mood qualifies like a table), travel with schema sync (typ: keys, before tables) and list in SHOW TYPES with their CREATE statement. Not offered: ALTER TYPE … RENAME VALUE, ALTER DOMAIN … SET | DROP NOT NULL, composite types, collations.

See also CREATE TABLE · ALTER TABLE · Column constraints · SHOW TYPES

Indexes

CREATE INDEX

CREATE [UNIQUE] INDEX [IF NOT EXISTS] <name> ON <table> (<component> [, ...]) [WITH (global = true)] [WHERE <expr>]
  -- <component> := <path>[[]] | (<expr>)   -- an expression component; WHERE = a partial index
       -- WITH (global = true): value-sharded index — equality probes route to the value's
       -- replica set instead of scattering; ranges keep the scatter path (GLOBAL_INDEXES.md)
DROP   INDEX [IF EXISTS] <name>

CREATE INDEX with one path is a single-column index; with several it is a composite index (ordered left-to-right). See Indexing notes. The target may be an event rollup's bucket table (CREATE INDEX i ON r (total)): the same range-capable index, maintained by every refresh — see CREATE ROLLUP.

Global indexes

WITH (global = true) makes it a global (value-sharded) index: entries live in an internal replicated table placed on the ring by indexed value, so a full-tuple equality/IN probe routes to one replica set instead of scattering to every member — the RF < members win; local stays the default. Ranges and partial prefixes keep the scatter path. The DDL acks at schema-apply and backfills in the background (SHOW INDEXES says global (building); probes fall back to scatter until ready). Guide: INDEXING.md.

Multikey components

A [] suffix on one path (CREATE INDEX i ON t (account, labels[])) marks a multikey component: the value there is an array and each element gets its own index entry, so labels = 'x' (element containment) becomes an index probe — including exact index-only counts. At most one [] per index. The planner uses a multikey index only when every column through the [] component is equality-constrained; other shapes (ranges or sorts on the array column) fall back to a scan.

Partial and expression indexes

CREATE [UNIQUE] INDEX i ON t ((lower(email))) indexes an expression's value per row (any deterministic expression over the row's columns — the CHECK rules: no aggregates, windows, subqueries, parameters, now(), nextval); the planner uses it for a filter whose left side is the same expression (WHERE lower(email) = 'x', ranges, IN, ORDER BY lower(email)), matched by canonical text. A deterministic call or arithmetic over literals and bound parameters in WHERE / ON is evaluated before planning, so WHERE lower(email) = lower(?), ts > 1792800000000 - 3600000 and id = to_timestamp(?) plan like the values they evaluate to (select items keep their text, and with it their column names).

… WHERE <predicate> makes an index partial: only rows satisfying the predicate have entries, and the planner uses it only when the query's WHERE implies the predicate — every AND-conjunct of the predicate appears literally among the query's AND-conjuncts (WHERE active = true AND score > 5 uses an index … WHERE active = true; WHERE score > 5 alone does not). A count(*) whose WHERE is exactly the predicate is the index's entry count (no row read), and the same plus a string/boolean equality or range on the index's own columns is a range count. A partial UNIQUE index constrains only the rows it covers. Both compose with global and multikey ([] on a plain path only) and round-trip through SHOW INDEXES, schema sync and skaidbsh export.

UNIQUE indexes

CREATE UNIQUE INDEX <i> ON <t> (<path>[, ...]) constrains a value (or value tuple) to at most one row — the second unique axis a primary key cannot express (users keyed by id but email unique too), and composite uniqueness beside a surrogate key. A violating write fails with a distinct unique violation error (catchable separately from other constraint errors) and writes nothing. Rows where any indexed path is absent or NULL are unconstrained, matching SQL.

Uniqueness is enforced cluster-wide, not per shard: the constraint is a reservation keyed by the value itself, claimed through the same per-key consensus round as UPDATE, so concurrent writers racing one value yield exactly one winner.

UPDATE enforces and maintains the constraint like INSERT: updating INTO a taken value is rejected (the row untouched), updating a row off a value frees it, the moved-to value is claimed, and a multi-row UPDATE whose own rows would collide on a unique value is rejected whole before anything is written (a collision with an EXISTING value still errors at the colliding row, so earlier rows of a multi-row statement stay applied — the same mid-statement semantics as any other row error).

An UPDATE that assigns a primary-key column MOVES the row: a unique value it keeps across the move stays its own; a new key already held by a row the statement does not move is a duplicate-key error (<table>_pkey) on every table, UNIQUE or not — never a silent overwrite; two rows given one new key are refused; rows exchanging keys (a swap, any cycle) move as one unit (a cycle larger than the 64 MiB staging bound is refused). Not supported: a swap/cycle on a table with a FOREIGN KEY (error), and on a cluster a swap/cycle or a PK change on a UNIQUE table; inside a transaction a move to a LOWER key on a UNIQUE table is refused at COMMIT.

Decimal unique values compare exactly (1.0 and 1.00 are one value; 1.0 and 1.000000001 are two). Deleting a row frees its values.

On a cluster, a multi-row INSERT (a driver's executemany batch included) into a unique-indexed table claims its values in parallel — each still a single-key consensus round — and writes its rows as one replicated batch, 256 rows at a time; when a claim is refused (a value held elsewhere, the same value twice, a row handing its value to a later row) the batch releases what it claimed and that slice goes row by row, so the outcome is exactly the row-at-a-time one described above. A table with value-TOAST, a global index, an EXCLUDE constraint, or a foreign key that other tables reference keeps the row-at-a-time path.

CREATE UNIQUE INDEX over data that already contains duplicates fails rather than silently tolerating them. A partial unique index (… WHERE active = true) constrains only the rows its predicate holds for — the rows already in the table when it is built exactly as every write after: duplicates among the rows the predicate leaves out do not fail the DDL, and a row moving into the predicate is checked (one moving out frees its value). Cannot be combined with WITH (global = true), and a component cannot be an array element (tags[]) — uniqueness per element is not enforced, so the DDL is refused.

On a cluster, UPDATE/upserts on unique-indexed tables take the plain read-compute-put path rather than the per-row linearizable round (same documented fallback as toast/gidx tables); the VALUE claim itself is still consensus-serialized, so uniqueness stays exactly-one-winner.

See also REBUILD INDEX · CHECK INDEX · SHOW INDEXES · DESCRIBE · Indexing notes · INDEXING.md

CREATE VECTOR INDEX

CREATE VECTOR INDEX [IF NOT EXISTS] <name> ON <table> (<path>) DIM <n> [USING <metric>] [QUANTIZED [BINARY|INT4]] [MULTI] [SPARSE] [EMBED]
DROP   VECTOR INDEX [IF EXISTS] <name>
ALTER  VECTOR INDEX <name> SET (ef = <n>)   -- live recall/latency tuning

CREATE VECTOR INDEX builds an HNSW index for nearest-neighbor search over the float array at <path>. DIM <n> (the vector dimension) is required; USING <metric> is cosine (default), l2, or dot.

SPARSE indexes a column of (dimension, weight) pairs — a learned-sparse model's output — invertedly, and scores it EXACTLY by dot product; DIM is not required and the query is NEAREST (col, sparse([dims], [weights]), k).

MULTI indexes an ARRAY OF VECTORS per row (late interaction): every sub-vector becomes a graph node, hits collapse back to rows, and a query — one vector, or an array of vectors — scores each row by MaxSim (the sum of per-query-vector best distances). SELECT DISTINCT ON (g) … NEAREST is grouped vector search: the best row per group, retrieving enough candidates to cover the groups.

QUANTIZED stores int8 scalar-quantized vectors in the in-RAM graph (4× less vector RAM), QUANTIZED BINARY one sign bit per dimension (32× less, Hamming-steered traversal), QUANTIZED INT4 two components per byte (8× less, real distances); either way queries over-fetch and rescore the top-k against the exact row vectors, so returned distances stay exact (build-time choice — rebuild to change; not combinable with EMBED).

It broadcasts across the cluster so every node indexes its shard. Query it with the NEAREST clause (see Vector search and VECTOR.md).

See also Vector search · Hybrid search · CHECK INDEX · REBUILD INDEX · Model providers · VECTOR.md

CREATE GEO INDEX

CREATE GEO INDEX [IF NOT EXISTS] <name> ON <table> (<point-path>)
       -- Morton/Z-order index: geo_distance / geo_bbox prune to a neighborhood
DROP   GEO INDEX [IF EXISTS] <name>

CREATE GEO INDEX builds a Morton (Z-order) spatial index over the {lat, lon} point column at <point-path>, so geo_distance / geo_bbox predicates in a WHERE scan a small set of code ranges instead of the whole table — no query change, the index is used transparently when present. It broadcasts across the cluster (each node indexes its shard), maintains itself on write, backfills existing rows in the background, and persists on disk (no rebuild on restart). There is nothing to configure; a row whose column is not a readable point is simply not indexed. See GEO.md.

See also Geospatial functions · CHECK INDEX · GEO.md

CREATE SEARCH INDEX

CREATE SEARCH INDEX [IF NOT EXISTS] <name> ON <table> (<path> [, <path> ...])
       [WITH (<option> = <literal> [, <option> = <literal> ...])]
       -- options are global (analyzer, refresh_ms) or per-column (<path>.<option>)
DROP    SEARCH INDEX [IF EXISTS] <name>
REBUILD SEARCH INDEX <name>
ALTER   SEARCH INDEX <name> SET (<option> = <literal> [, ...])
        -- query-time options only: synonyms, refresh_ms,
        -- <col>.search_analyzer, <col>.boost (applied live, no reindex)

CREATE SEARCH INDEX builds a full-text (BM25) index over the listed document paths. Global options: analyzer ('standard' default, 'folding', 'whitespace', 'keyword', 'ngram(min,max)', 'edge_ngram(min,max)', or a language like 'english' — full list in SEARCH.md) and refresh_ms (integer, default 1000 — how quickly writes become searchable).

Per-column options use the path as prefix: <path>.type ('text' default, 'keyword', 'long', 'double', 'bool', 'date'), <path>.analyzer, <path>.search_analyzer, <path>.boost (number), <path>.keyword (boolean — adds a <path>.keyword exact-match twin), and <path>.copy_to (composite target field).

Query it with MATCH()/SEARCH() predicates and score() (see Full-text search and SEARCH.md).

REBUILD SEARCH INDEX re-indexes the table from scratch (recovery escape hatch). On a server it returns at once and the re-index runs in the background in short pages; searches on the index error "rebuilding … retry shortly" until it completes, and writes made meanwhile are kept.

See also Full-text search · SUGGEST · CHECK INDEX · REBUILD INDEX · SEARCH.md

REBUILD INDEX

REBUILD INDEX <name>
        -- every node re-derives its local copy of a secondary / vector /
        -- search index from its own rows (paged, `building` meanwhile);
        -- a GLOBAL index is refused

REBUILD SEARCH INDEX is described with CREATE SEARCH INDEX. For drift between an index and its rows, CHECK INDEX … REPAIR fixes the entries in place without a rebuild.

See also CREATE INDEX · CHECK INDEX · Index DDL privileges

CHECK INDEX

CHECK INDEX {<name> | ALL} [ON <table>] [REPAIR] [RESUME '<token>'] [TIMEOUT <duration> | NONE]
        -- audit THIS node's indexes (secondary, geo, search, vector, and
        -- GLOBAL where it holds every row) against its own rows (paged,
        -- short lock holds, one walk of a table for all its indexes,
        -- changes nothing); REPAIR puts missing entries back and removes
        -- stale ones (secondary, geo, GLOBAL, search documents, vectors;
        -- EMBED vector findings are reported only); never scattered

CHECK INDEX audits the local copy of indexes on the node it is sent to (never scattered — run it on each member) against what that node's rows produce: a secondary index's entries (one per value tuple), a geo index's (one per readable point), a search index's documents (one per row with a value an indexed column takes), a vector index's graph nodes (one per vector of the index's dimension; MULTI one per vector of the row, SPARSE one per non-empty sparse vector, EMBED one per non-empty text once embedded), and a GLOBAL index's entries — on a node that holds every row of its table and every entry (a standalone server, a full-copy cluster table); elsewhere it is listed skipped: sharded.

How an index is judged

Every row's entries are probed in the index (an absent one is missing), then the index is matched against what the rows produced — a hash ledger, so only entries in buckets that do not match have their row read back (an entry no stored row produces is stale; on a UNIQUE index two current entries with one value are a duplicate). A search index's committed documents are read in key order alongside the rows instead (a document for a row that is gone or has nothing indexable is stale, and a row indexed twice has one stale document); a vector index is matched in node-id order.

A clean table costs one sequential pass over the table — shared by all its indexes — and two over each index, no random read.

ALL covers every index of the current database, or of <table>; a named index on a range-partitioned table covers its per-partition copies.

It walks in pages, each under its own short read hold of the engine lock (about 8 ms, cut at 25 ms), pausing between pages so writers go first, with memory bounded to a page and the ledgers (at most 12 MB for one table's indexes) — never one hold for the table.

Output and progress

One row per index, table by table and in name order within a table: index, table, status (ok, drift, repaired, partial, skipped: building, skipped: sharded, stopped: index dropped|rebuilding|redefined), rows, entries (documents, nodes), missing, stale, duplicates, in_flight (rows and entries whose index maintenance was still on its way — queued, a search write not yet committed, a text waiting to be embedded, or a GLOBAL entry and its row written less than 30 s ago on a cluster member; judged once more at the end, and only counted when still on its way then; never drift), repaired, samples (up to five primary keys per kind, e.g. missing [42] -> ["s3"], stale [7] -> (52.500000, 13.400000), missing [5] -> document, stale [9] -> vector), resume, elapsed_ms.

Time limits and RESUME

The statement timeout stops it cleanly at 80 % of storage.statement_timeout_secs: the row says partial and resume holds a token (the indexes of one table cut in their shared walk carry the same one); CHECK INDEX … RESUME '<token>' continues from it (counts are per statement; the ledger lives for one statement, so a resumed check that has passed its row walk reads every remaining entry's row back — correct, and slower).

TIMEOUT <duration> gives the statement its own time limit in place of the node's (TIMEOUT NONE lifts it), so a table too large for storage.statement_timeout_secs is checked with its ledger in one statement; the client's own timeout still applies.

CHECK INDEX ALL starts no table the rest of the statement is unlikely to finish, and one cut after another table ran first restarts from its first row on RESUME (its partial rows report no rows), so only a table too large for one statement is finished entry by entry.

KILL QUERY stops it with the kill error; SHOW QUERIES shows its progress.

A row is judged by its last-writer-wins version across every storage source (what a scan returns), never by a point read's first hit.

REPAIR

REPAIR re-judges each finding of a secondary, geo or GLOBAL index under a short write hold (a key whose maintenance is on its way again is left to it), puts a missing entry back or removes a stale one, syncs the WAL after the hold, and logs a line per index; it is idempotent and the index keeps serving reads throughout.

A GLOBAL repair writes this node's copy of the entry table, and anti-entropy carries it to the other replicas.

A search index's findings are repaired the same way, key by key: a row missing its document is indexed again from its stored version, a document no stored version stands behind (row gone, or no indexable value) is removed, both stamped with the row's HLC; the index is committed after each hold, so a repair survives a crash. No whole-index rebuild, no long lock hold — use it instead of REBUILD SEARCH INDEX for drift.

A vector index's findings are repaired row by row too: the row's vectors are derived again from its stored version (a lost one inserted, one whose row is gone or holds no vector removed); the graph is in memory and the repair is durable at the next vector checkpoint (every 10 minutes, and at a clean stop) — a crash before it leaves the finding for the next check.

A managed (EMBED) index's findings are reported only (REBUILD INDEX re-derives it); duplicates are reported, never repaired.

Privilege: SELECT on the table (on the database for ALL without ON); REPAIR needs CREATE, like REBUILD INDEX.

See also REBUILD INDEX · SHOW QUERIES · SHOW INDEXES · CLUSTER_REPAIR.md

Views

CREATE VIEW

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] <name> [(<col> [, ...])] AS <select>
       [WITH [CASCADED | LOCAL] CHECK OPTION]  -- writes through the view must stay visible
ALTER  VIEW <name> RENAME TO <new-name>
ALTER  VIEW <name> [(<col> [, ...])] AS <select> [WITH [CASCADED | LOCAL] CHECK OPTION]
DROP   VIEW [IF EXISTS] <name> [, <name> ...]

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] v [(c1, c2, …)] AS <select> stores a named SELECT (any SELECT the engine accepts: joins, GROUP BY, set ops, ORDER BY / LIMIT, FTS, NEAREST — but no bind parameters and no nextval/setval).

A view lives in the table namespace of its database (db.v qualifies like a table; a view and a table cannot share a name), sees the tables as they are now (the store is schema-less: a column added later shows through SELECT * immediately), and exposes exactly its projection — with a column list or a non-* item list, any other column is column "x" does not exist in view "v", and a computed item has no sub-fields.

Querying it: a simple body (no DISTINCT / GROUP BY / HAVING / set ops / LIMIT / OFFSET / aggregates / windows / NEAREST) is inlined — the outer query is rewritten onto the base table, so point lookups, index scans, ORDER BY … LIMIT pushdown, FTS and NEAREST all keep working through it; any other body (or a view on the right of a JOIN) runs first and the outer query reads its rows as a derived table, with outer WHERE conjuncts over a grouped body's plain grouping columns pushed into it.

Inlined, the body's own table takes the name the outer query reads the view by (FROM v names it v), so a subquery in the body that names one of ITS tables like that while it reads the body's table — CREATE VIEW v AS SELECT id, EXISTS (SELECT 1 FROM c v WHERE v.pid = p.id) … FROM p — would read its own table where the body meant the view's row. Such a body is refused at CREATE VIEW, ALTER VIEW … AS and ALTER VIEW … RENAME TO with an error that names the view and asks for another alias for that table, and a read under a clashing alias (FROM w AS v) is refused the same way — never misread. A view over a view inlines the inner body under the OUTER view's name, so the check follows that chain: creating ov AS SELECT … FROM iv is refused when iv's body would clash under the name ov, and so is redefining iv into a clash with a view that reads it.

Views over views expand recursively (a cycle is refused at CREATE; depth capped at 16). DROP TABLE / RENAME of a table a view names is refused (view "v" depends on "t" — drop the view first), DROP DATABASE drops its views.

RBAC: a view runs with the caller's rights, not its author's. SELECT on the view's name is checked, and so is SELECT on every base table its body reads, following nested views — re-checked at each run, so a later REVOKE takes effect immediately. Creating (or redefining) one is checked the same way: you cannot wrap a table you may not read. A view therefore never widens access; to expose a narrowed slice of a table, grant on the columns (GRANT SELECT (a, b) ON t).

CREATE VIEW / ALTER VIEW need CREATE on the database, DROP VIEW DROP. A materialized view's backing store is engine-internal and cannot be selected directly.

Definitions travel with schema sync as CREATE OR REPLACE VIEW (vw:<name> keys, tombstoned on DROP), in dependency order.

Writes through a view

INSERT / UPDATE / DELETE (and INSERT … SELECT, ON CONFLICT, RETURNING) on a plain view whose body is a single-table SELECT without joins, DISTINCT, GROUP BY / HAVING, aggregates or window functions, set operations, LIMIT / OFFSET, NEAREST / ranking or WITH are rewritten onto the base table: the named columns map through the view's projection (a computed item is refused — column "c" of view "v" is computed; a column the view does not expose is does not exist in view "v"), and an UPDATE's or DELETE's WHERE is ANDed with the view's own WHERE, so only rows the view shows are reached. RETURNING * returns the view's columns. A view over a view unwinds to the bottom table. Any other body is refused (view "v" is not updatable: it groups rows), as is a materialized view.

An INSERT may land a row the view does not show (a column the view filters on left unset) unless the view carries WITH CHECK OPTION: then a row the view's WHERE would hide — evaluated on the row as it lands, after DEFAULTs, generated columns and the UPDATE's assignments — is refused (new row violates check option for view "v"). A plain INSERT's rows are all checked before any is written; an UPDATE writes row by row, so rows before the offending one stay updated (as with any mid-statement error).

WITH [CASCADED] CHECK OPTION checks this view's WHERE and every underlying view's; WITH LOCAL CHECK OPTION checks its own WHERE and those of underlying views that declare an option themselves.

RBAC: a write through a view needs the statement's privilege on the view's name and on every base table the view names (INSERT through v needs INSERT where v's rows live), re-checked each run.

ALTER VIEW

ALTER VIEW v RENAME TO w moves the definition (refused while another view depends on v, and across databases); ALTER VIEW v [(cols)] AS <select> [WITH … CHECK OPTION] replaces the definition in place — grants on v are untouched, and the new body is validated and authorized as CREATE VIEW's. Plain views only: a materialized view is dropped and created again. Both replicate as DDL and travel with schema sync (a rename as the old name's tombstone plus the new name's definition).

See also CREATE MATERIALIZED VIEW · WITH (common table expressions) · SHOW VIEWS · GRANT

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW [IF NOT EXISTS] <name> [(<col> [, ...])]
       [WITH (refresh = '<interval>')] AS <select> [WITH [NO] DATA]
REFRESH MATERIALIZED VIEW [CONCURRENTLY] <name>  -- re-runs the body; affected = new row count
CREATE [UNIQUE] INDEX [IF NOT EXISTS] <name> ON <matview> (...)  -- maintained by every refresh
DROP   MATERIALIZED VIEW [IF EXISTS] <name> [, <name> ...]

CREATE MATERIALIZED VIEW [IF NOT EXISTS] m [(cols)] [WITH (refresh = '<interval>')] AS <select> [WITH [NO] DATA] runs the body and stores its result in a hidden backing table (__mv__m, PRIMARY KEY (__row): a content hash of the output row plus an occurrence ordinal, so an unchanged result lands on unchanged keys and duplicate rows survive), placed like the body's FROM table.

Reading m is an ordinary table read of that snapshot — point lookups, ORDER BY … LIMIT, streaming all apply; the backing key never shows and SELECT * on a never-refreshed view (or WITH NO DATA) returns no rows.

REFRESH MATERIALIZED VIEW m re-runs the body at the session consistency, upserts every output row and deletes the keys the new result did not produce — readers during a refresh see the union of old and new rows, never an empty table; it reports the new row count as the affected count.

REFRESH MATERIALIZED VIEW CONCURRENTLY m writes the new result beside the old one as the view's next generation (keys g<n>:<hash>) and then switches readers to it with one write of the view's metadata row: a reader sees the whole old result until that write and the whole new one after it, never a mix; the old generation's rows are deleted afterwards. It costs the new result's size in extra storage while it runs and rewrites every row (a plain refresh leaves unchanged rows in place).

Indexes on a materialized view: CREATE [UNIQUE] INDEX i ON m (col) indexes the backing table; reads through m plan it like any table index, both refresh kinds maintain it (they write through the ordinary write path), SHOW INDEXES / schema sync / skaidbsh export list it against m (after the view, so a replay can create it), and DROP MATERIALIZED VIEW takes it along. A UNIQUE index there is checked per generation's row, so CONCURRENTLY over a UNIQUE index collides with the old generation — use a plain refresh or a non-unique index.

WITH (refresh = '5m') (ms/s/m/h/d/w) refreshes on the maintenance tick (every 60 s) once the interval has elapsed — on a cluster from the ring owner of the view's name.

SHOW VIEWS lists view, kind (view | matview), refresh, last_refresh, rows, definition in dependency order (rows is the backing table's count); SHOW TABLES lists views with kind = view | matview and no key; SHOW CREATE TABLE m prints the CREATE statement. DROP MATERIALIZED VIEW m drops the backing table too (DROP VIEW on a materialized view, and vice versa, is refused).

skaidbsh export writes views into schema.sql after the tables (a materialized view as … WITH NO DATA) and a REFRESH MATERIALIZED VIEW per materialized view into constraints.sql, so an import ends populated.

See also CREATE VIEW · CREATE INDEX · SHOW VIEWS · CREATE TABLE … AS

Writing data

INSERT

INSERT INTO <t> (...) [OVERRIDING SYSTEM VALUE] VALUES (...)
       [ON CONFLICT DO NOTHING                       -- keep existing row; affected 0
      | ON CONFLICT DO UPDATE SET <col> = <expr>, ...] -- merge into existing row
       [RETURNING * | <expr> [AS <alias>] [, ...]]   -- the rows as they landed
INSERT INTO <table> (<col> [, <col> ...]) VALUES (<expr> | DEFAULT, ...) [, (<expr>, ...) ...]
INSERT INTO <table> [(<col> [, <col> ...])] <select>   -- no column list: the query's output names
       [ON CONFLICT ...] [RETURNING ...]

INSERT INTO t [(cols)] <select> is described with CREATE TABLE … AS; OVERRIDING SYSTEM VALUE and the DEFAULT element with SERIAL and identity columns; an INSERT into a view under Writes through a view.

Existing primary keys

A plain INSERT of an existing primary key REPLACES the row by default. A table created (or altered) WITH (insert_conflict = 'error') gives it the Postgres semantics instead: the statement fails with a unique violation on <table>_pkey (unique violation: index "t_pkey" already has (id)=(1); UNIQUE_VIOLATION in a procedure handler), and so does a key repeated within one statement. A multi-row INSERT is all or nothing: every key is checked before any row is written, and each row is re-checked as it lands (a consensus round per row on a cluster, like ON CONFLICT), so a key created concurrently still fails rather than being replaced. The rule covers every INSERT into the table — SQL, driver batches, INSERT … SELECT, and the REST, Elasticsearch-gateway and MQTT-sink writes that insert rows. ON CONFLICT clauses keep their own meaning on such a table.

ON CONFLICT

INSERT ... ON CONFLICT makes inserts upserts: with no clause an existing primary key is REPLACED wholesale (unless the table says insert_conflict = 'error', see Existing primary keys); DO NOTHING leaves the existing row untouched and reports 0 affected for that row; DO UPDATE SET ... merges only the named columns into the existing row — other columns survive.

Merge expressions see the EXISTING row's columns directly and the incoming row's values as excluded.<col> (the name excluded is reserved inside this clause), so SET hits = hits + 1 and SET name = excluded.name compose. The existing row can also be named explicitly as <table>.<col>, so the Postgres spelling SET hits = t.hits + 1 works too — with one precedence rule the schemaless model forces: if the row really has a column named after its own table, that column wins and t.x stays a path into it.

On a cluster the whole check-and-write runs inside the same per-row consensus round as UPDATE, so concurrent create-or-update races serialize: exactly one insert wins and every merge counts. A multi-row upsert runs those rounds batched, as a multi-row UPDATE does (rows sharing a replica set share each consensus step); a statement repeating a key runs its rows one after another, so the later row sees the earlier one's result.

When a row fails (a CHECK on the merged row, say), the statement returns the error without starting any further batch, but it is not atomic: other rows of the failing row's own batch (up to 128 rows, not only those before it in the statement) may still commit, as with a multi-row UPDATE. Retry only idempotent merges blindly.

Value-TOAST, UNIQUE-indexed and globally-indexed tables use a best-effort read-merge-write instead (same caveat class as their UPDATEs).

See also RETURNING · UPDATE · Column constraints · UNIQUE indexes · Generated columns

UPDATE

UPDATE <table> SET <path> = <expr> [, <path> = <expr> ...] [WHERE <expr>]
       [RETURNING * | <expr> [AS <alias>] [, ...]]   -- the rows as updated

Assignments may use the array functions (SET tags = array_append(tags, ?)) and a table qualifier (SET, WHERE and RETURNING accept <table>.<col>). An UPDATE that changes a clustering value moves the row; one that assigns a primary-key column on a table with UNIQUE indexes follows the rules there.

Concurrency and atomicity

Clustered UPDATE is linearizable per row: every row a clustered UPDATE touches is read-modified-written through a per-key consensus round (single-decree Paxos among the row's replicas), so concurrent updates to one row serialize — SET n = n + 1 from many clients counts every increment exactly.

Caveats: assignments that change a PRIMARY KEY column take the plain last-writer-wins path (a consensus commit cannot move a key); plain INSERT/DELETE stay last-writer-wins, so mixing overwrites of the same key with concurrent UPDATEs forfeits the guarantee for those writes; a single hot row serializes every writer behind one consensus round at a time (CLUSTER_CONSISTENCY.md "Hot keys" has the counter patterns: shard over N rows, batch client-side, or append and roll up); heavy single-key contention can error with CAS contention … (not applied; safe to retry …) after bounded retries — the statement provably had no effect and will never take one, so clients retry safely (an update whose effect landed is recognized and returns success, never an error).

When that cannot be proven — a replica that may hold one of the statement's proposals did not answer, e.g. a member is down, the statement hit its deadline or KILL while resolving, or more than 1,024 rival commits landed on the key while the statement was stalled (the row's bounded commit history then "no longer covers this statement's proposals") — the error is CAS outcome unknown: … (may have been applied; do not retry blindly): the update may yet commit, so re-running a non-idempotent statement (n = n + 1) can apply it twice.

A multi-row UPDATE or upsert whose rows partly committed before another row gave up errors with CAS partial outcome: N of M rows … (partially applied; do not retry blindly); "not applied" always means no row of the statement changed.

Embedded (single-node) UPDATEs were always serialized by the engine lock.

On a standalone server an INSERT / UPDATE / DELETE statement (foreign-key cascades included) that fails part-way — a constraint, a full disk, an I/O error — leaves no row of it applied: its rows and index entries are written together after the WAL has reserved room for all of them. Values a failed statement took from a sequence (nextval, SERIAL) stay consumed. This is error atomicity, not crash atomicity: a crash or power loss in the middle of the final writes can leave part of a statement (use a transaction — BEGIN … COMMIT, whose commit is journaled — when that matters).

A statement writing more than 64 MiB of WAL is applied in 64 MiB chunks, each committed on its own: a failure leaves the chunks before it applied. Monitoring: skaidb_cluster_cas_*_total metrics.

See also RETURNING · INSERT · ON CONFLICT · Writes through a view · CLUSTER_CONSISTENCY.md

DELETE

DELETE FROM <table> [WHERE <expr>]
       [RETURNING * | <expr> [AS <alias>] [, ...]]   -- the rows as they were

Foreign-key ON DELETE actions run with the deleted rows (FOREIGN KEY), and a standalone server applies all of a statement's rows or none of them (Concurrency and atomicity). Time-series tables are append-only (CREATE TIMESERIES TABLE).

See also RETURNING · Writes through a view · Row expiry (TTL)

RETURNING

INSERT … RETURNING * | <expr> [AS alias], … returns the rows as they landed — defaults (so the generated id) applied, the merged row for ON CONFLICT DO UPDATE, and nothing for a row DO NOTHING skipped — as a result set instead of an affected count; * is every field of the rows in name order. UPDATE … RETURNING returns the updated rows as they landed (assignments, generated columns and a moved key applied — on a cluster the document each per-key consensus round actually committed) and DELETE … RETURNING the deleted rows as they were, in key order, with the same items and row shape a SELECT of them would have; a filter that matches nothing returns the columns and no rows (* then has no columns to show). Column-level grants treat a RETURNING as a read of the table, like an assignment.

See also INSERT · UPDATE · DELETE · Column-level grants

Querying data

SELECT

SELECT <expr> [[AS] <alias>] [, ...]   -- no FROM: constant projection, one row
                                       -- (`SELECT 1` liveness probe; no other
                                       --  clause may follow; `*` needs a table)
SELECT [DISTINCT | DISTINCT ON (<expr> [, ...])] <select-item> [, <select-item> ...]
FROM { <table> [[AS] <alias>] | <unnest> }
[ <join> | , [LATERAL] <unnest> ... ]
[NEAREST (<path>, <query-vector>, <k>) ...]  -- several legs, fused by RANK BY RRF
[WHERE <expr>]
[RANK BY RRF [(<constant>)]]            -- hybrid: fuse NEAREST + WHERE-search by RRF
[RERANK [ON <col>] [WITH '<model>'] [QUERY '<text>'] [TOP <n>] [TIMEOUT <ms>]]
                                        -- second-stage cross-encoder reranking
[GROUP BY <expr> [, <expr> ...] [TOP <k> BY <expr> [ASC|DESC]]]
[HAVING <expr>]
[ { UNION | INTERSECT | EXCEPT } [ALL] <select> ... ]     -- applied left to right
[ORDER BY <expr> [ASC|DESC] [NULLS FIRST|NULLS LAST] [, <expr> [ASC|DESC] [NULLS FIRST|NULLS LAST] ...]]
[LIMIT <n>] [OFFSET <n>]
[AFTER (<sort-value>, <pk-value>)]      -- deep pagination: keyset cursor (search queries)
[AS OF <instant>]                       -- point-in-time read (unix ms, timestamp string, or now() - <ms>)

<join> := [INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | CROSS] JOIN <table> [[AS] <alias>] [ON <expr>]
        | [INNER | LEFT [OUTER] | CROSS] JOIN [LATERAL] <unnest> [ON <expr>]
<unnest> := UNNEST(<array-expr> [, ...]) [WITH ORDINALITY] [[AS] <alias>[(<column> [, ...])]]
                                        -- one row per element (see UNNEST)

<select-item> is * (all fields seen in the result rows) or <expr> [[AS] <alias>].

Clauses with their own sections: JOIN, UNNEST, NEAREST, RANK BY RRF, RERANK, UNION / INTERSECT / EXCEPT, AFTER and AS OF; a WITH list before the SELECT is under WITH (common table expressions).

Table qualifiers

A table qualifier names the table, with or without a join. In a single-table SELECT, <alias>.<col> — or <table>.<col> when the FROM gives no alias — is the column <col> in every clause (SELECT d.pid FROM dept d WHERE d.region = 'us' ORDER BY d.pid), and a nested path continues after it (e.addr.city). UPDATE (SET, WHERE, RETURNING) and DELETE accept <table>.<col> the same way. An unaliased qualified column keeps its written label in the result (d.pid), as in a join. An alias replaces the table name, so under FROM address a the path address.city is the nested field city of the field address (also spelled a.address.city). Any other leading segment is a nested-document path.

Precedence when the qualifier is also a field. A table whose rows hold a top-level document field named like the qualifier keeps the nested-path reading: over rows {id, address: {city, zip}}, SELECT address.city FROM address WHERE address.city = 'Oslo' reads the nested city, in every clause, and an index on address.city serves it. The reading is chosen once per statement — one reading for the projection, WHERE, GROUP BY, HAVING, ORDER BY, SET and RETURNING, so an index can serve the predicate — from what the table shows on the node running the statement: an index defined on a path under the field (address.city), or a document in that field in one of the table's first 16 rows in key order. A scalar field of that name does not count (FROM docs d over rows with a date field d still reads d.id as the column id). To pin a reading whatever the rows hold, alias the table: under FROM address a, address.city is always the nested path and a.city the column.

DISTINCT and DISTINCT ON

DISTINCT ON (<expr>, …) keeps the first row of each group of equal expressions, in the query's ORDER BY — whose leading keys must be exactly the DISTINCT ON expressions (SELECT DISTINCT ON (k) k, v FROM ev ORDER BY k, ts DESC = the latest row per k); without an ORDER BY the row picked per group is arbitrary. LIMIT / OFFSET apply after the pick. Lowered onto ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) = 1, so it materializes the filtered set like a window function and cannot combine with GROUP BY / aggregates (use GROUP BY … TOP 1 BY … for that) or appear in a set-operation branch.

DISTINCT removes duplicate output rows. SELECT DISTINCT <one plain column> (no ORDER BY/GROUP BY/JOIN) streams the value set without materializing rows — safe on tables of any size; an array-valued column dedupes whole arrays (its elements: SELECT DISTINCT tag FROM t, unnest(t.tags) AS tag). NULL and a missing field are one distinct value: the result has a NULL row when any matching row has no value, with one column selected or several (count(DISTINCT col) counts values only).

GROUP BY, HAVING and TOP

HAVING filters groups after aggregation (it may reference aggregates and the GROUP BY columns).

GROUP BY ... TOP <k> BY <expr> [ASC|DESC] — per-group top-k rows: instead of one aggregated row per group, each group contributes its k best rows ranked by the expression (DESC — best-first — is the default; NULLs rank last either way). The select items are then ordinary per-row expressions (* works; aggregates cannot mix with TOP), and HAVING (which may aggregate) still filters whole groups first. ORDER BY/LIMIT/OFFSET apply to the flattened output; without ORDER BY, groups keep first-seen order with rows best-first inside each. Under a search predicate TOP k BY score() ranks by BM25 — the SQL spelling of ES top_hits (per-group best documents), and HIGHLIGHT() works in the projection.

ORDER BY

Numbers sort by value whatever their type — a column holding ints, floats, decimals and timestamps interleaves them (0, 0.5, 1, 1.5); values of other types follow by type (bools sort before numbers; then strings, bytes, uuids, arrays, documents). This holds on every access path: a secondary index that serves the ORDER BY merges its numeric types by value.

Where NULL sorts: NULLS FIRST | NULLS LAST. Each sort key takes an optional placement for NULL and missing values after its direction (ORDER BY d DESC NULLS LAST, id); with it, the placement is the same on every access path and at every consistency level. Without it, the placement of the leading key follows how the statement reads its rows:

  • a statement that reads its rows in order from a secondary index on the leading key places them where the index keys do — NULL and missing values first ascending, last descending. SELECT … FROM messages WHERE account = ? ORDER BY date DESC LIMIT 50 through an index on (account, date) lists the newest dated rows; rows without a date come after every dated one, and a keyset cursor (date < ?) taken from the page continues it;
  • a statement that sorts its rows places them last ascending and first descending — no index on the leading key, a leading key that is an expression, a GROUP BY / window / UNION result, and every key after the first.

A statement reads in index order when the leading key is a plain column that is the next column of a secondary index after the columns the WHERE pins by equality, the planner picks that index, and the read is bounded: at consistency ONE on a table the node holds in full (and on a single node) with or without a LIMIT; at QUORUM / ALL (and at ONE on a sharded table) when LIMIT + OFFSET is at most 10,000 (a larger or absent limit sorts). A window of more than 4,096 rows at QUORUM / ALL in primary-key order (ascending, on a filter no index narrows) reads the first OFFSET + LIMIT rows of the merged key-order walk, whatever the table's size; another order, on a table every member holds and the coordinator can level — up to a million stored keys — is read from the coordinator's copy brought level with the read quorum, as ONE reads it. Past that, an order a secondary index or the primary key walks is read from every member's walk of the window and a margin of an eighth more, re-read at the read consistency once the union provably holds the window — while those walks rank the rows as the statement does: past 10,000 rows not when the walked range holds NULL entries, unless the statement asks for the index's own placement (ASC NULLS FIRST, DESC NULLS LAST). Otherwise a single-key order gathers the matching rows keeping only the best OFFSET + LIMIT, and a multi-key order gathers them all. Rows tied on a single sort key come in no defined order, so consecutive OFFSET pages can repeat or skip a row of a tie run that crosses a page edge (as with any single-key order); adding the primary key to the ORDER BY makes the pages stable. A primary-key lookup, a geo predicate, a WHERE that pins a primary-key prefix without a LIMIT, a partitioned parent table, a statement inside a transaction and a read while the index is still building all sort.

The placement is decided once per statement and every step ranks by it — the index walk, a coordinator's merge of its members' windows, the final sort of a multi-key order — so a page is always presented in the order it was picked in. Write NULLS FIRST / NULLS LAST when the placement must not depend on the plan. An index serves either placement on a single node and at ONE; at QUORUM a placement opposite to the index's own (ASC NULLS LAST, DESC NULLS FIRST) over a range that holds NULL entries is answered by the exact gather.

Output aliases and positions

Output aliases resolve in ORDER BY, GROUP BY, and HAVING (SELECT count(*) AS c … HAVING c > 1 ORDER BY c DESC, SELECT time_bucket(1m, ts) AS t … GROUP BY t). ORDER BY/GROUP BY resolve bare names (ORDER BY c, not ORDER BY c + 1); HAVING resolves references anywhere in its predicate. When an alias shadows a source column of the same name (SELECT price >= 10 AS price), ORDER BY prefers the output column while GROUP BY/HAVING keep the source column — SQL's usual preference rules.

Output positions: a positive integer in GROUP BY, ORDER BY or DISTINCT ON (…) names the select-list item at that position, as in PostgreSQL and Trino — SELECT region, count(*) FROM o GROUP BY 1 ORDER BY 2 DESC, SELECT DISTINCT ON (1) k, v FROM ev ORDER BY 1, ts DESC. Over a UNION/INTERSECT/EXCEPT, ORDER BY 2 is the combined result's second column. A position past the select list, GROUP BY of an aggregate or a window function, and a position over SELECT * (no fixed positions) are errors; any other expression (ORDER BY 1 + 0) is an ordinary key. A bound parameter is a value, never a position: ORDER BY ? sorts by a constant in every query shape, bound to an integer or not.

See also EXPLAIN · Aggregate functions · Window functions · Streamed queries · Subqueries

JOIN

JOIN combines tables — equi-joins (ON a = b) run as a hash join, other predicates and RIGHT joins fall back to a nested loop.

A single [LEFT] JOIN t whose ON equates t's WHOLE primary key with columns of the FROM table (ON t.account = m.account AND t.id = m.id, other ON conjuncts allowed) is an index nested loop instead: the FROM table is read under its own m.-qualified WHERE conjuncts (index, point and key-slice plans apply) and t is point-read in batches of keys, never gathered. Under a single ORDER BY m.<col> LIMIT n (no grouping, DISTINCT or windows) the FROM walk runs in that order in pages of growing size and stops once OFFSET + LIMIT joined rows pass the WHERE. Keys match as = compares them (the probes above); the ON is re-checked on every probed row. EXPLAIN names it (join: index nested loop via primary-key probes of <t> (<key>)).

A single INNER JOIN is read from its selective side, whichever side of the JOIN it is written on, when that side's own WHERE and ON conjuncts (b.cat = ?) select few rows and the other side's join column leads an index or its primary key (ON a.k = b.id with an index on a.k): the selective side is gathered first (at most 16,384 rows), then the other side is read by k IN (…) of the gathered values in batches, through that index, under its own conjuncts — never walked. It is chosen when the other side's own conjuncts select at least eight times the selective side's rows plus the rows the probe reads (their share of the other table), by bounded peeks at the index ranges, primary-key slices and table sizes on the coordinator's copy (no row read). Values match as = compares them, a NULL joins nothing, and the ON and WHERE are re-checked on every pair. EXPLAIN names it (join: index nested loop driven from <t>: … then <o> by IN (…)…). LEFT, RIGHT and FULL joins keep their sides' roles.

A join runs on the coordinator. Each side is gathered like any table, under the WHERE conjuncts that read that side alone (o.region = ? narrows o's gather, index plans included; the full WHERE still decides every joined row) — except a side an outer join may NULL-extend, which is gathered whole so an IS NULL anti-join sees its NULL-extended rows; charged to storage.scan_byte_budget at its stored size, decoding only the columns the statement reads. A joined row is a set of references into the sides, and only the rows that pass the ON and the WHERE become output rows, holding the columns read after the WHERE (SELECT a.id, b.name keeps one field per side, SELECT count(*) none, SELECT * every field once).

A join's output is not bounded by its inputs, so what the join builds — hash buckets, joined-row references and output rows, at their heap size (about 0.9 KB per output row, plus 0.9 KB per alias the statement names, plus the values) — has a limit of its own of four times storage.scan_byte_budget: past it the statement fails with resource limit: join result exceeded N bytes materialized (4 x storage.scan_byte_budget) instead of growing the node's memory. A finished join stage hands back its buckets and input references, and the candidate loops honor the statement timeout and KILL QUERY. DISTINCT and UNION keep a hash and a row index per distinct row (rows are compared exactly) and charge nothing beyond the rows themselves.

INNER/LEFT/RIGHT/FULL/CROSS are supported (JOIN alone means INNER; CROSS JOIN takes no ON). Reference columns qualified by table alias (u.id, o.amt); an unqualified field resolves against whichever joined table defines it (first table wins on a name clash). SELECT * over a join expands to the underlying fields.

See also SELECT · UNNEST · Correlated subqueries · Not part of the SQL surface

UNNEST

UNNEST(<array>) turns an array into rows: a join side whose rows are the elements of an array computed from each row joined before it (an implicitly LATERAL source). It is how you group, filter or join on the elements of an array column — GROUP BY tags groups whole arrays, so ['a','b'] and ['b'] are two groups; per tag:

SELECT u.tag, count(*) FROM t CROSS JOIN UNNEST(t.tags) AS u(tag) GROUP BY u.tag  -- Trino / Athena
SELECT tag, count(*) FROM t, unnest(t.tags) AS tag GROUP BY tag                   -- PostgreSQL
SELECT unnest(tags) AS tag, count(*) FROM t GROUP BY tag                          -- select-list form

All three are the same statement. The alias names the side; with a column list (AS u(tag)) the element is u.tag (or bare tag), and without one the alias IS the column (unnest(tags) AS tag → tag; no alias → unnest). A document element exposes its fields (line.sku over unnest(o.lines) AS line). Each row repeats once per element; an array that is NULL, missing or empty contributes no row, and any other value one row (itself) — MongoDB's $unwind. LEFT JOIN UNNEST(…) AS u(x) ON TRUE keeps the rows that yield nothing (the side NULL), an ON filters the elements, and only CROSS, INNER and LEFT joins take an UNNEST (a RIGHT/FULL one would null the rows it reads).

Several arrays zip side by side, the shorter padded with NULL, and WITH ORDINALITY adds the 1-based position as a last column — both need the column list: UNNEST(t.names, t.scores) WITH ORDINALITY AS p(name, score, pos). FROM UNNEST(?) AS k(id) JOIN t ON t.id = k.id joins a bound array of keys to a table, and SELECT unnest([3, 1, 2]) needs no table.

In the select list, unnest(<array>) (one argument) returns rows as in PostgreSQL: every call becomes one generated UNNEST side — several calls zip, a repeated call is one column, an unaliased call is named unnest — and, beyond PostgreSQL, grouping by it (GROUP BY tag or GROUP BY unnest(tags)) groups the elements. Anywhere else (WHERE, ON, HAVING, an aggregate's or a window's argument) unnest(…) is refused: unnest in FROM and filter its column. A scalar alias wins its own name: unnest(p.tags) AS tag makes bare tag the element even when p has a tag field (read that one as p.tag). A subquery inside the arguments is refused (join its table and unnest the column), and so is nextval() over the rows of an UNNEST — a FROM-less SELECT nextval(…) is one row.

UNNEST runs where the join runs (on the coordinator in a cluster, after the FROM table's gather and its own WHERE conjuncts): it builds only the columns the statement reads, its rows count toward the join's limit (JOIN), and EXPLAIN names it (unnest: …). LATERAL is accepted before UNNEST only. Joined to UNNEST sides alone, the FROM table takes every WHERE conjunct that reads only its columns — qualified or bare, a bare name an UNNEST side defines staying that side's — so WHERE is_archived = false narrows the table's read (by an index or not) before any row is unnested. A MATCH()/SEARCH() predicate on the table combines with its UNNEST sides — facet counts over search hits (see SEARCH.md); one on an UNNEST column is refused.

Rows per element, from a multikey index

With a multikey index whose [] component is the unnested column, how many rows hold each element is counted from the index's entries — no row read, however many rows the WHERE selects:

CREATE INDEX i_tags ON message_tags (account, tags[]);
SELECT tag, count(DISTINCT id) AS n
FROM message_tags CROSS JOIN UNNEST(tags) AS u(tag)
WHERE account = ? GROUP BY tag ORDER BY n DESC;

It applies when the WHERE pins exactly the index's columns before the [] one, each by = to a string, boolean, UUID or bytes literal (nothing else in the WHERE), the join is an inner or cross join on one UNNEST of that column without WITH ORDINALITY, the statement groups by the element (or is SELECT DISTINCT <element>), and its aggregates are count(DISTINCT <col>) / approx_count_distinct(<col>) where <col> completes the row's primary key with the columns the WHERE pins, or count(*) — HAVING, ORDER BY, LIMIT and expressions over them work as usual.

The index holds each distinct element once per row, while count(*) counts 'a' twice in ['a', 'a']. So count(*) is served from the index only while each replica asked holds a proof that no row repeats an element. A build of the whole table that meets no repeat writes the proof; storing a row that repeats an element withdraws it, and only REBUILD INDEX brings it back. Without the proof count(*) joins the rows, as it does on an index built before the proof existed until that index is rebuilt. A restored backup comes back without the proof.

The NULL group's rows are read to tell a NULL element from a row without an array (more than 256 → the rows are joined). ONE counts this replica's entries; QUORUM/ALL serve them when the replicas the level needs count the same, and join the rows otherwise: when too few answer within seconds, or their counts keep differing. Joining the rows can exceed the statement's scan budget on a large table; skaidb_cluster_element_count_fallbacks_total counts these and the log names the table and reason. On a member that is no pin of a pinned table, the pins' indexes count. EXPLAIN says rows per element counted from multikey index '…'.

See also JOIN · SELECT · Multikey components · Array functions

UNION, INTERSECT, EXCEPT

UNION / INTERSECT / EXCEPT [ALL] combine the rows of several SELECTs that share a column count: UNION concatenates, INTERSECT keeps the rows present in both, EXCEPT removes the rows the right side has. Without ALL the result is deduplicated; with ALL it is a multiset (UNION ALL keeps every row, INTERSECT ALL keeps each row as many times as both sides have it, EXCEPT ALL removes one occurrence per right-side row). Operations apply left to right (no INTERSECT-binds-tighter rule: parenthesise by writing the legs in the order intended). A trailing ORDER BY/LIMIT/OFFSET after the last branch applies to the whole combined result and references the output column names.

See also SELECT · Output aliases and positions

WITH (common table expressions)

WITH <name> [(<col> [, ...])] AS (<select>) [, ...] <select>

WITH a [(cols)] AS (SELECT …), b AS (SELECT … FROM a …) SELECT … FROM b JOIN a … names one or more selects for the statement that follows: a SELECT, or an UPDATE, DELETE or INSERT (WITH gone AS (SELECT id FROM t WHERE …) DELETE FROM t WHERE id IN (SELECT id FROM gone)), where the CTEs are in scope for the write's subqueries; the write's own target is always a table, never a CTE. Each CTE behaves exactly like a view scoped to the statement: the same inline-or-overlay expansion, the same projection rule (column "x" does not exist in view "a"), visible to the statement's subqueries and to a view body that uses WITH; a later CTE may name an earlier one; a CTE shadows a table or view of the same name. A CTE is not a materialization fence — a CTE named twice runs twice (a recursive one excepted, see Recursive CTEs). A CTE body is itself a SELECT: WITH … AS (INSERT …) (data-modifying CTEs) is not offered.

Recursive CTEs

WITH RECURSIVE name [(cols)] AS (<base> UNION [ALL] <recursive>) …. RECURSIVE belongs to the whole WITH list; a CTE of the list whose body names itself is recursive, the others are ordinary. The body is one or more non-recursive legs followed by one or more legs that name the CTE (in a FROM or a JOIN), joined by UNION or UNION ALL (all recursive legs the same).

Evaluation: the base legs produce the first working table; each iteration runs the recursive legs with the CTE's name bound to the previous iteration's rows; it stops when an iteration produces no rows. UNION ALL keeps every row; UNION drops rows already produced, which is what ends a walk over a cycle. The accumulated rows are then an in-memory table to the statement (joins, aggregates, subqueries, a write's WHERE), materialized once. Columns are the column list's, else the base leg's output names.

Bounds: at most 10,000 iterations (beyond that the statement fails with recursive CTE "r" exceeded 10000 iterations), every row the working table retains is charged to storage.scan_byte_budget, and each iteration's reads run under the statement's scan row budget and timeout. The body may not carry ORDER BY, LIMIT / OFFSET or DISTINCT (apply them in the statement that reads the CTE); the first leg must not name the CTE.

Walking a tree: WITH RECURSIVE sub (id, depth) AS (SELECT id, 0 FROM tree WHERE id = 2 UNION ALL SELECT t.id, s.depth + 1 FROM tree t JOIN sub s ON t.parent = s.id) SELECT * FROM sub.

See also CREATE VIEW · SELECT · Subqueries

EXPLAIN

EXPLAIN <statement>
EXPLAIN ANALYZE <select>
EXPLAIN SCORE <select> FOR <pk-literal>

EXPLAIN <statement> returns the plan the executor would choose for any SELECT/DML statement, as (aspect, decision) rows. The access row names the path: point read / point-read set (primary-key =/IN, N keys) / index scan via '<name>' (<bounds>) / index-ordered walk via '<name>' (… early-stop at LIMIT | the whole range in order) / global-index probe via '<name>' (routed to the value's replica set) / primary-key ordered walk on '<col>' (ASC | DESC tail-walk, early-stop at LIMIT) — an ORDER BY on the LEADING primary-key column needs no sort and no separate index read, because the table is its primary index / primary-key slice walk on '<col>' (the PK columns before it equality-pinned, ASC | DESC tail-walk, early-stop at LIMIT) — the same for an ORDER BY on the primary-key column right after the ones the filter pins, as in WHERE account = ? ORDER BY id LIMIT 10 on PRIMARY KEY (account, id); both walks return keys of different types in key order (type tag, then value), so a key column holding both ints and floats lists them by type / primary-key range slice / BM25 top-k, index-ordered or unranked search pushdown / search-aggregation pushdown vs. row-gather fallback / search count: hit set of '<search index>' ∩ index keys of '<index>' (a search count answered from key sets alone, no row gather) / search via '<search index>': hit-set walk of the sorted index '<name>' or hit-set scan of the bounded index range on '<name>' (an ordered search page that reads only hits), plus a search_key_set row (N keys (primary key pinned by …)) when the WHERE pins every primary-key column to =/IN literals — the search then answers for those keys only / HNSW vector search / full table scan (streaming k-way merge).

An order row notes ORDER BY strategy, and names the clustered top-k cliff: a window (OFFSET + LIMIT) of at most 10,000 rows is a bounded per-member scatter (a multi-key sort under a completeness proof, gathering every matching row when the proof fails); past it the row says how the deeper window is read — the members' walks of the window where an index or the primary key walks the order, else no bounded scatter: every matching row is gathered; residual-filter and join-strategy rows follow (join: index nested loop via primary-key probes of <t> … for a join on a whole primary key, join: index nested loop driven from <t> … for an inner join read from its selective side), a subquery row per correlated subquery answered by primary-key probes (anti-join / semi-join / anti-join (NOT IN) / semi-join (IN) / scalar lookup via primary-key probes of <t> (<key>), and whether the outer walk drives the probes with an early stop at OFFSET + LIMIT or every distinct key is probed), and — on a cluster — cluster.* rows (the coordinating member that planned and serves the statement, members — the ring's, witnesses not counted — replication factor, fan-out: point-routed / global-probe-routed / served locally on full copy / scatter-gather; a search SELECT reports its search scatter even when its key is pinned).

Advisory: it mirrors the planner's decision logic without executing anything, so EXPLAIN DELETE ... is safe. Bindable (EXPLAIN SELECT … WHERE id = ? prepares like the statement it wraps). Gated by the wrapped statement's own privilege. EXPLAIN EXPLAIN is rejected. Worked example with output: INDEXING.md.

EXPLAIN ANALYZE

EXPLAIN ANALYZE <select> returns the same plan, plus what the statement actually cost: elapsed, rows returned, rows examined, and a selectivity line reading those two against each other. That ratio is the diagnosis — "the planner intends a full scan" and "the scan read 3.7M rows to return nine" are different facts, and only the second says something is wrong. Past 100× the message names the likely fix.

It runs the statement, so it takes a SELECT only: executing somebody's DELETE because they wanted to see its plan is not a trade worth making. Counting does not require storage.scan_row_budget to be set.

On a cluster the statement runs through the path the plan describes and the rows report what the coordinator paid: elapsed, rows returned, rows examined (this node) — its own engine's rows, a peer's work being the peer's — and peer requests, the requests the statement sent other members (its own, and those of the workers it ran in parallel; another statement's and the node's background traffic are not counted).

Where a probe walk (a correlated [NOT] EXISTS / [NOT] IN, an anti-join, walked in keyset pages) or a QUORUM agreed window spent its time is reported as well, for the parts the statement entered: time reading probe candidates (the walk's pages), time probing (the subquery's lookups), and on a cluster time in agreed window walks (this node) and time waiting on agreed windows (peers) — the wait for the consulted peers' windows once this node's own walk was done. Each sums over the statement's threads, so parts that run side by side (the next page read while one is probed; a page's peer wait is inside its read) add up past elapsed.

EXPLAIN SCORE

EXPLAIN SCORE <select> FOR <pk literal> is a standalone statement returning the BM25 breakdown (explanation column, tantivy's JSON — per-term K1 / idf(n, N) / tf-normalization) of how the row with that primary-key value scored against the SELECT's search predicates. One row when the key matches, zero rows when it does not; an error without a search predicate. On a cluster the explain routes to a replica of the key, so it works at any replication factor.

See also SELECT · Indexing notes · INDEXING.md · ADVISOR.md

Deep pagination (AFTER)

Stable keyset pagination for search queries — the SQL analogue of Elasticsearch's search_after. Instead of OFFSET (which re-fetches and discards every earlier row, and shifts under concurrent writes), pass the previous page's last sort value and last primary-key value:

-- page 1
SELECT id, title, score() FROM docs
WHERE MATCH(body, 'compaction') ORDER BY score() DESC LIMIT 10;
-- page 2: strictly after (last score, last id) of page 1
SELECT id, title, score() FROM docs
WHERE MATCH(body, 'compaction') ORDER BY score() DESC LIMIT 10
AFTER (0.7311, 'doc-141');

Works with ORDER BY score() DESC (cursor = last score) or ORDER BY <col> [ASC|DESC] (cursor = last column value). The primary key is the implicit tie-break, always ascending — every sorted search page is ordered (sort value, pk) deterministically, so ties never straddle a page boundary inconsistently. Rules:

  • Requires a search predicate in WHERE, exactly one ORDER BY key, and LIMIT; a single-column primary key; mutually exclusive with OFFSET, GROUP BY/DISTINCT, RANK BY RRF, and RERANK. Filter-only (non-search) queries can keyset-paginate with WHERE <col> > <last>, or — ordered by the primary key — with the plain-select AFTER form below.
  • The cursor is resolved by re-ranking: the fetch depth doubles until the page fills or the match set is exhausted (capped at 65,536 ranked hits), so a page costs about the same as the equivalent OFFSET fetch — the win is stability (concurrent writes never shift or duplicate pages; a row inserted before the cursor simply never appears) plus no offset ceiling.
  • Score-sorted deep paging inherits the usual distributed-search caveat (per-shard BM25 statistics; scores move if the index changes between pages) — the same caveat ES documents for search_after without a PIT.

ES clients use the _search search_after parameter, echoing each hit's sort array (SEARCH.md).

Plain-select keyset pages (ORDER BY <pk> … AFTER)

A non-search SELECT of bare columns can page directly on the primary key:

SELECT id, status, v FROM events
WHERE status = 'open'
ORDER BY id LIMIT 1000 AFTER ('evt-01H8...');

Requires bare-column items, ORDER BY the table's single-column primary key ASC, a LIMIT, and a single non-NULL cursor literal; no GROUP BY/DISTINCT/joins/set ops/OFFSET. The cursor resumes the primary-key walk by stored key position, not by an SQL comparison — so on a schema-less table whose keys mix types (integer and string ids), pages cover every row exactly once, where WHERE id > <last> would silently skip rows whose key type differs from the cursor's (cross-type comparisons are NULL). Rows arrive in the key encoding's order (type tag, then value — the same order ORDER BY <pk> walks).

On a cluster this form pages at any consistency and placement: a node holding a full copy at consistency ONE walks its own shard, and every other route — QUORUM (the default), sharded tables, pinned tables — serves each page as a bounded, LWW-merged distributed read seeded strictly after the cursor, with the page's read quorum enforced. Each page is its own read: a row written between pages appears or not per ordinary keyset-pagination semantics.

Streamed queries: what pages server-side

A streamed SELECT — a driver's streaming call (OP_QUERY_STREAM; with bound parameters, OP_QUERY_STREAM_PARAMS) or a REST /query POST — always reaches the client in chunks. Whether the node also avoids holding the whole result depends on the statement:

  • Pages, in storage-key order. A plain SELECT of bare columns (or a lone *) from one table, with any filter that is a function of the row — columns, literals, operators and the pure scalar functions (lower, upper, trim, length, substr, concat, coalesce, to_timestamp, to_int, abs, round, array_contains, …) — and either no ORDER BY or ORDER BY a leading run of the table's storage key (cluster_by…, primary key) ascending. Each page is the statement itself, resumed strictly after the storage key the previous page reached: it returns at most 1,024 rows, examines at most 8,192 rows or 32 MB of stored rows (whichever comes first — a table of 100 KB rows pages a few hundred rows at a time) and holds at most 4 MB, then reports where it stopped. A page decodes only the columns the statement reads — its items, its filter and its order; other fields are never decoded, and the TOASTed values of fields it does not read are never fetched. (On a cluster the replicas' page rows still travel whole and are pruned on the coordinator.) So every key shape pages — a composite primary key, a cluster_by table (a range on the leading cluster column reads only its slice) — and no scan budget applies to a page: a sparse filter costs pages that hold no rows, never scan budget exceeded part-way through. The byte budget, statement_timeout_secs and KILL QUERY apply to each page; the stream as a whole runs as long as the client consumes it. A stream is one heavy scan for iwm.max_concurrent_scans: once its pages have examined iwm.heavy_scan_rows rows, each further page runs holding one admission slot and gives it back before its rows are written to the client, so a slow reader holds no slot while it reads — counted, like a statement's, toward its database's share under iwm.max_concurrent_scans_per_database. With every slot (or its database's share) taken the stream waits before its next page — holding no lock, and sending nothing, so the client sees a pause — for up to 30 seconds (or statement_timeout_secs, when that is shorter), and only then fails with the retryable too many concurrent scans (skaidb_iwm_stream_slot_waits_total counts the streams that waited). Literal-only calls and arithmetic are evaluated once, before the first page, and now() is read once for the whole stream — so WHERE ts >= now() - 1d and a bound to_timestamp(?) page like the literals they evaluate to. Rows arrive in the key encoding's order (type tag, then value; a NULL or missing cluster value first).
  • Runs whole first. When a secondary index narrows the filter to at most 16,384 rows (a quarter of scan_row_budget, when that is lower), the planner's index read is far cheaper than walking the table, so the stream runs the statement whole — holding at most 16 MB — and pages it instead if that read exceeds a scan budget, is refused a heavy-scan slot (the paged stream then takes its turn for one between pages) or runs past statement_timeout_secs (the first page then arrives after that long). skaidb_query_stream_whole_gave_way_total counts those.
  • Time slices. A raw read of a time-series table pages on ts >= <cursor> ORDER BY ts (TIMESERIES.md). Each slice is an ordinary statement: one that examines iwm.heavy_scan_rows rows takes a heavy-scan slot like any statement, and is refused, not held back, when none is free.
  • Materialized, then delivered in chunks. Everything else: joins, GROUP BY and aggregates, DISTINCT, set operations, an ORDER BY other than the storage key ascending, LIMIT / OFFSET (a bounded read keeps the plan its LIMIT picks), searches, a filter calling a search predicate, a sequence or a subquery, a selected VIRTUAL generated column (or a * over a table that has one — it is computed per statement; one in the filter pages), a view, a range-partitioned table, a statement inside a transaction. The scan budgets apply to these exactly as to a one-shot statement, and a budget error then says why the statement could not page. A one-shot SELECT that fails a budget but would page if streamed says so in its error.

EXPLAIN SELECT … ends with a stream row saying which of these the streamed form takes. skaidb_query_stream_total{path} counts streams by what they did: keyset (paged in storage-key order), ts (time slices), buffered (ran whole), unpaged (planned to page, but another path served it whole — a shape the lane should have refused).

What a paged stream is, and is not:

  • Not a snapshot. Each page is its own read. A row written behind the position is not seen; one written ahead of it is. A row whose storage key changes while the stream runs (an UPDATE of a cluster_by column moves it) can be seen twice or not at all. AS OF <instant> pins every page to one instant (a row changed after it drops out of the stream).
  • Bounded per page, not per stream. statement_timeout_secs and KILL QUERY act on the page running when they fire: SHOW QUERIES lists each page as its own statement, and a KILL that lands between two pages misses (close the stream from the client). A stream over a table whose keys grow ahead of the position faster than the client drains it does not end — bound it with a filter (ts < <the start>).

A lone SELECT * streams too: its column set is resolved up front by one retain-free discovery scan over the matching rows (the sorted field union — the same columns a one-shot SELECT * reports), then streamed as those explicit columns with absent fields as NULL. The discovery reads field NAMES: values are decoded only for the filter's columns, and no toasted value is fetched (a toasted field's name comes from the row's manifest). A field that first appears in a row written after the stream started is not among that stream's columns. On a cluster the discovery reads the local shard when the node holds the whole table (at any consistency — the pages themselves run at the requested level) and otherwise unions every member's shard; only when a member cannot answer (down, or older than the request) does SELECT * fall back to the materializing path.

On a cluster every read route pages: a node holding a full copy at consistency ONE walks its own shard, and every other route — QUORUM (the default), sharded tables, pinned tables — serves each page as a bounded, LWW-merged distributed read (each replica's agreed chunks, or the merge of every source) resumed strictly after the page's position, with the page's read quorum enforced. Each page is its own read: a row written between pages appears or not per ordinary keyset-pagination semantics (AS OF pins every page to one instant).

Point-in-time reads (AS OF)

SELECT … AS OF <instant> reads the table as it stood at that instant: a row is visible only when the version currently stored for it was written at or before the instant. Rows inserted, updated or deleted afterwards drop out of the result — the earlier version of an updated row is not resurrected (compaction folds old versions away), so the rule is deliberately simple: newer wins, and newer-than-the-instant is hidden. The operand is a constant expression: unix milliseconds, a timestamp string in any to_timestamp form, or now() - <ms>; an operand that does not convert is ignored (the select runs unbounded).

-- every page of a long export reads the same instant, whatever gets
-- written meanwhile
SELECT id, body FROM events ORDER BY id LIMIT 1000 AFTER ('evt-01H8...')
AS OF 1757066400000;

SELECT count(*) FROM events WHERE status = 'open' AS OF '2026-09-05T10:00:00Z';

The bound applies to everything the statement reads — the scan, point reads, index lookups, search hits, CTEs, views and subqueries — on standalone and cluster deployments alike (a coordinator tests each replica's row stamp at its merge). Two things it cannot do: index-side facets (GROUP BY pushed into a search index) and key-count statistics carry no per-row stamps, so an AS OF statement takes the row-reading path for those (exact, slower); and BM25 scores are computed from the index's current statistics, so relevance order under AS OF may differ from the order at the instant. The ES gateway's point-in-time and scroll ids are built on this clause (SEARCH.md).

Full-text, vector, hybrid and reranked search are clauses of an ordinary SELECT. The indexes they read are created with CREATE SEARCH INDEX and CREATE VECTOR INDEX.

Full-text search (MATCH / SEARCH)

Full-text predicates run against a search index on the table (BM25-ranked, Tantivy-backed). They are ordinary WHERE conditions: search predicates compose with AND/OR/NOT among themselves and join ordinary conditions at the top level with AND (those filter the matches afterward). Mixing a search predicate with an ordinary condition under OR/NOT is rejected — the index cannot serve it.

CREATE SEARCH INDEX articles_fts ON articles (title, body)
  WITH (analyzer = 'english', refresh_ms = 1000);

-- Ranked retrieval: top-k pushed into the index, best first. The BM25
-- score is exposed as score() (and a _score field on the row);
-- HIGHLIGHT() projects a snippet with the matches marked.
SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet FROM articles
WHERE MATCH(body, 'quick brown fox') AND published = true
ORDER BY score() DESC LIMIT 10;

-- Query-string mini-language over all indexed columns:
SELECT id, score() FROM articles
WHERE SEARCH('title:"rust database" +body:performance -draft')
ORDER BY score() DESC LIMIT 20;

-- Bool composition of search predicates (must / should / must_not):
SELECT id FROM articles
WHERE (MATCH(body, 'rust') OR MATCH(title, 'rust'))
  AND NOT MATCH_PHRASE(body, 'rust belt') AND year >= 2024;
  • Analyzed predicates (query text goes through the field's query-time analyzer — search_analyzer if set, else the index-time one):
    • MATCH(<col>, '<text>') — true if the column matches any term (OR).
    • MATCH_PHRASE(<col>, '<text>' [, <slop>]) — terms in order, within <slop> transpositions (default 0).
    • FUZZY(<col>, '<text>' [, <distance>]) — Levenshtein-fuzzy terms, distance ≤ 2 (default 1).
    • SEARCH('<query-string>') — mini-language, bare terms over the text columns: term, "phrase", col:term, +must, -must_not, AND/OR, and ranges over typed columns (year:[2020 TO 2024], price:[30 TO *], published:true).
    • MATCH_CROSS(<col>, <col> [, ...], '<text>') — term-centric multi-field match (ES multi_match cross_fields): the listed fields behave like one big field — each term scores by its best field, terms OR together. At least two columns.
    • MATCH_BEST(<col>, <col> [, ...], '<text>') — field-centric twin (ES best_fields over an explicit subset): each listed field scores the whole query and the best field wins (dis-max). MATCH with all text fields already dis-maxes; MATCH_BEST picks the subset.
  • Pattern predicates (not analyzed — they run against the indexed terms, so with a lowercasing analyzer write patterns lowercase):
    • MATCH_PREFIX(<col>, '<prefix>') — term prefix.
    • WILDCARD(<col>, '<pattern>') — * any run, ? any one char.
    • REGEXP(<col>, '<pattern>') — regular expression.
  • MORE_LIKE_THIS(<col>, '<like text>') — rows textually similar to the given text: its most distinctive terms (by in-index document frequency; terms in fewer than 2 docs are ignored, at most 25 terms) OR-ed together. Composes like the other predicates.
  • BOOSTED(<required>, <optional> [, <optional> ...]) — optional scoring composition (ES bool must + should): rows match iff <required> matches; each <optional> predicate only raises the score of rows that already match. Every argument must itself be a search predicate (possibly AND/OR/NOT-composed).
  • AT_LEAST(<n>, <predicate>, <predicate> [, ...]) — rows match when at least <n> of the search predicates match (ES should with minimum_should_match: n); every matching predicate adds its score. <n> is a positive integer no larger than the predicate count.
  • BOOST(<predicate>, <factor>) — the predicate's score multiplied by the positive numeric <factor> (ES per-field ^boost); matching is unchanged. Composes with AND/OR/NOT and the other shapes.
  • <col> may also be a declared .keyword twin (title.keyword, exact original string) or a copy_to composite field. Text predicates on a numeric/date/bool column error.
  • NOT returns only rows the index contains: a row with none of the indexed columns present is never returned by a search query.
  • score() projects the BM25 relevance of the row against the search predicates; it is also injected as a _score field (like _distance for vector search). Only valid together with a search predicate — else an error. Multi-field matches score dis-max (best field wins, ES best_fields).
  • HIGHLIGHT(<col> [, <max_chars> [, <pre_tag>, <post_tag> [, <no_match_size> [, <fragments> [, <boundary>]]]]] [, MATCH(...)]) projects the best-scoring snippet of the column's text (default fragment size 150 chars), matches wrapped in tags (<b>…</b> by default, HTML-escaped otherwise; empty string if the column didn't match). A pre_tag/post_tag string pair overrides the markers (ES pre_tags/post_tags); no_match_size returns that many leading characters when nothing matched (ES no_match_size); fragments 2–10 returns an array of fragments, 0 the whole field with every match marked; a trailing 'word' / 'sentence' (default 'chars') widens fragment cuts to word or sentence boundaries; a MATCH()/SEARCH() argument marks that query's terms instead of the statement's (ES highlight_query). Only valid together with a search predicate (SEARCH.md).
  • ORDER BY score() DESC LIMIT k pushes BM25 top-k retrieval into the index (no full scan); it requires LIMIT, and score() orders only descending. Without ORDER BY, matches return in unspecified order.
  • ORDER BY <col> [ASC|DESC] also works with search predicates: a single declared fast-field column with LIMIT retrieves index-ordered top-k directly; any other ordering (multi-key, expressions, non-fast columns) gathers the matches and sorts through the ordinary executor. Keyset pagination: ORDER BY <col> LIMIT k plus a residual range predicate (AND col > <last>).
  • The named column(s) must be covered by a search index on the table — errors if none exists. Query text may be a bind parameter (?).
  • Aggregates / GROUP BY work over search queries (SELECT region, COUNT(*) … WHERE MATCH(…) GROUP BY region) — keyword and time_bucket(step, date-col) groupings with COUNT/COUNT(DISTINCT col)/SUM/AVG/MIN/MAX push down as exact fast-field facets; every other shape (HAVING, text-column grouping, residual predicates) aggregates the gathered matching rows. See SEARCH.md.
  • COUNT(DISTINCT <expr>) counts distinct non-null values, exactly — in any aggregate query, search or not. DISTINCT arguments to other aggregate functions are not supported.
  • Cannot combine with JOIN, UNION, DISTINCT, or NEAREST. Residual WHERE conditions, LIMIT, and OFFSET apply normally, post-search.
  • Visibility: writes become searchable within refresh_ms (default 1 s, near-real-time); on the single-node write path a search after a write sees it immediately. The index is derived data — the table is the source of truth, and a lost or stale index rebuilds from it (automatically on restart, or via REBUILD SEARCH INDEX).
  • Cluster mode: the DDL broadcasts like other DDL, every node indexes its shard from replicated writes, and a search scatters to all members — per-shard top-k merged by score at the coordinator, survivors re-read at read consistency. Every acked write is searchable cluster-wide (replicas commit pending index writes before answering). Unreachable members are skipped; their rows surface through reachable replicas.

EXPLAIN SCORE (one row's BM25 breakdown) is described with EXPLAIN.

See also CREATE SEARCH INDEX · SUGGEST · Hybrid search · Reranking · Deep pagination · SEARCH.md

SUGGEST

SUGGEST '<text>' ON <index> [COLUMN <col>] [LIMIT <n>]

A standalone statement returning "did you mean" term suggestions (input, suggestion, distance, doc_freq — closest first, most frequent within a distance, at most n per input token, default 5). COLUMN is required when the index covers several text columns. For search-as-you-type completion, use an edge_ngram analyzer with MATCH_PREFIX instead.

See also CREATE SEARCH INDEX · Full-text search

Vector search (NEAREST)

NEAREST (<path>, <query-vector>, <k>) runs an approximate nearest-neighbor search over a vector index on (<table>, <path>), returning the <k> closest rows ordered nearest-first, with the match distance exposed as a _distance field:

CREATE VECTOR INDEX docs_emb ON docs (embedding) DIM 3 USING cosine;
SELECT id, _distance FROM docs NEAREST (embedding, [1.0, 0.0, 0.0], 5);
SELECT id FROM docs NEAREST (embedding, [1.0, 0.0, 0.0], 5) WHERE cat = 'news';

-- Managed (semantic_text) — embed a TEXT column via the [inference] provider:
CREATE VECTOR INDEX docs_sem ON docs (body) EMBED DIM 768;
SELECT id FROM docs NEAREST (body, 'natural language query', 10);  -- query auto-embedded

The query position takes, depending on the index on <path>:

  • a numeric array — a dense vector ([0.1, …]);
  • an array of arrays — a multi-vector query against a MULTI index, scored by MaxSim ([[…], […]]);
  • sparse([dims], [weights]), or a document in the stored shape, against a SPARSE index (exact dot product; _distance is its negation);
  • recommend([positive keys], [negative keys]) — the mean of the positive rows' vectors minus the negatives', with the seed rows excluded;
  • a string, auto-embedded, on a managed EMBED index.

Several NEAREST clauses in one SELECT, each over its own vector column, are a table's named vectors; they must be fused by RANK BY RRF (see SEARCH.md). SELECT DISTINCT ON (g) over a NEAREST query returns the best row per group, widening its own retrieval to cover the groups. Details and the index options in VECTOR.md.

EMBED makes the index managed: path names a TEXT column that skaidb embeds via the configured [inference] endpoint (rather than reading a pre-computed vector array), and a string NEAREST query on it is auto-embedded. Embedding is out of band — a write commits with the raw text and a background worker embeds it, so the model server being down delays searchability but never blocks or fails a write. Needs [inference] enabled and DIM matching the model; a managed index errors at create otherwise.

<query-vector> and <k> may be literals or bind parameters (?) in a prepared statement. Requires a vector index on the path; errors if none exists. Cannot combine with JOIN, UNION, aggregates/GROUP BY, or ORDER BY (results are already ordered by distance) — WHERE, LIMIT, and OFFSET apply normally, post-search.

See also CREATE VECTOR INDEX · Hybrid search · Reranking · VECTOR.md

Hybrid search (RANK BY RRF)

Fuse a full-text leg and a vector leg in one query by Reciprocal Rank Fusion. The NEAREST clause supplies the vector leg, the WHERE search predicate supplies the text leg, and RANK BY RRF fuses them:

SELECT id, rrf_score() FROM docs
NEAREST (embedding, [1.0, 0.0, 0.0], 100)   -- vector leg (100 candidates)
WHERE MATCH(body, 'quick brown fox')         -- text leg
RANK BY RRF                                  -- fuse (default constant 60)
LIMIT 10;

Each leg fetches the NEAREST <k> candidates, then a hit at 1-based rank r in a leg contributes 1 / (c + r) to its fused score; rrf_score() exposes the sum. Because fusion is rank-based, BM25 scores and vector distances need no normalization. Results are ordered by rrf_score() descending; LIMIT takes the final top-k.

  • RANK BY RRF (<c>) overrides the rank constant (default 60; must be ≥ 1).
  • The residual (non-search) part of WHERE filters both legs, e.g. WHERE MATCH(body, 'fox') AND published = true.
  • Requires both a NEAREST clause and a search predicate in WHERE. Cannot combine with JOIN, UNION, DISTINCT, GROUP BY, or ORDER BY (ordering is rrf_score() desc). Works cluster-wide (both legs scatter-gather; the coordinator fuses).

Reranking (RERANK)

Second-stage relevance: retrieve the top candidates by BM25 (or hybrid RRF), send (query, document) pairs to an external cross-encoder rerank endpoint ([inference] rerank_url — see VECTOR.md), and return the page in the reranker's order:

SELECT id, title, score() FROM docs
WHERE MATCH(body, 'how do I tune compaction')
RERANK TOP 100                               -- rerank the top 100 BM25 hits
LIMIT 10;

RERANK [ON <col>] [WITH '<model>'] [QUERY '<text>'] [TOP <n>] [TIMEOUT <ms>] — every part is optional:

  • ON <col> — the column whose text is sent as each candidate's document. Default: the columns the search predicate targets (all string fields for a field-less SEARCH('…')). Document text is capped at 4000 chars.
  • WITH '<model>' — the model name sent to the endpoint. Default: inference.rerank_model.
  • QUERY '<text>' — the query the reranker scores against. Default: the search predicate's text (e.g. the MATCH string).
  • TOP <n> — the candidate window (default 100, capped at 10,000; windows past 1,000 go to the endpoint in batches of 1,000 — cross-encoder scores are per (query, document) pair, so batching does not change them). LIMIT/OFFSET then page the reranked list.
  • TIMEOUT <ms> — this statement's request timeout for the rerank endpoint, overriding inference.timeout_secs (a slow model or a large window).

score() reads the rerank score. Composes with hybrid retrieval — … RANK BY RRF RERANK TOP 50 LIMIT 10 reranks the fused top-50 (rrf_score() keeps the fusion score). Requires a search predicate in WHERE; cannot combine with ORDER BY (the reranker orders results), GROUP BY, or aggregates. Runs coordinator-side over the already-gathered candidates, cluster-wide; it is opt-in per query, so a down rerank endpoint fails only queries that ask for it. ES clients reach the same path via the text_similarity_reranker retriever (SEARCH.md).

See also Model providers · Full-text search · VECTOR.md

Time-series tables

CREATE TIMESERIES TABLE declares a table whose rows are samples, stored in the time-series engine (Gorilla-compressed chunks; feature status and internals in TIMESERIES.md). Distributed: the DDL broadcasts, series place on the ring and replicate at the write consistency, and queries union-merge across members. Joins and decommissions migrate series like any other data.

CREATE TIMESERIES TABLE cpu (SERIES KEY (host, core), RETENTION 30d);
INSERT INTO cpu (host, core, ts, value) VALUES ('web1', '0', 1712000000000, 0.63);

SELECT time_bucket(1m, ts) AS t, host, avg(value), max(value)
FROM cpu WHERE ts >= now() - 1h AND host = 'web1'
GROUP BY t, host ORDER BY t;

SELECT time_bucket(5m, ts) AS t, rate(value) FROM cpu
WHERE ts >= now() - 6h GROUP BY t;

CREATE TIMESERIES TABLE

CREATE TIMESERIES TABLE [IF NOT EXISTS] <table>
       (SERIES KEY (<label> [, <label> ...])
        [, RETENTION <duration>] [, OOO <duration>])
  • Columns have roles. SERIES KEY columns are string labels (every insert must set all of them); ts is the sample timestamp (a timestamp or integer milliseconds, required, strictly increasing per series); every other inserted column is a numeric field. Names starting with __ are reserved.
  • Append-only. UPDATE/DELETE are rejected; old data expires via RETENTION <duration> (whole storage blocks drop as they age out). Older timestamps for a series are rejected per sample unless the table declares an out-of-order window (OOO 10m): samples within the window of the series' newest are buffered and merged in time order; an equal timestamp overwrites (last write wins).
  • Dropped points are reported. An INSERT whose samples fall outside the OOO window succeeds but returns the count that actually landed — affected: 0 when everything was discarded (one count per numeric field when rows carry several). Ingest code should compare affected against what it sent.
  • Retention and OOO are live-tunable. ALTER TABLE cpu SET (retention = 60d) / SET (ooo = 1h) apply without recreating the table: retention takes effect at the next flush (widening it cannot resurrect already-dropped blocks), the OOO window on subsequent inserts — temporarily widening it is the supported way to backfill history into a live table. retention = 0 clears retention (keep forever).

See also ALTER TABLE · CREATE ROLLUP · SHOW SERIES · TIMESERIES.md

Querying time series

  • Pushdown. AND-combined ts comparisons and label =/!= predicates narrow the storage read; any other predicate still applies with full SQL semantics afterward.
  • Time-series aggregates, valid wherever other aggregates are: rate(f) (per-second counter rate, reset-aware), increase(f) (total counter increase, reset-aware), delta(f) (last - first), first(f) / last(f) (value at the earliest/latest ts in the group). rate/increase/delta are computed per series over its time-ordered samples, then summed across the series in the group — PromQL sum(rate(...)) semantics. Group by label columns to keep series separate.
  • GROUP BY/ORDER BY may reference output aliases (GROUP BY t with time_bucket(1m, ts) AS t) — on every table kind; see the general alias-resolution rules under Output aliases and positions.
  • Rollup query rewrite: an aggregate query on the source table whose window reaches past the source's RETENTION horizon answers the aged buckets from the coarsest rollup whose BUCKET divides the group's time_bucket step, stitched with exact source data for the within-retention part — so long-range dashboards keep working after raw samples age out, without naming the rollup. Applies to count/sum/avg/min/max/first/last; rate/increase/delta need raw samples and never read rollups. In the rollup-served region, first()/last() order series at bucket granularity, and the window edge trims to whole rollup buckets.
  • Not supported on time-series tables: JOIN, UNION, NEAREST, transactions.

See also Aggregate functions · Scalar functions · CREATE ROLLUP · TIMESERIES.md

SHOW SERIES

SHOW SERIES FROM <ts-table> [WHERE <filter>] [LIMIT <n>]
                                        -- the series catalog: one row per series,
                                        -- series-key columns + first_ts, last_ts;
                                        -- served from series metadata, never samples
                                        -- (TIMESERIES.md); needs SELECT on the table.
                                        -- <filter> names series-key columns and
                                        -- first_ts / last_ts (`last_ts >= now() - 15m`
                                        -- lists the series still reporting)

See also CREATE TIMESERIES TABLE · TIMESERIES.md

CREATE ROLLUP

CREATE ROLLUP [IF NOT EXISTS] <name> ON <ts-table> BUCKET <duration>
       [RETENTION <duration>]
CREATE ROLLUP [IF NOT EXISTS] <name> ON <event-table> BUCKET <duration>
       [BY (<dim> [, <dim> ...])]
       AGGREGATE (count(*)|count(<col>)|sum(<col>)|min(<col>)|max(<col>) AS <alias> [, ...])
       [REFRESH <duration>] [RETENTION <duration>]
       -- event-table form: pre-aggregated bucket table over a
       -- partitioned/clustered regular table (see Event-table rollups)

Time-series rollups

Rollups (CREATE ROLLUP r30m ON cpu BUCKET 30m RETENTION 90d): a derived time-series table holding per-bucket partials of its source, maintained automatically when source windows flush. For each source field f it stores f_count, f_sum, f_min, f_max, f_first, f_last (so avg = f_sum / f_count), keyed by the same series labels at the bucket-start timestamp. Query it like any TS table (SELECT ts, value_sum / value_count FROM r30m WHERE host = '...'). BUCKET must evenly divide the 2 h storage window. Dropped with DROP TABLE (dropping the source cascades). Repair-merged samples do not retroactively update rollups.

Event-table rollups

The event-table form, CREATE ROLLUP <r> ON <event-table> BUCKET <duration> [BY (<dims>)] AGGREGATE (<agg> AS <alias>, …) [REFRESH <duration>] [RETENTION <duration>] maintains a pre-aggregated bucket table over a partitioned or clustered regular table: one row per (bucket, dims…) holding the declared aggregates (count(*), count(col), sum, min, max) — dashboards query the small rollup (SELECT bucket, n FROM r WHERE bucket >= …) instead of re-scanning raw events.

The bucketed column is inferred: the source's partition column, else its clustering head (a source with neither is refused — recompute must prune). The rollup is a real table with PRIMARY KEY (bucket, dims…): query it, index it, back it up like any other.

A background pass (~1 min cadence; on clusters one member owns each rollup) recomputes the trailing REFRESH window (default 2 buckets) from the raw source and upserts the buckets — recompute replaces rather than increments, so retries, ownership handovers and replica divergence all converge to the same values; late rows within the window are absorbed on the next pass. The same pass deletes the window's buckets the recompute no longer produces (every source row of the bucket deleted, or a dim value moved to another group), so the rollup tracks removals as well as additions. A row arriving LATER than the refresh window does not update its bucket, and a bucket outside the window is not reconciled — both wait for a repopulation. On creation the first pass populates the full history (and so does the first pass after a restart, which reconciles every bucket).

RETENTION expires rollup rows by age (independent of the source's ttl — keep 13 months of hourlies over 30 days of raw events). Groups whose bucket or dim value is missing/NULL are skipped. Dropping the source drops its rollups; SHOW CREATE TABLE <r> shows the full rollup definition followed by its indexes. Rollups of rollups are refused.

Indexes on a rollup

CREATE INDEX <i> ON <r> (<col>) on a dimension or an aggregate alias is an ordinary range-capable secondary index: the planner uses it for WHERE col >= a AND col < b, for ORDER BY col [DESC] LIMIT k (an index-ordered walk with early stop) and for index-only range counts, exactly as on a plain table (a rollup with RETENTION counts by scan, the rule for every table with a ttl). Every refresh maintains it through the write path — an upserted bucket replaces its old entry, a reconciled-away bucket loses its entry — on every replica of a cluster. It is listed by SHOW INDEXES, rendered by SHOW CREATE TABLE <r>, replayed with the rollup on schema sync, mirrored to witnesses with witness.mirror_indexes, and dropped with the rollup (and with the source, through the cascade).

See also CREATE TIMESERIES TABLE · Range partitioning · Clustering · Querying time series · SHOW CREATE TABLE

Streams, procedures, jobs and triggers

CREATE STREAM

CREATE STREAM [IF NOT EXISTS] <name> ON <table> WHEN (<predicate>)
       [WITH (start = 'now' | 'earliest' [, retention = '<duration>'])]
       -- a standing filter over the table's writes; see STREAMS.md
DROP   STREAM [IF EXISTS] <name>
SHOW   STREAMS   -- (name, table, predicate, consumers, lag)

CREATE STREAM declares a standing filter over a table's writes: every committed change to <table> is matched against the WHEN predicate, which is an ordinary boolean expression evaluated like a WHERE clause. retention accepts a duration ('24h', '90m', or a bare 30d); start is 'now' (future writes only, the default) or 'earliest' (first backfills every row that already matches), and any other start is refused rather than silently ignored. pre_image = true adds the row as it was before the change, in an old column. The predicate is stored as text and re-parsed on open, which is also what SHOW STREAMS displays.

Events land in a table you can read today. Each matching write appends to _stream_<name>, an ordinary table in the same database: columns id (position), op (put, exit when a row stops matching, or delete), k (the row's primary-key value), ts, doc (the row; for a delete, the row as it last was) and, with pre_image, old. It replicates like any table, retention is its TTL, and a replay is an ordinary ordered read: SELECT * FROM _stream_orders WHERE id > '<last>' ORDER BY id LIMIT 500. Dropping the stream drops the log. With the MQTT broker enabled, each event is also pushed live to $stream/<db>/<name> (best-effort, so a consumer that missed one replays the log from its last id). Delivery is at-least-once and ordered per key only; see STREAMS.md. A stream that matches most writes roughly doubles the table's write volume, since every matching change writes a second replicated row.

See also CREATE TRIGGER · PUBLISH · STREAMS.md

CREATE PROCEDURE

CREATE PROCEDURE [IF NOT EXISTS] <name>([IN | OUT | INOUT] <param> <TYPE> [, ...])
       BEGIN <step>; [<step>; ...] [EXCEPTION WHEN <cond> THEN <step>; ...] END
       -- a step is a SQL statement, a nested CALL, or control flow:
       --   DECLARE <v> <TYPE> [DEFAULT <expr>] | DECLARE <c> CURSOR FOR <select>
       --   SET <v> = <expr> | SELECT ... INTO <v> [, <v>] FROM ...
       --   IF <c> THEN ... [ELSEIF <c> THEN ...] [ELSE ...] END IF
       --   WHILE <c> DO ... END WHILE | FOR <v> IN (<select>) DO ... END FOR
       --   OPEN <c> | FETCH <c> INTO <v> [, <v>] | CLOSE <c> | LEAVE
       --   RETURN [<expr> | {<k>: <expr>, ...}] | EMIT <select> | BEGIN ... END
       -- see PROCEDURES.md
DROP   PROCEDURE [IF EXISTS] <name>
CALL   <name>(<expr> [, <expr> ...])   -- returns the last statement's result set
SHOW   PROCEDURES  -- (name, params, definer, created)

CREATE PROCEDURE stores a statement list in the catalog that CALL runs server-side, so a fixed sequence of statements costs one round trip instead of N. A body takes SELECT, INSERT, UPDATE, DELETE and nested CALL — no DDL, no BEGIN/COMMIT, no dynamic SQL — and each statement ends with ;, including the last. Nesting is capped at 16 deep, which is what ends a recursive cycle. Parameters carry a declared type (TEXT, INT, FLOAT, BOOL, TIMESTAMP, JSON, ARRAY, ANY) checked against the argument at the call; the store stays schema-less, so the type is a contract on the call and nothing else. A CALL returns the last statement's result set, inherits the caller's scan budget and deadline, and runs with invoker rights: EXECUTE on the procedure plus every privilege the body itself needs.

Control flow is a PSM subset, not an embedded VM: every leaf is a statement the ordinary executor already runs. A WHILE has no iteration cap — it is bounded by the caller's scan budget and deadline, which is the only termination guarantee — and an EXCEPTION handler can catch data errors (NOT_FOUND, CONSTRAINT, UNIQUE_VIOLATION, FOREIGN_KEY_VIOLATION, NOT_NULL_VIOLATION, CHECK_VIOLATION, TYPE_ERROR, UNSUPPORTED, OTHERS) but never a resource limit or a storage/cluster error. A cursor is a resumable keyset scan over the primary key, not a snapshot: it sees writes made while it is open. See PROCEDURES.md.

See also CREATE JOB · CREATE TRIGGER · GRANT · PROCEDURES.md

CREATE JOB

CREATE JOB [IF NOT EXISTS] <name>
       ON SCHEDULE { EVERY '<duration>' | CRON '<expr>' }
       CALL <procedure>(<expr> [, ...]) [WITH (<option> = <literal>, ...)]
       -- options: timezone (UTC or a fixed +HH:MM offset; CRON only),
       -- catchup, timeout, budget, ttl, result_cap; see PROCEDURES.md
DROP   JOB [IF EXISTS] <name>
SHOW   JOBS  -- (name, source, procedure, definer, owner, next_run,
             --  last_run, last_status, last_error, failures)

CREATE JOB runs a stored procedure on a schedule — EVERY '<duration>' (a duration since the last tick) or CRON '<expr>' (calendar instants, five fields, no seconds). Exactly one node runs each job: the ring picks a preferred owner and a linearizable CAS lease makes that correct. A job runs as the role that created it, always with an explicit timeout and row budget (an unattended run has no caller to inherit one from), and its state and history live in the feature-owned _job_state / _job_runs tables, which clients may read and nothing else. Delivery is at-least-once, so a body should key its writes on SCHEDULED_AT() — the logical tick, stable across attempts. Timezones are UTC or a fixed +HH:MM offset; named zones are refused, since skaidb embeds no timezone database. See PROCEDURES.md.

See also CREATE PROCEDURE · CREATE TRIGGER · PROCEDURES.md

CREATE TRIGGER

CREATE TRIGGER [IF NOT EXISTS] <name> ON <table> WHEN (<predicate>)
       CALL <procedure>(<expr> [, ...]) [WITH (<option> = <literal>, ...)]
       -- a job whose source is a change stream; owns a hidden stream
DROP   TRIGGER [IF EXISTS] <name>   -- removes the trigger AND its stream
SHOW   TRIGGERS  -- (name, table, predicate, procedure, definer, stream,
                 --  cursor, last_status, last_error, failures)

CREATE TRIGGER is the same job with a change stream as its source instead of a schedule: it owns a hidden stream carrying pre-images, and DROP TRIGGER removes both. The body sees NEW, OLD (NULL on an insert), EVENT() and OP, all from the log rather than from a re-read. It fires from the log, never inline on the write path. A cascade cycle between triggers is refused at declaration — a cascade without one is a DAG and terminates by construction.

See also CREATE STREAM · CREATE PROCEDURE · CREATE JOB · PROCEDURES.md

PUBLISH

PUBLISH '<topic>' PAYLOAD <expr> [QOS 0|1|2] [RETAIN]

PUBLISH sends one MQTT message from SQL, handed to the node's broker (MQTT.md): subscribers on this node receive it, peers get the usual forward, a RETAIN lands in the retained store, QOS is the delivery QoS (default 0). The topic is a literal concrete name (no +/#, not the broker-owned $ tree); the payload is any expression — a string publishes its bytes, a document or array its JSON, NULL an empty message, other values their text. Needs PUBLISH on the topic under the role's MQTT topic grants (a role with no topic grants keeps the open default, like a connected client); the node must run the broker ([mqtt] enabled = true). Allowed in procedure bodies, so a trigger can publish a change (docs/PROCEDURES.md); a CALL is then gated on the body's topics. Returns one affected row. Delivery only: topic→table capture rules run on client publishes.

See also CREATE TRIGGER · GRANT · MQTT.md

Transactions

BEGIN, COMMIT, ROLLBACK

BEGIN [TRANSACTION]                       -- embedded / standalone driver connections
BEGIN ATOMIC [TRANSACTION]                -- cluster: cross-partition txn (two-phase commit)
BEGIN ATOMIC PARTITION (<col> = <literal> [, ...])  -- cluster: single-shard txn
COMMIT [TRANSACTION]
ROLLBACK [TRANSACTION]

BEGIN/COMMIT/ROLLBACK wrap several statements in a transaction with read-your-writes: buffered writes are invisible to every OTHER session until COMMIT (transaction state is per-connection), and ROLLBACK discards them. Available on the embedded engine and over binary-driver connections to a standalone server; stateless surfaces (REST/ES/UI) autocommit, and cluster mode refuses (no distributed transaction coordinator). Concurrent sessions' transactions coexist; conflicting commits resolve last-writer-wins. DDL is not transactional.

Measured ACID contract (acid-crash harness, kill -9 rounds): every acknowledged statement survives a crash (durability is fsync-on-ack), and a transaction commit is all-or-nothing across crashes — COMMIT persists a redo journal before applying, and recovery replays an interrupted commit to completion (a crash before the journal is durable means the transaction cleanly never happened).

See also BEGIN ATOMIC · BEGIN ATOMIC PARTITION · Concurrency and atomicity

BEGIN ATOMIC

BEGIN ATOMIC [TRANSACTION] (no pin) is the CLUSTER cross-partition transaction: rows of any row tables — several partitions, plain replicated tables — buffer with the same overlay and commit all-or-nothing; a write set spanning more than one placement group takes two-phase commit (a prepared manifest entry per group, then one decision-row round as the atomic point, then the applies; replicas' sweepers carry out a committed decision and abort an entry left undecided past a minute). Same refusals as the pinned form; UNIQUE indexes enforced at COMMIT. Details in CLUSTER_PLACEMENT.md.

See also BEGIN ATOMIC PARTITION · CLUSTER_PLACEMENT.md

BEGIN ATOMIC PARTITION

BEGIN ATOMIC PARTITION (col = lit, ...) is the CLUSTER transaction: a single-shard transaction pinned to one partition of distribute_by tables (over a binary-driver connection). Every statement inside may touch only rows whose distribution columns equal the pinned literals — across any number of tables distributed by those columns — and reads see the session's own buffered overlay. COMMIT applies the write set all-or-nothing: it serializes on the partition through a consensus round whose committed manifest embeds the write set, so a coordinator crash after the round is completed forward by every replica's sweeper (never a partial commit).

Isolation is atomicity only — concurrent transactions and non-transactional writes resolve last-writer-wins per row; there is no read-set validation.

UNIQUE indexes are enforced at COMMIT (every introduced value is claimed through the reservation round before the partition round, so a duplicate fails the whole transaction with nothing applied; retired values are released after the apply).

Constraints: the buffered write set is capped at 8 MB; tables with GLOBAL indexes or value-TOAST are refused (those write legs live outside the partition); DDL and USE are refused while a transaction is open; time-series writes cannot participate.

Transaction control requires the INSERT privilege on the session's current database (the same gate as embedded BEGIN) — a grant ON * also satisfies it. The gate decides only whether a session may open a transaction; every statement inside is still authorized against its own table.

Clients MUST stop on a failed BEGIN: the statements that follow would otherwise execute in autocommit — durable partial writes where a transaction was intended.

See also BEGIN ATOMIC · CLUSTER_PLACEMENT.md

Databases and sessions

CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] <name>
DROP   DATABASE [IF EXISTS] <name>
USE    [DATABASE] <name>
SHOW DATABASES

CREATE/DROP DATABASE, USE, and SHOW DATABASES manage databases — each is an isolated set of tables and indexes. A database is a namespace: internally a table is stored under a per-database name, with the implicit default database using unprefixed names (so an existing single-database directory keeps working unchanged — its tables are the default database). USE sets the current database for the connection (the skaidbsh shell, or a binary-protocol connection); unqualified table names resolve against it, and db.table reaches another database without switching. SHOW DATABASES lists them as (database, current) rows with * marking the current one; SHOW TABLES/SHOW INDEXES are scoped to the current database. Server-owned databases whose name starts with _ (_bi, _mqtt) are listed only for a role holding ADMIN — a listing convenience, not a privilege boundary: access to them is governed by grants like any other database. The default database cannot be dropped; dropping the current database (or its cascade) reverts the connection to default.

  • DROP DATABASE cascades to everything in the database: its tables (time-series tables and rollups included) with every index on them, views and materialized views, sequences, types and domains, streams, procedures, jobs and triggers. It also revokes, from every role, each grant on the database itself (ON DATABASE <name>) and on its tables, columns and procedures. A database created later under the same name therefore starts empty and with no grants; global grants, topic grants and grants on other databases are untouched.
  • Replication: in cluster mode, CREATE/DROP DATABASE broadcast to every node (like other DDL), and writes inside any database replicate by the same quorum/hinted-handoff/read-repair path as the default database. The REST /query gateway is stateless — it always starts at default, so reach other databases there with db.table qualifiers rather than USE.
  • USE waits on nothing: it checks the name against the node's catalog as of its last schema change, which the node keeps apart from its data — a long write elsewhere on the node does not delay it. SHOW TABLES reads the same copy.
  • Ensure-schema statements stay local: CREATE DATABASE, CREATE TABLE and CREATE INDEX (not WITH (global = true)) … IF NOT EXISTS of an object the node receiving them already holds change nothing, and the node answers them itself from that copy of its catalog — without contacting the other members of a cluster, and without waiting for a long write — so an application may run them on every start or cycle. A member that missed the object gets it from the schema sync of the next anti-entropy pass, as for any other DDL. IF NOT EXISTS of an existing object neither compares its definition nor validates the rest of the statement (a vector, search or geo index's IF NOT EXISTS does compare — those still broadcast).

See also SHOW TABLES · GRANT · Identifiers & literals

Session settings

SET CONSISTENCY { ONE | QUORUM | ALL }        -- per-connection override
SET SCAN BUDGET ROWS <n> [BYTES <n>]          -- session scan limits (tightening-only)
SET SCAN BUDGET DEFAULT                       -- clear the session override

SET CONSISTENCY is per-connection session state on binary-protocol sessions (it overrides the wire consistency until changed); the stateless REST gateway rejects it with guidance.

SET SCAN BUDGET ROWS <n> [BYTES <n>] is per-connection too, and tightening-only: statements run under min(node config, session) per axis, so a session can restrict what its own statements may scan but never lift the operator's ceiling — which is why any authenticated role may issue it (values must be positive; DEFAULT clears). One trap to know: after a silent driver reconnect the session reverts to node defaults — drivers replay the session database but not this setting (the Rust driver's set_scan_budget_rows is the exception: it stores and replays it).

See also SET CONFIG · Streamed queries · CLUSTER_CONSISTENCY.md

Access control

Users and roles

CREATE USER [IF NOT EXISTS] <name> PASSWORD '<password>'
CREATE USER [IF NOT EXISTS] <name> GSSAPI      -- external Kerberos principal,
                                               -- no local secret; <name> is the
                                               -- principal, e.g. "user@REALM"
                                               -- (double-quote it — it contains
                                               -- '@' and '.')
ALTER  USER <name> PASSWORD '<password>'
DROP   USER [IF EXISTS] <name>
CREATE ROLE [IF NOT EXISTS] <name>
DROP   ROLE [IF EXISTS] <name>

A user authenticates (SCRAM on the binary protocol, HTTP Basic on REST) and acts as its own-named role; GRANT ROLE r TO u adds inherited roles. A user created … GSSAPI is external: it has no local password — a Kerberos KDC vouches for the principal, and skaidb only maps the authenticated principal to its own-named role (grants and role inheritance work identically). An external user cannot authenticate by password/SCRAM, and a password user is never reachable through the external path.

Users/roles persist in the catalog, replicate like other DDL (passwords travel between nodes only as salted SCRAM verifiers, never plaintext), and converge via schema repair. Each credential carries a random salt, drawn once when the password is set and stored inside the verifier, so it is identical on every member; ALTER USER … PASSWORD draws a fresh one. The config-file superuser remains the bootstrap principal. The Prometheus endpoints are covered too: remote_write requires INSERT and /api/v1/query* require SELECT on the metrics table.

See also GRANT · KERBEROS.md · CLUSTER_SECURITY.md

GRANT and REVOKE

GRANT  <privilege> [(<column>, …)] ON { <table> | DATABASE <db> | TOPIC '<filter>' | * } TO <role>
REVOKE <privilege> [(<column>, …)] ON { <table> | DATABASE <db> | TOPIC '<filter>' | * } FROM <role>
GRANT  ROLE <role> TO <user>
REVOKE ROLE <role> FROM <user>
SHOW GRANTS [FOR <role>]

<privilege> is one of SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, MONITOR, PUBLISH, SUBSCRIBE, EXECUTE, BI, ADMIN (ADMIN on * = superuser; ADMIN on a table implies every privilege on it). BI (granted ON *) lets a role use the business-intelligence app on a BI node — a separate decision from reading a table through a driver, and it widens nothing: what the role may read is still exactly its SELECT grants (see BI.md). MONITOR (granted ON *) opens the read-only control plane — SHOW CLUSTER, SHOW CONFIG (secrets stay masked), SHOW SLOW QUERIES, and the matching read-only HTTP admin endpoints — so an application role can report cluster health and its effective config without an admin credential; it never authorizes a mutation (SET CONFIG, repair, membership stay ADMIN).

Delegating GRANT

GRANT is delegable per object: GRANT / REVOKE of a privilege need the GRANT privilege on the object the statement names — on the database for ON DATABASE <db>, on the table or its database for ON <table> (and likewise a procedure). A role holding GRANT ON DATABASE app therefore administers access to app — it can grant and revoke any privilege on app, its tables and its procedures, to any role — and to nothing else: ON *, ON TOPIC, another database, and user/role management (CREATE/ALTER/DROP USER, CREATE/DROP ROLE, GRANT ROLE, SHOW GRANTS) need GRANT ON *. Holding GRANT on a database gives no access to its data by itself.

Index DDL privileges

Index DDL is table-scoped: CREATE INDEX needs CREATE on the target table, and DROP INDEX / REBUILD/ALTER of an index need that same CREATE on the index's owning table — an index is derived data, so a role that can create its own indexes can also drop or retune them (no DROP on Global needed; DROP TABLE still requires DROP). Dropping a nonexistent index needs no privilege (IF EXISTS stays an idempotent no-op; index existence is already free via SHOW INDEXES).

Database and table grants

A grant ON DATABASE db covers every table in that database (checked against the session's current database; shown by SHOW GRANTS as db:<name>).

A table grant is scoped to the table's canonical <database>.<table> identity, not the raw name. A bare GRANT … ON t issued while the session is in database d grants d.t — never a wildcard that would match a same-named t in another database — and a qualified GRANT … ON d.t authorizes the natural USE d; … t query just as it authorizes … FROM d.t (both resolve to the same table). SHOW GRANTS renders the object as <database>.<table> (bare for the default database).

Column-level grants

GRANT SELECT (id, email) ON users TO r (also INSERT (…) and UPDATE (…)) grants the privilege on the listed top-level fields only, one grant per column; REVOKE SELECT (email) ON users FROM r takes one back. A statement runs under a column grant when every column it touches on the table is granted: for SELECT, whatever the projection, predicate, ORDER BY/GROUP BY, join condition (a qualified u.col is charged to the aliased table), subquery, or set-operation leg names — SELECT * needs the table grant, count(*) needs no column; for INSERT, the statement's column list (a list-less INSERT needs the table grant); for UPDATE, the assigned columns. A column-level INSERT/UPDATE still needs SELECT on whatever the statement reads of the table (assignment expressions, the predicate, RETURNING), because an assignment or RETURNING could copy a column out. A nested path (addr.city) is charged to its root field (addr). Column grants inherit through GRANT ROLE, a table- or database-level grant supersedes them, and SHOW GRANTS renders each as <table>(<column>). A denial names the granted set and the column the statement wanted. DELETE has no per-column form.

Who may manage access

All management statements (and SHOW GRANTS) require the GRANT privilege cluster-wide — except SHOW GRANTS FOR <your own role>, which any authenticated role may run to inspect itself. Auth DDL (user/role/grant changes) is recorded in the identity audit log (the login-log category) with the acting role and a secret-free statement summary.

See also Users and roles · CREATE VIEW · Writes through a view · BI.md

Introspection

Read-only catalog statements; none of them reads table data unless its section says so.

SHOW TABLES and SHOW INDEXES

SHOW TABLES
SHOW INDEXES

SHOW TABLES lists catalog tables as (table, primary_key, replication, nodes, witness, transition, kind) rows — replication/nodes per-table placement, witness the mirroring flag, transition whether a placement change's dual-placement window is currently open, kind one of row / timeseries / rollup / view / matview. On a cluster a trailing sharded column is true when no single member holds every row of a row table (its effective RF, the per-table override or the cluster default, is below the member count and it is not pinned); SHOW INDEXES lists secondary, vector, and search indexes as (index, table, kind, columns, local, definition) — definition is the canonical CREATE … IF NOT EXISTS statement that recreates the index exactly (the same rendering cluster schema sync replays, so vector DIM/metric/QUANTIZED/EMBED, search analyzer options, UNIQUE and global flags are all present); local is this node's live state for the index: ok (open and serving), building (backfill or catch-up running), or missing (in the catalog but no live index — the divergence to check first when one node's searches fail while its peers' succeed). Both are read-only and require no special privilege, so a monitoring/tooling agent can enumerate the schema without /query data access. In cluster mode they answer from the local catalog — ask each node to compare local states across the ring. SHOW TABLES reads the node's catalog as of its last schema change without waiting for the node's other work, so a long write does not delay it.

See also DESCRIBE · SHOW CREATE TABLE · Per-table placement · CREATE INDEX

SHOW CREATE TABLE

SHOW CREATE TABLE <table>                     -- the table's DDL + its indexes + constraints, replayable

SHOW CREATE TABLE <t> renders the table's definition as replayable DDL — the CREATE TABLE (or CREATE TIMESERIES TABLE / CREATE ROLLUP) statement carrying every catalog option (ttl, memory, replication, nodes, witness, toast_threshold, distribute_by), followed by one row per index on the table (secondary/global, vector, search, geo). One statement column, one row per statement; works on hidden __toast__/ __gidx__ companions when named explicitly. Needs no privilege beyond connecting (same class as DESCRIBE).

See also DESCRIBE · SHOW TABLES

DESCRIBE

DESCRIBE <table>                              -- alias DESC <table>; catalog columns only
DESCRIBE <table> FULL [SAMPLE <n> | EXACT]    -- also reads rows: every field and its types

DESCRIBE <table> (alias DESC <table>) is one table's structure as (column, key, indexes) rows — one per column that is part of the primary key or an index. key is primary key (with a (n/m) position for a composite key); indexes lists the covering indexes as name (kind) (secondary, secondary, building, vector, search), and a MULTIKEY [] suffix is stripped from the column name. A generated column is listed too, with generated stored / generated virtual as its key (an index over a VIRTUAL column's expression is attributed to the column). Rows come out primary-key columns first (in key order), then the remaining columns alphabetically.

Like the SHOW pair it is read-only, needs no privilege, and answers from the local catalog — no data is read. The store is schema-less, so a column that is neither part of the key nor indexed is not in the catalog and does not appear here. An unknown table is an error. Accepts a db.table qualifier.

  • DESCRIBE <table> FULL [SAMPLE n | EXACT] additionally reads the data to surface every field — the schema-less fields the catalog can't know. It returns (column, type, key, indexes): the added type is the set of value types seen for that field, joined by | (a field can hold several types). Because it reads rows, FULL requires the SELECT privilege (plain DESCRIBE does not) and, on a cluster, reads the local shard (complete when RF ≥ members). Time-series tables report catalog columns only (blank types).
    • Default/SAMPLE n: reads the first n rows in primary-key order (default 1000), streamed so it reads at most n rows and never materializes the table — a field that appears only in rows outside the sample is not seen; widen SAMPLE to trade cost for completeness.
    • EXACT: scans all rows (streamed — memory stays bounded regardless of table size) and caches the field map in a RAM field registry, stamped with the table's write sequence. Repeated EXACTs on an unchanged table answer from RAM in O(fields); any write, delete, or reclaim to the table invalidates the stamp so the next call rescans — results are always exact, including fields disappearing when their last row is deleted. The registry is process-local (a restart clears it; the first EXACT after rebuilds) and is skipped for TTL tables, whose row visibility decays without writes — EXACT on those rescans every call.

See also SHOW TABLES · SHOW CREATE TABLE · Generated columns

SHOW STATUS

SHOW STATUS

SHOW STATUS returns storage and runtime statistics as (metric, value) rows — the node's totals (table/index counts, on-disk and memtable bytes, SSTable count, WAL bytes/fsyncs, compactions, cache hit/miss/hit-rate) and a per-table table.<db>.<name>.* breakdown of the current database only (other databases' tables, search indexes and time-series tables are not listed): row tables report {live_keys,tombstones,disk_bytes}, TIME-SERIES tables report {kind=timeseries,series,samples_appended,disk_bytes} (there are no tombstones and no cheap exact live-sample count; the legacy timeseries.<name>.* keys remain). It is the same data the server publishes at GET /metrics, surfaced as SQL.

See also SHOW MAINTENANCE · METRICS.md

Other catalog listings

SHOW CONSTRAINTS [ON <table>]                 -- NOT NULL / DEFAULT / IDENTITY / GENERATED / CHECK / FOREIGN KEY, one view
SHOW FOREIGN KEYS [ON <table>]                -- foreign keys declared on / referencing a table
SHOW SEQUENCES                                -- every sequence of the current database + its counter
SHOW VIEWS                                    -- views and materialized views of the current database, dependency order
SHOW TYPES                                    -- enum types and domains of the current database (SHOW DOMAINS is the same)
SHOW ENDPOINTS                                -- every member's client endpoint, its state
                                              -- (ready | resyncing | unreachable, as the
                                              -- answering member sees it) and `local` on the
                                              -- answering member; no privilege needed, no rows
                                              -- on a standalone server. What driver pools read
                                              -- to balance across members (PROTOCOL.md §6.1)

Each listing is described with the objects it lists: SHOW CONSTRAINTS with Column constraints, SHOW FOREIGN KEYS with FOREIGN KEY, SHOW SEQUENCES with Listing and exporting sequences, SHOW VIEWS with CREATE MATERIALIZED VIEW, and SHOW TYPES with Enum types and domains. SHOW ENDPOINTS is what driver pools balance on — PROTOCOL.md §6.1.

See also SHOW TABLES · DESCRIBE

Administration

The admin control plane runs on the network server only. Reads need MONITOR on *, mutations need ADMIN on * (ADMIN implies MONITOR).

Admin statements

SHOW CLUSTER                                  -- ring, members, epoch, liveness
SHOW CONFIG [LIKE '<pattern>']                -- flattened keys, secrets masked
SET  CONFIG <section.field> = <literal>       -- live-mutable keys apply instantly (ADMIN)
SHOW SLOW QUERIES [LIMIT <n>]                 -- masked slow-query sample

Admin statements (SHOW CLUSTER/SHOW CONFIG/SET CONFIG/ SHOW SLOW QUERIES/REPAIR CLUSTER/RECLAIM/ALTER CLUSTER) are the SQL spellings of the HTTP /admin/* control plane — identical handler, RBAC (reads: MONITOR on *; mutations: ADMIN on *), and audit; results come back as (key, value) rows (LIKE uses %/_). They execute on the network server — the embedded --local engine rejects them.

See also Cluster membership and repair · SHOW QUERIES · Session settings · GRANT and REVOKE

SHOW QUERIES and KILL QUERY

SHOW QUERIES                                  -- statements running NOW on this node
                                              -- (id, user, db, via, elapsed_ms,
                                              --  rows_examined, sql)
KILL QUERY <id>                               -- cooperatively terminate a running
                                              -- statement by its SHOW QUERIES id (ADMIN);
                                              -- it fails with "terminated by KILL QUERY"
                                              -- at its next scan tick, or within 0.2 s
                                              -- while it waits on another member's reply

See also SHOW MAINTENANCE · CHECK INDEX · Streamed queries

SHOW MAINTENANCE

SHOW MAINTENANCE                              -- background jobs running NOW on this
                                              -- node (id, kind, target, elapsed_ms,
                                              -- progress — "tables 37/415 — verifying:
                                              -- t@peer (since +12 s)", "index 1/3 x:
                                              -- 20480 rows read, 20480 queued"): anti-entropy passes,
                                              -- content-digest builds, index/vector/
                                              -- geo backfills, search catch-ups,
                                              -- hint drains, flush builds,
                                              -- compactions, embed drains

See also SHOW QUERIES · SHOW STATUS · CHECK INDEX

Cluster membership and repair

REPAIR CLUSTER                                -- one anti-entropy pass on every member; answers
                                              -- repaired / complete / incomplete.<member>
                                              -- (complete = every member reconciled every
                                              -- table with every peer; see CLUSTER_REPAIR.md)
RECLAIM                                       -- drop unowned keys/series
ALTER CLUSTER ADD    NODE '<host:port>'
ALTER CLUSTER REMOVE NODE '<id>' [FORCE]     -- FORCE: drop a node that is down or
                                              -- wedged WITHOUT draining it (then
                                              -- REPAIR CLUSTER re-replicates its keys)
ALTER CLUSTER SET NAME '<name>'               -- rename the cluster (ADMIN)
ALTER NODE '<alias|dotted|id>' SET NAME '<n>' -- rename a member/witness alias (ADMIN)
RESET OOM COUNTER [ON '<node>']               -- zero the OOM-kill counter reported in
                                              -- node_stats/host stats, on every member
                                              -- AND registered witness, or on one node
                                              -- by its id / witness id (ADMIN); returns
                                              -- (node, absorbed, status) — witnesses
                                              -- apply on their next pull cycle
                                              -- (absorbed = NULL, status "stamped")

See also Per-table placement · SHOW TABLES · CLUSTER_MEMBERSHIP.md · CLUSTER_REPAIR.md

Advisor

SHOW ADVICE [FOR DATABASE d] [LIMIT n]        -- the advisor's findings the caller may
                                              -- see (docs/ADVISOR.md); no privilege
ALTER ADVICE '<id>' SET STATUS '<status>' [NOTE '…']  -- acknowledged | dismissed | fixed |
                                              -- false_positive | new, on a visible finding
SHOW ADVISOR                                  -- settings, advisor node, last run (MONITOR)
ALTER ADVISOR SET (schedule = '5m'|'off', model_schedule = '30m'|'off', on_observation = false,
                   docs_grounding = true, sample_databases = 'd1,d2', categories = 'database,os,bug',
                   node = '<member>', enabled = true, provider = '<name>'|'none',
                   data_sharing = 'shapes'|'none'|'samples',
                   token_budget_per_day = 300000, max_runs_per_hour = 4)   -- ADMIN
CALL advisor.run()                            -- the rules now, then the model stage when
                                              -- a provider is set (ADMIN); returns SHOW ADVICE
CALL advisor.run('<db>')                      -- scoped model run for one database (anyone
                                              -- who may read it); its database findings only
CALL advisor.export_bug_report('<id>')        -- ADMIN: a Markdown bug report for one finding,
                                              -- written by the provider, stored on the finding
CALL advisor.digest()                         -- ADVISOR ON * / ADMIN: the digest as one JSON row
                                              -- (the MCP pull mode)
CALL advisor.report('<findings json>')        -- ADVISOR ON * / ADMIN: land an external agent's
                                              -- findings (validated; origin 'external')

See also Model providers · EXPLAIN · ADVISOR.md

Model providers

CREATE MODEL PROVIDER [IF NOT EXISTS] <name> [TYPE 'anthropic'|'openai'|'openai_compatible'|'ollama'|'embedding'|'rerank']
  WITH (endpoint = 'https://…', model = '…', api_key = 'env:NAME'|'file:PATH'|'cmd:PATH',
        timeout_secs = 120, max_output_tokens = 8000, fallbacks = true,
        tls_verify = 'system'|'insecure',
        reasoning_effort = 'none'|'low'|'medium'|'high'|…,
        dim = 768, batch_size = 32, active = true)   -- ADMIN; the key is a REFERENCE, never a
                                              -- literal; dim/batch_size/active: embedding and
                                              -- rerank types (the active one replaces [inference])
ALTER MODEL PROVIDER <name> SET (…)           -- ADMIN
DROP MODEL PROVIDER [IF EXISTS] <name>        -- ADMIN
SHOW MODEL PROVIDERS                          -- MONITOR; the key's reference, never its value

A provider of the embedding type serves managed EMBED vector indexes (CREATE VECTOR INDEX) and one of the rerank type serves RERANK.

See also Advisor · ADVISOR.md

BACKUP

BACKUP TO '<path>'        -- crash-consistent copy of this node's data dir
BACKUP CLUSTER TO '<path>'  -- every member backs up + one shared cut instant;
                            -- '{node}' in the path = each member's own id
                            -- (192_168_7_174_7100) for a shared mount

BACKUP TO '<path>' copies the whole data directory (tables, WALs, catalog, search and time-series stores) under the exclusive lock — the copy only: the markers that describe it (the WAL cut and the completion marker, below) are written after the lock is released, and the result's locked_ms is the time the lock was held, elapsed_ms the whole statement — crash-consistent by construction (opening the copy replays WALs like a crash recovery); vector indexes load their persisted snapshot on open and replay only rows newer than its watermark (full rebuild if absent).

A target already holding a skaidb backup is updated incrementally: unchanged files are reused, changed ones re-copied, removed ones deleted — always a faithful full snapshot, costing only the delta (the result reports files/bytes copied, reused_files/ reused_bytes, deleted). A target that exists but is not a backup is refused.

The process lock (.lock) and the disk guard's reserve file (.skaidb-reserve) are live-node state and are never copied.

Completion markers

A backup writes BACKUP_IN_PROGRESS into the target before its first copy and BACKUP_COMPLETE after its last: the file list with sizes, a checksum of catalog.json, and the completion time. The completion marker is the LAST thing written (atomically), so a backup interrupted at any point — the node killed, the disk full, the statement cancelled — leaves BACKUP_IN_PROGRESS and no valid BACKUP_COMPLETE; that is what an incomplete backup looks like, and ls <path> shows it. RESTORE refuses such a target (backup at '<path>' is incomplete: the backup that wrote it was interrupted …), refuses one whose files disagree with its marker (backup at '<path>' is damaged: <file> …).

The next BACKUP into an interrupted target takes it over: files that match their source (size and mtime — a half-copied file never does) are kept, everything else is copied again, and the run ends with a valid BACKUP_COMPLETE, so a scheduled backup recovers by itself from a run that was killed (the server log says backup target '<path>' holds an interrupted backup … taking it over). Only a target that another BACKUP of the same node is still writing is refused (another backup into this target is still running).

A target with a valid BACKUP_COMPLETE beside a leftover BACKUP_IN_PROGRESS is complete (the marker is authoritative). A backup with NEITHER marker was written by a skaidb without them: it restores, and the result carries a warning column saying its completeness was not verified — the only way to restore an unverified backup; there is no override that accepts an interrupted one. The next BACKUP into such a target adds the markers. Extra files beside the listed ones (CLUSTER_CUT) never fail verification.

BACKUP CLUSTER

On a cluster each node backs up its own shard — and BACKUP CLUSTER TO '<path>' does that on EVERY member in one statement, then mints a single cut instant strictly AFTER all backups completed and records it beside each one (CLUSTER_CUT, and the cut_ms result column). Restoring every node with RESTORE FROM '<path>' TO TIMESTAMP <cut_ms> lands the whole ring on that one instant: each node's archive replay extends its backup FORWARD to the cut (replay is exact at its end — it can reach forward, never rewind), which is why the cut postdates the backups. Requires storage.wal_archive_dir for the restore to be exact.

See also RESTORE · CLUSTER_OPERATIONS.md

RESTORE

RESTORE FROM '<path>'     -- embedded / single node only; old data kept aside
RESTORE FROM '<path>' TO TIMESTAMP '<when>'   -- point-in-time recovery

RESTORE FROM '<path>' swaps the backup in and reopens, moving the previous data aside to <dir>.pre-restore[-<n>] (never deleted; the restored directory keeps the mode of the one it replaces); it refuses to run on a cluster — stop the node and restore offline, then let repair converge it.

Point-in-time recovery

RESTORE FROM '<path>' TO TIMESTAMP '<when>' is point-in-time recovery: the backup is restored, then archived WAL segments are replayed on top of it up to <when> — so a bad DELETE at 14:07 is undone without losing the work done since the last backup. <when> is ISO-8601 ('2026-08-19T14:06:00Z', '2026-08-19 14:06') or epoch milliseconds, and the window INCLUDES everything stamped in that millisecond. Requires storage.wal_archive_dir; the result adds replayed_to, segments, records and segments_skipped columns, and tables_skipped when a table could not be replayed: a table whose name nests below another path (u/a) shares its WAL archive with other names, so it is left at backup time and listed there.

If the restore fails after the swap (the restored data does not open, or the archive replay fails), the previous data is put back: the failed directory is renamed to <dir>.failed-restore-<n>, the set-aside copy renamed into place, then the failed copy deleted — a crash mid-way leaves the previous data whole, either at <dir> or still at <dir>.pre-restore[-<n>]. A leftover <dir>.failed-restore-<n> is a failed restore's data: delete it. If the error says putting the data back failed too, it names where the previous data is, and the node refuses every statement until it is restarted: stop it, move the previous data back to <dir> and restart. A restore cut short by a crash leaves a RESTORE_IN_PROGRESS file in <dir>, and a server refuses to start there (or on a missing <dir> with a .pre-restore* / .failed-restore-* directory beside it) until the right directory is moved back.

The replay costs what the window costs, not what the archive holds: BACKUP TO records where the backup sits in the node's archive (a WAL_CUT file in the backup), and the restore skips every archived segment up to there — the backup holds those records already (segments_skipped). A backup without that record (taken by an older skaidb, or streamed) or one whose record does not match the archive it is restored against is replayed from the archive's first segment; the result is the same either way. Records apply in batches with one WAL sync per segment, and the replayed records are not archived a second time.

Refused, rather than silently approximated, when: no archive is configured, the instant is unparseable, or the window crosses a schema change — the archive holds row mutations only, so DDL inside the window cannot be replayed and rows belonging to it would be dropped. Restore to a point before the change, or use a later backup.

Replay is idempotent (every record keeps its original stamp and the engine is last-writer-wins: a replayed record older than the version the table holds changes nothing, index entries included), so running the same restore twice gives the same state. Replayed rows go through the same apply path a replicated write does, so secondary, full-text, vector and geo indexes are maintained — a recovered row is found by MATCH, NEAREST and an indexed lookup, not only by a scan. Change streams observe replayed rows as they would any other write, so a consumer may re-see changes it already processed. Time-series tables replay too, cut on the sample's ARRIVAL time rather than its own timestamp — a backfill written at 14:10 carrying ts=13:00 does not appear in a restore to 14:06.

See also BACKUP · Point-in-time reads · CLUSTER_OPERATIONS.md

Expressions

Nesting limit and chain width

Nesting limit: a statement nests at most 128 levels — parentheses, function arguments, array/object literals, subqueries and CTE bodies, NOT/- chains, procedure blocks (the statement, its SELECT and a subquery's SELECT count as levels too). Deeper statements fail to parse with expression nesting exceeds 128 levels.

Chain width: a flat AND/OR chain of any width parses (it is folded into a balanced tree; operands keep their order and short-circuit as usual), as does a long IN list or VALUES list. A flat + - * / chain keeps left-to-right grouping and is limited to 512 terms; longer ones fail to parse with expression chain of <op> exceeds 512 terms — group with parentheses ((…) + (…)) to go wider.

Literals

Integer (42), float (3.14, scientific 1.2e-5/3E8), string ('ada'), TRUE, FALSE, NULL, array literals [<lit> [, <lit> ...]] (constant elements only — a literal or a negated number, e.g. [0.1, -0.2, 0.3]), and object/document literals {<key>: <lit> [, ...]} — the way to write a nested document in SET/VALUES. Keys are bare identifiers or string literals (quote reserved words: {'from': 1}); values must be constant and may nest arrays and objects: {name: 'ada', tags: ['x'], addr: {city: 'paris'}}.

A duplicate key keeps the last value; {} is the empty document. Assigning an object literal to a path (SET meta.addr = {…}) replaces that whole sub-document (sibling fields outside the path survive); dotted-path SET (SET meta.addr.city = 'x') remains the idiom for updating a single scalar leaf in place.

Integer literals span the whole int64 range, -9223372036854775808 … 9223372036854775807; a larger literal is a parse error (write a float: 9.3e18).

Duration literals: an integer immediately followed by a unit — 250ms, 15s, 5m, 2h, 30d, 1w — is a duration, valued as integer milliseconds (5m = 300000). Usable anywhere an integer is.

See also Identifiers & literals · Types · Numbers

Column and field paths

name, or a dotted path into a nested document: address.city, a.b.c. Usable in projections, WHERE, GROUP BY, ORDER BY, and UPDATE … SET targets.

See also Table qualifiers

Operators

By increasing precedence:

  1. OR
  2. AND
  3. NOT <expr>
  4. comparison: =, != (or <>), <, <=, >, >=, and && (range overlap: both sides two-element [lo, hi) arrays, a NULL bound = unbounded; NULL for anything else)
  5. <expr> IS [NOT] NULL
  6. postfix predicates: <expr> [NOT] IN (<expr> [, ...]), <expr> [NOT] BETWEEN <expr> AND <expr>, <expr> [NOT] { LIKE | ILIKE } <pattern-expr>
  7. additive: +, -
  8. multiplicative: *, /, % (remainder; a % b is mod(a, b))
  9. unary: -<expr>, NOT <expr>
  10. parentheses ( … )

in, between, like, and ilike are contextual keywords — still usable as column names outside the operator position.

% / mod(a, b) — the remainder of the truncating division, with the sign of the dividend (7 % 3 = 1, -7 % 3 = -1), an integer for two integers and a float otherwise; a zero divisor yields NULL, like /.

See also CASE · EXCLUDE constraints · Numbers

Array membership

Array membership: comparing an array-valued column to a non-array scalar tests containment: labels = 'work' matches rows whose array holds 'work'; != is not-contains. Array-to-array comparison remains whole-value equality.

See also IN · Multikey components

CASE

The conditional expression, in both standard forms:

CASE WHEN <condition> THEN <result> [WHEN <condition> THEN <result> ...] [ELSE <result>] END
CASE <operand> WHEN <value> THEN <result> [WHEN <value> THEN <result> ...] [ELSE <result>] END

The searched form returns the <result> of the first <condition> that is true; the simple form compares <operand> = <value> arm by arm (so a NULL operand matches no arm). With no arm taken the value is the ELSE result, or NULL without one. Arms are tried in order and only the taken result is evaluated; a condition that is NULL or not a boolean is simply not taken. Usable wherever an expression is — select items, WHERE, GROUP BY / ORDER BY, inside aggregates (sum(CASE WHEN ok THEN 1 ELSE 0 END)), UPDATE … SET, CHECK, generated columns and expression indexes; an unaliased CASE column is labelled case. At most 512 WHEN arms. CASE is contextual: case remains a valid column name, and reads as the keyword only where a WHEN follows it in the same expression (write (case) for the column directly before a WHEN). if(<condition>, <then> [, <else>]) is the two-way shorthand.

See also Scalar functions

IN and NOT IN

Set membership: x IN (a, b, c) is true when x equals any listed element (three-valued: unknown if x is NULL, or if no element matches but some element is NULL); NOT IN negates it. The list needs at least one element (IN () is a parse error). A list element that is an array is flattened — each of its elements becomes a candidate — so a bound array parameter works directly: WHERE id IN (?) with ? = [1, 2, 3] tests membership in that set (the "fetch these N ids" pattern). When the left side is an array column, IN matches if the array holds any listed value, mirroring the = containment rule (Array membership).

Performance: when every primary-key column is pinned by = or a literal IN list (bound array parameters included), the query resolves as a point-read set — one bloom-gated point read per candidate key (cross product on composite keys, any size up to 1,000,000 keys; the keys are not counted against scan_row_budget, while the byte budget and the statement deadline still bound the result; only the predicates the key set does not cover — other columns, NOT IN, a second pin on a key column — are re-checked per row), routed to each key's replica set on a cluster in batches of 128 keys per round trip; EXPLAIN shows point-read set (and the batching on a cluster). A result streamed in keyset pages (ORDER BY <pk> LIMIT n AFTER (…), which is how an unbounded row-returning statement is streamed) reads each page as the next n listed keys past the cursor, by the same point reads — the rows between two listed keys are never walked. A key that finds no row is read again under its =-twins — the same number in the other numeric types (2, 2.0, the decimal 2, epoch-ms 2 as a timestamp) and the timestamp an ISO-8601 string names — so WHERE id = 2 finds a row stored as 2.0, and an epoch-ms integer finds a timestamp key, as a scan does; UPDATE and DELETE reach that row too. The twins are read only on a miss, and only in the key types the table holds (a table keyed by integers alone reads none) — on a cluster that check uses the coordinator's own copy, so it applies at ONE; above ONE every twin is read on a miss, since a lagging member cannot rule out a key type other replicas already hold. 2 and 2.0 are distinct keys, and a table holding both answers the spelling the statement used. A spelling is a twin only when = holds exactly — an integer beyond 2^53 has no float twin (its nearest double is another number), and 0 reaches a key stored as -0.0. Past the cap the statement plans a scan, and EXPLAIN says so. With a search predicate (MATCH() family) in the same WHERE, the pinned keys instead restrict the search itself (up to 4096 keys): the search index returns only the listed rows' documents, scored exactly as without the list — including keys stored as a type the row-side = coerces to (id = 1.0 finds Int id 1; an ISO string finds a Timestamp key; a Timestamp literal or |number| ≥ 2^53 leaves the search unrestricted); EXPLAIN shows search_key_set. Other IN shapes (non-PK columns, NOT IN, non-literal elements) evaluate as a residual row filter over the scanned range and can trip scan budget exceeded on large unindexed scans — pair those with a narrowing indexed predicate.

See also Subqueries · Bind parameters · Streamed queries

BETWEEN

x BETWEEN lo AND hi is the inclusive range x >= lo AND x <= hi (three-valued: a NULL operand or bound makes the undecided side unknown); NOT BETWEEN negates it. A col BETWEEN lo AND hi with literal bounds contributes both bounds to index/PK range pushdown, exactly like writing the two comparisons.

Performance: a range on the leftmost primary-key column is a bounded key slice, not a scan — the table is stored in primary-key order, so WHERE id >= ? AND id < ? seeks. A time-sortable PK (ULID/snowflake) therefore serves time ranges, including range aggregations (GROUP BY, count). An equality-pinned PK prefix plus a range on the next column slices the same way. EXPLAIN shows primary-key range slice. The one shape that does NOT take the slice: an ORDER BY a secondary index can walk in order, under a LIMIT — the index-ordered walk stops after LIMIT matches, where the slice would read the whole prefix for the executor to sort (WHERE account = ? ORDER BY internal_date DESC LIMIT 1 with an index on (account, internal_date) reads one row, not the mailbox).

LIKE and ILIKE

SQL pattern match on strings: % matches any run of characters (including none), _ matches exactly one; every other character matches itself, and there is no escape sequence (a literal %/_ in the data cannot be targeted — use equality or a search index). ILIKE is case-insensitive (Unicode-aware lowercasing). The pattern is any expression (usually a literal or a ? parameter). Non-string operands — including NULL — compare as unknown, so a mixed-type column never errors the query. This is exact substring/prefix matching, complementing the analyzed full-text MATCH() (which tokenizes words): use LIKE '%needle%' for verbatim fragments, MATCH for word search.

Performance note: LIKE/ILIKE evaluate as a residual row filter (no index acceleration, including 'prefix%') — the same scan-budget caveat as IN applies on large unindexed scans.

See also Full-text search

Subqueries

<expr> [NOT] IN (SELECT …) (membership in the subquery's first column), [NOT] EXISTS (SELECT …), a scalar (SELECT …) (one column, at most one row — NULL when none; more rows or columns is an error), and the quantified comparison <expr> <op> ANY (SELECT …) / SOME (…) / ALL (…) with op one of =, != / <>, <, <=, >, >= — anywhere an expression goes: a SELECT's items / WHERE / HAVING / ORDER BY / GROUP BY / join ON, an UPDATE's SET and WHERE, a DELETE's WHERE, an INSERT's VALUES; they nest, and may name a view.

Each runs before the statement, at the session consistency and under the statement's scan row/byte budgets and timeout, and is folded into it — IN becomes an IN (…) list (NULLs dropped, so NOT IN behaves like a plain list rather than Postgres' three-valued trap), EXISTS a boolean (the inner runs with LIMIT 1), a scalar its value, ANY / ALL a comparison over the column's values — so the statement that then runs is an ordinary one: single-table WHERE pushdown, index scans, keyset paging and every cluster path see a plain predicate.

An IN list is checked per candidate row by a linear scan, as any IN (…); a large membership set is what a JOIN is for. Not allowed in a CHECK / DEFAULT / generated expression. RBAC: SELECT on every table a statement reads — its FROM, join sides, set-operation legs, subqueries (correlated or not) and CTE bodies — is checked on the user's own grants.

ANY and ALL

SQL three-valued logic over the subquery's first column, NULLs included. x op ANY (…) is true when some comparison is true, false when every comparison is false (and over no rows), otherwise NULL; x op ALL (…) is false when some comparison is false, true when every comparison is true (and over no rows), otherwise NULL. So x <> ALL (SELECT c …) is never true when c holds a NULL, and a NULL x is NULL against any non-empty set. = ANY is IN except for that NULL handling. With a parenthesised non-subquery operand the quantifier ranges over that ARRAY value: 'b' = ANY (tags), x > ALL ([1, 2]) (a NULL operand is NULL).

Correlated subqueries

A subquery may read the enclosing row through the outer query's alias or bare table name — SELECT name FROM cust c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id), SELECT name, (SELECT max(total) FROM orders o WHERE o.cust_id = c.id) FROM cust c, UPDATE cust SET spent = (SELECT sum(total) FROM orders o WHERE o.cust_id = cust.id). A correlated reference is a column qualified by an alias of the enclosing query that the subquery does not declare itself (an alias inside the subquery shadows it; an explicit alias hides its table's name, so FROM orders o2 leaves an outer orders visible). An UNqualified name is always the subquery's own — the store is schema-less, so an inner name can never be proven to belong to the outer row. Inside a subquery, its own single-table alias qualifies its columns as expected (o.total). An INSERT has no outer row. Evaluation, in two shapes:

  • Equality shape — every correlated reference sits in a top-level AND conjunct <inner expr> = <outer column> of the subquery's WHERE, and the subquery is plain (one item, no GROUP BY / DISTINCT / ORDER BY / LIMIT / set operation). It is decorrelated into ONE inner pass whatever the outer size: EXISTS reads the distinct keys, an aggregate item runs grouped by the keys (count(*) is 0 for a key with no rows), a plain item is collected per key. NULL keys never match, and numbers that compare equal match across integer / float / decimal.
  • General shape (anything else — a non-equality correlation, an ORDER BY … LIMIT inside, correlation in the item): the inner query runs once per DISTINCT combination of the outer values it reads (the outer rows gathered by the outer FROM scope and its uncorrelated WHERE conjuncts), with the outer values substituted, cached per statement; at most 10,000 combinations per statement, beyond which the statement fails and suggests the equality form or a JOIN.
  • Primary-key probes — the equality shape where the correlation equalities (plus, for x [NOT] IN (SELECT t.k …), the item against the tested value) pin the inner table's WHOLE primary key, the inner table being a stored one (not a view, CTE or time-series table): NOT EXISTS (SELECT 1 FROM message_tags t WHERE t.account = m.account AND t.id = m.id [AND <t-only predicates>]). Each outer row asks about at most one inner row, so the inner table is read as batches of primary-key IN reads of the keys the outer rows present — never a pass over it. A scalar subquery with a plain item ((SELECT t.tags FROM t WHERE <whole key>)) probes the same way.

When the outer query is one table ordered and cut by LIMIT (no grouping, aggregates, DISTINCT, windows or search) and the subquery is its only correlated top-level WHERE conjunct, the outer query's own walk drives the probes until OFFSET + LIMIT rows pass. Outer filters such as (m.labels != 'SPAM' OR m.labels IS NULL) remain candidate filters just like their unqualified forms: they run before the walk chooses its passing rows. Qualification in that same query scope is not a correlation; a nested SELECT may shadow an outer alias. Candidate filters use the normal table/document qualifier rules, so a stored document path named like the alias stays a document path. An ORDER BY key naming a qualified column such as o.d takes the every-key run below instead.

The final statement point-reads exactly the passing rows (at every consistency: a statement whose WHERE pins every primary-key column to literals — up to 1,000,000 keys — is point reads whatever its ORDER BY; past 1,000,000 passing rows, or when a passing row's order value is NULL — which an index walk and the sort of point-read rows place at opposite ends — the final statement walks its own plan under the probe answers, bounded to the order values the probe walk reached (NULL-ordered rows kept when one passed), so it re-reads the walked range but never beyond it) — NOT EXISTS (…) ORDER BY internal_date DESC LIMIT 10 reads a prefix of the mailbox's index walk whatever the tag table holds; the sparser the passing rows, the longer that prefix.

When the outer table has a primary key and the first ORDER BY key is a plain column, the walk is a keyset walk: pages of ORDER BY … LIMIT n rows reading only the correlation keys, the outer primary key and that column (n from 4 × (OFFSET + LIMIT), at least 64, doubling to 2,048), each page continuing from the previous page's last value (col <= v descending, col >= v ascending — a tie run crossing a page edge is re-read and its seen rows skipped; a page that finds nothing new grows fourfold, up to 10,000, and the next page after the bound moves on is back to at most 2,048); every page is one bounded ordered read, so the walk's depth costs pages, never a larger gather, and the byte budget counts what the walk keeps — the passing rows' keys and probe answers, the primary keys seen at the current bound's value, and a 16-byte hash of every walked row's primary key (a row whose order value moves under the walk is met again: it passes, and counts as walked, once) — not the rows it walked past: the cost of a deep walk (a sparse NOT EXISTS, say 1 passing row in 40) grows linearly with its depth, about two rows examined per walked row, and its memory with OFFSET + LIMIT.

At QUORUM each page is one ordered read that a full-copy table answers from the coordinator's window once a read quorum agrees on it (the agreed window, see CLUSTER_CONSISTENCY.md), so the walk examines what it examines at ONE; above ONE the next page is read while this page's keys are probed, unless this page's pass rate already finishes the walk, so a page costs about what the slower of the two costs (a page read ahead that the walk then does not need still counts toward the statement's rows examined).

A walk that finds fewer than OFFSET + LIMIT passing rows runs dry: it has then read every candidate row's keys and probed each one (about two rows examined per outer row), so on a large outer match set it can reach the row budget or the statement timeout. A walk that runs dry under a page bound reads one candidate row at the far end of the order (LIMIT 1, at the statement's consistency — so rows only a lagging coordinator's peers hold count; walking descending, NULL order values first): an order value of another type there probes every candidate key, a NULL one reads exactly the rows the bound cannot compare and probes them — the walk's answer stands when none of them passes. Order values of more than one type (an Int and a Float, a number and a string), a passing row with a NULL order value under a bound, or a walk that reaches 10,000-row pages without progress (a run of NULL order values longer than that) probe every candidate key once and the final statement's own plan decides where those rows sort.

Without a primary key or with another first ORDER BY key the pages restart from the top, n growing fourfold up to 10,000, and past that the rest of the outer rows are read in one ordered pass, only their not-yet-probed keys probed: the cost is then the outer match set, not a prefix.

Otherwise (a count, the subquery in the select list or under OR) one pass collects the outer rows' distinct keys and every one is probed — unless the inner table's fast row count is no bigger than that key set (every probed key counts as a row examined, found or not), in which case the one-pass equality shape reads it instead.

Keys match the way = does, not by encoding: each key is point-read in its own encoding first, and only a key that found nothing is read again in every encoding = equates with it (an integer, integral float or decimal and an epoch-ms timestamp; the timestamp an ISO-8601 string names), so a hit costs one read and a miss on a numeric key up to five; the one gap is an outer timestamp against a stored ISO-8601 string key, which is not found. NULL correlation values match nothing; NULL [NOT] IN a non-empty set is NULL.

EXPLAIN names the strategy (subquery: anti-join via primary-key probes of message_tags (account, id) …, semi-join, anti-join (NOT IN), scalar lookup) and which run applies: the keyset walk (keyset pages of 64 to 2048 rows, each page cut to the rows the walk still expects to need once some pass), the restarting walk (with its 10,000-row page cap) or every key probed (naming the one-pass alternative the row counts decide at run time).

A fallback names the blocking condition: no LIMIT, a non-plain outer query, multiple correlated conjuncts, placement under OR or in the select list, scalar/quantified shape, or an unsupported projection/order. Correlations without a supported whole-primary-key probe shape report the equality-pass or per-tuple strategy instead. These decisions use the execution recognizers.

A LEFT JOIN t ON <t's whole primary key> = <FROM-table columns> [AND <t-only predicates>] WHERE … AND t.<key column> IS NULL, with t read nowhere else in the statement, is the anti-join: it is rewritten to NOT EXISTS (SELECT 1 FROM t WHERE <the ON>) over the FROM table alone (its m. qualifiers dropped, output names kept) and resolved by primary-key probes as above. Either way the result travels inside the statement as a per-key lookup the outer predicate evaluates per row, so the outer query keeps its ordinary plan and every cluster path. Every inner read runs at the outer statement's consistency and shares its scan budgets and timeout.

See also JOIN · WITH (common table expressions) · EXPLAIN

Aggregate functions

COUNT(*) / COUNT(<expr>) / COUNT(DISTINCT <expr>) (exact distinct non-null values), APPROX_COUNT_DISTINCT(<expr>) (opt-in approximate distinct: the search-index pushdown may answer from an HLL sketch — never truncates, ~±1–2% at high cardinality; every other path answers exactly), SUM, AVG, MIN, MAX, PERCENTILE(<expr>, <p>) (the linear-interpolated percentile_cont quantile of the group's numeric values; <p> is a literal fraction in (0, 1], e.g. PERCENTILE(latency_ms, 0.95) — computed exactly over the gathered rows), APPROX_PERCENTILE(<expr>, <p>) (the same quantile, but the caller opts into approximation: over a search predicate it pushes into the index as a t-digest sketch — global, not grouped — and merges across shards; every other path computes it exactly), and the time-series aggregates RATE, INCREASE, DELTA, FIRST, LAST (see Querying time series). Using an aggregate (or GROUP BY) puts the query in aggregate mode.

See also GROUP BY, HAVING and TOP · Window functions

Window functions

ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(<expr> [, <offset> [, <default>]]), and LEAD(<expr> [, <offset> [, <default>]]), each followed by an OVER ( [PARTITION BY <expr> [, ...]] [ORDER BY <key> [ASC|DESC] [, ...]] ) clause. Rows split into partitions by the PARTITION BY values (one partition when absent), order within each partition by the window's ORDER BY (ties — and the whole order when absent — break by input position), and the function assigns per row along that order: ROW_NUMBER counts 1, 2, 3, …; RANK gives equal order-keys equal rank with gaps after ties; DENSE_RANK the same without gaps; LAG/LEAD evaluate <expr> against the row <offset> positions earlier/later in the partition (<offset> defaults to 1, must be a non-negative integer) and yield <default> (else NULL) when that row falls outside the partition. Window functions are valid only in the SELECT list (alias them and ORDER BY the alias to sort by a window value); using one in WHERE/GROUP BY/HAVING is an error, and they cannot combine with GROUP BY/aggregate mode. Values are computed over the full filtered row set before the outer ORDER BY/LIMIT applies — a LIMIT never truncates what a window sees: SELECT id, rank() OVER (PARTITION BY region ORDER BY amount DESC) AS rk FROM sales ORDER BY rk LIMIT 10.

See also Aggregate functions · DISTINCT and DISTINCT ON

Scalar functions

distance('<n><unit>') (a distance literal in metres — '500m', '5km', '1mi', '3NM'; the natural radius for geo_distance comparisons, and constant, so the geo index still prunes: WHERE geo_distance(loc, 40.7, -74.0) <= distance('5km')), now() (the query's start time, as a timestamp — one instant per statement), nextval('<seq>') / setval('<seq>', <n> [, <is_called>]) (sequence counters — see CREATE SEQUENCE for where they may appear), coalesce(<a>, <b>, ...) (the first non-NULL argument, NULL if all are), the string functions lower(s), upper(s), trim(s), length(s) (characters; elements for an array), substr(s, start [, len]) (1-based), concat(a, b, ...) (NULLs skipped, numbers rendered), starts_with(s, p) / ends_with(s, p), the geo helpers geo_lat(p) / geo_lon(p) (a point's coordinates) and geohash(p [, precision]) (the base-32 geohash cell, 1–12 characters, default 5 — a grid key to GROUP BY), and the number functions abs(n), floor(x), ceil(x), round(x [, digits]) (one argument rounds to a whole number and yields an INT; two keep the fraction and yield a FLOAT — round(avg(v), 2) — with negative digits rounding to tens and hundreds; a non-string / non-number argument yields NULL) — all deterministic, so usable in CHECK, generated columns and expression indexes,

if(<condition>, <then> [, <else>]) (conditional value; <else> defaults to NULL, and a NULL/non-boolean condition takes the else branch rather than erroring) and nullif(<a>, <b>) (NULL when the two are equal, else <a>) — the pair that makes conditional aggregates a single pass, since COUNT ignores NULLs: SELECT count(if(status >= 400, 1, NULL)), count(*) FROM reqs gives an error count and a total in ONE scan instead of two, time_bucket(<step>, <ts>) (floors <ts> to a <step>-wide bucket: time_bucket(5m, ts)), and to_timestamp(<value>) — coerce to a timestamp: numeric epoch-ms passes through and an ISO-8601 string is parsed (YYYY-MM-DD, YYYY-MM-DD[T ]HH:MM[:SS[.fff]], optional Z/±HH[:MM] offset; no offset = UTC). Unparseable or mistyped input yields NULL, never an error — so string timestamps (e.g. from a document-store migration) range-filter in-query: WHERE to_timestamp(created_at) >= now() - 30d. Comparing a TIMESTAMP column directly with a string literal coerces the string the same way (WHERE ts >= '2026-08-10' behaves exactly like a typed timestamp bound, including time-series scan pruning); an unparseable string compares as NULL.

The standard spelling CAST(<expr> AS <type>) (types: INT, FLOAT, STRING, BOOL, TIMESTAMP) desugars to the matching coercion (to_int, to_float, to_string — timestamps render as ISO-8601 —, to_bool, to_timestamp), all with the same NULL-on-unconvertible policy. cast stays usable as a column name.

A bare identifier directly followed by ( parses as a function call; unknown functions are execution errors. The full-text search functions MATCH, MATCH_PHRASE, FUZZY, SEARCH, and score parse the same way and are only valid in a search query (see Full-text search).

See also Array functions · Geospatial functions · CASE

Array functions

Array functions operate on list-valued fields. Mutators — array_append(a, x), array_append_distinct(a, x) (no-op when an equal element exists; never reorders), array_remove(a, x) (drops all equal elements), array_set(a, i, x) (positional write; out-of-bounds is an error) — are evaluated server-side inside UPDATE/upserts, so the whole element mutation inherits per-row linearizability on a cluster: UPDATE t SET tags = array_append(tags, ?) WHERE id = ? is one atomic operation, and a NULL/absent base acts as the empty array (array_append(NULL, x) = [x]). Element equality is the SQL = comparison, so mixed numeric encodings match numerically. Readers: array_length(a) (NULL for non-arrays) and array_contains(a, x) (false for non-arrays), both usable in WHERE.

See also UPDATE · UNNEST · Array membership

Geospatial functions

geo_distance(<point>, <lat>, <lon>) returns the great-circle (haversine) distance in metres from a point column to (<lat>, <lon>); geo_bbox(<point>, <min_lat>, <min_lon>, <max_lat>, <max_lon>) tests whether a point lies inside a bounding box (min_lon > max_lon crosses the antimeridian); geo_polygon(<point>, <polygon>) tests whether it lies inside a polygon — [[lat, lon], …] (three or more vertices, the closing vertex optional) or the string 'lat,lon; lat,lon; …', even-odd rule, edges count as inside. A point is a {lat, lon} object (lng also accepted) or a [lat, lon] array; a non-point / NULL value (or an unparsable polygon) yields NULL (so it drops from a filter) rather than erroring. Use them anywhere: WHERE geo_distance(loc, 40.71, -74.0) <= 5000, ORDER BY geo_distance(loc, 40.71, -74.0) LIMIT 10 (nearest-first), WHERE geo_bbox(loc, 40.4, -74.3, 40.9, -73.7), or WHERE geo_polygon(loc, [[40.8, -74.0], [40.75, -73.9], [40.7, -74.0]]). A CREATE GEO INDEX makes these predicates prune to a neighborhood instead of scanning the whole table (a polygon prunes by its bounding box, then the exact test runs on the candidates); without one they still work over a (scan-budget-bounded) scan.

See also CREATE GEO INDEX · GEO.md

Bind parameters

? marks a positional parameter in any expression position of a prepared SELECT/INSERT/UPDATE/DELETE (e.g. INSERT INTO t (id, v) VALUES (?, ?)), and additionally in LIMIT ? / OFFSET ? (the bound value must be a non-negative integer). Parameters are numbered by order of appearance and bound to values at execute time via the binary protocol's prepared-statement messages (or a driver's prepare/execute API). EXPLAIN of a preparable statement is itself preparable, so the exact bound query can be explained. DDL and session-control statements cannot be prepared, and a ? submitted through the plain one-shot query path is an error at execution.

See also EXPLAIN · PROTOCOL.md

Three-valued logic

Comparisons follow three-valued logic (NULL compares as unknown).

See also IN · ANY and ALL

Identifiers & literals (lexical)

  • Bare identifiers start with a letter or _ and continue with letters, digits, or _. Keywords are case-insensitive.
  • Quoted identifiers use double quotes: "select", "weird name". A quoted identifier is ONE name whatever it contains: "a.b" names the object a.b of the current database and db."a.b" that of db; only an unquoted . separates a database qualifier. A quoted identifier may not contain NUL, U+0001 (names starting with it are the engine's own hidden fields) or U+001F (the internal name separator) — on every entry point, SQL, REST, the BI editor and the Elasticsearch gateway alike — and statement text may not contain U+001F at all.
  • Object names (tables, indexes of every kind, views, materialized views, rollups, streams, triggers, sequences, databases, constraint names, and ALTER TABLE … RENAME TO targets) become directory names on disk, so a NEW object's name may not contain /, \, %, . or a NUL character, and its encoded qualified form may not exceed 240 bytes — CREATE TABLE "a/b" … fails with invalid name. Objects created under such names by older versions keep working (their directories are stored under an escaped name, and a cluster peer replaying their DDL is not refused).
  • String literals use single quotes; embed a quote by doubling it: 'O''Brien'.
  • ; may terminate a statement. Only one statement is executed per call.

Types

Values are dynamically typed: null, bool, int64, float64, decimal, string, bytes, uuid, timestamp (Unix time, milliseconds), array, document. Only int, float, string, bool, null, array, and document (object) literals can be written directly in SQL; decimal/uuid/bytes/timestamp values arrive via stored data or the value codec (e.g. bound parameters), not as SQL literals.

Numbers

int64, float64, decimal and timestamp values compare with each other by value (1 = 1.0). Integers and timestamps compare exactly over the whole 64-bit range, with each other and against a float (the mathematical order of the two numbers; the integer is never rounded to a double) — in comparisons, IN, BETWEEN, ORDER BY, join keys and min/max, with or without an index. 64-bit identifiers beyond 2^53 (snowflake ids, nanosecond timestamps) are therefore distinct values: ref = 1234567890123456789 does not match 1234567890123456790. A decimal compares exactly with integers, timestamps and other decimals (1.0 = 1.00, while 1 = 1.000000000000000001 is false), and with a float through the float's precision — the decimal's nearest float64, as PostgreSQL compares numeric with double precision — so the float literal 0.1 matches the decimal 0.10 a driver stored.

GROUP BY, DISTINCT, count(DISTINCT …), the duplicate removal of UNION, primary keys and index order identify a value by its storage key instead: by type first — 1, 1.0 and the decimal 1 are three groups, three keys — and a decimal by its float64 value, so decimals that differ only beyond double precision are one group and one key.

Arithmetic on two integers stays int64 and is checked: a result outside the int64 range fails the statement — type error: integer out of range: 9223372036854775807 + 1 does not fit int64 — for +, -, *, unary -, abs() and -9223372036854775808 / -1. Nothing wraps around. Integer / truncates toward zero (7 / 2 is 3, -7 / 2 is -3). Division by zero yields NULL, for integers and floats alike (it is not an error). An operation with a float, decimal or timestamp operand is computed in float64.

SUM over integers is exact and never wraps: the result is an int64 while the total fits and the same total as a decimal (scale 0) beyond that; a sum that includes any non-integer value is a float64.

Indexing notes (query-relevant)

  • A WHERE predicate is index-accelerated when it constrains an indexed column by equality or range (=, <, <=, >, >=, and AND-combined ranges / BETWEEN-style), or when ORDER BY follows an index. Composite indexes use a leftmost prefix of equalities plus an optional trailing range, and serve ORDER BY along that prefix. Everything else falls back to a scan; results are identical either way.
  • The primary key is the routing key in a cluster: WHERE pk = <v> is a point read to the key's replica set; other indexed predicates are pushed to each node's local index.

Not part of the SQL surface

  • No data-modifying CTEs (WITH x AS (INSERT …)), LATERAL subqueries (LATERAL UNNEST(…) is supported — see UNNEST), comma joins between tables (FROM a, b — a comma introduces only an UNNEST), or row-valued comparisons ((a, b) IN (SELECT …), (a, b) = ANY (…)). Correlated subqueries, ANY / SOME / ALL, WITH RECURSIVE and a WITH clause before INSERT / UPDATE / DELETE are supported — see Subqueries and Recursive CTEs. DISTINCT ON, FULL [OUTER] JOIN and INTERSECT / EXCEPT [ALL] are supported (set operations apply left to right). Window functions are supported in the SELECT list only (see Window functions), never in WHERE or HAVING.
  • No writes through a view whose body joins, groups, aggregates, uses DISTINCT / set operations / LIMIT, or through a materialized view; no ALTER MATERIALIZED VIEW (drop and create it again); no INSTEAD OF triggers or rules.
  • No ALTER beyond RENAME TABLE / RENAME COLUMN / constraint ADD/VALIDATE/DROP CONSTRAINT / ALTER COLUMN … SET|DROP NOT NULL | DEFAULT / ALTER COLUMN … ADD GENERATED … AS IDENTITY | DROP IDENTITY / ALTER COLUMN … ADD GENERATED ALWAYS AS (…) STORED | SET EXPRESSION | DROP EXPRESSION / ALTER SEQUENCE (the store is schema-less, so there is nothing to ADD/DROP COLUMN and no column types to alter).
  • No generated column as a key / distribution / clustering / partition column, and no NOT NULL on a VIRTUAL one.
  • No plain BEGIN on a cluster. BEGIN/COMMIT/ROLLBACK work against an embedded Database or a standalone-server driver connection; the cluster coordinator autocommits each statement and rejects plain BEGIN. A cluster commits atomically instead: BEGIN ATOMIC across partitions (two-phase commit) and BEGIN ATOMIC PARTITION within one partition of distribute_by tables — atomicity, not isolation.
  • Joins have no pushdown in a cluster: a single-table WHERE is pushed to the shards (each node returns only matching keys, re-read at quorum), but a join pulls each table to the coordinator and joins there — so joins suit modest tables / lookups, not large fact-to-fact joins.
  • Vector search uses NEAREST, not an ORDER BY embedding <-> [...] operator (see Vector search).
Command index ↑
Search the docs