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; AWS's 2022 DynamoDB paper describes B-tree storage nodes with a write-ahead log, so its behavior comes from partitioning, not LSM compaction
- DynamoDB “planning” You are the planner: partition key design determines hot partitions; GSIs have a 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; AWS’s 2022 DynamoDB paper describes B-tree storage nodes with a write-ahead log, so its behavior comes from partitioning, not LSM compaction. It has no SQL planner, only the 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 the storage engine to the access pattern: PostgreSQL B-trees for relational queries and 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 | Cassandra, RocksDB-based stores (Keyspaces is Cassandra-compatible) |
Query planner surprises on RDS
- Stale stats lead to a wrong index choice and a seq scan on a 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 a 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 are covered in part 4 of this track.
Frequently asked questions
When should I pick DynamoDB instead of Aurora PostgreSQL indexes?
Why did Aurora pick a sequential scan after a bulk load?
What could go wrong if I model DynamoDB as a B-tree?
Does RDS Performance Insights replace EXPLAIN ANALYZE?
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.
