Using Apache Phoenix Secondary Indexes for Low‑Latency Queries: Architecture Note

Requirements

The workload needs fast point lookups and range scans on columns that are not part of the primary key. Write throughput must stay within acceptable limits, and the solution should not compromise the strong consistency guarantees Phoenix provides for single‑row transactions.

Smallest Suitable Design

Create a single‑column secondary index on the target non‑key column using Phoenix’s default immutable index type. This adds a separate HBase table that stores the indexed column value as the row key and the original table’s row key as the column value. The index is maintained automatically by Phoenix.

Example: adding an index

-- Run in the Phoenix query client (sqlline.py) as a user with CREATE/ALTER privileges
CREATE TABLE IF NOT EXISTS my_schema.orders (
    order_id VARCHAR PRIMARY KEY,
    customer_id VARCHAR,
    order_date DATE,
    total_amount DECIMAL
);

-- Add a secondary index on customer_id
CREATE INDEX idx_orders_customer ON my_schema.orders (customer_id);

After the command returns, Phoenix begins building the index in the background.

Trust/Data Boundaries

The index table lives in the same HBase cluster as the base table but is stored as a distinct HBase table (e.g., MY_SCHEMA.ORDERS_IDX_...). Phoenix guarantees transactional consistency between the base table and its index for mutations that affect a single row. Updates that span multiple rows are propagated to the index eventually, which means a read that follows a cross‑row write may temporarily see stale index data.

Operational Checks

• Monitor build status: SELECT INDEX_NAME, STATUS FROM SYSTEM.CATALOG WHERE TABLE_NAME = 'ORDERS'; Look for BUILT; a FAILED status indicates a problem.
• Verify usage: run EXPLAIN SELECT * FROM my_schema.orders WHERE customer_id = 'C123'; and confirm the plan shows an INDEX SCAN on idx_orders_customer.
• Observe write load: in the HBase UI or via hbase org.apache.hadoop.hbase.tool.LoadTestTool, watch the region server metrics for the index table during bulk loads; increased write traffic is expected.
• Check size and splits: the HBase master UI shows the index table’s region count and split patterns; excessive splits may signal hotspotting.

Failure Modes

• Build failures: If the index creation encounters an error (e.g., insufficient disk space), the index remains in BUILDING or FAILED state. Queries fall back to full table scans, and manual intervention (dropping and recreating the index) is required.
• Corrupt index files: HDFS corruption in the index table’s store files can cause query exceptions such as ScannerTimeoutException. Running hbase hbck on the index table helps detect missing or corrupted regions.
• Write‑induced hotspots: Heavy write workloads generate many updates to the index table, potentially overloading specific region servers. This manifests as elevated latency for both writes and index‑based reads.

Conditions That Would Change the Design

• If write throughput exceeds the index table’s capacity, consider disabling the index (DROP INDEX) or using a salted index (CREATE INDEX ... SALTED BUCKETS = 16) to distribute load.
• When strong consistency across rows is required (e.g., financial ledgers where a read must see all prior writes), avoid secondary indexes and rely on Phoenix transactions or application‑level locking.
• Schema changes that drop or rename the indexed column force index recreation; any ALTER TABLE that modifies the indexed column must be followed by DROP INDEX and CREATE INDEX.
• Older Phoenix releases (<= 4.7) do not support indexing of array or binary blob columns; verify data type compatibility before creating the index.