Re: How to find space occupied by postgres on harddisk

dipesh wrote:
> Hello,
> Myself Dipesh Mistry from Ahmedabad India.
> I want to know that if i dump the 5GB sql file then how many space does 
> postgres  occupy on harddisk.

Do you mean a 5GB database? If that's what you meant, then the size of 
the resulting dump depends on the dump format, the FILLFACTOR of your 
tables and indices, the number of indices you have, etc.

If you mean that you have a 5GB SQL dump and you want to know how big it 
will be when loaded into PostgreSQL, well, the same applies but in 
reverse. It depends on the table and index fillfactors, how many indexes 
you have, etc.

My database is a bit less than 1GB on disk as stored by PostgreSQL, 
including xlogs, indexes, etc. When I dump it in PostgreSQL's custom 
compressed dump format (pg_dump -Fc) it uses 25MB of storage. It's 
VACUUMed and REINDEXed regularly and has fillfactors of around 60% for 
most tables/indices.

If I use the ordinary uncompressed SQL dump format it uses 140MB.

All this depends on your data. Some data types "expand" more than others  when converted from their SQL dump file
representationto their 
 
representation in PostgreSQL's storage. Some are stored smaller in Pg 
than in an SQL dump. Additionally, indexes use space too, potentially 
LOTS of space. Finally, your tables will "waste" some space with deleted 
rows, padding for non-100% fillfactors, etc.

The best thing to do is load it into PostgreSQL and see (or dump it, if 
that's what you meant). That'll tell you for sure. It's not like a 5GB 
dump will take all that long to load.

--
Craig Ringer