29 ms·

PostgreSQL vs. MS SQL

142 points by cribwi 12y ago

taspeotis 12y ago
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.
- nathan-muir 12y ago
  Can't say much about 1 & 3. But, pgAdmin[1] has a great visual "explain" tool. [1] http://www.pgadmin.org/ http://www.pgadmin.org/
- roller 12y ago
  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.html http://www.postgresql.org/docs/9.3/interactive/auto-explain....
  - taspeotis 12y ago
    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-query-view http://www.sqlsentry.com/products/plan-explorer/sql-server-q...
sarciszewski 12y ago
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.
- bigdubs 12y ago
  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.
  - lgas 12y ago
    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?
    - bigdubs 12y ago
      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)
arthursilva 12y ago
Pg is SOLID but I'm sure mssql have its merits.
masklinn 12y ago
> 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.html 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)
- DrJokepu 12y ago
  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.
  - henk53 12y ago
    +1
  - UnoriginalGuy 12y ago
    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 12y ago
      commonly, s3 or zfs nas for storage and pg for metadata
      - DrJokepu 12y ago
        That's basically what a client of mine does.
blahblahOOO 12y ago
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 12y ago
  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 12y ago
    QFA "MS SQL Server can neither import nor export CSV"
    - appleflaxen 12y ago
      I took it to mean "can neither import nor export conformant CSV"
      - socceroos 12y ago
        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.
    - x0x0 12y ago
      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.
keithwarren 12y ago
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.
- rodgerd 12y ago
  > 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.
  - x0x0 12y ago
    this is, well, particularly apropos for keithwarren -- I had no end of pain getting csv into sql server; pg handles it fine
    - keithwarren 12y ago
      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?
- cdh 12y ago
  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.
threeseed 12y ago
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.
- rodgerd 12y ago
  For B), EnterpriseDB look promising. Ex-Red Hatters, similar model.
- keithwarren 12y ago
  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.
  - angersock 12y ago
    Would you care to elaborate on that?
    - keithwarren 12y ago
      see rest of comments, it is not taking long for people to point out the flaws in the OP
      - UnoriginalGuy 12y ago
        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).
        keithwarren 12y ago
general_failure 12y ago
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..."
- bergeoisie 12y ago
  Weird that OpenSSL isn't on the list of OSS projects.
  - tedunangst 12y ago
    Weird that Google Maps isn't on the list of commercial projects next to iOS Maps...
- fluffygrenade 12y ago
  I didn't even get that far. Clearly SQL Server definitely wasn't suitable for our 4.5TiB of LoB financial data in 500 tables for the last 15 years and was such poor quality that we have absolutely no problems whatsoever running two 48 core Xeons with 64GiB of RAM at 50% load 24/7... Edit: I live down the road from the guy: Matthew Byrne (address removed, but lives in Shepperton, UK) Please have enough balls to put your name on the site and the article or enough skills to hide your whois entry.
  - partisan 12y ago
    Just curious as to why the article merits this type of vitriol. I'm a .NET/SQL Server guy from the states, so I have no skin in this game, but it just seems to me that this is unwarranted.
codexon 12y ago
What about the lack of upsert in PostgreSQL?
- dozzie 12y ago
  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=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGY...
- swasheck 12y ago
  yeah. sql server's `MERGE` is hacky. http://sqlperformance.com/2013/02/t-sql-queries/another-merge-bug http://sqlperformance.com/2013/02/t-sql-queries/another-merg...
JohnBooty 12y ago
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 12y ago
  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.
MichaelGG 12y ago
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.
- zapov 12y ago
duncans 12y ago
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.
ely-s 12y ago
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
jamhan 12y ago
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 12y ago
  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...
dscrd 12y ago
Tangentially, can anyone recommend a good book for learning PL/PGSQL and other ways to procedurally program on psql?
tracker1 12y ago
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 12y ago
  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.
swasheck 12y ago
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.
pathikrit 12y ago
Is there something like this for MYSQL? pg-versus-my.com?
Pherdnut 12y ago
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?

142 points by cribwi 12y ago

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.

nathan-muir 12y ago
Can't say much about 1 & 3. But, pgAdmin[1] has a great visual "explain" tool. [1] http://www.pgadmin.org/ http://www.pgadmin.org/
roller 12y ago
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.html http://www.postgresql.org/docs/9.3/interactive/auto-explain....
- taspeotis 12y ago
  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-query-view http://www.sqlsentry.com/products/plan-explorer/sql-server-q...

sarciszewski 12y ago

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.

bigdubs 12y ago
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.
- lgas 12y ago
  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?
  - bigdubs 12y ago
    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)

arthursilva 12y ago

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

masklinn 12y ago

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

DrJokepu 12y ago
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.
- henk53 12y ago
  +1
- UnoriginalGuy 12y ago
  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 12y ago
    commonly, s3 or zfs nas for storage and pg for metadata
    - DrJokepu 12y ago
      That's basically what a client of mine does.

blahblahOOO 12y ago

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 12y ago
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 12y ago
  QFA "MS SQL Server can neither import nor export CSV"
  - appleflaxen 12y ago
    I took it to mean "can neither import nor export conformant CSV"
    - socceroos 12y ago
      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.
  - x0x0 12y ago
    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.

keithwarren 12y ago

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.

rodgerd 12y ago
> 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.
- x0x0 12y ago
  this is, well, particularly apropos for keithwarren -- I had no end of pain getting csv into sql server; pg handles it fine
  - keithwarren 12y ago
    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?
cdh 12y ago
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.

threeseed 12y ago

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.

rodgerd 12y ago
For B), EnterpriseDB look promising. Ex-Red Hatters, similar model.
keithwarren 12y ago
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.
- angersock 12y ago
  Would you care to elaborate on that?
  - keithwarren 12y ago
    see rest of comments, it is not taking long for people to point out the flaws in the OP
    - UnoriginalGuy 12y ago
      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).
      - keithwarren 12y ago

general_failure 12y ago

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

bergeoisie 12y ago
Weird that OpenSSL isn't on the list of OSS projects.
- tedunangst 12y ago
  Weird that Google Maps isn't on the list of commercial projects next to iOS Maps...
fluffygrenade 12y ago
I didn't even get that far. Clearly SQL Server definitely wasn't suitable for our 4.5TiB of LoB financial data in 500 tables for the last 15 years and was such poor quality that we have absolutely no problems whatsoever running two 48 core Xeons with 64GiB of RAM at 50% load 24/7... Edit: I live down the road from the guy: Matthew Byrne (address removed, but lives in Shepperton, UK) Please have enough balls to put your name on the site and the article or enough skills to hide your whois entry.
- partisan 12y ago
  Just curious as to why the article merits this type of vitriol. I'm a .NET/SQL Server guy from the states, so I have no skin in this game, but it just seems to me that this is unwarranted.

codexon 12y ago

What about the lack of upsert in PostgreSQL?

dozzie 12y ago
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=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGY...
swasheck 12y ago
yeah. sql server's `MERGE` is hacky. http://sqlperformance.com/2013/02/t-sql-queries/another-merge-bug http://sqlperformance.com/2013/02/t-sql-queries/another-merg...

JohnBooty 12y ago

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 12y ago
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.

MichaelGG 12y ago

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.

zapov 12y ago

duncans 12y ago

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.

ely-s 12y ago

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

jamhan 12y ago

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 12y ago
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...

dscrd 12y ago

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

tracker1 12y ago

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 12y ago
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.

swasheck 12y ago

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.

pathikrit 12y ago

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

Pherdnut 12y ago

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?