# The Bug That Only Showed Up After a Week: Why We Still Needed to Add a Daily VACUUM

The first two posts in this series were about a **dramatic redesign** and an even more **dramatic** night of debugging. This one is different. There was no outage. No paging. No 1 AM EXPLAIN ANALYZE session under pressure.

This is a story about what happens a few weeks after a migration ships, when the adrenaline is gone and the system is just... running. And about how the quiet, unglamorous parts of ownership matter just as much as the dramatic ones.

* * *

## The Symptom

A few weeks after the partition migration went live, folks on the team mentioned that the Django admin pages felt slow. **Not broken, but sluggish certainly**. Listener latency check which verifies collection pending metrics was **occasionally timing out**. Listing and filtering on the telemetry table, and the small operational dashboard view we use to check pending sync counts, both felt sluggish.

It was the kind of thing that is easy to dismiss.

> "Maybe the table just has more data now."
> 
> "Maybe it's network."
> 
> "Maybe it's always been like this and I'm imagining it."

We did not dismiss it. We had just spent two posts' worth of effort getting this system right, and "the admin feels slow" was worth five minutes of investigation.

* * *

## What EXPLAIN ANALYZE Showed

We ran `EXPLAIN ANALYZE` on the queries behind both the admin pages and the dashboard view. The query plans **looked structurally correct** index-only scans on the older partitions, exactly as designed. But one partition stood out.

The partition covering yesterday — call it T-1, the partition that had just finished its busiest day of writes and updates — was showing meaningfully more heap fetches than the others.

```plaintext
Index Only Scan using ..._id_idx on ..._partition_yesterday
  Heap Fetches: 178
```

Compare that to a partition from **three or four days earlier**:

```plaintext
Index Only Scan using ..._id_idx on ..._partition_4_days_ago
  Heap Fetches: 0
```

Zero heap fetches on the older partitions. Real, nonzero heap fetches on T-1.

This was NOT a query plan problem. The planner was choosing the right strategy: index-only scans throughout.

The problem was that the *visibility map* on T-1 was stale, which forces PostgreSQL to double-check the heap even when it is using an index-only scan, just to confirm that what the index says is actually still visible to the current transaction.

* * *

## Why T-1 Specifically

Every partition goes through the same lifecycle. It is **created** a couple of days ahead of time, **sits** **empty**, then **becomes "today"** and then **bursts** **to life**

> absorbs a full day of high-frequency writes and updates as telemetry rows flow in and get processed by downstream sync jobs.

![](https://cdn.hashnode.com/uploads/covers/69e47735ee84f66e94219a17/c0f32703-3f6d-495d-b3a6-39721db2dad8.svg align="center")

The day a partition is "today," it is under **constant write pressure**. Autovacuum is working, but it is competing with live traffic, and its default trigger thresholds are tuned for steady-state tables, not a partition that goes from **empty to a million rows of churn in 24 hours**.

The day after — when that partition becomes T-1 — the write pressure has dropped off. The partition is no longer "today's" hot target. This is exactly the moment **autovacuum should have caught up**. But in practice, depending on timing, the partition could still be carrying a meaningfully stale visibility map from yesterday's churn, and autovacuum's natural schedule had not yet gotten around to settling it.

Every query that touched T-1, whether through the admin pages or the dashboard view **paid the heap-fetch tax**. Multiply that by every admin page load, every dashboard poll, every day, and you get a **system that "feels a bit slower"** without any single query being dramatically broken.

* * *

## The Fix

The fix was straightforward once we understood the cause: **don't wait for autovacuum** to get around to T-1 on its own schedule. **Vacuum it explicitly**, every day, as part of the maintenance routine that was already running.

```sql
VACUUM (ANALYZE) telemetry_table_partition_yesterday;
```

Added as a step in the daily maintenance cron, the same script responsible for moving the oldest partition into archive and pre-creating partitions ahead of time. T-1 gets an **explicit**, **deliberate VACUUM** the moment its busiest day is behind it, rather than waiting for autovacuum's organic timing.

> The fix was a one-time addition. The problem did not recur.

T-1 is now reliably clean by the time anyone queries it the next morning, and the heap fetches we saw in that EXPLAIN ANALYZE output have not come back.

* * *

## A Smaller Improvement, Found Along the Way

While we were investigating the admin slowness, we also tightened up the dashboard view itself. It had originally been written using `COUNT(table.*)` **counting full row values** rather than counting rows directly. That small difference forces PostgreSQL to **fetch the entire row from the heap** to construct the composite value, which defeats index-only scanning even on partitions that were otherwise perfectly healthy.

Switching to `COUNT(*)` let the planner go back to **pure index-only scans** wherever the visibility map was current, and as a side benefit, planning time dropped from the tens of milliseconds down to under a millisecond noticeable on a view that gets polled frequently.

![](https://cdn.hashnode.com/uploads/covers/69e47735ee84f66e94219a17/e2ad0bfb-40f7-4261-a918-de4e8cda22eb.svg align="center")

It was NOT the headline fix. But it is the kind of detail that is easy to introduce by accident and easy to miss in review, and it is worth knowing the difference.

* * *

## What This Says About Ownership

The first two posts in this series were about **big, deliberate engineering moved.** A structural **redesign**, a methodical **debugging** process under real production pressure. This post is about something quieter: noticing that something feels slightly off, **taking it seriously** even though nothing was broken, and tracing it back to a gap in how a new system's maintenance routine interacted with PostgreSQL's autovacuum defaults.

Migrations do not end when the swap script commits. They end when the system has been **observed, tuned, and trusted** under real operating conditions for long enough that **nothing is left to discover**. A few weeks is often when the truly quiet bugs show up the ones that do not throw errors, do not page anyone, and would have been very easy to write off as "the table just has more data now."

The discipline that mattered here was not technical sophistication. It was treating a **vague, easily dismissible report** "the admin feels slow" with the **same seriousness as a 25-second production incident.** Both were signals. One was just much quieter than the other.

* * *

*This closes out our three-part series on rebuilding our IoT telemetry pipeline: the structural redesign, the live debugging story, and now the quiet follow-up that came weeks later. If this kind of careful, ongoing systems work is interesting to you, we would like to hear from you.*