Re: Controlling Database Growth

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/25/07 15:43, Bill Moran wrote:
> In response to Mark Drago <markdrago(at)mail(dot)com>:
[snip]
> I don't think either of those are good ideas, because they both
> rely on disk limits to trigger drastic changes in database size,
> which will then require drastic maintenance operations (vacuum
> full, reindex) to clean up.
>
> Personally, I think you'd be a lot better off estimating how much
> new data comes in each day, and scheduling a daily delete of old
> data combined with regular vacuum (either via cron or using
> autovacuum) and an occasional reindex.
>
> Add to that some system monitoring via snmp traps -- maybe even
> graphing with mrtg -- to keep an eye on things in case you need
> to adjust the frequency or amount of stuff that's done, and you
> should see the database stabilize at a manageable size.

Agree totally with this.

You could even partition the table (by month, probably) either using
a view of a UNION ALL or with PostgreSQL's built-in partitioning.
Dropping the oldest month would then be a rapid process.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFuUOIS9HxQb37XmcRArJKAJ4u39v+IpTjpCZ6oPSpmfrhkybikACfWrGB
1JM2fokqQafd/yOWGv7vDa8=
=1jNP
-----END PGP SIGNATURE-----