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

51–60 of 83 posts

Re: Ask HN: Is PostgreSQL better than MySQL?

#51

Post by exabrial »

We benchmarked both. There are some edge cases where postgres is faster, but MySQL used significantly less disk space in those scenarios. Postgres has some esoteric functions not available in MySql, but enh. You probably shouldn’t use those in your app to avoid sharp edges that could be deprecated in the future. Stick to the SQL standard.

All other things being equal they actually perform about the same for our use case.

Then let’s talk replication… MySql GTID/Binlog replication is stupid easy to setup. And _it just works _ and recovers really easy. You can make hundreds of near-real time replicas.

Finally the tooling. I find MySql’s tooling far easier to use than Postgres. Nearly everything is SQL. You have to memorize configuration in both cases so take this last point is an opinion.

Both are a good choice. I think would save if you need absolute single-server performance, probably Postgres is the better option, but understand you're only getting marginal gains. If you want to make hundreds of read-only replicas or use their semi-sync HA plugin, MySQL is probably way to go, but understand that multi-master writes is still not really a thing.

Re: Ask HN: Is PostgreSQL better than MySQL?

#52

Post by frodowtf »

Most important difference is probably clustering. We had to do a lot of PK changes when our mysql database received more rows.

Our access patterns are mostly analytical. A lot of scanning and aggregating has to be done. Nobody cared about clustering before so we had to change a lot of table schemas to make our IO efficient.

I only heard from colleagues that such issues are easier to handle in postgres because it is more flexible. But for mysql, you have to understand that primary key and clustering key are always the same thing.

(of course this depends on the storage engine, but it's the behavior of the standard engine innodb)

Re: Ask HN: Is PostgreSQL better than MySQL?

#53

Post by Euphorbium »

Glench wrote: post #45
Euphorbium wrote: post #16

Earlier quoted context omitted.

It is only good if you do few writes, with at least seconds in between.

Incorrect. Here are some actual numbers: https://blog.wesleyac.com/posts/consider-sqlite > Writing 512 byte blobs as separate transactions, in WAL mode with synchronous=normal3, temp_store=memory, and mmap enabled, I got 13.78μs per write, or ~72,568 writes per second.

Go on, trust the metrics. It locked up every time I had to do many writes without delay.

Re: Ask HN: Is PostgreSQL better than MySQL?

#54

Post by Glench »

Euphorbium wrote: post #53
Glench wrote: post #45

Earlier quoted context omitted.

Incorrect. Here are some actual numbers: https://blog.wesleyac.com/posts/consider-sqlite > Writing 512 byte blobs as separate transactions, in WAL mode with synchronous=normal3, temp_store=memory, and mmap enabled, I got 13.78μs per write, or ~72,568 writes per second.

Go on, trust the metrics. It locked up every time I had to do many writes without delay.

Not sure what your experience is but I'm running SQLite in my production web app and it's just fine :)

Re: Ask HN: Is PostgreSQL better than MySQL?

#55

Post by santa_boy »

ZiiS wrote: post #26

PostgreSQL has a lot of power. Need your table trigger to make a HTTP request, need a stored procedure written in Rust/Java/Javascript/Perl/Python (to name a few _common_ options ;-), want to index JSON fields, or GIS, or NLP vectors. Need everything be strict and correct ACID, referenced integrity, row level security. Need point in time recovery with a The fact it overlaps with MySQL if your use case is "backend for…

Row level security is the main reason I choose Postgres. Is there any way to get RLS implemented in MySQL?

Re: Ask HN: Is PostgreSQL better than MySQL?

#56

Post by porker »

idoubtit wrote: post #24
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…

I also believe these were the original philosophies, since MySQL emerged from practical issues and Postgresql has more academic origins. But nowadays, I'm not sure MySQL is generally faster. In my experience, the main differences are that many simple things are simpler with MySQL, but Postgres is much more rigorous and complete. For instance, a major upgrade of a MySQL server is usually painless (Debian even upgrades…

> silent truncation when inserting is still the default behavior, I think

Hasn't been the default since MySQL 5.6. MySQL 5.7 was released in 2015 and some distros may have altered the defaults, but I doubt it.

Re: Ask HN: Is PostgreSQL better than MySQL?

#57

Post by juancn »

If you need to ask, probably yes.

PostgreSQL is a great database, it's hard to go wrong with it.

I mean, unless there's something you know for sure that PostgreSQL won't do for you, it's a great default choice (even over commercial options such as Oracle or SQLServer).

Re: Ask HN: Is PostgreSQL better than MySQL?

#58

Post by juancn »

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…

I agree, since version 9-ish PostgreSQL is likely a better default choice of database than any other competitor (commercial or otherwise), unless you have some weird workload.

Re: Ask HN: Is PostgreSQL better than MySQL?

#59

Post by evanelias »

idoubtit wrote: post #24
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…

I also believe these were the original philosophies, since MySQL emerged from practical issues and Postgresql has more academic origins. But nowadays, I'm not sure MySQL is generally faster. In my experience, the main differences are that many simple things are simpler with MySQL, but Postgres is much more rigorous and complete. For instance, a major upgrade of a MySQL server is usually painless (Debian even upgrades…

> silent truncation when inserting is still the default behavior, I think

MySQL 5.7, released in Oct 2015, changed the default to enable strict sql_mode. All prior versions have hit end-of-life for support years ago, so there is no modern version of MySQL with this silent truncation behavior.

That said, there's still a major problem, but it's not MySQL's fault; rather, it's Amazon and their nonstandard defaults. AWS's managed database offerings inexplicably change the default sql_mode to disable strict-mode, both for regular RDS as well as Aurora. This is a huge problem, and it also definitely perpetuates this "MySQL still silently truncates!" perception.

Post reply on HN