Part 2 of 5 · 1 min read
Why secondary indexes complicate partitioning
A row's position is decided by its primary partitioning key; a secondary index on another column has no such natural position.
Think of a library that shelves books by their catalog number. This is a fine system for finding a specific book. It is unhelpful for a visitor who only knows the book's subject and needs every book on that subject, wherever on the shelves they happen to sit.
In plain words: a row's partition is determined by its primary partitioning key, so looking up a row by that key is efficient, since the query knows exactly which partition to check. A secondary index is built on a different column, one not used for primary partitioning, so rows matching a secondary-index lookup can be scattered across every partition rather than sitting together on one. This forces a choice about how the secondary index itself gets partitioned, which is exactly the choice the next two parts describe.
A worked example. A table of orders partitioned by customer ID stores all of one customer's orders together, making "find this customer's orders" fast. A query for "find all orders placed on March 1st" has no such guarantee, since orders placed that day could belong to any customer and therefore sit on any partition.
The key lines. No new SQL; the need for a secondary index arises from a query pattern, such as WHERE order_date = '2024-03-01', that filters on a column other than the partitioning key.
Secondary index (in a partitioned system)
A secondary index, in a partitioned system, is an index built on a column other than the primary partitioning key, requiring its own partitioning decision since the rows it indexes are not naturally grouped by that column.
Example: an index on order date, when orders are partitioned by customer ID, is a secondary index facing exactly this problem.