In this lesson, you will learn how to bypass costly full-table scans by utilizing secondary indexes to pinpoint data using non-partitioned keys.
The Problem: When Partition Keys Aren't Enough
In distributed databases, we typically organize data using a Partition Key to ensure even distribution across a cluster. When you query by that partition key, the database knows exactly which node holds the data. However, what happens when your application needs to search by an attribute that isn't the partition key—for example, looking up a user_id by their email_address?
Without a secondary structure, the database is forced to perform a "scatter-gather" scan, touching every single node in the cluster to find the matching row. This is the performance killer we want to avoid.
Implementing Secondary Indexes
A secondary index acts as a specialized, separate data structure—essentially a map—that stores the mapping from your non-partitioned attribute to the partition key (or the primary location of the record).
Think of it like the index at the back of a textbook: instead of flipping through every page (the full-table scan) to find a keyword, you look it up in the index to get the exact page number.
In practice, this is implemented as follows:
-- Creating a secondary index on 'email' to support lookups
-- without scanning the entire user table
CREATE INDEX idx_user_email ON users (email);
-- Now, the database uses the index to find the primary key,
-- then performs a direct lookup.
SELECT * FROM users WHERE email = 'dev@example.com';
The Trade-off
While secondary indexes drastically reduce latency for read operations, they introduce a "write penalty." Every time you insert or update a record in the main table, the database must also update the secondary index structure. Furthermore, in distributed systems, these indexes may reside on different nodes than the data itself, potentially introducing extra network hops.
When designing your schema, use secondary indexes strategically: index only the fields that are frequently used in WHERE clauses to keep your write throughput healthy and your storage overhead manageable.
Key Takeaway: Secondary indexes transform expensive, cluster-wide full-table scans into efficient, surgical lookups by mapping non-partitioned attributes directly to your primary data location.