PostgreSQL drop table command is not reducing DB size

sql postgresql postgresql-9.1 postgresql-9.2 postgresql-8.4
Beautiful Mind picture Beautiful Mind · Sep 25, 2013 · Viewed 10.7k times · Source

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?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Sep 25, 2013

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));

Related questions

PostgreSQL: How to list all available datatypes?

Question: In PostgreSQL (using SQL, not the console), how can I list all available datataypes ? Ideally like this: http://www.java2s.com/Code/PostgreSQL/Postgre-SQL/Displaysalldatatypesintheconnecteddatabasewithcomments.htm It should also list user defined types, if there are any. Just …

Read More
How to use SQL LIKE condition with multiple values in PostgreSQL?

Is there any shorter way to look for multiple matches: SELECT * from table WHERE column LIKE "AAA%" OR column LIKE "BBB%" OR column LIKE "CCC%" This questions applies to PostgreSQL 9.1, but if there is a generic solution it would be …

Read More
Strange PostgreSQL "value too long for type character varying(500)"

I have a Postgres schema which looks like: The problem is that whenever I save text longer than 500 characters in the description column I get the error: value too long for type character varying(500) In the documentation for Postgres it …

Read More