When to Use Aerospike Secondary Indexes: Memory Trade‑offs and Query Performance

The problem: fast look‑ups beyond the primary key

Many Aerospike applications need to find records by a field that is not the primary key—for example, locating all users in a specific city or all products with a price below a threshold. Scanning the whole namespace for each query is prohibitive at scale, so developers look for a way to index those secondary bins.

Thesis: secondary indexes give millisecond‑range queries but add memory and write‑latency costs

Aerospike secondary indexes live in memory and are kept up‑to‑date automatically on every write. They enable equality and IN look‑ups (and lexicographic range queries on strings) without a full scan. The trade‑off is that each indexed record consumes a few bytes of RAM and each write incurs a small extra cost to update the index. Understanding where the balance lies helps you decide which bins to index.

How secondary indexes work

When you create a secondary index on a bin, Aerospike builds an in‑memory structure that maps the bin’s value to the set of primary‑key digests that hold that value. The index is updated synchronously with the primary‑index write path, so the index is always consistent with the latest committed version of the record (though reads may see a slightly stale view due to the eventual‑consistency model of the cluster).

Index types:

• NUMERIC – for integers or floats; supports equality, IN, and numeric range queries.
• STRING – for UTF‑8 strings; supports equality, IN, and lexicographic range queries.

Creating and verifying an index

You can add a secondary index with a single AQL statement. The example below indexes a numeric bin called price in the namespace test and set products.

# Run in the AQL shell (aql)
CREATE INDEX price_idx ON test.products(price) TYPE NUMERIC;

After creation, you can list indexes to confirm:

SHOW INDEXES;

The output will include a line similar to:

Index name: price_idx, Namespace: test, Set: products, Bin: price, Type: NUMERIC, State: READWRITE

No claim is made that this exact output was observed; it illustrates the expected format.

Worked example: querying the index

Once the index exists, you can run equality or range queries that use it.

# Equality query
SELECT * FROM test.products WHERE price = 49.99;

# Numeric range query (requires NUMERIC index)
SELECT * FROM test.products WHERE price BETWEEN 10 AND 100;

To see whether the index is being used, you can examine the query plan with the EXPLAIN keyword:

EXPLAIN SELECT * FROM test.products WHERE price = 49.99;

The plan should show an INDEX operation rather than a SCAN. Again, this is the expected behavior, not a verified result from a specific run.

Performance impact and measurement

Each write that modifies an indexed bin must also update the index structure. In practice this adds a few microseconds of latency per indexed bin. You can measure the effect with Aerospike’s benchmark tool (asbenchmark) or by timing client‑side writes in a test environment:

1. Run a write‑only workload with no secondary indexes and record average latency.
2. Create one or more indexes on the same bin set.
3. Repeat the workload and compare the latency numbers.

If latency growth becomes unacceptable, consider limiting the number of indexed bins or using a write‑optimized configuration (e.g., increasing the write-block-size or adjusting the defragmentation-lwm).

Trade‑offs and limitations

Three practical limits shape indexing decisions:

• Memory consumption – each indexed record uses roughly 2–3 bytes. Indexing many bins in a high‑cardinality namespace can quickly consume several gigabytes of RAM.
• Write‑latency spikes – under heavy write loads, index maintenance competes with primary‑key writes, leading to occasional latency outliers.
• Eventual consistency – a secondary‑index query immediately after a write may not yet reflect that write, because the index is updated as part of the commit but read replicas may lag.

String range queries are lexicographic; if you need numeric ordering on numeric data, always declare the index as NUMERIC to avoid surprising results.

Actionable closing

Before adding a secondary index:

1. Identify the query patterns that truly need non‑primary‑key look‑ups.
2. Estimate the memory footprint: record_count × avg_bytes_per_index.
3. Test write latency with and without the index in a staging cluster.
4. Monitor index size and write latency in production via asadm -e "show indexes" and latency histograms.
5. Keep the number of indexed bins low enough that memory growth stays predictable and write latency stays within your SLA.

When those checks pass, a secondary index can give you the millisecond‑level response times you need without jeopardizing cluster stability.