Creating and Validating Apache Phoenix Secondary Indexes on HBase Tables

Desired outcome

Create a secondary index on a Phoenix‑mapped HBase table so that queries filtering on the indexed columns use an index scan instead of a full table scan, reducing latency for point lookups and range scans.

Prerequisites

• A running HBase cluster (version compatible with Phoenix 5.x).
• Apache Phoenix 5.x installed and configured; the phoenix-5.x.jar on the classpath.
• A Phoenix‑mapped table that already contains data (e.g., MY_SCHEMA.USERS).
• A user with the table owner role or SUPERUSER privilege, allowing DDL on the table.
• Access to the Phoenix query client (typically sqlline.py) or JDBC/ODBC connection.

Procedure

1. Identify columns to index. Choose columns that appear frequently in WHERE clauses, join predicates, or order‑by lists. For this guide we index the EMAIL column of MY_SCHEMA.USERS.

2. Start the Phoenix client. Run the command on a host that can reach HBase ZooKeeper:

   # Replace <zookeeper-quorum> with your actual quorum, e.g., zk1,zk2,zk3:2181
   ./sqlline.py <zookeeper-quorum>

   You should see the Phoenix 0: jdbc:phoenix> prompt.

3. Create the index. Execute a CREATE INDEX statement. The example creates a GLOBAL index (default) with optional salt buckets to improve write distribution:

   CREATE INDEX IDX_USERS_EMAIL
   ON MY_SCHEMA.USERS (EMAIL)
   INDEX_TYPE GLOBAL
   SALT_BUCKETS 8;

   Required permissions: the executing user must be the owner of MY_SCHEMA.USERS or have SUPERUSER.

4. Wait for the index build to finish. Phoenix builds the index asynchronously. Monitor progress via the system table:

   SELECT INDEX_NAME, STATUS, ROW_COUNT
   FROM SYSTEM.INDEXES
   WHERE INDEX_NAME = 'IDX_USERS_EMAIL';

   Initially STATUS shows BUILDING. When the build completes, it changes to READY. The time required depends on table size and cluster load; you can re‑run the query periodically.

5. Verify the index is used. Run an EXPLAIN on a representative query:

   EXPLAIN SELECT * FROM MY_SCHEMA.USERS WHERE EMAIL = 'alice@example.com';

   Look for the string INDEX SCAN in the output plan. If the plan shows a full table scan (TABLE SCAN), the index is not being used—check that the indexed column appears exactly as in the index definition and that no functions wrap it.

6. Measure performance (optional but recommended). Before creating the index, time a query:

   !time SELECT COUNT(*) FROM MY_SCHEMA.USERS WHERE EMAIL = 'alice@example.com';

   Record the elapsed time. After the index is READY, run the same query again and compare. A noticeable reduction in latency indicates the index is effective.

Expected checks

• SYSTEM.INDEXES entry for IDX_USERS_EMAIL with STATUS = 'READY'.
• EXPLAIN output contains INDEX SCAN for queries on the indexed column.
• Query latency after index creation is lower than the baseline measurement.

Limitations and considerations

• Secondary indexes store a copy of the indexed columns (plus the primary key) in separate index tables, increasing storage usage.
• Each write (INSERT, UPSERT, UPDATE, DELETE) must also update the index, which can slow down write throughput.
• Index type matters: GLOBAL indexes are usable for cross‑region scans but require more coordination; LOCAL indexes are scoped to a single region and may not be chosen for queries that span multiple regions.
• The SALT_BUCKETS option behaved differently between Phoenix 4.x and 5.x. Using an unsupported value on an older version will cause the CREATE INDEX statement to fail with a syntax error.

Recovery options

If the index build fails (e.g., due to insufficient resources or a typo in the DDL), you can drop the problematic index and retry:

DROP INDEX IDX_USERS_EMAIL;

After dropping, verify that the entry disappears from SYSTEM.INDEXES, then re‑issue the CREATE INDEX statement with corrected parameters.

If an index becomes corrupted (rare, but possible after a cluster failure), the same drop‑and‑recreate approach works. Phoenix 5.x also provides an ALTER INDEX … REBUILD command, but dropping and recreating is simpler and equally effective.

Practical way to confirm success

1. Run the SYSTEM.INDEXES query and ensure STATUS = 'READY'.
2. Execute EXPLAIN on a query that filters the indexed column; confirm the plan shows INDEX SCAN.
3. Optionally, run a latency benchmark before and after index creation to observe the performance impact.