pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dawid Kuroczko <qnex42@gmail.com>
To: pgsql-performance@postgresql.org
Subject: PostgreSQL clustering VS MySQL clustering
Date: Sat, 22 Jan 2005 14:18:27 +0100
Message-ID: <758d5e7f0501220518477cedfa@mail.gmail.com> (raw)
In-Reply-To: <758d5e7f050122011713f9e27f@mail.gmail.com>
References: <41F144FE.30902@outputservices.com>
	<41F14758.4030602@outputservices.com>
	<20050122.121300.41631131.t-ishii@sra.co.jp>
	<758d5e7f050122011713f9e27f@mail.gmail.com>

On Sat, 22 Jan 2005 12:13:00 +0900 (JST), Tatsuo Ishii
<t-ishii@sra.co.jp> wrote:
> IMO the bottle neck is not WAL but table/index bloat. Lots of updates
> on large tables will produce lots of dead tuples. Problem is, There'
> is no effective way to reuse these dead tuples since VACUUM on huge
> tables takes longer time. 8.0 adds new vacuum delay
> paramters. Unfortunately this does not help. It just make the
> execution time of VACUUM longer, that means more and more dead tuples
> are being made while updating.
>
> Probably VACUUM works well for small to medium size tables, but not
> for huge ones. I'm considering about to implement "on the spot
> salvaging dead tuples".

Quick thought -- would it be to possible to implement a 'partial VACUUM'
per analogiam to partial indexes?

It would be then posiible to do:
VACUUM footable WHERE footime < current_date - 60;
after a statement to DELETE all/some rows older than 60 days.

The VACUUM would check visibility of columns which are mentioned
in an index (in this case: footable_footime_index ;)).

Of course it is not a great solution, but could be great for doing
housecleaning after large update/delete in a known range.

...and should be relatively simple to implement, I guess
(maybe without 'ANALYZE' part).

  Regards,
      Dawid



view thread (131+ messages)  latest in thread

Message-ID: <758d5e7f0501220518477cedfa@mail.gmail.com>
Permalink:  ../758d5e7f0501220518477cedfa@mail.gmail.com/
Also on:    postgresql.org/message-id/758d5e7f0501220518477cedfa@mail.gmail.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: qnex42@gmail.com
  Subject: Re: PostgreSQL clustering VS MySQL clustering
  In-Reply-To: <758d5e7f0501220518477cedfa@mail.gmail.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