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

61–70 of 99 posts

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#61

Post by tscs37 »

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

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 into JSON (partial indices even; only index rows where a field is present in the JSONB)

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#62

Post by tscs37 »

scott113341 wrote: post #53
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…

> 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 letters that are far apart on my QWERTZ keyboard (bit local but it should work for most keyboard sets and I have parity to fall back on)

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#63

Post by zzzcpan »

firebacon wrote: post #55
zzzcpan wrote: post #14

Earlier quoted context omitted.

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

> by generating more than 2^B UUIDs on the same machine within a fairly short timeframe and fairly large B

Which can be made impossible.

IDs are part of the finite system and don't exist on their own. And the system can perform a finite number of operations both concurrently and in a finite time-interval with some amount of uniqueness distinguishing nodes and other useful properties. Making it possible to always find an id generating algorithm for this system that can never have collisions.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#64

Post by derekperkins »

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…

MySQL has close to feature parity in terms of JSON functionality too.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#65

Post by netcraft »

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

jsonb functionality in PG is fantastic. It does everything youd expect, plus you can do a lot of other nifty things with it that you normally cant do with relational dbs very easily.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#66

Post by cosmie »

pletnes wrote: post #31
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).

That's generally the route I've gone in the past, particularly for complex data models. The entity has a uuid field with a unique constraint along with an auto-incrementing int/bigint for a primary key. The UUID is what gets exposed for any external/API usage, but all internal join logic leverages the int primary key.

That way you mitigate the potential performance impact of doing complex joins on UUID fields. While also gaining a bit of flexibility in any future change management process by decoupling your internal database ids from any publicly exposed id. So instead of e.g. having to coordinate with external applications to ensure things don't break when you switch your id from an int to a bigint, you keep the uuid consistent and internal database optimizations and changes stay transparent to stakeholders of the database.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#67

Post by stevenwoo »

nvivo wrote: post #35
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.

What did you do to address the mysql connection problem?

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#68

Post by firebacon »

zzzcpan wrote: post #63
firebacon wrote: post #55

Earlier quoted context omitted.

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

> by generating more than 2^B UUIDs on the same machine within a fairly short timeframe and fairly large B Which can be made impossible. IDs are part of the finite system and don't exist on their own. And the system can perform a finite number of operations both concurrently and in a finite time-interval with some amount of uniqueness distinguishing nodes and other useful properties. Making it possible to always find…

I agree, if we are allowed to make the UUID arbitrarily large (and not be limited to the 128 bits from the "official" UUID algorithm), it should always be possible to set B to a large enough value so that our scheme assigns a unique and finite-sized UUID to every possible state of the system, since all the system's parameters (number of servers, time, etc) should be discrete and finite. I.e. basically make B large enough that 2^B becomes greater than the number of discrete timesteps in our experiment. That's an interesting observation.

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#69

Post by garyclarke27 »

Interesting but obviously biased in favour of MySQL. I disagree with some Postgres (which I know v well not MySQL though) comments. Wrong everyone does not use Sequential Integer Primary Keys, because they are completely useless as a Set constraint, I use real world logic. Update is irrelevant to me, so Vacuum not a problem, I use append only ie immutable database, the upside (not mentioned) is that this architecture makes PostgreSQL rock solid, can stay up for years unlike for example MS SQL Server that requires regular reboots and often fails to come backup cleanly. Also provide DDL transactions with rollback. I’m curious does MySQL now have Check Constraints, Arrays, Drop Schema with cascade (a huge timesaver), V8 PL, Lateral Joins, Range Types, Custom Aggregates (so powerful) SQL Function inlining, Parallel Query, Functional Indexes, Exclusion Constraints (eg no period overlap), Notify/Listen, Foreign Data Wrappers?

Re: Showdown: MySQL 8 vs. PostgreSQL 10

#70

Post by zzzcpan »

firebacon wrote: post #68
zzzcpan wrote: post #63

Earlier quoted context omitted.

> by generating more than 2^B UUIDs on the same machine within a fairly short timeframe and fairly large B Which can be made impossible. IDs are part of the finite system and don't exist on their own. And the system can perform a finite number of operations both concurrently and in a finite time-interval with some amount of uniqueness distinguishing nodes and other useful properties. Making it possible to always find…

I agree, if we are allowed to make the UUID arbitrarily large (and not be limited to the 128 bits from the "official" UUID algorithm), it should always be possible to set B to a large enough value so that our scheme assigns a unique and finite-sized UUID to every possible state of the system, since all the system's parameters (number of servers, time, etc) should be discrete and finite. I.e. basically make B large en…

You can get away with a lot less than 128 bits for most systems. It's only that big because it tries to be universal.
Post reply on HN