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

[Jhon]

Plus installing LAMP is sounds cooler than installing LAPP.

Re:I choose MS SQL Server

[Foofoobar]

Yeah and it runs GREAT on Linux. Like a rock.

Re:I choose MS SQL Server

[neminem]

More like for 100% of tasks. MSSQL is pretty good; a bit of a pain to install, and obviously crazy pricy unless you're just already paying for it for some other reason, but it's plenty powerful and pretty simple to use.

Oracle, on the other hand, is a steaming pile of turd.

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

[TechyImmigrant]

More like for 100% of tasks.

I tried invoking it on the Linux server that runs my back end and it couldn't even find it. It's worse than useless for that task.

Re:I choose MS SQL Server

[neminem]

Ok, fine, you win. Oracle does technically work on Linux (for sufficiently gross values of "work"), so it does beat MSSQL in that regard. Still... while I certainly understand the existence of non-Microsoft ecosystems and that that could in some cases be the smart choice, I definitely can't understand going with a Linux setup and then choosing to use *Oracle*, for *anything*. Why would you possibly hate yourself that much?

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

[jedidiah]

> 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.

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

[jedidiah]

So? There's a free version of Oracle too, if you are willing to expose yourself like that.

"Getting something for nothing" usually isn't the point of using "serious commercial enterprise software".

Non-free Oracle can even be licensed in ways that make it as cheap as a more expensive consumer application.

Re:I choose MS SQL Server

[jedidiah]

Oracle doesn't "merely work" on Linux, it's been Oracle's flagship platform for a number of years now. It took that title away from the darling of the commercial Unix world (Solaris Sparc).

Oracle may have it's warts but at least it isn't pre-configured to eat itself. No wonder Windows admins are so used to rebooting machines so often.

You would think that Microsoft would at least use a sane, sensible, and industry standard default.

Re:I choose MS SQL Server

[TJ_Phazerhacki]

Shhhhh, don't say that out loud! This is Slashdot after all - MS is the Devil!

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

[segedunum]

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

Alas, everyone has to pay salaries and then it's a question of how much you have left to get anything done. Nice try though.

Re:I choose MS SQL Server

[TechyImmigrant]

Why said Oracle was an option being considered?

I've used a few databases and they all suck with layers of excess complexity. BDB and related DBs are ok. They don't suffer interface bloat nearly as much. A Turing complete query language is completely stupid from a security standpoint.

I'm moving to an in-memory database held in a running program with the client transaction atomicity enforced through the API and with a transaction log to disk from which the running state can be re-built.

It used to be a problem to keep things in memory, but I can drop 64Gig of RAM in a server and keep all the store inventory and sale data live. The clients don't know the difference, except they don't have to deal with build SQL strings or typing the schema to the constraints of the latest and greatest ORM to come along this week.

God I hate SQL databases.

Re:I choose MS SQL Server

[Alain Williams]

Those are the current limits. So do you build your business round the database that is free today and hope that: a) your business does not grow so that it needs more, and b) that MS does not reduce the limits and catch you. Either way you run the risk of ending up having to pay the license fees. Why not pick a database that will always be free - and keep that cash for something else ?

Re:I choose MS SQL Server

[Richard_at_work]

You can use that translation if you ignore my entire last paragraph...

For fetching content for a CMS for a My Little Brony blog, its perfectly fine. Even in production.

Hell, I do complex ETL on hundred million row datasets using SQL Server Express...

Re:I choose MS SQL Server

[Anonymous Coward]

I've got it running on quite a few production server. I've got others running MSSQL enterprise, and others runng Postgres. Just because it doesn't fit what you do doesn't mean it won't work fine in production in a lot of other places.

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

[Anonymous Coward]

Depends on what you mean by production. If your needs are modest then why not use the free version?

Re:I choose MS SQL Server

[WaffleMonster]

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.

This is exactly why we recommend Microsoft SQL Server to customers. Barely trained monkeys is more realistic than expecting a trained DBA on staff.

I think Microsoft has the only RDBMS that ever had a genuine viral exploit in the wild.

So what is the relevance some dozen years later? By all measures SQL Server has had a good security record compared with competing products. Check public CVE data for each product and make an informed decision.

Left a test Oracle server running overnight accidentally a number of years ago it had been owned by time I got in the next day...cherry picking is worthless... everyone can find an example supporting their presuppositions.

Re:I choose MS SQL Server

[Richard_at_work]

If your business grows, its trivial to migrate from Express to Standard - and I do mean trivial. In most circumstances you can just detach the DB files and reattach them to the Standard instance.

And MS has yet to reduce the Express limits, infact they tend to expand them with each new version.

A major issue with picking databases is whether they come with decent client libraries, so my decision making process rarely puts a significant amount of weight on whether something has no licensing cost or not. A much larger factor is how hard is it to use in code.

Re:I choose MS SQL Server

[Anonymous Coward]

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.

One of the great ironies of SQL Server is that their original Java (JDBC) drivers were crap. I had to work with an app that was heavy into transactions and the Microsoft driver couldn't handle it at all.

Fortunately there was a public-domain SQL Server JDBC driver on SourceForge that had no problems with all those transactions.

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

[Anonymous Coward]

MS products are just garbages

Re: I choose MS SQL Server

[senatorpjt]

I work on a large data-driven application. At the insistence of a couple of large customers, we have to support MSSQL on the backend in addition to Postgres. MSSQL is the bane of my existence.

Re:I choose MS SQL Server

[MightyMartian]

Or why not use PostgreSQL?

Re: I choose MS SQL Server

[senatorpjt]

99% of the deployments I would consider MySQL for are low-volume PHP sites that will be deployed on shared Linux hosting, so no.

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

[Nostalgia4Infinity]

MySQL is pretty damn good for 99% of deployments, it scales and is always free. I'm not seeing the benefit over MS SQL.

Re:I choose MS SQL Server

[Anonymous Coward]

InnoDB is the engine that makes the differences between Postgres Maria/MySQL largely inconsequential.

ROFLMAO

Re:I choose MS SQL Server

[Anonymous Coward]

Awesome troll

Re:I choose MS SQL Server

[Anonymous Coward]

"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."

Please, out companies like this in public so those of us with dignity and self-respect can avoid those hellholes like the plague.

Re:I choose MS SQL Server

[DogDude]

the pricing does get nasty when you get to the point of needing clusters, high core counts, and standby sites.

If you're doing something that data intensive and can't afford a DB license, then you're doing something wrong.

Re:I choose MS SQL Server

[Bengie]

How do you maintain relational constraints? I love constraints of all kinds, saves me a lot of time from bugs messing up data.

Re:I choose MS SQL Server

[Anonymous Coward]

Hard to install? Are you confusing MSSQL with Oracle?

Re:I choose MS SQL Server

[Anonymous Coward]

Hell, I do complex ETL on hundred million row datasets using SQL Server Express...

Then you're small potatoes. Seriously. Step into the Oracle pool with real datasets.

Re:I choose MS SQL Server

[the_B0fh]

Umm, isn't the point of a "SQL" database, SQL? What sort of client libraries do you need such that something like postgres is not able to support you?

Re:I choose MS SQL Server

[Anonymous Coward]

I had to rate this as Flamebait because you are ignoring over 90% of the market which in terms of "Servers" runs on Linux. I don't care what you do on your desktop where MS has the market share still. Here is a very short list of big players who's whole back end is Linux. Google, Facebook, Amazon, Godaddy, Rackspace, Salesforce, HP's Cloud, CSC's Cloud, IBM's Cloud, Yahoo, SAP. Oracle would be about 1/2 and 1/2 since they do still support massive Sparc hardware running Oracle (128-256 processors). Would you also like to play the game with Telecom's and see who runs back end Linux vs. MS? You would lose that one too.

And look, I do understand that mom&pop shops (mostly small 1 offs built by people that "think" they are smarter than everyone else) run MS products for "Servers". More often than not, they end up having to outsource their "Servers" after getting royally fucked over by the guy who thought he knew everything.

Re:I choose MS SQL Server

[TechyImmigrant]

How do you maintain relational constraints?

With a few lines of python that put constraints in the database.

Re:I choose MS SQL Server

[Anonymous Coward]

You obviously have zero experience to compare. Out of box default install for MSSQL is as easy as Oracle, maybe a bit easier with a wizard launching at the end of the install as opposed to reading a document to run 1 command. Now in MSSQL configure a different location for transactions, logs, and databases and see how easy the install is. Those same things are a joke to configure in Oracle.

Re:I choose MS SQL Server

[phantomfive]

SQL Server also has some "subtleties" that make it oddly more user hostile than even Oracle.

What subtleties are those? Serious question.

Re:I choose MS SQL Server

[plopez]

"It is blazing fast for small amounts of data with low concurrency."

Just about anything can be blazingly fast of you turn off logging, constraints, transactions, and cache the entire DB in RAM. Call me when you hit terabytes and then I'll take a look at your performance tuning.

Until then... later...

Re:I choose MS SQL Server

[cowdung]

In many organizations the App is not king. The database is.

For such organizations using an in-memory database is usually frowned upon. SQL is king of the hill.

Re:I choose MS SQL Server

[Anonymous Coward]

MS makes good products -- my biggest frustration and why I'm beginning to move away from them is licensing is frustrating as hell. I've been a Linux person my whole life but I fell in love with visual studio, dynamics NAV is hands down the best ERP available, Windows 7 was awesome as a desktop environment -- then we got the license audit and I have 3 different people telling me 3 COMPLETELY different things about what licenses I need. One even said I needed to get CALs for a DNS and DHCP server!

Now I'm trying to get NAV 2015 -- I literally can't figure out who gets my money. Is licensing included in Azure? Who knows...

Re:I choose MS SQL Server

[bingoUV]

Currently i an having to use a database as a slow shared memory between different instances of the application (kind of a cluster, but not exactly). Do you intend to never run multiple instances of your application working together, say, under a load balancer?

Re: I choose MS SQL Server

[Anonymous Coward]

That's why I call it PLAP.

Re:I choose MS SQL Server

[RoLi]

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.

Re: I choose MS SQL Server

[Anonymous Coward]

So you responded without understanding his point and quickly descended into insults.

And HE'S the fan boy?

Re:I choose MS SQL Server

[RoLi]

3: Finding MS SQL expertise is easy.

That's a lie - and you know it. Everybody (and their dogs) runs MySQL. Wordpress uses MySQL. Pretty much everybody in the computing industry who is working with databases (except maybe some Microsoft-diehards who refuse to run anything not from Redmond out of principle) has at least some MySQL experience.

Re: I choose MS SQL Server

[RoLi]

Exactly. My hoster even charges extra for the privilege to run Windows. (Here in continental Europe, Windows is used on less than 10% of servers) - which makes MS SQL a "no go" for most installations.

In fact I would not even consider a database that runs only on a proprietary platform.

Re:I choose MS SQL Server

[Hognoxious]

Oracle produce their own distro tweaked for running it, so they clearly don't hate Linux that much.

http://en.wikipedia.org/wiki/O...

Of course it could be a trap...

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?

Re:I choose MS SQL Server

[RoLi]

It's always the same. The realists come with real issues, real arguments and real examples.

The Microsoft apologists only say that it's "pretty damn good". That's it. No specifics, no reasons, no nothing. It's just that good, "honestly".

Re:I choose MS SQL Server

[Kjella]

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.

And they're selling a very popular OS that barely trained monkeys can manage.
And they're selling a very popular office suite that barely trained monkeys can use.
And so on.... the world runs on barely trained monkeys.

If you have a problem with that, take it up with the Creator who made a 1% patch to a chimp.

Re:I choose MS SQL Server

[Anonymous Coward]

You are aware that MSSQL can run lock-free (MVCC snapshots) for like 10 years, right?

Re: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.

Friends don't let friends use Micro$oft products.

Re:I choose MS SQL Server

[Drinking Bleach]

And how do you talk SQL over some form of socket? You need a driver.

Re:I choose MS SQL Server

[Pascal Sartoretti]

The Express Edition of MS SQL Server is pretty damn good for 99% of the deployments you would consider MySQL for, and its free.

It is not free, its price is included in the Windows Server licence.

Re:I choose MS SQL Server

[Fallso]

Then run it in AWS or another cloud if you want the cheap option.If you're crunching data 24/7 and still can't afford that licensing structure, you really are doing something wrong.

Re:I choose MS SQL Server

[TheRaven64]

Because needs grow. The entire point of the free version is to encourage people to use it for everything and then discover that their data has grown to over 10GB and they can either pay MS for the full version or spend a lot more migrating all of their data and software to something else. If you start out on PostgreSQL, then you don't have that issue.

Re:I choose MS SQL Server

[drinkypoo]

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.)

Put up or shut up: Tell us who it was, or save it. This FUD just stirs people up and helps nobody.

Re: I choose MS SQL Server

[senatorpjt]

FWIW, I used to work at a webhosting company, there are a few reasons it costs more money. Aside from the obvious (licensing costs) it requires more hardware per hosted site, and is much more costly time consuming to administer (I could fix most problems on a Linux server faster than I could RDP into a Windows server).

Re:I choose MS SQL Server

[gmack]

It also, quite amusingly doesn't come pre tuned to handle Oracle databases. Last month a burst of traffic crashed two Oracle instances on Oracle Linux machines I don't maintain. When they called me in to debug it, the problem turned out that the SHM limits were too small.

One thing I absolutely love about PostgreSQL is that it sanity checks the system limits on startup and throws an error if something is off.

Re:I choose MS SQL Server

[Richard_at_work]

And yet you ignore the fact that most uses of databases are for "small potato" uses, in fact I wouldn't hesitate to suggest that the vast majority of databases are idle 99% of the time, and barely stressed for the remaining 1% of the time that they are actually doing something.

Hence doing ETL tasks on SQL Server Express with 100 million data rows is something I do - and I don't pay money to do it. And the free version of SQL Server is perfectly adequate for the job.

Re:I choose MS SQL Server

[Richard_at_work]

Sure, talk SQL. Now, what format are the data sets being returned in, and can you get access to the execution plans etc for audit purposes? How easy is it to integrate with an ORM. How easy is it to roll back a transaction. Does it choke when pulling a million rows?

There is no point in having a rock solid database if the results being returned to your code are untrustworthy due to poor quality client libraries.

In other words, you don't have any idea about the intricacies involved.

Re:I choose MS SQL Server

[jeremyp]

MyISAM is basically BerkeleyDB with a server process over it.

I think that's unlikely. If it were true, it would be able to support transactions and more granular than table locking because BerkeleyDB does.

Re:I choose MS SQL Server

[Anonymous Coward]

Oh dear.

This is an internet facing database for one of the bigger Insurance companies in the UK. And this is just their UK site... they have others across all Europe and the US.

SQL> select sum(bytes)/1024/1024/1024/1024 "Tb" from dba_data_files;

        Tb
----------
8.99534374

When I log into the MIS database thats updated by countless GoldenGate streams from other regions the numbers are truly staggering.

Maybe this website doesnt qualify for the statement "That should run most websites and business apps fine". But no sane person wopuld recommend this enterprise use anything other than Oracle.

Not yet anyway.

Re:I choose MS SQL Server

[DogDude]

There are lots of applications where using DB would price you right out of the market.

Like what?

Re:I choose MS SQL Server

[bad-badtz-maru]

Just the wording "a DB license" leads me to believe your exposure is with smaller companies and smaller datasets. Have you ever priced a single 32 core Oracle installation with the data warehousing option enabled? If it's a business of any size, you'll need to cluster for reliability, so multiply that single license out. And if it's a publicly traded company, you'll probably need an offsite standby cluster. Even though it sits there and does nothing 24/7, the licensing fee for the standby cluster will be the exact same price as if it was the primary. Also, don't forget dev and testing environments, those aren't free.

Not every large company is a high-margin high tech company. The retail sector, for example, has mountains of data and relatively low margins. DB license expense can be a big concern.

Re:I choose MS SQL Server

[Anonymous Coward]

I thought Oracle can't rollback DDL too? It commits automatically.

Re:I choose MS SQL Server

[aralin]

It is. We all here have 15 years of experience with Microsoft as the Devil and this makes it hard to ever trust them again. Because we did trust them and trust them again and again and again and every single freaking time, they've done something so horrible to break this trust that our very souls have felt like burning in hell.

Re:I choose MS SQL Server

[Anonymous Coward]

Generally speaking I would rather hire 2-4 more developers than have to pay that money in licenses. Or being cash flow positive as a startup.

Re:I choose MS SQL Server

[jeffmflanagan]

So do I, but only because that what I'm required to use at work, and I wanted to have the same platform at home for my side business. I bought a used server with an SQL server license from a failed business for about the value of the hardware. SQL server doesn't need to be expensive if you look for a deal.

Re:I choose MS SQL Server

[TechyImmigrant]

If the business got that big, I'd be hiring people to do it for me and there would be some benefits to using more standard approaches since getting people who think like I do would suck and it would suck for employees to have to follow their boss's crazy ideas on the right way to make a responsive database for a point of sale environment.

But there's nothing preventing an in-memory database being duplicated, load balanced, redundantized (or whatever the right word is for adding redundant mirror servers) etc. In fact the consistency transactions are easier because the parties are algorithms talking to each other, rather than algorithms talking via a storage medium. You would have to write it yourself though since the world still seems to have a hard on for SQL long after they left Cobol behind, which is bad for the same reasons. In a point of sale situation, the order of transactions is not that important except for specific things, like you can't return an item before you've purchased it. So it's easy to post-hoc order the transaction log, a bit like the bitcoin block chain, where the current head of the list is indeterminate, but it's resolved a few steps back.

My issue isn't with disk vs. memory. That's a normal tradeoff. My issue is with SQL, which is horrible in every way that an insecure, computationaly undecidable lump of middleware can be.

Re:I choose MS SQL Server

[Richard_at_work]

And yet I can run it on a Windows client, so its not included in the server license...

Re:I choose MS SQL Server

[Bacon Bits]

Eh, there is less control in SQL Server over locking than there is in other RDBMSs, and it is infamous for escalating locks to the page or table level even when you ask for lower level locks. It's rare that it happens, but it's not unheard of. The fact that the system uses optimistic locking and there's no good equivalent to SELECT ... FOR UPDATE is also somewhat problematic.

It's greatly mitigated in 2005+ by using read committed row versioning (MVCC) and/or snapshot isolation, but those are database level options and you may need to specifically request the right isolation levels with your code. The biggest problem is that you have to remember to use the feature; it's just always on with Oracle from what I hear (I haven't used it since I was in school).

There's a mountain of documentation (and videos!) from Microsoft on all this. The greatest thing about SQL Server is the extremely high quality of the documentation. It's a joy to learn about compared to IBM's DB2 documentation (but then, anything is better than IBM documentation), and Books Online is a step ahead of Oracle, MySQL, and PostgreSQL.

Re:I choose MS SQL Server

[Anonymous Coward]

Nobody is expecting something for nothing. There are plenty of companies offering commercial extensions and commercial support for PostgreSQL at a fraction of the cost Microsoft or Oracle think their shit is worth.

Re:I choose MS SQL Server

[phantomfive]

There's a way to do it, but the syntax is really messed up.

Re:I choose MS SQL Server

[TechyImmigrant]

In many organizations the App is not king. The database is.

For such organizations using an in-memory database is usually frowned upon. SQL is king of the hill.

Alas, ((Popular != Correct) && (Popular != Optimal) && (Popular != Secure)) == True.

Re: I choose MS SQL Server

[cinky]

Depending on what "production" means. A company website with cms? An internal IS for a small company? Absolutely no problem in production...

Re:I choose MS SQL Server

[Bacon Bits]

Really? My experience says the opposite. When you get to the point where you need clusters, high core counts, and standby sites, the licensing costs of your RDBMS are a drop in the bucket. Sure, $100,000 looks like a lot, but next to the $500,000 you're spending on infrastructure and the $10 million you're spending on the application itself, you're really not spending all that much.

Re:I choose MS SQL Server

[Anonymous Coward]

On normal servers you have about 4 - 40 times as much databases. So limiting that number to 50 will increase you cost by a factor 4 (at least). You make expensive websites.

Re:I choose MS SQL Server

[greg1104]

Oracle has features like Edition-Based Redefinition for this job. They are a lot more work for the simple job of transactional DDL.

There's an old but accurate at the time page comparing this feature across databases at Transactional DDL in PostgreSQL: A Competitive Analysis.

Re:I choose MS SQL Server

[Gr8Apes]

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

I did get around it by writing a custom implementation of the commercial module we were using for the particular transaction in question. It is true that the implementation sucked, but it sucked less on other DBs vs MS SQL, which just goes back to MS SQL sucks. I've had similar issues on Oracle and DB2, and neither of those went legs up under similar conditions like MS SQL. It's literally like driving off a cliff in a bus.

Re:I choose MS SQL Server

[Gr8Apes]

Sorry, MVCC does not magically solve all problems, especially under high concurrency loads. It solves a subset, but still doesn't overcome the fact that the underlying DB is shit. Why doesn't MS ship SQL server with MVCC fully enabled by default?

Re:I choose MS SQL Server

[Gr8Apes]

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

It's honestly better than Access.... :-D

Re:I choose MS SQL Server

[Hognoxious]

It also, quite amusingly doesn't come pre tuned to handle Oracle databases.

It's possible that your usage is atypical. It's also possible that Oracle are happy to charge you 2k bucks a consultant-day for "customisation".

Let's balance the hypotheticals with some actuals. One, Larry likes yachts. Two, yachts don't buy themselves.

Re:I choose MS SQL Server

[bad-badtz-maru]

Seriously? You think a clustered, 2 site license only runs $100K? Why are you even responding when you clearly have no idea what the pricing is. I assure you, it's NOT a drop in the bucket. Your pricing for the application side is equally clueless, except in the other direction.

Re:I choose MS SQL Server

[austinm3]

Actually, SQL Server does not provide a native JSON type which is one of the key benefits the author mentioned for PostgreSQL. However, there is a tool called JSON4SQL (http://www.json4sql.com) which is a port of the PostgreSQL JSONB type to SQLServer. That data type plus SQL Server is a pretty good alternative to PostgeSQL.

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.

Re:Duh

[Anonymous Coward]

Also, good luck on being Oracle-owned!

Re:Duh

[nullchar]

Double duh. (tag story with "duh"). Try using a CTE to insert into a history table with the results of another table's delete in MySQL.

Re:Duh

[Anonymous Coward]

Almost everything you said was correct. You blew any credibility with:

Good web frameworks like Django

Re: Duh

[bill_mcgonigle]

No kidding. I made the switch myself 14 years ago, nearly the same comparison. I was an msql user, found MySQL, then took a job where they used Pg. "Let me show you subselects" and that was that. I'm a total software "liberal" - show me a better solution and I'm there. The degree of software "conservatism" amazes me.

Re:Duh

[coofercat]

Whilst I dispute the claim about Drupal being crap, the core works just fine on Postgres (although you will have to live without a good chunk of the contrib modules, but in my experience most of them fall into your PHPtards category).

There is one very, very good module called dbtng_migrator which can take one type of Drupal database and convert it into another one. I just used it to convert some old MySQL based sites to Postgres. There are comments on the website about someone using it to convert from something to SQLite to 'sunset' a couple of Drupal sites. Either way, it's excellent - and means there's literally no reason to use MySQL (unless you want to). I'll be converting my home projects to Postgres when I get time to do it.

At least

[Anonymous Coward]

It's not Access

Re:At least

[Anonymous Coward]

It's not Access

True.

If Access breaks, you at least have a throat to choke.

When MySQL shits the bed, you're up shit creek - without a boat, much less a paddle.

Re:At least

[newcastlejon]

Better Access than an antiquated, undocumented rats' nest of VBA cruft in Excel.

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".

Re:A more intringuing question...

[Anonymous Coward]

I will repeat again for those who missed it then:
If you were around long enough, you'd already know he's Nick Kolakowski, a known astroturfing employee.

Re:A more intringuing question...

[tbuddy]

Totally came to the thread for this. Was hoping to have someone rant that Postgre was for hipster rails developers, but comments are pretty early.

This guy should really update his website to say Postgre is superb

Re:A more intringuing question...

[jtollefson]

What easier way to do market research to sell? Start a hot-topic discussion among a known entity that has depth in the field.

Everything is marketing these days, more than ever before the old saying rings true.

"You are not the customer, you are the content."

Re:A more intringuing question...

[Anonymous Coward]

I think this whenever I see an article starting with "Why...". My mind always fills in the rest with "...should I give a crap what you think?".

Slashdot is no longer news for nerds, that ship has sailed, but could we at least try and stick to news and avoid all this op-ed shit?

Re:Microsoft SQL server is the way to go

[cheesybagel]

MS SQL Server is for 3rd world lamers. Macho PHBs demand Oracle!

Inertia

[Anonymous Coward]

I use MySQL/MariaDB mostly out of inertia. When I started working on websites, MySQL was typically the default software available. When I went on to set up my own websites and various other projects I continued to use MySQL out of habit, it was what I knew. Today I continue to use MySQL/MariaDB, mostly because I've never needed to do anything that it did not do well.

I have heard about corner cases where other databases perform better are behave in a "more correct" way, but none of those cases has ever been relevant to me, so I continue to happily use MySQL.

Re:Inertia

[Anonymous Coward]

Today I continue to use MySQL/MariaDB, mostly because I've never needed to do anything that it did not do well.

So in other words, you've never needed to do anything.

Re:Inertia

[nullchar]

You must only be using the most basic of SQL features. Any quick research towards advanced SQL points to Postgres.

Re:Inertia

[HornWumpus]

Been there, done that.

Dbase 3+/Foxpro/Dataflex was as good as MySQL is today. Eventually I learned to do things better.

The really huge disservice is getting kids started on MySQL. How many wasted personlives?

At least when we were using weak databases the strong ones required big iron or a strong workstation. Your just using crap from ignorance.

Re:Inertia

[Anonymous Coward]

The majority of users for almost any sophisticated piece of technology use only a small subset of the features, so it's no surprise that so many can get away with what MySQL offers.

Hell, I don't even use 1/3 of the features my clock radio offers, never mind the mind boggling number of features modern smart phones provide.

Re:Inertia

[RoLi]

Pretty much the same for me.

I tried to use Postgrest 2 years ago, but I could not get it to compile into a custom directory and decided that it's not worth the effort.

In the meantime I have written so many wrappers geared to MySQL that I will use it for many years to come. But I will certainly give Postgrest a try sometime in the future.

Re:Inertia

[TheRaven64]

For most use cases I've found where MySQL is more appropriate than PostgreSQL, SQLite is better than either. For things where you actually want a real RDBMS, PostgreSQL usually wins.

Re:Inertia

[Alien among you]

Not trying to "convert" you,
Genuine Question: why did you need to compile postgresql to a custom directory?

Cool story bro.

[buckfeta2014]

We don't care that you're different. Must be a soft news day at Slashdot... Oh wait. It's always a slow news day at Slashdot.

Re:Cool story bro.

[Anonymous Coward]

You mean two weeks ago there was a slow news day. Slashdot is normally a week or two behind everyone else.

Re:Cool story bro.

[Lunix Nutcase]

Yeah but they still got you to click the inane Dice.com clickbait.

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

[Anonymous Coward]

The good thing about SQLite is that it's very easy to backup. I've actually found it to be faster for some things than MySQL.

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.

Re:SQLite3

[puzzled_decoy]

You can enable "only_full_group_by" mode.

Re:SQLite3

[Anonymous Coward]

You can enable "only_full_group_by" mode.

This mode is the default in 5.7.5 and higher.

Re:SQLite3

[Anonymous Coward]

Agreed.

SQLite is a fantastic "custom file format" system. You can define a structure using basic, familiar SQL syntax, then use basic, familiar data access libraries to run basic, familiar SQL commands against a file on an accessible filesystem. It's lightweight, ad-hoc, and fantastic. That said, it's truly terrible for anything you already have a real server set up for.

I'm currently working on using SQLite for a Windows Embedded program, replacing old-school strongly-typed ADO datasets stored as XML. I expect SQLite to be approximately eleventy hojillion times better.

Re:SQLite3

[Bacon Bits]

That's true, although it's historically not been enabled by default, although AC was kind enough to say it is now.

One of the biggest problems I find people have coming from a MySQL background is not understanding why aggregate queries they're used to working suddenly emit errors like, "Column 'LAST_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

The next big problem I see people having is people violating First Normal Form and then complaining that their queries perform really poorly or are hugely complicated, but that's not exactly MySQL's fault.

Re:SQLite3

[Anonymous Coward]

I've seen cases where the SQLite DB just corrupted itself without any hardware issue or power cycling.

It's easy and quick to use when you want to try out something without having to deploy a full fledged SQL server with configuration and permissions, but beyond that I wouldn't dare use it on anything serious.

Re:SQLite3

[Anonymous Coward]

SQLite3 is a fantastic product, but it's primarily intended as an embedded SQL database, not an RDBMS.

Idiot. An embedded SQL database is an RDBMS. All that an RDBMS is is a relational database management system. What you should have said is that SQLite3 is not intended to be an RDBMS of the type where you connect to it remotely via sockets.

Re:SQLite3

[Anonymous Coward]

Of course first normal form violations are thick within the MySQL community.

I remember back when I first discovered that (pre-innodb) foreign key relationships were parsed but there was no code that implemented them. I sent out a message to the dev list, and the project manager at the time wrote back that they were unnecessary, impacted performance, and you could just as easily implement them in code. I wrote back that they were part of what one minimally would expect in a relational database (or else the relations can't be maintained); that you can't just as easily implement them in code because he hadn't implemented (this was advertised) transactions; my code could die / network disconnect between the table updates, and that I didn't want to litter my code with the database's responsibility of maintaining the database "data" model.

The next reply was a rant about my lazy coding, including strong wording and a bit of profanity, which was hilarious in retrospect as I probably could have cut-and-pasted it in the reply as my own words and although I don't like to talk to people in that manner, it would have mostly applied.

That's when I left MySQL, and never turned back. It's the database of people who need to fill out their web forms, but don't use it like a database. To Oracle's credit, after they bought it, the ejected the worst in the design, and managed to drop a decent database engine (innodb) which now only means that MySQL has on of the least standard SQL syntaxes out there. Apparently it's at a cost of performance, but that's just fine by me. My human data integrity repair skills are far slower and more expensive than a few extra clock cycles.

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.

Re:Postgres hands down

[Anonymous Coward]

Sounds like PostgreSQL is a great general purpose operating system. Does it also read email?

Seriously, why is your database re-sizing images for you?

This is what you get when DBAs start writing web applications.

Re:Postgres hands down

[HornWumpus]

Being able to call out of SQL into a real language is a big deal.

Your only apparent counter is 'don't do that'.

Note that all the big database vendors provide similar functionality.

Re:Postgres hands down

[Anonymous Coward]

MySql it great people that need to back a website with a database and that is fine for a whole lot of people. If you need anything remotely complicated, then Postgresql is the way to go. Basically, MySql is entry level and Postgresql for advanced stuff.

Re:Postgres hands down

[HornWumpus]

I was going to correct you. Then I kept reading and realized which troll this is.

You're 'not even wrong.' You clearly don't understand what your speaking of.

Re:Postgres hands down

[Anonymous Coward]

Same AC as grandparent here.

Being an application developer means that I have a different perspective than, for example, a DBA. So I generally think that if the db is calling *out* to some other language, there's an inversion of responsibility, there. Instead, the application should be calling *into* the database, not the other way around.

As for "apparent counters," what's the justification for re-sizing an image from within the database? (Really, I'd like to know.)

Re:Postgres hands down

[HornWumpus]

Brittle thinking. Also note: The DBA doesn't have to write the code the server invokes.

Using the supplied example. Resizing a graphic on row update makes all kinds of sense if you have a dozen datapaths coming into the database.

Granting in a well architected application you could do it in the data layer. Not everybody is building complete apps. Some work enhancing applications they don't have source to. It's ugly, but real world.

Re:Postgres hands down

[Anonymous Coward]

> MySQLisms that they don't run on ANSI-compliant engines.

Wrong. MySQL is ANSI. You just set a flag in my.cnf, and it is. It's just isn't set by default.

Interesting. So, why don't new installs have that flag set? It seems like MySQL should be moving in the direction of standards compliance if they want to be taken seriously. I understand that most RDBMSs grew up inventing the rules as they went along (all the big guys did, this too) and MySQL is guilty of the same, but now that the standards exist, they should migrate if for no reason other than being more useful to a wider audience.

At least it has that setting unlike other SQL servers that intentionally do not support standard SQL. Other databases are absolute crap and require a bunch of nonstandard crap just to work.

+1

Every RDBMS I've worked with requires *something* non-standard to get it to work for you. You just have to pick your poison and stick with it unless you have a compelling reason to switch.

Microsoft's garbage attempt at an SQL server that they bought from Sybase doesn't even allow you to put a column in the results of most queries. It lies and claims the SQL standards only allow you to put aggregate functions or the columns in the GROUP BY in the results if you use a GROUP BY. That is a lie.

You've lost me.

MySQL doesn't lie like that. You people need stop your lies about MySQL. It is more compatible. Also, the summary spouts a bunch of MySQL lies. MySQL is ACID. Anyone that lies and says it isn't is a liar. It is. It is a lie to say it isn't.

Careful, you are about to run off the rails.

TFA says that InnoDB is ACID-compliant, but says that MyISAM (the *old* default storage engine) is not. While MySQL's own documentation does not say "MyISAM is not ADIC-compliant", it does say that the InnoDB storage engine is *fully* ACID-compliant, insinuating that MyISAM is not. They describe ACID as being closely tied to transactions which of course aren't supported at all in MyISAM tables.

So, MySQL can be considered non-ACID-compliant by a reasonable person. There's no need for a tirade.

You are acting like a damn Republican talking about their racist views when you say it isn't. You are as ignorant as a KKK member. That is what you sound like. We might as well call you Cletus since your kind is so stupid. Fuck you. I hope you die of stomach cancer. I hear it is painful. And just before you die, I hope your best friend stabs you in the back while proving to you that MySQL is ACID, because it is. No amount of stupid lies can change that.

Jesus... calm the fuck down. It's just a fucking database.

CAPTCHA: inferior

Re:Postgres hands down

[afidel]

As someone who's used many, many DBMS engines MySQL is by far my least favorite. Even engines that are completely different from ANSI SQL like Intersystems Cache make more sense to me than the half standards compliant, half brokenness that is MySQL.

Re:Postgres hands down

[Anonymous Coward]

Different AC - I can't vouch for image resizing, but caling out to a language to do things like checking that a value is a valid member of an arbitrary domain is pretty handy (eg. checksums on credit cards or UPC/EAN barcodes).

Re:Postgres hands down

[Anonymous Coward]

He said MySQL is ACID. It is. How is he wrong? I guess you're just one of those irrational bashers that has to lie in order to attack MySQL. He is right to be angry at irrational people like you.

Re:Postgres hands down

[Bacon Bits]

Instead, the application should be calling *into* the database, not the other way around.

Which is great... until you want two different applications to use the same database at the same time and need to occasionally do the same things the same way. When your data is more complex than what Amazon or Google use and closer to what a hospital information system or school information system use, you can no longer rely on a single application from a single vendor using a single database. Shit ain't that simple anymore.

Re:Postgres hands down

[Anonymous Coward]

I wouldn't call it "brittle thinking". Instead, I'd call it proper software design and architecture.

Your "support an existing application" argument is a little shaky here since presupposing the existence of some advanced, orthogonal feature in the database (e.g. re-sizing an image which really isn't related to data storage -- the db's primary purpose) kind of means that it could be used for that purpose. If you had a database that didn't support that kind of thing (e.g. Oracle) then "supporting an existing application" wouldn't carry you into the thought of doing things like using such advanced features.

Re-reading the above hardly makes any sense. I'm trying to say that if you have a system of poorly-separated concerns, you will continue to have a system of poorly-separated concerns unless you actively do something about it. So, you wouldn't have been tempted to rely on an advanced feature if it didn't exist (obviously) and you may not have been tempted to hack it together to work that way if the feature didn't exist but could have been hacked to exist (e.g. calling-out to ImageMagick). The application or system may be so big that it's got so much inertia and you simply can't change it without a huge load of effort. I get that.

But, to call MySQL useless because it wouldn't support this kind of crazy use-case is kind of begging the question. If MySQL *had* been chosen, one would not have used these (unavailable) advanced features in the first place; another solution would have been found. And if the decision has been made (for PostgreSQL or whatever) and you're in the too-big-to-redesign phase, well, then MySQL simply can't meet your needs.

MySQL can fit the billing for a wide range of use-cases. Simple because it doesn't meet *some* use-case doesn't make it a load of crap (which is what's being said here, not necessarily by you personally).

Re:Postgres hands down

[Anonymous Coward]

> MySQLisms that they don't run on ANSI-compliant engines.

Wrong. MySQL is ANSI. You just set a flag in my.cnf, and it is.

Are you saying that setting an option in my.cnf will cause applications written for MySQL to magically start working on other DBMSes?

Re:Postgres hands down

[Anonymous Coward]

So your solution, instead of using a proper layer of abstraction (like a service), is to throw everything into the database? Yikes.

Re:Postgres hands down

[Anonymous Coward]

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.

Re:Postgres hands down

[bad-badtz-maru]

You nailed it. The AC's idea that all data access is performed via a single application is naive. There are cases where the only common interface point between applications is the database.

Re:Postgres hands down

[jedidiah]

It gets better than that. There are behaviors for which there are no ANSI standards. So it doesn't matter how much you want to whine that your pet brand is 'more standard". There are enough low level holes in ANSI to ensure that even with the simplest use cases you are still working around vendor specific syntax.

That's just the way it is.

So whining that one engine is "less standard" than any of the other ones is ignorant blather.

Re:Postgres hands down

[HornWumpus]

No. It's brittle thinking. There is no principle of 'proper software design and architecture' that says: 'Don't put complex things behind database triggers.' If fact, if the event that triggers a complex thing _is_ a database update it's one of the best choices. Especially if there are many data paths that would have to updated.

OLAP cubes are often, more or less, complicated analytics that update on triggers.

I didn't realize you were defending MySQL. I thought we were talking about features of good databases. If you have got this far into the discussion and still don't see any reasons not to use MySQL, I just don't know what to say. Good luck.

Resizing a graphic wasn't my example. But there are real world cases where putting the code in the only possible common place is a good thing. Granting not having a pigfuck to deal with would be better. But that leads to rules like: 'Don't build pigfucks' which implies 'Don't use MySQL'.

Re:Postgres hands down

[Anonymous Coward]

You really need to calm down. I'm the same AC who has been replying to this thread without losing my mind. Are you just trying to get Google to associate the word "MySQL" with "lie" or something? Miserable Failure.

Anyhow, I'm a big MySQL fan and have used it in several large, non-trivial, projects which have been in production for years. But I can be reasonable about it. Even MySQL's own documentation says that MyISAM is not ACID-compliant. No problem: just use InnoDB like I and the rest of the world have been doing for quite a while.

This has nothing to do with Oracle shilling, spreading lies, or defaming a project that you seem to be defending, but are failing to do so because all you are doing is calling everyone a liar.

Re:Postgres hands down

[MSG]

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

Exactly. That is the primary reason I not only choose not to use MySQL, but actively advocate other SQL engines to other developers. Even where MySQL supports a standard syntax, their documentation tends to encourage their proprietary alternative syntax, making ports that much harder.

Re:Postgres hands down

[genkernel]

You, my friend, have never used Microsoft Access

Re:Postgres hands down

[Anonymous Coward]

It's possible to build a pigfuck with or without MySQL. I don't believe that merely using MySQL makes a product a pigfuck, though.

Honestly, I see reasons to use other RDBMSs for various things. I also see reasons to use MySQL in some places. It's not just for idiots who a) don't know any better and b) build future-pigfucks.

Re:Postgres hands down

[HornWumpus]

MySQL is ether ACID or fast, depending on how it's setup. 99% of MySQL apps require it be setup in non-strict, non-acid mode.

Also note the second para and which regular retarded troll we are dealing with.

He also clearly is just spouting words he doesn't understand. What does an aggregate function return without a 'GroupBy'? It's undefined, but he wants it to work.

Re:Postgres hands down

[HornWumpus]

It will stop it from working with MySQL.

Re:Postgres hands down

[HornWumpus]

Access hasn't been a database engine in over a decade. It's now a frontend for SQL server.

Re:Postgres hands down

[Anonymous Coward]

Not all of us right toy software like you do. Yes, ugly compromises have to be made IN THE REAL WORLD.

Re:Postgres hands down

[Lunix Nutcase]

Wrong. MySQL is ANSI. You just set a flag in my.cnf, and it is.

And most people don't set it. So trying to claim it is ANSI because of an optional flag that next to no one uses doesn't really cut it.

Re:Postgres hands down

[Anonymous Coward]

What's next? Shoving an HTTP interface in there because using a 3rd party web server is hard? Hardware device drivers in the database? DBOS?

Re:Postgres hands down

[Anonymous Coward]

queries in Watcom-SQL

I'll get off your lawn right now sir.

Re: Postgres hands down

[Anonymous Coward]

PostGIS uses it to generate map images from datapoints. Can't think of much reason for resizing an image in the DB unless it was generated there and you didn't want to save it out else to resize it.

Re:Postgres hands down

[Anonymous Coward]

I love Postgres. I guess I just love stored procedures and a database that is an integrated package of both data and logic. PG has never failed me. what a marvelous piece of software. I am constantly amazed at what it does for me and am so thankful for all those who contribute to its ongoing success. I dont begrudge MySQL. I'm sure its wonderful, but it does not seem that much more than SQLite on steroids ( and I also love SQLite).

Re:Postgres hands down

[Anonymous Coward]

Even where MySQL supports a standard syntax, their documentation tends to encourage their proprietary alternative syntax, making ports that much harder.

Long-time MySQL docs writer here.

We document what the software supports. We show clearly what's standard and what's not. We don't make choices for users regarding which they should employ.

Re:Postgres hands down

[Anonymous Coward]

If you hide access by using a service (when there are multiple applications using the same data), you sidestep having to do synchronized redeployments when the DB structure has been updated. The other applications (besides the main one) don't even notice any change.

Re:Postgres hands down

[Tablizer]

Merge Emacs and PostgreSql, and you have the Mother of All Swiss Army Tools, that scales even.

Re:Postgres hands down

[aralin]

Probably because the images are data and databases do data manipulation and normalization in correct design. We usually offload CPU heavy data transformations or impossible to do data transformations into application layer, but that is a break in the design forced on you by constraints. If I said that database converts all integers into INT4 on insert, or all strings into sanitized strings or all XML into JSON format, you would not say a word. Converting images is the same thing.

Re:Postgres hands down

[HornWumpus]

MySQL puts you firmly on the road to pigfuckdom. You have to code around many idiosyncrasies, which is a _terrible_ place to start. Soon the database engine selection is 'set in code' and you are stuck.

Re:Postgres hands down

[Anonymous Coward]

> There is no principle of 'proper software design and architecture' that says: 'Don't put complex things behind database triggers.'

Around here, we call it separation of concerns.

Re:Postgres hands down

[HornWumpus]

You didn't even read the link!?

It does provide an argument for calling out of your SQL dialect into an analytic object of some type.

Re:Microsoft SQL server is the way to go

[Anonymous Coward]

Show us on the doll where "open sores" touched your micro-soft and gave you this raging case of bs.

Why I don't care.

[Anonymous Coward]

I just need a database to hold crap, I don't care about it being 1 nanoseconds faster.

Re:Why I don't care.

[Anonymous Coward]

I just need a database to hold crap

If you use MySQL, that's exactly what you'll get.

Re:Why I don't care.

[nullchar]

Then use NoSQL instead since you don't care about the structure of your data or how you might query that data to extract value.

Re:Why I don't care.

[jellomizer]

1 nanosecond adds up when you have millions/billions of records.

Re:Why I don't care.

[jedidiah]

Does anyone seriously think that mere millions is remotely impressive anymore?

Billions isn't even all that impressive. Some of us were dealing with databases like that 15 years ago.

That tragic "stuff that slows you down" is also the stuff that saves your ass when things inevitably go wrong. Ditch that stuff and you are just gambling with your future.

Re:Why I don't care.

[Anonymous Coward]

1 nanosecond adds up when you have millions/billions of records.

millions/billions = 1/1000

I thought we were over the whole SQL thing

[i_ate_god]

Isn't everyone all NoSQL nowadays or has that faded away?

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.

Re:I thought we were over the whole SQL thing

[HornWumpus]

They still use SQL, but only for things smaller then 'web scale'.

Am I joking?

Re:I thought we were over the whole SQL thing

[nullchar]

They still are using NoSQL... until they want to report and extract value from their data, then they'll migrate to an RDBMS.

PostgreSQL is actually a pretty good NoSQL database that you can also use as a kick-ass relational database.

Re:I thought we were over the whole SQL thing

[new_01]

NoSQL has its use cases. But the fact that NoSQL lacked mature ACID compliance meant that many of the large companies who relied on certifying data integrity (think bank transactions and other large companies for which standard relational databases already provided them with safe transactions out of the box) couldn't switch over to it. "NewSQL" is the latest buzzword that is going around. They're finding that heavily modifying the database to fit into the distributed world is better than dropping SQL and ACID altogether. Maybe it's better to maintain integrity in the database itself rather than forcing developers to do it in the application layer. At least that's what I got out of reading through Google's paper on F1.

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.

Re:I thought we were over the whole SQL thing

[ArcadeMan]

And everything in "data" is CSV.

Re: I thought we were over the whole SQL thing

[Anonymous Coward]

Now you need 14 different JavaScript frameworks to access it.

Re:I thought we were over the whole SQL thing

[rickb928]

So it's a cluster, um,whatever.

Re:I thought we were over the whole SQL thing

[angel'o'sphere]

You likely are joking :)

Because the term 'web scale' has no meaning at all ...

Re:I thought we were over the whole SQL thing

[angel'o'sphere]

The point is not to switch over to NoSQL, but to use it where it has its benefits.

90% of the data in our days does not need 'integrity constrains' or even transactions.

RDBS are abused for everything because developers are to stupid to realize when a simple file is sufficient. Or their managers ... or what ever.

Everytime I see someone 'logging' intoma database I like to carry him into the base,ent and chain him onto a wall.

Re:I thought we were over the whole SQL thing

[rycamor]

I wouldn't even recommend bothering with hstore. There are several even better ways to use Postgres in a "NoSQL" setting.

For example there is the Mongres project, that lets a PostgreSQL database emulate the MongoDB protocol. So you could literally drop Postgres into a Mongo-powered application with not a single hiccup, and get a) better performance and b) all the back-end relational stuff you need when it comes time to do reporting or other business logic.

There's also the new JSONB datatype in PostgreSQL 9.4, which I would recommend over hstore if you want to just store "free-form" data in records.

EnterpriseDB did a very well-thought-out study on PostgreSQL/NoSQL.

Re: I thought we were over the whole SQL thing

[AshtangiMan]

so palmOS then?

Re:I thought we were over the whole SQL thing

[Anonymous Coward]

90% of the data in our days does not need 'integrity constrains' or even transactions.

This indicates to me that you do trivial work, at best.

Re:I thought we were over the whole SQL thing

[plopez]

We all know MySQL is not web scale
https://www.youtube.com/watch?...

Re:I thought we were over the whole SQL thing

[shutdown -p now]

90% of the work is trivial. But there's a lot of it, so it still takes up most of the time.

Re:I thought we were over the whole SQL thing

[tigersha]

CSV is for p*ssies. Postgres can store BLOBs directly, right? All you need is a table with an integer id and a blob.

Re:I thought we were over the whole SQL thing

[Tablizer]

That should be NodeJSONXML. Keep up!

Re:I thought we were over the whole SQL thing

[Tablizer]

Congratulations, you invented the File System!

Re:I thought we were over the whole SQL thing

[nullchar]

I didn't know about Mongres, thanks for sharing.

This is the EnterpriseDB blog post comparing performance of Pg and Mongo.

And yeah, hstore is old-school now that JSONB exists.

Re:Microsoft SQL server is the way to go

[hyperar]

Don't even bother, you can pretty much identify him on every post lately

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:Some old quotes...

[phantomfive]

MySQL is not necessarily faster. Check out this graph of response times. I posted this mainly to illustrate the difficulty of getting good benchmarks, more than anything.

What about the tools?

[Anonymous Coward]

pgAdmin is terrible

Re:What about the tools?

[jellomizer]

I love Postgresql... However I agree pgAdmin, isn't that great. I would love an alternative.

Re:What about the tools?

[plopez]

then write one

Re:What about the tools?

[buddyglass]

I will say that Postgres's command-line tool "psql" is about a million times better than Oracle's "sqlplus". It's been a while since I used MySQL's command-line tool, but I remember not liking it as much as psql.

Re:What about the tools?

[greg1104]

The pgAdmin developers agree, and have already started on a full rewrite in Python. The goal of having a GUI app that's cross-platform used to take tools like C++ and wxWidgets, and that's what the current pgAdmin III is written in. The new one doesn't need to be so low-level in its library use.

Re:What about the tools?

[Alien among you]

I would if I had a team. Now what features do you want?

Django *rules* as long as it's chained.

Someone chose PostgreSQL for their use case

[Anonymous Coward]

This is news, but the only thing to discuss is other use cases (problems already solved) or no use cases in which we all go into armchair mode and have endless debates already hashed out a million times over.

Re:Someone chose PostgreSQL for their use case

[colinrichardday]

no use cases in which we all go into armchair mode and have endless debates already hashed out a million times over.

Yes, this is slashdot.

Doesn't PostgreSQL have transactional metadata?

[K. S. Kyosuke]

I'm not quite sure about that but I though that not even MySQL's InnoDB allows you to alter your DB schema "under full throttle", whereas databases like PostgreSQL or Firebird shouldn't really have problems with that - transactions up to n see the old schema, transactions from n+1 onwards see the new one, and the RDBMS insulates you from all the nasty bookkeeping. I'm mentioning this as a post scriptum to the "Table Changes Without Locking" feature which is really 1980s stuff. (True, for Firebird, even table structure altering without locking is 1980s stuff, but that would be a low blow for MySQL. ;-))

Re:Doesn't PostgreSQL have transactional metadata?

[bad-badtz-maru]

Yes, DDL application is transactional. The company I work for went from Oracle to PG last year. The ease in application of DDL changes was a huge benefit that we didn't originally anticipate.

Re:Doesn't PostgreSQL have transactional metadata?

[Anonymous Coward]

If you're doing DDL in the application, you don't know how to design a DB.

Re:Doesn't PostgreSQL have transactional metadata?

[bad-badtz-maru]

Can you read? I said "the ease in application of DDL changes", not that the application was applying DDL. In other words, when our DBA applied changes under Oracle, packages would be invalidated and all users had to exit the application. This issue doesn't exist under PG. You're an idiot, BTW.

Postgres has referential integrity

[Scott.Simpson]

The MOST important reason for using Postgres is that it has object ids (OIDs). This allows true referential integrity. You can have a row point at another row and this reference stays the same REGARDLESS of whether you change the primary key of the referenced row. This allows true object orientation.

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.

Re:Postgres has referential integrity

[thogard]

The OID concept does fix a common problem. Take a typical CRM database where you have customer account and a ship to address. At some point, the ship to address for a customer gets updated to their new office yet someone wants to check where an old order was shipped to and the programmer didn't think of it so now reprinting the old invoices show the new address. It is amazing how many times I've seen that type of problem cause massive issues in data integrity.

Re:Postgres has referential integrity

[rycamor]

How do OIDs solve this? Updating a record it still updating a record. OIDs don't magically make that problem go away.

One solves this problem the way one solves any other data problem: logical thinking and planning ahead. If you are creating a long-running business application where things like addresses may change, you design your database to take that into account. You store a timestamped address with every order record, or you store multiple addresses by date range. It's not exactly rocket science.

There is literally no reason to use OIDs except as a crutch when one has created a table without a primary or candidate key--and even then OIDs won't save you from bad logic, such as duplicate records or other idiocy.

BTW, it is important to also remember that OIDs are not enabled by default for new table creation. Many times the PostgreSQL core team has discussed whether to deprecate OIDs completely. The decision was made to keep them for two reasons: a) some applications still depend on them, however misguided their reasons and b) Some PostgreSQL add-ons and external solutions (replication, etc...) use them.

Re:Postgres has referential integrity

[jeremyp]

That's just poor design on the application designers part. It should be obvious that, even if the customer hasn't moved, shipping addresses aren't necessarily the same as the office address.

Re:Postgres has referential integrity

[Grishnakh]

This doesn't seem like a hard problem; you should just design your DB schema so that every order has a "shipped-to" address field, where the order was shipped to. The order information shouldn't change, so it obviously shouldn't be pointing to information which can also change. Yes, this duplicates data somewhat, but mailing addresses do not consume a lot of space.

Besides, just because a customer has a preferred ship-to address doesn't mean they'll always want every order shipped there. What if the customer is large and has multiple ship-to addresses? What if they want something shipped someplace odd for some odd reason?

Re:Postgres has referential integrity

[greg1104]

Postgres stopped using OIDs for regular tables in version 8.1, many years ago. You can force the old behavior with default_with_oids, but no one does that anymore. OIDs haven't been needed for referential integrity in quite a while. Only the system tables still use them to connect tables on a typical server, which is mainly because those need to be read during bootstrapping before all the SQL features are available.

Always MySQL vs Postgres

[Anonymous Coward]

What about Firebird? It's a fully featured RDBMS with a fair bit of performance.

I dare say it beats MySQL on almost every front. Mostly popular on Windows with the Delphi crowd.

Journalistic Integrity

[Anonymous Coward]

Are these stories *ever* going to mention that Dice is /.'s parent company?

Re:Journalistic Integrity

[netsavior]

They probably inserted the story in MySQL at the same time they inserted the reference to the parent company. There was a race condition and the reference to Dice was dropped. Shame they didn't use Postgresql, the objectIds representing the slashdot/dice relationship would never change and it would be never be lost in the name of speed.

Re:Journalistic Integrity

[xxxJonBoyxxx]

It's already on the bottom of every web page.

>> Slashdot is a Dice Holdings, Inc. service.

Anyone who's been here more than two weeks already knows that most of "Nerval's Lobster" submissions come in the form of "compare similar terms X and Y often found on resumes"

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:In my experience

[Anonymous Coward]

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...

Re:In my experience

[Anonymous Coward]

MyISAM is not ACID compliant and doesn't support foreign key constraints.

Re:In my experience

[chuckinator]

Makes sense to me. PostgreSQL is essentially the open source successor to Ingres (even down to being forked from the original Ingres codebase). In fact, PostgreSQL's name even means Post-ingres.

Re:In my experience

[rickb928]

Ditto. PostgreSQL ha been sold for real money for a long long time. Developers who made a living off their applications used it and made a living. many of them.

You dismiss it out of hand to your disadvantage.

Re:In my experience

[Anonymous Coward]

And neither are CSV files, but what in the hell do either of those facts have to do with this discussion? MySQL is ACID out of the box with no configuration changes and has been since at least 2010 when I reviewed a book on it for Addison Wesley. I know it supported ACID for many years before even that, but not as a default.

Look, if your favorite database can't compete without using incorrect arguments, then maybe you need to reexamine your position.

Re:In my experience

[Anonymous Coward]

Talk about the pot and the kettle...

At any rate, even with InnoDB as the default engine, MySQL still lacks proper support for nested transactions, or any truly usable workaround (such as the savepoints Postgres uses). You'll get some pretty hairy behavior in MySQL if you try to rollback a nested transaction. The docs are pretty clear about that.

Re:In my experience

[Anonymous Coward]

I'm confused.

I love postgres exactly because it walks among the giants and doesn't look like a misfit... but you can't really use it without changing configuration files. It won't even accept remote connections unless you do, let alon handle a database that has even a modest volume of writes (shared buffers anyone?).

Postgres is great, but "easy to setup" is not a reason.

Re:In my experience

[Anonymous Coward]

Just Like Linux, MySQL has always been a nerdy fanboy SQL platform.
Windows and MSSQL have always been a frustratingly useless noncompliant silo of their own.
And FreeBSD and PostgreSQL has always been serious correctness platform of choice.
Whether good or bad, those things just aren't chainging anytime soon because it's part of their cultures.

Re:In my experience

[plopez]

you can speed up any DB by turning off logging, relaxing constrains, and not locking. Which is essentially what many DB engines did to meet benchmarks e.g. MySQL and "NoSQL" DB engines. Until the loss of data integrity caused real problems and people started screaming about how they had just been screwed by the DB engine they were using. Then they got religion and are, over time, beginning to resemble PG and a real relational model.

Re:In my experience

[Anonymous Coward]

Back in the early days, I got the "yeah but you can embed the code to update both tables, so you don't need foreign keys (to work)".

Re:In my experience

[Anonymous Coward]

PG is still considerably slower than MySQL

Complete nonsense. Redo your research.

Re:In my experience

[Alien among you]

There have been a few times I used postgresql to upgrade an access '97 based front-end (Bonds Plus, and an id-card database I might get in trouble for mentioning) to support a higher number of records. No, MS Jet 3.0 didn't solve the problems.

True Army story:
I had to explain, (succinctly without cuss-words) to a General why the --redacted-- database was losing information. I don't know if the id card system was made by a solder or a contractor; it was built on Access.
The ID cards were blue, red, yellow, brown, etc. Since I was a combat style Staff Sergeant, the hardest part was not cussing. And before you say it: No, the upgrade to MSSQL server did NOT work because of corruption; 3 minutes after running it would fail and rollback to ZERO records. More details available if you ask.

me: Sir, think of your database as a tube, like a soda straw. Each badge is an M & M. You have blue, red, etc m*ms. Eventually the tube gets filled. When you push a new m&m in one side of the tube, another falls out the other end. You don't care about *that* m&m because you don't know what color it is or who it belongs to. Then one day the Deputy Minister of Misinformation for the NonExistant DMV comes in to get his badge. So we search thru the backups, find his m&m and insert it into the tube. He's happy and we're awesome! -Except another m&m gets pushed out of the tube. That's why I recommend Postgresql [showed slide with elephant logo]. It can handle billions of records...
General: "RIGHT!!! because an elephant has a huge fuckin ass, and we can shove as many m&m as we want into it! Do it."

This was in 2009, MNFI badge office. You just can't make this shit up.

MySQL is ACID

[Anonymous Coward]

The summary is a lie. If you don't like MySQL then just don't use it. Don't bash it by making-up lies.

One reason

[frisket]

We have a lot of XML publishing workflows. MySQL provides a -X commandline option which returns the results of a query in XML. I don't know if PostgreSQL, MSSQL, or Oracle have the equivalent (perhaps someone who knows can post). Right now, it's a pain-free way to get what we need in the form we want, with zero additional effort. If it exists in other rDBMSs, that makes our choices wider.

Re:One reason

[jedidiah]

Really? This is your show stopper. Any coder on here should be able to knock something like this out in their free time.

There's probably already something along these lines on Sourceforge. Might even be platform neutral. That's fairly easy with something like Perl or Java.

Re:One reason

[MightyMartian]

Why shouldn't it be a show stopper. If XML is important, why shouldn't someone use a product that already delivers the goods, as opposed to one where the defense is "Well, just write something, or go look on Sourceforge and see if you get lucky!"

Re:One reason

[Anonymous Coward]

I Googled "sql server xml" and this was the second result. One caveat from someone who has ripped it out of several lazily-coded systems that used it: It's slow as shit. Only use this if you don't care how long a process takes.

Just as an example, a process using this took 45 minutes to run for 6000 rows of data. To be fair, there was a heavy amount of processing going on behind all of that. But removing the XML formatting from the database and returning a simple datatable back to the .NET program that called it improved processing time to about 45 seconds. It still had to do that same "heavy processing", it just didn't have to format it as XML. It only took another 45 seconds for .NET to generate and spit out those 6000 rows as XML files, giving us a 96% reduction in run time.

Rule of thumb: Databases are good at searching, filtering, and returning resultsets of strongly-typed data. Forcing it to do formatting and presentation will just bog it down doing a job that it's not designed to do.

Re:One reason

[phantomfive]

Why shouldn't it be a show stopper.

Because your choice of database shouldn't be predicated on something that will take an afternoon of coding to fix.

Re:One reason

[Anonymous Coward]

Like resizing images at the application layer instead of using some inappropriate database call to do it?

Re:One reason

[Zontar The Mindless]

I think it can get pretty interesting around here when people actually read the discussions.

Re:One reason

[Anonymous Coward]

> I don't know if PostgreSQL

Does this help?

Re:One reason

[aplcomp]

PostgreSQL has a long while supported Native XML extension, vs. MySQL/MariaDB limited xml shredding.

JetProfiler is why you should use MySQL

[mveloso]

Chances are, you don't know anything about databases. JetProfiler will show you the crappy queries you're using in an easy-to-understand way so you can fix your stuff and make everything faster.

AFAIK, no such tool exists for Postgresql.

As a bonus you don't have to deal with the annoying psql/pgsql crap, which for some reason drives me bonkers. I mean come on, make it psql or pgsql, not both. WTF?

Re:JetProfiler is why you should use MySQL

[plopez]

see http://www.postgresql.org/docs...

Just about every modern DB engine has something like it. If you are not even aware of it you should really learn more of your tool set.

Re:JetProfiler is why you should use MySQL

[greg1104]

VividCortex is a commercial tool similar to JetProfiler. The company is run by guys who are also MySQL experts, so I expect them to assimilate all the interesting JetProfiler features into there eventually. And the core Postgres profiling keeps getting better each release too.

Post is irrelevant

[Anonymous Coward]

For anything that matters you use Oracle, for everything else you can use whatever the hell you want

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:Bottom line

[jabuzz]

You could add back in the day when the web was taking off Postgres did not do SQL. If you wanted SQL your choice was mSQL which has all sorts of licensing issues, or MySQL which was GPL but had issues with ACID and transactions. It was not until sometime later that QUEL was replaced with SQL.

It was that gap, when Postgres didn't do SQL that allowed MySQL to take off.

Re:Bottom line

[putaro]

That gap was pretty small. I remember doing postgresql back in '97. I actually wrote an early JDBC driver for Postgres around that time, but it was for an internal project and we didn't release it as open source.

Re:Bottom line

[greg1104]

The SQL gap is not what let MySQL take off. PostgreSQL didn't get a good Windows port until version 8.0 in 2005. That delay is what let MySQL get such a major installed base ahead of PostgreSQL being usable for the typical desktop app.

"Better Licensing"?

[93 Escort Wagon]

Come on - who really cares about MIT versus GPL licensing in this context? And by "who" I mean "people who manage and use databases as part of their paid job"?

And why does anyone care what someone writing for Dice says on this topic? I read the article, and it doesn't sound like the author has even used any of the features he's decided favor postgres.

What's next - a Dice article on emacs vs. vi?

We all agree Postgres is better

[Anonymous Coward]

So we all agree that Postgres is better, ok? Now let me get back to my MySQL database which has all those features poorly implemented but is easy to use and does exactly what I want.

Re:We all agree Postgres is better

[Anonymous Coward]

Now let me get back to my MySQL database which has all those features poorly implemented

This part is true.

but is easy to use

People who say this generally think that "easy to use" means "easy to make it do something".

and does exactly what I want.

If what you want matches what MySQL does, then I don't want you anywhere near any project that I'm involved with.

Re:We all agree Postgres is better

[Anonymous Coward]

I don't think anybody here wants to be anywhere near you or any project you're involved with, either.

Re:We all agree Postgres is better

[putaro]

It's funny, but I find Postgres much easier and quicker to setup than MySQL. Probably just familiarity. I will say that in 20 years of using Postgres I've never had it eat any data. MySQL, well, I'd had it destroy its data a couple of time.

maybe, but... probably not.

[fyngyrz]

I've seen cases where the SQLite DB just corrupted itself without any hardware issue or power cycling.

I haven't -- I've seen cases where the filesystem corrupted itself (with or without the help of hardware failure, no idea), but I've used SQLite most extensively (seriously, I bet I'm close to having used every feature in the thing) in its various revisions for years, as have tens of thousands of my users consequent to my incorporation of same, and I've heard of, and experienced, exactly zero events of DB failure (and one of my most popular apps would have crapped itself and gone blind if such happened, so I'd surely have heard about it.)

Also, different app (SdrDx), but I use it the other way around here; I've got an SQLite DB on my website that tracks startups of the code as to version, revision, step, beta status, platform (windows, OSX), time, date and IP, as part of the handshake that lets the users know if there are any upgrades (in-app title bar notification, nothing invasive.) This lets me keep track, somewhat, how many people are using what version of the app. SdrDx is well over 15 thousand regular users, many more that aren't regular, and the DB is moderately busy as a result, again, no problems. The writes to this particular DB are pretty straightforward, but the queries I've written for it span a decent range of futzing about. No problems to date. Of course it's backed up, but no need for a backup as yet.

I'm also presently using it, again extensively, in an incomplete, but complete enough for my day to day use, DSLR application of mine, code compiled in, along the lines of Lightroom and Aperture, and again, zero DB failures of any kind. Oodles of DB activity for every image, every library op, every bit of auto-categorizing, library backup, tagging, annotating, posted-to recording, flagging, versioning, plug-in and plug-in settings recording, and so on.

So I'm going to go with... possible, but even slightly likely. Also, any DB can take a crap if the drive underneath it goes bad, or the OS (or some all too clever use of root privs) allows corruption of its storage. None of which should be construed as any kind of criticism of any DB engine subject to the same.

Also, did you report these purported corruption events to the author of SQLite? Sure hope you did. :/

postgreSQL + PLV8 + JSONB + NodeJS

[Anonymous Coward]

With the last iteration adding JsonB build a MVC with PLV8 is a no brainer, Fast and robust development!

postgreSQL + PLV8 + JSONB + NodeJS

krb5 auth?

[Anonymous Coward]

Gimme something with krb5 auth.

mqsql has ideal use cases

[rubycodez]

If you don't care about your data, and don't mind it getting corrupted every few years, put it in mysql or a fork of it. I've seen it time and again over the last 15 years clobber data at various employers. Developers use that hobbyist grade toy because they don't know any better, it's what they played with on their pc so they use it at work.

But did it melt?

[Anonymous Coward]

You chose Itanic? Really? And your dedicated 1MWatt power plant is located where?

And stupid cunts like you

[Anonymous Coward]

Can't tell the difference between a programming framework and a web blog. How "Interesting".

*shrug* As long as it has stored procs

[msobkow]

As long as a database engine has stored procedures and a decent client binding library, I can make it go. I've worked with MySQL, SAP/Sybase ASE, DB/2 LUW, PostgreSQL, Oracle, and SQL Server extensively over the years. There comes a point where you just know enough about the quircks and foibles of each of the databases to get around their particular issues and "just make it go."

People who bitch about the minor syntactic differences between the vendors clearly haven't really ported an application, because the differences in behaviour go far beyond syntactic sugar. Despite the ANSI standards, you can't just install the schema on a competitor's database and expect it to run an application properly without a lot of rework and restructuring.

Sure your basic table structures may remain compatible, but that's about it.

Every vendor has at least a few features that encourage "lock-in" by being incompatible with all their competitor's products.

I do have a rule about which databases I work with, though: if it doesn't have stored procedures, I won't use it. The performance benefits of complex stored procedures vs. logic in the client is just too dramatic to ignore and gloss over. Not to mention the fact that coding the logic in the client application is extremely verbose compared to any stored procedure syntax I've ever encountered.

Re:*shrug* As long as it has stored procs

[Anonymous Coward]

syntactic sugar

I want to punch you in the face for using that phrase.

Great run down of "Why Postgres" here

[Anonymous Coward]

http://www.brightball.com/postgresql/why-should-you-learn-postgresql

Its not MySQL users starting the pissing contest

[Anonymous Coward]

Its not MySQL users starting the pissing contest. Its *ALWAYS* some annoying guy saying "Teh Postgress iz da bomb and you are all rong for using MySQL". Every. Time. And they try and try, and I still don't care. I'm using Mariadb instead, and I'm completely happy. When I need assistance, I pay them. Its a good-enough deal for me. Thanks again for your time. Buh bye.

Nerval's Lobster Advertising Service

[edibobb]

Nervals Lobster's last 15 posts have been links to news.dice.com, the parent company of Slashdot. They're nothing but ads.
[Add obligatory Slashdot's-going-to-hell-in-a-handbasket slam here.]

Stop trolling

[Anonymous Coward]

Left a test Oracle server running overnight accidentally a number of years ago it had been owned by time I got in the next day

Absolute rubbish! Tthe person you responded to provided the fact that MSSQL is the _ONLY_ (read that twice) DB product to ever have a known exploit all on it's own. Not that someone "forgot" to configure some security, but that it's ownable just by running regardless of how you secure the server. What you are describing is your fuckup, not Oracle's fuckup. Yeah, we all make mistakes but most of us don't try to badmouth innocent parties when describing ours.

Re:Stop trolling

[jeremyp]

They didn't provide a fact, they expressed an opinion. It's an irrelevant opinion given that other database servers also have had vulnerabilities.

MySQL's Query Planner Still Sucks

[Anonymous Coward]

Take a query that joins against 10 or more tables. Yes, it happens in the real world. I have seen at least 16, not counting subqueries. Run it in MySQL and it is slow. Don't do that, some may say. But this is what SQL is for, to relate tables. Breaking it apart because your database server sucks just means that you picked the wrong database. Run the same complex query in PostgreSQL or any commercial database, and it runs just fine. MySQL has an undeserved reputation for being fast. It is only fast when you do not make the same basic safety guarantees that PostgreSQL or any commercial databases offer by default.

Plus, MySQL has fugly syntax. If you know Oracle and Microsoft SQL Server, then one of those two syntax's that you already know will work with PostgreSQL. MySQL is a database made by web developers who do not understand SQL or data safety or correctness. Want to store February 31st? MySQL will take it. Any other database says: "WTF is this?". It is poor for developing and testing code because it accepts dumb things like that.

Re:MySQL's Query Planner Still Sucks

[elp]

And if you had read the manual and turned on the option to make it do those checks ( sql_mode='TRADITIONAL' ) It would have objected.

Blaming mysql because you can't RTFM makes you look like a beginner. If you had actually used a large range of databases professionally you would know that apart from CRUD operations every single database has its own quirks and there is no meaningful standard. Seriously, go read something like the SQL cookbook the variations are all over the place. I've also seen both PG and Mysql do some seriously dumb things with joins.

One of the things real engineers do is understand the tradeoffs between different options and choose the one that suits the particular application and environment at hand. Postgresql's unstoppable OCD has always made it a good choice for accounting type systems while mysql has always been good for blog style sites where the data is very dirty. PG 9.3 and 9.4 have finally got reasonable replication but prior to that it wasn't always the best choice if you needed replication.

That article was just click bait with large parts either wrong or irrelevant.

Re:MySQL's Query Planner Still Sucks

[Anonymous Coward]

Sorry, but this is no simple RTFM issue. No tool should require a RTFM to turn off February 31st as a valid option. MySQL is in the wrong here, and Oracle knows it. They are fixing it now, and the latest release breaks old code that allowed this buggy behavior. So prepare yourself for a bunch of broken blogs because Oracle is cleaning this up after being in the hands of sloppy web developers for so long. But it will be a long time before they fix everything.

Yes, some databases have quirks, but MySQL has major fuckups. Do not even try to pass them off as equivalent to different behaviors in other databases. Being OCD about protecting data is your RTFM problem. PostgreSQL lets you disable fsync and many other data protection functionality if you want extra performance without protecting data data and possibly other data. And you still did not address the issue of the query planner sucking.

I choose SQLite

[TapeCutter]

AFAIK, the most popular RDBMS by installed base is still SQLite which the authors released into the public domain many years ago. It won't keep up with oracle's performance on very large data sets but it's a hell of lot less complex to set up, and as you say most business/consumer applications simply don't need the performance (and price tag) of something like Oracle or MSSQL.

The other databases are weird

[Art3x]

Microsoft SQL:
- select top 100 * from table instead of select * from table limit 100
- White space after values is ignored ('Bob' = 'Bob ')
- Command-line client sucks

Oracle
- A column of type date is actually timestamp. There is no column type that stores just a date.
- Command-line client sucks
- expensive

MySQL
- You can quote strings with single ticks, double quotes, or backticks
- The MyISAM engine
- Query cache based on the text of the select statement, rather than its meaning. So slightly rewording your query will skip the cache. Also updating a single row will clear the cache. This is inferior to how I understand PostgreSQL's shared buffer cache, which keeps frequently read rows in cache, only flushing out the ones that are updated, and deciding whether to use the cache after the query is parsed, and so not dependent on the query being literally written the same way.

It's no wonder so few web developers fully exploit the powers of the database, reimplementing many of its features in PHP, poorly. I once went to a local PHP meeting. The leader gave a talk, mainly about object-oriented programming, which I never got into. Anyway, he also recommended some kind of job queue application, like to email new users a welcome message. Don't use your database for that, he said, because keeping track of who you've emailed in the users table would upset MySQL's delicate query cache. At the end of the talk, I asked the group of 20 or 30 who had used PostgreSQL. Nobody.

Like others have said, most web developers probably should use SQLite. It's great not only as an embedded database but also the backend for most of the little web apps out there. Or if you're writing business applications for a large company, use PostgreSQL. The rest can go to the dumpster.

200 comments

[Hognoxious]

200 comments and nobody has asked whether it's webscale or not. This place is going to the dogs already.

Drupal 7 on Postgres 9.3.4

[KayakFun]

I use Drupal 7 on Postgres 9.3.4 and it works like a charm, never crashed in 1.5 year.

I did have to convince the hosting provider to upgrade Postgres to a higher version, because we were the only ones not on MySQL, but after pointing out that they declared on their own site to being technically advanced etc they did this.

Re:Drupal 7 on Postgres 9.3.4

[Anonymous Coward]

You should also upgrade to using variable-width fonts on slashdot.

Re:Drupal 7 on Postgres 9.3.4

[KayakFun]

Changed my text setting from 'code' to normal, thanks.

Re:Drupal 7 on Postgres 9.3.4

[Anonymous Coward]

Now upgrade from PHP to anything else.

Re:Drupal 7 on Postgres 9.3.4

[KayakFun]

I have been coding in Perl for the last 15 years, I cannot upgrade any higher.

PHP is just the foundation that Drupal sits on, there is hardly any reason to use it if you use the Drupal modules to the fullest.

simple opinion

[Tom]

I've used MySQL for almost 20 years for different projects of mine. In my professional life, I've also used ADABAS, Oracle and this and that other.

I was interested in Postgres some years ago but never went beyond reading one book. Then two years ago I decided to start a new project with Postgres from the start, because I wanted PostGIS.

I'm not looking back. Every future project I do will always use Postgres. Aside from the technical and functional and other rational arguments, the feeling you get is like graduating from BASIC to a real programming language.

Re:simple opinion

[danaris]

I've used MySQL for almost 20 years for different projects of mine. In my professional life, I've also used ADABAS, Oracle and this and that other.

I was interested in Postgres some years ago but never went beyond reading one book. Then two years ago I decided to start a new project with Postgres from the start, because I wanted PostGIS.

I'm not looking back. Every future project I do will always use Postgres. Aside from the technical and functional and other rational arguments, the feeling you get is like graduating from BASIC to a real programming language.

Can you comment a little about some of the specifics of what makes it feel that way, for those of us who haven't had the opportunity to use it much, but are interested?

Dan Aris

Re:simple opinion

[Tom]

Firstly, the general feeling that Postgres is engineered and designed and not cobbled together.

Secondly, support for non-trivial SQL is just a lot better. For a forum or simple application, MySQL is fine by language, but if you get into the more tricky SQL, it will fail you much sooner.

Thirdly, schemas, views, stored procedures the whole environment around the tables is so much more refined and powerful. Not that it's easy to say "MySQL cannot do this" - there's usually some hack or roundabout way in which it can do it, but in Postgres you don't need the hacks.

And it seems to me that it's so much clearer and better to do serials and foreign keys and all that. In MySQL it always felt to me like everything that's not trivial was added on, by someone else than the last feature. Postgres is just much more consistent in its approach.

Oh yes, and it does GIS. And blobs (properly). And UTF (properly). I just feel a lot more comfortable throwing everything at it and not thinking "will it handle it?" all the time.

Dumb

[Anonymous Coward]

So what? They both work well. Use the tool that works best for you. This is a dumb debate.

Nothing new here...

[Anonymous Coward]

I've always favored postgresql for those reasons and it never really was that much slower than mysql but others always following the herd and drooling/slobbering mysql which I never felt was a battle that was worth the cost and lived with what was a halfassed db on many projects. Backing up mysql was always "fun" back in the day... especially when it would silently phailwhale, which was often...

Process management, PG versus Mysql

[bad-badtz-maru]

One thing very relevant for discussion, that I didn't see mentioned here, is how connections are handled across the different RDBMS. PG still uses one process per connection. This can make its memory utilization grow substantially under high connection counts. Oracle and Mysql don't suffer from this issue.

Re:I choose MS SQL Server [Backups]

[Tablizer]

The biggest problem I find with the Express edition is backups. You cannot use a regular file-based backup system to backup such databases due to locks, data pointers, etc. The paid edition has scheduled backup dumps built in.

There are tricks, such as scripts to deactivate the DB, copy the files, and re-activate it, but that's too risky and clunky.

Does anybody know a better way to get backups from E?

Re:I choose MS SQL Server [Backups]

[Richard_at_work]

Yes, PowerShell scripts to trigger a copy-only backup - no issues with locking etc, no need to take the db off line or anything of that ilk.

Stored Routines

[jswalter9]

Stored Procedures are big for me, and MySQL is my absolute favorite. It's also really simple to create MySQL UDFs (user defined functions). I wrote and currently maintain MVProc (on SourceForge), which uses every feature MySQL offers in stored procs. I have wanted since the beginning to implement MVProc for other databases, especially PostgreSQL, but it's so feature poor I can't make it work without creating an entirely different product. Just my 2 cents.

Re:I choose MS SQL Server [Backups]

[Tablizer]

I've seen multiple sources that say NOT to backup while the database is on-line. Maybe one can get lucky and nothing bad happens regarding pointer references or locks most of the time after hours, but is that good enough? If you want to gamble that much, use MS-Access; it's nimbler to set up and change.

Databases are like assholes...

[v3xt0r]

...so essentially, the author of this article sat back and tried to comprehend why anyone else would use a database that he would never use, put himself in their position, fap, and blog about it, only to conclude that he still loves the one he uses commonly, to begin with. I think the influx of reddit migrants has sent slashdot back to n00b flamebait central.

who? people who got stung by ExtJS

[oneiros27]

When ExtJS changed their license to GPL3, not LGPL, as you would expect for a library.

The owner of Sencha then put out a statement that if you built something that made use of ExtJS, then you had to release your software under GPL3 ... including the server components.

I have no problem with releasing the client side -- that's all javascript that people could view the source and see ... but releasing the server side? That requires security audits and a review by legal ... it's just not going to happen.

Reading the review, the reviewer seemed to have the same take on what GPL meant from the statement :

With MySQL, on the other hand, the client library is GPL, so you must pay a commercial fee to Oracle or supply the source code of your application. (Thatâ(TM)s less of an issue when using MySQL in websites; MariaDB uses the GPL 2 license but also has a less restrictive LGPL license for MySQL Client libraries.)

Now, if the issue is simply the *client* code, then you could get around it by using ODBC, or something like Perl's DBD::mysqlPP, which doesn't use the MySQL client code. Do you have to release the whole application if it's just something that makes use of a mysql database? I don't know, but with all other things being equal, and more and more people coming to this conclusion, I'd rather just stick with something that's LGPL or MIT.

Re: Oracle

[presidenteloco]

If you have a team of unemployed DBA plodders that you need to provide work for, and an employer that needs to get rid of piles of money fast as a tax dodge, use Oracle, ...

Re:I choose MS SQL Server [Backups]

[Richard_at_work]

Copy-only is fine for online databases, you will never have an issue with locks etc as its specifically designed to cope with them.

Also the action of copy-only is done by the database engine, so it knows what it has to deal with.

Incase you are misunderstanding, copy-only is an option you provide to SQL Server when copying the database via SQL commands and the database engine, it has nothing to do with dealing with the actual data files the live database is using.

I would never advocate backing up the actual mdf and ldf files, always use the systems the database engine provides because that knows better than you how to handle various situations and will flush stuff to disk as needed.

Mariadb has Dynamic Column + Cassandra integration

[Anonymous Coward]

The author conveniently left this out. Anyone who actually works with large datasets, especially time series data knows how huge this is. Cassandra clusters scale linearly. I say mariadb and cassandra is a match made in heaven. You can mix and match acid and nosql as you please. Json document stores are for front end web devs and script kiddies. there are many features missing is postrges as well. many because the developers at postgres simply don't listen to their users . no Multi-source Replication, hints etc. etc.

test

[Anonymous Coward]

test

Your SA hates you... if he's any good

[Dimes]

From a developers standpoint either are good... Pros and cons for each.

BUT

Postgres does not do Master Master streaming replication, which means hitless upgrades, VIP fail over and fail backs at ease just are not possible.

Re:Microsoft SQL server is the way to go

[tigersha]

Uhuh

We tried both, and MySQL was superior to PG

[wad4ever]

A few months back, we had completed initial development on a new persistence layer on a demanding application. We'd put it all into PostgreSQL, and were enjoying the easy JSON and other features. It worked great.

So we got it up and running on high-end hardware in our five data centers, then we turned on the pipes for all the writes. But our systems team members were going insane, trying to get High Availability working right. It turns out that there is just no good way to accomplish this in PostgreSQL. It could fail over to the slave if the master stopped responding, but fail-back was basically impossible. It had to do an rsync on the file system level, which was expected to fail. When it failed, the docs said, just do it again. It took almost a full day to run, each time.

And it failed with alarming regularity! When under load, every couple of days the database would just freeze for ten to fifteen minutes, choking on some non-scary query. It would just sit there, stuck. Calls to it would just block, and eventually timeout. When this happened, it would fail over to the slave, and we're days away from getting back to a sane state.

Don't think we didn't do our best to solve this issue. We spent many thousands of dollars on two different highly recommended consulting companies, who specialized in PostgreSQL. They came onsite and looked at everything, and recommended a number of configuration adjustments, but nothing helped.

In desperation, the project now seriously behind schedule, we worked over Christmas, and branched the code to use MySQL as the database, instead of PostgreSQL. Then we set up two parallel systems. Both on identical high-end hardware ($50,000 machines), one for each database, and turned on all the pipes.

The result? MySQL answered its queries in 50% less time than PostgreSQL. Plus we already knew that it did HA quite well, and it never just froze up like PostgreSQL would.

We have since completely obliterated all traces of PostgreSQL from our code base.

Re:I choose MS SQL Server [Backups]

[Tablizer]

The Express edition does not have those features. It's why it's free.

Re:I choose MS SQL Server [Backups]

[Richard_at_work]

That's funny, I use copy-only backups all the time on SQL Server Express.

Stop talking out of your arse.

Re:I choose MS SQL Server [Backups]

[Tablizer]

Is your rudeness necessary? I helps nothing.

Well, it hasn't worked for us, and I've found others complaining about it on the Internet also. There are work-arounds, but they are hokey and assume certain permission access levels.

Note that I am talking about automatic periodic backups, not one-time manual runs.

Re:I choose MS SQL Server [Backups]

[Tablizer]

Is your rudeness necessary? It solves nothing.

It hasn't worked for us, and others have reported similar on forums. There are round-about work-arounds, but they have a lot of layers and dependencies. Not worth risk to save a buck.

Note I am talking about automatic periodic backups, not one-time manual backups.

(My apologies if this message shows up twice. The first didn't appear for some reason after submitting. I can't rule out Mondayitus.)