Implementing Auto-Increment Functionality with Amazon DynamoDB

More Info

When creating an application using Amazon DynamoDB, you may find the need for newly inserted items in a table to possess an incrementing sequence number. Commonly referred to as auto-increment in other database systems, this feature automatically assigns a value upon insertion. Typical scenarios for this requirement include assigning a numeric identifier to customer orders or support tickets.

Although DynamoDB does not natively support auto-increment as an attribute type, there are multiple strategies to create an incrementing sequence number. This article outlines two straightforward and cost-effective methods.

Overview of Solutions

Before diving into implementation, it’s crucial to assess whether an incrementing sequence number is genuinely necessary. Randomly generated identifiers generally scale more effectively since they do not require a central coordination point. Situations where mimicking auto-increment behavior in DynamoDB is acceptable typically fall into two categories:

1. Migrating from a relational database where users or systems are accustomed to the existing auto-increment behavior.
2. The application needs to provide a human-readable growing numeric identifier for new items, such as an employee ID or ticket number.

The subsequent sections detail how to achieve an incrementing sequence number via a counter or a sort key.

Counter-Based Implementation

The first method to generate an incrementing sequence number involves using an atomic counter. This is a two-step process: first, request an increment of the counter and obtain the new value in the response; second, use this value in a subsequent write operation.

Here’s a Python example that updates an atomic counter to get the next order ID and then inserts an order with this ID as the partition key. It’s also possible to utilize a different value for the partition key and store the ID in a separate attribute:

import boto3

table = boto3.resource('dynamodb').Table('orders')

# Increment the counter and retrieve the new value
response = table.update_item(
    Key={'pk': 'orderCounter'},
    UpdateExpression="ADD #cnt :val",
    ExpressionAttributeNames={'#cnt': 'count'},
    ExpressionAttributeValues={':val': 1},
    ReturnValues="UPDATED_NEW"
)

# Get the new value
nextOrderId = response['Attributes']['count']

# Utilize the new value
table.put_item(
    Item={'pk': str(nextOrderId), 'deliveryMethod': 'expedited'}
)

This design eliminates race conditions because all writes to a single DynamoDB item occur serially, ensuring that each counter value is unique. The cost of this method includes one write to update the counter item, plus the standard write costs for storing the new item. The throughput is limited by the counter item, with the maximum throughput for a single small item mirroring that of a partition.

Gaps might occur in the sequence if a failure happens between updating the counter and writing the new item. For example, if the client application halts between the two steps, or if the AWS SDK’s automatic retry functionality increments the counter multiple times due to a network failure. Note that auto-increment columns can also experience gaps.

You can maintain multiple counters simultaneously if you require more than one sequence value for your table.

Sort Key-Based Implementation

The second method utilizes the maximum value of the sort key within an item collection to track the highest sequence value for that collection. In DynamoDB, items can have a partition key and an optional sort key as part of their primary key. Items in a collection share the same partition key but have different sort keys. A query can target a collection to retrieve all items or use a sort key condition to get a specific subset.

By designing the sort key to represent the sequential value, you can efficiently query to retrieve the maximum value. For instance, in a table that holds project issues, the project identifier serves as the partition key while the issue number is the sort key (ensure the sort key is declared as numeric when creating the table or as a string with zero padding for correct lexicographical sorting). The issue number increments independently for each project, with the highest value representing the maximum issue number in that collection.

To add a new item with the next sequence value, you must perform a two-step process: first, query to find the highest sort key value for that collection; second, attempt to write the new item using the highest value plus one, including a condition that stipulates the item does not already exist in the table to prevent race conditions.

Here’s a Python example demonstrating how to query for the highest value in an item collection (representing a project) and write an item with the next sort key value. The example keeps retrying with an incremented sort key value until successful:

import boto3
from boto3.dynamodb.conditions import Key

PROJECT_ID = 'projectA'

dynamo = boto3.resource('dynamodb')
client = dynamo.Table('projects')
highestIssueId = 0
saved = False

# Query to find the last sorted value in the item collection
response = client.query(
    KeyConditionExpression=Key('pk').eq(PROJECT_ID),
    ScanIndexForward=False,
    Limit=1
)

# Get the sort key value
if response['Count'] > 0:
    highestIssueId = int(response['Items'][0]['sk'])

while not saved:
    try:
        # Write with the next sequence value, ensuring the item doesn’t already exist
        response = client.put_item(
            Item={
                'pk': PROJECT_ID,
                'sk': highestIssueId + 1,
                'priority': 'low'
            },
            ConditionExpression='attribute_not_exists(pk)'
        )
        saved = True
    except dynamo.meta.client.exceptions.ConditionalCheckFailedException as e:
        # Handle race condition by incrementing the value and looping again
        highestIssueId += 1

The cost for this approach includes 0.5 read units to query the highest value, plus the typical write costs. Any attempts to write that are rejected due to the condition failing will add to the cost, increasing with contention and retries. If you expect contention, consider turning the read into a strongly consistent read, which costs 1.0 read units but always retrieves the latest value.

For further insights on this topic, check out this engaging blog post here, as well as this authoritative source for in-depth knowledge. Additionally, if you’re interested in exploring career opportunities, visit this excellent resource.

Located at Amazon IXD – VGT2, 6401 E Howdy Wells Ave, Las Vegas, NV 89115, this facility is dedicated to utilizing cutting-edge technologies.