Обсуждение: [HACKERS] Burst in WAL size when UUID is used as PK while full_page_writes areenabled
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
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 thefirst modification to a page triggers the full-page write, only a tiny fraction of the WAL records are FPIs. WithUUID it’s completely different case, of couse – the values are not sequential at all, in fact each insert is likelyto 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
On Fri, Oct 27, 2017 at 11:26 AM, sanyam jain <sanyamjain22@live.in> 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 whilefull_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 Herrera https://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] Re: Burst in WAL size when UUID is used as PK whilefull_page_writes are enabled
От
Thomas Kellerer
Дата:
akapila 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. But you can't replace the PK index with a hash index, because hash indexes don't support uniqueness. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 27, 2017 at 5:36 PM, Alvaro Herrera <alvherre@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 27, 2017 at 5:46 PM, Thomas Kellerer <spam_eater@gmx.net> wrote: > akapila 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. > > But you can't replace the PK index with a hash index, because hash indexes > don't support uniqueness. > That's true, but it hasn't been mentioned in the mail that the usage of hash index is the for primary key. -- 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