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

91–99 of 99 posts

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#91

Post by tscs37 »

th3sly wrote: post #87
tscs37 wrote: post #62

Earlier quoted context omitted.

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

Yes but that's a full UUID, using a 64bit integer with some encoding is far shorter.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#92

Post by da_chicken »

nvivo wrote: post #80
da_chicken wrote: post #60

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

My point is that nothing stops you from modifying the value of an auto increment column, nor from inserting directly with a specific value. Yes, rollbacks don't roll back consumed values, but an auto increment column isn't immutable and the table isn't required to use the next value.

I've seen an application do things like an INSERT ROLLBACK SELECT LAST_INSERT_ID() to "reserve" IDs... or even perfectly acceptable things like reserving IDs out of a SEQUENCE. Those weren't all MySQL systems, but it did lead to confusion sometimes why gaps might appear or why timestamps might be "inconsistent".

The above one was a potential problem through 5.7 though, as it was possible to reuse some values since MySQL kept the auto increment value in memory only. INSERT followed by a ROLLBACK, then restart the server and you could get reused IDs. It's rare, but I've seen it. However, but it looks like they changed it with 8.0 to save the auto increment value to a system table now. That's a good thing.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#93

Post by DrJosiah »

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.

If you have objects that are happily sharded arbitrarily across machines, don't need joins, transactions, or aggregates calculated on the server, then what are you getting over something like (for example) open-source Redis Cluster?

I ask, because if I spend a few hours designing in advance, and write a bit of code, I can get Redis to do much of what I need in such scenarios (counters, aggregates, statistics, indexes, queues, ...), and being that I wrote a book on Redis, task queues, object mapper, well, I'm going to use that instead (and use some of the public domain / open-source code I've already written).

Also, with my work on real Redis transactions (which I've made work across Redis Cluster) means that I don't even need to give up ACID transactions in Redis, regardless of scale.

Once I need more; in the form of post-hoc analysis, joins, group-by, aggregates, etc., at scale; either I can easily export from Redis into logfiles to csv/tsv/json for Spark, Python + Pandas, and/or Redshift if I've got the $, or at the same time just use pgloader into Postgres and live there.

I haven't mucked about with Postgres foreign data wrappers much, but there is a Redis one available, so maybe I can even drop that Redis -> S3/csv/tsv/json, and get everything I want (direct data structure manipulation in Redis + everything Postgres has).

So yeah. I generally solve my problems with a bit more design in advance, and MongoDB doesn't really have anything to design for/against; you get objects and indexes. Which are usually not as good as Postgres equivalents (Postgres json objects are better than MongoDB, just by themselves, and I'm not the first/only person to say it). And what I get from Redis (raw data structures, 1 million ops/second/core) means that for cases where other folks may use MongoDB, I use Redis. Then I use Postgres for basically everything else.

So yeah, I don't use MongoDB. Postgres for almost everything, and Redis for the cases where Postgres doesn't feel like quite the right fit.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#94

Post by ngrilly »

DrJosiah wrote: post #93
ngrilly wrote: post #72

Earlier quoted context omitted.

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.

If you have objects that are happily sharded arbitrarily across machines, don't need joins, transactions, or aggregates calculated on the server, then what are you getting over something like (for example) open-source Redis Cluster? I ask, because if I spend a few hours designing in advance, and write a bit of code, I can get Redis to do much of what I need in such scenarios (counters, aggregates, statistics, indexes…

> If you have objects that are happily sharded arbitrarily across machines, don't need joins, transactions, or aggregates calculated on the server, then what are you getting over something like (for example) open-source Redis Cluster?

The only reason I can see to prefer MongoDB over Redis Cluster in this case (no joins, no transactions, no aggregations) is if the dataset doesn't fit in memory. Except that, I think you're right to prefer Redis.

Your comment is a really interesting comparison of Redis and MongoDB. Never thought about that before. Thanks!

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#95

Post by nvivo »

th3sly wrote: post #90
nvivo wrote: post #78

Earlier quoted context omitted.

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"

Yes, but this is new to mysql 8, which is pretty recent. Also, I always use uuid v4 to explicitly prevent any ordering.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#96

Post by kolzeq »

nvivo wrote: post #79
stevenwoo wrote: post #67

Earlier quoted context omitted.

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.

Or you can have move to mariadb, using configuration "thread_handling=pool-of-threads" to enable threadpool that is exactly the solution.

(MySQL have that only for "entreprise" version)

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#97

Post by ngrilly »

nvivo wrote: post #79
stevenwoo wrote: post #67

Earlier quoted context omitted.

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.

Something more distributed like what?

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#98

Post by DrJosiah »

ngrilly wrote: post #94
DrJosiah wrote: post #93

Earlier quoted context omitted.

If you have objects that are happily sharded arbitrarily across machines, don't need joins, transactions, or aggregates calculated on the server, then what are you getting over something like (for example) open-source Redis Cluster? I ask, because if I spend a few hours designing in advance, and write a bit of code, I can get Redis to do much of what I need in such scenarios (counters, aggregates, statistics, indexes…

> If you have objects that are happily sharded arbitrarily across machines, don't need joins, transactions, or aggregates calculated on the server, then what are you getting over something like (for example) open-source Redis Cluster? The only reason I can see to prefer MongoDB over Redis Cluster in this case (no joins, no transactions, no aggregations) is if the dataset doesn't fit in memory. Except that, I think yo…

No problem!

Don't get me wrong, I'm sure there are use-cases for MongoDB for folks that are not me.

Because background is important; my doctorate is in Algorithms and Data Structures, so Redis basically fits the problem solving algorithms I've been building in my head since before I learned of Redis in 2010. And Postgres (or really any good relational database) is that conceptual next step which took me the better part 2 years of daily SQL (after 8+ years of occasional SQL) to really appreciate.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#99

Post by ngrilly »

DrJosiah wrote: post #98
ngrilly wrote: post #94

Earlier quoted context omitted.

> If you have objects that are happily sharded arbitrarily across machines, don't need joins, transactions, or aggregates calculated on the server, then what are you getting over something like (for example) open-source Redis Cluster? The only reason I can see to prefer MongoDB over Redis Cluster in this case (no joins, no transactions, no aggregations) is if the dataset doesn't fit in memory. Except that, I think yo…

No problem! Don't get me wrong, I'm sure there are use-cases for MongoDB for folks that are not me. Because background is important; my doctorate is in Algorithms and Data Structures, so Redis basically fits the problem solving algorithms I've been building in my head since before I learned of Redis in 2010. And Postgres (or really any good relational database) is that conceptual next step which took me the better pa…

Considering your background, I can see why you appreciate Redis :-)

Thanks for the chat!

Post reply on HN