Vacuum analyze after huge deleting of rows not freeing space in postgres

Question

I delete a huge amount of rows in our postgres database and ran a vacuum analyze later on.

I noticed the disk usage did not moved much in AWS RDS console.

Tried running the query, based on that the table size did not move much as well.

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , 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;

Is this normal? Or disk space are not being reclaimed at all?

bobflux bobflux · Accepted Answer · 2017-08-20T08:27:07

VACUUM reclaims storage occupied by dead tuples, that is, it marks the space inside the table files and index as available for reuse, but it does not free up disk space.

I think what you want is a command that rewrites the entire table and indexes while only keeping the data that is really there. That would be CLUSTER, or VACUUM FULL, or one of the forms of ALTER TABLE which requires a table rewrite. If your performance can benefit from clustering on a certain order, now is a good time to do it.

It locks the table though, so don't do it on a live server which requires uninterrupted access, unless the table is small and you can live with a few seconds downtime.

Vacuum analyze after huge deleting of rows not freeing space in postgres

1 Answers