Postgresql partitioned table is using space despite no entries

Question

We have recently partitioned a master table with couple of millions of rows. This table is partitioned by "id range". There are 7 child tables created under this master table and entries are going into the child table during insert. All these are managed by pg_partman extension.

While running this query, the master table is shown to occupy about 300GB of disk space. This is strange because this table has no entries and I could confirm that by running check_parent() function.

SELECT nspname || '.' || relname AS "relation",pg_size_pretty(pg_relation_size(C.oid)) AS "size"  FROM pg_class C  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')  ORDER BY pg_relation_size(C.oid) DESC  LIMIT 30;

We never had this problem while partitioning this table in other environments, where the data is not that much. Could this be due to unreleased disk space during partitioning?

Answer 1

Yes, that could definitely be due to unreleased disk space. You should do a VACUUM FULL after moving data to different structures like partitioned tables.

PostgreSQL generally does not release table space automatically. Normal (automatic) VACUUM ANALYZE maintains the database but does not shrink tables on disk. VACUUM FULL locks the table, though, so be careful not to run it during normal operation hours.