On Wed, 2005-08-31 at 07:18, Bohdan Linda wrote: > > > > From the postgresql manual > > http://www.postgresql.org/docs/8.0/interactive/maintenance.html : > > " The standard form of VACUUM is best used with the goal of maintaining > > a fairly level steady-state usage of disk space. If you need to return > > disk space to the operating system you can use VACUUM FULL ? but what's > > the point of releasing disk space that will only have to be allocated > > again soon? Moderately frequent standard VACUUM runs are a better > > approach than infrequent VACUUM FULL runs for maintaining > > heavily-updated tables." > > > > From this I conclude that an ordinary VACUUM is sufficent to your > > purpose cause you insert/delete almost the same amount of data daily. > > > > But then again I can be mistaken so if anyone can back me up here or > > throw the manual on me will be nice ;P > > > If I vacuum only the table, the records may be used by new lines, that is > fine. Problem is, that when creating select on such table, it takes more > pages to be read from the IO (it will read laso deleted rows) thus the > select will last a bit longer. It really depends on what percentage of rows you're updating. If you are updating 240,000 rows a day, and have a database with 100M rows, then that's not too bad. Regular vacuums once a day would be plenty. If you're updating 240,000 rows a day, spread out over the day, and you have a table that has 10,000 rows, then you will need to run vacuum far more often to keep the table at a steady state of 10,000 to 20,000 rows. If you're doing the 240,000 updates all at once on a small table, then you might well be a candidate for a vacuum full. So, again, it's about percentages really. If 240k rows represents 1% of your table, then daily, regular vacuums will do fine. Personally, I just install pg_autovacuum and check on it once a week or so to make sure it's doing its job. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
