Re: Burst in WAL size when UUID is used as PK while full_page_writes are enabled

On 10/27/2017 07:56 AM, sanyam jain wrote:
> Hi,
>
> I was reading the
> blog https://blog.2ndquadrant.com/on-the-impact-of-full-page-writes .
>

For the record, I assume you're referring to this part:

With BIGSERIAL new values are sequential, and so get inserted to the
same leaf pages in the btree index. As only the first modification
to a page triggers the full-page write, only a tiny fraction of the
WAL records are FPIs. With UUID it’s completely different case, of
couse – the values are not sequential at all, in fact each insert is
likely to touch completely new leaf index leaf page (assuming the
index is large enough).

> My queries:
>
> How randomness of UUID will likely to create new leaf page in btree index?
> In my understanding as the size of UUID is 128 bits i.e. twice of
> BIGSERIAL , more number of pages will be required to store the same
> number of rows and hence there can be increase in WAL size due to FPW .
> When compared the index size in local setup UUID index is ~2x greater in
> size.
>

Perhaps this is just a poor choice of words on my side, but I wasn't
suggesting new leaf pages will be created but merely that the inserts
will touch a different (possibly existing) leaf page. That's a direct
consequence of the inherent UUID randomness.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services