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: Amit Kapila <amit.kapila16@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2018-12-03T05:30:12Z
Lists: pgsql-hackers 
On 12/3/18, Amit Kapila <amit.kapila16@gmail.com> wrote:
>> During pg_upgrade, skip transfer of FSMs if they wouldn't have been
>> created on the new cluster.
>
> I think in some cases, it won't be consistent with HEAD's behavior.
> After truncate, we leave the FSM as it is, so the case where before
> upgrade the relation was truncated, we won't create the FSM in new
> cluster and that will be inconsistent with the behavior of HEAD.

To be precise, with the TRUNCATE statement, the FSM (everything but
the main relation fork, I think) is deleted, but using DELETE to
remove all rows from the table will preserve the forks. In the latter
case, when VACUUM truncates the FSM, it removes all leaf pages leaving
behind the root page and one mid-level page. I haven't changed this
behavior.

> I think similar anomaly will be there when we delete rows from the table
> such that after deletion size of relation becomes smaller than
> HEAP_FSM_CREATION_THRESHOLD.

Yes, in that case there will be inconsistency, but I'm comfortable
with it. Others may not be.

> I am not sure if it is a good idea to *not* transfer FSM files during
> upgrade unless we ensure that we remove FSM whenever the relation size
> falls below HEAP_FSM_CREATION_THRESHOLD.  What do you think?  BTW,
> what is your reasoning for not removing FSM on truncate?

My reasoning is that if we ever went past the threshold, it's likely
we'll do so again, so I didn't feel it was worth the extra code
complexity to remove the FSM. In the pg_upgrade case, however, it is
simple to not copy the FSM.

-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