PostgreSQL Maintenance
PostgreSQL Maintenance
Migration in progress.
api.legendary-arena.comand its PostgreSQL are moving off Render onto a self-hosted DigitalOcean Ubuntu droplet fronted by Cloudflare, perdocs/PLAN.md. Until decommission, Render stays warm as the rollback target.
Summary
Routine vacuuming is automatic (autovacuum). Disk space that a table has already grown into is not given back to the OS unless the table is rewritten. 52-vacuum-reclaim.ps1
is the one-shot tool for that. It is aimed at bgio.matches, whose large TOASTed match blobs are the database’s main growth source; Render needed storage autoscaling because finished matches are never pruned.
Mechanics
- Runs
VACUUM (FULL, ANALYZE)on each table inVACUUM_TABLES(defaultbgio.matches) and prints the size before and after, plus what was reclaimed. VACUUM FULLholds an ACCESS EXCLUSIVE lock while it rewrites the table, so nothing can read or write it. The script therefore refuses to run whilela-serveris active, unlessVACUUM_ALLOW_LIVE=true.- Table names must be plain
schema.tableidentifiers.
Interactions
- Monitoring — the disk-usage check (85% by default) is the usual reason to run this.
- Backup and Restore — take a fresh backup first; a rewrite is the moment you want one.
Edge Cases
- Needs free space.
VACUUM FULLwrites a complete new copy before dropping the old one. If the disk is nearly full, resize the volume first. - Reclaim is temporary without retention. Until finished matches are pruned (an engine-repo follow-up), the table grows back.
Execute
sudo pwsh -File /opt/la-lab/infra/scripts/50-backup.ps1 # fresh backup first
sudo systemctl stop la-server
sudo pwsh -File /opt/la-lab/infra/scripts/52-vacuum-reclaim.ps1
sudo systemctl start la-server
Verify
The script’s last lines report before -> after (reclaimed N MB) per table. df -h / shows the space back on the disk. curl -fsS http://127.0.0.1:3000/health confirms the server is back.
References
infra/scripts/52-vacuum-reclaim.ps1— owning artifact.- Engine repo
render.yaml— thestorageAutoscalingEnablednote about unpruned match storage.