From: Tom Lane <tgl@sss.pgh.pa.us>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Tomas Vondra <tv@fuzzy.cz>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Bloated tables and why is vacuum full the only option
Date: Sun, 09 Feb 2014 14:40:00 -0500
Message-ID: <19897.1391974800@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGTBQpZH5eFK0L62U_2YRJNcwDoxEwv9qs+5DF31+0Vcx9MVxw@mail.gmail.com>
References: <CAGTBQpYO0xK6_RExbEgCpe782uSRq_0sdZ4jxh=hSotAYj94xg@mail.gmail.com>
<52F7A3AC.2090905@fuzzy.cz>
<CAGTBQpZH5eFK0L62U_2YRJNcwDoxEwv9qs+5DF31+0Vcx9MVxw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Claudio Freire <klaussfreire@gmail.com> writes:
>>> I also do routine reindexing to stop index bloat on its tracks, yet
>>> freshly-reindexed indexes get considerably reduced in size with vacuum
>>> full.
AFAIK there's no reason for vacuum full to produce a different result
from reindex. Did you mean to say that the indexes get smaller than
what they had been after some normal operation? If so it's worth noting
this comment from the btree index building code (nbtsort.c):
* It is not wise to pack the pages entirely full, since then *any*
* insertion would cause a split (and not only of the leaf page; the need
* for a split would cascade right up the tree). The steady-state load
* factor for btrees is usually estimated at 70%. We choose to pack leaf
* pages to the user-controllable fill factor (default 90%) while upper pages
* are always packed to 70%. This gives us reasonable density (there aren't
* many upper pages if the keys are reasonable-size) without risking a lot of
* cascading splits during early insertions.
As the comment notes, the initial state of a freshly-built index is packed
more densely than what you can expect after a lot of insertions/updates
have occurred. That's not a bug, it's just a fact of life.
Also, there are certain usage patterns that can result in btree indexes
having densities much lower than the conventional-wisdom 70%. The main
one I've seen in practice is "decimation", where you delete say 99 out
of every 100 entries in index order. This leaves just a few live entries
in each leaf page --- but our btree code doesn't reclaim an index page
for recycling until it's totally empty. So you can end up with a very
low load factor after doing something like that, and a reindex is the
only good way to fix it.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, klaussfreire@gmail.com, tv@fuzzy.cz
Subject: Re: Bloated tables and why is vacuum full the only option
In-Reply-To: <19897.1391974800@sss.pgh.pa.us>
* 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