Thread: About vacuuming
Questions regarding PostgreSQL 7.1.3 (if things are different with 7.2 I'd like to know that too :) How often one should vacuum his db? Is vacuum --analyze for just providing information or does it somehow store it's info for postgresql's use (in other words: should I run it too, and if so, should I do it before or after the real vacuum?). Should postmaster be running or not when vacuuming? Regards, Peter
How often depends on how busy your db is. But I'd recommend running it at least once a day. Vacuum analyze updates the statistics that the db uses to determine various query plans, so I'd always run it as vacuum analyze, rather than just vacuum. Leave postmater running when you run it. There's no need to take it offline. Consider running it as a cron job. Tim ----- Original Message ----- From: "Peter Peltonen" <peter.peltonen@fivetec.com> To: <pgsql-general@postgresql.org> Sent: Wednesday, February 27, 2002 7:12 AM Subject: [GENERAL] About vacuuming > Questions regarding PostgreSQL 7.1.3 (if things are different with 7.2 I'd > like to know that too :) > > How often one should vacuum his db? > > Is vacuum --analyze for just providing information or does it somehow > store it's info for postgresql's use (in other words: should I run it too, > and if so, should I do it before or after the real vacuum?). > > Should postmaster be running or not when vacuuming? > > Regards, > Peter > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
When a row is updated it is really inserted (and old copy deleted). Also, you app may sometimes delete rows. After a while there will be much unusable space because of all the deletes. Vacuum recovers disk space so it can be reused. If you run 'vacuum analyse', you run the basic vacuum as just described, but also you gather statistics that the optimiser uses when evaluating your SQL statements. Decisisons about whether to use seq-scan or and index are based on these statistics. Unless something changed in recent versions, you can only run analyse as part of vacuum. Leave postmaster running. How often? Depends on your system (don't you hate this type of answer?). My system is doing almost nothing when it's dark out, so running every night is not a problem. Some databases on my system see so little avtivity, I only vacuum weekly. I've of systems that run it several times per day. Frank At 05:12 PM 2/27/02 +0200, Peter Peltonen wrote: >Questions regarding PostgreSQL 7.1.3 (if things are different with 7.2 I'd >like to know that too :) > >How often one should vacuum his db? > >Is vacuum --analyze for just providing information or does it somehow >store it's info for postgresql's use (in other words: should I run it too, >and if so, should I do it before or after the real vacuum?). > >Should postmaster be running or not when vacuuming? > >Regards, >Peter
On Wed, 2002-02-27 at 22:32, Frank Bax wrote: > Unless something changed in > recent versions, you can only run analyse as part of vacuum. This is no longer the case with PostgreSQL 7.2 -- there is a separate ANALYZE command. Check the 7.2 docs for more info on the changes to VACUUM in this release. Basically, vacuuming is now substantially less onerous: it is faster and doesn't require an exclusive lock on the table. ANALYZE now uses statistical sampling for large tables (instead of scanning every row), so it should be significantly faster as well. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Just to further this a bit -- running or not running VACUUM ANALYZE can have huge performance implications. Some queries were taking extremely long times to execute on our database. Running a VACUUM ANALYZE (which had not ever been done on this database, despite having inserted hundreds of thousands of records) led to at least an order of magnitude improvement in the performance of the query. Basically, the optimizer was working with no good information before the VACUUM ANALYZE; now it's got all kinds of statistics to improve its optimization. This particular query was joining about 5 tables, so there were lots of opportunities for optimization. Jason Priebe Mi-Co http://www.mi-corporation.com/ > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tim Barnard > > Vacuum analyze updates the statistics that the db uses to > determine various > query plans, so I'd always run it as vacuum analyze, rather than just > vacuum. > > ----- Original Message ----- > From: "Peter Peltonen" <peter.peltonen@fivetec.com> > > > Is vacuum --analyze for just providing information or does > it somehow > > store it's info for postgresql's use (in other words: > should I run it too, > > and if so, should I do it before or after the real vacuum?). > >