PostgreSQL vs. MS SQL
pg-versus-ms.comI 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..."
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.
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).
I just can't replace the keyboard.
I wouldn't let it anger you. I care about quality but every for-profit-company development team I've been on, I was forced to hold my nose at some point/many points/constantly due to some feature demand(s) that had nothing to do with providing a higher quality product and everything to do with either a customer and/or a manager that was a complete and total moron.
Weird that OpenSSL isn't on the list of OSS projects.
Weird that Google Maps isn't on the list of commercial projects next to iOS Maps...
I hit the one about how MS SQL Server doesn't run on Linux and had a similar reaction.
As someone that uses PostgreSQL, would you care to elaborate on the topic? I haven't used MS SQL Server so maybe if you can actually provide some useful information we can start a discussion and learn something new.
I think many IT departments choose MSSQL for the same reason they choose Windows - it's an easy to setup system with good tools and lots of commercial support. MSSQL also integrates very tightly with Windows and you can use single-sign-on via Windows authentication with no setup.
The administration GUI included with SQL Server (called SQL Server Management Studio/SSMS) is very robust and whenever I look, I always see other people asking around for something as good as SSMS for other RDBMS. If you use Unix you might prefer your command line tools, but a lot folks would rather obviously use a GUI.
For developers, I haven't found anything better than SQL Server Data Tools (SSDT) which is a set of extensions for Visual Studio that let you design and develop a SQL Server database. It creates version migration scripts, diffs database schemas and data, integrates with git and other source control providers and generates DML scripts among other things.
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.
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?
QFA "MS SQL Server can neither import nor export CSV"
that's a deliberately obtuse response formed by avoiding reading the 478 words of supporting detail immediately following. Certainly there exist csvs mssql will import. But I've run into problems with nested quoting, commas inside fields, and unicode. I'm apparently far from the only one. So I think it's hardly hyperbole to say mssql doesn't support csv.
I took it to mean "can neither import nor export conformant CSV"
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.
My experience with MsSql has been more like You can sorta kinda export CSV files that will mostly work in Excel if the data happens to be just right. His description of how easy it is in Postgres makes me want to grab a copy and try it out. I really hope somebody tells me that there is infact a way to export query results from MsSql to CSV if the text columns might contain commas that isn't a huge ugly PITA.
I don't know about most of the other stuff, honestly. I've never felt the need to write scripting code into database queries or write my own aggregate functions, so I have no idea what would be better at that. I'll take his word that Postgres is better, and grab it if I ever find myself having to do that.
> 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.
Are you saying there is no programatic interface for exporting csv? You must rely on the administration tool to generate the csv for you???
@sergiosgc Nope. There are several ways of doing this. Using the GUI is the easiest, and the most common method. You can use SSIS, or the bcp command.
For example:
bcp "SELECT blah... FROM blah" queryout C:\..\blah.csv -c -t, -T –S<servername>
I have spent quite a bit of time in the past trying to import CSV data to SQL Server. It's just about possible, but bcp (and its format files) are absolutely horrifying. As far as I could tell, other methods of importing data involved either having access to put files onto the server so it could read them or getting SSIS to work - both pretty much impossible in a large organisation.
If there was one thing the author was right about it was MSSQL's poor CSV support. BCP doesn't handle escaping special characters in CSV (e.g. commas, quotes, tabs if you using tabs as delimiter).
Although I've had experience with both PostgreSQL and MS SQL, well over 90% of my experience is with the former.
When exporting data to CSV where a text field contains the delimiter, PostgreSQL will do the correct thing every time: wrap that field in quotes. MS SQL...will not.
That's where MS tends to bug me. Why wasn't that fixed within a few hours of somebody discovering the problem? I ran into a similar issue on an app I just wrote that spits out CSVs. I googled, found a stackoverflow post about how to escape CSVs properly with Excel in mind, and fixed it. One primarily JavaScript dev and maybe an hour of testing, diagnosis and repair.
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.)
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.
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.
I've done a fair amount of work importing CSV files into SQL Server 2008. I have no idea if this is still broken, but I can confirm that 2008 mishandles CSV files where the character used to surround string columns appears escaped within the column itself. Such as:
57,4.3209,"green","He calls himself""Waldo""",98,"Y"
This is a perfectly valid CSV file, but SSIS will choke. I ended up writing a custom script component to load these files anyways.
That said, I absolutely love SQL Server, and I think this guy is a little over the top in his religious hatred of it. In an Enterprise setting, it works really, really well.
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).
I completely agree with you on Excel. It's gotten bad enough that I'm starting to toy with the idea of replacing Excel with a CSV viewer in my workflow (since all that I use Excel for is to view small-ish CSVs, anyway).
I've gotten into using node.js with the mssql module for handling csv or xml in a stream... it does work really well, and the compatibility is pretty smooth.
I wish that line-item JSON were more popular, it's more expressive than csv, the tooling (programatic) seems to be a bit better, and much less verbose than equivalent XML, which I always had a problem with, as mapping XML to/from an object model is always more painful by comparison.
line-item utf-8 json+gz work really well for import/export data...
> 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.
RFC 4180 is close enough for me.
what gets me is scientific notation. Sure, I'd love for you not ask me and convert all my large numbers into scientific notation.... and loose digits
> 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.
It's also totally counterproductive. If you tell me to use something and you start telling me things that aren't true I'm going to be deeply skeptical of your possibly-valid points if I know you're either ignorant or dishonest in some cases.
this is, well, particularly apropos for keithwarren -- I had no end of pain getting csv into sql server; pg handles it fine
anecdotal but I have not had such issues, but being fair I have mostly not had to deal with non-english scenarios.
It is one thing to say 'SQL Server takes some effort to import CSV files with X scenarios'; but to say it does not import them at all?
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.
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.
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/
[2] https://www.wittenburg.co.uk/Entry.aspx?id=d8c54975-bd0a-410...
> 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?
Bringing up the "no warranty" clause is a total straw man argument. Almost every software license (whether proprietary or open source) has a no warranty clause.
EnterpriseDB seems to be the corporate benefactor behind PostgreSQL, and is run by what looks to be a lot of former Redhat guys. There's definitely commercial support out there.
As a programmer, I would love to use PostgreSQL everywhere over all the options you mention... Mongo has a pretty decent replication/failover setup, better than MS-SQL imho, and MS-SQL being a close second... administering PostgreSQL for high availability seems like an exercise in frustration, with lots of after-thought bolted on solutions.
I think by the time we see either 9.6 or so, or 10.0 (depending on how versioning goes), with PL/v8 and sane replication/failover solution in the box, it'll become my db of choice.
Most of what I use MongoDB for today can be done almost as nicely in PostgreSQL, and the latter can do a lot the former would trip on.
As someone who has worked with PG, MSSQL, Oracle, MySQL and others for almost two decades - I can promise you the author is misinformed about more than just enterprise politics.
Would you care to elaborate on that?
see rest of comments, it is not taking long for people to point out the flaws in the OP
So that is a "no" then? You make a blatant argument from authority and then when asked to affirm your opinion with facts rather than claimed qualifications you refuse (saying "read the other posts" is a refusal if I've ever seen one).
In all honesty it would take hours to write a proper rebuttal. There are many factual errors but there are also many errors that are mostly incorrect. CSV import/export is one of the first things he calls out and he never mentions BCP or SSIS, which are the methods for CSV import in SQL Server. It would be like saying Apache does not support SSL - it is just factually wrong.
I've been using MS SQL Server for over 10 years, including versions as early as 2000. I have extensively used CSVs in every version
Then you're well aware of how broken MS SQL is when it comes to exporting CSVs where the delimiter is in one of the string fields or where a field has an EOL character, etc. PostgreSQL handles all of that with no problems whatsoever. MS SQL doesn't even attempt to follow RFC 4180.
I've actually written small wrapper scripts that feed a query into MS SQL, fetch the results row by row and--with a proper CSV writer (eg Python's csv, Perl's Text::CSV, etc), dump to file. It's relatively slow, but at least it works.
For B), EnterpriseDB look promising. Ex-Red Hatters, similar model.
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.
Can't say much about 1 & 3. But, pgAdmin[1] has a great visual "explain" tool.
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....
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...
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)
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.
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.
I had to solve this problem at my last job and we had vendors let us know how companies like Apple, Sony, Disney, EA etc have solved it. Basically there are two ways to do it. (1) Store it on some "filesystem". In quotes because how you do it can vary wildly e.g. S3, GlusterFS, Standard directories using DRBD for HA. (2) Take the blob, slice it into pieces, hash it and spread it across a sharded database.
Generally it seems to be that if you have lots of unique large files then use filesystem. But if you have files which are likely to have duplicates then use a database. So a file storage locker may use (1) but a service like iTunes Match would use (2). And IIRC Apple in fact does store at least uploaded music files in Cassandra.
We found that a GlusterFS filesystem is a good way to go for our case of 100s of terabytes of unique files with some failover. Amazon S3 would simply cost far more. The file metadata is in the RDBMS, obviously. But storing the binary data in the RDBMS wouldn't be a good idea, and would choke long before it got to our scale.
Usually storing in filesystem with a reference in database seems to work best. Much easier for database management and backup, and file management as well.
The OP doesn't appear to have hear of SQL Server FileStream or FileTables, which handle this abstraction for you: http://msdn.microsoft.com/en-us/library/ff929144.aspx
On pgsql, the blob type. It stores the object out of the row store, but still in the database.
commonly, s3 or zfs nas for storage and pg for metadata
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.
That's basically what a client of mine does.
+1
Let's agree he should have said "no practical length limit".
If you are using gigabyte-long text strings in a relational database, you are doing it very wrong.
> Let's agree he should have said "no practical length limit".
I would have agreed if he hadn't explicitly mentioned the 2GB limit of MSSQL's TEXT and NTEXT in the very next paragraph.
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.
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.
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.)
a) i can't say with 100% certainty, but with 99% certainty someone dropped the index. they don't just disappear.
b) i really wouldn't recommend hitting mssql from anything other than the home grown system.data.sqlclient built into .net.
c) there are best practices on when and how to install updates. this is not it.
d) that is totally a pain in the butt and something msft should probably figure out how to have a reasonable option for.
one, very legitimate gripe i have for mssql is the default lock levels on reads. not everything needs to be fully synchronized and having to litter queries with (nolock) feels like bad design.
one, very legitimate gripe i have for pg; no stored procedures. functions are not stored procedures. also multiple result sets for a single query, but i digress.
Genuine curiosity: what do you mean about functions not being stored procedures? What is it you want to be able to do with them that you can't in postgres?
pl/pgsql functions require you to define the schema that it returns in the declaration itself; this isn't an issue really as it should more or less be known after you've writing the guts of a query, but it's just more boilerplate.
the key difference is the multiple result sets with different schemas (which you can do with stored procedures but not with functions)
A) Indexes do not just disappear. Someone dropped them or restored an older version of the DB.
B) What? That's not SQL Server's fault, it's either a broken third party package or the display settings on your UI chopped the data to fit on a screen or something.
C) Never heard about this. I even tried to google it but I can't find any information about this. Do you have a source?
D) Third party tool problem probably. Not SQL Server's fault. Did the BAK file restore normally using SQL Server?
I wrote the "third party tool" actually. It was tested thoroughly but on the few records with non-ASCII characters, the data came out weird. It was only about 10 or so records so I just manually corrected it. (I don't recommend this approach, it was a "we're under the gun, just change history and don't fret" decision)
I've been using MSSQL since the 7.0 days in the 1990s and have never seen nor heard of A) happening. As you said, I'm certain this was "too many cooks in the kitchen."
I didn't have DBA access so I couldn't do much to prove someone had maliciously dropped indexes. It was about an every-other-month occurrence, and it would make our response time go from 200 ms to 90s.
it sounds like your issues had nothing to do with MS SQL Server and more to do with system administration nightmares and with php.
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.
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.
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.
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...
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.
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)
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.
#1 has gotten significantly better since 9.0. There are still issues, but it continues to get better.
For #2, there are plenty of managed PG services (AWS RDS, Heroku, etc) if you have trouble with the sysadmin.
For #4, Postgres has materialized view support in 9.3 with some improvements in 9.4.
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
If you are interested in Postgres way of doing multiple result sets you can check out my post on it https://blog.dsl-platform.com/multiple-result-sets-alternati...
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 SetupTangentially, 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?
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...
yeah. sql server's `MERGE` is hacky.
http://sqlperformance.com/2013/02/t-sql-queries/another-merg...
Pg is SOLID but I'm sure mssql have its merits.