Reasons To Use Postgres Over SQL Server (And Vice Versa)

I just recently read this blog post by Rob Sullivan about why he prefers Postgres over SQL Server.  Honestly, I’m not very impressed.  3 of his 10 reasons aren’t really reasons; yes, you don’t get these things if you aren’t using Enterprise Edition, but here’s the thing:  if you’re running a business and decide that SQL Server is the right product for you, go with Enterprise Edition.  Yes, it’s going to cost more money, but the difference isn’t really that much if you’ve already decided to go down the paid RDBMS route.  Not being able to install SQL Server on non-Windows machines is a bit of a letdown, but again, if that’s one of your top reasons, it’s not really much of a reason for most enterprises, as they’re already using Windows servers somewhere.

Regarding arrays, it’s true that SQL Server doesn’t have them.  There are better and worse alternatives, but aside from parameter input, if you’re using arrays, you’re thinking procedurally and that’s doing it wrong.

Now, I’ll say that I like Postgres a lot—it’s my second-favorite relational platform, and I use it on my own corporate site.  And way up on my list of reasons to use Postgres over SQL Server is its greater support for windowing functions.  But I decided to try to come up with 10 reasons to use SQL Server over Postgres, just to see if I can do it.  Here goes:

  1. SQL Server Integration Services.  You get a fully-featured, high-quality ETL tool out of the box.
  2. SQL Server Analysis Services.  You get probably the most complete implementation of the Kimball method for data warehousing out of the box.
  3. SQL Server Reporting Services.  You get a mid-range, pretty decent reporting solution out of the box.
  4. SQL Server Management Studio.  This is an outstanding tool.  Yeah, it’s kind of a mish-mash of developer tool plus management tool, but it works quite well.
  5. Always On Availability Groups.  You get failover and read-only replicas out of the box.  There are high availability options available for Postgres, but I’m not familiar enough with them to know how well they work; I am familiar enough with Always On to know that it does a nice job.
  6. Service Broker.  This is a built-in message queue system which SQL Server uses internally.  It’s true that it won’t scale to the extent of Rabbit or another in-memory queuing system, but for relatively low-frequency message pushing, it works well out of the box.
  7. SQL Server Agent.  You get a good scheduling tool out of the box.  Yeah, there are better schedulers out there, but it does the job pretty well.
  8. Extended Events and wait stats.  Managing a SQL Server instance is relatively easy due to these two things.  Extended Events let you monitor system health and wait stats tell you which resources your queries are waiting for (be they locks, latches, CPU, disk, memory, network, or even because somebody selected 20 million rows and their client is taking its sweet time reading in all of the data).  I have no idea if these concepts exist in Postgres, but they’re out of the box in SQL Server, and 2012 even has a GUI for Extended Events.
  9. Policy-Based Management.  This is another management tool, but one which can act as preventative maintenance as well.  You set up policies (such as how procedures should be named) and can apply those pro-actively or use them to scan a system for problems.
  10. Resource Governor.  SQL Server 2014 version should make this a must-do for enterprises, because then you’ll be able to throttle and cap I/O as well as CPU.  This way, your business analysts won’t take down your OLTP server by running crazy queries on it.
  11. Community.  This is my ace in the hole.  SQL Server has a huge support community, with blogs, user groups (virtual and in-person), ridiculous numbers of videos and tutorials from outstanding DBAs, regular free conferences, big conferences, and even newsletters.

SQL Server isn’t for all businesses, but there are some powerful reasons to adopt it as well as Postgres.

Advertisements

3 thoughts on “Reasons To Use Postgres Over SQL Server (And Vice Versa)

  1. Charles Shuller says:

    Regarding Arrays: These aren’t really procedural. Arrays are an intrigal part of most, if not all, declaritive languages where they tend to be called lists (See Prolog and Mercury). Additionally, the difference between tuples, sets, and arrays are essentially implementation details, so I’m not sure how you justify the assertion that they break a declaritive paradigm.

    Regarding the Toolset bullet points: I am a bit dissapointed that you dedicated so much time to the solutions that ship with SQL Server. Frankly, I was hoping for something dealing with performance characteristics or database functionality. The fact that database applications(SSIS, SSMS, SS??) ship with SQL Server is hardly relevant to how the actual databse works, unless you’re trying to somehow justify it’s cost of course.

    Finally, I don’t think I’d recommend anyone use SSIS, it gave me nothing but grief, and I lost a week trying to use it. I wrote the same script in Python in about 6 hours. All I wanted to do was dump out tables into a format simmilar to what Postgres uses as the output of it’s COPY command, but that required detecting NULLs, and SQL Server tools seem to actively attempt to confuse NULL and the Empty String.

    • On arrays, I’m saying that they break the relational paradigm, and that’s vital for this DSL. The big reason comes down to normalization. If I have an array of something, then we are quite likely to violate first normal form, which increases greatly the risk of bad data. Developers already tend to violate the basic rules of normalization far too often… The primary exception to this point is using an array as a set of input parameters to a stored procedure or function to simplify handling an indeterminate number of inputs (as I noted). T-SQL has table-valued parameters as a set-based alternative, although they are somewhat limited and have some non-zero overhead cost of building and populating the table. The biggest counter-argument to my point is that arrays are in fact part of ANSI SQL 99, but to be honest, I consider that a mistake because they essentially give free license to violate 1NF.

      The fact that there are a number of high-quality tools available for SQL Server is absolutely a valid point in part because of how unique this is and in part because of the nature of the discussion. The FOSS options typically don’t ship with many tools at all, outside of the database engine and a basic, command line-driven query editor. And on the paid side, Oracle and DB2 have you start shelling out cash for core components like a graphical query editor. The fact that I can walk into a shop and assume not only that I have these tools available but also that I can translate my current skills without a hitch over to another shop is certainly quite valuable. I infer from your last half-sentence (“unless you’re trying to somehow justify it’s cost of course”) that you don’t consider this valuable, but for both Ron Sullivan and me, that was the entire point of this exercise. Sullivan’s post was a list of reasons “why I’m favoring Postgres over SQL Server from a technical/business aspect” (emphasis mine). In other words, he’s giving reasons why he recommends that businesses pick up Postgres, and my exercise was to the contrary: to give reasons why I recommend that businesses pick up SQL Server. It’s not really an argument of which is best in comparison to some Platonic (or ANSI) ideal, but rather the recommendation of a consultant for a business whose management knows that the company needs to get off of spreadsheets and paper or Foxpro and DBase but doesn’t know where to go from there. For that reason, I consider tool sets to be completely in-bounds for discussion.

      I’m sorry to hear about your bad experience with SSIS. It has a pronounced S-shaped learning curve and once you hit that first inflection point, it can feel like a terrible product (especially the pre-2012 versions). It does get much, much better once you get further along, however. The primary use case for SSIS isn’t necessarily so much dumping out tables (although it can do that), but advanced ETL like data warehousing. You and I can bang out a Python, Perl, or Powershell script in a relatively short amount of time to copy a table’s contents to text file, but my primary use cases for SSIS involve retrieving, validating, cleansing, and transforming data; maintaining slowly-changing dimensions; putting rows into different tables based on given criteria; logging relevant activity (start and end times, number of rows down each relevant path, details on any rows which fail at any step along the way); and handling all errors gracefully (along with giving me the relevant information I need to troubleshoot the problem without re-running anything) with the option to restart in the middle. You certainly can do that in the language set of your choice (some SQL dialect + whatever) but Integration Services really shines in those scenarios and I know I definitely would get that job done in a small fraction of the time compared to writing all of this in any C-based or scripting language.

      By the way, thanks for your comments. They were both thought-provoking and interesting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s