Amazon Webservice Blog just announce a new feature, you can now create local secondary indexes for Amazon DynamoDB tables. These indexes provide give you the power to query your tables in new ways, and can also increase retrieval efficiency.
What’s a Local Secondary Index?
The local secondary index model builds on DynamoDB’s existing key model.
Up until today you would have to select one of the following two primary key options when you create a table:
- Hash – A strongly typed (string, number, or binary) value that uniquely identifies each item in a particular table. DynamoDB allows you to retrieve items by their hash keys.
- Hash + Range – A pair of strongly typed items that collectively form a unique identifier for each item in a particular table. DynamoDB supports range queries that allow you to retrieve some or all of the items that match the hash portion of the primary key.
With today’s release we are extending the Hash + Range option with support for up to five local secondary indexes per table. Like the primary key, the indexes must be defined when you create the table. Each index references a non-primary attribute, and enables efficient retrieval using a combination of the hash key and the specified secondary key.
You can also choose to project some or all of the table’s other attributes into a secondary index. DynamoDB will automatically retrieve attribute values from the table or from the index as required. Projecting a particular attribute will improve retrieval speed and lessen the amount of provisioned throughput consumed, but will require additional storage space. Items within a secondary index are stored physically close to each other and in sorted order for fast query performance.
Show Me an Example!
Let’s say that you need to access a table containing information about heads of state. You create a table like this, with Country as the hash key and PresNumber as the range key:
|United States||1||George Washington||John Adams||None||57||6.34|
|United States||2||John Adams||Thomas Jefferson||Federalist||61||4|
|United States||3||Thomas Jefferson||Aaron Burr||Democratic-Republican||57||8|
|United States||4||James Madison||George Clinton||Democratic-Republican||57||8|
|United States||5||James Monroe||Daniel Tompkins||Democratic-Republican||58||8|
With this schema, you can retrieve heads of state using a country and the ordinal number for the presidency. Now, let’s say that you want to query by Age (upon taking office) as well. You would create the table with a local secondary index (Age) like this:
|United States||57||1||George Washington||John Adams||None||6.34|
|United States||61||2||John Adams||Thomas Jefferson||Federalist||4|
|United States||57||3||Thomas Jefferson||Aaron Burr||Democratic-Republican||8|
|United States||57||4||James Madison||George Clinton||Democratic-Republican||8|
|United States||58||5||James Monroe||Daniel Tompkins||Democratic-Republican||8|
How Do I Create and Use a Local Secondary Index?
As I noted earlier, you must create your local secondary indexes when you create the DynamoDB table. Here is how you would create them in the AWS Management Console:
DynamoDB’s existing Query API now supports the use of local secondary indexes. Your call must specify the table, the name of the index, the attributes you want to be returned, and any query conditions that you want to apply. We have examples in Java, PHP, and .NET / C#.
Costs and Provisioned Throughput
Let’s talk about the implications of local secondary indexes on the DynamoDB cost structure.
Every secondary index means more work for DynamoDB. When you add, delete, or replace items in a table that has local secondary indexes, DynamoDB will use additional write capacity units to update the relevant indexes.
When you query a table that has one or more local secondary indexes, you need to consider two distinct cases:
For queries that use index keys and projected attributes, DynamoDB will read from the index instead of from the table and will compute the number of read capacity units accordingly. This can result in lower costs if there are less attributes in the index than in the table.
For index queries that read non-projected attributes, DynamoDB will need to read the table and the index. This will consume additional read capacity units.