index growth

Alex Mayrhofer <axelm@nona.net> writes:

i have a bunch of indices over rather frequently updated large tables. Those
indices grow in size with the updates, so i frequently re-index them.

That usually shouldn't be necessary since PG 7.4 or so. Do you have
some strange pattern of index key usage that is triggering index bloat,
or is this just a habit leftover from pre-7.4?

regards, tom lane

Tom Lane wrote:

Alex Mayrhofer <axelm@nona.net> writes:

i have a bunch of indices over rather frequently updated large tables. Those
indices grow in size with the updates, so i frequently re-index them.

That usually shouldn't be necessary since PG 7.4 or so. Do you have
some strange pattern of index key usage that is triggering index bloat,
or is this just a habit leftover from pre-7.4?

Hmm, my index definition (running 8.1.0) looks as follows:

Index "public.l_renderjobs_uri_idx"
Column | Type
-------------+------------------------
request_uri | character varying(200)
btree, for table "public.l_renderjobs"

a recent REINDEX reduced that index from about 30000 pages to 18000.

The table contains about 1.5 millions URLs, and i load http usage updates on
the table every day, which modifies around 40000 of those records daily.

So, is this something where bloating should be expected, or am i doing
something wrong?

thanks,

Alex

Alex Mayrhofer <axelm@nona.net> writes:

a recent REINDEX reduced that index from about 30000 pages to 18000.

That's not bloat, that's normal overhead. The traditional rule of thumb
for a btree is that at steady state, pages will be about 2/3rds full.
REINDEX packs pages to 90% IIRC, but you can't expect that that density
will be maintained in the face of heavy update activity. It looks to me
like your index is not so far off the rule-of-thumb load factor, and you
should just not worry, be happy. If you were finding that your indexes
grow to ten times the minimum size, *that* would be worth worrying
about.

regards, tom lane