Re: Difference in DB size with dump and pg_database_size

> On Tue, Jul 5, 2011 at 10:38, Condor <condor(at)stz-bg(dot)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