Re: Question on REINDEX

From: Tom Lane
Subject: Re: Question on REINDEX
Date: ,
Msg-id: 23183.1113933754@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Question on REINDEX  (Josh Berkus)
List: pgsql-performance

Tree view

Question on REINDEX  (Bill Chandler, )
 Re: Question on REINDEX  (Josh Berkus, )
  Re: Question on REINDEX  (Tom Lane, )
 Re: Question on REINDEX  (Bruno Wolff III, )
 Re: Question on REINDEX  ("Tambet Matiisen", )
  Re: Question on REINDEX  (Tom Lane, )
   Re: Question on REINDEX  (Alvaro Herrera, )
    Re: Question on REINDEX  (Tom Lane, )
  Re: Question on REINDEX  (Josh Berkus, )
   Re: Question on REINDEX  (Tom Lane, )
    Re: Question on REINDEX  (Josh Berkus, )
     Re: Question on REINDEX  (Tom Lane, )

Josh Berkus <> writes:
>> Not at all.  What it says is that you expect 100% of the pages to have
>> useful amounts of free space, which is a *much* weaker criterion.

> Hmmmm .... actually, it seems like, if you are vacuuming regularly, you only
> *do* need to track pages that have been touched by DELETE or UPDATE.   Other
> pages would have already been vacuumed and not have any useful free space
> left.   Yes?

Well, the space has to be remembered until it's reused.  On the other
hand, there's nothing that says FSM has to be aware of all the free
space available at all times --- the real criterion to avoid bloat
is that after a VACUUM, enough space is logged in FSM to satisfy all
the insertions that will happen before the next VACUUM.  So you could
have situations where free space is temporarily forgotten (for lack
of slots in FSM), but other free space gets used instead, and eventually
a later VACUUM re-finds that free space and puts it into FSM.

I think it's true that the more often you vacuum, the less FSM you need,
but this doesn't have much to do with how much free space is actually
out there on disk.  It's because you only need enough FSM to record the
free space you'll need until the next vacuum.

            regards, tom lane


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Bad n_distinct estimation; hacks suggested?
From: Josh Berkus
Date:
Subject: Re: Bad n_distinct estimation; hacks suggested?