Re: max_fsm_pages

On 7/1/04 4:00 PM, "Scott Marlowe" <smarlowe(at)qwest(dot)net> wrote:

> Let's say you have a table with 1,000 rows, but you've deleted 1,000,000
> over the past year, and most of those are unclaimed. Regular vacuum
> will put those 900,000 odd pages into the FSM, and the database can use
> them. However, scans on this table will still be mostly reading empty
> space.
>
> So, to collapse the table back down to something reasonable, you'll need
> to do a vacuum full, then regular vacuums should keep things tight from
> then on.

Right. But let's say max_fsm_pages is set to the default (20,000) and that
is enough for 100,000 rows and I do a VACUUM ANALYZE. If I understand it
right, only 100,000 will be marked as available for reuse (I probably should
not have used the word 'reclaim' in the original email). If I run VACUUM
ANALYZE again, will there still be only 100,000 available for reuse, or will
an additional 100,000 be made available for a total of 200,000?

If I bump up max_fsm_pages to 1,000,000 and run VACUUM ANALYZE, then would
all 900,000 rows then be available for reused? Based on what Chris said,
the answer to the second question is 'yes' - On any given VACUUM ANALYZE it
will be able to free up to the current max_fsm_pages worth of rows. I'm not
sure about the first question, though. I'm guessing that 800,000 would
remain unavailable regardless of how many times you ran VACUUM ANALYZE -
that max_fsm_pages is the total amount of free space it will track at one
time?

Wes