Aerospike Secondary Indexes with ASQL Filter Pushdown: Setup, Limits, and Verification

Start here: stop full cluster scans for bin lookups

If your Aerospike queries filter on a non-primary-key bin without a secondary index, the cluster scans every partition to find matches. As data grows, that scan increases latency and network traffic across all nodes. The useful takeaway is to create the secondary index before query traffic begins, use ASQL WHERE clauses so the server pushes the filter to the index scan, and verify with asinfo that the index is actually being used.

How the index and filter pushdown work

A secondary index stores the value of an indexed bin together with the record's primary key. In Aerospike, a namespace is a storage container similar to a database, and a set is a logical grouping inside it, similar to a table. A bin is a named field within a record.

When you write a record, Aerospike updates the index so it stays consistent with the data. When a client runs a query with a matching WHERE clause, filter pushdown moves that condition into the index scan. The server looks up matching primary keys in memory, reads only those records from the responsible partitions, and returns the results. Records that do not match are never read from storage.

Worked example: create an index and query it

Run these commands from a machine with network access to the Aerospike cluster, typically on port 3000. You need an account with privileges to read metadata and create indexes.

asql -h 192.0.2.10 -p 3000

Replace 192.0.2.10 with your cluster node address. Inside the asql shell, create an index on an email bin:

CREATE INDEX ON users.accounts (email_address);

Here users is the namespace and accounts is the set. email_address is the bin to index.

Now run a filtered query:

SELECT * FROM users.accounts WHERE email_address = '[contact removed]';

The server uses the index to find the primary key for that email address, reads the matching record from the partition that owns it, and returns the row to the client.

Risk: creating an index on a namespace that already contains millions of records can consume significant CPU and memory during the initial backfill and may temporarily increase write latency. Plan the change during a low-traffic window or test on a staging cluster first.

Verify that the index is used

After creating the index, confirm that queries are using it rather than falling back to a full scan. From a shell with access to the Aerospike node, inspect index statistics with asinfo. Exact stat names vary by Aerospike version, so consult your server documentation for the correct command. Look for the index you created and confirm its entry count matches the expected number of distinct values in the indexed bin.

Then compare query latency before and after index creation under a representative workload. A useful check is to run the same SELECT ... WHERE statement with the index present and absent, measuring response time and cluster network traffic. If the index-size metric does not grow as expected, or if latency does not improve, the query may still be scanning all partitions.

Limits and operational trade-offs

• Index count per namespace. Aerospike places a limit on how many secondary indexes a namespace can support; confirm the exact maximum for your server version before planning multiple indexes.
• Bin type restrictions. Indexed bins cannot be of type List or Map. Use scalar types such as string or integer.
• High-cardinality bins. A bin with many unique values makes the index larger, consuming more memory and potentially increasing write latency because every insert or update touches the index.
• No native range predicates. Aerospike secondary indexes do not natively support range queries such as WHERE age > 30. Attempting to use an index for a range predicate can result in a full scan.
• Write-heavy workloads. Each indexed write must be propagated to all replicas, which adds latency compared with writes to an unindexed bin.

Common mistakes

• Creating the index after queries begin. Queries run against an unindexed bin trigger cluster-wide scans. Create the index before production traffic depends on it.
• Ignoring case sensitivity. String index lookups are case-sensitive unless you configure a case-insensitive index. A query for [contact removed] will not match [contact removed] stored in the index.
• Relying on indexes for ranges. Using >, <, or BETWEEN on an indexed bin does not use the index the way an equality match does.

Rollback

Because CREATE INDEX changes cluster state, you can remove the index if it causes unacceptable write overhead or memory pressure:

DROP INDEX ON users.accounts (email_address);

After dropping the index, queries that previously used it revert to full partition scans. Confirm query behavior and latency after removal before declaring the rollback complete.

Quick verification checklist

1. Create the index on the target namespace, set, and bin.
2. Run SELECT ... WHERE in asql and confirm results return.
3. Check asinfo index statistics for the expected index entry and record count.
4. Compare query latency with and without the index under a realistic load.
5. Monitor write latency and memory usage on indexed namespaces, especially under write-heavy traffic.