How do I create a columnar table in UltraFabric?

CREATE TABLE … WITH (storage = columnar). The table has the same schema, SQL and tenant isolation as a row table; only the physical layout differs.

Can I update or delete rows in a columnar collection?

Not yet. Columnar collections are append-only: an INSERT of an existing primary key fails with SQLSTATE 23505. Updates and deletes on columnar collections are listed as not built.

Platform · Columnar collections

Same table, same rules, column layout.

A columnar collection holds the same rows with the same schema, security and query semantics as a row table. Only the physical layout differs. It is meant for analytic, append-mostly data: telemetry, events, facts.

TPC-H results SQL and psql

TPC-H SF 0.1, seconds (log scale)

Row vs columnar storage vs DuckDB 1.1.3 on one thread; all answers equal (22 Oct 2026, commit a69b309)

Row Columnar DuckDB

One model

Nothing changes except the layout

Choosing columnar is a storage decision, not a security or SQL decision.

Created in SQL

CREATE TABLE … WITH (storage = columnar). The same types, NOT NULL, primary key and SELECT subset as a row table.

Built

Same verifier

Row groups live inside the tenant's key range, so tenant isolation stays a key range and no raw request can reach another tenant's groups. The same plan check applies to both layouts.

Built

Same answers

A columnar-vs-row differential over at least 1 000 generated plans, and the ten-million-plan campaign, hold the two layouts to identical results.

Built

Append-only

An INSERT of an existing primary key is rejected with 23505. Row tables keep upsert semantics; columnar tables do not, so duplicate detection stays a point lookup.

Built

Each row seen once

A snapshot sees a row in the delta before compaction or in a row group after it, never both and never neither.

Built

Compaction after inserts

The node compacts a tenant's delta rows into new row groups after inserts; moving the rows and marking them happen in one write batch.

Built

Row groups

Limits and encodings

Every limit below is enforced by the engine; stored bytes are treated as untrusted and decoded with a strict decoder, exactly as rows are.

Row group limits

Rows per group	≤ 4 096
Column chunk	≤ 6 MiB
Group	≤ 8 MiB
Fields per collection	≤ 65 534

The group header records the row count and, per column, the null count and the min/max of orderable scalars: a zone map for later predicate skipping.

Column chunk encodings

Int, Date, Time, Timestamp, Decimal	Frame of reference (a minimum) plus bit-packed offsets of width 0–128
UInt	The same frame, unsigned
Bool	Bitmap
F32, F64, Uuid	Raw bits; 16 bytes per UUID
String, Bytes	Dictionary with bit-packed codes when there are at most n/2 distinct values (and at most 65 535); otherwise lengths plus data
Other types	Length-prefixed values

Every chunk starts with a version, a type code, a row count and, if any value is NULL, a validity bitmap. Decoded values are checked for type, nullability, normal form and decimal precision.

Scans

Read only the columns the plan observes

Per group, a scan reads the header and only the chunks of the columns the plan can observe, then the delta rows. Row order within a scan is unspecified, as for every unordered operator.

TPC-H SF 0.1, seconds, median of three including planning

shared development host, 3 CPUs / 6 GB; indicative, not a production promise. lineitem 600 572 rows. The columnar load compacts, advances the GC horizon and compacts the LSM, as a steady-state system would.

Query	Row storage	Columnar	Gain	DuckDB 1 thread	Columnar vs DuckDB	Answers
Q1	0.869	0.308	2.8×	0.0128	24×	equal
Q3	0.71	0.166	4.3×	0.0085	20×	equal
Q5	0.896	0.363	2.5×	0.0078	46×	equal
Q6	0.535	0.074	7.3×	0.002	36×	equal
Q10	0.771	0.245	3.1×	0.0229	11×	equal

Reading the table

What the report says the numbers mean

Q6, a filter and a sum over lineitem, gains the most: 0.535 s to 0.074 s, because it touches few columns.
In one profiling run, reading two columns of lineitem's row groups took about 15 ms. The remaining gap to DuckDB is the executor, not storage; see Analytics.
Two findings from measuring are now in the specification: compaction markers moved to their own subspace so scans stop walking them, and deleted row keys leave tombstones until the GC horizon passes them, so a deployment that holds the horizon back pays with slower delta scans.
Both columns include the executor changes of the same phase; before them the same queries took 3.3–4.3 s.

Not yet

What columnar collections do not do

Listed in the specification as not built. None of these is approximated; a statement that needs one fails and says so.

Updates and deletes

A columnar collection is append-only. UPDATE and DELETE on it are not built; on row tables they are.

Planned

Background compaction

The node compacts after inserts. Automatic compaction on a schedule or on size is not built.

Planned

Zone-map skipping

The header min/max is recorded, but scans do not yet skip row groups by predicate. That needs the security-aware pushdown rule.

Planned

Compression beyond bit-packing

Bit-packing and dictionaries only; no general-purpose compression of chunks yet.

Planned

Compare it with the row engine.

Row storage carries the transactional side; columnar carries the facts. One verifier checks both.

UltraFabric vs DuckDB Row storage