AWS News Blog

Local Secondary Indexes for Amazon DynamoDB

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:

Country PresNumber Name VP Party Age YearsInOffice
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:

Country Age PresNumber Name VP Party YearsInOffice
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.

Get Started Now
Today, local secondary indexes must be defined at the time you create your DynamoDB tables.  In the future, we plan to provide you with an ability to add or drop them for existing tables.  If you want to equip an existing DynamoDB table to local secondary indexes immediately, you can export the data from your existing table using Elastic Map Reduce, and import it to a new table with local secondary indexes defined.

Local secondary indexes are available today in the US East (Northern Virginia), US West (Oregon and Northern California), South America (So Paulo), Europe (Ireland), and Asia Pacific (Singapore, Tokyo, and Sydney) Regions and you can start using them today.

— Jeff;

 

Jeff Barr

Jeff Barr

Jeff Barr is Chief Evangelist for AWS. He started this blog in 2004 and has been writing posts just about non-stop ever since.