Horton

Re: WIP: Avoid creation of the free space map for small tables

John Naylor <jcnaylor@gmail.com>

View thread View on postgr.es
From: John Naylor <jcnaylor@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Amit Kapila <amit.kapila16@gmail.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2018-11-05T09:28:17Z
Lists: pgsql-hackers 
On 11/2/18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> On a FSM-less table, I'd be inclined to just check the
> last page and then grow the table if the tuple doesn't fit there.
> This would, in many cases, soon result in a FSM being created, but
> I think that's just fine.  The point of the change is to optimize
> for cases where a table *never* gets more than a few inserts.  Not, IMO,
> for cases where a table gets a lot of churn but never has a whole lot of
> live tuples.  In the latter scenario we are far better off having a FSM.

and...

On 11/2/18, Robert Haas <robertmhaas@gmail.com> wrote:
> Just checking the last page of the table doesn't sound like a good
> idea to me.  I think that will just lead to a lot of stupid bloat.  It
> seems likely that checking every page of the table is fine for npages
> <= 3, and that would still be win in a very significant number of
> cases,

I see the merit of both of these arguments, and it occurred to me that
there is middle ground between checking only the last page and
checking every page: Check the last 3 pages and set the threshold to
6. That way, with npages <= 3, every page will be checked. In the
unlikely case that npages = 6 and the first 3 pages are all wasted
space, that's the amount of space that would have gone to the FSM
anyway, and the relation will likely grow beyond the threshold soon,
at which point the free space will become visible again.

-John Naylor

Commits

  1. Improve code comments in b0eaa4c51b.

  2. During pg_upgrade, conditionally skip transfer of FSMs.

  3. Add more tests for FSM.

  4. Doc: Update the documentation for FSM behavior for small tables.

  5. Make FSM test portable.

  6. Avoid creation of the free space map for small heap relations, take 2.

  7. Move page initialization from RelationAddExtraBlocks() to use, take 2.

  8. Avoid creation of the free space map for small heap relations.

  9. In bootstrap mode, don't allow the creation of files if they don't already