---
title: B-Tree vs LSM and Query Planner Internals on AWS Databases
description: 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.
url: https://www.joinhorizon.co/blog/btree-lsm-query-planner-database-engine-internals/
datePublished: 2026-06-12T00:00:00.000Z
dateModified: 2026-10-28T00:00:00.000Z
author: palaniappan-p
category: Cloud Architecture
tags: engineering-guide, database, postgresql, dynamodb, aws
---

# B-Tree vs LSM and Query Planner Internals on AWS Databases

> 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 `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 `ANALYZE`** after bulk load; use Performance Insights wait `IO:DataFileRead`.
- **Prepared plans** can stick after data distribution shifts; `DEALLOCATE ALL` in 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

1. `EXPLAIN (ANALYZE, BUFFERS)` top 5 slow queries on Aurora.
2. Enable `pg_stat_statements`; find regression after deploy.
3. For DynamoDB, graph consumed capacity per partition key prefix.

## More in This Track

Part of the **Engineering Guides** library (June 2026).

- Previous: [Part 1](https://www.joinhorizon.co/blog/postgresql-transaction-isolation-acid-vs-base-aws/)
- Next: [Part 3](https://www.joinhorizon.co/blog/database-deadlocks-connection-pools-prepared-statements-rds/)
- Browse tracks: [Engineering Guides hub](https://www.joinhorizon.co/resources/engineering-guides/)

## What this guide doesn't cover

Vacuum and index bloat are covered in part 4 of this track.

## FAQ

### When should I pick DynamoDB instead of Aurora PostgreSQL indexes?
Pick DynamoDB when access is partition-key plus sort-key, not multi-table joins. You are the planner: hot partitions and GSI write cost replace EXPLAIN. Aurora B-trees win on relational range scans and joins.

### Why did Aurora pick a sequential scan after a bulk load?
Stale pg_stats. The cost-based planner can drop a covering index after distribution shifts. Run ANALYZE after bulk load, watch Performance Insights IO:DataFileRead, and unpin sticky prepared plans in a maintenance window if needed.

### What could go wrong if I model DynamoDB as a B-tree?
Write amplification and hot partitions look like “slow indexes.” LSM-style storage wants sequential writes and partition-key cardinality. Redesign the key instead of adding a SQL-style secondary index and expecting Aurora behavior.

### Does RDS Performance Insights replace EXPLAIN ANALYZE?
No. Use EXPLAIN (ANALYZE, BUFFERS) on the top slow queries, then Insights for wait events. DynamoDB has no SQL planner, so graph consumed capacity per partition-key prefix instead.

---

*Source: https://www.joinhorizon.co/blog/btree-lsm-query-planner-database-engine-internals/*