From: | Luke Gordon <gordysc(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | UUIDs & Clustered Indexes |
Date: | 2016-08-30 13:59:09 |
Message-ID: | CAB47w9mvemxsWh1hv0BUQ_su4eoLtnt2NxmK3ktF0OztZALZbg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm trying to decide on which UUID generator to use for my Postgres
database, and I've narrowed it down to gen_random & uuid_generate_v1mc.
There's a fascinating article that discusses performance implications
between gen_random_uuid & uuid_generate_v1mc:
https://www.starkandwayne.com/blog/uuid-primary-keys-in-postgresql/
TL;DR, the article suggests:
"Random produces very fragmented inserts that destroy tables. Use
uuid_generate_v1mc() [instead].."
However, according to a message on this mailing list, Postgres doesn't have
clustered indexes:
"But Postgres doesn't _have_ clustered indexes, so that article doesn't
apply at all. The other authors appear to have missed this important point."
https://www.postgresql.org/message-id/56798352.7060902%40uchicago.edu
But, doing a quick check, it appears Postgres does indeed have a mechanism
for a clustered index:
https://www.postgresql.org/docs/9.5/static/sql-cluster.html
<https://www.postgresql.org/docs/9.5/static/sql-cluster.html>So, does
anyone know which is best? Or are the performance differences so minute
they'd only matter in extremely unique circumstances?
Regards,
Luke Gordon
PS I attempted to reply to the above thread, but downloading the "raw" link
resulted in a basic auth challenge. If I entered my mailing list
credentials, I'd just get rechallenged with basic auth...
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-08-30 14:15:54 | Re: UUIDs & Clustered Indexes |
Previous Message | Eduardo Morras | 2016-08-30 13:05:43 | Re: Clustered index to preserve data locality in a multitenant application? |