---
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. Vacuum strategy on Aurora, plus Aurora Limitless and DynamoDB hot key mitigation.
url: https://www.factualminds.com/blog/postgresql-vacuum-index-bloat-sharding-hot-partitions-aws/
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, 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. 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.

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

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

## What this guide doesn't cover

Full Limitless migration—see [Aurora Limitless blog](/blog/amazon-aurora-limitless-database/) if published.

---

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