Fast Isn’t Always Enough: Why Secondary Indexes Matter in Aerospike

Introduction

Aerospike is a high-performance NoSQL database designed for speed and scalability. While its primary index enables fast lookups based on primary keys, sometimes applications require searching data based on other fields. This is where Secondary Indexes (SI) come into play.

In this blog, we will explore when to use secondary indexes, how to create and manage them, and the best practices for optimizing their usage in Aerospike.

When to Use Secondary Indexes

Unlike traditional relational databases, Aerospike primarily uses primary key lookups. However, secondary indexes are useful in the following scenarios:

• Querying Non-Primary Key Fields: If your application frequently searches for records based on non-primary key fields, secondary indexes provide efficient filtering.
• Range Queries: When you need to retrieve records within a specific numeric range (e.g., timestamps, prices, or counters).
• Filtering by String or List Elements: Secondary indexes support querying for records based on string, integer, or list bin values.
• Optimizing Read Queries: Instead of scanning the entire dataset, secondary indexes speed up queries by allowing indexed lookups.

How to Create and Use Secondary Indexes

1.Creating and Managing Secondary Indexes

Before Aerospike 5.7, secondary indexes could be created and dropped using AQL:

To create index

aql> CREATE INDEX <index_name> ON ns.set (<bin_name>) <data_type>;

To drop index 

aql> DROP INDEX ns.set <index_name>;

After 5.7: Managing Indexes via asadm

From Aerospike 6.2 onwards, secondary indexes can only be managed using asadm. This method was available before 5.7 as well but became mandatory after the change.
 

To create index

Admin> enable
Admin+> manage sindex create <bin_data_type> <index_name> ns <namespace_name> set <set_name> bin <bin_name>

To drop index

Admin> enable
Admin+> manage sindex delete <index_name> ns <namespace_name> set <set_name>

To view list of indexes 

In aql

aql> show indexes

In asadm

Admin> show sindex

2. Querying Without and With Secondary Indexes    

We need to create a secondary index for the other bin if we need to fetch data using a WHERE condition on that bin. Without a secondary index, we cannot fetch data using a WHERE condition, except by using the primary key (PK).

Querying Without a Secondary Index

aql> SELECT * FROM test.demo WHERE id = 1;
Error: (-12) Max retries exceeded: 5
sub-errors:
201 AEROSPIKE_ERR_INDEX_NOT_FOUND

Querying With a Secondary Index

Admin> enable
Admin+> manage sindex create numeric idx2 ns test set demo bin id
Use 'show sindex' to confirm idx2 was created successfully.

aql> SELECT * FROM test.demo WHERE id = 1;
+----+----+---------+------------+
| PK | id | name    | totalmarks |
+----+----+---------+------------+
| 1  | 1  | "ahash" | 457        |
+----+----+---------+------------+
1 row in set (0.005 secs)

Admin> enable
Admin+> manage sindex create numeric idx3 ns test set demo bin totalmarks
Use 'show sindex' to confirm idx3 was created successfully.

aql> SELECT * FROM test.demo WHERE totalmarks BETWEEN 450 AND 490;
+----+----+---------+------------+
| PK | id | name    | totalmarks |
+----+----+---------+------------+
| 1  | 1  | "ahash" | 457        |
| 4  | 4  | "varun" | 489        |
+----+----+---------+------------+
2 rows in set (0.006 secs)

Storage Methods for Secondary Indexes 

In Aerospike Enterprise Edition (EE), secondary indexes can be stored using different methods depending on the system requirements and performance considerations. The available storage options include:

In Aerospike EE, secondary indexes can be stored in:

• Shared Memory (SHMem) (default) – Fast access using RAM.
• Persistent Memory (PMem) – Ensures indexes persist after a restart.
• Flash (NVMe SSDs) – Reduces RAM usage while maintaining performance.

Each namespace can have a different storage method. Aerospike Community Edition supports only shared memory.

PMem Storage

Database 7.0 and Later

namespace test {  
    sindex-type pmem {  
        mount /mnt/pmem1  
        mounts-budget 750G  
    }  
}  

Prior to Database 7.0

namespace test {  
    sindex-type pmem {  
        mount /mnt/pmem1  
        mounts-size-limit 750G  
    }  
}  

Flash Storage

Database 7.0 and Later

namespace test {  
    sindex-type flash {  
        mount /mnt/nvmea  
        mount /mnt/nvmeb  
        mount /mnt/nvmec  
        mount /mnt/nvmed  
        mounts-budget 1T  
    }  
}  

Prior to Database 7.0

namespace test {  
    sindex-type flash {  
        mount /mnt/nvmea  
        mount /mnt/nvmeb  
        mount /mnt/nvmec  
        mount /mnt/nvmed  
        mounts-size-limit 1T  
    }  
}

Best Practices for Using Secondary Indexes

• Use Only When Necessary: Indexes consume memory, so avoid indexing bins unless required.
• Limit Indexes on Frequently Updated Bins: Updating indexed bins frequently increases overhead.
• Optimize Queries for Performance: Combine secondary index filtering with primary key lookups where possible.
• Monitor Index Usage: Regularly check index efficiency and remove unused indexes.

Conclusion

Secondary indexes in Aerospike provide powerful query capabilities beyond primary key lookups. While they improve read performance, they also introduce memory overhead, so careful planning is required. By following best practices, you can leverage secondary indexes effectively to enhance your application’s performance.