Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Showdown: MySQL 8 vs. PostgreSQL 10

blog.dumper.io

Post reply on HN
Print view View original

11–20 of 99 posts

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#11

Post by mbroshi »

foxylion wrote: post #2

The 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…

There are functional benefits for having UUIDs as a primary key, but yes there are performance impacts on writes and ORDER BY. The best way to find out how it will impact your application is to have performance tests in place, and test out the primary key change in a development environment. I do not think you'll able to determine the impact on performance/scalability based on "pure thought".

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#12

Post by simonw »

foxylion wrote: post #2

The 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 one time I used UUIDs as primary keys I quickly regretted it. They're huge, they're not possible to manually enter ("which row ID was causing that bug?") and they make foreign key relationships ugly and large too.

If you want to generate IDs independently of the database you can do so using a "ID generator" mechanism.

Set up three redis instances (or MySQL or anything else that can increment a counter). Have each one increment by three each time. Start then at 0, 1 and 2.

Now you can ask any of those theee instances for a new ID and you'll get one that has not been used before, thanks to them being offset from each other.

I first saw this technique used by Flickr when they switched to a Shaffer database.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#13

Post by hodgesrm »

> [2] When I say Postgres is great for analytics, I mean it. In case you don’t know about TimescaleDB, it’s a wrapper on top of PostgreSQL that allows you to INSERT 1 million records per second, 100+ billion rows per server. Crazy stuff. No wonder why Amazon chose PostgreSQL as its base for Redshift.

Correction: Amazon chose ParAccel, which was a data warehouse forked from PostgreSQL.

Many data warehouse products have followed this path due to licensing. MySQL is GPLv2 which means you can't ship derivative works without releasing your code. PostgreSQL has a permissive license similar to MIT/BSD. You can do anything you want with the code. That's still a major consideration which the article omitted.

(Cross-posted from another HN link to same article.)

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#14

Post by zzzcpan »

jerrysievert wrote: post #4
foxylion wrote: post #2

The 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.

> uuid's aren't guaranteed to be unique at generation

If they are not unique, they are not really UUIDs. In which case you should tweak the algorithm to make uniqueness guaranteed. Like add a client id and its logical time in there.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#15

Post by [deleted] »

foxylion wrote: post #2

The 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…

[deleted]

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#16

Post by zzzcpan »

foxylion wrote: post #10
firenotire wrote: post #7

Earlier quoted context omitted.

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.

You can easily guarantee uniqueness within the system without any coordination.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#17

Post by jerrysievert »

mbroshi wrote: post #6
jerrysievert wrote: post #4

Earlier 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.

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

> It's pretty unlikely to get a collision

unlikely is not zero, which was why I commented. I'd hate to rely on uniqueness of something that has a chance of not being unique.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#18

Post by parvenu74 »

simonw wrote: post #12
foxylion wrote: post #2

The 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 one time I used UUIDs as primary keys I quickly regretted it. They're huge, they're not possible to manually enter ("which row ID was causing that bug?") and they make foreign key relationships ugly and large too. If you want to generate IDs independently of the database you can do so using a "ID generator" mechanism. Set up three redis instances (or MySQL or anything else that can increment a counter). Have each…

UUID (or GUID on SqlServer) are essentially 128-bit integers. The fact they don't have inherent meaning beyond uniquely identifying a row and establishing referential integrity is a plus. In my opinion the primary key is for the database's needs; a human-friendly number or code for lookup is both optional and trivial to implement so when there's a requirement for an easy reference number or code for a record I use both a GUID for the primary key and a unique (or autonumber) field for human reference.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#19

Post by SloopJon »

mbroshi wrote: post #6
jerrysievert wrote: post #4

Earlier 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.

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

The rarity of UUID collisions depends on the quality of implementation. The Quora answers assume UUIDv4 with a high-quality RNG.

I was able to reproduce UUIDv1 collisions at will when the timestamp had microsecond resolution and the clock sequence had to be generated randomly each time. That is, I simply had to get two processes to generate the same fourteen bits within a microsecond.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#20

Post by mbell »

The connection comments are a bit dubious. MySQL will use less memory for 1000 connections but performance will still drop due to contention and context switching. In both systems you want a small number of connections to the actual database, something on the order of 1-2x cpu cores usually, and something on top pooling client connections if you need a lot of them, pgbouncer or the equivalent for MySQL.
Post reply on HN