PostgreSQL Vacuum, Index Bloat, and Sharding Hot Partitions on AWS
Quick summary: Autovacuum cannot keep up after Black Friday bulk deletes—and your BRIN index is not helping point lookups. Vacuum strategy on Aurora, plus Aurora Limitless and DynamoDB hot key mitigation.
Key Takeaways
- Vacuum strategy on Aurora, plus Aurora Limitless and DynamoDB hot key mitigation
- Aurora PostgreSQL (June 2026) still needs VACUUM—storage is auto-growing but dead tuple bloat hurts index efficiency and planner stats
- Benchmark pattern (hypothetical workload) — Aurora PostgreSQL 500GB table, index bloat at 42% after 90 days, VACUUM FULL maintenance window 3
- 2 hours; partition by created_at monthly cuts hot partition size to 18GB, autovacuum keeps bloat under 8%
- 2
Table of Contents
Aurora PostgreSQL (June 2026) still needs VACUUM—storage is auto-growing but dead tuple bloat hurts index efficiency and planner stats.
Symptom → mechanism → AWS control
| Production symptom | Mechanism | AWS control |
|---|---|---|
| Query slowdown without row growth | Index bloat inflates page reads | REINDEX CONCURRENTLY, monitor pg_stat_user_indexes |
| Autovacuum can’t keep up | Hot partition write churn | Table partitioning by time, per-partition vacuum tuning |
| Storage cost creep | Dead tuples not reclaimed | Aurora storage auto-scales; vacuum reclaims logical space |
Opinionated take: Partition PostgreSQL tables before they hit 100GB and treat autovacuum lag as a paging event—not a DBA chore for next quarter.
Benchmark pattern (hypothetical workload) — Aurora PostgreSQL 500GB table, index bloat at 42% after 90 days, VACUUM FULL maintenance window 3.2 hours; partition by created_at monthly cuts hot partition size to 18GB, autovacuum keeps bloat under 8%.
Index bloat
Heavy UPDATE/DELETE leaves dead tuples; autovacuum reclaims space. Tune autovacuum_vacuum_scale_factor on large tables; watch n_dead_tup in pg_stat_user_tables.
Sharding and hot partitions
| Service | Hot spot pattern | Mitigation |
|---|---|---|
| DynamoDB | Single partition key | Write sharding suffix, random salt, or DAX |
| Aurora Limitless | Shard router hotspots | Shard key cardinality review |
| RDS single writer | N/A | Read replicas + cache |
Opinionated take: Shard SQL only after Aurora Limitless or proven partition strategy—avoid premature micro-shard ops burden.
AWS services map
| Need | Service | Skip when |
|---|---|---|
| Partition management | Aurora PostgreSQL declarative partitioning | Table under 10GB with low churn |
| Vacuum monitoring | CloudWatch RDS metrics + Performance Insights | DynamoDB with TTL instead |
| Shard hot keys | DynamoDB write sharding suffix | Single PostgreSQL primary handles load |
What to do this week
- Alert on
MaximumUsedTransactionIDsand autovacuum lag. REINDEX CONCURRENTLYon top bloated index after measurement.- For DynamoDB throttling, enable CloudWatch
ThrottledRequestsper table.
More in This Track
Part of the Engineering Guides library (June 2026).
- Previous: Part 3
- Next: Part 6
- Browse tracks: Engineering Guides hub
What this guide doesn’t cover
Full Limitless migration—see Aurora Limitless blog if published.
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.
