PostgreSQL vs. MS SQL (opens in new tab)

I scrolled to a random page. It was totally absurd, stopped right there.

"Crucially, because open-source software tends to be written by people who care deeply about its quality (often because they have a direct personal stake in ensuring that the software works as well as possible), it is often of the very highest standard (PostgreSQL, Linux, MySQL, XBMC, Hadoop, Android, VLC, Neo4JS, Redis, 7Zip, FreeBSD, golang, PHP, Python, R, Nginx, Apache, node.js, Chrome, Firefox...). On the other hand, commercial software is often designed by committee, written in cube farms and developed without proper guidance or inspiration (Microsoft BOB, RealPlayer, Internet Explorer 6, iOS Maps, Lotus Notes, Windows ME, Windows Vista, QuickTime, SharePoint..."

As a DBA, that has years of experience with both. I disagree with you.

I stopped reading after the very first bullet, and just skimmed through most of the titles.

First, you can import/export csv's. Hell all you need to do to export, is right click the view and hit export, then select .csv.

Second, you don't want such a simple installation, MSSQL has several options/packages/configurations. If you don't need it you turn it off, otherwise you will be charged a fee.

That being said, if you have chocolatey installed in powershell. It's also a one line command, and you can configure your own custom installation command.

choco install MsSqlServer2012Express

Third, I don't feel like correcting the rest of your mistakes.

I'm not shitting on Postgres, it's pretty awesome, but out of the box MSSQL is better. Especially when you have hundreds of severs running it.

This article is a fantastic tour of some of Postgres' programmer-friendly features.

If you're interested in databases but aren't familiar with Postgres, it's a good read if you skip the criticisms of MSSQL. Some of those criticisms are spot-on, some are iffy, and some are rah-rah-yay-yay-open-source exhortations that seem to be lifted from Slashdot threads fifteen years ago.

(For whatever it's worth, I love MSSQL. Been using it for well over a decade. That said, I certainly don't mind reading criticisms of it.)

There is so much here, it is so verbose...so religious.

There is the appearance of facts, but mostly it is bias by omission. Take for example the CSV complaints..."MS SQL Server can neither import nor export CSV. Most people don't believe me when I tell them this". This is just false, patently and obviously false. BCP and SSIS, both part of SQL Server have excellent support for importing and exporting flat files. I did keyword searches through the whole post, no instances of BCP, SSIS or Integration Services (the long term for SSIS).

I am sure others will break this thing apart section for section but I wish people would be fair and open about stuff like this - I mean, seriously if you are going to put the time and effort into a long document like this, don't be so blatantly bias.

The author is pretty misinformed about how enterprises work.

a) Security is critical. It routinely trumps common sense and evidence. And telling people "not to fall for it" is advice that will get you nowhere.

b) Vendor support is critical. And no your local mom+pop consulting shop doesn't count. PostgreSQL could really do with solid, global companies like Microsoft, Datastax, Mongo etc who offer training and proper SLAs.

Three things I really enjoy about MSSQL are:

1) SQL Server Data Tools for complex schema management.

2) Graphical execution plans.

3) It's the database best supported by Entity Framework. If you know what IEnumerable is, and you know what IQueryable is, and you know what a leaky abstraction is, you can use EF to quickly start getting data in and out of SQL Server with very reasonable performance.

I develop against MSSQL most of the time and have tinkered with Postgres a bit was agreeing with most of the points, until I got to:

> "but MS SQL Server does have a bizarre failure mode which I have witnessed more than once: its transaction logs become enormous and prevent the database from working. In theory the logs can be truncated or deleted but the documentation is full of dire warnings against such action."

This belies what appears to be a fundamental ignorance of SQL Server and is not at all bizarre. If a database has been deployed in a non-simple recovery model, then the transaction log needs to be truncated as part of a backup procedure. If this isn't the case then you should have hired a/better DBA.

> PostgreSQL: the docs actively encourage you to simply use the TEXT type. This is a high-performance, UTF-8 validated text storage type which has no length limit.

The emphasised part is incorrect, Postgres's text storage has a 1GB limit (that stands for CHAR, VARCHAR and TEXT which all use the same underlying mechanism): http://www.postgresql.org/docs/9.4/static/datatype-character...

> In any case, the longest possible character string that can be stored is about 1 GB.

Although note that this is 1GB, you have to take in account both multibyte characters and compression (which by default is enabled on text values of more than 2k, using LZ)

I too am biased towards PG. At one of my previous gigs, I had to develop a HTML5 mobile app around a MS SQL database (though eventually we migrated to PostgreSQL). Among the problems we ran into:

A) Indexes disappeared without rhyme or reason. (This might have been a "too many cooks in the kitchen" thing, where someone restored from a backup and didn't tell anyone.)

B) In the default configuration provided by the php5-mssql package in Ubuntu, text fields were chopped at 255 characters.

C) Our Windows Server did updates, then came back up with a "you need to renew your license" lock that prevented any I/O and cost us half a day of productivity (on top of the licensing costs).

And of course, D) it screwed up the file encoding when we did the final export during the "migrate to PostgreSQL" step.

Incidentally, migrating our platform to PostgreSQL and kicking the tires was one of the last projects I completed at that job, and everyone was much happier for it.

The author mentions no understanding N'V' etc, early on, then rants about MS's support for Unicode...

Also, IMHO one of the single biggest shortcomings from PostgreSQL is that there's still no baked in solution for replication with any kind of either multi-master or hot failover. If PostgreSQL supported this (in the box) as well as MongoDB was, along with having PL/v8 support easier to install, it'd be my db of choice.

MS-SQL does have a lot of shortcomings compared to PostgreSQL from a developer perspective, but from being able to easily install and administrate one over the other, I think MS-SQL has a significant lead here.

As long as replication/sharding are bolt-on solutions for PostgreSQL, it really isn't an option for a lot of projects.

Alright so somebody with experience in a wide variety of DBs help me out here. Are the enterprise solutions really any more scalable than something like postgresql or is it more that they're more accommodating an enterprise mentality where you'll tend to run into more skill level diversity on the extremes and team A often doesn't necessarily have team B's expertise at stuff? By accommodating of course I mean willingness to take a buttload of money to help teams out with problems when needed and/or to blame when their mistakes explode in their faces and to never ever try to tell anybody that they're doing it wrong once VIPs with enough hit dice are invested in that sort of thing.

I do gather that a lot of DB admins that aren't very political about it do in fact respect MS SQL for some of its more competitive features. I'm just wondering if there's any reason to start with it for a low-cost startup scenario that could ultimately result in a non-trivial but fairly straightforward DB schema with potential for being used by very large institutions (universities at the largest I'd imagine).

I'm ignorant enough (primary experience is in web UI) that I'm leaning towards postgresql because I like the way the Django guys think and they seem to dig it. Also the no-nonsense license and yes, the not-profit-motivated thing is nice when backed by a strong core group which I gather postgres has.

But do the enterprise DB solutions handle severely massive amounts of scale better for some reason? Or is it more that they're culture-friendly to the sorts of companies that typically handle DBs of this nature?

For all its faults, this article does mention one thing about PostgreSQL that I have always been happy with: ease of installation.

Having been through many, many installs of Oracle and MS-SQL in my career, the speed at which you can get a PostgreSQL server up and running with data is simply awesome.

Hm. I love postgres and find many of its features to be intriguing and exciting. I work with MS SQL Server professionally and find many of the things it does to be excellent as well. The unfortunate thing about this piece is that much of it is actually Op-Ed and is based on the author's preference (e.g. cascading drop). The second section is nearly unreadable as it's full of FUD and smear.

Yes, there are things that PG does better than MSSQL. Yes, there are things that MSSQL does better than PG. It sucks that we still have to debate it based on a smear piece from someone with an agenda.

MSSQL has some problems, some of which the post talks about. However, I tried pg out, planned to use it instead of MSSQL.

1. HA, via replication, clustering, etc. are all fantastic as well as easy and trivial to setup. Once PG can offer a simple little wizard and setup replication, or shared-nothing clustering with automatic fail over, awesome. Or even tx log shipping with a few clicks.

Instead, last I tried (9.0 I think), pg drops you off with some weird system that makes you run shell as a specific user, where you run generic sounding commands. Oh and some default config that seemingly has some bad defaults you probably should change.

2. Overall, MSSQL makes it easy to run a DB. I was doing a billion transactions a day (each which wrote to a few tables and also included a real ACID balance update), and I didn't need a full time DBA or have to have particularly awesome experience beforehand. With pg, I sorta got somewhere, but I had little confidence

3. Development is far superior on MSSQL. Supporting other languages is a null point, because they aren't running as part of the query execution engine. That is, there's no real difference in using Python inside PG versus an external client, as far as I could tell. You still had to submit queries and make a transition. So TSQL seemed far nicer to work with than plpgsql. Although, the record types in pg were much nicer I'll admit. Another annoyance: pg didn't offer multiple returnsets in many situations. This made it awkward to run a sub function that needed to return results from different tables as separate queries. And the perf tools and UI was just so, so much better.

4. There'd be strange "little" features you'd just expect to be there, like materialized views, which pg simply does not have. Pg still lacks materialized views. The current implementation is essentially pointless, as it doesn't update the view automatically.

I'm very much for PG, and believe it's an important project and am trying to use it for future development. Much because Microsoft went back on its word that it wouldn't move to an Oracle-style licensing where you pay for CPU power instead of just sockets. They've also made questionable decisions with Enterprise vs Standard, putting extra cumbersome limitations. They aren't adding features like JSON or arrays quickly. And also, I think it's important to run open source and try to make sure free systems stay viable.

But MSSQL has a lot going for it, and the ease of use and built in HA options don't even seem like goals for PG. If licensing weren't in the way, and I just wanted an easy system that was capable but didn't require lots of time, MSSQL makes a strong choice.

Microsoft has a thing for very long names though – possibly its greatest achievement ever is

    Microsoft® WinFX™ Software Development Kit for Microsoft® Pre-Release Windows Operating System Code-Named "Longhorn", Beta 1 Web Setup

Tangentially, can anyone recommend a good book for learning PL/PGSQL and other ways to procedurally program on psql?

Is there something like this for MYSQL? pg-versus-my.com?

What about the lack of upsert in PostgreSQL?

Pg is SOLID but I'm sure mssql have its merits.