If you are going to use UUIDs as the primary key, it is recommended you use uuid_generate_v1mc to generate the ids. This is because uuid_generate_v1mc is time based which means the ids will be mostly sequential which is good for your primary key index.
This is refuted by Bill Moran on this PostgreSQL mailing list thread:
There's no substance to these claims. Chasing the links around we finally
find this article:
http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
which makes the reasonable argument that random primary keys can cause performance robbing fragmentation on 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.
One could make the argument that the index itself becomming fragmented could cause some performance degredation, but I've yet to see any convincing evidence that index fragmentation produces any measurable performance issues (my own experiments have been inconclusive).
It would be good to find a canonical source of truth for this one way or the other.
chadfurman
commented
7 years ago
https://www.graphile.org/postgraphile/postgresql-schema-design/
This is refuted by Bill Moran on this PostgreSQL mailing list thread:
https://www.postgresql.org/message-id/20151222124018.bee10b60b3d9b58d7b3a1839%40potentialtech.com
It would be good to find a canonical source of truth for this one way or the other.
See also: https://github.com/postgraphql/postgraphql/issues/578