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

[Amit Kapila]

On Fri, Oct 27, 2017 at 5:36 PM, Alvaro Herrera
 wrote:
> Amit Kapila wrote:
>
>> You might want to give a try with the hash index if you are planning
>> to use PG10 and your queries involve equality operations.
>
> So, btree indexes on monotonically increasing sequences don't write tons
> of full page writes because typically the same page is touched many
> times by insertions on each checkpoint cycle; so only one or very few
> full page writes are generated for a limited number of index pages.
>
> With UUID you lose locality of access: each insert goes to a different
> btree page, so you generate tons of full page writes because the number
> of modified index pages is very large.
>
> With hash on monotonically increasing keys, my guess is that you get
> behavior similar to btrees on UUID: the inserts are all over the place
> in the index, so tons of full page writes.  Am I wrong?
>
> With hash on UUID, the same thing should happen.  Am I wrong?
>

If the bucket pages are decided merely based on hashkey, then what you
are saying should be right.  However, we mask the hash key with
high|low mask due to which it falls in one of existing page in the
hash index.  Also, I have suggested based on some of the tests we have
done on UUID column and the result was that most of the time hash
index size was lesser than btree size.  See pages 15-17 of hash index
presentation in the last PGCon [1].

[1] - 
https://www.pgcon.org/2017/schedule/attachments/458_durable-hash-indexes-postgresql.pdf

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Alvaro Herrera]

Amit Kapila wrote:

> You might want to give a try with the hash index if you are planning
> to use PG10 and your queries involve equality operations.

So, btree indexes on monotonically increasing sequences don't write tons
of full page writes because typically the same page is touched many
times by insertions on each checkpoint cycle; so only one or very few
full page writes are generated for a limited number of index pages.

With UUID you lose locality of access: each insert goes to a different
btree page, so you generate tons of full page writes because the number
of modified index pages is very large.

With hash on monotonically increasing keys, my guess is that you get
behavior similar to btrees on UUID: the inserts are all over the place
in the index, so tons of full page writes.  Am I wrong?

With hash on UUID, the same thing should happen.  Am I wrong?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Amit Kapila]

On Fri, Oct 27, 2017 at 11:26 AM, sanyam jain  wrote:
> Hi,
>
> I was reading the blog
> https://blog.2ndquadrant.com/on-the-impact-of-full-page-writes .
>
> 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.
>

You might want to give a try with the hash index if you are planning
to use PG10 and your queries involve equality operations.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tomas Vondra]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Burst in WAL size when UUID is used as PK while full_page_writes are enabled

[sanyam jain]

Hi,

I was reading the blog 
https://blog.2ndquadrant.com/on-the-impact-of-full-page-writes .

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.


Thanks,

Sanyam Jain