Re: Help on maintaining pgsql/data folder size

Hi Tom,

> pg_xlog shouldn't grow unreasonably big unless you've somehow turned off
checkpointing.  pg_clog shouldn't grow unreasonably big unless you've
neglected
> appropriate vacuuming procedures (see the manual).  So I think this is
mostly pilot error.

After going through the manual, I changed the checkpoint_segments parameter
in postgresql.conf file to 1 from default of 3. With this setting as per the
manual, there wont be more than 4 segments each of 16 MB. If this is the
case, then there is no issues with the disk space. I don't want it to
increase more than 64 MB. As for as vaccuming is considered...i have written
a script file which will vacuum the database at regular intervals.

> It might be worth your while to reduce the size of xlog segments --- if
you have three or four 8M or 4M segments instead of three or four 16M
segments, that makes a
> difference when you're trying to fit in 512M.
> It would require some fooling with the source code to make that happen in
7.4; you should consider moving to 8.0 where there's just one configuration
#define to adjust.

Well if fooling with the source code is not worth a major risk...then I
would be interested in doing that. In future we may consider 8.0 but as of
now NO.

> In general though I wonder whether you shouldn't have picked another
database.  PG isn't really designed for that sort of environment.

Before deciding on PgSQL, we did consider other databases like mySQL, Sybase
and the likes....but out of them PgSQL seemed to be the best of the lot
because of its support to triggers and stored procedures, so the effort
required is less. Only compromise is on the performance.

> Aside from the fact that we aren't targeting a tiny disk footprint, we
expect to rewrite the WAL files again and again and again.  What's the
write-cycles lifetime spec for > your flash?

Well I have no idea regarding the write-cycles lifetime of the flash...I
have to check out with the hardware ppl.

Regards,
Pradeep