[Question] PostgreSQL vs MySQL?

3    29 Jul 2015 19:26 by u/a_of_s_t

All I can find are old articles stating that MySQL has a larger community, more support (from managed VPSs), and that PostgreSQL used to be slower.

Is this still the case? I hear that PostgreSQL is ACID compliant, but MySQL is not. Are there any recent articles demonstrating the pros and cons of both?

5 comments

2 u/Craftkorb 29 Jul 2015 22:07

First, MySQL (Or MariaDB) is also ACID compliant. If it does it well is a question I can't answer, as I barely ever used it.

I want to however point out why I think PostgreSQL is a overall 'better' solution. Disclaimer: I haven't used MySQL in a long time myself, things may have changed!

  • Foreign Data Wrappers: If needed, you can access all kinds of other databases through PostgreSQL. This may be another SQL DB, MongoDB, or something else. This feature is comparable to UNIXs mount functionality. You may end up never using these though.
  • The #postgresql IRC channel over at Freenode is quite active and helpful.
  • Also, their documentation is great.
  • Many features are added every release. Can't wait for the built in UPSERT coming in version 9.5 (UPSERT = INSERT or UPDATE if it exists)
  • It supports JSON directly. You can work with stored JSON documents much like with any other data type. You can SELECT them, you can even add indexes. This is a huge plus in my book.
  • If you fear it's hard to set up yourself, don't: It worked for me out of the box, no configuration changes needed.
  • PostgreSQL is more happy with throwing errors. So, if you have a VARCHAR(30) column and you want to put something in there longer than 30 characters, MySQL (by default at least) will just chop off everything coming after the 30th character and carry on. PostgreSQL will raise an error, telling you that it's too long. This is a really important thing to have, as MySQLs behaviour may just break things unexpected.

If I can, I'll stick to PostgreSQL.

1 u/Drenki 29 Jul 2015 22:47

Pretty much all of this goes for either MySQL or MariaDB.

  • MySQL has had "foreign data wrapper" support since around 2007, they call it Federated Databases.
  • #mysql on Freenode.
  • https://dev.mysql.com/doc/ - some of the best-written documentation anywhere.
  • MySQL has a good number of creature-comfor featurse. For example, the "UPSERT" command has been with MySQL for a decade. MySQL also has had the "REPLACE" command which will either add a new row, or replace one if the values you're passing match an existing row's primary key.
  • MariaDB has support for working with JSON directly, though I don't know why you'd do this at the database level unless you absolutely had to. Probably makes more sense at the app level.
  • MySQL can operate in a strict mode where it will complain about truncated values or bad dates more vocally. (By default, it generates warnings, in strict mode, it will error.)

One of the things that really took me by surprise with Postgres is how rigid it is. For example, there is no good way to join tables from two different databases, even if they are on the same server. Actually, I think you can do it with federated databases now, but it's still a bunch of hoops to jump through. With MySQL the syntax is straightforward, the only drawback is that the user doing the query must have the right permissions for both.

There's lots of little differences like this (and big ones, too). At this point, I don't even know if it makes sense to try to compare them. For your purposes, either is most likely going to do the job and then some.

2 u/Drenki 29 Jul 2015 22:22

Man, it's an old, tired debate as to which is better.

For small projects, really doesn't matter. For serious projects, you're likely going to need to mod the database itself to get the featurse / performance you need.

A common jab at MySQL / MariaDb is that its SQL syntax is not 100% ANSI compliant. In the real world, it doesn't matter too much whether your SQL is 100% ANSI compliant as the one advantage would be for when you switch database providers and that almost never happens. Even when it does happen, it's likely that you had some kind of wrapper around your SQL and that wrapper will be able to gracefully handle the switch to a new database system. In day to day use, the choices made for MySQL syntax make sense and make the life of a DB admin a lot easier.

Actually, all of the tools around MySQL make it a nicer environment for administration. phpMyAdmin and the MySQL command-line tool are head and shoulders above what PostgreSQL has on offer (i.e. pgAdmin III).

ACID compliance. PostgreSQL and MySQL both offer ACID compliant databases (MySQL through its InnoDB storage engine).

There is the problem of scaling for both. Once you get past a single server, unless it's a simple master-slave set up, you're going to have to make a serious compromise. Take a look at this matrix to get an idea of how messy the landscape is here. For MySQL, there is a storage engine called NDB that's made for high-availability applications. One major downside is that the tables must be kept in memory so you will have some beefy hardware requirements. NDB seems to provide a much more sophisticated solution to PostgreSQL's.

Short story, for a small-medium website, either is going to work pretty well for you. MySQL / MariaDB tends to be more intuitive while PostgreSQL tends to be more "correct."

Here's a good example of how MySQL is easier to work with in a day-to-day situation. TL;DR While column order reallly doesn't matter too much, MySQL goes above the strict ANSI SQL to give you a waaaaay nicer way to add a column to a table where you want it. PostgreSQL is stuck with the mentality that plain ANSI syntax is the only thing they'll consider. I run into these little quirks all the time since I work with both DBs and it amazes me how fun it is to work with MySQL while PostgreSQL is such a pain.

For day-to-day maintenance, there's only one thing I've found Postgres doing better and that's listening when I tell it to shut down a run-away or locked query. Doesn't happen often, but when I need it, Postgres handles it pretty quick. been a while since I've had to do it on the MySQL db but a few years ago when it was more actively worked on, it seemed like the kill command was obeyed only sometimes.

1 u/Cuddlefluff 30 Jul 2015 00:57

Unless configured intently, specifically and correctly, MySQL is a complete clusterfuck. Stick with PostgreSQL unless there's some sort of legacy reason to use MySQL.

However, for business applications and high-performance environments I recommend going for the higher tier (the non-free databases) as they tend to outperform and out-feature both of them by a very significant amount.

0 u/Tingfar 29 Jul 2015 20:30

Check out MariaDB instead MySQL. They are both very powerful dbs now and the choice is really what you are most familiar with.