Array Engine

NodeDB's array engine stores multi-dimensional sparse data with bitemporal support — system time (when the cell was written) and valid time (when the cell represents). Cells are coordinate-tuple indexed, grouped into tiles, compressed per-tile, and queryable via SQL table-valued functions.

This is a peer engine but uses its own DDL family (CREATE ARRAY) — not CREATE COLLECTION ... WITH (engine='array').

When to Use

• Genomics: (chromosome × position × sample × allele) — replaces TileDB-VCF
• Single-cell biology: (gene × cell × condition × replicate) — replaces TileDB-SOMA
• Earth observation: (lat × lon × band × time) raster cubes — replaces Zarr / TileDB-Geo
• Climate models: (lat × lon × level × time × variable) — replaces HDF5 + Dask
• Astronomy: (RA × Dec × wavelength × time) — replaces custom Zarr stacks
• Sparse ML features: (user × item × context) — replaces specialized matrix-factorization systems

Key Features

• ND coordinate-tuple keying — arbitrary number of dimensions; only materialized cells are stored
• Tile-based compression — cells grouped into tiles; each tile independently compressed (ALP, FastLanes, Gorilla, LZ4 via nodedb-codec)
• Z-order indexing — Hilbert/Z-order curve linearization for spatial locality and fast range queries
• Per-tile ND MBR statistics — minimum bounding rectangle skip; queries prune entire tiles before decompressing
• Bitemporal — both system time (audit trail) and valid time (temporal semantics) tracked per tile
• Row-major or column-major layout — cell_order chosen at creation
• Cross-engine surrogate identity — array cells participate in cross-engine bitmap intersections alongside vector / graph / document / columnar
• Distributed — tiles vShard-routed; queries scatter-gather across cores and nodes
• WAL-durable + Raft-replicated — same durability guarantees as the rest of NodeDB
• Tile-level retention — audit_retain_ms enables GDPR / data-minimization compliance

DDL Syntax

CREATE ARRAY spatial_grid
  DIMS (
    x INT64 [0..1000],
    y INT64 [0..1000],
    z INT64 [0..1000]
  )
  ATTRS (
    temperature FLOAT64 NOT NULL,
    pressure    FLOAT64,
    humidity    FLOAT64
  )
  TILE_EXTENTS (64, 64, 64)
  CELL_ORDER ZORDER
  TILE_ORDER ROW_MAJOR
  WITH (
    prefix_bits = 8,
    audit_retain_ms = 86400000,
    minimum_audit_retain_ms = 3600000
  );

ALTER ARRAY <name> SET (audit_retain_ms = ...) updates retention; DROP ARRAY [IF EXISTS] <name> is two-phase like DROP COLLECTION.

Attribute types

An attribute stores each cell at its declared width. A value outside the width is refused with SQLSTATE 22003 on INSERT, COPY, and the native bulk load. It is never wrapped or clamped.

• An unsigned type reads as the next wider signed column type, so every stored value fits.
• INT is the 4-byte INT32, as in PostgreSQL. A bare FLOAT is the 8-byte FLOAT64.
• A float written to a FLOAT32 attribute rounds to the nearest f32. Only a finite value past the f32 range is refused. 0.1 stored as FLOAT32 reads back as 0.1.
• An integer literal is accepted by every integer and float type. A float literal is accepted by FLOAT32 and FLOAT64 only.
• An integer column compresses by its value range, so a UINT16 column takes the same bytes on disk as an INT64 column of the same values. A FLOAT32 column stores at most 4 bytes per value, half the raw width of FLOAT64.
• A NULL stays NULL through a flush and a compaction, in every type.

Insert

CREATE ARRAY elevation_map
  DIMS (
    lon FLOAT64 [-180..180],
    lat FLOAT64 [-90..90]
  )
  ATTRS (height FLOAT64)
  TILE_EXTENTS (256, 256);

-- COORDS lists the dimensions in schema order. VALUES lists the attributes in schema order.
-- Values are literals: numbers, strings, or NULL for an attribute.
INSERT INTO ARRAY elevation_map
  COORDS (-73.5, 40.7) VALUES (10.5),
  COORDS (-73.6, 40.8) VALUES (12.3),
  COORDS (-73.7, 40.6) VALUES (8.9);

-- Remove cells by coordinate
DELETE FROM ARRAY elevation_map WHERE COORDS IN ((-73.7, 40.6));

-- Force the in-memory tiles to durable storage
SELECT ARRAY_FLUSH('elevation_map');

Bulk Load

COPY <array> FROM STDIN loads cells in bulk. Columns are the dimensions in schema order, then the attributes in schema order.

COPY scene FROM STDIN WITH (FORMAT csv);
COPY scene FROM STDIN WITH (FORMAT csv, DELIMITER ';', HEADER);
COPY scene FROM STDIN;                       -- text format, tab-delimited

• FORMAT is csv or text. The default is text, tab-delimited. HEADER is off unless given. A bare HEADER turns it on. The file form COPY <array> FROM '<path>' takes CSV only, and its HEADER defaults to true.
• Each value parses by its column's declared type. No type is guessed.
• With HEADER, the first line must name the dimensions, then the attributes, in schema order. A mismatch is 42703.
• A wrong column count or an unparsable value is 22P02. A NULL in a NOT NULL attribute is 23502. A coordinate outside its dimension domain is 22003. A value outside its attribute's width, such as 256 in a UINT8 attribute, is 22003.
• The load streams. Rows are checked as they arrive and load in chunks of about 4 MiB. Each chunk is written before the next rows are read, so the server holds a few chunks whatever the input size. A repeated coordinate keeps its last value.
• Outside a transaction block, each chunk commits on its own, as each row of a collection COPY does. A bad row or a refused chunk, such as 53200 under memory pressure, stops the load. The chunks before it stay committed. The error names the row and the committed row count: COPY: CSV row 150000: ... (139810 rows committed). Those rows are in the array.
• Inside BEGIN, every chunk stages, and an error aborts the block, so the load commits or rolls back whole. A chunk that takes the staged redo past the 64 MiB WAL record cap is refused with 54000 before it stages. Load more than that outside a block.
• A record must end within the array engine's memory headroom. A record with no line break past it is 54000.
• The native protocol loads the same way through the ArrayPutBatch opcode (NodeDb::array_put_cells in nodedb-client). The same rules apply to each request.

Query Functions

Array queries are expressed as table-valued functions in FROM. System time and valid time apply via AS OF clauses.

ARRAY_SLICE — multi-dimensional range

SELECT * FROM ARRAY_SLICE(
  'elevation_map',
  '{lon: [-74.0, -73.0], lat: [40.0, 41.0]}',
  ['height'],   -- attribute projection (optional)
  1000          -- max cells (optional)
);

Result Relation

ARRAY_SLICE and ARRAY_PROJECT return one row per cell. The row has one column per dimension, then one column per requested attribute, or every attribute when the list is empty. Each column carries its declared type:

SELECT * lists the dimensions first, then the attributes, in schema order. For a 2-D image with nir and red bands:

SELECT * FROM ARRAY_SLICE('img', '{x: [0, 3], y: [0, 3]}');
--  x | y | nir  | red
-- ---+---+------+------
--  0 | 0 | 0.80 | 0.20
--  0 | 1 | 0.60 | 0.30

Every SELECT clause applies to the rows: WHERE, computed columns, GROUP BY, aggregates, HAVING, ORDER BY, DISTINCT, LIMIT / OFFSET, and window functions. Qualify a column with the call's alias, or with the array name when the call has no alias.

-- NDVI per cell, vegetated cells only, greenest first
SELECT x, y, (nir - red) / (nir + red) AS ndvi
FROM ARRAY_SLICE('img', '{x: [0, 3], y: [0, 3]}') AS t
WHERE t.nir > t.red
ORDER BY ndvi DESC
LIMIT 10;

-- Mean NIR per column of pixels
SELECT x, AVG(nir) AS mean_nir
FROM ARRAY_SLICE('img', '{x: [0, 3], y: [0, 3]}')
GROUP BY x;

The 4th-argument limit caps the cells the slice reads before any clause applies. An outer LIMIT applies to the query's result. A smaller outer LIMIT also lowers the read cap when no other clause reads past the cut. A WHERE bound on a dimension narrows the read range only when the slice has no cap. A capped slice keeps the cells its cap selects.

AS OF SYSTEM TIME NULL adds a _ts_system column: the version's system time in milliseconds. SELECT * omits it. Name it to read it.

ARRAY_PROJECT — attribute projection

SELECT * FROM ARRAY_PROJECT('spatial_grid', ['temperature', 'pressure']);

ARRAY_PROJECT returns the same typed relation as ARRAY_SLICE, so every SELECT clause applies to its rows. The attribute list is required and must not be empty.

ARRAY_AGG — reduce an attribute

Reduces one attribute with a reducer. The optional 4th argument names a dimension to group by:

-- One result for the whole array
SELECT * FROM ARRAY_AGG('spatial_grid', 'temperature', 'SUM');

-- One result per value of x
SELECT * FROM ARRAY_AGG('spatial_grid', 'temperature', 'SUM', 'x');

The result has a result column. A grouped call also has a group column that holds the dimension value. The group column takes the group dimension's declared type: TIMESTAMP_MS gives timestamp, FLOAT64 gives float8, INT64 gives int8, and STRING gives text.

A temporal call (AS OF) appends one summary row with the bool column truncated_before_horizon.

Reducers are case-insensitive: 'SUM', 'COUNT', 'MIN', 'MAX', and 'MEAN' (or 'AVG').

The result type follows the reducer and the attribute type:

SUM, MEAN, MIN, and MAX accept integer and float attributes only. COUNT accepts every attribute type. Another reducer over a STRING or BYTES attribute is refused at plan time with a type mismatch error.

An integer SUM adds exactly, so 2^53 + 1 stays 9007199254740993. A SUM past the bigint range is refused with SQLSTATE 22003. MIN and MAX sort NaN above every other float, as PostgreSQL does.

ARRAY_ELEMENTWISE — between two arrays of the same shape

SELECT * FROM ARRAY_ELEMENTWISE('current_grid', 'baseline_grid', 'sub', 'temperature');

Operations are 'add', 'sub', 'mul', and 'div', case-insensitive. The arrays must share a schema shape. The result has coords and attrs columns, both JSON.

On integer attributes, add, sub, and mul are exact 64-bit integers, and a result past the bigint range is refused with SQLSTATE 22003. div, and every operation on a float attribute, yields a 64-bit float. Division by zero yields NULL.

ARRAY_AGG and ARRAY_ELEMENTWISE return rows of their own shape, not dimensions and attributes. A query over them takes * or their own output columns, and LIMIT. Every other clause is refused with an error that names the clause. Use ARRAY_SLICE to filter, group, or order cells.

Maintenance

SELECT ARRAY_FLUSH('spatial_grid');    -- force memtable flush
SELECT ARRAY_COMPACT('spatial_grid');  -- merge tile versions, reclaim space

ARRAY_FLUSH returns one object {"flushed": 1} on success. ARRAY_COMPACT returns one object {"compacted": n}, where n is 1 when a merge ran and 0 when there was nothing to merge. A failure raises an error. Compaction also runs automatically in the background.

Bitemporal Queries

Every array cell carries two times:

• System time — when the value was written (audit trail, compliance, point-in-time recovery)
• Valid time — when the value represents (forecasts, backdated corrections, scientific replays)

-- Read cells as the array existed in the past
SELECT * FROM ARRAY_SLICE('data', '{x: [0, 99], y: [0, 99]}', ['value'])
AS OF SYSTEM TIME 1700000000000;

-- Read cells whose valid-time interval includes a given moment
SELECT * FROM ARRAY_SLICE('forecast', '{x: [0, 99], y: [0, 99]}', ['temp'])
AS OF VALID TIME 1700000000000;

-- Both clauses combined
SELECT * FROM ARRAY_SLICE('forecast', '{x: [0, 99], y: [0, 99]}', ['temp'])
AS OF SYSTEM TIME 1700000000000 AS OF VALID TIME 1700000001000;

A time is an integer in milliseconds since the Unix epoch, NOW(), or an ISO-8601 string such as '2024-01-15T00:00:00Z'.

System-time–based retention is the path to GDPR and data-minimization compliance: audit_retain_ms makes tiles older than the window eligible for irreversible purge during compaction.

Cross-Engine Queries

Array cells carry surrogate identity like the rows of the other engines. An ARRAY_SLICE or ARRAY_PROJECT call can be a JOIN source. A slice row holds one field per dimension and attribute, keyed by name, so a join key reads them directly. A slice row has no id column.

-- Join slice cells to a collection on a dimension value
SELECT c.x, c.y, c.attr1, s.name
FROM ARRAY_SLICE('spatial_data', '{x: [0, 999], y: [0, 999]}', ['attr1']) AS c
JOIN sites s ON c.x = s.grid_x;

See Architecture Overview for the cross-engine identity model.

Performance

• Tile-level parallelism — each tile is read and processed on its own core
• Compression — typical 5–20× depending on data homogeneity
• Range queries — Z-order layout gives cache-friendly access; ND-MBR per-tile stats prune irrelevant tiles before decompression
• Sparse-friendly — only materialized cells are stored; implicit zeros and empty regions cost nothing