Jim C. Nasby wrote: -- Start of PGP signed section. > From > http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : > > "Recommended practice for most sites is to schedule a database-wide > VACUUM once a day at a low-usage time of day, supplemented by more > frequent vacuuming of heavily-updated tables if necessary. (Some > installations with extremely high update rates vacuum their busiest > tables as often as once every few minutes.) If you have multiple > databases in a cluster, don't forget to VACUUM each one; the program > vacuumdb might be helpful." > > Do we still want that to be our formal recommendation? ISTM it would be > more logical to recommend a combination of autovac, daily vacuumdb -a if > you can afford it and have a quiet period, and frequent manual vacuuming > of things like web session tables. > > I'm happy to come up with a patch, but I figure there should be > consensus first...
I have applied the following patch to emphasize autovacuum rather than administrator-scheduled vacuums. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/maintenance.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v retrieving revision 1.78 diff -c -c -r1.78 maintenance.sgml *** doc/src/sgml/maintenance.sgml 19 Aug 2007 01:41:24 -0000 1.78 --- doc/src/sgml/maintenance.sgml 13 Sep 2007 23:37:50 -0000 *************** *** 59,66 **** </indexterm> <para> ! <productname>PostgreSQL</productname>'s <command>VACUUM</> command ! <emphasis>must</emphasis> be run on a regular basis for several reasons: <orderedlist> <listitem> --- 59,67 ---- </indexterm> <para> ! <productname>PostgreSQL</productname>'s <command>VACUUM</> (<xref ! linkend="sql-vacuum"> command has to run on a regular basis for several ! reasons: <orderedlist> <listitem> *************** *** 78,91 **** <firstterm>transaction ID wraparound</>.</simpara> </listitem> </orderedlist> - - The frequency and scope of the <command>VACUUM</> operations - performed for each of these reasons will vary depending on the - needs of each site. Therefore, database administrators must - understand these issues and develop an appropriate maintenance - strategy. This section concentrates on explaining the high-level - issues; for details about command syntax and so on, see the <xref - linkend="sql-vacuum" endterm="sql-vacuum-title"> reference page. </para> <para> --- 79,84 ---- *************** *** 103,115 **** </para> <para> ! An automated mechanism for performing the necessary <command>VACUUM</> ! operations has been added in <productname>PostgreSQL</productname> 8.1. ! See <xref linkend="autovacuum">. </para> <sect2 id="vacuum-for-space-recovery"> ! <title>Recovering disk space</title> <indexterm zone="vacuum-for-space-recovery"> <primary>disk space</primary> --- 96,109 ---- </para> <para> ! Fortunately, autovacuum (<xref linkend="autovacuum">) monitors table ! activity and performs <command>VACUUM</command>s when necessary. ! Autovacuum works dynamically so it is often better ! administration-scheduled vacuuming. </para> <sect2 id="vacuum-for-space-recovery"> ! <title>Recovering Disk Space</title> <indexterm zone="vacuum-for-space-recovery"> <primary>disk space</primary> *************** *** 129,145 **** </para> <para> - Clearly, a table that receives frequent updates or deletes will need - to be vacuumed more often than tables that are seldom updated. It - might be useful to set up periodic <application>cron</> tasks that - <command>VACUUM</command> only selected tables, skipping tables that are known not to - change often. This is only likely to be helpful if you have both - large heavily-updated tables and large seldom-updated tables — the - extra cost of vacuuming a small table isn't enough to be worth - worrying about. - </para> - - <para> There are two variants of the <command>VACUUM</command> command. The first form, known as <quote>lazy vacuum</quote> or just <command>VACUUM</command>, marks dead data in tables and --- 123,128 ---- *************** *** 167,196 **** </para> <para> ! The standard form of <command>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 ! <command>VACUUM FULL</> — but what's the point of releasing disk ! space that will only have to be allocated again soon? Moderately ! frequent standard <command>VACUUM</> runs are a better approach ! than infrequent <command>VACUUM FULL</> runs for maintaining ! heavily-updated tables. However, if some heavily-updated tables ! have gone too long with infrequent <command>VACUUM</>, you can use <command>VACUUM FULL</> or <command>CLUSTER</> to get performance back (it is much slower to scan a table containing almost only dead rows). </para> <para> ! Recommended practice for most sites is to schedule a database-wide ! <command>VACUUM</> once a day at a low-usage time of day, ! supplemented by more frequent vacuuming of heavily-updated tables ! if necessary. (Some installations with extremely high update rates ! vacuum their busiest tables as often as once every few minutes.) ! If you have multiple databases ! in a cluster, don't forget to <command>VACUUM</command> each one; ! the program <xref linkend="app-vacuumdb" endterm="app-vacuumdb-title"> ! might be helpful. </para> <para> --- 150,185 ---- </para> <para> ! Fortunately, autovacuum (<xref linkend="autovacuum">) monitors table ! activity and performs <command>VACUUM</command>s when necessary. This ! eliminates the need for administrators to worry about disk space ! recovery in all but the most unusual cases. ! </para> ! ! <para> ! For administrators who want to control <command>VACUUM</command> ! themselves, the standard form of <command>VACUUM</> is best used to ! maintain a steady-state usage of disk space. If you need to return ! disk space to the operating system, you can use <command>VACUUM ! FULL</>, but this is unwise if the table will just grow again in the ! future. Moderately-frequent standard <command>VACUUM</> runs are a ! better approach than infrequent <command>VACUUM FULL</> runs for ! maintaining heavily-updated tables. However, if some heavily-updated ! tables have gone too long with infrequent <command>VACUUM</>, you can use <command>VACUUM FULL</> or <command>CLUSTER</> to get performance back (it is much slower to scan a table containing almost only dead rows). </para> <para> ! For those not using autovacuum, one approach is to schedule a ! database-wide <command>VACUUM</> once a day during low-usage period, ! supplemented by more frequent vacuuming of heavily-updated tables if ! necessary. (Some installations with extremely high update rates vacuum ! their busiest tables as often as once every few minutes.) If you have ! multiple databases in a cluster, don't forget to ! <command>VACUUM</command> each one; the program <xref ! linkend="app-vacuumdb" endterm="app-vacuumdb-title"> might be helpful. </para> <para>
---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org