pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedRemove recommendation for nightly VACUUM
6+ messages / 3 participants
[nested] [flat]
* Remove recommendation for nightly VACUUM
@ 2022-06-27 06:31 Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2022-06-27 06:31 UTC (permalink / raw)
To: pgsql-docs
A customer recently pointed me to
https://www.postgresql.org/docs/current/sql-vacuum.html
and asked if I agree with the statement there that a nightly
scheduled VACUUM were a good idea:
"We recommend that active production databases be vacuumed frequently
(at least nightly), in order to remove dead rows. After adding or
deleting a large number of rows, it might be a good idea to issue a
VACUUM ANALYZE command for the affected table. This will update the
system catalogs with the results of all recent changes, and allow the
PostgreSQL query planner to make better choices in planning queries."
Looking at the Git history, most of that paragraph is from a time
when autovacuum did not yet exist or was much less reliable than it
is now. So I suggest removing all that and pointing to autovacuum
instead, as done in the attached patch.
Yours,
Laurenz Albe
Attachments:
[text/x-patch] 0001-Remove-outdated-recommendation-for-manual-VACUUM.patch (2.4K, ../../6f5e3da98fec14640f389d7b84c3b413833697f4.camel@cybertec.at/2-0001-Remove-outdated-recommendation-for-manual-VACUUM.patch)
download | inline diff:
From efd3c4080c48bd8c3512fb7f6a215fe96a3912ce Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Mon, 27 Jun 2022 08:25:07 +0200
Subject: [PATCH] Remove outdated recommendation for manual VACUUM
We have been having a working and highly tunable autovacuum
for at least a decade now, so remove the recommendation to
vacuum tables at least every night.
Autovacuum is now also triggered by INSERTs, so we can also
remove the recommendation to run VACUUM (ANALYZE) after lots
of INSERTs or DELETEs.
Instead, suggest using autovacuum by moving the respective
paragraph up to where the importance of VACUUM is emphasized.
---
doc/src/sgml/ref/vacuum.sgml | 20 +++++---------------
1 file changed, 5 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 3df32b58ee..68c4450c29 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -377,15 +377,11 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</para>
<para>
- We recommend that active production databases be
- vacuumed frequently (at least nightly), in order to
- remove dead rows. After adding or deleting a large number
- of rows, it might be a good idea to issue a <command>VACUUM
- ANALYZE</command> command for the affected table. This will update the
- system catalogs with
- the results of all recent changes, and allow the
- <productname>PostgreSQL</productname> query planner to make better
- choices in planning queries.
+ We recommend that active production databases be vacuumed regularly in
+ order to remove dead rows. <productname>PostgreSQL</productname> includes
+ an <quote>autovacuum</quote> facility which can automate routine vacuum
+ maintenance. For more information about automatic and manual vacuuming,
+ see <xref linkend="routine-vacuuming"/>.
</para>
<para>
@@ -412,12 +408,6 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
details.
</para>
- <para>
- <productname>PostgreSQL</productname> includes an <quote>autovacuum</quote>
- facility which can automate routine vacuum maintenance. For more
- information about automatic and manual vacuuming, see
- <xref linkend="routine-vacuuming"/>.
- </para>
<para>
Each backend running <command>VACUUM</command> without the
<literal>FULL</literal> option will report its progress in the
--
2.35.3
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Remove recommendation for nightly VACUUM
@ 2022-06-27 08:31 Magnus Hagander <magnus@hagander.net>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 6+ messages in thread
From: Magnus Hagander @ 2022-06-27 08:31 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs
On Mon, Jun 27, 2022 at 8:31 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> A customer recently pointed me to
> https://www.postgresql.org/docs/current/sql-vacuum.html
> and asked if I agree with the statement there that a nightly
> scheduled VACUUM were a good idea:
>
> "We recommend that active production databases be vacuumed frequently
> (at least nightly), in order to remove dead rows. After adding or
> deleting a large number of rows, it might be a good idea to issue a
> VACUUM ANALYZE command for the affected table. This will update the
> system catalogs with the results of all recent changes, and allow the
> PostgreSQL query planner to make better choices in planning queries."
>
> Looking at the Git history, most of that paragraph is from a time
> when autovacuum did not yet exist or was much less reliable than it
> is now. So I suggest removing all that and pointing to autovacuum
> instead, as done in the attached patch.
>
Agred. A nightly vacuum is definitely not something for "most people"
anymore.
But also. "active production databases". Surely we recommend regular vacuum
in *all* databases when it's primarily driven by autovacuum? At least all
active. But there's nothing special about "production"? Since we're
tweaking the wording, I would suggest removing that reference as well.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Remove recommendation for nightly VACUUM
@ 2022-06-27 08:52 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2022-06-27 08:52 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: pgsql-docs
On Mon, 2022-06-27 at 10:31 +0200, Magnus Hagander wrote:
> On Mon, Jun 27, 2022 at 8:31 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > A customer recently pointed me to
> > https://www.postgresql.org/docs/current/sql-vacuum.html
> > and asked if I agree with the statement there that a nightly
> > scheduled VACUUM were a good idea:
>
> Agred. A nightly vacuum is definitely not something for "most people" anymore.
>
> But also. "active production databases". Surely we recommend regular vacuum in *all*
> databases when it's primarily driven by autovacuum? At least all active. But
> there's nothing special about "production"? Since we're tweaking the wording,
> I would suggest removing that reference as well.
You are right. Done in the attached.
Yours,
Laurenz Albe
Attachments:
[text/x-patch] 0001-Remove-outdated-recommendation-for-manual-VACUUM.V2.patch (2.4K, ../../962fbfac0c3e10b418d8ddfcf4109360d0cce380.camel@cybertec.at/2-0001-Remove-outdated-recommendation-for-manual-VACUUM.V2.patch)
download | inline diff:
From 1f2dd793a522e8e16970d40421264d5dc8aa5e10 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Mon, 27 Jun 2022 10:50:12 +0200
Subject: [PATCH] Remove outdated recommendation for manual VACUUM
We have been having a working and highly tunable autovacuum
for at least a decade now, so remove the recommendation to
vacuum tables at least every night.
Autovacuum is now also triggered by INSERTs, so we can also
remove the recommendation to run VACUUM (ANALYZE) after lots
of INSERTs or DELETEs.
Instead, suggest using autovacuum by moving the respective
paragraph up to where the importance of VACUUM is emphasized.
---
doc/src/sgml/ref/vacuum.sgml | 20 +++++---------------
1 file changed, 5 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 3df32b58ee..c582021d29 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -377,15 +377,11 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</para>
<para>
- We recommend that active production databases be
- vacuumed frequently (at least nightly), in order to
- remove dead rows. After adding or deleting a large number
- of rows, it might be a good idea to issue a <command>VACUUM
- ANALYZE</command> command for the affected table. This will update the
- system catalogs with
- the results of all recent changes, and allow the
- <productname>PostgreSQL</productname> query planner to make better
- choices in planning queries.
+ We recommend that all databases be vacuumed regularly in
+ order to remove dead rows. <productname>PostgreSQL</productname> includes
+ an <quote>autovacuum</quote> facility which can automate routine vacuum
+ maintenance. For more information about automatic and manual vacuuming,
+ see <xref linkend="routine-vacuuming"/>.
</para>
<para>
@@ -412,12 +408,6 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
details.
</para>
- <para>
- <productname>PostgreSQL</productname> includes an <quote>autovacuum</quote>
- facility which can automate routine vacuum maintenance. For more
- information about automatic and manual vacuuming, see
- <xref linkend="routine-vacuuming"/>.
- </para>
<para>
Each backend running <command>VACUUM</command> without the
<literal>FULL</literal> option will report its progress in the
--
2.35.3
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Remove recommendation for nightly VACUUM
@ 2022-06-27 15:35 Peter Geoghegan <pg@bowt.ie>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 1 reply; 6+ messages in thread
From: Peter Geoghegan @ 2022-06-27 15:35 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-docs
On Mon, Jun 27, 2022 at 1:32 AM Magnus Hagander <magnus@hagander.net> wrote:
>> Looking at the Git history, most of that paragraph is from a time
>> when autovacuum did not yet exist or was much less reliable than it
>> is now. So I suggest removing all that and pointing to autovacuum
>> instead, as done in the attached patch.
>
>
> Agred. A nightly vacuum is definitely not something for "most people" anymore.
+1.
The VACUUM documentation needs to be totally overhauled. This is a start.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Remove recommendation for nightly VACUUM
@ 2022-06-27 17:48 Magnus Hagander <magnus@hagander.net>
parent: Peter Geoghegan <pg@bowt.ie>
0 siblings, 1 reply; 6+ messages in thread
From: Magnus Hagander @ 2022-06-27 17:48 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-docs
On Mon, Jun 27, 2022 at 5:36 PM Peter Geoghegan <pg@bowt.ie> wrote:
> On Mon, Jun 27, 2022 at 1:32 AM Magnus Hagander <magnus@hagander.net>
> wrote:
> >> Looking at the Git history, most of that paragraph is from a time
> >> when autovacuum did not yet exist or was much less reliable than it
> >> is now. So I suggest removing all that and pointing to autovacuum
> >> instead, as done in the attached patch.
> >
> >
> > Agred. A nightly vacuum is definitely not something for "most people"
> anymore.
>
> +1.
>
> The VACUUM documentation needs to be totally overhauled. This is a start.
>
Agred, let's do partial improvement and not wait for a rewrite. Thus,
applied! Thanks!
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Remove recommendation for nightly VACUUM
@ 2022-06-27 20:06 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2022-06-27 20:06 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; Peter Geoghegan <pg@bowt.ie>; +Cc: pgsql-docs
On Mon, 2022-06-27 at 19:48 +0200, Magnus Hagander wrote:
> On Mon, Jun 27, 2022 at 5:36 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > On Mon, Jun 27, 2022 at 1:32 AM Magnus Hagander <magnus@hagander.net> wrote:
> > > > Looking at the Git history, most of that paragraph is from a time
> > > > when autovacuum did not yet exist or was much less reliable than it
> > > > is now. So I suggest removing all that and pointing to autovacuum
> > > > instead, as done in the attached patch.
> > >
> > >
> > > Agred. A nightly vacuum is definitely not something for "most people" anymore.
> >
> > +1.
> >
> > The VACUUM documentation needs to be totally overhauled. This is a start.
>
> Agred, let's do partial improvement and not wait for a rewrite. Thus, applied! Thanks!
Thanks!
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-06-27 20:06 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-27 06:31 Remove recommendation for nightly VACUUM Laurenz Albe <laurenz.albe@cybertec.at>
2022-06-27 08:31 ` Magnus Hagander <magnus@hagander.net>
2022-06-27 08:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-06-27 15:35 ` Peter Geoghegan <pg@bowt.ie>
2022-06-27 17:48 ` Magnus Hagander <magnus@hagander.net>
2022-06-27 20:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox