Indexes

Goal

Explain what types of indexes are available in AngaraBase, when to use them, and how they interact with MVCC.

AngaraTree — index engine

AngaraTree is the index engine for AngaraBase. Indexes are stored in .atl files separate from heap data.

B+tree (default)

The primary index type. Suitable for equality and range queries; keys are stored in deterministic order.

-- Creating a B+tree index (equivalent forms):
CREATE INDEX idx_name ON orders (customer_id);
CREATE INDEX idx_name ON orders USING btree (customer_id);

A B+tree index accelerates:

• Exact matches: WHERE customer_id = 42
• Ranges: WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'
• Sorting: ORDER BY customer_id

BRIN (Block Range Index)

A compact index for data with a natural order (append-only, time-series). BRIN stores min/max values for ranges of heap pages, allowing entire blocks to be skipped during scans.

CREATE INDEX idx_ts ON events USING brin (created_at);

Supported key types:

How BRIN works: the index acts as an accelerator path — first it prunes blocks that do not contain the required values, then a heap fetch is performed with an MVCC predicate recheck. BRIN does not guarantee exactness — it only narrows the search area.

Efficiency metric: angara_brin_range_efficiency shows the fraction of blocks skipped thanks to BRIN. The closer to 1.0, the more efficient the index (data is well-clustered).

Hash / Bloom

Reserved as optional/future index types. Not currently implemented.

Indexes and MVCC

An index stores TID references (page_id, slot_id) to rows in the heap. The visibility of a row is determined not by the index, but by the MVCC layer when reading the heap page:

1. The query accesses the index → retrieves a set of TIDs.
2. For each TID, the heap page is read.
3. The MVCC layer checks the visibility of the row version for the current transaction.

Consequence: an index may contain references to invisible (obsolete) row versions. This is normal — such entries are filtered during the heap fetch.

IndexStore — persistent secondary indexes

AngaraBase supports persistent secondary indexes for RowStore tables via IndexStore.

How it works

• CREATE INDEX builds the index via a full table scan (build_from_rows) and saves the result.
• DML (INSERT/DELETE) automatically updates all table indexes — fail-closed: if the index update fails, the heap mutation is rolled back.
• The optimizer uses the index for WHERE col = value queries (O(log N) instead of O(N) seq_scan).

Resource constraints

Observability

Current Limitations

Attempting to create an unsupported index returns SQLSTATE 0A000 (feature_not_supported).

When to create indexes

Recommended:

• On columns frequently used in WHERE, JOIN ON, ORDER BY.
• BRIN — on time-series columns of tables with append_only = true, where data is inserted in ascending order of the key.

Not recommended:

• On tables with a small number of rows (a full scan will be faster).
• On columns with very low selectivity (e.g., boolean flags).
• Creating many indexes on a single table slows down INSERT/UPDATE/DELETE.

Use EXPLAIN ANALYZE to check if the optimizer is using an index. For more details — Query processing.

Index Integrity Check

To perform an offline check of B+tree index integrity, the validate() function is available:

SELECT angara_index_validate('idx_name');

Recommended to run after a crash or recovery from backup.

Related Sections

Concepts (What to read next)

• Query Processing — how the optimizer selects and combines indexes.
• Storage Engine — how B+tree pages map onto the tablespace.
• Transactions and MVCC — why updating indexes under load requires MVCC visibility.

How-to (What to do)

• DDL: CREATE/DROP INDEX — syntax for creating and dropping indexes.
• Diagnostics — how to use EXPLAIN ANALYZE and sys.* to see if an index is used.

Reference

• Data Types — which types are supported as index keys.
• System Views sys.* — sys.indexes, sys.column_stats for coverage analysis.