Showdown: MySQL 8 vs. PostgreSQL 10
blog.dumper.io
Showdown: MySQL 8 vs. PostgreSQL 10
1–10 of 99 posts
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#2The article contains a footnote about UUIDs as primary keys.
> UUID as a primary key is a terrible idea, by the way — cryptographic randomness is utterly designed to kill locality of reference, hence the performance penalty
Is there anyone who can go a little bit more in detail?
We planned to migrate our database to use UUIDs as primary keys. This will allow creating new rows on clients knowing the new primary key before sending them to the server (simplifying client and server code).
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#3Referenced in the article:
https://wiki.postgresql.org/wiki/Future_of_storage
This reads like all my dreams come true.
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#4The article contains a footnote about UUIDs as primary keys. > UUID as a primary key is a terrible idea, by the way — cryptographic randomness is utterly designed to kill locality of reference, hence the performance penalty Is there anyone who can go a little bit more in detail? We planned to migrate our database to use UUIDs as primary keys. This will allow creating new rows on clients knowing the new primary key be…
uuid's aren't guaranteed to be unique at generation, there is still a non-zero chance of it having a collision. using it as a primary key to be generated by the database helps mitigate that, as there will normally be a uniqueness clause on the index.
creating that uuid on the client likely will not accomplish what you're hoping.
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#5The article contains a footnote about UUIDs as primary keys. > UUID as a primary key is a terrible idea, by the way — cryptographic randomness is utterly designed to kill locality of reference, hence the performance penalty Is there anyone who can go a little bit more in detail? We planned to migrate our database to use UUIDs as primary keys. This will allow creating new rows on clients knowing the new primary key be…
uuid's aren't guaranteed to be unique at generation, there is still a non-zero chance of it having a collision. using it as a primary key to be generated by the database helps mitigate that, as there will normally be a uniqueness clause on the index. creating that uuid on the client likely will not accomplish what you're hoping.
Primary keys on postgresql are unique (and non null). It’s not possible to change that behavior as it’s the definition of a PK field.
You can thus create more UUID fields that are unique and non-null by specifying that instead of “primary key” on column creation.
https://www.postgresql.org/docs/8.1/static/ddl-constraints.h...
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#6The article contains a footnote about UUIDs as primary keys. > UUID as a primary key is a terrible idea, by the way — cryptographic randomness is utterly designed to kill locality of reference, hence the performance penalty Is there anyone who can go a little bit more in detail? We planned to migrate our database to use UUIDs as primary keys. This will allow creating new rows on clients knowing the new primary key be…
uuid's aren't guaranteed to be unique at generation, there is still a non-zero chance of it having a collision. using it as a primary key to be generated by the database helps mitigate that, as there will normally be a uniqueness clause on the index. creating that uuid on the client likely will not accomplish what you're hoping.
It's pretty unlikely to get a collision[1], and the client should be able to handle the gracefully (regenerate the UUID and retry).
[1] https://www.quora.com/Has-there-ever-been-a-UUID-collision
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#7The article contains a footnote about UUIDs as primary keys. > UUID as a primary key is a terrible idea, by the way — cryptographic randomness is utterly designed to kill locality of reference, hence the performance penalty Is there anyone who can go a little bit more in detail? We planned to migrate our database to use UUIDs as primary keys. This will allow creating new rows on clients knowing the new primary key be…
uuid's aren't guaranteed to be unique at generation, there is still a non-zero chance of it having a collision. using it as a primary key to be generated by the database helps mitigate that, as there will normally be a uniqueness clause on the index. creating that uuid on the client likely will not accomplish what you're hoping.
Can't you just enforce uniqueness anyways for that field? And then have the client retry?
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#8The article contains a footnote about UUIDs as primary keys. > UUID as a primary key is a terrible idea, by the way — cryptographic randomness is utterly designed to kill locality of reference, hence the performance penalty Is there anyone who can go a little bit more in detail? We planned to migrate our database to use UUIDs as primary keys. This will allow creating new rows on clients knowing the new primary key be…
I would suggest to write your own UUID generator. Instagram published their own approach [0], we are using something very similar to it. It seems to originate at Facebook, as the IDs generated from both instagram and FB are very similar. It can be completely automated in Postgres (probably in Mysql too).
On the plus side, because it's timestamp based, you can use the generated IDs in sorting and paging as there is a guarantee that each passing second will yield larger bigint. One caveat. If you are going to use it in Javascript, make sure you send it as string as Javascript only supports 53bit integers (due the fact that all integers in Javascript are floating points).
[0] https://instagram-engineering.com/sharding-ids-at-instagram-...
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#9The article contains a footnote about UUIDs as primary keys. > UUID as a primary key is a terrible idea, by the way — cryptographic randomness is utterly designed to kill locality of reference, hence the performance penalty Is there anyone who can go a little bit more in detail? We planned to migrate our database to use UUIDs as primary keys. This will allow creating new rows on clients knowing the new primary key be…
(The author is assuming that the primary key controls disk layout, which is usually true.) One advantage of using an incrementing integer is that rows will be ordered on disk based on when they were created. This often helps performance. If a query asks for 25 consecutive rows, there is a good chance they will all be on the same page. If you use UUIDs, then they could be on 25 different pages and you will have to do 25x the disk IO to handle the query.
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#10Earlier quoted context omitted.
uuid's aren't guaranteed to be unique at generation, there is still a non-zero chance of it having a collision. using it as a primary key to be generated by the database helps mitigate that, as there will normally be a uniqueness clause on the index. creating that uuid on the client likely will not accomplish what you're hoping.
Can't you just enforce uniqueness anyways for that field? And then have the client retry?
Yes, we are planning to just fail, as the chance of a collision is really low and the user will then be able to just retry creating the object.