DynamoDB Single-Table Design: Modeling Complex Schemas

Relational database systems encourage normalization, splitting data into distinct, clean tables and assembling them at runtime via SQL JOIN queries. In contrast, Amazon DynamoDB is a non-relational, distributed key-value database that scales horizontally by partitioning data across physical nodes. In DynamoDB, runtime joins do not exist. To maintain single-digit millisecond latency at scale, applications must use single-table design.

Single-table design involves co-locating diverse entity types—such as customers, orders, and order items—in a single DynamoDB table. By structuring partition keys (PK) and sort keys (SK) using generic composite patterns, you can query multiple hierarchical entities in a single I/O request. This guide explains how to model complex schemas, leverage sparse Global Secondary Indexes (GSIs), construct queries using the Go AWS SDK v2, and resolve production failure modes.

---

Single-Table Schema Modeling

To consolidate different entities in a single table, the primary keys must use generic names, typically PK and SK. We identify records using structured prefixes.

Entity Relationships and Key Strategy

Let’s model an e-commerce platform with the following relationships:

1. User Profile: A user has basic contact metadata.
2. Order: A user can place multiple orders over time.
3. Order Item: Each order contains one or more line items.
4. Fulfillment Queue: Operations needs to query only the orders that are currently “UNSHIPPED”.

The primary keys are structured as follows:

Entity Type	PK Pattern	SK Pattern	Attributes
User	USER#<UserID>	METADATA	Email, FullName, CreatedAt
Order	USER#<UserID>	ORDER#<OrderID>	OrderDate, TotalAmount, ShippedStatus (e.g., “UNSHIPPED” or “SHIPPED”)
Order Item	USER#<UserID>	ORDER#<OrderID>#ITEM#<ItemID>	ProductName, Quantity, Price

By using this structure, we can retrieve a user’s profile and all their orders in a single query by calling:

• Query Operation: PK = USER#10293 and SK BEGINS_WITH ORDER#

Sparse Global Secondary Indexes (GSIs)

Global Secondary Indexes allow querying data using alternative key attributes. In DynamoDB, an index is “sparse” if a row does not contain the GSI’s partition key. DynamoDB does not replicate items to the GSI unless the index keys are present in the base item. This saves storage costs and prevents unnecessary write capacity consumption.

To create a GSI tracking pending shipments, we define a GSI with:

• GSI1PK: ShippedStatus (only populated with the value "UNSHIPPED" for pending orders; deleted or omitted once the order is shipped).
• GSI1SK: OrderDate (allows sorting the shipment queue by age).

Once an order is marked as shipped, the application updates the item, removing the ShippedStatus attribute. DynamoDB automatically deletes the index entry, keeping the index small and optimized.

---

Go Client Integration with AWS SDK v2

The following complete Go application demonstrates how to connect to DynamoDB using the AWS SDK v2, execute a composite key query to fetch a user and their orders, and query the sparse GSI to locate unshipped orders.

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/aws/aws-sdk-go-v2/config"
	"github.com/aws/aws-sdk-go-v2/feature/dynamodb/attributevalue"
	"github.com/aws/aws-sdk-go-v2/service/dynamodb"
	"github.com/aws/aws-sdk-go-v2/service/dynamodb/types"
)

// UserMetadata represents the profile info of a customer
type UserMetadata struct {
	PK        string    `dynamodbav:"PK"`
	SK        string    `dynamodbav:"SK"`
	Email     string    `dynamodbav:"Email"`
	FullName  string    `dynamodbav:"FullName"`
	CreatedAt time.Time `dynamodbav:"CreatedAt"`
}

// OrderMetadata represents an order record in the table
type OrderMetadata struct {
	PK            string    `dynamodbav:"PK"`
	SK            string    `dynamodbav:"SK"`
	OrderDate     time.Time `dynamodbav:"OrderDate"`
	TotalAmount   float64   `dynamodbav:"TotalAmount"`
	ShippedStatus string    `dynamodbav:"ShippedStatus,omitempty"`
}

// DynamoAdapter manages interactions with AWS DynamoDB
type DynamoAdapter struct {
	Client    *dynamodb.Client
	TableName string
}

// NewDynamoAdapter initializes the AWS Config and returns a Client wrapper
func NewDynamoAdapter(ctx context.Context, tableName string) (*DynamoAdapter, error) {
	cfg, err := config.LoadDefaultConfig(ctx, config.WithRegion("us-east-1"))
	if err != nil {
		return nil, fmt.Errorf("unable to load SDK config: %w", err)
	}

	client := dynamodb.NewFromConfig(cfg)
	return &DynamoAdapter{
		Client:    client,
		TableName: tableName,
	}, nil
}

// GetUserAndOrders fetches the user's metadata and orders using one query request
func (d *DynamoAdapter) GetUserAndOrders(ctx context.Context, userID string) ([]map[string]types.AttributeValue, error) {
	pkValue := fmt.Sprintf("USER#%s", userID)

	input := &dynamodb.QueryInput{
		TableName:              &d.TableName,
		KeyConditionExpression: &[]string{"PK = :pk AND SK >= :skPrefix"}[0],
		ExpressionAttributeValues: map[string]types.AttributeValue{
			":pk":       &types.AttributeValueMemberS{Value: pkValue},
			":skPrefix": &types.AttributeValueMemberS{Value: "METADATA"},
		},
		ScanIndexForward: &[]bool{true}[0], // Sort ascending
	}

	res, err := d.Client.Query(ctx, input)
	if err != nil {
		return nil, fmt.Errorf("failed to query user items: %w", err)
	}

	return res.Items, nil
}

// GetUnshippedOrders queries the sparse secondary index GSI1
func (d *DynamoAdapter) GetUnshippedOrders(ctx context.Context, limit int32) ([]OrderMetadata, error) {
	input := &dynamodb.QueryInput{
		TableName:              &d.TableName,
		IndexName:              &[]string{"GSI1"}[0],
		KeyConditionExpression: &[]string{"GSI1PK = :gsi1pk"}[0],
		ExpressionAttributeValues: map[string]types.AttributeValue{
			":gsi1pk": &types.AttributeValueMemberS{Value: "UNSHIPPED"},
		},
		Limit: &limit,
	}

	res, err := d.Client.Query(ctx, input)
	if err != nil {
		return nil, fmt.Errorf("failed to query GSI1: %w", err)
	}

	var orders []OrderMetadata
	err = attributevalue.UnmarshalListOfMaps(res.Items, &orders)
	if err != nil {
		return nil, fmt.Errorf("failed to unmarshal GSI1 records: %w", err)
	}

	return orders, nil
}

func main() {
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	adapter, err := NewDynamoAdapter(ctx, "EcommerceProductionTable")
	if err != nil {
		log.Fatalf("Initialization failed: %v", err)
	}

	// Fetch specific user and their nested order entities
	items, err := adapter.GetUserAndOrders(ctx, "usr_99823")
	if err != nil {
		log.Printf("Query error: %v", err)
	} else {
		fmt.Printf("Retrieved %d partition items in a single query call.\n", len(items))
		for _, item := range items {
			var sk string
			if val, ok := item["SK"].(*types.AttributeValueMemberS); ok {
				sk = val.Value
			}
			fmt.Printf(" - Found Entity Key: %s\n", sk)
		}
	}

	// Fetch work queue from sparse GSI
	unshipped, err := adapter.GetUnshippedOrders(ctx, 10)
	if err != nil {
		log.Printf("Sparse GSI Query error: %v", err)
	} else {
		fmt.Printf("Retrieved %d pending orders from GSI1.\n", len(unshipped))
	}
}

---

Metrics Comparison Table

The table below contrasts single-table design queries with a multi-table setup where entities are split into distinct DynamoDB tables and joined at the application level.

Operational Dimension	Normalized Multi-Table Design	Co-Located Single-Table Design	Production Impact
API Round Trips	3 separate network calls (User, Orders, Items)	1 optimized Query call	Lower client connection latency.
Read Capacity Units (RCUs)	3.0 RCUs minimum (Separate table overhead)	1.0 RCU (Co-located item collections)	Reduces AWS read billing costs by up to 66%.
Sparse Index Storage Footprint	N/A (Index replicates all rows)	Bounded to only pending items (e.g., < 5%)	Saves storage and reduces GSI write capacity requirements.
Write Capacity Units (WCUs)	1.0 WCU per table write	1.0 WCU base, no GSI write for shipped orders	Avoids index write capacity consumption on inactive records.
Average Query Response Latency	35 ms to 80 ms (Sequential execution)	4 ms to 9 ms (Point lookup)	Provides highly predictable, low-latency API performance.
Scan Space Overhead	100% of tables must be scanned	Zero scans required (All reads use target keys)	Prevents high-cost table scanning operations.

---

What Breaks in Production

Adopting single-table design requires strict discipline. When scaling to millions of transactions, several design vulnerabilities can degrade cluster performance.

1. Hot Partitions and Throughput Throttling

A physical partition in DynamoDB is capped at a maximum of 1,000 WCUs, 3,000 RCUs, and 10GB of storage. If your partition key is designed around high-traffic items (e.g., a specific popular product PRODUCT#electronics_sale), write and read requests targeting that key will exceed the partition limits.

• Symptoms: The database returns ProvisionedThroughputExceededException errors, client SDKs initiate retry loops, response latencies spike, and applications exhaust their connection pools.
• Remediation:
  1. Implement write sharding. Append a calculated random suffix (e.g., PRODUCT#electronics_sale#1, PRODUCT#electronics_sale#2) to distribute data across multiple physical partitions.
  2. Use DynamoDB Accelerator (DAX) to cache reads for hot items, protecting the partition from read capacity exhaustion.
  3. Ensure the partition key has high cardinality (e.g., using UUIDs like USER#<UUID> rather than low-cardinality keys like COUNTRY#US).

2. GSI Replication Lags and Base Table Throttling

DynamoDB replicates base table updates to GSIs asynchronously. However, if a GSI is under-provisioned (meaning its write capacity is set lower than that of the base table), the base table will throttle writes to match the rate at which the GSI can ingest updates.

• Symptoms: Writes to the base table fail with write throttling errors, even though the base table itself has ample write capacity. GSI replication lag increases, leading to inconsistent read results.
• Remediation:
  1. Always provision GSI write capacity to match or exceed the base table write capacity, or use On-Demand billing to let AWS scale capacity automatically.
  2. Monitor the AWS CloudWatch metric PendingReplicationCount to detect when GSIs are falling behind.
  3. Avoid writing large batch updates to the base table that update index keys unless you have pre-scaled the GSI capacity.

3. Accidental Scan Operations Draining Read Capacity

In a single-table design database, executing a Scan operation reads every item in the entire table, regardless of its type.

• Symptoms: A single query from a developer or reporting tool consumes thousands of RCUs in seconds, triggering immediate throttling across all production users and APIs.
• Remediation:
  1. Restrict IAM permissions for the production application so that the dynamodb:Scan permission is explicitly denied for normal operational roles.
  2. Enforce structural testing and code reviews using linters to verify that queries always specify a KeyConditionExpression.
  3. Export data to Amazon S3 via DynamoDB streams or the export-to-S3 feature for analytics and reporting, ensuring that analytical queries never run against the active transactional table.

---

FAQs

What is the primary benefit of this design pattern?

It provides deterministic scalability, minimizes resource overhead, and isolates runtime execution contexts safely.

How do we verify the performance improvements?

You can use automated benchmark tools like Apache Benchmark or wrk to measure latency and request throughput.