Accelerating Phoenix Queries with Secondary Indexes: Creation, Maintenance, and Trade‑offs

Problem: Slow Non‑Primary‑Key Queries in Phoenix

When you query a Phoenix table on a column that isn’t part of the primary key, the engine must scan the entire underlying HBase region for qualifying rows. In a table with millions of rows, this full scan can dominate query latency and consume excessive I/O. The obvious solution is to add a secondary index, but deciding when, how, and what to index requires a clear understanding of the mechanics, the performance impact, and the maintenance overhead.

How Phoenix Secondary Indexes Work

In Phoenix, a secondary index is not a simple B‑tree or hash; it’s an additional HBase table that stores the indexed column values together with the primary key of the base table. The index table is created and maintained by a coprocessor that listens to mutations on the base table.

• Index Table Structure: Each row in the index table contains the indexed column(s) as the row key and the base table’s primary key as a column value. Phoenix automatically generates the necessary column families.
• Asynchronous Population: When you run CREATE INDEX, Phoenix marks the index as BUILDING and starts a background scan to materialize existing rows. New writes to the base table are immediately propagated to the index via the coprocessor.
• Query Optimizer: During query planning, Phoenix checks the predicate. If the predicate matches the indexed column(s) exactly or as a supported range, Phoenix rewrites the plan to perform an INDEX RANGE SCAN against the index table, joining back to the base table only for the required columns.

Creating and Maintaining Indexes

Below is a step‑by‑step guide that shows how to create a secondary index, monitor its status, and keep it healthy.

1. Define the Base Table

CREATE TABLE orders (
    order_id   BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATE,
    total      DECIMAL(10,2)
) WITH (ROW_KEY_FORMAT = 'ROW_KEY');

Replace ROW_KEY with your preferred key format. This table uses order_id as the primary key.

2. Create a Secondary Index on customer_id

CREATE INDEX idx_customer_id ON orders (customer_id);

Run this as a user with CREATE INDEX privileges. Phoenix will immediately mark the index as BUILDING and start populating it in the background.

3. Verify the Index Status

Check the system view SYSTEM.INDEXES to see when the index becomes USABLE:

SELECT INDEX_NAME, STATUS, BUILD_TIME
FROM SYSTEM.INDEXES
WHERE TABLE_NAME = 'ORDERS';

When STATUS = 'USABLE', the index is ready for query use.

4. Use the Index in a Query

Run an EXPLAIN to confirm the optimizer picks the index:

EXPLAIN SELECT * FROM orders WHERE customer_id = 987654321;

The output should show an INDEX RANGE SCAN instead of a TABLE SCAN. If you see a full table scan, the predicate might not match the index exactly (e.g., casting, functions, or unsupported expressions).

5. Monitor and Maintain

• Write Amplification: Every insert, update, or delete on orders triggers an update to idx_customer_id. Monitor write latency with SELECT * FROM SYSTEM.TABLES WHERE TABLE_NAME = 'ORDERS'; and compare WRITE_LATENCY_MS before and after index creation.
• Rebuild on Schema Change: Altering customer_id (e.g., changing its type) sets the index status to UNUSABLE. Recreate the index to restore performance.
• Storage Cost: The index table duplicates primary key data. Use SELECT SUM(COLUMN_FAMILY_SIZE) FROM SYSTEM.TABLES WHERE TABLE_NAME = 'IDX_CUSTOMER_ID'; to estimate growth.

Worked Example: From Scan to Index

Assume you have a table with 5 million rows. You run:

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

Output shows:

TABLE SCAN: ORDERS

After creating the index and waiting for it to be USABLE, rerun the same EXPLAIN:

INDEX RANGE SCAN: IDX_CUSTOMER_ID

Running the actual query should now return results in a fraction of the previous time, while the index table grows by roughly the size of the primary key plus the indexed column.

Trade‑offs & Limitations

Aspect	Impact
Write Throughput	Increases linearly with the number of indexes; consider staggering writes or batch ingestion.
Storage	Index tables duplicate primary key data; monitor disk usage.
Query Coverage	Only predicates that match the indexed columns exactly or via supported ranges benefit.
Complex Expressions	Functions, casts, or multi‑column predicates that don’t align with the index definition prevent usage.
Compatibility	Requires Phoenix 4.4+ and an HBase version that supports coprocessors.

Actionable Take‑away

Use secondary indexes when:

• Queries on a non‑primary column are frequent and selective.
• Write traffic is moderate; you can tolerate the extra write cost.
• You have control over the schema and can keep indexed columns stable.

Steps to adopt:

1. Identify the most selective predicates in your workload.
2. Create a test index and verify EXPLAIN output.
3. Measure write latency and storage overhead in a staging environment.
4. Deploy to production, monitor STATUS and WRITE_LATENCY_MS, and rebuild if schema changes occur.

By following this workflow, you can confidently accelerate Phoenix queries while keeping an eye on the trade‑offs that come with secondary indexes.