Re: Does it has a way to compact the database size?

Does it has a way to schedule the process for make sure that i'm running
VACUUM often enough? How?
(ie. create some script on PostgreSQL from running VACUUM FULL on 3.00
AM for first date of every month.)

>>> Richard Huxton <dev(at)archonet(dot)com> 3/6/2007 16:50 >>>
Premsun Choltanwanich wrote:
> Dear All,
>
> I use PostgreSQL 8.0.3 for store my data started on Sep'06. Today, I
use
> database_size('name') function for checking my database size and
found that it's
> about 1209715345.
>
> I think that something maybe wrong on my database because I backup my
database
> everyday and the backup size is about 10 MB. So I restore databse
from my
> backup file then use database_size('name') function again and found
that
> database size is about 56642193.
>
> I can said both 1209715345 and 56642193 are coming from the same
database but I
> wonder that why it quiet difference on size.
>
> Any Idea?
> And How can I control or compact my database size for make it
smallest as possibled?

To expand on Andreas' answer.

If you want to get your live database down to 56642193 you'll probably

want to VACUUM FULL and REINDEX the whole database. That should
basically get it as small as possible.

While the database is in use, make sure you are running VACUUM often
enough (and have free-space-map [fsm] settings high enough) to keep
track of freed space in your database files. That way the database size

should stay static.

You'll never get as small as the backup file, because (1) it doesn't
contain any indexes etc. and (2) it's compressed.

--
Richard Huxton
Archonet Ltd

NETsolutions Asia Limited
+66 (2) 237 7247
http://www.nsasia.co.th