On Tue, Aug 30, 2016 at 9:59 AM, Luke Gordon <gordysc@xxxxxxxxx> wrote:
You did not mention your PostgreSQL version or O/S, which would be helpful for future reference, but please take note.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: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:So, does anyone know which is best? Or are the performance differences so minute they'd only matter in extremely unique circumstances?Regards,Luke GordonPS 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...
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.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
