Thread: How to optimize PostgreSQL database size

The article on the subject can be read here:

  http://linux.inet.hr/optimize_postgresql_database_size.html

Comments most welcome!

""Linux Portal"" <linportal@gmail.com> wrote
> The article on the subject can be read here:
>
>   http://linux.inet.hr/optimize_postgresql_database_size.html
>

After dump/restore the database size is 1685 MB and after
vacuum-full/reindex is 1990 MB. Where we saved 305 MB?

Regards,
Qingqing

On Jun 18, 2006, at 10:47 PM, Qingqing Zhou wrote:
> ""Linux Portal"" <linportal@gmail.com> wrote
>> The article on the subject can be read here:
>>
>>   http://linux.inet.hr/optimize_postgresql_database_size.html
>>
>
> After dump/restore the database size is 1685 MB and after
> vacuum-full/reindex is 1990 MB. Where we saved 305 MB?

My guess would be due to toasted text columns... does VACUUM FULL
know to vacuum the toast tables only after committing the vacuum on
the base heap?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim Nasby <jnasby@pervasive.com> writes:
> On Jun 18, 2006, at 10:47 PM, Qingqing Zhou wrote:
>> After dump/restore the database size is 1685 MB and after
>> vacuum-full/reindex is 1990 MB. Where we saved 305 MB?

> My guess would be due to toasted text columns...

No, it's probably got more to do with the fact that VACUUM FULL stops
trying to compact a table once it finds a row that it can't move down.
There can be quite a lot of empty space left in the table, if one of the
last rows is significantly wider than the average for the table.

            regards, tom lane