Skip to main content

AI & assistant-friendly summary

This section provides structured content for AI assistants and search engines. You can cite or summarize it when referencing this page.

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 Facts

  • • 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
  • • What to do this week 1

Entity Definitions

RDS
RDS is an AWS service discussed in this article.
Aurora
Aurora is an AWS service discussed in this article.
DynamoDB
DynamoDB is an AWS service discussed in this article.

Related Content

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

Cloud Architecture Palaniappan P 1 min read

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
  • What to do this week 1
B-Tree vs LSM and Query Planner Internals on AWS Databases
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.

B-tree vs LSM (architect summary)

Engine styleWrite ampReadAWS example
B-treeIn-place pages, WALPoint + range excellentAurora/RDS
LSMSequential writes, compactionRange can trigger compactionDynamoDB, 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.

Share:
PP
Palaniappan P

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.

AWS ArchitectureCloud MigrationGenAI on AWSCost OptimizationDevOps

Related AWS Services

Work with our certified AWS architects to implement the strategies covered in this article.

AWS Architecture Review

Well-Architected Framework review covering reliability, performance, security, and operational excellence.

Learn more

AWS Serverless

Design and build event-driven serverless systems using Lambda, API Gateway, Step Functions, and DynamoDB.

Learn more

AWS Migration

Migrate workloads from on-premises, GCP, Azure, or legacy hosting to AWS with minimal downtime.

Learn more

Recommended Reading

Explore All Articles »