Re: DB Tuning Notes for comment...

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> I think it would be worth looking at removing max_fsm_tables as a tuning
> option, and adding a 'relhasfsm' flag to pg_class for those tables that
> should not be mapped. Default to 't'. Then, make the table grow dynamically
> as tables are added, or when a VACUUM occurs...

If we could "make the table grow dynamically" then there'd not be much
need for the config parameters at all. The real problem is to fit into
a shmem segment whose size has to be frozen at postmaster start (which,
not incidentally, is before we've ever looked at the database...). We
could make the constraint be on total space for relation entries + page
entries rather than either individually, but I think that'd mostly make
it harder to interpret the config setting rather than offer any real
ease of administration.

> AFAICT, the only justification for a smaller list of relations is for those
> that are *almost never* subject to deletes or updates. They are certainly
> common in DB design, but I'd let the DBA designate them.

It doesn't seem to me to be that hard for the system to recognize them
automatically. Basically, if there are no holes of useful size in the
table, there's no need to create an FSM entry for it. The trick is
"useful size" here --- but VACUUM already does the work needed to
estimate an average tuple size, so I'd think it could do a reasonably
good job of realizing that all the available holes are just leftover
space. (The relation's very last page is also a special case that's
likely not special-cased correctly at the moment: perhaps it should never
be entered in FSM at all, certainly not if it's the only page that would
be entered.)

regards, tom lane