Optimizing Aerospike Secondary Indexes for High-Performance Querying

In Aerospike, retrieving a record by its primary key is a sub-millisecond operation. When an application needs to find records by a non-primary-key attribute—such as a user email, status code, or timestamp—the database would otherwise perform a full cluster scan, reading every record in the namespace. Secondary Indexes (SIs) eliminate that scan by building an in-memory mapping between attribute values and primary keys, enabling targeted equality and range queries without touching unrelated records.

Prerequisites

• Memory capacity: Secondary indexes are stored entirely in RAM. The aerospike.conf must allocate enough memory to hold the cardinality sets of the indexed field. Large indexes can exhaust namespace memory if not planned.
• Administrative access: Index creation requires privileges through the Aerospike CLI (aql), the admin tool, or a client with security roles that permit DDL operations.
• Schema awareness: The attribute you index must exist in the records of the target set. Records missing the bin are simply not included in the index.

Choosing Between Scalar and Bitmap Indexes

Aerospike supports two index types that map to different query patterns:

• Scalar indexes: Suited to integers, doubles, floats, and strings. They support equality (=) and range (gt, lt, ge, le) predicates.
• Bitmap (filter) indexes: Designed for low-cardinality strings or boolean values. They map each value to a bitset of primary keys, making logical combinations such as AND and OR efficient.

Creating a Scalar Index

Use the Aerospike Query Language (aql) shell to create a scalar index on an integer bin. The following example indexes the age bin in the users set of the test namespace.

# Run in the aql shell connected to your cluster
CREATE INDEX idx_age ON test.users (age) NUMERIC;

Replace idx_age with your index name, test with the target namespace, users with the set name, and age with the bin name. Confirm the exact type keyword against your server version, as data type names can vary between releases.

Monitoring the Background Build

Issuing the create command starts a background process that scans existing records to populate the index. This scan consumes CPU and I/O, and the index is not atomic: queries issued during the build may return partial results until the scan completes.

Check the index status and memory footprint with asinfo:

# Run from any cluster node with administrative access
asinfo -d indexes

Look for the status field in the output. If it indicates the index is still building, the scan is in progress; once it reports complete, the index is ready. Also monitor the index-build metric in your monitoring stack to confirm the background process finished successfully.

Verifying Index Usage

After the build completes, run a filtered query to confirm the index is used rather than a table scan:

# Run in the aql shell
SELECT * FROM test.users WHERE age > 25;

If the index is active, the query returns results with low latency and minimal cluster-wide CPU. You can also inspect query metrics or logs to confirm the index was selected instead of a scan.

Enforcing Unique Constraints

Aerospike can enforce uniqueness at the database level using a secondary index. When a unique index is defined on a bin, the server rejects writes that would create duplicate values for that bin. This shifts uniqueness enforcement from application logic into the database, though it adds write-path overhead because every write must check the index.

Recovery Options

If an index is misconfigured, consumes too much memory, or degrades write latency, drop it. Dropping an index removes the in-memory mapping but does not affect the underlying records.

# Run in the aql shell
DROP INDEX idx_age ON test.users;

After dropping, re-run asinfo -d indexes to confirm the index is gone. If the index was dropped accidentally, recreate it and wait for the background build to complete before relying on it for queries.

Limitations and Risks

• Write latency: Every update to an indexed bin requires updating the corresponding index entry. High-cardinality indexes increase this overhead.
• Memory exhaustion: Index metadata lives in RAM. If the index grows beyond the allocated namespace memory, the node can evict records or refuse writes.
• Non-atomic creation: Queries during the build phase may return incomplete results. Applications should tolerate partial results or wait for the build to finish.
• Version assumptions: Index types, configuration parameters, and CLI syntax can change between Aerospike server versions. Validate commands against your specific server and client documentation before production use.

A practical final check is to compare query latency and node CPU before and after index creation. If the filtered query does not improve materially, review the index type, cardinality, and query predicate to ensure the optimizer can use the index effectively.