PostgreSQL drop table command is not reducing DB size
Solution 1
VACUUM
(or VACUUM FULL
) is hardly useful in this case, since it only reclaims space from within tables. Neither is required after dropping a table. Each table (and index) is stored as separate file in the operating system, which is deleted along with the table. So space is reclaimed almost instantly.
Well, there are entries in catalog tables that would leave dead tuples behind after dropping a table. So the database can occupy slightly more space after a table has been created and dropped again.
To get a db down to minimum size again, run (as privileged user):
VACUUM FULL;
Without a list of tables, all accessible tables are rewritten to pristine condition. (Not advisable for a big database with concurrent load, as it takes exclusive locks!)
Or the client tool vacuumdb
with the --full
option:
vacuumdb -f mydb
This also rewrites system catalogs (also tables) and indices in pristine condition. Details in the Postgres Wiki:
Postgres has dedicated object size functions to measure db size:
SELECT pg_size_pretty(pg_database_size(mydb));
Solution 2
Use truncate, understand the warning about MVCC first
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.
http://www.postgresql.org/docs/9.1/static/sql-truncate.html
Solution 3
6586KB is about the size of an "empty" (freshly created) database. If the tables you dropped were very small or empty, dropping them will not reduce the size by much if any.
When I drop a populated large table, the reduction in database size (as seen by psql's "\l+" command) is reflected near instantaneously, with no need for a vacuum or a checkpoint.
Beautiful Mind
Updated on June 23, 2022Comments
-
Beautiful Mind almost 2 years
I dropped a couple of tables from my Postgres database. However, before dropping tables the size of the database was 6586kB, and after dropping the table size of the database remains same. I think size should be reduced.
What do I need to do to get the actual size?
I know about the VACUUM command. Do I need to use that? And how?
-
Beautiful Mind over 10 years6586kB is not empty DB size. Empty DB size is 6562kB. I need to know the data growth. That's why I need original size after dropping tables.
-
jjanes over 10 yearsThe exact size depends on what options it was compiled with, what hardware, etc. Is 34kb really something to worry about? It is a rounding error. If you are trying to figure out what is going on, use a table large enough to matter. If you try to learn based on the smallest possible data size, you will find your knowledge does not extrapolate to reality.
-
Paul Holden over 2 yearsI believe
vacuumdb
is effectively the same as running vacuum by other means. From the documentation: "vacuumdb is a wrapper around the SQL command VACUUM. There is no effective difference between vacuuming and analyzing databases via this utility and via other methods for accessing the server." postgresql.org/docs/9.6/app-vacuumdb.html -
Erwin Brandstetter over 2 years@PaulHolden: Yes, all true. My point was that the only lasting effect on database size could be entries in system catalogs.
vacuumdb -f
(or justVACUUM FULL
) cleans that up - as opposed to justVACUUM
or evenVACUUM FULL
on the table. That's how you get the db down to minimum size, the actual objective of the OP. I clarified. -
Erwin Brandstetter over 2 yearsThe OP did not
DELETE FROM
, butDROP
the table.TRUNCATE
cannot help to reclaim more space there.