PostgreSQL - Empty table

Question

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?

Answer 1

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.