Why I Choose PostgreSQL Over MySQL/MariaDB

Nerval's Lobster writes For the past ten years, developers and tech pros have made a game of comparing MySQL and PostgreSQL, with the latter seen by many as technically superior. Those who support PostgreSQL argue that its standards support and ACID compliance outweighs MySQL's speed. But MySQL remains popular thanks to its inclusion in every Linux Web hosting package, meaning that a mind-boggling number of Web developers have used it. In a new article, developer David Bolton compares MySQL/MariaDB 5.7.6 (released March 9, 2015) with PostgreSQL 9.4.1 and thinks the latter remains superior on several fronts, including subqueries, JSON support, and better licensing and data integrity: "I think MySQL has done a great job of improving itself to keep relevant, but I have to confess to favoring PostgreSQL."

Comments

I choose MS SQL Server

[Anonymous Coward]

Best of all worlds. And guess what, in the grand scheme of things, the price is a drop in the bucket compared to salaries.

Re:I choose MS SQL Server

[Anonymous Coward]

MS SQL server has its place:

1: Oftentimes a company already has it licensed, so might as well use it.

2: It is auditor friendly, with the pieces of paper (FIPS, etc.) that don't mean much in real life, but do mean a lot when ISO, or other audits happen, and you have to justify your existence and design decisions. (For those who say certificates/certifications don't matter, one place I worked actually had auditors that would fire people on the spot for "failing to have authority to run the equipment" if their RHCE/MCSE/CCIE certs lapsed.)

3: Finding MS SQL expertise is easy.

4: MS SQL does work and is decently secure. For 99.99% of tasks, it is just as good as Oracle.

This isn't to say that PostgreSQL is bad... but there are times where MS SQL is the ideal choice.

Re:I choose MS SQL Server

[Anonymous Coward]

In terms of licensing, I think the MS sales force is learning too much from Oracle. We are backing out from supporting MS-SQL due to the insanely expensive licensing terms for deployments that MS sales is starting to apply to it.

Re:I choose MS SQL Server

[Gr8Apes]

MS SQL server has its place:

Our competitors or enemies servers? A trashcan?

1: Oftentimes a company already has it licensed, so might as well use it.

Lemmings....

2: It is auditor friendly, with the pieces of paper (FIPS, etc.) that don't mean much in real life, but do mean a lot when ISO, or other audits happen, and you have to justify your existence and design decisions. (For those who say certificates/certifications don't matter, one place I worked actually had auditors that would fire people on the spot for "failing to have authority to run the equipment" if their RHCE/MCSE/CCIE certs lapsed.)

Sounds like a thankless place to work, but still doesn't support using MS SQL.

3: Finding MS SQL expertise is easy.

citation? Finding people who have seen MS SQL is easy, finding expertise, however, is as much or more limited than for other systems, mainly because most with real expertise won't touch MS SQL except when the business end of a pointy stick is poking them in the eye.

4: MS SQL does work and is decently secure. For 99.99% of tasks, it is just as good as Oracle.

This isn't to say that PostgreSQL is bad... but there are times where MS SQL is the ideal choice.

MS SQL barely works, and falls over as soon as it is hit with significant load. It's an old massive pile of crap essentially given to MS by Sybase, who mistakenly didn't believe anyone would be stupid enough to continue using that smelly pile when their new database was released. They severely underestimated MS's marketing prowess in this regard, and someone like you (an AC no less!!!) propagates the continuation of this incredibly terrible solution.

Re:I choose MS SQL Server

[Richard_at_work]

The Express Edition of MS SQL Server is pretty damn good for 99% of the deployments you would consider MySQL for, and its free. The limits are memory usage (1GB per instance), database size (10GB), CPU (1 physical or 4 cores) and instances (50 per server).

That should run most websites and business apps fine. Because most websites and business apps are drastically overspecced and under used.

Re:I choose MS SQL Server

[DarkOx]

Was the last version of SQL Server you used 7.0 or something. I love to dump on Microsoft as much as the next guy, but honestly SQL Sever 2000 on is pretty damn good. As far as falling over when hit with significant load, I was running a 60TB database on the first Itanium versions of SQL 2000 back in '04 and it never 'fell over'.

The project was big enough and cost enough Microsoft was willing to send people out to help us tweak and tune. That is all we did though nothing exotic like a custom build or anything. Just end user tuneables and guidance on schema around partition views and like.

So really there are plenty of legitimate criticisms of the Microsoft platform family but SQL Server falling over ain't one of them.

Re:I choose MS SQL Server

[bad-badtz-maru]

It's not cheap at all once you get into even just medium-scale usage. If you have a situation where you are starting out small but plan to grow, you need to really consider whether it's wise to go with a commercial RDMBS, because the pricing does get nasty when you get to the point of needing clusters, high core counts, and standby sites.

Re:I choose MS SQL Server

[MightyMartian]

Translation: It has limits that renders it useless on production servers.

Re:I choose MS SQL Server

[MightyMartian]

There's this little matter of having to run Windows. Not very bloody useful, particularly on production servers, unless you want to a. buy a Windows Server license ($$$) and SQL Server licenses ($$$).

Whereas I can install a Linux or BSD server, throw PostgreSQL or MySQL on it and the cost is the hardware and my time. If I need to add more infrastructure, again it's my cost and my time.

Re:I choose MS SQL Server

[Gr8Apes]

I've had the misfortunate to work with 2000, 2005, 2008 and 2008 R2, and 2012, and every single one of them has failed spectacularly, many of them with the same basic issue, that wonderful escalating locks problem, which MS spins as a "performance improvement" much like driving a bus off a cliff improves its performance, and in much the same way.

Re:I choose MS SQL Server

[phantomfive]

I don't know, in my experience, I rank it like this:

1) Postgresql - full of random features that makes things easier and better (you can use almost any language for stored procedures, for example. Not huge, but nice).
2) MS SQL - Works fine, not too interesting.
3) MySQL - full of random feature that makes things harder (do you know you can't rollback a transaction that modifies a table? Every other database can....).
4) Oracle - Has all the features, but some of them have very funky syntax.

-- -- -- -- -- --

If you support using the 'best tool for the job,' then the choice is obvious, following this algorithm:
1) If you're using a Microsoft stack, use MsSQL (I've tried using entity framework with MySQL....it mostly works, but it's a pain).
2) If you're using a stack that integrates well with MySQL, use MySQL.
3) If you are in desperate need of corporate CYA, use Oracle.
4) For anything else, use Postgresql. You won't regret it.

Re:I choose MS SQL Server

[WaffleMonster]

I've had the misfortunate to work with 2000, 2005, 2008 and 2008 R2, and 2012, and every single one of them has failed spectacularly, many of them with the same basic issue, that wonderful escalating locks problem, which MS spins as a "performance improvement" much like driving a bus off a cliff improves its performance, and in much the same way.

If lock escalation is your problem then lock escalation isn't the problem.

Re:I choose MS SQL Server

[phantomfive]

No, you misunderstood what I wrote. I was referring to the fact that MySQL DDL is not transactional. So if you run an 'alter table' command, don't expect to be able to roll it back. Seriously though, even SQLite offers this feature, so MySQL is just a dunce.

The main difference that I see is that Postgres fans generally have the same zeal and lack of experience that Rails fanboys exhibit. I am not sure where you fall but you are doing a disservice to our community by spouting false claims when you do not understand what you are talking about. (That sounds like a rails fanboy to me.)

I'm pretty sure it means you're a moron. Or rather, you should have thought a little before spouting insults.

Re:I choose MS SQL Server

[RoLi]

(un)paid advertizement:

I love to dump on Microsoft as much as the next guy, but honestly SQL Sever 2000 on is pretty damn good.

Now, if SQL server is "honestly" so good, why are the one million busiest sites slowly migrating away from Microsoft?

http://news.netcraft.com/archi...

In 2008, 20% of the million busiest websites used Microsoft, now only 12% do, and the decline slowly continues.

When we talk about these installations, we talk about very heavy loads, very much data and very high requirements on reliability and availability.

So why does the high-end "enterprise" systems move away from that "pretty damn good" platform? The Microsoft apologists on this thread constantly tell me who licensing costs don't matter and how good all Microsoft products are ("honestly"!) - but exactly in the one area where licensing costs really don't matter (the one million busiest sites) Microsoft is also losing it. So why then?

Maybe it's not as "pretty damn good" as some anonymous internet commentators claim? Honestly?

Duh

[Anonymous Coward]

Who trusts MySQL with important data? No one who knows about PG. Good web frameworks like Django prefer PG, while crap ones like Drupal and other PHPtards prefer MySQL.

At least

[Anonymous Coward]

It's not Access

Re:At least

[HornWumpus]

If Excel is using the old jet engine you get both in one deal.

The worst stack I've ever seen. ASP invokes Access (via OLE automation) which in turn calls FORTRAN. I will smoke a turd in purgatory for showing them how to invoke Access.Application from VB.

I quit in disgust shortly after. 'They' were supposed to define an API without knowing it, so we could replace the Access part. Management said: 'It's working, great lets move forward.'

A more intringuing question...

[leonbloy]

would be why I still choose to read Slashdot instead of ... anything else.

Let me quote, from the comments thread at a recent article by same submitter:

Could we stop having Dice articles submitted by Nerval's Lobster? Why not fully disclose that the story was submitted by the corporate parent of Slashdot?

Another user, in the same thread, had speculated:

What comes next, a thread on "is Emacs better than Vi"?

No, sir, you were utterly wrong. It came "Postgresql is better than Mysql".

SQLite3

[fyngyrz]

And for many tasks, you don't need any of that. Have a look at SQLite3 (also, it's built into Python, which can be handy.)

Worried about stability? You can compile the SQLite3 source code right into your project. That way, your databases always match your shipping product, indefinitely, period.

It's not usable for everything -- only a decent subset of SQL is supported -- but you might be surprised at just how much is there, and working well.

Re:SQLite3

[Bacon Bits]

SQLite3 is a fantastic product, but it's primarily intended as an embedded SQL database, not an RDBMS. They're not really intended to do the same things.

On the other hand, at least SQLite doesn't "feature" silent non-deterministic aggregates.

Postgres hands down

[infernalC]

I come from a Sybase SQL Anywhere shop. It never ceases to amaze me how stuff that can be elegantly expressed in a couple of queries in Watcom-SQL typically takes four times as much code in MySQL's dialect. I love Sybase's support for the ANSI standards, subqueries, Java/.NET/C/PHP/Perl stored procedures when they are the right tool for the job (ever needed to resize raster images in an INSERT trigger coming from some third-party application?), and great drivers. I shouldn't have to spend 10 minutes trying to figure out why MySQL doesn't support the standard casting string concatenation operator by default (||), or why subqueries don't work like they ought, etc.

Having used Postgres, all of the worthwhile MySQL features are there, most of the SQLA features are there, and the pain level is much, much lower in Postgres than MySQL for someone coming from a full-featured commercial RDBMS.

What really sucks is all of the applications that are so coded around MySQLisms that they don't run on ANSI-compliant engines.

Some old quotes...

[mi]

Those who support PostgreSQL argue that its standards support and ACID compliance outweighs MySQL's speed.

One expression I remember seeing on the topic went something like: "I can make it as fast as you want as long as it does not have to actually work". The conversation was about filesystems comparing (the non-complying) async-mode with the safer (but slower) alternatives, that actually stood by the promise of fsync(2).

And another, more modern idea (only about 10 years old) quote is "Object/Relational Mapping is the Vietnam of Computer Science". Which, for the purposes of TFA, may be interpreted as something like "who cares for ACID compliance — we can deal with occasional data-corruption and inconsistencies — just make it fast in the usual case".

I rather doubt, we'll settle the question in this discussion...

Re: I thought we were over the whole SQL thing

[Anonymous Coward]

NoSQL only caught on with dumbasses. Everyone with a brain ignored it.

Re:I thought we were over the whole SQL thing

[ArcadeMan]

Nope, we all moved to XML.

In my experience

[Trailer Trash]

And I'm probably going to step on a lot of toes here, but people like me strongly prefer Postgres to MySQL. And by "people like me" I mean folks for whom their first real rdbms experience was theoretical or "commercial". I did both.

I used ingres in college to a small extent and then the Ingres commercial product for years after that. I have also used Sybase and Oracle professionally. PostgreSQL easily walks among the giants of that industry.

Every time this discussion comes up the MySQL side has to say "yeah, but..." about a thousand times. MySQL doesn't do ______ properly? "Yeah, but if you just install this other piece of software and change a couple of config files it *can* do it.' Well, con-fucking-gratulations!

The point is that PostgreSQL does exactly what it should do out of the box. I don't have to change a configuration file to make it ACID compliant, fast, correct, whatever. It just works and works correctly out of the box.

Every time someone tells me how easy MySQL is to set up they've betrayed their experience level in this realm.

I know a lot of you are going to mod me down - I don't care. But why not reply instead?

Re:I thought we were over the whole SQL thing

[Anonymous Coward]

I moved all my XML into JSON, then stored it in a MySQL table named "JSONXML" with a single column named "data". I call it "web 3.0 agile database". I'm in the process of writing a manifesto and bunch of books about it.

Bottom line

[sjames]

In the beginning, Postgress set out with correctness as the primary goal. Whatever it did, it had to do it correctly. It started life on the slow and resource hungry side. MySQL set out to be fast and more or less correct in the common case. Back in the '90s that made a lot of sense for small servers.

In the decades since, servers have gotten bigger and Postgress got fast and efficient while still being correct. Why would I want to incur a performance penalty in the surrounding software to check behind the database to make sure it didn't just scrag my data?

Re:Postgres has referential integrity

[rycamor]

That's not even close to what "referential integrity" means. In fact, it could be used to accomplish quite the opposite.

OIDs are one feature of PostgreSQL that should be buried inside the implementation and not allowed to be accessed from the developer side. Otherwise you are pretty much completely going around the whole point of the Relational Model. If you are developing an application in such a way that it needs pointers to rows, you might as well just store data on the filesystem and be done with it. Or use one of those fancy NoSQL thingies and enjoy your data corruption.