guid.comb not sequential in PostgreSQL

[RickZeeland]

When using the NHibernate 5 guid.comb generator with C# in PostgreSQL 9.5 on Windows 10, the generated GUID's are not in sequential order.
Could it be that this is only implemented for SQL Server ?

[fredericDelaporte]

Yes, indeed. Its NHibernate implementation originates from The Cost of GUIDs as Primary Keys article (as pointed out in guid.comb generator source code), and is made for having them sequential under SQL-Server, which sorts them first by their 6 last bytes.

So its usefulness for other databases depends on how these other databases sort them. As stated in NHibernate reference:

To address some of the performance concerns with using Guids as primary keys, foreign keys, and as part of indexes with MS SQL the guid.comb can be used. The benefit of using the guid.comb with other databases that support GUIDs has not been measured.

Well, I have found this article which measures it. (Its AtEnd mode is close to NHibernate guid.comb.)

As written in it, sequentiality of GUIDs primary keys does not have a strong impact on all databases. For Oracle this is quite expected to me, since Oracle stores its data as un-ordered heaps by default. (While the default for SQL-Server is to set its primary key as clustered, which in SQL-Server means storing the data ordered by its PK.) Maybe that is the same for PostgreSQL than for Oracle, since its among the databases less impacted by GUID order.

This article is also primarily about a GUID COMB solution which can be beneficial to different databases, thanks to a configuration parameter. So you could write (and eventually contribute) your own configurable custom generator for NHibernate, based on this article. (For practical reasons I would likely replace the use of RNGCryptoServiceProvider by Guid.NewGuid() as suggested in this comment and as the author himself seems to do according to its answer. NHibernate guid.comb also uses Guid.NewGuid().)

So overall for the issue you have opened here, the behavior you witness is "as designed" and "as documented", closing.

[RickZeeland]

@fredericDelaporte Thanks for your explanation !
I read the excellent CodeProject article and it seems things are not that dramatic for PostgreSQL performance when using "normal" GUID's.
Still I find it a pity that when I browse my PostgeSQL tables they are not sorted in some sort of logical order by default.
But I guess the ball lies in the PostgreSQL camp to provide an "sequential uuid" that is implemented natively in the database ...