Re: more about pg_toast growth

Jeffrey W. Baker wrote:
> On Fri, 2002-03-15 at 10:35, Jeffrey W. Baker wrote:
> > On Fri, 2002-03-15 at 06:47, Jan Wieck wrote:
> >
> > > > I have increased the free space map and will be able to restart the
> > > > postmaster today at around midnight GMT.
> > >
> > > Any news?
> >
> > I couldn't work it in to the last maintenance window. I'll give it
> > another run tonight.
>
> I increased the FSM and restarted postgres several weeks ago, and the
> toast tables continue to just grow and grow. I set it to:
>
> max_fsm_relations = 1000 # min 10, fsm is free space map
> max_fsm_pages = 100000 # min 1000, fsm is free space map
>
> Now, my table with only 107490 rows takes 20GB on disk. I dumped and
> reloaded the database and got 18.5GB free space back.

Dump and reload is a bit drastic. A "VACUUM FULL" and
"REINDEX" should've done that as well.

> I really think this is a bug in the implementation.

Come on, Jeffrey, do a little math - it's not *that* hard to
understand. A free space map of 100,000 entries can hold
information about 100,000 pages where new tuples can be
stored. that's 100,000 maybe partially filled 8K pages, so
we're talking about 800MB here.

As soon as your table once has more than 100,000 partially
filled or entirely free pages due to deleted rows, you start
loosing pages. Now, how much data where you pumping through
that table by the hour?

I really think this time the bug is in front of the screen,
not behind it :-) Give it a chance and increase max_fsm_pages
to 10 million.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #