Creating Apache Phoenix Secondary Indexes to Accelerate HBase Queries

Problem and Takeaway

Queries that filter on non‑key columns in an Apache Phoenix table trigger full table scans on HBase, causing high latency. Creating a secondary index on the filtered column lets Phoenix serve the query with an index scan instead, reducing response time for selective predicates.

Prerequisites

• An Apache HBase cluster (2.x) running with the Phoenix coprocessor deployed.
• Phoenix 5.x client (sqlline or JDBC) compatible with the HBase version.
• Sufficient region‑server CPU, memory, and disk for the index build phase.
• Database privileges to create tables and indexes on the target schema.

Procedure

1. Verify the Target Table Exists

Connect to Phoenix and confirm the table you want to index is present.

!connect jdbc:phoenix::2181:/hbase-unsecure

Run:

SELECT * FROM SYSTEM.CATALOG WHERE TABLE_NAME = 'USERS';

2. Create the Secondary Index

Issue a DDL statement that names the index, the table, and the column(s) to index. Use the INCLUDE clause to add columns that should be stored in the index for covering queries (avoiding a lookup back to the data table).

CREATE INDEX IDX_USERS_EMAIL ON USERS (EMAIL) INCLUDE (LAST_LOGIN);

Run this command from the same sqlline session or any Phoenix client with DDL permissions.

3. Wait for the Index Build to Complete

The build runs asynchronously. Monitor the SYSTEM.INDEX until the status changes to BUILT.

SELECT INDEX_NAME, TABLE_NAME, STATUS FROM SYSTEM.INDEX WHERE TABLE_NAME = 'USERS' AND INDEX_NAME = 'IDX_USERS_EMAIL';

Expect output showing BUILDING initially, then BUILT. Large tables may take minutes to hours; schedule builds during low‑traffic windows.

4. Verify Query Plan Uses the Index

Run EXPLAIN on a representative query that filters on the indexed column.

EXPLAIN SELECT ID, LAST_LOGIN FROM USERS WHERE EMAIL = '[contact removed]';

Look for INDEX SCAN in the plan output. If you see FULL SCAN, the optimizer did not choose the index—check statistics or hint usage.

Expected Checks

• Plan verification: EXPLAIN shows INDEX SCAN with the correct index name.
• Status confirmation: SYSTEM.INDEX row for the index reports STATUS = 'BUILT'.
• Latency comparison: Benchmark the same query before and after index creation (e.g., 100 runs with a tool like phoenix-perf or a custom script). A sustained reduction in average response time confirms effectiveness.
• Cluster health: Watch region‑server CPU and disk I/O during the build; excessive load may require throttling or pausing the build.

Recovery Options

Failed Index Build

If the index remains in BUILDING or shows ERROR, drop it and investigate region‑server logs.

DROP INDEX IDX_USERS_EMAIL ON USERS;

Query Performance Degradation After Index Creation

Write overhead from maintaining the index can slow ingest. Temporarily disable the index without dropping it:

ALTER INDEX IDX_USERS_EMAIL ON USERS UNUSABLE;

Rebuild later with ALTER INDEX ... REBUILD after schema changes or data corrections.

Pre‑DDL Safety Net

Before any major DDL, take an HBase snapshot of the table:

hbase shell> snapshot 'USERS', 'USERS_PRE_INDEX_SNAPSHOT'

Restore with restore_snapshot if the operation corrupts metadata.

Limitations and Trade‑offs

• Each insert, update, or delete on USERS now writes to both the data table and the index table, increasing write latency and disk usage.
• Functional indexes (expressions) and partial indexes are not supported in Phoenix 5.x; only column‑level indexes are available.
• Index builds are not transactional; a failure leaves the index in an unusable state requiring manual cleanup.

Practical Verification Checklist

1. Run EXPLAIN SELECT ... WHERE EMAIL = '...' and confirm INDEX SCAN appears.
2. Query SYSTEM.INDEX and verify STATUS = 'BUILT'.
3. Execute a latency benchmark (e.g., 100 iterations) before and after; record median and 95th‑percentile times.
4. Monitor region‑server metrics during the build for abnormal spikes.