Selecting Secondary Index Types in Apache Phoenix: Global vs. Local

The Performance Trade-off in HBase Indexing

When querying Apache Phoenix tables on columns that are not part of the primary key, the system defaults to a full table scan. While secondary indexes eliminate these scans, they introduce a critical architectural decision: whether to distribute the index globally or keep it local to the data region. The wrong choice can either bottleneck your write throughput or lead to "scatter-gather" read patterns that degrade latency.

Decision Constraints

Your choice depends on three primary constraints:

• Write Amplification: Every index update is an additional write operation. High-frequency updates to indexed columns increase the load on the HBase cluster.
• Read Latency: The number of region servers contacted to satisfy a query directly impacts response time.
• Data Distribution: Whether your queries typically filter by a specific region key or search across the entire dataset.

Comparison of Index Types

Feature	Global Secondary Index (GSI)	Local Secondary Index (LSI)
Storage	Stored in a separate index table	Stored within the same region as the data
Write Impact	Higher; requires cross-region updates	Lower; updates remain local to the region
Read Pattern	Fast point lookups across the whole table	Fast when combined with the primary key/region
Typical Use Case	Queries filtering on non-key columns only	Queries filtering on both region key and index column

Architectural Trade-offs

A Global Secondary Index creates a dedicated index table. When you query an indexed column, Phoenix hits the index table first to find the primary keys, then fetches the full rows from the data table. This is highly efficient for point lookups but creates a distributed transaction overhead during writes, as the index entry may reside on a different region server than the data row.

A Local Secondary Index stores the index entries in the same region as the corresponding data row. This eliminates cross-region write coordination, significantly reducing write latency. However, if a query does not include the primary key (or the region-limiting part of the key), Phoenix must scan the local index on every region in the table, which can be slower than a GSI for large clusters.

Implementation and Validation

To implement a secondary index, use the CREATE INDEX command. By default, Phoenix creates global indexes.

Example: Creating a Global Index
Run this command in the Phoenix Query Server (sqlline) with administrative permissions:

CREATE INDEX idx_user_status ON users (status);

Validating Index Usage
To confirm the optimizer is utilizing the index rather than performing a full table scan, use the EXPLAIN keyword before your query:

EXPLAIN SELECT * FROM users WHERE status = 'ACTIVE';

In the resulting execution plan, look for the INDEX SCAN operator. If you see FULL SCAN, the optimizer has determined the index is not the most efficient path, or the index was not created successfully.

Limitations and Practical Checks

• Uniqueness: Global indexes can support unique constraints (via CREATE UNIQUE INDEX), whereas local indexes cannot enforce uniqueness across different regions.
• Version Dependencies: Index behavior and asynchronous update options vary by Phoenix and HBase versions. Users should verify their specific version's documentation regarding index.async properties to manage write blocking.
• Resource Monitoring: Monitor the HBase RegionServer logs for warnings related to index update failures or timeouts, which typically indicate that write amplification is exceeding the cluster's I/O capacity.

Rollback

If an index causes excessive write latency or consumes too much storage, it can be removed without affecting the underlying data table:

DROP INDEX idx_user_status ON users;

After dropping the index, re-run the EXPLAIN command to verify that the query plan has reverted to a FULL SCAN.