Showdown: MySQL 8 vs. PostgreSQL 10 • Hacker News Board

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#31

Post by pletnes » Wed, May 23, 2018, 5:26 PM UTC

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…

Why not generate an UUID field that’s unique, but keep a surrogate integer primary key on the server? You can join on either one on the server, but keep track of the rows using the UUID. Asking as someone with no SQL expertise (relative to the HN crowd).

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#32

Post by nvivo » Wed, May 23, 2018, 5:31 PM UTC

patrickg_zill wrote: post #23

I am pretty sure that PG has had clustered indexes for a decade or more... ? e.g. https://www.postgresql.org/docs/9.1/static/sql-cluster.html Or is this term referring to a different feature/method than this? One thing not mentioned: PL/SQL vs. whatever the MySQL equivalent is.

I think it's different. From the docs, this is a one time update to the table. The table itself is not clustered, it's just ordered based on a clustered index, so selects by default should come in that order. But once an insert is done, it's again ouy of order.

Also, an order by using the cluster field most probably invokes an index scan, while a clustered table doesn't.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#33

Post by openasocket » Wed, May 23, 2018, 5:31 PM UTC

jerrysievert wrote: post #17

mbroshi wrote: post #6

Earlier quoted context omitted.

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.

If you generate 1 billion UUIDv4s a second, it would take, on average, 85 years for you to produce a duplicate, and the resulting list of UUIDs would take up ~45 exabytes. And keep in mind that even if inserting a row fails because you've somehow managed to generate a duplicate UUID, it is trivial to make a new UUID and retry. Since the database enforces the uniqueness constraints of primary keys, I'm hard pressed to come up with a scenario in which generating a duplicate UUID would actually do anything serious.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#34

Post by gldalmaso » Wed, May 23, 2018, 5:33 PM UTC

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…

I believe one of the sought after features when people want to generate ids outside the database is for offline apps, so there is no way to connect to outside counters. The client should have a way to locally generate an id that is final and will be used for the rest of the entity's life. This simplifies the synchronization with the backend because otherwise there must be a (temporary) client id and a final true id. This leads to very convoluted code that deals with two logically different entities, the "pending" ones and the "synced" ones.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#35

Post by nvivo » Wed, May 23, 2018, 5:33 PM UTC

mbell wrote: post #20

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.

I had some real issues with mysql handling more than 2000 connections. Once past that limit, cpu usage increases exponentially with few connections due to context switching. At 3000 connections, 80% of thr cpu was used gor context switching and it got unusable. That was a 64 core/256gb ram server.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#36

Post by sakuronto » Wed, May 23, 2018, 5:38 PM UTC

parvenu74 wrote: post #25

mbroshi wrote: post #11

Earlier quoted context omitted.

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

Why would you want to ORDER BY on a UUID field? Not trolling, I honestly can't think of a reason why you would want to do this. Secondly, aren't UUIDs treated by the database engine as a 128-bit integer? If they are being treated as varchar fields then I can see how this would affect performance negatively but again, I question if this is really the case.

What if you just want a stable row ordering, and don't care what that ordering is?

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#37

Post by netcraft » Wed, May 23, 2018, 5:41 PM UTC

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…

we use both uuid's created in our application layer and stored as char(35) and also uuid's produced in the database stored as uuid data types. We have tens of millions of records and have no issues. Once we get upgraded to PG10 and can use hash indexes for them we expect a further speedup.

I don't know anything about the author, but every time in the past ive heard someone say that uuid's as keys are a problem it turns out theyve never actually tried it, theyre just saying what theyve heard in the past. Theoretically they should be worse than they are - and theres no doubt that a regular int/bigint would be faster - but the truth is there are so many other things that are going to slow you down before that.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#38

Post by cat199 » Wed, May 23, 2018, 5:41 PM UTC

this states all of the drawbacks to process vs thread but none of the benefits (resiliency / compartmentalization of errors, less need for lock coordination and less risk of locking related bottlenecks with scale, somewhat better host OS CPU & IO utilization, etc. )

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#39

Post by netcraft » Wed, May 23, 2018, 5:42 PM UTC

masklinn wrote: post #22

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…

> Is there anyone who can go a little bit more in detail? * UUIDs are way more painful than serials to recognise, remember, input or transmit especially if you're not dealing with huge tables. "18574" is easy to read/grok, "21caeffa-0fca-4f4e-b845-46ef0576e42a" is not. * UUID are 128 bit instead of 32 for most serial PKs by default, this may or may not matter. Note that this doesn't just impact the table itself (lowe…

> UUIDs are way more painful than serials to recognise, remember, input or transmit especially if you're not dealing with huge tables. "18574" is easy to read/grok, "21caeffa-0fca-4f4e-b845-46ef0576e42a" is not.

One of the reasons we use them is because theyre not easily recognized or sequential.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#40

Post by meritt » Wed, May 23, 2018, 5:43 PM UTC

asah wrote: post #24

Great to see MySQL adding this stuff! There's still a ton of reasons to choose Postgres and more and more silicon valley startups seem to be choosing pg - I don't remember the last time I met a startup choosing MySQL. DBMSs are giant complex pieces of software with a million features - it's really hard to compare them. But if I had to sum it up, you can dump freaking line noise into Postgres and then hide the nastine…

> EXAMPLE: I was once handed a MySQL database of IoT signals where timestamps were in seconds since the epoch and asked to report on this data without changing the database

I'm not following why you moved the data into postgres other than to say you did? Are you suggesting that because you were restricted from making schema changes to the MySQL instance that that's a reason why postgres is superior?