Re: [SQL] 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 representation to 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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql