On Tue, Aug 30, 2016 at 9:59 AM, Luke Gordon <gord...@gmail.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.
