From: Greg Williamson <gwilliamson39@yahoo.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Index Bloat Problem
Date: Sat, 18 Aug 2012 01:01:44 -0700 (PDT)
Message-ID: <1345276904.17534.YahooMailNeo@web125902.mail.ne1.yahoo.com> (raw)
In-Reply-To: <CAMkU=1ysTfuS+7DkvmhASN4rzzTJLGxkb4QKd-r6gFV6jkwwBQ@mail.gmail.com>
References: <CADKbJJW0aCD_jp6Dy1j-VUP-zHSEvUkyfJ0Az3T1NnLX8tu_vw@mail.gmail.com>
<CAMkU=1z=jOw0BUuxoxYDtcaq+GCdBamN_47TjkZTFkZKJCCnCg@mail.gmail.com>
<CADKbJJVm8XhauJRiFEN6d9opGTy689-w0ZVhLrHbqL_Eh_d2Wg@mail.gmail.com>
<CAMkU=1ysTfuS+7DkvmhASN4rzzTJLGxkb4QKd-r6gFV6jkwwBQ@mail.gmail.com>
Thanks for this description--we have index bloat problems on a massively active (but small) database.This may help shed light on our problems.
Sorry for top-posting--challenged email reader.
Greg W.
>________________________________
> From: Jeff Janes <jeff.janes@gmail.com>
>To: Strahinja Kustudić <strahinjak@nordeus.com>
>Cc: pgsql-performance@postgresql.org
>Sent: Friday, August 17, 2012 7:33 PM
>Subject: Re: [PERFORM] Index Bloat Problem
>
>On Thu, Aug 16, 2012 at 12:57 PM, Strahinja Kustudić
><strahinjak@nordeus.com> wrote:
>>
>> @Jeff I'm not sure if I understand what you mean? I know that we never reuse
>> key ranges. Could you be more clear, or give an example please.
>
>If an index leaf page is completely empty because every entry on it
>were deleted, it will get recycled to be used in some other part of
>the index. (Eventually--it can take a while, especially if you have
>long-running transactions).
>
>But if the leaf page is only mostly empty, because only most of
>entries on it were deleted, than it can never be reused, except for
>entries that naturally fall into its existing key range (which will
>never happen, if you never reuse key ranges)
>
>So if you have a million records with keys 1..1000000, and do a
>"delete from foo where key between 1 and 990000", then 99% of those
>old index pages will become completely empty and eligible for reuse.
>But if you do "delete from foo where key%100>0", then all of the pages
>will become 99% empty, and none will be eligible for reuse (except the
>very last one, which can still accept 1000001 and so on)
>
>There has been talk of allowing logically adjacent, mostly empty
>pages to be merged so that one of them becomes empty, but the way
>concurrent access to btree indexes was designed this is extremely hard
>to do safely.
>
>Cheers,
>
>Jeff
>
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
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: gwilliamson39@yahoo.com
Subject: Re: Index Bloat Problem
In-Reply-To: <1345276904.17534.YahooMailNeo@web125902.mail.ne1.yahoo.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