
PostgreSQL vs. MS SQL - cribwi
http://www.pg-versus-ms.com
======
general_failure
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..."

~~~
keithwarren
I know numerous guys on the SQL Server team and it actually causes me a bit of
anger when I read a clueless statement like that, for the author I feel sorry
for his ignorance; the anger comes from the fact that lots of clueless people
will actually read this and believe it to be authoritarian because of the
verbosity of it alone.

~~~
rbanffy
The SQL Server group (and their product) is one of the very few things in
Microsoft that has earned my deepest respect. Not Windows, not Exchange and,
certainly, not ShamePoint.

I frequently rank Microsoft's product line as follows:

1 - Natural keyboard - the one there is no reasonable replacement for (the
kinesis keyboards are 5 times as expensive). If there is a reason my none of
my machines is 100% Microsoft free, this is it.

2 - their mice - simple, precise, comfortable, inexpensive

3 - SQL Server - it almost makes having a Windows server around worth the
pain.

Having said that, I have enormous respect for PostgreSQL. It's a very solid
RDBMS and it's my database of choice most of the time. It is more comfortable
to use from a command line than SQL Server will ever be (which is OK, because
a CLI is not a high priority over there).

~~~
Implicated
I just can't replace the keyboard.

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

~~~
Retra
The author spends quite some time explaining that MS SQL Server's CSV is
mangled and broken, and what you've said is that it exports CSV. So what does
your comment contribute, except to say that you didn't read the article?

~~~
keithwarren
QFA "MS SQL Server can neither import nor export CSV"

~~~
appleflaxen
I took it to mean "can neither import nor export conformant CSV"

~~~
socceroos
Yeah, me too. The author points this out painstakingly too. I'm not familiar
with MS SQL CSV exporting so I can't verify the truth of that statement
though.

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

~~~
socceroos
This is exactly the feeling I got from the article. While the author did state
that he wasn't as familiar with MSSQL as with PostgreSQL, he didn't do very
well in enumerating on the facts surrounding his criticisms of MSSQL. I think
this greatly hurt his good points.

But yeah, an awesome resource for people dipping their toes in the Postgres
pool.

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

~~~
UnoriginalGuy
They likely should have said "standards compliant CSV." I've worked a lot with
CSV (too much!) and let me tell you Microsoft's tools are simply awful at
following the CSV standard.

Excel in particular drives me up the will. Plus the automatic inferences in
Excel break all kind of things. Have a six digit number? Randomly a date. Have
a UPC? Going to corrupt it in numerous ways (leading zero stripping,
converting to scientific notation, etc).

I can definitely sympathize with anyone who criticises Microsoft's CSV
support. They really just want you to use an Excel format (either the binary
format or the zip-XML one). However Excel formats are overly complicated for
the type of data migration work CSV is often utilised for (XML is too large,
it adds up, and requires custom code to import/export (even if that is XSL
from CSV), JSON might be better but isn't "yet" popular).

~~~
mason55
_> let me tell you Microsoft's tools are simply awful at following the CSV
standard._

There's no such thing. CSV is a convention that people mostly agree on but
there's no written CSV standard and no CSV standards body.

~~~
loqi
RFC 4180 is close enough for me.

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

~~~
jordigh
What vendor support do you get exactly from Microsoft? Their EULA typically
has a SHOUTY CAPS NO WARRANTY CLAUSE, so what does the SLA cover? Have you
successfully resorted to the SLA to handle problems you've had?

I'm honestly curious. I just don't know how SLAs work, and whom can you blame
if you have problems with MS SQL.

~~~
Spearchucker
Vendor support from Microsoft is about requesting help with issues. You get x
number of support calls, depending on what you paid. Microsoft has fixed
service levels for that, and you've little option other than paying more money
if you want a quicker turn-around.

SQL Server itself doesn't ship with an SLA.

You'd get that from your service provider or systems integrator, because the
customer chooses her own service levels (determined by business continuity
objectives). Her SP or SI (or in-house IT department) would then deploy SQL
Server in a way intended to meet the required service level. Specifically, the
customer might opt for five nines (99.999%) availability. Such a deployment
doesn't look anything like one that must only be available from 8am to 5pm on
weekdays - even though both SLAs are met using the same SQL Server code base
from Microsoft (but on vastly different hardware and network configurations).

The availability associated with an SLA usually goes hand-in-hand with
disaster recovery (recovery time objective, and recovery point objective[1]),
but can also apply to support turn-around (a support request is triaged, and
based on severity is resolved within the amount of time specified for that
severity by the SLA). As mentioned, if I'm not mistaken Microsoft has fixed
service levels for that. IIRC an MSDN subscription gets you a small number of
free requests. An Enterprise Agreement gets you a whole bunch more.

There are many other quality objectives you can specify with an SLA, including
efficiency (capacity), integrity (security), and robustness (stability) [2].

So unless you're hosted by Azure or have an Enterprise Agreement, Microsoft is
rather unlikely to provide you with an SLA. And even then _you 'd_ be the one
telling _them_ what your service level requirement is. If Microsoft can't
deliver on your service level requirements, you'd do it yourself, or get a
systems integrator to do it for you.

[1] [http://www.druva.com/blog/understanding-rpo-and-
rto/](http://www.druva.com/blog/understanding-rpo-and-rto/)

[2]
[https://www.wittenburg.co.uk/Entry.aspx?id=d8c54975-bd0a-410...](https://www.wittenburg.co.uk/Entry.aspx?id=d8c54975-bd0a-410b-b266-d73c1f45c641)

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

~~~
roller
Just on your #2: I'm not entirely sure how the graphical executions plans help
in the end. I've seen too many plans that wouldn't fit on 30" display. The
postgresql explain text format is reasonable, and can even be automatically
logged when queries are slow [1].

[http://www.postgresql.org/docs/9.3/interactive/auto-
explain....](http://www.postgresql.org/docs/9.3/interactive/auto-explain.html)

~~~
taspeotis
If an execution plan doesn't fit on your 30" monitor then you've probably got
a more sophisticated query that requires more analysis than simply eyeballing
execution plans for clustered index scans or places where predicate pushing
hasn't worked as effectively as you hoped.

External tools aren't really in the scope of this discussion, but while SSMS
does a pretty good job of visualizing execution plans SQL Sentry Plan Explorer
does a better job (a more compact view with a color gradient for the most
expensive operators) and makes my job even easier.

[http://www.sqlsentry.com/products/plan-explorer/sql-
server-q...](http://www.sqlsentry.com/products/plan-explorer/sql-server-query-
view)

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

------
masklinn
> 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...](http://www.postgresql.org/docs/9.4/static/datatype-
character.html)

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

~~~
DrJokepu
That being said, if your relational database has 1GB records, you really need
to reevaluate whether you are using the right tool for the job.

~~~
UnoriginalGuy
While I kind of agree, what exactly is the "right tool" for storing large
blobs?

You can use the filesystem but then you have to manage data migration/sync by
"hand" which is kind of a bitch for distributed systems.

Honestly storing large blobs with meta-data telling you about the blobs is
extremely common. I'm yet to hear of how you're meant to do it correctly.

~~~
x0x0
commonly, s3 or zfs nas for storage and pg for metadata

~~~
threeseed
Commonly ? No not even close. The most common solution is that people just
dump it as a BLOB in whatever database they are currently using. Which based
on numbers is likely to be MySQL.

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

~~~
AaronFriel
I don't think your experiences with SQL Server are representative. I'm not a
fanboy, I've run into more than my fair share of issues with MS SQL, but none
of your problems are a result of any SQL Server version I've heard of. Your
response screams FUD, to me.

Now, you say that you possibly had too many cooks in the kitchen. So perhaps
your complaints are due to the other cooks, or maybe some of your complaints
came to you second-hand. But here is my analysis of your complaints, having
run SQL Server for a number of years:

A) Just doesn't happen. Index-related statements are transactional and ACID-
compliant. (i.e.: you can, in a transaction, add, drop, and update triggers,
indexes, entire tables, etc. along with data.) This has been the case since at
least I've started using SQL Server with the 2005 release.

B) I don't know why this is the case with php5-mssql, but I would blame the
authors of the package. varchar, nvarchar, text, etc fields all support > 255
characters.

C) I don't know of any version of SQL Server or Windows Server that does this.
No Windows Server version "locks IO" on a license check failure (this would
obviously cause huge problems with filesystem consistency). As far as I know,
no SQL Server version does live license checking or phones home. I have no
idea what your symptom was, but I suspect someone else broke something. A
running SQL Server installation does not "stop", nor does it need the license
to be renewed at any point. Cumulative and service pack updates don't do
license checks either, as far as I know. In fact, an expired license doesn't
affect anything, and probably even reflects a misunderstanding of how
licensing works. If you purchased a license, even if you paid yearly for 2-3
years, your SQL Server license is good forever. Forever! New versions require
software assurance, but you have to choose to install those manually, it's
opt-in, not opt-out. Automatic updates should never break SQL Server.

D) Is it at all possible this was the migrate to PostgreSQL tool's fault?
Because I've run weekly backups and restores of SQL Server databases for the
past several versions for many years, and had no problem with a .bak file
being corrupt.

~~~
sarciszewski
D) Nope. I wrote the tool and tested it multiple times. The data kept coming
out malformed, and playing with mb_convert_encoding() didn't really help much.

I appreciate the feedback though. This was my only time ever using MS SQL.

(Re: the 255 char thing, I had to edit the /etc/tsql/tsql.conf to change the
sybase version to make the problem go away. Not a MSSQL problem, but
definitely a problem with using it from a PHP + nginx on Linux env.)

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

~~~
sitharus
Fortunately Postgres is getting a lot more replication love. You can already
do a lot with the warm-standby and FDWs to do basic sharding, and they're
rolling in a lot more to 9.4 and 9.5. EnterpriseDB are the place to look for
that to be made in to a full product.

Someday soon Postgres will have materialised views that auto update :)

However, as someone who works with MSSQL daily they're still not comparable.
I'd say Postgres is much nicer in development and I'd use it for my projects,
but MSSQL has much more scalability without developer intervention.

------
Pherdnut
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?

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

~~~
tracker1
Now setup replication and a solution for hot/fast failover to a new master...

Not having an in the box solution for this is the single biggest thing keeping
me off of PostgreSQL...

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

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

~~~
tdubhro1
Interesting that you mention HA/replication, reading the article I was swept
along with the pg evangelism but I fully expected at least a grudging
concession to mssql on replication.

We use both pg and mssql, I was surprised the article didn't mention
replication. While one might be justified in describing pg as more "developer
friendly", mssql is more "enterprise friendly". As a developer, I prefer pg,
but as a stack designer, I still lean more toward mssql. Put another way, if
I'm joining a project as a developer, I hope they're using pg, but if I'm
consulting and designing a stack for a company I'll probably recommend mssql,
almost definitely if they're doing something that requires replication, and/or
the team isn't going to have lots of db expertise.

I guess this is quite self-contradictory on one level but I think it's a
fairly common view (having checked with some colleagues).

I know that pg has support for replication, but it certainly isn't as easy to
set up as mssql, and you need to do a LOT of reading to figure out what flavor
you should use, and it's not entirely obvious what features you get with each,
or which ones are really recommended and which are semi-deprecated.

CTO: Do you support replication? mssql: yes, and it's pretty easy to set up.
pg: well, we've got 3 or 4 ways of doing that, depending on what you want
exactly, method A isn't really maintained any more, and method D looks like
it's going to be great in a release or two, so maybe that's the one to bet on,
but in the meantime you have to put up with a bit of pain.

(dramatisation, probably inaccurate paraphrase, but that's what the cto heard)

~~~
breakingcups
I've actually found MSSQL to be easier to use as a developer than PG. Setting
up local instances, quickly making backups and what not, designing databases,
are all much easier with MSSQL because of their incredibly easy to use tools.

I keep wanting to switch to PG every few months because it's open source, but
keep being stumped by the lack of good tooling. pgAdmin is a joke, rarely have
I ever used a more unintuitive piece of software.

------
ely-s
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

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

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

------
codexon
What about the lack of upsert in PostgreSQL?

~~~
dozzie
Well, the same as the lack of upsert in SQL Server. Bear in mind that UPSERT
!= MERGE.

[http://www.postgresql.org/message-
id/CAM3SWZRP0c3g6+aJ=YYDGY...](http://www.postgresql.org/message-
id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com)

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

