Using Apache Phoenix Secondary Indexes Without Slowing Writes

Start with the trade-off, not the syntax

Apache Phoenix is a SQL layer for HBase. HBase stores rows sorted by row key, so a query that filters on any other column normally requires a full table scan: Phoenix reads every row and discards the ones that do not match. A secondary index is a separate HBase table that mirrors a subset of your columns, sorted so Phoenix can seek directly to matching rows. The useful takeaway is that an index speeds up reads but slows down writes, because every insert, update, or delete on the base table also writes to the index table.

Assumptions and terms

This guide assumes Apache Phoenix 5.x or later with an HBase backend. A row key is the primary key under which HBase physically stores a row. Write amplification means one logical write produces multiple physical HBase writes. Cardinality is the number of distinct values in a column; low cardinality means many rows share the same value.

Worked example: create an index and check the plan

Run the following through the Phoenix SQL client, typically sqlline.py, on a host with the Phoenix client jars. The connecting user needs table-level permission to create objects on the target schema.

CREATE INDEX IF NOT EXISTS idx_customers_email
ON customers (email);

Replace customers and email with your table and column. Phoenix creates the index asynchronously, so it may not be usable immediately.

Check whether the index is ready before relying on it:

SELECT index_name, index_status
FROM SYSTEM.INDEXES
WHERE table_name = 'CUSTOMERS';

Look for a status that indicates the index is usable; exact status strings vary by Phoenix version.

Now run a query that filters on the indexed column:

SELECT id, name, email
FROM customers
WHERE email = '[contact removed]';

To see whether Phoenix used the index, run EXPLAIN against the same statement:

EXPLAIN SELECT id, name, email
FROM customers
WHERE email = '[contact removed]';

Read the plan output for an index scan or index range scan rather than a full table scan. Planner statistics, hints, and Phoenix version affect the chosen plan, so do not assume index usage without checking EXPLAIN.

Limits and common mistakes

• Low-cardinality columns. Indexing a column such as status with only a few distinct values often scans almost as many rows as the table, providing little benefit.
• Functions and casts. Wrapping an indexed column in a function, or allowing an implicit type cast, can prevent the planner from selecting the index. Compare WHERE email = 'x' with WHERE LOWER(email) = 'x'; the second form is less likely to use a plain index on email.
• Indexing before bulk load. Creating secondary indexes before a large bulk load slows ingestion because every loaded row also writes to the index. Load the data first, then create the index.
• Write cost. Every indexed column adds HBase writes to inserts, updates, and deletes. Index only the columns your queries actually filter or join on.
• Storage is not reclaimed immediately. Dropping an index removes the index object, but the underlying HBase cells may remain until a major compaction runs.

Practical verification

1. Create the index on a production-like table, not a tiny test table.
2. Run EXPLAIN on representative queries before and after index creation.
3. Compare scan count and latency on the same dataset with and without the index.
4. Query SYSTEM.INDEXES to confirm the index reached a usable state.

Removing an index

If an index does not improve query performance or adds too much write load, drop it:

DROP INDEX IF EXISTS idx_customers_email ON customers;

Schedule a major compaction on the affected HBase region server if you need to reclaim the storage space promptly.

Indexes are a read optimization, not a free performance fix. Verify with EXPLAIN and measure write impact before deploying to a busy production table.