Subject: vacuum and routine maintenance docs

From: Scott Marlowe <[email-masked]>
Date: Tue, 17 Jan 2006 11:14:09 -0600

After the most recent post on the general mailing list by someone who
was a bit confused by postgresql's vacuuming and use of the free space
map, I've been reviewing the page on routine maintenance.

It's pretty obvious that this page was originally written for an older
version of PostgreSQL and has had bits and pieces welded onto it over
time to cover newer features, like the autovacuum daemon.

I'd like to rewrite most of the page, or at least the parts referring to
vacuuming and analyzing.  They seem a bit scattered about on the page,
and there's no mention at all of the free space map, something which is
integral to vacuuming the database.

So, before I go of slogging through the snow, a couple of questions.

1:  Is there already a newer version in CVS?  Should I check out the
docs/src directory from there to start?   Or can I just go on what's in
the 8.1 release?

2:  Any input on anyone as to what they feel they'd like to see in the
docs for this section?  

The section on vacuuming and analysis seem kind of light on theory.  I'm
thinking we should mention something about the default statistics or at
least a reference to a page that explains it.

This also leads me to believe that what we cover on the
runtime-config-resource page, because the coverage of the fsm there is
kind of light too.

So, any advice?

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 17 Jan 2006 14:34:28 -0300

> docs/src directory from there to start?   Or can I just go on what's in

I suggest you look at 
http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/

Those are the latest versions of the doc source.  The built versions are
here: http://developer.postgresql.org/docs/postgres/index.html

-- 
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Cuando miro a alguien, más me atrae cómo cambia que quién es" (J. Binoche)

---

From: "Jim C. Nasby" <[email-masked]>
Date: Tue, 17 Jan 2006 16:49:41 -0600

Before you 'put pen to paper', I have two articles that you should look
at. One is on FSM, the other is more about MVCC from the standpoint of
why we use it and why it means needing to vacuum. I don't have the URL's
handy here, but I can post them tomorrow.

On Tue, Jan 17, 2006 at 11:14:09AM -0600, Scott Marlowe wrote:
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: Scott Marlowe <[email-masked]>
Date: Tue, 17 Jan 2006 16:59:29 -0600

Thanks.  Yeah, I'm thinking more of a very brief mention of what the FSM
is, with a link to the other part of the docs that discuss it.  And then
having a link (possibly in both places) or two to more in depth articles
on fsm and fsm tuning.

---

From: "Jim C. Nasby" <[email-masked]>
Date: Tue, 17 Jan 2006 17:11:23 -0600

Sounds good. The articles are part of a Pervasive newsletter, hosted on
pervasive-postgres.com, but they can get posted else where with proper
attribution.

---

From: "Jim C. Nasby" <[email-masked]>
Date: Wed, 18 Jan 2006 16:19:51 -0600

> having a link (possibly in both places) or two to more in depth articles

Got the links...
http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Dec.asp#2
is general info about MVCC and why you need to vacuum.
http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3
is all about the FSM (yeah, I kinda put the horse before the cart...)

I'm working on finding out if the community can use that text verbatim
without attribution, but if nothing else it might provide you with some
ideas.
-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: Scott Marlowe <[email-masked]>
Date: Wed, 18 Jan 2006 17:13:28 -0600

> without attribution, but if nothing else it might provide you with some

Well, I'm kind of tempted to rewrite the whole section.  I'd like to
come up with some objectives of what we're trying to cover there, and
basically start over with all the information that's there plus the
other stuff on fsm and mvcc as it applies to vacuum, or link to it as
needed.

To me, the docs feel kind of like they've evolved over time, so the
ordering and general layout feel klunky.  No slam on the folks who wrote
them to start with, or updated them.  Just makes my tech docs training
come out.

Thanks for the links!

---

From: Jeff Frost <[email-masked]>
Date: Wed, 18 Jan 2006 15:28:51 -0800 (PST)

> other stuff on fsm and mvcc as it applies to vacuum, or link to it as

A couple questions I find difficult to answer using the online docs are:

* Is my autovacuum doing a good enough job?
* What percentage of bloat is reasonable (and how can I tell what my bloat
level is)?
* What transaction loads would require specific autovacuum settings, or manual 
vacuum fulls?

-- 
Jeff Frost, Owner 	<[email-masked]>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954

---

From: Tom Lane <[email-masked]>
Date: Wed, 18 Jan 2006 19:55:07 -0500

> them to start with, or updated them.  Just makes my tech docs training

You're certainly right about the history of the docs, and I agree that a
ground-up rewrite is the only thing that would help much --- trying to
incorporate new separately-written material is just going to be "more of
the same", ISTM, no matter how good that material might be on its own.

Is it reasonable to try to make an outline of how you think the chapter
should go, or would that be premature without more discussion of
objectives?

			regards, tom lane

---

From: "Jim C. Nasby" <[email-masked]>
Date: Wed, 18 Jan 2006 19:14:22 -0600

> should go, or would that be premature without more discussion of

I'm wondering if people feel this is an issue with the docs in general
and isn't limited to just the admin stuff?

I know I certainly have trouble finding things... I generally look for
stuff by going to the appropriate page in the SQL syntax section and
seeing what it links too, but of course there's a lot of things that
aren't (and shouldn't be) linked to from there...
-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: Tom Lane <[email-masked]>
Date: Wed, 18 Jan 2006 20:19:16 -0500

> and isn't limited to just the admin stuff?

Probably, but the admin stuff seems to suffer worst.  In any case, Scott
volunteered to look at redoing that part, and I'm not going to repay the
offer by asking him to redo the whole manual ;-)

			regards, tom lane

---

From: "Joshua D. Drake" <[email-masked]>
Date: Wed, 18 Jan 2006 17:24:47 -0800

> aren't (and shouldn't be) linked to from there...

I find that it is generally difficult to find things in the docs but 
that once you find it the information is usually very good.

There are exceptions as have already been pointed out of course.

Example issues I see:

Why is Managing Database and Routine Database Management separate?

Server configuration is kind of vague... Perhaps PostgreSQL 
configuration? But then why isn't that under Managing Databases :)

Sincerely,

Joshua D. Drake

-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

---

From: "Jim C. Nasby" <[email-masked]>
Date: Wed, 18 Jan 2006 19:31:34 -0600

> offer by asking him to redo the whole manual ;-)

Certainly true. :) But if we're going to start at an outline level I
think it would be enlightening to do a short (as in only 2 levels deep)
re-outline of all the docs and see how it compares to what we have. It
would at least indicate things that should be wholesale moved out of the
admin section...

Plus hopefully we could get others to help. :) I'd certainly lend a
hand.

Is there an easy way to get a 2-level outline out of the sgml?
-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: Neil Conway <[email-masked]>
Date: Thu, 19 Jan 2006 06:54:30 -0500

> Why is Managing Database and Routine Database Management separate?

I assume you mean the "Managing Databases" and "Routine Database
Maintenance Tasks" chapters. I think these chapters are separate because
they address fairly different subject matter. You could call the latter
chapter "Routine Maintenance Tasks" without loss of meaning, as it
doesn't focus on maintaining individual databases per se.

> configuration?

Personally I don't think that's an improvement, although I'm not
completely satisfied with "Server Configuration" either.

> But then why isn't that under Managing Databases :)

Because that chapter describes managing individual databases and
tablespaces, not an entire PostgreSQL instance.

-Neil

---

From: Scott Marlowe <[email-masked]>
Date: Thu, 19 Jan 2006 10:15:19 -0600

> Is there an easy way to get a 2-level outline out of the sgml?

I agree completely.  We don't probably need it in sgml just yet.  From a
40,000 ft perspective, we can break administration up into several large
chunks, and then decide what needs to go in each.  The general "big
topics" we already have seem pretty serviceable.  However, I tend to
think of OS env and server config as being sub topics under
installation.  Under that, we can put the individual subjects
accordingly.

- Installation
-- Documentation scope specification
-- Preparation (setting objectives for the installation)
-- Hardware considerations (emphasizing things like fsyncing and all)
-- OS configuration (i.e. shared memory)
-- Software installation (source versus rpm versus pkg etc.)
-- Cluster initialization (localization issues, location, etc...)
-- Server configuration (i.e. pg_hba / postgresql.conf et. al.)
-- Running the server (start up scripts, by hand, etc.)
-- Verifying server operation (regression tests)

- Management
-- Databases
-- Users
-- Roles and Privileges

- Maintenance
-- Backup and Restore
-- PITR
-- Replication (where to look for it.)
-- Monitoring
-- Routine Maintenance (vacuum, analyze, etc...)

- Troubleshooting
 (I'm not sure what to put under here right now...  Will think on it.)

Please, feel free to rip this up and reassemble as necessary.  But I do
think it's important to regroup our subjects under a few very broad
topics.

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 19 Jan 2006 18:00:36 +0100

> Is there an easy way to get a 2-level outline out of the sgml?

How would you define the TOC that we currently produce?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---

From: "Jim C. Nasby" <[email-masked]>
Date: Thu, 19 Jan 2006 15:51:48 -0600

> How would you define the TOC that we currently produce?

Sorry, I guess what I was actually thinking of is a 3-level one. In any
case, I'm sure I can figure it out from whatever code generates
index.html...
-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: "Jim C. Nasby" <[email-masked]>
Date: Thu, 19 Jan 2006 16:00:41 -0600

> I agree completely.  We don't probably need it in sgml just yet.  From a

Actually, I was looking for how to get the view out of the existing SGML
;) In any case, I think admin is probably broad enough that there won't
be much overlap with other sections.

> -- Verifying server operation (regression tests)

Should probably mention contrib in here somewhere... and other
resources, like pgFoundry. Afterall, the install section is somewhat of
someone's introduction to PostgreSQL...

> -- Roles and Privileges

Somewhere in one or both of the above should probably be some discussion
on security practices... Also, pg_hba.conf is a bit of a stickler,
because it is closely related to users and authentication.
postgresql.conf is also a bit tricky, because many of it's settings
require knowledge from other areas. Maybe the best way to deal with
these is extensive cross-linking? IE: each postgresql.conf item (or set
of items) should have a link back to whatever section explains it in
detail.
-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: Scott Marlowe <[email-masked]>
Date: Thu, 19 Jan 2006 16:51:34 -0600

> someone's introduction to PostgreSQL...

Good point.  I'll add that somewhere in there under software
installation.

> of items) should have a link back to whatever section explains it in

Yeah, I keep thinking more and more we need a LOT of linkable resources
for this, much like the ones we have for all the standard command
reference stuff.  I'm leaning towards having the main page of each of
these things be somewhat closer to an executive summary (not that light
on info, but you know what I mean) and have links to more info for each
subject.  So that each section can have a much more in depth coverage
but not make it a huge slog to get through the documentation.

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Jan 2006 01:10:30 -0500

> but not make it a huge slog to get through the documentation.

The refrain that I keep hearing is that the info is in there but it's
not so easy to find.  So this sounds like a plan to me: quick overviews
with links should make it easier to find the parts people need to read.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 2 Mar 2006 16:42:07 -0500 (EST)

Any update on this effort?

---------------------------------------------------------------------------

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

---

From: Scott Marlowe <[email-masked]>
Date: Mon, 13 Mar 2006 10:29:16 -0600

Sorry for the long delay.  1:  We've been busy as a one legged man in a
butt kicking contest at work and 2:  My laptop, which I use for all this
kind of stuff, died and I just got it back a week ago.  I will work on
it this week, promise.

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 7 Jun 2006 08:58:02 -0400 (EDT)

Any progress on this?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com