Skip to content

Secondary index queries

Queries matching a (relatively) small subset of records within a large Aerospike namespace or set can be accelerated by optionally indexing often-queried values with a secondary index. Such secondary index (SI) queries run faster than equivalent primary index (PI) queries. This is a trade-off between (typically in-memory) index storage and performance.

Secondary indexes can index the value of a specified record bin or the computed value of an expression applied to the record.

PI and SI queries are served by the same subsystem and share the same policies and features. The SI query is the same as a PI query with the additional ability to use an index filter - a simple predicate leveraging the secondary index. The SI query can then apply a filter expression to the records first matched by the index filter predicate.

Read-only SI queries use the same projection options as primary index queries (bin projection; operation projection on Database 8.1.2 and later).

Types of SI queries

There are four types of SI queries:

  • Basic - read-only, returns records to the client.
  • Aggregation - read-only, returns aggregated results to the client. Aggregations use stream UDFs with map/reduce functions to perform aggregation.
  • Background UDF - write-only, returns nothing to client. Uses a Record UDF to modify records.
  • Background ops - write-only, returns nothing to client.

All types use an index filter to define a predicate used directly against a secondary index:

  • Equals comparison against indexed string, blob or integer values.
  • Range comparisons against indexed integer values. Range query results are inclusive, that is both low end and high end boundary values are included in the results.
  • Point-In-Region or Region-Contain-Point comparisons against geospatial indexes.

A record filter expression can be added to apply further predicates to records matched by the index filter. With the filter expressions, you can:

  • Filter out records based on record metadata, such as last update time or storage size.
  • Filter out records based on bin data, such as integer size thresholds or regular expressions on string bins.
  • Note: aggregation queries do not support filter expressions. Stream UDFs have their own filter function mechanism.

You can fine-tune the Aerospike query system for your use case based on:

  • Query selectivity - the average number of records returned. When multiple secondary indexes are available, the application should query the most selective index (meaning the index with the lowest entries_per_bval). The purpose of the secondary index is to reduce, as much as possible, the number of records read from namespace data storage. A filter expression can be applied to these records, in order to further exclude records from being streamed back to the client waiting for the query results.
  • Throughput and latency.
  • Query load vs. read/write load — the frequency that the secondary index updates.
  • SSD parallelism — the IOPS required to support query throughput.

Key data type

One part of a secondary index definition is the expected data type of the value(s) being indexed. This is termed the key type, or ktype. Secondary indexes key on bin values (RDBMS columns). Secondary index keys can only be created on the following primitive data types:

In Aerospike, distinct secondary indexes are created on a combination of namespace, set name, bin name, key data type, and index type. You can also create a secondary index on a CDT context of a map (document) or list collection data type (CDT) bin to identify the path to the value that should be indexed.

Index type

Aerospike supports four index types (AKA itype):

  • Default where a single value of the given ktype is expected.
  • List where a list of values of the given ktype is expected.
  • Map keys where the ktype-matching keys of a map CDT are indexed.
  • Map values where the ktype-matching values of a map CDT are indexed.

Caveats

Secondary indexes are built independently on each cluster node, a structure for each data partition, which means that a secondary index query hits all the nodes. For better performance, secondary indexes should not be used when key-based commands are an alternative.

For example, secondary indexes should not be built on a “primary key”. The record already has an entry in the primary index for direct, low latency access with read, upsert and delete commands.

Similarly, secondary indexes are not the most efficient way to implement a unique index query (where the entries_per_bval index statistic is equal to 1). Instead, a lookup table provides significantly better performance and throughput. A distinct set can be used to insert all the unique identifiers as record keys with a digest stored in a record bin. A single read operation against the lookup table retrieves the digest of the actual record, which is then used to access that record directly in a subsequent read command. This two-phase lookup pattern is one or two orders of magnitude faster than a secondary index query to find zero or one record.

Limitations

  • Aerospike supports up to 256 secondary indexes per namespace.
  • String and blob/bytes data types are limited to less than 2048 bytes - larger values are silently excluded from the index on write, and queries with an oversized predicate value fail with error code 4 (AEROSPIKE_ERR_REQUEST_INVALID). See secondary index byte-size limits.
  • Migration-tolerant queries were introduced in Aerospike Database 6 and require fully compatible clients.

Use an SI query in your application

To use a secondary index query in your application:

  1. Create secondary indexes on a bin value (in the case of List or Map bins it can be a nested CDT context) or computed value of an expression.
  2. Insert/update records. These write operations will trigger synchronous indexing.
  3. Construct a query with a secondary index filter predicate, optionally adding more predicates with a filter expression. Execute the SI query and process the records streaming back to the client. Use projection operations to project read operations or read expressions in the result.

Create an index

You create and manage secondary indexes with the manage sindex commands of the Aerospike admin tool (asadm). Alternatively, use the sindex-create and other sindex-* info commands. Indexes can also be created programmatically with the createIndex and dropIndex equivalent client API calls in each language client. This requires the sindex-admin privilege if security is turned on.

When you create a secondary index on a bin value:

  • If no set-name is specified the sindex includes records across the entire namespace.
  • The disallow-null-setname parameter controls whether null set names are allowed. By default, the value is false, meaning that null set names are accepted.
Create a bin index
Admin+> manage sindex create numeric dnr-age-idx ns test set donor bin age
Use 'show sindex' to confirm dnr-age-idx was created successfully.

Indexing a value embedded inside a list or map requires a CDT context describing the path to the element.

Create a bin index with a CDT context
Admin+> manage sindex create numeric dnr-zip-idx ns test set donor bin address ctx map_key(zip)
Use 'show sindex' to confirm dnr-zip-idx was created successfully.

Alternatively, this same index can be created with the base64-encoded CDT context

Terminal window
Admin+> manage sindex create numeric dnr-zip-idx ns test set donor bin address ctx_base64 kiKkA3ppcA==

To create an expression index you don’t specify a bin name or context; instead you provide a base64-encoded expression. The computed value must adhere to the specified ktype and itype. To skip indexing the record send back an unknown() from the expression.

Assemble a base64-encoded expression
Expression campaignTotal = Exp.build(
    Exp.add(Exp.intBin("campaign1"), Exp.intBin("campaign2"), Exp.intBin("campaign3"))
);
System.out.println(campaignTotal.getBase64());
// lBSTUQKpY2FtcGFpZ24xk1ECqWNhbXBhaWduMpNRAqljYW1wYWlnbjM=

The base64-encoded expression is used to create the expression index

Create an expression index
Admin+> manage sindex create numeric dnr-sum-idx ns test set donor exp_base64 lBSTUQKpY2FtcGFpZ24xk1ECqWNhbXBhaWduMpNRAqljYW1wYWlnbjM=
Use 'show sindex' to confirm dnr-sum-idx was created successfully.

After the secondary index creation command reaches one node in the cluster, the cluster propagates the request to all cluster nodes. Each node begin building the secondary index on its local data.

In asadm, show sindex describes all the existing secondary indexes assembled on the namespace and its sets.

Terminal window
Admin+> show sindex
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Secondary Indexes (2026-04-08 22:51:37 UTC)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Index Name|Namespace|  Set|    Bin|    Bin|  Index|State|              Context|                                                           Expression
           |         |     |       |   Type|   Type|     |                     |
dnr-age-idx|test     |donor|    age|numeric|default|RW   |--                   |--
dnr-sum-idx|test     |donor|   null|numeric|default|RW   |--                   |add(bin_int("campaign1"), bin_int("campaign2"), bin_int("campaign3"))
dnr-zip-idx|test     |donor|address|numeric|default|RW   |[map_key(<string#3>)]|--
Number of rows: 3

Insert data

Insert data programmatically using one of the Aerospike client libraries. The following examples insert a record into the donor set with this structure:

Example record in the set 'donor'
{
  name: "Austin Albertson",
  age: 57,
  campaign1: 150,
  campaign2: 100,
  campaign3: 75,
  address: {
    state: "CA",
    city: "Los Altos",
    zip: 94023,
  }
}
Key key = new Key("test", "donor", "donor1");


Map<String, Object> address = new HashMap<>();
address.put("state", "CA");
address.put("city", "Los Altos");
address.put("zip", 94023);


client.put(null, key,
    new Bin("name", "Austin Albertson"),
    new Bin("age", 57),
    new Bin("campaign1", 150),
    new Bin("campaign2", 100),
    new Bin("campaign3", 75),
    new Bin("address", address));

Query a bin index

The bin index dnr-age-idx allows you to query for an integer age range.

index filter: age >= 18

filter expression: campaign1 > 200

Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("donor");
stmt.setFilter(Filter.range("age", 18, Integer.MAX_VALUE));


QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.build(
    Exp.gt(Exp.intBin("campaign1"), Exp.val(200))
);


RecordSet recordSet = client.query(queryPolicy, stmt);

In this case, you have a secondary index on age but no index on campaign1. The index filter predicate finds records matching the age criteria, and then the filter expression is applied to each of them.

Query a bin index using CDT context

The bin index dnr-zip-idx indexes the integer value of an embedded field zip of Map bin address.

index filter: address.zip = 94023

filter expression: age >= 18

Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("donor");
stmt.setFilter(Filter.equal("address", 94023,
    CTX.mapKey(Value.get("zip"))));


QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.build(
    Exp.ge(Exp.intBin("age"), Exp.val(18))
);


RecordSet recordSet = client.query(queryPolicy, stmt);

You now have two potential secondary indexes to use. Using entries_per_bval in the Avg Per Bin Val column of asadm’s info sindex or the sindex-stat info command, you can compare how many records share the same indexed value on average. The index with the lowest entries_per_bval is the most selective — use it for the index filter. In a production dataset with many donors, dnr-zip-idx would typically be more selective than dnr-age-idx, so you would choose it for the index filter and apply the age condition through a filter expression onto the records matched by the secondary index.

Terminal window
Admin+> info sindex
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Secondary Index Information (2026-04-08 22:51:43 UTC)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Index Name|Namespace|  Set|                       Node|    Bin|    Bin|     State|~~~~~~~~Entries~~~~~~~~|~~~~Storage~~~~|              Context|                                                           Expression
           |         |     |                           |       |   Type|          |  Total|Avg Per|Avg Per| Type|     Used|                     |
           |         |     |                           |       |       |          |       |    Rec|Bin Val|     |         |                     |
dnr-age-idx|test     |donor|1.0.0.127:3000|age    |numeric|Read-Write|1.000  |0.000  |0.000  |shmem|16.000 MB|--                   |--
dnr-sum-idx|test     |donor|1.0.0.127:3000|null   |numeric|Read-Write|1.000  |0.000  |0.000  |shmem|16.000 MB|--                   |add(bin_int("campaign1"), bin_int("campaign2"), bin_int("campaign3"))
dnr-zip-idx|test     |donor|1.0.0.127:3000|address|numeric|Read-Write|1.000  |0.000  |0.000  |shmem|16.000 MB|[map_key(<string#3>)]|--
           |test     |donor|                           |       |       |          |3.000  |       |0.000  |     |48.000 MB|                     |
Number of rows: 3


Admin+> asinfo -v 'sindex-stat:namespace=test;indexname=dnr-zip-idx'
1.0.0.127:3000 (127.0.0.1) returned:
entries=1;used_bytes=16777216;entries_per_bval=0;entries_per_rec=0;load_pct=100;load_time=0;stat_gc_recs=0


Admin+> asinfo -v 'sindex-stat:namespace=test;indexname=dnr-age-idx'
1.0.0.127:3000 (127.0.0.1) returned:
entries=1;used_bytes=16777216;entries_per_bval=0;entries_per_rec=0;load_pct=100;load_time=0;stat_gc_recs=0

Query an expression index by index name

The expression index dnr-sum-idx can be queried using its index name.

index filter: dnr-sum-idx BETWEEN 200 AND 2000

filter expression: age >= 18

Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("donor");
stmt.setFilter(Filter.rangeByIndex("dnr-sum-idx", 200, 2000));


QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.build(
    Exp.ge(Exp.intBin("age"), Exp.val(18))
);


RecordSet recordSet = client.query(queryPolicy, stmt);

Query an expression index using the expression

The expression index dnr-sum-idx can be queried using a matching expression.

index filter: add(bin_int(“campaign1”), bin_int(“campaign2”), bin_int(“campaign3”)) BETWEEN 200 AND 2000

filter expression: age >= 18

Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("donor");


Expression sumExp = Exp.build(Exp.add(Exp.intBin("campaign1"),
    Exp.intBin("campaign2"), Exp.intBin("campaign3")));
stmt.setFilter(Filter.range(sumExp, 200, 2000));


QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.build(
    Exp.ge(Exp.intBin("age"), Exp.val(18))
);


RecordSet recordSet = client.query(queryPolicy, stmt);

You can get the expression used for a secondary index through asadm’s show sindex command, and get either the human-readable expression or its base64-encoded format using the sindex-list info command.

Background queries

Background queries modify records in place on the server. The example below uses the dnr-age-idx secondary index to target donors aged 30-65 and award a campaign bonus by incrementing campaign1 by 50, throttled to 1000 records per second per node.

A filter expression checks that the update_pass bin is less than 5 (or doesn’t exist), and the operations both increment campaign1 and set update_pass to 5. This makes the query idempotent — re-running it skips records that were already updated.

index filter: dnr-age-idx BETWEEN 30 AND 65

filter expression: update_pass < 5 (or bin does not exist)

operations: increment campaign1 by 50; set update_pass to 5

RPS limit: 1000

Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("donor");
stmt.setFilter(Filter.range("age", 30, 65));
stmt.setRecordsPerSecond(1000);


WritePolicy writePolicy = new WritePolicy();
writePolicy.filterExp = Exp.build(
    Exp.or(
        Exp.not(Exp.binExists("update_pass")),
        Exp.lt(Exp.intBin("update_pass"), Exp.val(5))));


ExecuteTask task = client.execute(writePolicy, stmt,
    Operation.add(new Bin("campaign1", 50)),
    Operation.put(new Bin("update_pass", 5)));


task.waitTillComplete();
Feedback

Was this page helpful?

What type of feedback are you giving?

What would you like us to know?

+Capture screenshot

Can we reach out to you?