pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: snelling@email.marc.usda.gov
Cc: gbarosio@gmail.com
Cc: pgsql-admin@postgresql.org
Subject: Re: 7.4 <-> 8.0
Date: Fri, 16 Sep 2005 11:59:16 -0500
Message-ID: <1126889956.30120.34.camel@state.g2switchworks.com> (raw)
In-Reply-To: <1126886236.12350.106.camel@localhost>
References: <1126811405.12350.29.camel@localhost>
	<f7f6b4c7050915133528861ce5@mail.gmail.com>
	<f7f6b4c705091513473b7def79@mail.gmail.com>
	<1126886236.12350.106.camel@localhost>

On Fri, 2005-09-16 at 10:57, Warren Snelling wrote:
> Guido,
> 
> Thanks for the pointers.  I have routinely vacuumed the db, but not
> using full. 
> 
> By itself, the "vacuum full" run overnight did not free the space I was
> expecting - du still showed the old database taking about 12 Gb disk,
> and the fresh databases taking about 6.5 Gb.  Running "reindex" on one
> large table freed ~5 Gb, so the old db now takes 7 Gb.  Reindexing the
> remaining tables should get the databases to the same size.
> 
> Would you suggest scripting the vacuum full / reindex process to run
> periodically (once a week or so)?  Right now disk space on this machine
> is not an issue, but vacuuming and reindexing occasionally should keep
> the db from consuming too much excess space.

Assuming that you run regular vacuums often enough, and your fsm
settings are high enough to hold on to all the references to the deleted
tuples, you shouldn't need to run either full vacuums or reindexes. 
There are some usage patterns that can result in indexes with very
sparsely populated blocks, such as when you create indexes to the
numbers 1 to 1000, then delete 95% or so of those references, and all
the new ones will be >1000, then repeat, increasing the number all the
time.  But that type of situation is the only that might require
occasional reindexing.



view thread (7+ messages)

Message-ID: <1126889956.30120.34.camel@state.g2switchworks.com>
Permalink:  ../1126889956.30120.34.camel@state.g2switchworks.com/
Also on:    postgresql.org/message-id/1126889956.30120.34.camel@state.g2switchworks.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: smarlowe@g2switchworks.com, snelling@email.marc.usda.gov, gbarosio@gmail.com
  Subject: Re: 7.4 <-> 8.0
  In-Reply-To: <1126889956.30120.34.camel@state.g2switchworks.com>
  References: <1126811405.12350.29.camel@localhost> <f7f6b4c7050915133528861ce5@mail.gmail.com> <f7f6b4c705091513473b7def79@mail.gmail.com> <1126886236.12350.106.camel@localhost> <1126889956.30120.34.camel@state.g2switchworks.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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