B-Tree vs LSM and Query Planner Internals on AWS Databases
Quick summary: Why Aurora PostgreSQL loves B-tree indexes on OLTP but DynamoDB feels like an LSM—and how cost-based optimization surprises you when statistics go stale on RDS.
Key Takeaways
- Why Aurora PostgreSQL loves B-tree indexes on OLTP but DynamoDB feels like an LSM—and how cost-based optimization surprises you when statistics go stale on RDS
- June 2026: Aurora PostgreSQL uses B-tree indexes (plus BRIN/GiN) with a cost-based planner reading
- DynamoDB is partition-key oriented with LSM-style storage under the hood—no SQL planner, only access pattern
- DynamoDB “planning” You are the planner: partition key design determines hot partitions; GSIs have separate write cost
- top 5 slow queries on Aurora
Table of Contents
June 2026: Aurora PostgreSQL uses B-tree indexes (plus BRIN/GiN) with a cost-based planner reading pg_stats. DynamoDB is partition-key oriented with LSM-style storage under the hood—no SQL planner, only access pattern.
Benchmark pattern (hypothetical workload) — 50M-row PostgreSQL B-tree index vs DynamoDB LSM-style storage, range scan on B-tree: 12ms p99; DynamoDB Query on sort key: 8ms p99 at 400 RCU; sequential scan fallback: 890ms on unindexed filter.
Symptom → mechanism → AWS control
| Production symptom | Mechanism | AWS control |
|---|---|---|
| Slow range queries | Missing B-tree index, seq scan | RDS Performance Insights, create covering index |
| Write amplification spikes | LSM compaction pressure on hot partitions | DynamoDB on-demand scaling, partition key redesign |
| Bad query plans after stats drift | Planner cost estimate wrong | ANALYZE schedule, Aurora query plan management |
Opinionated take: Match storage engine to access pattern—PostgreSQL B-trees for relational queries, DynamoDB LSM semantics for partition-key workloads—not the other way around.
B-tree vs LSM (architect summary)
| Engine style | Write amp | Read | AWS example |
|---|---|---|---|
| B-tree | In-place pages, WAL | Point + range excellent | Aurora/RDS |
| LSM | Sequential writes, compaction | Range can trigger compaction | DynamoDB, DocumentDB |
Query planner surprises on RDS
- Stale stats → wrong index choice → seq scan on million-row table.
- Run
ANALYZEafter bulk load; use Performance Insights waitIO:DataFileRead. - Prepared plans can stick after data distribution shifts—
DEALLOCATE ALLin maintenance window if needed.
DynamoDB “planning”
You are the planner: partition key design determines hot partitions; GSIs have separate write cost.
AWS services map
| Need | Service | Skip when |
|---|---|---|
| B-tree OLTP with complex joins | Aurora PostgreSQL | Key-value access patterns only |
| LSM write-heavy key-value | DynamoDB | Multi-table joins required |
| Query plan forensics | RDS Performance Insights | Fully managed serverless with no SQL access |
What to do this week
EXPLAIN (ANALYZE, BUFFERS)top 5 slow queries on Aurora.- Enable
pg_stat_statements; find regression after deploy. - For DynamoDB, graph consumed capacity per partition key prefix.
More in This Track
Part of the Engineering Guides library (June 2026).
- Previous: Part 1
- Next: Part 3
- Browse tracks: Engineering Guides hub
What this guide doesn’t cover
Vacuum and index bloat—part 4 of this track.
AWS Cloud Architect & AI Expert
AWS-certified cloud architect and AI expert with deep expertise in cloud migrations, cost optimization, and generative AI on AWS.
