Horton

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

John Naylor <john.naylor@2ndquadrant.com>

View thread View on postgr.es
From: John Naylor <john.naylor@2ndquadrant.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Mithun Cy <mithun.cy@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-06T11:49:26Z
Lists: pgsql-hackers 
On Fri, Jan 25, 2019 at 9:50 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> Once we agree on the code, we need to test below scenarios:
> (a) upgrade from all supported versions to the latest version
> (b) upgrade standby with and without using rsync.

Although the code hasn't been reviewed yet, I went ahead and tested
(a) on v21 of the pg_upgrade patch [1]. To do this I dumped out a 9.4
instance with the regression database and restored it to all supported
versions. To make it work with pg_upgrade, I first had to drop tables
with oids, drop functions referring to C libraries, and drop the
later-removed '=>' operator. Then I pg_upgrade'd in copy mode from all
versions to HEAD with the patch applied. pg_upgrade worked without
error, and the following query returned 0 bytes on all the new
clusters:

select sum(pg_relation_size(oid, 'fsm')) as total_fsm_size
from pg_class where relkind in ('r', 't')
and pg_relation_size(oid, 'main') <= 4 * 8192;

The complementary query (> 4 * 8192) returned the same number of bytes
as in the original 9.4 instance.

To make it easy to find, the latest regression test patch, which is
intended to be independent of block-size, was in [2].

[1] https://www.postgresql.org/message-id/CACPNZCu4cOdm3uGnNEGXivy7Gz8UWyQjynDpdkPGabQ18_zK6g%40mail.gmail.com

[2] https://www.postgresql.org/message-id/CACPNZCsWa%3Ddd0K%2BFiODwM%3DLEsepAHVJCoSx2Avew%3DxBEX3Ywjw%40mail.gmail.com

-- 
John Naylor                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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