On Tue, Jan 2, 2018 at 3:02 PM, Rick Otten <rottenwindf...@gmail.com> wrote:
> After reading this article about keys in relational databases, highlighted > on hacker news this morning: > https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html > > I keep pondering the performance chart, regarding uuid insert, shown > towards the bottom of the article. I believe he was doing that test with > PostgreSQL. > > My understanding is that the performance is degrading because he has a > btree primary key index. Is it possible to try a hash index or some other > index type for a uuid primary key that would mitigate the performance issue > he is recording? > > After all, I can't think of any use case where I query for a "range" of > uuid values. They are always exact matches. So a hash index would > possibly be a really good fit. > > I have many tables, several with more than 1 billion rows, that use uuid's > as the primary key. Many of those uuid's are generated off system, so I > can't play around with the uuid generation algorithm like he was doing. > > I'm hoping to move to PG 10 any day now, and can migrate the data with > updated index definitions if it will actually help performance in any way. > (I'm always looking for ways to tweak the performance for the better any > chance I get.) > > Hash indexes unfortunately don't support UNIQUE indexes. At least not yet. So while you can use them for regular indexing, they cannot be used as a PRIMARY KEY. -- Magnus Hagander Me: https://www.hagander.net/ <http://www.hagander.net/> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
