Optimizing Data Retrieval with Aerospike Secondary Indexes

The Problem: Finding Records Without the Primary Key

Aerospike is optimized for primary key lookups, which provide predictable, single-digit millisecond latency. However, real-world applications often need to retrieve records based on non-key attributes—such as finding all users in a specific city or all orders within a price range. Without a secondary index, the only way to find these records is via a scan, which reads every record in the namespace, causing massive CPU and I/O spikes in production environments.

The solution is the Secondary Index (SI). An SI allows you to query a specific bin (column) to find the primary keys of records that match a value, turning a full-cluster scan into a targeted lookup.

How Secondary Indexes Work

Unlike the primary index, which is partitioned across the cluster, a secondary index is local to each node. Each node indexes only the data it stores. When you execute a query, the client sends the request to all nodes in the cluster (a "scatter-gather" operation). Each node searches its local index and returns the matching records to the client.

Implementation Example: User Location Lookup

In this example, we assume a namespace named demo and a set named users. We want to retrieve users based on their city bin.

1. Define the Index via AQL

Run this command in the Aerospike Query Language (AQL) shell to create a string index on the city bin:

CREATE INDEX idx_city ON demo.users (city) STRING

2. Querying via Java Client

Using the Aerospike Java Client, you must define a QueryPolicy and a Statement. The Statement specifies the filter used to traverse the index.

// Define the query policy
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.setNamespace("demo");
queryPolicy.setSetName("users");

// Create a statement with an equality filter for the 'city' bin
Statement statement = new Statement();
statement.setFilter(Filter.newEq("city", "Paris"));

// Bound the result set to prevent memory exhaustion
statement.setMaxRecords(100);

// Execute the query
RecordSet recordSet = client.query(queryPolicy, statement);

while (recordSet.hasNext()) {
    Record record = recordSet.next();
    System.out.println("Found User Key: " + record.key);
}

Performance and Resource Limits

Secondary indexes provide speed but introduce specific engineering trade-offs:

• Memory Consumption: SIs are stored in RAM by default. Every indexed entry consumes memory proportional to the size of the bin value and the primary key. Excessive indexing can lead to memory pressure, triggering record evictions.
• Scatter-Gather Overhead: Because the index is local, the query cost increases linearly with the number of nodes in the cluster. If you have a 100-node cluster, every SI query hits 100 nodes.
• Asynchronous Population: When you create an index, Aerospike builds it in the background. Queries executed immediately after creation may return partial results.

Common Engineering Mistakes

Using SIs for High-Selectivity Queries

A common mistake is using an SI for a value that matches 20% or more of the total dataset. If a query returns a huge fraction of the records, the overhead of managing the RecordSet and the network traffic often makes a filtered scan more efficient. SIs are intended for low-selectivity lookups (finding a small needle in a large haystack).

Ignoring the Single-Filter Limit

Classic Aerospike SI queries support only one indexed filter per statement. If you need to find users who live in "Paris" AND are "Active", you must:

1. Index the city bin.
2. Use Filter.newEq("city", "Paris") to narrow the results via the index.
3. Chain an additional Filter.newEq("status", "Active") using a logical AND. The second filter is applied as a post-index check on the records retrieved by the first filter.

Verification and Diagnostics

To verify that an index is fully built and active, use the asinfo tool on the server:

# Run on the Aerospike server node
asinfo -v 'sindex'

Look for the status of your index. An index is only fully usable once its status is RW (Read-Write). If it is still in the BUILDING state, your query results will be incomplete.

Rollback

If an index is consuming too much memory or is no longer needed, remove it via AQL to reclaim RAM:

DROP INDEX idx_city ON demo.users