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

On Fri, Oct 27, 2017 at 5:36 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> 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