Phoenix Secondary Indexes vs Row-Key Range Scans

The decision: index or row-key range scan?

Apache Phoenix runs SQL against HBase, and every table has a row key—the column or columns that determine where data is physically stored. When a query filters on the row key, Phoenix can go directly to the relevant HBase region server and row range. When a query filters on any other column, the default is a full table scan: every region server reads every row and discards non-matches. That works on small tables or nightly reports, but it becomes expensive at scale.

Useful takeaway: add a secondary index when you repeatedly filter on non-row-key columns and cannot practically redesign the row key. Keep row-key range scans for high-write workloads, time-series data, or filters on low-cardinality columns where an index returns too many rows to help.

Constraints and version assumptions

Phoenix index syntax and support vary across major releases. Behavior in Phoenix 4.x, 5.x, and 6.x is not identical, and some index features depend on the matching HBase version. Before creating indexes in production, check your Phoenix and HBase compatibility matrix and test on a non-production cluster. Treat version-specific claims as unverified until you confirm them against your release notes.

Supported index options

Option	Typical syntax	Best suited for	Main cost
Non-unique index	CREATE INDEX idx ON table (col)	Equality or range filters on a non-row-key column	Extra storage and write amplification on every UPSERT and DELETE
Unique index	CREATE UNIQUE INDEX idx ON table (col)	Enforcing uniqueness on a column outside the row key	Higher write coordination; writes fail on duplicate values
Covered index	CREATE INDEX idx ON table (col) INCLUDE (col2)	Queries that need only the indexed column plus included columns	Larger index; more write amplification; avoids base-table row fetch
Row-key range scan	No index; filter on row-key prefix	High-write tables and sequential or time-series keys	Full table scan if the filter misses the row-key prefix

Trade-offs to weigh

• Reads: indexes turn full scans into index lookups for matching WHERE clauses, which can reduce latency and region-server load.
• Writes: each UPSERT (insert-or-update) or DELETE must update every index on the table. Frequently updated columns are poor index candidates.
• Storage: indexes duplicate data in HBase, increasing HDFS usage and compaction work.
• Uniqueness: unique indexes prevent duplicate writes but add coordination overhead and can reject legitimate-looking loads.
• Query shape: functions, implicit casts, or arithmetic on an indexed column may stop Phoenix from using the index.

Implementation: create an index and verify the plan

Run these statements in SQLLine, the Phoenix SQL client, typically launched with sqlline.py against your Phoenix JDBC endpoint. The running user needs table ownership or index creation privileges on the target schema.

Example: a customers table where analysts often search by email, but the row key is customer_id.

CREATE INDEX idx_customers_email ON customers (email_address);

If queries also need the customer name without touching the base table, a covered index can include it:

CREATE INDEX idx_customers_email_name ON customers (email_address) INCLUDE (full_name);

Before creating the index, capture the execution plan:

EXPLAIN SELECT customer_id, email_address FROM customers WHERE email_address = '[contact removed]';

After the index build completes, run the same EXPLAIN again. A useful result is a plan that references the index instead of scanning the full customer table. Do not copy expected plan text from other environments; compare the before-and-after plans in your own cluster.

Risks during index creation: the build is typically a long-running MapReduce-style job. It can add load to region servers and slow or block table access while it runs. Schedule it during a maintenance window or low-traffic period, and monitor region-server heap and request queues.

Limitations and how to check results

• Do not index columns updated on every write; each update rewrites every index row.
• Unique indexes fail when incoming data contains duplicates.
• Check compatibility before using covered or unique indexes on your Phoenix/HBase combination.
• Measure representative UPSERT latency with and without the index under production-like load before rolling out broadly.

Practical verification: run EXPLAIN on your target query before and after index creation, then compare whether the planner chooses the index. Confirm write impact by timing a batch of UPSERTs against a clone or staging table with the same schema and index definition.

Rollback: remove an index

Because index creation changes table metadata and data, you can reverse it:

DROP INDEX idx_customers_email ON customers;

Run DROP INDEX in SQLLine with the same privileges used to create the index. The drop removes the index data and stops write amplification, but queries that depended on it will revert to full scans until you recreate it or redesign the row key.