Skip to main content
SaltStack Support

Vacuuming the SSE Database To Reclaim Disk Space

In rare cases we have seen PostgreSQL (Pg) instances attached to SaltStack Enterprise consume an inordinate amount of disk space for no clear reason. If the normal automatic database trim routines built in to SSE are running regularly this situation can be caused by Pg's standard automatic vacuum process not being aggressive enough when releasing deleted row space back to the OS. To force a more aggressive cleanup follow these steps.

Note: VACUUMing a Pg database with the FULL keyword can create significant load and disk I/O on the Pg server. We suggest running this procedure only when high load and I/O will not impact other business-critical tasks.
  1. Login to the Pg instance as the Pg user that owns your SSE database. In this example we will assume that user is named salt
psql --user salt raas_<your raas customer_id>
  1. Verify there is a reasonable amount of disk space available. The amount of free space should be equal to or greater than the amount of space consumed by the largest table in any of Pg's databases. The following query should show the amount of space consumed by every table in the database.
SELECT table_schema, table_name, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a order by total_bytes desc;
  1. If you have sufficient space, run the following command at the psql prompt:
vacuum full;

When the psql prompt returns you should see that more disk space is available.

  1. You may see warnings like these
WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_db_role_setting" --- only superuser can vacuum it

These indicate that this user doesn't have Pg superuser access and thus cannot vacuum all tables. They may be safely ignored, or you may login to Pg as a user with superuser privileges and re-vacuum.

  1. If you do not appear to have sufficient space you can run a full vacuum on individual smaller tables until enough space is freed up to vacuum the larger tables. For example:
vacuum full jobs;
  • Was this article helpful?