Skip to content

title: Postgres VACUUM + ANALYZE schedule description: When to run VACUUM and ANALYZE manually on top of Postgres autovacuum. Mostly: after big bulk loads. status: active tags: - runbook - backend

Postgres VACUUM + ANALYZE schedule

Postgres autovacuum handles 95% of cases. The remaining 5% is when our scraper or affiliate-reconciliation work bulk-inserts thousands of rows in a tight window — autovacuum waits for thresholds, the planner ends up working off stale stats, and queries get slow.

Manual ANALYZE — when

Run after: - npm run scrape completes when listing count delta > 500 rows. A full scrape adds ~2,800 listings; the planner needs to know. - Affiliate-reconciliation CSV import (#17) — bulk insert into Click and (eventually) AffiliateConversion. - Match-confidence threshold tuning (#146) — bulk update of Listing.matchStatus.

docker exec 961tech-postgres psql -U postgres -d tech961 -c "ANALYZE;"

Or scoped:

ANALYZE "Listing", "ListingPrice", "Click";

Cheap (seconds even on a populated DB). Always safe.

Manual VACUUM — when

Autovacuum should be enough for everything we write today. Manual VACUUM becomes useful only if: - A long-running transaction blocked autovacuum and bloat accumulated. Symptom: a previously-fast query now slow on pg_stat_user_tables.n_dead_tup > 20% of n_live_tup. - A table is approaching XID wraparound (~1.5B transactions). Diagnostic: SELECT datname, age(datfrozenxid) FROM pg_database;. Hit ~1B → manual VACUUM FREEZE.

Daily lifecycle on hobby scale: don't manually VACUUM. Trust autovacuum.

Automated schedule (M2 onwards)

Per #67 once pg-boss runs, the post-scrape ANALYZE becomes a chained job:

// Sketch — lands when #67 ships.
export async function postScrape(): Promise<void> {
  await runScrapers();
  await db.$executeRaw`ANALYZE "Listing", "ListingPrice"`;
}

For now, the manual command above runs at the end of npm run scrape if listing count jumped meaningfully (the script already counts delta). Add the db.$executeRaw('ANALYZE') line in scripts/run-scrapers.ts when prod telemetry shows it's worth the additional step.

Neon-specific

Neon manages autovacuum aggressively (it's their cost surface — every undead tuple is a row they'd otherwise have to bill for). You shouldn't need manual VACUUM on Neon. Manual ANALYZE still helps the planner.

See also