-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 01/25/07 15:43, Bill Moran wrote:
> In response to Mark Drago <[EMAIL PROTECTED]>:
[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-----
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
