[sigil]

> Is there really something about PSQL that makes it better than MySQL these days?

In a word: correctness.

Yes, MySQL has an UPSERT implementation. Like so many things MySQL rushed out the door, it's also buggy and unpredictable. Did you know UPSERTing into a MySQL table with multiple unique indexes can result in duplicate records? Did you know MySQL's ON CONFLICT IGNORE will insert records that violate other not-NULL constraints? [1]

I've used both MySQL and PostgreSQL for over a decade, and working around the many MANY misbehaviors and surprises in MySQL requires continuous dev effort. PostgreSQL on the other hand is correct, unsurprising, and just as performant these days.

MySQL is what happens when you build a database out of pure WAT [2].

[1] https://wiki.postgresql.org/wiki/UPSERT#MySQL.27s_INSERT_......

[2] https://www.destroyallsoftware.com/talks/wat

[mmaunder]

Nah. MySQL rocks. I've been using it since 1998 at Credit Suisse, 2000 at eToys.com where we used it to run the entire company from warehouse to web. I used it at the BBC in 2003 for a high traffic Radio 1 application and I've used it since then on my own companies with serious volume including a job search engine featured in NYTimes and Time Mag in 2005 and feedjit.com doing real-time traffic on over 700,000 sites. We use it for Wordfence now which is where the image link comes from I posted earlier with 20K TPS. All very high traffic with consequences if it screws up. I've never run into any of the issues you mention.

You say "upserting into a mysql table". Which storage engine? MyISAM? InnoDB? I find MySQL to be both reliable and incredible durable i.e. it handles yanking the power cord quite well. The performance also scales up linearly for InnoDB even for very high traffic and concurrency applications.

We use redis, memcached and other storage engines - by no means are we tied to mysql. But for what it does, it does it incredibly well.

I'm also completely open to using PostgreSQL and I was hoping someone could give me a compelling reason to switch to it or to use it.

[brobinson]

I've worked at a company that was doing > 20,000 TPS (transactions/sec) on a single PG instance with no problem. That is baby-tier usage for a DB.

As far as why you should give up MySQL like I did four years ago: http://grimoire.ca/mysql/choose-something-else

[simoncion]

> I find MySQL to be both reliable and incredible durable i.e. it handles yanking the power cord quite well.

Mmm. Assuming that your underlying disks don't lie, losing only the data that was in-flight to the WAL (or whatever is the equivalent in your DB of choice) is the absolute worst data loss you should see from a real SQL database in that situation. [0]

Think about the case where an error causes the DB software to crash... You really can't make good data robustness guarantees if an unexpected crash endangers more than just the data that's in-flight to the WAL.

I personally have had the disks that back a rather large (but relatively low-update-volume) Postgres DB drop out on multiple occasions and never lost any data at all. This shouldn't be unexpected behavior. :)

> You say "upserting into a mysql table". Which storage engine? MyISAM? InnoDB?

"In addition, an INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)" [1]

Given that this is an unqualified assertion, straight from the MySQL docs, it seems safe to say that this is a weakness in MySQL's UPSERT-alike, rather than any limitation of a particular underlying storage engine. (Here are some bug reports that also make unqualified statements about the behavior at issue: [2][3])

> ...I was hoping someone could give me a compelling reason to switch to it or to use it.

shrug Both MySQL and Postgres are capable databases. Postgresql has better documentation, appears to have substantially better internal architecture, and -from what I remember of my early days with MySQL- has far fewer hidden sharp edges than MySQL.

I'm not here to tell you to change what DB you're currently using for your production stuff... I don't think anyone is. For my projects, I have had substantially better experiences with Postgres than MySQL.

EDIT: It occurred to me in the shower that I didn't make "loss of in-flight WAL data" sufficiently clear. Unless you explicitly ask for a mode where writes are acknowledged before the DB believes that they're safe on disk, [4] then the only "data loss" in the scenarios I described in the comment would be data that had been transmitted to the server, but not acknowledged as committed to the DB. So, correctly-written client programs would experience this "data loss" only as a transaction that failed to commit, maybe followed by DB unavailability. Sorry for the ambiguity. :(

[0] http://www.postgresql.org/docs/9.4/static/wal-reliability.ht...

[1] http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.h...

[2] http://bugs.mysql.com/bug.php?id=58637

[3] http://bugs.mysql.com/bug.php?id=72921

[4] I say "believes they're safe" because underlying storage can always lie, and there's not a goddamn thing you can reliably do about it if it does.