Amazon DynamoDB Explained for Beginners

Amazon DynamoDB is the database behind a great many shopping carts, user sessions, game states and device registries. AWS describes it as a serverless, fully managed, distributed NoSQL database with single-digit millisecond performance at any scale, purpose-built for operational workloads (DynamoDB docs: what is DynamoDB).

It achieves that by removing most of what a relational database offers. There are no joins, no ad hoc SQL across tables, and no query planner to rescue a careless request. In exchange, a request that uses the key takes the same short time whether the table holds a thousand items or a billion. Learning DynamoDB is mostly learning to work within that bargain.

Tables, items, attributes

• A table is a collection of items.
• An item is a group of attributes, comparable to a row.
• An attribute is a named value, comparable to a column, and can be nested.

Apart from the primary key, a table is schemaless: neither the attributes nor their types are defined beforehand, and each item can have different ones (DynamoDB docs: core components).

The primary key decides everything

A table's primary key takes one of two forms:

• Partition key only. DynamoDB hashes the value to decide which partition stores the item. No two items share a key.
• Partition key and sort key. Items with the same partition key are stored together, in sorted order by sort key. Many items can share a partition key if their sort keys differ.

The second form is where the power is. Give DynamoDB a partition key and it goes straight to one group of items; add a condition on the sort key and it reads only a slice of that group.

Query and Scan, on real data

To see the consequences, we loaded the 412 invoices from the Chinook sample database into a table, with the customer as partition key and the invoice date and number as sort key, then asked four questions. The code runs against moto, a local emulator of the AWS API, so it shows what DynamoDB examines and returns, not real latency or cost.

import os, sqlite3
from decimal import Decimal
import boto3
from boto3.dynamodb.conditions import Attr, Key
from moto import mock_aws

os.environ.update(AWS_ACCESS_KEY_ID="testing", AWS_SECRET_ACCESS_KEY="testing",
                  AWS_DEFAULT_REGION="us-east-1")

rows = sqlite3.connect("chinook.sqlite").execute(
    "SELECT InvoiceId, CustomerId, InvoiceDate, BillingCountry, Total FROM Invoice").fetchall()


@mock_aws
def main():
    db = boto3.resource("dynamodb")
    table = db.create_table(
        TableName="Invoices",
        BillingMode="PAY_PER_REQUEST",
        KeySchema=[{"AttributeName": "pk", "KeyType": "HASH"},      # partition key
                   {"AttributeName": "sk", "KeyType": "RANGE"}],    # sort key
        AttributeDefinitions=[{"AttributeName": "pk", "AttributeType": "S"},
                              {"AttributeName": "sk", "AttributeType": "S"},
                              {"AttributeName": "country", "AttributeType": "S"}],
        GlobalSecondaryIndexes=[{
            "IndexName": "by_country",
            "KeySchema": [{"AttributeName": "country", "KeyType": "HASH"},
                          {"AttributeName": "sk", "KeyType": "RANGE"}],
            "Projection": {"ProjectionType": "ALL"}}],
    )
    with table.batch_writer() as batch:
        for invoice_id, customer_id, date, country, total in rows:
            batch.put_item(Item={"pk": f"CUSTOMER#{customer_id}",
                                 "sk": f"{date[:10]}#{invoice_id}",
                                 "country": country,
                                 "total": Decimal(str(total))})
    print("items written:", len(rows))

    def report(label, r):
        print(f"{label:46} returned {r['Count']:3}  examined {r['ScannedCount']:3}")

    # 1. Everything for one customer: the partition key alone
    r = table.query(KeyConditionExpression=Key("pk").eq("CUSTOMER#12"))
    report("Query  pk = CUSTOMER#12", r)
    print("   first and last sort keys:", r["Items"][0]["sk"], "...", r["Items"][-1]["sk"])

    # 2. One customer, one year: partition key plus a range on the sort key
    r = table.query(KeyConditionExpression=Key("pk").eq("CUSTOMER#12") & Key("sk").begins_with("2013"))
    report("Query  pk = CUSTOMER#12, sk begins with 2013", r)

    # 3. A question the key was not designed for: all invoices billed to Germany
    r = table.scan(FilterExpression=Attr("country").eq("Germany"))
    report("Scan   filter country = Germany", r)

    # 4. The same question through a global secondary index
    r = table.query(IndexName="by_country", KeyConditionExpression=Key("country").eq("Germany"))
    report("Query  index by_country, country = Germany", r)


main()

items written: 412
Query  pk = CUSTOMER#12                        returned   7  examined   7
   first and last sort keys: 2009-05-23#34 ... 2013-10-05#395
Query  pk = CUSTOMER#12, sk begins with 2013   returned   3  examined   3
Scan   filter country = Germany                returned  28  examined 412
Query  index by_country, country = Germany     returned  28  examined  28

Read the right-hand column, the items examined.

1. Query by partition key. Customer 12's seven invoices: seven items examined, returned in date order because of the sort key.
2. Query with a sort key condition. Their invoices from 2013: three examined, three returned. The date prefix in the sort key makes a time range a key lookup.
3. Scan with a filter. Invoices billed to Germany: 28 returned, but all 412 items examined. The filter is applied after reading. On a real table of a hundred million items the request would read a hundred million items.
4. Query on an index. The same question through a global secondary index keyed on country: 28 examined, 28 returned.

The guide says the same in words: a Scan always scans the entire table or index and then filters out values, it slows as the table grows, and tables and indexes should be designed so that applications can use Query instead (DynamoDB docs: query and scan best practices).

Design from the questions

In a relational database you model the data, then write whatever queries you need. In DynamoDB the order is reversed: list the questions the application will ask, the access patterns, and design keys so each is a Query.

For the invoice table the patterns were "a customer's invoices" and "a customer's invoices in a period", so customer and date went into the key. "Invoices by country" was a third pattern, and needed an index.

Two more design points:

• Spread the load. The guide's advice is to design for uniform activity across all partition keys. A partition key with few values, or one that is far busier than the rest, concentrates traffic on one partition.
• Composite sort keys. Putting several facts into the sort key, as date#invoice does here, lets one table answer several range questions.

Secondary indexes

An index lets you query by an alternate key. There are two kinds:

Each index is a second copy of the chosen attributes that DynamoDB maintains on every write, so each one adds to write cost. Add indexes for real access patterns, not in case they are useful.

Capacity and consistency

Capacity modes. On-demand mode is pay-per-request with no capacity planning, and AWS describes it as the default and recommended option for most workloads. Provisioned mode has you set read and write capacity, which can cost less for steady, predictable traffic (DynamoDB docs: throughput capacity).

Read consistency. Reads are eventually consistent by default: a read just after a write might not reflect it. Tables and local secondary indexes also offer strongly consistent reads, at twice the cost; global secondary indexes are always eventually consistent (DynamoDB docs: read consistency).

When to use it, and when not

DynamoDB is an operational database. It is the transactional side of OLTP vs OLAP, and it is not where analysis should happen.

Getting data out for analytics

Data engineers usually meet DynamoDB as a source. The routes that avoid scanning the live table:

• Export to S3. A managed export writes full or incremental table data to a bucket without consuming read capacity or affecting the table, ready for Athena, Glue or EMR. It needs point-in-time recovery enabled (DynamoDB docs: export to S3).
• DynamoDB Streams. A time-ordered log of item-level changes, kept for up to 24 hours, which a Lambda function or pipeline can read: the service's form of change data capture.
• Federated query. Athena can read a table through a connector for occasional questions; see Athena federated queries.

Beginner mistakes

• Designing tables like relational tables, one per entity, then needing joins.
• Using Scan with filters as the normal way to read.
• A partition key with few distinct values.
• Adding indexes speculatively.
• Running analytics against the production table.

AWS data services and the difference between transactional and analytical systems are covered in the Data Engineering course.

Sources

• What is Amazon DynamoDB? (Amazon DynamoDB Developer Guide) — AWS Documentation
• Core components of Amazon DynamoDB (Amazon DynamoDB Developer Guide) — AWS Documentation
• Best practices for querying and scanning data in DynamoDB (Amazon DynamoDB Developer Guide) — AWS Documentation
• DynamoDB read consistency (Amazon DynamoDB Developer Guide) — AWS Documentation
• DynamoDB throughput capacity (Amazon DynamoDB Developer Guide) — AWS Documentation
• DynamoDB data export to Amazon S3: how it works (Amazon DynamoDB Developer Guide) — AWS Documentation