Slashdot Mirror
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."
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.
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.
The cesspool just got a check and balance.
> 3: Finding MS SQL expertise is easy.
No. Not really. Microsoft pushes the idea that you don't need to have any clue to use it's products. It helps enable this idea with better novice interfaces. This leads to the problem that you end up with barely trained monkeys having the appearance that they can us Microsoft products.
> 4: MS SQL does work and is decently secure. For 99.99% of tasks, it is just as good as Oracle.
I think Microsoft has the only RDBMS that ever had a genuine viral exploit in the wild. That's quite an accomplishment. SQL Server also has some "subtleties" that make it oddly more user hostile than even Oracle.
A Pirate and a Puritan look the same on a balance sheet.
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.
So your solution, instead of using a proper layer of abstraction (like a service), is to throw everything into the database? Yikes.
The database IS a layer of abstraction.
Exactly! It's always blown my mind how much genuinely important functionality MySQL zealots are willing to do without in the name of simplicity. It really lets me know how little experience that have with genuinely mission-critical systems, or how small the environments they've worked in are.
I absolutely think MySQL has its place, primarily as a support DB for web frameworks - but honestly that's mostly just because it's so firmly entrenched there that I long ago gave up trying to suggest that the developers make their systems more database agnostic (plus, the work to do so would be better spent on making those frameworks less of a pain to wrangle, from what the PHP developers I know tell me).
When I need to keep a system stable, and ensure that things as important as say, financial transactions don't get messed up by silly things like improperly handling transactions or some other thing MySQL is stamping its feet about staying on the hobbyist side of things for, it's a no brainer to me. Plenty of people will preach the benefits of a commercial vendor (mostly just because it makes passing the legal hot potato easier if you really screw up customer data - the issue of support should be a no brainer with how many consultancies are out there, if you happen to somehow hire a total dullard as your DBA), and for them there's EnterpriseDB - a commercially produced and supported version of Postgres.
What's more, Postgres completely blows the doors off of most other platforms. I've worked with production data in tables with up to a billion rows (yes, that one should've been in hadoop, but that wasn't a very agile company, and honestly it's a bit silly to setup hadoop for one table out of about 1200 they used), where MySQL would've been completely out of its element, and Postgres handled the load just fine (mind, querying against a billion rows on ANY RDBMS requires carefully crafted queries if you're going to avoid bogging down production). In data warehousing, the company needed that same data set (admittedly aggregated via ETL) in a proper EDW for consumption in their BI presentation tools. Our ETL tool was capable of outputting to multiple output sources very easily, and at one point I was tasked with deciding which platform was the best (we had Postgres, MS-SQL and Oracle servers, all tuned for peak performance). Long story short in terms of the ETL done, the output data set was say, 80 million rows or so to the fact table. Postgres was, by a large margin, the fastest to finish writing the data - and it did so on the smallest hardware. Oracle took almost an additional 30 minutes to complete the load. MS-SQL was behind by a margin I honestly don't recall. This was basically a debate between myself and the CIO about whether Postgres or Oracle should be our EDW platform, so I didn't commit the gap to memory.
When it came time to query against the data for BI, Postgres 9.1 was 10-15% faster than Oracle (mind, this is with half the CPUs/cores, and 1/8 the RAM of the Oracle box, which had been tuned by a BI consulting firm specifically for BI/OLAP performance). When tested against Postgres 9.2 (which added the benefit of index-only scans), the Postgres box was easily 25-30% faster than the Oracle box, at its slowest.
Most of the people who champion MySQL have never worked with a data set like that, so I don't hold their naivete against them, but it's cute to hear them go on about how clunky and difficult Postgres is...
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.
Translation: It has limits that renders it useless on production servers.
The world's burning. Moped Jesus spotted on I50. Details at 11.
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.
The world's burning. Moped Jesus spotted on I50. Details at 11.
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.
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.
"First they came for the slanderers and i said nothing."
If you're doing something that data intensive and can't afford a DB license, then you're doing something wrong.
The sheer stupidity in that statement is outrageous. You may not believe it, but it's not just Fortune 500 companies that do "data intensive" stuff. Even a freelancer (i.e. a single person) is able to do "data intensive" stuff.
So, whether you can "afford" a DB license depends on the application. There are lots of applications where using DB would price you right out of the market.