🐘 When Caching Bites Back: Fixing Postgres Slowness Caused by Auto-Vacuum

🌍 Top Remote Tech Roles Are Open Now!
Frontend · Backend · Full Stack · Mobile · DevOps · AI · Design
💰 Earn $50–$120/hr | Global Full-Time & Contract Roles
⏳ Don’t wait — early applicants get priority.

👉 Apply Now — Secure the Best Offers!

You know that moment when you finally solve one big problem — and a new one pops up right behind it?

That’s what happened to us after we rolled out our req_keeper caching table (explained in my previous blog). It worked great until our database started slowing down during peak hours.

Here’s how we tracked it down, what we learned about Postgres internals, and the fix that finally worked.

🐌 The Unexpected Slowness

A few weeks after release, we noticed a pattern:

• During peak hours, the app would suddenly slow down.
• Queries that normally ran fine became sluggish.
• Database CPU usage spiked even though overall traffic wasn’t dramatically higher.

At first, it felt like something in our application was off. But digging deeper, we realized the slowdown came from Postgres itself. Specifically: auto-vacuum was kicking in.

And when we checked which table it was cleaning, the answer surprised us:
👉 pg_toast for our req_keeper table.

🔎 The Investigation

This raised two questions:

1. Why was auto-vacuum running so aggressively?
2. Why was it targeting req_keeper (our caching table)?

That’s when we remembered how Postgres works under the hood:

• When you run a DELETE or UPDATEPostgres doesn’t immediately remove the row. Instead, it marks the old row as a dead tuple (a ghost row).
• Those dead tuples aren’t automatically cleared — Postgres relies on auto-vacuum to eventually clean them up.
• Large values (like our cached JSONB responses) are stored in a TOAST table.

🥪 What’s pg_toast?

TOAST stands for The Oversized-Attribute Storage Technique. It’s Postgres’s way of handling large column values (like big JSON, text, or bytea) that don’t fit neatly into a normal table row. When those values are updated or deleted, their old versions live on in pg_toast as dead tuples — until vacuum comes to clean them up.

If you want to dig deeper, Postgres has excellent docs on TOAST tables here.

Now it made sense:

• Our cache invalidation process deleted rows whenever data changed.
• Our daily cleanup job deleted old rows in bulk.
• Together, these created massive numbers of dead tuples inside pg_toast.
• Postgres auto-vacuum was simply trying to clean up… but often during peak hours.

In short:
⚡ Our deletes were keeping Postgres busy cleaning instead of serving queries.

💡 The Insight: Stop Creating Dead Tuples

Once we understood the cause, the real insight was this:
👉 The problem wasn’t the auto-vacuum itself. The problem was that we were creating so many dead tuples in the first place.

Deletes (and updates) in Postgres always create dead tuples. But truncates and drops do not. That’s when we asked ourselves:

Get Shubham Maurya’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

“What if instead of deleting old rows, we just dropped them altogether?”

🛠️ The Fix: Partitioning + Drop Strategy

Here’s the reasoning:

• Deleting millions of rows → dead tuples → vacuum storm → peak-hour slowness.
• Dropping a whole partition → instant cleanup, no dead tuples, no vacuum overhead.

So we restructured req_keeper:

1. Partitioned by created_at — Each day’s cache got its own partition.
2. Postgres cron job:

• Create a new partition daily.
• Drop the partition older than N days.

This way, instead of deleting rows, we simply dropped entire partitions. Cleanup became instant and lightweight.

Example: Partition Setup

-- Create partitioned table
CREATE TABLE req_keeper (
    req_keeper_pk UUID DEFAULT gen_random_uuid(),
    company UUID NOT NULL,
    hash VARCHAR(256) NOT NULL,
    type VARCHAR(256) NOT NULL,
    data JSONB NOT NULL,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Example: create partition for a single day
CREATE TABLE req_keeper_20251003 PARTITION OF req_keeper
    FOR VALUES FROM ('2025-10-03 00:00:00') TO ('2025-10-04 00:00:00');
-- Dropping old partition (fast, no dead tuples)
DROP TABLE req_keeper_20251002;

And with a scheduled job (via pg_cron or any scheduler), this became automatic.

🚀 The Results

After switching to partitioning + drops:

• 🚫 No more vacuum spikes during peak hours.
• ⚡ Cache cleanup became near-instant.
• 🧹 Database stayed lean — no dead tuple build-up.
• 📈 Overall app performance stabilized.

📚 What This Taught Us

• Deletes aren’t free. At scale, they leave behind ghosts (dead tuples) that your database has to clean up later.
• Partitioning is powerful. It turns slow deletes into instant drops.
• Understand your tools. Knowing how Postgres stores and cleans data helped us find the right fix.

🌟 Wrapping Up

Fixing one problem often creates another — and that’s okay. It’s part of building real systems.

Partitioning gave us a simple and effective way to fix the problem — no dead tuples, no vacuum storms, just a cleaner database.

👉 Have you faced similar challenges with Postgres vacuum or bloat, and how did you handle them?

Thank you for being a part of the community

Before you go:

👉 Be sure to clap and follow the writer ️👏️️

👉 Follow us: X | Medium

👉 CodeToDeploy Tech Community is live on Discord — Join now!

👉 Follow our publication, CodeToDeploy

Note: This Post may contain affiliate links.