Phoenix Secondary Indexes: Choosing Global vs. Local Without Doubling Your Writes

Your Phoenix query filters on a column that is not the primary key, so Phoenix falls back to a full scan and your response time suffers. Adding a secondary index seems obvious, but Phoenix gives you two very different tools—global and local—and the wrong choice either inflates every write or leaves the index unused. The practical takeaway: default to a global index for correctness and flexible queries, and reserve local indexes for write-heavy tables where every query already filters by the row-key prefix.

Secondary indexes are just extra Phoenix tables

A Phoenix secondary index is a separate HBase table that Phoenix maintains for you. Its row key is built from the indexed column value followed by the original primary key, so Phoenix can do a point lookup or range scan against the smaller index table instead of scanning the base table.

The consequence is direct: every write to the base table must also write to each index table. Index three columns and a single insert becomes four physical writes. That is the trade, and it is why index selection should follow your query workload rather than a guess about which columns look important.

Global versus local: consistency on one side, write cost on the other

A global index is updated synchronously with the base table. When the write returns, both tables are consistent. The cost is write latency that grows with the number of indexed columns, because Phoenix waits for every index table to acknowledge the update. In return, your query does not need to know the primary key—you can filter on the indexed column alone.

A local index is scoped to a single HBase region server. Index entries live in the same region as the base rows, lowering coordination and write cost. The catch is that Phoenix can only use a local index when your query includes the row-key prefix that scopes the region. Query by the indexed column alone and Phoenix falls back to a full scan. Local indexes fit predictable, prefix-aware access patterns, not ad-hoc analytics.

Covering indexes: pay storage to skip base-table reads

By default, a secondary index stores only the indexed columns plus the primary key. After Phoenix finds matching keys, it still reads the base table for any other selected columns. A covering index includes additional columns in the index table, so selective queries can be satisfied entirely from the index. This improves read performance at the expense of more storage and slightly higher write amplification. Use covering indexes for hot, selective queries where the extra columns are small and frequently needed.

A worked example you can run in the Phoenix CLI

Run these in a Phoenix sqlline session with permission to create tables and indexes. Replace the table and column names with values from your environment.

CREATE TABLE IF NOT EXISTS my_table (
  pk VARCHAR PRIMARY KEY,
  col1 INTEGER,
  col2 VARCHAR
);

INSERT INTO my_table VALUES ('user1', 42, 'active');
INSERT INTO my_table VALUES ('user2', 99, 'inactive');

CREATE INDEX my_idx ON my_table (col1);

Check the plan:

EXPLAIN SELECT * FROM my_table WHERE col1 = 42;

With the index in place, the plan should switch from a FULL SCAN on my_table to an INDEX SCAN against the index table. To confirm the index is populated, open the HBase shell and scan the index table. Phoenix typically names it MY_TABLE_IDX, but verify the exact name in SYSTEM.CATALOG if unsure:

# Run in the HBase shell, not Phoenix
scan 'MY_TABLE_IDX'

You should see rows that mirror the indexed column value and the original primary key. If the scan returns nothing, the index has not been populated or the write path is failing.

Now compare with a local index:

CREATE LOCAL INDEX my_local_idx ON my_table (col1);
EXPLAIN SELECT * FROM my_table WHERE col1 = 42;

Because the query lacks the primary-key prefix, Phoenix is unlikely to choose the local index. Add the prefix and the optimizer can route the scan to the correct region.

Write amplification, storage, and version-specific DDL

Every secondary index multiplies storage and increases write amplification—the ratio of physical HBase writes to logical application writes. A table with five indexes turns one insert into six writes. On a write-heavy workload, that overhead can dominate cluster I/O long before it helps reads.

Index metadata changes carry their own risk. In Phoenix versions prior to 5.0, dropping or altering an index requires the base table to be offline. Newer versions support online index maintenance, but with limitations that vary by release. Before running index DDL on a production table, confirm your Phoenix version and test on a staging cluster. If you need to revert, dropping the index is the rollback path, but that too may require downtime on older versions.

How to decide

Start with the query workload, not the schema. If your application needs strong consistency and filters on non-primary-key columns without knowing the row-key prefix, create a global index. Add covering columns only for the hottest queries. If your access pattern always includes the row-key prefix and write latency is the bottleneck, evaluate a local index. In every case, run EXPLAIN before and after, monitor write latency and HBase region sizes, and keep the number of indexes as small as your workload allows.