Skip to content

Secondary index queries

For the complete documentation index see: llms.txt

All documentation pages available in markdown.

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.

The Java SDK and Python SDK tabs use Developer SDK AEL text where it can express the example; the other tabs build the same expression with their client’s Exp builder. See the AEL reference and Query records in the Developer SDK.

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:

  • Foreground - 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.

For a foreground read query, the query optimizer selects the index by reading the AEL the query carries. A query that carries no AEL must name its index with an explicit index filter, as background queries and aggregations always must:

  • 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.

On Database 8.2.0 and later, a query with no readable matching index fails with AS_ERR_SINDEX_NOT_FOUND (201), because allowScansWithWhere is false by default. Set it to true to let the query read every record in the namespace or set instead. See Primary index fallback.

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 an SI 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 (also known as 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 an SI 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 an SI query to find zero or one record.

Limitations

  • Aerospike supports up to 256 secondary indexes per namespace.
  • String and blob/bytes index keys are limited to 2048 bytes. Values longer than 2048 bytes are silently excluded from the index on write. A filter whose literal is 2048 bytes or longer cannot use a secondary index. In AEL it is dropped from index selection and evaluated on each record instead, with no error. An explicit index filter carrying such a value is rejected 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.

Foreground queries

Foreground queries are read-only. To use an SI 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 in one of these ways:

    • The query optimizer, which selects the index from the query’s AEL.
    • Explicit SI filter predicate plus optional filter expression (required for background queries and legacy SI queries).

    Execute the SI or PI query and process the records streaming back to the client. Use operation projection to project read operations or read expressions in the result.

Create an index

Create and manage secondary indexes with manage sindex in asadm or sindex-create through asinfo. Both accept the integer key type for integer bins in Database 8.2.0 and later. Use asadm for interactive work and asinfo for automation.

If you are creating a database administration application, you can also create a secondary index programmatically with the createIndex and dropIndex equivalent client API calls in each language client.

When you create an SI:

  • If a set name is specified, the index covers only records in that set.
  • If no set name is specified:
    • Database 6.1.0 and later: the index covers all records in the namespace, regardless of set membership. In show sindex output, these indexes display null in the Set column.
    • Prior to Database 6.1.0: the index covers only records not belonging to any set (the null set).
Create a bin index (asadm)
Admin+> manage sindex create integer dnr-age-idx ns test set donor bin age
Create a bin index (asinfo)
asinfo -v 'sindex-create:indexname=dnr-age-idx;namespace=test;set=donor;bin=age;type=integer;indextype=default'
Verify the index
Admin+> show sindex
~~~~~Secondary Indexes (2026-09-26 15:07:24 UTC)~~~~~
 Index Name|Namespace|  Set|Bin|    Bin|  Index|State
           |         |     |   |   Type|   Type|
dnr-age-idx|test     |donor|age|integer|default|RW
Number of rows: 1

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 integer dnr-zip-idx ns test set donor bin address ctx map_key(zip)


Admin+> show sindex
~~~~~~~~~~~~~~~~~~Secondary Indexes (2026-09-26 15:07:24 UTC)~~~~~~~~~~~~~~~~~~
 Index Name|Namespace|  Set|    Bin|    Bin|  Index|State|              Context
           |         |     |       |   Type|   Type|     |
dnr-zip-idx|test     |donor|address|integer|default|RW   |[map_key(<string#3>)]
Number of rows: 1

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

Terminal window
Admin+> manage sindex create integer 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.

The result type of the expression decides which comparisons the index can serve. An expression that returns an integer serves both equality and range comparisons. An expression that returns a string or blob serves equality only, because string and blob keys are stored hashed and have no order to range over. If you need range queries, write the expression so it returns an integer.

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 integer dnr-sum-idx ns test set donor exp_base64 lBSTUQKpY2FtcGFpZ24xk1ECqWNhbXBhaWduMpNRAqljYW1wYWlnbjM=
Use 'show sindex' to confirm dnr-sum-idx was created successfully.

Alternatively, on Database 8.2.0 and later with Tools 13.1.0 (asadm 5.1.0) or later, you can create the same expression index directly from Aerospike Expression Language (AEL) source using asadm, without writing any client code. Pass the AEL text to ael, surrounded by single quotes:

Create an expression index from AEL
Admin+> manage sindex create integer dnr-sum-ael-idx ns test set donor ael '$.campaign1:INT + $.campaign2:INT + $.campaign3:INT'
Use 'show sindex' to confirm dnr-sum-ael-idx was created successfully.

The :INT type pins are required here: with three unpinned bins in a + chain, and no literal to anchor the type, the compiler cannot tell whether + means numeric addition or string concatenation, and rejects the expression.

If the AEL text is awkward to quote in your shell or script, base64-encode it and pass it to ael_b64 instead. The following command creates the same index, where the encoded value is base64("$.campaign1:INT + $.campaign2:INT + $.campaign3:INT"):

Create an expression index from base64-encoded AEL
Admin+> manage sindex create integer dnr-sum-ael-idx ns test set donor ael_b64 JC5jYW1wYWlnbjE6SU5UICsgJC5jYW1wYWlnbjI6SU5UICsgJC5jYW1wYWlnbjM6SU5U
Use 'show sindex' to confirm dnr-sum-ael-idx was created successfully.

The ael and ael_b64 modifiers require Tools 13.1.0 (asadm 5.1.0) or later.

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

In asadm, show sindex describes all the existing secondary indexes assembled on the namespace and its sets. The Expression column shows the server’s human-readable rendering for indexes created with exp_base64. The AEL column shows the original AEL source for indexes created with ael or ael_b64, and requires Tools 13.1.0 (asadm 5.1.0) or later.

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

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,
  }
}
DataSet donor = DataSet.of("test", "donor");


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


session.upsert(donor.id("donor1"))
    .bin("name").setTo("Austin Albertson")
    .bin("age").setTo(57)
    .bin("campaign1").setTo(150)
    .bin("campaign2").setTo(100)
    .bin("campaign3").setTo(75)
    .bin("address").setTo(address)
    .execute();

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

DataSet donor = DataSet.of("test", "donor");


RecordStream stream = session.query(donor)
    .where("$.age >= 18 and $.campaign1 > 200")
    .execute();

In this case, you have an SI 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 namespace-wide index (no set)

When you create an index without specifying a set, it covers all records in the namespace. This is useful for bins that exist across multiple sets, such as a created timestamp.

Create a namespace-wide index
Admin+> manage sindex create integer ns-created-idx ns test bin created

Use show sindex to confirm ns-created-idx was created successfully.

Verify the index: Set column shows null
Admin+> show sindex
~~~~~~~~Secondary Indexes (2026-09-26 15:07:24 UTC)~~~~~~~~
    Index Name|Namespace| Set|    Bin|    Bin|  Index|State
              |         |    |       |   Type|   Type|
ns-created-idx|test     |null|created|integer|default|RW
Number of rows: 1

To query this index, omit the set from the statement. The query returns matching records from all sets and from records with no set.

index filter: created BETWEEN one_week_ago AND now

long now = System.currentTimeMillis();
long oneWeekAgo = now - 7L * 24 * 60 * 60 * 1000;


DataSet wholeNamespace = DataSet.of("test", null);


RecordStream stream = session.query(wholeNamespace)
    .where("$.created >= " + oneWeekAgo + " and $.created <= " + now)
    .execute();

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

DataSet donor = DataSet.of("test", "donor");


RecordStream stream = session.query(donor)
    .where("$.address.zip == 94023 and $.age >= 18")
    .execute();

You now have two potential secondary indexes to use. If the query carries AEL, the query optimizer chooses between them by entries_per_bval. Otherwise the application chooses.

The following manual comparison applies when the application chooses the index itself: on Database versions earlier than 8.2.0, or for a query the optimizer does not select for, such as a background query, an aggregation, or any query that carries no AEL. 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 SI.

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    |integer|Read-Write|1.000  |0.000  |0.000  |shmem|16.000 MB|--                   |--
dnr-sum-idx|test     |donor|1.0.0.127:3000|null   |integer|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|integer|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

DataSet donor = DataSet.of("test", "donor");


RecordStream stream = session.query(donor)
    .where("$.campaign1:INT + $.campaign2:INT + $.campaign3:INT >= 200"
        + " and $.campaign1:INT + $.campaign2:INT + $.campaign3:INT <= 2000"
        + " and $.age >= 18")
    .withHint(hint -> hint.forIndex("dnr-sum-idx"))
    .execute();

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

DataSet donor = DataSet.of("test", "donor");


RecordStream stream = session.query(donor)
    .where("$.campaign1:INT + $.campaign2:INT + $.campaign3:INT >= 200"
        + " and $.campaign1:INT + $.campaign2:INT + $.campaign3:INT <= 2000"
        + " and $.age >= 18")
    .execute();

You can get the expression used for an SI through asadm’s show sindex command, and get either the human-readable expression or its base64-encoded format using the sindex-list info command. For an index created from AEL (Database 8.2.0 or later), show sindex in Tools 13.1.0 (asadm 5.1.0) or later shows the AEL source itself in the AEL column, and the Expression column shows --.

Hint the query optimizer to use a specific index

Some secondary index queries can be answered by more than one secondary index. For example, a query that filters on both age and country, where each bin has its own index, could be answered by either one, and the optimizer picks one automatically based on its own selectivity estimate. A QueryHint with index_name tells the optimizer which index to prefer instead. bin_name is not a second hint shape: it bypasses index selection entirely and runs the query against the primary index, and exists only to migrate code that relied on the older client-side behavior. The two are mutually exclusive. A hint can also override the query’s expected duration (query_duration). See Query optimizer.

This is the Developer SDK equivalent of naming an index explicitly with the legacy client’s Filter.rangeByIndex(...) / where_with_index_name(...) (see Query an expression index by index name), for use with where() predicates instead of a hand-built Statement/Filter.

import com.aerospike.client.sdk.DataSet;
import com.aerospike.client.sdk.Record;
import com.aerospike.client.sdk.RecordStream;


DataSet customers = DataSet.of("test", "customers");


RecordStream stream = session.query(customers)
    .where("$.age > 30 and $.country == 'USA'")
    .withHint(hint -> hint.forIndex("age_idx"))
    .execute();
stream.forEach(result -> {
    Record rec = result.recordOrThrow();
    System.out.printf("age=%d country=%s%n", rec.getInt("age"), rec.getString("country"));
});
stream.close();

Background queries

Background queries modify records in place on the server. The example below uses the dnr-age-idx SI 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, because 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

from aerospike_sdk import DataSet, Filter


donor = DataSet.of("test", "donor")


task = (
    session.background_task()
    .update(donor)
    .index_filters(Filter.range("age", 30, 65))
    .where("not($.update_pass.exists()) or $.update_pass < 5")
    .bin("campaign1").add(50)
    .bin("update_pass").set_to(5)
    .records_per_second(1000)
    .execute()
)
task.wait_till_complete_blocking()

Querying a masked bin

Data masking does not prevent you querying a set that contains masked bins. It prevents one thing: using a secondary index that is built on a masked bin. This applies to every client and every query type, including background and aggregation queries, since Database 8.1.1.

The queryRole without read-masked
Uses no secondary indexRuns. Masked bins come back masked, as they do for a record read
Filters with an expression that reads a masked binRuns. The expression sees the masked value, not the original
Uses a secondary index built on a masked binFails with AS_SEC_ERR_ROLE_VIOLATION (81)
Uses a secondary index built on a bin that is not maskedRuns, even if the records contain masked bins

The same applies to an expression index whose definition references a masked bin.

A secondary index stores the original values, not the masked ones. Letting a query range over an index on a masked bin would let a caller recover a masked value by narrowing the predicate until rows appear, which is what masking exists to prevent. A filter expression cannot be used that way, because it only ever sees masked data.

Grant read-masked on the bin, or query through an index on a bin that is not masked.