Re: UUIDs & Clustered Indexes

On Tue, Aug 30, 2016 at 9:59 AM, Luke Gordon <gordysc(at)gmail(dot)com> wrote:

> 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...
>
>

*You did not mention your PostgreSQL version or O/S, which would be helpful
for future reference, but please take
note.https://www.postgresql.org/docs/9.4/static/sql-cluster.html
<https://www.postgresql.org/docs/9.4/static/sql-cluster.html>*

*Yes, PostgreSQL does have a mechanism to cluster the index, BUT... as
stated in the docs, any subsequent insert or update will not be clustered,
so a periodic *

*reCLUSTER is required to maintain it.*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.