Re: more about pg_toast growth

On Tue, 2002-04-09 at 06:06, Jan Wieck wrote:
> 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.

VACUUM FULL takes an extremely long time, while dump and reload takes
about 5 minutes. They both require exclusive access.

> > 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.

Please post a URL to this documentation. Please post a URL which
clarifies whether the FSM is shared for all tables or if another is
allocated for each table.

I have "done a little math", and the peak amount of data in the table is
approximately 450MB. A free space map tracking 800MB of space should be
plenty.

> 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,

You haven't earned the right to insult me.

> not behind it :-) Give it a chance and increase max_fsm_pages
> to 10 million.

Your previous advice:

On Wed, 2002-03-13 at 07:22, Jan Wieck wrote:
> Anyway, I would suggest you increase the max_fsm_pages
> parameter. Commented out parameters in the postgresql.conf
> file means "default". You said you're doing about 1,000
> inserts an hour and a daily bulk delete of approx. 24,000.
> Assuming most of the toast tuples are contigous, that'd mean
> you are freeing something like 35,000 toast pages. I would
> suggest a freespace map size of 50,000 pages, to start with.

I doubled that, and it still doesn't work. You are suggesting I
increase your previous estimate by a factor of 200. Your email of
2002-03-13 at 15:16 -0500 suggests a FSM of 50,000 pages allocates "some
more shared memory. It's surely in the range of a few megabytes..."
Will a FSM map 200 times larger require 200 times more memory, or is the
growth nonlinear? How can I calculate this requirement? Without some
documentation this database is inoperable.

I stand behind my previous statement: if PostgreSQL's unchecked table
growth can only be prevented by changing an undocumented configuration
key using an undocumented formula producing undocumented system impact,
the implementation is flawed.

I thank you for your advice, but your insults are not welcome.

-jwb