November 5th, 2013, 09:08 AM
DB size is 30GB by the file system is ~250GB in size.
I am a newbie to postgresql DB's and also to this forum. I just took over an application that has postgresql DB 8.1 as the back end DB.
The DB from application is only 30 GB, but the data filesystem is ~250 GB with 99% utilized.
I tried to search and see if i can fix it, but my changes resulted nothing.
Below is my configuration: postgresql.conf
Also if i look at which folder is consuming a lot of storage space.
Any help on this issue is much appreciated.
November 7th, 2013, 10:02 AM
It is not uncommon for databases to requiremany times their own size in diskspace, because of a neat feature called "indexes". If you have a 10GB table and you index the majority of it's columns in four different ways, yo will create around 10GB of diskspace per index; 40GB of extra space will be required.
The PostgreSQL Wiki has a few queries that show exactly how much space is required for each element:
Another reason may be bloating; if there has not been enouch vacuumm activity your datafiles may contain lots of empty space that is never reclaimed:
And to find out if/where there is bloating going on:
Whatever you do, do not blindly start running vacuum and definately do not remove any indexes to save space. PostgreSQL can track index usage over time so you can see exactly which indexes are used and which are not.