Обсуждение: Difference in DB size with dump and pg_database_size

Hello,
any one can explain me why I have difference between db size when I
dump db, I see it's 5G and when I run SELECT
pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize; on my DB
postgresql return: 10 GB

I run vacuum on db every night. Why is that huge difference in size ?

--
Regards,
Condor

On Tue, Jul 5, 2011 at 10:38, Condor <condor@stz-bg.com> wrote:
> Hello,
> any one can explain me why I have difference between db size when I dump db,
> I see it's 5G and when I run SELECT
> pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize; on my DB
> postgresql return: 10 GB
>
> I run vacuum on db every night. Why is that huge difference in size ?

The dump does not include indexes - for each index it just contains
the CREATE INDEX statement, not the actual data in the index.

And perhaps you've also compressed your dumps? Though in that case,
the difference would probably be bigger...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Tue, 5 Jul 2011 10:43:38 +0200, Magnus Hagander wrote:
> On Tue, Jul 5, 2011 at 10:38, Condor <condor@stz-bg.com> wrote:
>> Hello,
>> any one can explain me why I have difference between db size when I
>> dump db,
>> I see it's 5G and when I run SELECT
>> pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize; on
>> my DB
>> postgresql return: 10 GB
>>
>> I run vacuum on db every night. Why is that huge difference in size
>> ?
>
> The dump does not include indexes - for each index it just contains
> the CREATE INDEX statement, not the actual data in the index.
>
> And perhaps you've also compressed your dumps? Though in that case,
> the difference would probably be bigger...
>
> --
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/


Thank you for your fast replay. Yes, I gzip my DB but that file size is
when I uncompress the gziped file. Anyway, I got the answer. Thank you.

--
Regards,
Condor

> On Tue, Jul 5, 2011 at 10:38, Condor <condor@stz-bg.com> wrote:
>> Hello,
>> any one can explain me why I have difference between db size when I dump
>> db,
>> I see it's 5G and when I run SELECT
>> pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize; on my DB
>> postgresql return: 10 GB
>>
>> I run vacuum on db every night. Why is that huge difference in size ?
>
> The dump does not include indexes - for each index it just contains
> the CREATE INDEX statement, not the actual data in the index.

There are other possible causes, although indexes are usually the most
significant one.

For example each row has a certain overhead (about 20B), and with narrow
tables (one or two fixed-length columns) this may easily double the table
size.

Encoding of numbers is another thing. For example an INT always takes 4B
in the table (unless it's NULL), but dump it's printed as string. So for
example '0' takes 1B only and '83493498' takes 8B. I've seen databases
where the dump was tiny compared to the database, as all the INT values
were small.

Tomas