pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Sven Clement <sven@dsign.lu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Kroeger <andrew@sprocks.gotdns.com>
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems with large telemetric datasets on 7.4.2
Date: Mon, 06 Aug 2007 10:51:09 +0100
Message-ID: <46B6EF0D.6030501@enterprisedb.com> (raw)
In-Reply-To: <881035ea0708060010u1bbb4b58r57bdf6f51b12f7d9@mail.gmail.com>
References: <881035ea0708030652r37b25a86g61e88e1f5e95eab2@mail.gmail.com>
	<1186150836.19184.194.camel@archimedes>
	<881035ea0708030857s621af020r4d3d7b994e2ca9a3@mail.gmail.com>
	<46B39A77.8050105@sprocks.gotdns.com>
	<4770.1186180449@sss.pgh.pa.us>
	<881035ea0708041303h4ea256b3q4b40c5291f8cd3b7@mail.gmail.com>
	<46B5F5C7.1070901@enterprisedb.com>
	<881035ea0708060010u1bbb4b58r57bdf6f51b12f7d9@mail.gmail.com>

Sven Clement wrote:
> Partially I found that one in the PostgreSQL Documentation for the
> 7.x.xversions under the command REINDEX where they claim that you
> should run a
> reindex under certain circumstances and for my comprehension this says that
> with some access pattern (as ours (major writes / one big delete per day))
> the index may be corrupted or otherwise not really useful.

Up to 7.3, periodical REINDEX was needed to trim down bloated indexes.
Since 7.4, empty index pages are recycled so that's no longer necessary.
You can still end up with larger than necessary indexes in recent
versions under unusual access patterns, like if you delete all but a few
index tuples from each index page, but it's rare in practice. And it's
not unbounded growth like in <= 7.3.

In any case, the indexes won't become corrupt.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com



view thread (14+ messages)  latest in thread

Message-ID: <46B6EF0D.6030501@enterprisedb.com>
Permalink:  ../46B6EF0D.6030501@enterprisedb.com/
Also on:    postgresql.org/message-id/46B6EF0D.6030501@enterprisedb.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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: heikki@enterprisedb.com, sven@dsign.lu, tgl@sss.pgh.pa.us, andrew@sprocks.gotdns.com, mark.lewis@mir3.com
  Subject: Re: Performance problems with large telemetric datasets on 7.4.2
  In-Reply-To: <46B6EF0D.6030501@enterprisedb.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