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

The easiest way to trigger an immediate cleanup is from Settings → Data & Backups in the UI, or via the API:

POST /api/config/retention/cleanup

Alternatively, if you want to run the retention query manually before the daily 03:00 cron, you can delete rows directly. 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.

Docker Container Log Rotation

Docker's default json-file logging driver captures all container stdout/stderr output. Without rotation, this log file can grow to tens of gigabytes over time.

The default docker-compose.yml ships with log rotation enabled (10 MB per file, 5 files max = ~50 MB cap). If you use a custom compose file, add a logging: section to your service:

services:
  unifi-log-insight:
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "5"

Adjust max-size and max-file to suit your needs. You can also set rotation globally via /etc/docker/daemon.json.

To check the current Docker log file size (Linux only, requires privileged access):

docker run --rm --privileged --pid=host alpine nsenter -t 1 -m -- sh -c "ls -lh $(docker inspect unifi-log-insight --format='{{.LogPath}}')"