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
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