Choosing Between Global and Local Secondary Indexes in Apache Phoenix

The problem: slow reads after adding indexes

You have a Phoenix table that stores time‑series sensor data. Queries that filter on the sensor‑id column are getting slower as the table grows, and you suspect the secondary index you added is causing write‑path overhead. You need to decide whether a global or a local index will give you the read speed you want without hurting ingest performance.

Thesis

For workloads where writes are frequent and queries usually include the table’s row‑key prefix, a local index reduces write amplification and keeps bulk loads fast. When queries are ad‑hoc, read‑heavy, and filter on arbitrary columns, a global index provides point‑lookup speed at the cost of extra write traffic and storage.

Understanding the two index types

Global indexes

A global index is stored as its own HBase table. Every indexed column gets a full copy of the indexed values, so point lookups on that column are fast regardless of the row key. The trade‑off is that each insert, update, or delete must be replicated to every index table, increasing write latency and storage usage.

Local indexes

A local index lives in the same HBase region as the data table, sharing the row‑key prefix. It can only speed up queries that include that prefix (often the leading part of the row key). Because the index data is co‑located, writes do not need to be sent to a separate table, which reduces write amplification and improves bulk‑load throughput.

Worked example: comparing index usage

Assume a table SENSOR_DATA with a composite row key (REGION, SENSOR_ID, TIMESTAMP) and a column VALUE. We will add both a global index on VALUE and a local index on VALUE, then examine the query plan for two different queries.

-- Create the base table
CREATE TABLE SENSOR_DATA (
  REGION VARCHAR NOT NULL,
  SENSOR_ID VARCHAR NOT NULL,
  TIMESTAMP TIMESTAMP NOT NULL,
  VALUE DOUBLE,
  CONSTRAINT pk PRIMARY KEY (REGION, SENSOR_ID, TIMESTAMP)
);

-- Global index on VALUE
CREATE INDEX IDX_VALUE_GLOBAL ON SENSOR_DATA (VALUE);

-- Local index on VALUE (requires the row‑key prefix)
CREATE INDEX IDX_VALUE_LOCAL ON SENSOR_DATA (VALUE) LOCAL;

To see which index Phoenix chooses, run EXPLAIN on the queries. Note: the output below illustrates the format; you must run the commands in your environment to see the actual plan.

EXPLAIN SELECT * FROM SENSOR_DATA WHERE VALUE = 12.5;

Expected pattern: the plan shows a lookup on IDX_VALUE_GLOBAL because the query does not include the row‑key prefix, so the local index cannot be used.

EXPLAIN SELECT * FROM SENSOR_DATA WHERE REGION = 'US-West' AND SENSOR_ID = 'S123' AND VALUE = 12.5;

Expected pattern: the plan shows a lookup on IDX_VALUE_LOCAL (or a range scan that uses the local index) because the query supplies the prefix REGION, SENSOR_ID.

You can verify index usage by checking the EXPLAIN output for the index name in the scan step.

Trade‑offs and limitations

• Storage: Each global index duplicates the indexed column values in a separate HBase table, potentially doubling storage for high‑cardinality columns. Local indexes add only a modest overhead because they share the region’s existing storage.
• Write overhead: Global indexes impose write amplification proportional to the number of indexed columns. Local indexes add minimal write cost because the index update happens within the same region.
• Query flexibility: Local indexes only help when the query includes the row‑key prefix. If your access patterns frequently filter on non‑prefix columns, a local index will be ignored and you will see no performance gain.

Practical way to check the result: after creating an index, run a representative write workload (e.g., 10 k synchronous inserts) and measure average latency. Then drop the index and repeat the test. Compare the numbers to see the overhead introduced by each index type.

Actionable closing

1. Profile your workload: identify the percentage of writes vs. reads and the columns used in query filters.
2. If writes dominate and filters usually contain the row‑key prefix, start with a local index on those columns.
3. If reads are ad‑hoc, read‑heavy, and you need fast lookups on arbitrary columns, add a global index—but monitor region server compaction and storage growth.
4. Use EXPLAIN to confirm the chosen index for critical queries, and adjust or drop indexes based on the observed plan.

Remember that index creation is online but can temporarily increase region‑server load; watch for splits and compactions during the build phase, and consider scheduling the operation during a maintenance window.