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

81–90 of 99 posts

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#81

Post by pritambaral »

ngrilly wrote: post #72
DrJosiah wrote: post #44

Earlier quoted context omitted.

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.

1. MongoDB is different from PostgreSQL in more ways than sharded clusters.

2. Pg has variants (e.g., Postgres-XL) and extensions (e.g., CitusDB, as you mention) and methods (e.g., postgres-fdw, pgBouncer etc) that let you keep using quite a lot of Pg features with your data horizontally distributed across machines.

3. If you still want automatically managed sharding, there are quite a few databases (SQL: CockroachDB and NoSQL: Cassandra, FoundationDB) better than MongoDB.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#82

Post by sufehmi »

What about upgrading to a newer version of PostgreSQL ? Does that still require upgrading the whole databases ?

I evaluated PostgreSQL several times in the past, and cancelled once I found out that upgrading to a new version requires upgrading the whole databases - our databases are too big and our uptime requirement are too strict, we can not afford it

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#83

Post by atombender »

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…

> their databases resemble vomitoriums

Curious what you mean by this. Vomitoriums had nothing to do with vomit, except in an etymological sense.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#84

Post by manquer »

sufehmi wrote: post #82

What about upgrading to a newer version of PostgreSQL ? Does that still require upgrading the whole databases ? I evaluated PostgreSQL several times in the past, and cancelled once I found out that upgrading to a new version requires upgrading the whole databases - our databases are too big and our uptime requirement are too strict, we can not afford it

You can use logical replication between 2 versions and fail over to the new version without any downtime . In pg 10+ it is built into core, for earlier versions you can use pglogical or similar tools.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#85

Post by sufehmi »

manquer wrote: post #84
sufehmi wrote: post #82

What about upgrading to a newer version of PostgreSQL ? Does that still require upgrading the whole databases ? I evaluated PostgreSQL several times in the past, and cancelled once I found out that upgrading to a new version requires upgrading the whole databases - our databases are too big and our uptime requirement are too strict, we can not afford it

You can use logical replication between 2 versions and fail over to the new version without any downtime . In pg 10+ it is built into core, for earlier versions you can use pglogical or similar tools.

Great idea - I'll try to see if this is doable in our situation. Thanks.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#86

Post by olavgg »

hodgesrm wrote: post #13

> [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 hav…

Postgresql is good for analytics, but it doesn't scale really well with a lot of data. I have moved my analytics to Clickhouse, 1000x better performance.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#87

Post by th3sly »

tscs37 wrote: post #62
scott113341 wrote: post #53

Earlier quoted context omitted.

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

You can somewhat mitigate the typo problem with integers by encoding them to the outside with parity included. An 8 bit parity should be able to easily tell any possible typo in a 32 or 64bit integer and even correct errors. You could even put the parity into the lowest 8 bit of the integer. I'm currently working on this for a project of mine to not only prevent typos but tolerate them by using the parity and using l…

check uuid_to_bin: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functi...

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#88

Post by th3sly »

RedCrowbar wrote: post #27
masklinn wrote: post #22

Earlier quoted context omitted.

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

> The latter can he mitigated by using "ordered UUIDs": you can generate UUID1 (nominally time-based) such that the final value has a sequential "head" and a random "tail" Specifically, in PostgreSQL such UUIDs can be generated using uuid_generate_v1mc()

you can use uuid_to_bin for this, check the docs: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functi...

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#89

Post by th3sly »

doh wrote: post #8
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 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…

Or you can shuffle it and convert to binary using uuid_to_bin like in this example uuid_to_bin(uuid(), true): https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functi...

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#90

Post by th3sly »

nvivo wrote: post #78
da_chicken wrote: post #71

Earlier quoted context omitted.

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

but it supports conversion to binary (+shuffle to keep order) using "uuid_to_bin"
Post reply on HN