Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: Is PostgreSQL better than MySQL?

news.ycombinator.com

Post reply on HN
Print view View original

71–80 of 83 posts

Re: Ask HN: Is PostgreSQL better than MySQL?

#71

Post by Thiez »

anonzzzies wrote: post #37
voganmother42 wrote: post #20

It depends: the projects make different trade offs and support different points of extension. I find it useful to remember that MySQL has various storage engines (that make different trade offs, and may themselves have different options such as various row formats), when using for instance the default of InnoDB knowing that it uses index organized (meaning stores the data in a btree as opposed to heap) tables can inf…

I think this is a good answer. Our case is weird; millions of tables with billions (not sure how many in total even) of rows and no joins. Mysql handles this far better in our testing.

May I ask what kind of data you are storing that you need millions of tables? If you have such varied data, what are the tradeoffs compared to only a few tables with json columns?

Re: Ask HN: Is PostgreSQL better than MySQL?

#72

Post by flashgordon »

VLM wrote: post #10

The philosophy of postgresql was to do the best possible job the rightest way. Not necessarily the easiest to use nor fastest. The philosophy of mysql was to do the best it can as fast as it can for as many users as it can. Not necessarily the 'right' or 'best' way. Its not like either philosophy is formally documented or mandatory but "generally" matches up pretty closely to real world behavior. The biggest problem…

What I liked about postgres (even as long time ago as 5-10 years ago) was that it tried to do things in an explainable and standards compliant way even if it mostly did not beat MySQL on latency. This never bothered me because my view had been to scale horizontally when needed and a db that led itself to better control plane development was more important than aggregate single node performance. Just my 2c.

Re: Ask HN: Is PostgreSQL better than MySQL?

#73

Post by azurelake »

hellcow wrote: post #31

Having managed both in production at decent scale for years, I would not choose MySQL for any projects going forward. Postgres has been more dependable in its performance, more powerful in its features. There’s no “pro” to choosing MySQL that I’m aware of in 2023.

MySQL has DDL replication.

Re: Ask HN: Is PostgreSQL better than MySQL?

#74

Post by thinkx »

anonzzzies wrote: post #37
voganmother42 wrote: post #20

It depends: the projects make different trade offs and support different points of extension. I find it useful to remember that MySQL has various storage engines (that make different trade offs, and may themselves have different options such as various row formats), when using for instance the default of InnoDB knowing that it uses index organized (meaning stores the data in a btree as opposed to heap) tables can inf…

I think this is a good answer. Our case is weird; millions of tables with billions (not sure how many in total even) of rows and no joins. Mysql handles this far better in our testing.

we’d love to test this scenario with the new OrioleDB storage engine for Postgres.

Re: Ask HN: Is PostgreSQL better than MySQL?

#75

Post by thinkx »

gtsteve wrote: post #2

While I use MySQL extensively, I don't really know Postgres so I can't really compare the two. In my current project, I opted to use MySQL because Amazon Aurora didn't support Postgres at the time and I wanted replication performance and serverless autoscaling for a low level read workload that could be randomly very spiky. Today, both databases are well supported as a service on major cloud platforms. Postgres does…

take a peek at https://github.com/orioledb/orioledb if the Uber blog is your frame of reference

Re: Ask HN: Is PostgreSQL better than MySQL?

#76

Post by nobozo »

I was part of the Postgres Research Group at UC Berkeley from 1991 to ~1995, working directly for Mike Stonebraker. To be honest, I didn't do any of the research work behind it, although I did port Postgres to Windows NT during this time.

Postgres back then was completely different then what it is now. It was mainly used for PhD and Master's student to hack on for their research. It was a mess internally and was hardly usable for production. I don't think that MySQL went through this style of development.

What eventually happened is that around 1995 SQL was added to Postgres and a bunch of non-Berkeley people started hacking on it. They did a fantastic job, and deserve all the credit for making it what it is today (Stonebraker has publically said this).

Re: Ask HN: Is PostgreSQL better than MySQL?

#78

Post by profwalkstr »

MySQL has a lot of gotchas, WTF moments, things that don't make sense and seem overly and needlessly complicated compared to PostgreSQL.

- Take data types, for example. Seems something simple, right? You can grok all data types in PostgreSQL in an afternoon. Want to grok MySQL data types? You're going to waste an entire week to grok them. There are multiple data types that seem to do the same thing with very small differences and a lot of incompatibilities... and a lot of exceptional behavior (see point below). MySQL data types are LESS powerful, less flexible and 10x more complicated than PostgreSQL data types.

- Enums and sets need to have their possible values defined on a table by table basis. What a nightmare to keep them in sync. PostgreSQL allows you to this once for the whole DB.

- Open MySQL manual and it's full of passages like: feature A does "blah" if X mode is enabled, but does "bar" if mode Y is enabled and will corrupt data if storage engine is used with mode Z enabled.

- SQL strict mode can be disabled by the client. You cannot enforce that your server will work only with SQL strict mode on because a client can simply open a connection with SQL strict mode off. There's no way to enforce that clients will only use SQL strict mode. You have to trust your clients. (This other comment explains strict mode: https://www.reddit.com/r/PostgreSQL/comments/xblooo/comment/...)

- According to the manual, if client A does stuff with strict mode on and client B does stuff with strict mode off, data corruption will occur.

- A lot of surprising and unexpected behavior, even with SQL Strict mode on

- String comparison is case insensitive. There are workarounds but they are still workarounds.

- A lot of behavior is dependent on server configuration. So switching between servers you can't expect them to behave the same.

- Behavior not standardized among versions and installs

- Changing the server SQL mode after creating and inserting data into partitioned tables can cause major changes in the behavior of such tables, and could lead to loss or corruption of data

- Useless error messages (see https://www.reddit.com/r/PostgreSQL/comments/xblooo/comment/...)

- MySQL, at the same time it is less powerful and flexible, has a lot more levers and knobs than PostgreSQL. PostgreSQL, while more powerful and flexible, conceptually is a lot simpler than MySQL.

- PostgreSQL stays out of your way; with MySQL you’re constantly fighting it

PostgreSQL -> simplicity

MySQL -> complexity

Re: Ask HN: Is PostgreSQL better than MySQL?

#80

Post by alentred »

ivolimmen wrote: post #28

As a software engineer that worked with a lot of different databases in the last 20 years I have come to believe that PostgreSQL is even better than Oracle (this will certainly spark some fires). I have had shit with a lot of databases but never with PostgreSQL. MySQL has some really nasty documented and undocumented "features" so I rather avoid that database. It has it's place in the universe but it's in on a differ…

Some 5 years ago I worked with a DBA, Oracle DBA more precisely, who told me that PostgreSQL is generally better than Oracle. At the same time, he wouldn't switch to PostgreSQL because... drumroll... he wouldn't have a job as a PostgreSQL DBA, because it is just simpler. Oracle DB, according to him, was more complex and harder to manage, and Oracle built entire offering around it: training, certifications, etc., which sustains the ecosystem.
Post reply on HN