Database Indexing & Optimization
Key concepts
Global secondary indexes
Local secondary indexes
Query vs Scan operations
Connection pooling
Performance Insights
Overview
Database indexing and optimization is about making reads and writes fast and cheap as data and traffic grow. An index is an extra data structure that lets the database find rows by attributes other than the primary key without inspecting every item. In Amazon DynamoDB (a managed NoSQL key-value and document database) you add indexes through Global Secondary Indexes and Local Secondary Indexes, and you choose between Query (targeted, key-based lookups) and Scan (full-table reads). In Amazon RDS (a managed relational database service running engines such as MySQL, PostgreSQL, and others) optimization centers on connection pooling and on diagnosing slow workloads with Performance Insights.
This topic is high-yield on the SAA-C03 exam through "the queries are slow" and "the database is the bottleneck" scenarios. You should know when a Global Secondary Index (GSI) versus a Local Secondary Index (LSI) fits, why Query is preferred over Scan, how connection pooling (often via Amazon RDS Proxy) protects a relational database under bursty load, and how Performance Insights identifies the SQL and waits that consume database time.
Index for the Access Pattern, Pool the Connections
In DynamoDB, design indexes around how you query: use a GSI for new partition and sort keys (added any time), and an LSI for an alternate sort key on the same partition (defined at table creation). In RDS, reuse database connections with pooling such as RDS Proxy, and find the heavy SQL with Performance Insights.
Remember the hard facts: an LSI must be created with the table and shares the table partition key (alternate sort key); a GSI can be added any time with its own partition and sort key and is eventually consistent. Query reads efficiently by key while Scan reads the whole table. RDS Proxy pools and shares database connections, and Performance Insights uses Average Active Sessions (AAS) to show what is consuming database time.
Key Concepts
Global Secondary Indexes
A New Key Schema, Added Any Time
A Global Secondary Index (GSI) is an index with its own partition key and optional sort key that can differ from the base table keys. It lets you query the same table by completely different attributes (for example, query orders by customerId when the table key is orderId). A GSI can be created or deleted at any time, spans all partitions, and has its own provisioned or on-demand capacity separate from the table. Reads from a GSI are eventually consistent only (strong consistency is not available). You control which attributes are copied into the index through projection (KEYS_ONLY, INCLUDE, or ALL).
Local Secondary Indexes
An Alternate Sort Key on the Same Partition
A Local Secondary Index (LSI) shares the base table partition key but uses a different sort key, so it reorders items within a partition (for example, the table sorts by orderDate while an LSI sorts by totalAmount for the same customer). An LSI must be defined when the table is created and cannot be added later. It shares the table read and write capacity and supports both strongly consistent and eventually consistent reads. Each table allows up to 5 LSIs, and an item collection (all items sharing one partition key plus its indexes) is limited to 10 GB.
GSI vs LSI
| Dimension | Global Secondary Index | Local Secondary Index |
|---|---|---|
| Partition key | Can differ from table | Same as table |
| Sort key | Own optional sort key | Different sort key |
| When created | Any time | At table creation only |
| Capacity | Separate from table | Shared with table |
| Read consistency | Eventually consistent only | Strong or eventual |
| Max per table | 20 (default soft limit) | 5 |
Query vs Scan Operations
Targeted Reads Beat Full-Table Reads
A Query operation finds items by an exact partition key value with optional sort key conditions, so DynamoDB reads only the matching items and you pay only for what is read. A Scan reads every item in the table or index and then applies any filter afterward, so it consumes capacity proportional to the whole dataset even if the filter returns few rows. Prefer Query for predictable, low-cost access; reserve Scan for occasional full exports or analytics. When a Scan is unavoidable, use a parallel Scan (multiple segments) and a smaller page size to limit throughput spikes.
Query vs Scan
| Dimension | Query | Scan |
|---|---|---|
| Reads | Only matching key range | Every item then filters |
| Cost | Proportional to results | Proportional to table size |
| Latency | Low and predictable | Grows with table |
| Needs index | Key or secondary index | None |
| Best for | Day-to-day access patterns | Rare full reads or exports |
# Query: read only items for one customer (efficient, key-based)
aws dynamodb query \
--table-name Orders \
--index-name customer-index \
--key-condition-expression "customerId = :c" \
--expression-attribute-values '{":c": {"S": "CUST-1024"}}'
# Scan: reads the whole table, filter applied after the read (avoid for hot paths)
aws dynamodb scan \
--table-name Orders \
--filter-expression "totalAmount > :amt" \
--expression-attribute-values '{":amt": {"N": "500"}}'Connection Pooling
Reuse Connections Instead of Opening New Ones
Connection pooling keeps a set of open database connections that are reused across requests, which avoids the cost of opening a fresh connection for every request. Relational engines have a limited number of connections, and serverless callers like AWS Lambda can open thousands at once during a spike, exhausting the database. Amazon RDS Proxy is a managed, fully serverless proxy that sits between the application and RDS or Aurora, pools and shares connections, queues new requests when the database is busy, and fails over faster during a database restart. It also integrates with AWS Secrets Manager and AWS IAM for authentication.
Pooling Options
| Approach | Where it runs | Best for |
|---|---|---|
| RDS Proxy | Managed AWS service | Lambda or bursty apps hitting connection limits |
| App-side pool (HikariCP, PgBouncer) | Inside the application or container | Long-running servers with steady load |
| No pooling | Per-request connect | Low-traffic or test workloads only |
Performance Insights
See What Is Consuming Database Time
Amazon RDS Performance Insights is a database performance tuning and monitoring feature for RDS and Aurora. Its core metric is Average Active Sessions (AAS): the average number of sessions actively working in the database. A dashboard breaks that load down by wait events (such as CPU, I/O, or lock waits), by top SQL statements, by host, and by user, so you can pinpoint the query or resource causing a bottleneck. It retains 7 days of data free, with longer retention available for a fee, and complements Amazon CloudWatch metrics with a query-level view that CloudWatch alone does not provide.
Best Practices
1. Model DynamoDB indexes around access patterns
├── GSI for new partition/sort keys (add any time, separate capacity)
└── LSI for an alternate sort key on the same partition (table creation only)
2. Project only the attributes you read
└── Use KEYS_ONLY or INCLUDE to keep index size and cost down
3. Prefer Query over Scan on hot paths
└── If a Scan is required, use parallel segments and small pages
4. Pool relational connections
├── Use RDS Proxy for Lambda and bursty clients
└── Use an app-side pool for steady long-running servers
5. Tune RDS with Performance Insights
├── Watch Average Active Sessions against the vCPU line
└── Drill into top SQL and wait events, then add indexes or scaleCommon Pitfalls
Pitfall 1: Scanning a Large DynamoDB Table on a Hot Path
Mistake: Using Scan with a filter expression to serve a frequent application query.
Why it fails: Scan reads every item and consumes capacity for the whole table, applying the filter only after the read, which is slow and expensive at scale.
Correct Approach: Add a GSI on the queried attribute and use Query so DynamoDB reads only the matching items.
Pitfall 2: Planning to Add an LSI After Launch
Mistake: Designing a table and expecting to attach a Local Secondary Index later when a new sort order is needed.
Why it fails: An LSI can only be created with the table, so it cannot be added afterward.
Correct Approach: Decide LSIs up front at table creation, or use a GSI, which can be added any time with its own keys.
Pitfall 3: Letting Lambda Exhaust RDS Connections
Mistake: Connecting directly from many concurrent Lambda functions to an RDS database with no pooling.
Why it fails: Each invocation opens its own connection, and a spike can hit the engine connection limit, causing errors and failed requests.
Correct Approach: Put Amazon RDS Proxy in front of the database so connections are pooled and shared, and requests queue when the database is busy.
Pitfall 4: Tuning Blind Without Query-Level Visibility
Mistake: Relying only on CloudWatch CPU and memory graphs to chase a slow database.
Why it fails: Coarse metrics show that the database is busy without revealing which SQL statements or wait events cause the load.
Correct Approach: Enable Performance Insights and use Average Active Sessions, top SQL, and wait events to find and fix the real bottleneck.
Test Your Knowledge
A DynamoDB table uses orderId as its partition key. The team now needs to look up orders by customerId, which is not a key, and this query runs constantly in production. What is the most efficient design?
A serverless application built on AWS Lambda connects directly to an Amazon RDS for PostgreSQL database. During traffic spikes, the application receives too many connections errors. What should the architect add?
An Amazon RDS database is slow during business hours, but CloudWatch shows only that CPU is high. The team needs to find which queries and waits drive the load. Which feature provides this?
Related Services
Quick Reference
Limits and Facts
Indexing and Optimization Limits
| Item | Value |
|---|---|
| LSIs per table | 5 (at creation only) |
| GSIs per table | 20 (default soft limit) |
| LSI item collection size | 10 GB max per partition key |
| GSI read consistency | Eventually consistent only |
| Performance Insights free retention | 7 days |
| RDS Proxy | Managed connection pooling for RDS and Aurora |
Common CLI Commands
# Create a Global Secondary Index on an existing table
aws dynamodb update-table \
--table-name Orders \
--attribute-definitions AttributeName=customerId,AttributeType=S \
--global-secondary-index-updates \
'[{"Create":{"IndexName":"customer-index","KeySchema":[{"AttributeName":"customerId","KeyType":"HASH"}],"Projection":{"ProjectionType":"ALL"}}}]'
# Create an RDS Proxy for connection pooling
aws rds create-db-proxy \
--db-proxy-name app-proxy \
--engine-family POSTGRESQL \
--auth '[{"AuthScheme":"SECRETS","SecretArn":"arn:aws:secretsmanager:us-east-1:123456789012:secret:db-creds"}]' \
--role-arn arn:aws:iam::123456789012:role/rds-proxy-role \
--vpc-subnet-ids subnet-abc subnet-def
# Enable Performance Insights on an RDS instance
aws rds modify-db-instance \
--db-instance-identifier app-db \
--enable-performance-insights \
--apply-immediately