Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 16:12 odesílatel Robert Haas <robertmhaas(at)gmail(dot)com>
napsal:

> On Thu, Dec 6, 2018 at 2:11 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >> > I am sorry, I know zero about zheap - does zheap use fill factor? if
> yes, why?
> >>
> >> Good question. It is required because tuples can expand (Update tuple
> >> to bigger length). In such cases, we try to perform in-place update
> >> if there is a space in the page. So, having fillfactor can help.
> >
> > Thank you for reply :)
>
> I suspect fillfactor is *more* likely to help with zheap than with the
> current heap. With the current heap, you need to leave enough space
> to store entire copies of the tuples to try to get HOT updates. But
> with zheap you only need enough room for the anticipate growth in the
> tuples.
>
> For instance, let's say that you plan to update 30% of the tuples in a
> table and make them 1 byte larger. With the heap, you'd need to leave
> ~ 3/13 = 23% of each page empty, plus a little bit more to allow for
> the storage growth. So to make all of those updates HOT, you would
> probably need a fillfactor of roughly 75%. Unfortunately, that will
> make your table larger by one-third, which is terrible.
>
> On the other hand, with zheap, you only need to leave enough room for
> the increased amount of tuple data. If you've got 121 items per page,
> as in Mithun's statistics, that means you need 121 bytes of free space
> to do all the updates in place. That means you need a fillfactor of 1
> - (121/8192) = ~98%. To be conservative you can set a fillfactor of
> say 95%. Your table will only get slightly bigger, and all of your
> updates will be in place, and everything will be great. At least with
> respect to fillfactor -- zheap is not free of other problems.
>

I have a problem to imagine it. When fill factor will be low, then there is
high risk of high fragmentation - or there some body should to do
defragmentation.

> Of course, you don't really set fillfactor based on an expectation of
> a single round of tuple updates, but imagine that the workload goes on
> for a while, with tuples getting bigger and smaller again as the exact
> values being stored change. In a heap table, you need LOTS of empty
> space on each page to get HOT updates. In a zheap table, you need
> very little, because the updates are in place.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>