I have a table called EVENTS on my PostgreSQL DB schema.
It is empty, i.e. when I execute
SELECT * FROM EVENTS
I get an empty results set.
Nonetheless, the table occupies 5MB of disk space.
I'm executing
SELECT round(pg_total_relation_size('events') / 1024.0 / 1024.0, 2)
And I'm getting 5.13MB.
I tried to explicitly run VACUUM, but it didn't change anything.
Any ideas?
Truncate the table:
truncate events;
From the documentation:
TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.
If you want to immediately reclaim disk space keeping existing rows of a non-empty table, you can use vacuum:
vacuum full events;
This locks exclusively the table and rewrite it (in fact, creates a new copy and drops the old one). It is an expensive operation and generally not recommended on larger tables.
In RDBMS some redundant usage of the disk space is a normal state. If you have a properly configured autovacuum daemon the unused space will be used when new rows are inserted.
Truncate table is faster (especially on large tables) than delete from table and has no side effects (other than that it reclaims disk space immediately). Unfortunately, a schema can not be truncated.vacuum full table locks exclusively the table and rewrite it (in fact, creates a new copy and drops the old one). It is expensive operation and generally not recommended. In RDBMS some redundant usage of the disk space is a normal state. The unused space will be used when new rows are inserted.If you have dead rows or bloat in your table, VACUUM will not actually reclaim its memory but make it reusable and this is used when you insert data to the table next time.
To reclaim the memory used, try
VACUUM FULL events;
