Choosing Between Phoenix Secondary Indexes and Custom Coprocessors for Point‑Read Latency

Decision Overview

When optimizing point‑read latency in a write‑heavy Phoenix workload you must decide whether to add a secondary index, rely on base‑table scans, or deploy a custom coprocessor.

Decision and Constraints

Adopt a secondary index when:

• Read latency for equality or range predicates is critical.
• Write overhead introduced by index maintenance stays below ~20% of baseline latency.
• Phoenix version is 4.7 or newer and the cluster has enough region capacity to store the duplicate index data.

If write overhead cannot be tolerated, consider a base‑table scan (no extra storage) or a custom coprocessor that computes the filter without duplicating data.

Comparison Table

Option	Read latency (point)	Write overhead	Storage overhead	Implementation complexity
Secondary index	O(log N) via index scan	Additional index‑update latency (≈10‑20% typical)	Duplicates indexed column values + INCLUDE columns	Low – declarative SQL
Base‑table scan	O(N) full scan (or existing row key scan)	None	None	Low – no extra objects
Custom coprocessor	Depends on implementation; can approach O(log N) if designed	Coprocessor CPU cost on RegionServer (variable)	None (uses existing columns)	High – write, package, deploy, version‑match

Trade‑offs

Secondary indexes give automatic, transparent read acceleration because Phoenix maintains the index alongside the table. The downside is write amplification: each insert/update/delete must also modify the index, increasing latency and storage consumption. Frequent writes can trigger more region splits, affecting cluster stability.

Custom coprocessors avoid duplicate storage by executing user‑supplied code at scan time. They require you to bundle a JAR, copy it to every RegionServer’s lib directory, and register it via hbase‑site.xml. Mismatched Phoenix and coprocessor versions can cause class‑path failures, and any change to the coprocessor demands a rolling restart.

Base‑table scans are the simplest fallback but only acceptable when the table is small or the predicate can be satisfied by the row key.

Implementation Example

Assume a table MY_SCHEMA.MY_TABLE with columns FLAG (varchar) and DATA (varchar). To speed up queries that filter on FLAG = 'ACTIVE':

1. Open the Phoenix SQL client (e.g., sqlline.py localhost:2181) with a user that has schema‑alter privileges.
2. Create the index:

CREATE INDEX IDX_FLAG ON MY_SCHEMA.MY_TABLE (FLAG) INCLUDE (DATA);

Phoenix will back‑fill the index; monitor SYSTEM.INDEXES until status shows BUILT.

Run a point‑read query:

SELECT * FROM MY_SCHEMA.MY_TABLE WHERE FLAG = 'ACTIVE';

Validate that the optimizer chose the index:

EXPLAIN SELECT * FROM MY_SCHEMA.MY_TABLE WHERE FLAG = 'ACTIVE';

Look for a plan line containing INDEX RANGE SCAN (or INDEX ONLY SCAN) indicating index usage.

Validation and Monitoring

• Check index status: SELECT INDEX_NAME, STATUS FROM SYSTEM.INDEXES WHERE TABLE_NAME = 'MY_TABLE'; ensure STATUS = BUILT.
• Measure latency: run a loop of 1 000 identical point queries before and after index creation, record average response time; expect a reduction proportional to the selectivity of FLAG.
• Monitor write‑path overhead: watch the HBase metric indexUpdateLatency (or equivalent) to confirm the added latency stays under the 20% threshold.
• After any schema change (e.g., adding a column), rebuild the index: ALTER INDEX IDX_FLAG ON MY_SCHEMA.MY_TABLE REBUILD; or drop and recreate.

Limitations

The index stores a copy of the FLAG (and any INCLUDE columns) for every row, increasing storage size proportionally to the cardinality of the indexed set. High write rates can lead to more frequent region splits and longer compaction cycles. If the write overhead exceeds acceptable limits, the index may degrade overall throughput, making a coprocessor or scan preferable.