Re: Index bloat problem?

От: a3a18850@telus.net
Тема: Re: Index bloat problem?
Дата: ,
Msg-id: 1114108908.4267f3ec2e0ea@webmail.telus.net
(см: обсуждение, исходный текст)
Ответ на: Index bloat problem?  (Bill Chandler)
Список: pgsql-performance

Скрыть дерево обсуждения

Index bloat problem?  (Bill Chandler, )
 Re: Index bloat problem?  (Josh Berkus, )
  Re: Index bloat problem?  (Alex Turner, )
   Re: Index bloat problem?  (Josh Berkus, )
 Re: Index bloat problem?  (Bill Chandler, )
  Re: Index bloat problem?  (Josh Berkus, )
  Re: Index bloat problem?  (Josh Berkus, )
   Re: Index bloat problem?  (Michael Guerin, )
    Re: Index bloat problem?  (Josh Berkus, )
     Re: Index bloat problem?  (Dave Chapeskie, )
      Re: Index bloat problem?  (Josh Berkus, )
      Re: Index bloat problem?  ("Jim C. Nasby", )
  Re: Index bloat problem?  (Alex Turner, )
 Re: Index bloat problem?  (Chris Browne, )
 Re: Index bloat problem?  (Bill Chandler, )
  Re: Index bloat problem?  (Mischa Sandberg, )
 Re: Index bloat problem?  (Bill Chandler, )
  Re: Index bloat problem?  (Josh Berkus, )
  Re: Index bloat problem?  (John A Meinel, )
 Re: Index bloat problem?  (Tom Lane, )
  Re: Index bloat problem?  (David Roussel, )
   Re: Index bloat problem?  (Tom Lane, )
    Re: Index bloat problem?  ("David Roussel", )
     Re: Index bloat problem?  (Tom Lane, )
 Re: Index bloat problem?  (, )

Quoting Bill Chandler <>:

> Running PostgreSQL 7.4.2, Solaris.
> Client is reporting that the size of an index is
> greater than the number of rows in the table (1.9
> million vs. 1.5 million).  Index was automatically
> created from a 'bigserial unique' column.

> We have been running 'VACUUM ANALYZE' very regularly.
> In fact, our vacuum schedule has probably been
> overkill.  We have been running on a per-table basis
> after every update (many per day, only inserts
> occurring) and after every purge (one per day,
> deleting a day's worth of data).
>
> What about if an out-of-the-ordinary number of rows
> were deleted (say 75% of rows in the table, as opposed
> to normal 5%) followed by a 'VACUUM ANALYZE'?  Could
> things get out of whack because of that situation?

I gather you mean, out-of-the-ordinary for most apps, but not for this client?

In case nobody else has asked: is your max_fsm_pages big enough to handle all
the deleted pages, across ALL tables hit by the purge? If not, you're
haemorrhaging pages, and VACUUM is probably warning you about exactly that.

If that's not a problem, you might want to consider partitioning the data.
Take a look at inherited tables. For me, they're a good approximation of
clustered indexes (sigh, miss'em) and equivalent to table spaces.

My app is in a similar boat to yours: up to 1/3 of a 10M-row table goes away
every day. For each of the child tables that is a candidate to be dropped, there
is a big prologue txn, whichs moves (INSERT then DELETE) the good rows into a
child table that is NOT to be dropped. Then BANG pull the plug on the tables you
don't want. MUCH faster than DELETE: the dropped tables' files' disk space goes
away in one shot, too.

Just my 2c.



В списке pgsql-performance по дате сообщения:

От: Greg Stark
Дата:
Сообщение: Re: Bad n_distinct estimation; hacks suggested?
От: "Joel Fradkin"
Дата:
Сообщение: Re: Joel's Performance Issues WAS : Opteron vs Xeon