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

71–80 of 99 posts

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#71

Post by da_chicken »

nvivo wrote: post #29
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…

I use UUIDs in mysql as primary keys in tables with a few million records, and I'm about to migrate a few tables with billions of records from bigint to uuid. Never saw any real problem. I use char(36), as it's easier to query manually when needed, but I' looking into binary(16) for those billion row tables. I think most issues with fragmented tables are old problems since ssd, and the overhead is something you will…

> I use char(36), as it's easier to query manually when needed, but I' looking into binary(16) for those billion row tables.

I would use whatever data type your RBDMS's UUID generator returns or the programming language your application is written in. If your RDBMS supports a UUID or GUID data type, however, I would 100% use that because you'll invariably have functions which help you deal with it.

Remember, however, that many (most?) RDBMSs store records in pages (or blocks) of a fixed size typically between 4KB or 8 KB, and they won't allow a record to span a page (usually when a record is too long for one page, non-key data will be moved to non-paged storage which is slower). In other words, if you reduce your record size by 20 bytes you might not actually see as big a change as you'd expect. You'd be storing less data per record, but you're maybe not changing the records per page. You're not increasing the efficiency of your data store at all because of how the data is physically stored. It also means that the answer might be different for each table since each table has a different row size.

Bottom line, however, is that I would favor storing UUIDs the way your particular RDBMS vendor tells you they should be stored. If your application has particular problems with storing UUIDs that way I would look at alternatives, but generally the RDBMS vendors have thought about this a little bit at least.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#72

Post by ngrilly »

DrJosiah wrote: post #44
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

I have not found a situation where mongodb has been a better choice than postgresql.

I'm a big fan of PostgreSQL, but don't you think MongoDB can be useful when you outgrow a single machine (vertical scaling is not possible anymore) and you need a sharded cluster (and you don't need joins and transactions...)? This is the only situation where MongoDB makes sense, maybe. But even though, I'd probably look at Citus instead.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#73

Post by dvlsg »

skrause wrote: post #57
zzzcpan wrote: post #21

Earlier quoted context omitted.

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

I've also done this at a medium ish scale. We had to be careful with how the uuids were generated, though. Specifically which portion of the bytes contained the timestamp (and in what order) since different databases store uuids differently.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#74

Post by dvlsg »

tscs37 wrote: post #61
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

Keep in mind that JSONB is different than JSON in PG. The later will not transform the JSON simply check consistency and has limits and performance hits. JSONB is encoded and will transform your JSON, it will drop duplicate keys and order might not be preserved. But it's also faster and easier to use (IMO). There is no real reason to use MongoDB tbh, PG JSONB can be handled like any other field and you can even index…

Aren't duplicate keys in json invalid anyways?

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#75

Post by tscs37 »

dvlsg wrote: post #74
tscs37 wrote: post #61

Earlier quoted context omitted.

Keep in mind that JSONB is different than JSON in PG. The later will not transform the JSON simply check consistency and has limits and performance hits. JSONB is encoded and will transform your JSON, it will drop duplicate keys and order might not be preserved. But it's also faster and easier to use (IMO). There is no real reason to use MongoDB tbh, PG JSONB can be handled like any other field and you can even index…

Aren't duplicate keys in json invalid anyways?

Technically yes.

In production no.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#76

Post by diaz »

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…

I can't comment on uuid as I never used that for ids. But, I can tell you that asking for a next ID from a postresql primary key sequence will make sure it is never used again because it is an atomic operation. Once a next value is requested and you keep it, you can be sure it will not be generated again. I've used this for many things.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#77

Post by diaz »

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

Also can exist indices inside the jsonb values and you can join tables on a field of the json. Which is imo very cool.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#78

Post by nvivo »

da_chicken wrote: post #71
nvivo wrote: post #29

Earlier quoted context omitted.

I use UUIDs in mysql as primary keys in tables with a few million records, and I'm about to migrate a few tables with billions of records from bigint to uuid. Never saw any real problem. I use char(36), as it's easier to query manually when needed, but I' looking into binary(16) for those billion row tables. I think most issues with fragmented tables are old problems since ssd, and the overhead is something you will…

> I use char(36), as it's easier to query manually when needed, but I' looking into binary(16) for those billion row tables. I would use whatever data type your RBDMS's UUID generator returns or the programming language your application is written in. If your RDBMS supports a UUID or GUID data type, however, I would 100% use that because you'll invariably have functions which help you deal with it. Remember, however,…

MySql doesn't have a UUID data type, the UUID() function returns a varchar. The way you store it is mostly preference and driver defaults. The C# driver used to handle binary(16) as Guids, then they deprecated that in favor of CHAR(36). But when dealing with a bilion rows, each byte counts and I'll favor binary(16) because it's smaller and that helps with the index sizes and memory usage.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#79

Post by nvivo »

stevenwoo wrote: post #67
nvivo wrote: post #35

Earlier quoted context omitted.

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.

What did you do to address the mysql connection problem?

I ended up reducing the number of clients. In my case I had a thousand servers, and I was able to change the application structure and merge them into a dozen big application servers. Now with connection pooling each server has less than 100 connections.

As a more permanent solution for scaling, I'm moving out of mysql into something more distributed.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#80

Post by nvivo »

da_chicken wrote: post #60
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. 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 colu…

> It's likely but not guaranteed that n+k was created after n.

This is true in mysql if you rollback a transaction, or use a INSERT INTO ... ON DUPLICATE KEY UPDATE.

In the first, the rollback doesn't revert the sequence, in the second the "insert part" will always increase the number, even if there is a duplicate to update.

Post reply on HN