>>> in our db system (for a website), i notice performance boosts after >>> a vacuum >>> full. but then, a VACUUM FULL takes 50min+ during which the db is >>> not really >>> accessible to web-users. is there another way to perform >>> maintenance tasks >>> AND leaving the db fully operable and accessible? >> >> You're not doing regular vacuums often enough. By the way, you can get that VACUUM FULL to be "less injurious" if you collect a list of tables: pubs=# select table_schema, table_name from information_schema.tables where table_type = 'BASE TABLE'; And then VACUUM FULL table by table. It'll take the same 50 minutes; it'll be more sporadically "unusable" which may turn out better. But that's just one step better; you want more steps :-). > well, shouldn't autovacuum take care of "regular" vacuums? in addition > to autovacuum, tables with data changes are vacuumed and reindexed > once a day - > still performance seems to degrade slowly until a vacuum full is > initiated... could an additional daily vacuum over the entire db (even > on tables that only get data added, never changed or removed) help? Tables which never see updates/deletes don't need to get vacuumed very often. They should only need to get a periodic ANALYZE so that the query optimizer gets the right stats. There are probably many tables where pg_autovacuum is doing a fine job. What you need to do is to figure out which tables *aren't* getting maintained well enough, and see about doing something special to them. What you may want to do is to go table by table and, for each one, do two things: 1) VACUUM VERBOSE, which will report some information about how much dead space there is on the table. 2) Contrib function pgstattuple(), which reports more detailed info about space usage (alas, for just the table). You'll find, between these, that there are some tables that have a LOT of dead space. At that point, there may be three answers: a) PG 8.1 pg_autovacuum allows you to modify how often specific tables are vacuumed; upping the numbers for the offending tables may clear things up b) Schedule cron jobs to periodically (hourly? several times per hour?) VACUUM the "offending" tables c) You may decide to fall back to VACUUM FULL; if you do so just for a small set of tables, the "time of pain" won't be the 50 minutes you're living with now... Try a), b), and c) in order on the "offending" tables as they address the problem at increasing cost... -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://linuxdatabases.info/info/x.html "Listen, strange women, lyin' in ponds, distributin' swords, is no basis for a system of government. Supreme executive power derives itself from a mandate from the masses, not from some farcical aquatic ceremony." -- Monty Python and the Holy Grail
