VARCHAR in Cluster Tables

DistributedData TypesQuery

Beta Works · surface still evolving · INSERT, SELECT, all comparisons, LIKE 'prefix%' and indexes on VARCHAR are proven end to end; values are capped at 16 KiB and text does not yet take part in joins.

Declaring a text column

CREATE TABLE users (id BIGINT, email VARCHAR);

The column is declared in the catalog before the daemon boots as an orchestrator, and marked as text in the orchestrator’s schema (TableSchema::text("email")) so the compiler knows to treat it as a string.

Inserting and reading

INSERT INTO users VALUES (1, 'ana@example.com'), (2, ''), (3, 'a');
SELECT id, email FROM users;

Every length comes back byte for byte, including the empty string. Strings of up to seven bytes are stored inside the row itself; longer ones are kept in a compact store beside the table’s file and are read as a slice, never copied into a heap string on the way to you.

Comparing

SELECT id FROM users WHERE email = 'ana@example.com';
SELECT id FROM users WHERE email <> 'a';
SELECT id FROM users WHERE email >= 'm' AND email < 'n';
SELECT id FROM users WHERE email LIKE 'ana%';

=, <>, <, <=, > and >= compare the full string, byte by byte in UTF-8 order (no collation). LIKE supports the form 'prefix%': a literal prefix followed by one trailing %. Any other pattern — a leading %, a _, several wildcards — is refused with an error rather than answered by a scan you did not ask for. A NULL in a text column compares as unknown, as everywhere in SQL.

With an index on the column, equality, ranges and prefix searches read only the rows that can match.

Storage and maintenance

Long strings live in a per-file store that only grows while the file is written. When the vacuum compacts a file, it rewrites that store with the surviving rows’ strings only, so deleted text is reclaimed too. Snapshots include the store, and a tenant’s strings never leave the tenant’s directory.

Limits in this release

• A single value may be at most 16 KiB.
• A statement may reference at most 32 distinct string literals.
• INSERT … VALUES accepts string and integer literals; NULL values are not yet accepted through the cluster path.
• Text columns are not yet used in cluster joins, and LIKE is limited to 'prefix%'.
• Comparisons are byte order, not locale order.

Secondary Indexes on Cluster Tables →

Point, range and prefix lookups without a scan.

Cluster Execution Engine →

Which SQL runs on the workers.

Hardware-Aware Semantic Types →

The single-node type system.