Legendary Arena Lab

PostgreSQL Maintenance

wiki

PostgreSQL Maintenance

Migration in progress. api.legendary-arena.com and its PostgreSQL are moving off Render onto a self-hosted DigitalOcean Ubuntu droplet fronted by Cloudflare, per docs/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 in VACUUM_TABLES (default bgio.matches) and prints the size before and after, plus what was reclaimed.
  • VACUUM FULL holds an ACCESS EXCLUSIVE lock while it rewrites the table, so nothing can read or write it. The script therefore refuses to run while la-server is active, unless VACUUM_ALLOW_LIVE=true.
  • Table names must be plain schema.table identifiers.

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 FULL writes 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