---
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.factualminds.com/blog/btree-lsm-query-planner-database-engine-internals/
datePublished: 2026-06-12T00:00:00.000Z
dateModified: 2026-06-12T00: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 with **LSM-style** storage under the hood—no SQL planner, only access pattern.

## 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 `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 separate write cost.

## 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.

## What this guide doesn't cover

Vacuum and index bloat—part 4 of this track.

---

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