up vote 0 down vote favorite

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?

share|improve this question
1  
What's with all the tags? – PeeHaa Sep 24 '13 at 22:33
2  
What's your actual version? Please fix the tags. How do you measure size? Provide more details please. – Erwin Brandstetter Sep 24 '13 at 23:28
    
I measure the size through select pg_size_pretty(pg_database_size(databaseName)); command – S.M.Shamimul Hasan Sep 24 '13 at 23:38
up vote 1 down vote

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.

share|improve this answer
    
6586kB 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. – S.M.Shamimul Hasan Sep 24 '13 at 23:24
1  
The 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. – jjanes Sep 25 '13 at 2:54
up vote 1 down vote

VACUUM (or VACUUM FULL) is hardly useful in this case, since it only reclaims space from within tables.

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 the client tool vacuumdb with the --full option:

vacuumdb -f mydb

On Postgres 9.0 or later, this also rewrites indices in prestine condition. Details in the Postgres Wiki: http://wiki.postgresql.org/wiki/VACUUM_FULL

That's how Postgres measures db size: with dedicated object size functions:

SELECT pg_size_pretty(pg_database_size(mydb));
share|improve this answer
up vote 1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.