Postgres Pro
Downloads
Home   >   mailing lists

Thread: index growth

index growth

From
Alex Mayrhofer
Date:
Hi,

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.

Are there any plans to add REINDEX estimation/jobs to the autovacuum process
- or, alternatively, any options on reducing the growth rate of those
indices (except not updating the respective tables)?

thanks

Alex
----
http://nona.net/features/map/

Re: index growth

From
Tom Lane
Date:
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

Re: index growth

From
Alex Mayrhofer
Date:
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

Re: index growth

From
Tom Lane
Date:
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