Database Maintenance

Why Disk Space Doesn't Shrink

PostgreSQL's DELETE command marks rows as dead but does not return the space to the operating system. The daily retention cleanup removes old logs on schedule, but the on-disk database files stay the same size until you explicitly reclaim the space.

Step 1: Run Retention Cleanup

If you want to force an immediate cleanup before the daily 03:00 cron, run the retention query manually. This deletes rows older than your configured retention period:

docker exec unifi-log-insight psql -U unifi -d unifi_logs -c "
  DELETE FROM logs
  WHERE timestamp < NOW() - INTERVAL '60 days';
"

Adjust 60 days to match your RETENTION_DAYS setting.

Step 2: VACUUM ANALYZE (Safe)

A standard VACUUM ANALYZE reclaims dead-row space for reuse by future inserts and updates query planner statistics. It runs without locking the table, so the application keeps working normally:

docker exec unifi-log-insight psql -U unifi -d unifi_logs -c "
  VACUUM ANALYZE logs;
"

This makes the freed space available for new rows but does not reduce the on-disk file size.

Step 3: VACUUM FULL ANALYZE (Shrinks Disk)

To actually shrink the database files on disk, run VACUUM FULL ANALYZE. This rewrites the entire table and returns space to the OS. Warning: it takes an exclusive lock on the table - reads and writes will block until it finishes.

docker exec unifi-log-insight psql -U unifi -d unifi_logs -c "
  VACUUM FULL ANALYZE logs;
"

For large databases this can take several minutes. Run it during a maintenance window when brief downtime is acceptable.

Additional Checks

If disk usage is still higher than expected after vacuuming, check the Docker container logs and the WAL directory:

# Check container logs for errors
docker logs unifi-log-insight --tail 50

# Check WAL directory size
docker exec unifi-log-insight du -sh /var/lib/postgresql/data/pg_wal

A large pg_wal directory can indicate replication slots or checkpoint issues. Restarting the container typically resolves temporary WAL buildup.