---
title: PostgreSQL Vacuum, Index Bloat, and Sharding Hot Partitions on AWS
description: Autovacuum cannot keep up after Black Friday bulk deletes, and your BRIN index is not helping point lookups. This guide covers vacuum strategy on Aurora, plus Aurora Limitless and DynamoDB hot key mitigation.
url: https://www.joinhorizon.co/blog/postgresql-vacuum-index-bloat-sharding-hot-partitions-aws/
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, aurora, aws
---

# PostgreSQL Vacuum, Index Bloat, and Sharding Hot Partitions on AWS

> Autovacuum cannot keep up after Black Friday bulk deletes, and your BRIN index is not helping point lookups. This guide covers vacuum strategy on Aurora, plus Aurora Limitless and DynamoDB hot key mitigation.

**Aurora PostgreSQL (June 2026)** still needs **VACUUM**. Storage grows automatically, 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 a proven partition strategy. Premature micro-sharding adds an operations 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

1. Alert on `MaximumUsedTransactionIDs` and autovacuum lag.
2. `REINDEX CONCURRENTLY` on top bloated index after measurement.
3. For DynamoDB throttling, enable CloudWatch `ThrottledRequests` per table.

## More in This Track

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

- Previous: [Part 3](https://www.joinhorizon.co/blog/database-deadlocks-connection-pools-prepared-statements-rds/)
- Next: [Part 6](https://www.joinhorizon.co/blog/aws-rds-vs-aurora-when-to-use-which-database/)
- Browse tracks: [Engineering Guides hub](https://www.joinhorizon.co/resources/engineering-guides/)

## What this guide doesn't cover

A full Limitless migration is out of scope; see the [Aurora Limitless blog](https://www.joinhorizon.co/blog/amazon-aurora-limitless-database/) if published.

## FAQ

### Does Aurora storage auto-grow mean I can skip VACUUM?
No. Aurora still needs VACUUM. Dead tuples bloat indexes and planner stats even when storage grows. Alert on autovacuum lag and MaximumUsedTransactionIDs; treat lag as a page, not a quarterly chore.

### When should I partition a PostgreSQL table on AWS?
Before it approaches 100GB of hot churn. Monthly created_at partitions keep autovacuum on a smaller heap. Tables under about 10GB with low churn can wait. Do not micro-shard SQL before Aurora Limitless or a proven partition key.

### How is DynamoDB hot-key mitigation different from Postgres bloat?
DynamoDB throttles a single partition key. Use a write-sharding suffix, salt, or DAX, not REINDEX. PostgreSQL bloat is dead tuples and index pages; REINDEX CONCURRENTLY after you measure pg_stat_user_indexes.

### Will VACUUM FULL in a 3-hour window be the default fix?
Only as a last resort after bulk deletes. Tune autovacuum_vacuum_scale_factor on large tables and partition so autovacuum can keep up. VACUUM FULL is a maintenance outage pattern, not weekly hygiene.

---

*Source: https://www.joinhorizon.co/blog/postgresql-vacuum-index-bloat-sharding-hot-partitions-aws/*