Yuxu Ge
Article · 2022-04-01

Cassandra Secondary Index Rebuilding in Practice

Cassandra secondary indexes do not suit all query patterns. Misuse degrades write performance—every write must also update the index table—increases query latency, and burdens the cluster. Cassandra 3.x introduced improved mechanisms such as SAI (Storage-Attached Indexing), but classical secondary indexes remain widely deployed. Understanding how they operate helps determine when indexing is appropriate and when to consider alternatives.

Why manual index rebuilding is needed

In principle, Cassandra secondary indexes maintain themselves automatically as data updates occur, requiring no manual intervention. In practice, production clusters often experience index anomalies or performance problems that demand repair. The common causes are:

When indexes fail to serve queries correctly—returning wrong results or timing out—or when the index itself causes significant write or read performance degradation, manual index rebuilding is an effective repair.

Practical index rebuilding steps

Below is a real-world scenario: we maintain a Keyspace keyspace_xxx with a table table_xxx indexed on column field_xxx via index index_xxx. Monitoring recently showed that queries on this indexed column produce anomalous results (expected data does not appear), and write latency has increased, suggesting index problems. We plan to rebuild during a maintenance window. The general procedure is:

Confirm index status

Before proceeding, assess the current index state. Log into a cluster node and check the disk space consumed by index files to gauge index scale. Cassandra data typically resides in .../data/data/<keyspace>/<table>, with secondary index data stored in subdirectories like <table>.<index_name>. Check the index directory size:

cd /path/to/cassandra-data/data/data/keyspace_xxx
du -h --max-depth=1 | grep index_xxx

If index data files are unexpectedly large (many gigabytes), exceeding expectations, the index likely holds significant historical debris. This confirms the need to rebuild.

Notify and stop applications

Index rebuilding requires dropping and recreating the index, during which queries relying on it will fail. Notify stakeholders and execute during a maintenance window. Stop or pause the application services using that index (for example, microservices like service_xxx in our case) to prevent incorrect query results during rebuilding. Halting writes to the table also helps ensure consistency during rebuild, though Cassandra supports online rebuilding; static environments carry lower operational risk.

Connect to the Cassandra cluster

Use CQL tools or drivers to connect and execute index operations. Connect via cqlsh:

$ cqlsh 10.x.x.x    # 连接到集群某节点的 CQL 接口，IP已脱敏为10.x.x.x

Then switch to the target Keyspace:

USE keyspace_xxx;

Back up the table schema (optional)

Before dropping the index, save the table schema using DESCRIBE TABLE table_xxx;. This documents table structure and index configuration, preventing mistakes. If needed, you can also retain a backup of the index definition.

Drop the index

Remove the anomalous index:

DROP INDEX IF EXISTS index_xxx;

IF EXISTS prevents errors if the index no longer exists. Dropping removes index metadata from the system and triggers local deletion of index data on all cluster nodes—cleanup of index SSTable files proceeds in the background.

Create the index

After confirming deletion, recreate the index:

CREATE INDEX index_xxx ON table_xxx (field_xxx);

Cassandra asynchronously rebuilds the index. Each cluster node scans the field_xxx column in table table_xxx and inserts index entries. For large tables, this process can take hours. During rebuilding, new index queries may not return complete results until the process finishes.

Monitor rebuild progress

After submitting the index creation, await completion. Monitor via:

Verify the index

Once rebuilding completes, validate index operation with a test query. We know the table contains a record where field_xxx equals 'some_value' (substitute the actual value):

SELECT * 
FROM table_xxx 
WHERE field_xxx = 'some_value' 
LIMIT 1;

If the result includes that record without timeout, index query functionality is restored. For numeric fields, omit quotes; adjust the query according to the field type.

Restore application services

After confirming index functionality, restart paused services:

# 重启相关应用服务
$ cd /path/to/service_xxx1 && sh startup.sh start
$ cd /path/to/service_xxx2 && sh startup.sh start
$ cd /path/to/service_xxx3 && sh startup.sh start

Reestablish application connections to Cassandra and verify business queries work. Index rebuilding is complete.

Through these steps, we successfully rebuilt the Cassandra secondary index. Dropping the index freed significant disk space; afterward, index queries resumed normal operation and write performance improved. This confirmed that manual index rebuilding effectively resolves index anomalies.

Python automation for index rebuilding

The above procedure can execute manually step-by-step, but automation enables faster and repeatable index operations. Below is a Python tool using the DataStax Cassandra Driver (cassandra-driver) to automate cluster connection and index rebuilding. The script:

Before using the script, install the Cassandra Python driver via pip install cassandra-driver and configure network connectivity and authentication for your cluster. If your cluster uses username/password authentication, add authentication support to the code—for example, using PlainTextAuthProvider.

Here is the script source:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Cassandra 二级索引重建工具
"""
import argparse
from cassandra.cluster import Cluster
# 如果需要认证，可启用以下导入并在 Cluster 时加入 auth_provider
# from cassandra.auth import PlainTextAuthProvider

# 解析命令行参数
parser = argparse.ArgumentParser(description="Cassandra index rebuild tool")
parser.add_argument('--hosts', required=True, help="Cassandra contact points (comma separated IP list)")
parser.add_argument('--keyspace', required=True, help="Keyspace name")
parser.add_argument('--table', required=True, help="Table name")
parser.add_argument('--column', required=True, help="Column name to index on")
parser.add_argument('--index', required=True, help="Index name")
parser.add_argument('--test-value', help="Optional test value to verify the index query")
args = parser.parse_args()

contact_points = args.hosts.split(',')
keyspace = args.keyspace
table = args.table
column = args.column
index = args.index
test_value = args.test_value

# 建立集群连接（默认端口9042，如需其它端口可在Cluster参数中指定）
# 如需认证: e.g., auth_provider = PlainTextAuthProvider(username='user', password='pass')
cluster = Cluster(contact_points)
session = cluster.connect(keyspace)
print(f"Connected to cluster at {contact_points}, keyspace {keyspace}")

# 删除已有索引
drop_cql = f"DROP INDEX IF EXISTS {index}"
session.execute(drop_cql)
print(f"Index {index} dropped (if existed).")

# 创建新索引
create_cql = f"CREATE INDEX {index} ON {table} ({column})"
session.execute(create_cql)
print(f"Index {index} created on {table}({column}).")

# 验证索引（如果提供了测试值）
if test_value:
    query = f"SELECT {column} FROM {table} WHERE {column}=%s LIMIT 1"
    rows = session.execute(query, [test_value])
    if rows:
        print(f"Index query successful, found {column} = {test_value}.")
    else:
        print(f"No results for {column} = {test_value}. The value might not exist in table.")
else:
    print("No test value provided for verification, skipping index query.")

# 关闭连接
session.shutdown()
cluster.shutdown()
print("Index rebuild completed.")

The script accepts command-line arguments for the target node, Keyspace, table, column, and index name, then rebuilds. We use DROP INDEX IF EXISTS to remove the index if present, then CREATE INDEX to rebuild. The cassandra-driver waits for schema agreement across the cluster. Note: CREATE INDEX returns immediately, but index rebuild happens asynchronously in the background. The script may complete before rebuild finishes; for large datasets, allow time before expecting complete query results.

With the --test-value parameter, the script queries the indexed column with that value—equivalent to SELECT ... WHERE column = 'value' LIMIT 1—and reports whether results appear. This simply checks whether index queries work. However, the result depends on the value actually existing in the table. If absent or the index is still rebuilding, the query may return nothing.

Example: rebuilding an index with the Python script

To rebuild the index_xxx index described above, where we know the indexed column field_xxx contains test value 'some_value', and a cluster node is at IP 10.x.x.x (anonymized):

$ python cass_rebuild_index.py \
    --hosts 10.x.x.x \
    --keyspace keyspace_xxx \
    --table table_xxx \
    --column field_xxx \
    --index index_xxx \
    --test-value some_value

The script outputs connection info, index deletion and creation progress, and verification results. Example expected output:

Connected to cluster at ['10.x.x.x'], keyspace keyspace_xxx  
Index index_xxx dropped (if existed).  
Index index_xxx created on table_xxx(field_xxx).  
Index query successful, found field_xxx = some_value.  
Index rebuild completed.

The output shows successful rebuild and confirms the test value is queryable. Still, monitor actual cluster rebuild completion. For large indexes, the script may report verification success before rebuild completes if the test value's portion happens to be indexed. In production, confirm rebuild completion through monitoring or logs before resuming traffic.

Conclusion

Secondary index failures in Cassandra require careful maintenance. As shown above, manual rebuilding—while temporarily affecting cluster performance—can effectively restore query correctness and improve write latency. Execute this procedure during low-traffic windows with prepared application shutdown or failover to minimize operational impact.

© 2026 Yuxu Ge ·