Why it matters
Applications with more than one query pattern almost always need secondary access paths. E-commerce needs to find orders by customer, by product, and by date. User management needs to find accounts by email, by phone, and by external ID. Without secondary indexes, HBase either becomes unusable for these access patterns or requires denormalization that turns simple writes into cascading updates.
Getting secondary index maintenance right — especially consistency between primary and index tables — is where most HBase implementations struggle. Understanding the options is the first step.
The architecture
Manual dual writes are the simplest approach. Application code writes to both the primary table and the index table in every operation. Simple but fragile: partial failures can leave the index inconsistent with the data.
Coprocessor-maintained indexes use a RegionObserver on the primary table that automatically writes to the index on every put. This centralizes index maintenance and ensures every write is accompanied by an index update, but does not solve the atomicity problem: primary and index writes are still separate operations.
How it works end to end
Apache Phoenix is the fullest-featured secondary index solution. Phoenix presents HBase as a SQL database, and CREATE INDEX statements automatically maintain secondary indexes. Phoenix uses coprocessors to keep indexes consistent, and supports functional and multi-column indexes.
Reads through a secondary index work as: query the index table by the secondary attribute; retrieve the primary row keys stored there; issue gets against the primary table to fetch the actual rows. Two lookups instead of one, but far faster than a full scan.
Consistency is the hard part. Index maintenance can lag behind data writes; the index can point to rows that no longer exist (stale index); or the data can exist without a matching index entry (missing index). Applications must tolerate some inconsistency or use Phoenix's stronger consistency modes.