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

51–60 of 99 posts

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#51

Post by DrJosiah »

sakuronto wrote: post #36
parvenu74 wrote: post #25

Earlier quoted context omitted.

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?

For random (and not timespace prefixed) uuids, you can end up hitting more blocks because if locality of reference, if you are using b+trees. If you are using an lsm index, you get blocks of data written at the same time in the index anyway, so your "slow" disk isn't so bad, because that is in your cache already. For b+trees and random uuids, data in blocks are basically scattered everywhere. So your index lookup of 1 billion items could hit 1 billion leaf blocks, instead of 1 billion / entries per leaf.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#52

Post by amunicio »

heyoni wrote: post #30
parvenu74 wrote: post #28

I like that PostgreSQL can have both relational table and JSONB document collections (NoSQL) in the same database. Use NoSQL where it makes sense and relational tables for data that is inherently relational and query and join both (or batch-process from one to the other). I find this very cool. Of course I wonder if it's too much cool and in trying to do everything it's falling short in some significant and fundament…

Wow, I had no idea it could store jsonb. Any obvious advantages to using mongodb for nosql that I should consider? I’m way more comfortable with Postgres and would rather stick to that

Warning: I haven't used MongoDB in several years, so this info can be outdated!

The main advantage of using JSONB on Postgres over MongoDB is that you can create tables that mix regular fields (varchar, number, date, etc...) with JSONB fields. Then you can do joins of your table with other tables (no need to map/reduce or other insane processing for a simple join).

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#53

Post by scott113341 »

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

Completely agree, but I've found this can be a non-technical "feature" too. Serial integer primary keys are much more susceptible to human error when doing any sort of direct database manipulation.

Make a typo on a integer PK? Wrong user gets deleted. UUID typo? Row not found (almost certainly).

Another source of error I've seen is when someone in sales asks "Hey, can you remove User #1234?", but they really meant Customer #1234." With UUIDs, there's no "collision" between the tables.

Clearly there are better process/tool-based ways to prevent these types of mistakes, but it's a useful side effect of UUIDs.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#54

Post by fermuch »

meritt wrote: post #40
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?

I read it as he had no way to alter mysql, and he needed to work with time functions, which is tricky without a real timestamp instead of an integer, and is impossible without correct indexes.

To me it seems like, in that case, he chose postgres since he could sync the data and use a custom schema over it, with proper types and indexes.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#55

Post by firebacon »

zzzcpan wrote: post #14
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.

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

The difference here seems to be the exact definition of "unique".

A UUID has a finite length, so if we generate N new UUIDs in a finite time-interval it seems clear that - in theory - we must have collisions for large values of N (or even infinite N), regardless of how clever we are in seeding our random number generator. But I don't think this can be fixed without using a variable length value or refusing to mint new UUIDs once the available bits are used up.

Of course in practice that should not really happen; at least if we only run on hardware from vendors where we can assume that every MAC address will be unique, the only way to actually get a colission - in practice - would be by generating more than 2^B UUIDs on the same machine within a fairly short timeframe and fairly large B.

EDIT: In v1 of the algorithm it seems that B=14 and the "short timeframe" is the smallest resolution increment your system clock supports. That is if we assume the "uniquifying" clock sequence is produced by incrementing a counter. So we can say that for practical purposes, a collision is impossible on a modern system unless we have invalid MAC assignments to our hardware, unreasonable transaction rate, or an incorrect implementation of the UUID algorithm.

Am I missing something?

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#56

Post by cosmie »

meritt wrote: post #40
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?

The reason for the migration was to be able to leverage the Functional/Expression Index[1] capability of Postgres. Any type of aggregated time series reporting queries were likely painful from a performance perspective, since an index on the epoch timestamp would have limited usefulness from an optimization standpoint.

So instead, he leveraged the expression index functionality of Postgres to pre-materialize an index against the converted timestamp. He didn't touch the table structure itself so it's transparent, but gets the performance benefits of that index already existing.

MySQL doesn't support function based indexes directly, although you can achieve a similar result in newer versions of MySQL with an intermediate step. You can create a Generated Column[2] first, and then build an index against that. If you specify it as a virtual generated column, then it's essentially the same as the above process where the column isn't physically stored, but you can index it. That said, asah may still not have been able to do that if the version of MySQL was too old or even that level of schema change was not allowed.

[1] https://www.postgresql.org/docs/current/static/indexes-expre...

[2] https://dev.mysql.com/doc/refman/5.7/en/create-table-generat...

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#57

Post by skrause »

zzzcpan wrote: post #21
default-kramer wrote: post #9

Earlier quoted context omitted.

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

> One advantage of using an incrementing integer is that rows will be ordered on disk based on when they were created If each identifier starts with a logical time, say lamport timestamp, you can still get the same ordering effect without incrementing integers in a centralized place somewhere.

I've written my own UUID generation function which uses the current timestamp with microsecond precision for the first 64 bits and a random value for the last 64 bits. So far it's been a great success.

Collisions are extremely unlikely unless you have Google scale and generated UUIDs are mostly ordered.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#58

Post by firebacon »

SloopJon wrote: post #19
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

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.

That only seems possible if both processes were running on the same physical machine (and hence share the same input MAC address).

It's a nasty corner case, but I'm sure the UUID designers considered it a valid tradeoff, since I believe the algorithm was designed for a distributed scenario. In the single-machine case an atomic counter would be a much easier solution with very reasonable efficiency anyway. Still, it might have been clever to also include the local process id in the UUID, I wonder why they didn't to that.

At any rate the problem is easily worked around by running the two processes on different machines, i.e. ensuring you have at most one UUID generating process per host (with respect to the database table in question).

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#59

Post by heyoni »

amunicio wrote: post #52
heyoni wrote: post #30

Earlier quoted context omitted.

Wow, I had no idea it could store jsonb. Any obvious advantages to using mongodb for nosql that I should consider? I’m way more comfortable with Postgres and would rather stick to that

Warning: I haven't used MongoDB in several years, so this info can be outdated! The main advantage of using JSONB on Postgres over MongoDB is that you can create tables that mix regular fields (varchar, number, date, etc...) with JSONB fields. Then you can do joins of your table with other tables (no need to map/reduce or other insane processing for a simple join).

That actually sounds really cool. Honestly, even if Mongodb has all those features, I would much rather do everything on postgres seeing as I'm a hundred times more familiar with it than mongo.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#60

Post by da_chicken »

default-kramer wrote: post #9
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 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…

> One advantage of using an incrementing integer is that rows will be ordered on disk based on when they were created.

Well, kind of. A lot of people think the auto incrementing integer function in many RDBMSs will always increase, or will never have gaps. It's likely but not guaranteed that n+k was created after n. If you really need to store the creation date, then you should store that in a datetime/timestamp column.

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

This is true, but it also means that if you need to write 25 different rows, it will be in 25 different pages. That sounds bad because non-sequential writes are slower, but you have to remember that it could be 25 different connections trying to write! In other words, you create a hot spot with sequential inserts. If that's the end of the table, you'll have threads constantly waiting for other processes to do inserts since inserts lock the page being inserted.

So, yes, clustering on a UUID can cause problems (fragmented indexes, inefficient reads), but clustering on an autoincrement can also cause issues depending on your work load.

In reality, what you need to do (in the general case) is cluster on your business key even if it's not the primary key for your table.

Post reply on HN