SQL Server 2008 r2 or PostgreSQL [closed]

Question

I am rewriting an application for my company which requires a database. We are currently using SQL Server 8 and I will be updating the database. My question is which database to use. I have been looking at both SQL Server 2008 r2 and PostgreSQL 9.

The database will be hosted on a Windows Server 2008 box and will be used for both the stand-alone app and an associated website written in .NET.

Some of my points of concern are:

1. Cost (obviously PostgreSQL wins out here).
2. Is it ready for primetime? I have read somethings about it being as good as SQL but would like some input from those using it.
3. Ease of use. I am very comfortable with SQL Server. I have been experimenting with PostgreSQL and while it is different than SQL Server I am getting the hang of it.
4. Speed. Is PostgreSQL as fast as SQL Server?
5. Can I use ADO.NET (.NET 4.0) with PostgreSQL and get the same functionality as Using SQL Server (will be using Linq or Entity Framework)?

I will NOT be converting our old database in SQL Server 8 to the new database so I am not concerned with any conversion.

I would be interested in peoples opinion of the two and if PostgreSQL is an acceptable solution for a business application given the above.

TIA

Brian

Answer 1

Necromancing here.
As pointed out by SteveG, SQL-Server Express is probably what you want.

But I've used SQL-Server, SQL-Server Express and PostgreSQL extensively, and you should be aware that SQL-Server Express has some more nasty limitations other than just the DB size:

• Installation requires administrator privileges (and system restart)
• ReportingService Express can only connect to a local database,
  
• ReportingService Express cannot use other data-sources than the local sql server (no ODBC)
  
• Some ReportingServices features are missing (e.g. graphs and CSV/XML export formats)
• Constrained to the usage of 1GB RAM (Same in 2008/2012)
  
• Constrained to a single CPU (newspeak: Limited to the lesser of 1 Socket or 4 cores)
  
• no Table and index partitioning
• no indexed views
• no database mirroring
• no database tuning advisor
• no notification services
• cannot send email (you might be able to do it using CLR-Stored procedures if you absolutely need to)
• cannot use/call Web-Services (newspeak: no HTTP Endpoints)
• no service broker
• no fulltext and no semantic search (requires Express Advanced Services)
• Profiler tool is not included with SQL Server Express editions. (there is SQL Express profiler on codeplex)
• Job Scheduling service is not available with SQL Server Express.
• Data import and export feature is not available with SQL Server Express (can/must be downloaded)
• does not come with SSIS

and as said
- 4GB database size (raised to 10GB in SQL 2008 R2 and SQL 2012)

Note that the limit of 10GB is neither 10GB nor 10 GiB (10 * 1024³), it's actually 10'240 MB (10 * 1'024 MB), which is actually only 95% of the 10 GB that I'd have interpreted as 10GiB (10'240'000'000*100/10'737'418'240)

Add to this the limitation that Microsoft-SQL-Server will only ever run on Windows (to date, Linux-port of SQL-server is now coming, but only as CTP so far, and without BI).

So to the other points often overlooked:

• In Windows 7, you can only have up to 20 concurrent connections but no more.
  If you need more, you'll have to put your SQL Server installation onto a Windows Server OS. That means if you use SQL-Server Express in a use case with more than 20 concurrent users, you'll still need a windows server license (the number of connections is not related to SQL Server edition, but to the operating system). You'll probably hit the 1GB RAM barrier before as bottleneck.
• If you want to economize, you'll have to run the db engine on the same server as the web server, with all resulting performance penalties.

So now to your points (in order of importance)
5. Yes, there is Npgsql.dll (MIT license), so there is no need for ODBC
BUT: If your code uses System.Data.SqlClient everywhere (e.g. parameters, especially when copy-pasted all over the code) then you will have to re-write more than just the SQL code (Use the abstract classes in System.Data.Common instead, or or the System.Data.IDB* interfaces).
If your code copy-pastes SqlClient everywhere, it could be argued that it needs rewriting anyway.

3. Ease of use:
   A little more of a learning curve than SQL-Server, but overall, not too different. pgAdmin may not fullfill your requirements if you're used to SSMS, but there are several other management & development tools you can use (e.g. Navicat) that come very close to SSMS or even top it.

4.Speed. Is PostgreSQL as fast as SQL Server?
Yes and no. It can actually be faster. Granted, because SQL-Server has had much work done on the query optimizer, PostgreSQL might be a little slower when compared to the Enterprise Ultimate version of SQL-Server (or whatever the current name of the killer-edition is). But, who ever has that version ? If you can use all your CPUs with all cores and all the RAM you want with PostgreSQL, you will always be faster than some cut-down Express or Standard version of SQL-Server with 1 CPU and 1 to a few GB of RAM (windows limits you to 64GB in 64-Bit afaik, with Linux you can exceed the 4 GB RAM per application limit even on a 32 bit processor with the bigmem kernel and can get it to address 64 GB RAM on a 32 bit processor, not to mention the 64-Bit limits), no matter how super-over-optimized the query optimizer is on SQL-Server. That said, my personal opinion from using both pg and ms is that PostgreSql has the better query optimizer, but that's just my opinion, I have no data to back that up. Besides, windows (Windows 7) is a slow operating system (again personal opinion, no data to back it up). So when you can let PG run on Linux in headless mode, it will almost certainly be faster than SQL-Server, even the enterprise ultimate edition.

Memory footprint
You can totally configure PostgreSQL. You are in charge of the configuration file. Can be small or large depending on how you adjust your settings. The overall PG memory footprint to achive the same as SQL-Server on Windows at comparative speed will be quite a bit lower, this is especially true when you run it in headless mode on Linux (no server GUI).



Cost
Yes, quite obviously PostgreSQL wins out here. Do not forget: It's not just the Server licensing costs we talk about, but also the operating system, and remote access (e.g. additional very expensive Citrix licenses on Windows compared to free SSH access on Linux).
And the other thing: PostgreSQL is OpenSource and Free. If you compare it to SQL-Server, you should not compare it to the Ultimate Enterprise Edition of SQL server (and windows server), but with the version you could actually afford. PostgreSQL however will always be the one free ultimate all-inclusive all-free all-opensource BSD license edition. If you compare the features of PostgreSQL to the Sql-Server Ultimate Enterprise Edition, you'll also have to compare the costs to the ultimate enterprise edition as well (and in 99% of cases, you'll find it not worth the price).


2. Is it ready for primetime?
Yes actually Skype and WhatsApp runs its database on PostgreSQL.
You'll find it has some problems (bad error messages - connection disconnected) when you try to run a 50+ MB insert scripts, but Microsoft's SSMS crashes long before that amount.

Summa summarum, PG probably is more ready for PrimeTime than SQL-Server.
- PG has has had paging for almost 10 years now (MySQL as well as SQL-Standard syntax compliant) , SQL server just got that feature with SQL-2012, only standard-compliant.

• You can simply xcopy deploy PostgreSQL, works perfectly without admin rights
  

• PG has XML support, just like SQL-Server
  

• PG has JSON support, SQL Server? I believe they are still fully invested in thinking that XML is the future - and purposely crippling the web as a platform. (they are adding JSON support now for SQL-Server 2016 CTP, but it stores data as nvarchar, so you won't be able to set (fulltext) indices on specific JSON-fields like you can on PostgreSQL)
  

• PG has HSTORE support (associative array column), SQL-Server doesn't
  

• PG has array columns. Arrays are a core part of programming, except in SQL Server, where they don't exist (try parametrize an IN-clause)...

• PG has had spatial support for a long time, SQL-Server's spatial support is relatively new and relies on .NET extensions and is object-oriented (hello RELATIONAL database? )
  

• PG has the better and faster fulltext search (personal opinion, not backed up by data because it's as clear as it's clear that Google-Chrome is faster than IE 8)
  

• PG has companies offering commercial support, just like Microsoft does for SQL-Server; the advantage is that you actually get your bugs/feature-requests fixed for your money on PG support. You probably know Microsoft support, so I'll spare you any further rants of mine ...
  

• PG has IPv6 support, Microsoft ?
  

• PG performs normal under heavy load, with SQL-Server on Windows we've had some strange issues that appear and disappear at random (Heisenbugs ?).
  

• SQL Server still to this day deploys pessimistic concurrency out of the box, PG doesn't
  

• Compression out of the box. In SQL Server, compression is an "Enterprise Edition+" feature which means you are spending the cost of at least 1 dev in order to get the ability to use compression. Once you have paid for that ability, you still have to figure out how to implement it. Postgres does this for you out of the box, automatically and for free.
  

• Concurrent Index Creation. This yet another feature that SQL Server is capable of doing, but only if you are able to afford Enterprise Edition. Postgres has your back on this and you can leave your wallet at home.
  

• Indexable functions – sargability. In Postgres, you can actually index certain functions and maintain sargability. With SQL Server, BOOM - table scans - adios performance
  

• PG employs Unicode (UTF8) by default. No longer can an incompetent predecessor cripple the entire application with horrible varchar and missing N' for string datatype like they do in SQL Server. The N' syntax still works though, so no compatibility problems if you use them.
  

• PG supports recursive cascaded deletes, SQL server doesn't support that at all (though it actively tells you when you want to put the cascade on the referential constraint)
  

• PG supports GREATEST(value [, ...]), LEAST(value [, ...])
  Microsoft: CASE WHEN HugeExp1 > HugeExp2 THEN HugeExp1 ELSE HugeExp2 END) ...
  CASE WHEN N > 2 THEN 'ROYALLY SCREWED' ...
  or this very maintainable gem
  SELECT ( SELECT MIN(Price) FROM ( VALUES (123),(456) ) AS AllPrices(Price) )

• PG supports UTF-8 CSV files since ever, SQL server doesn't support UTF-8 CSV files prior to SQL-Server 2014 SP2 (try import the MaxMind GeoIP database - you need to write your own program for SQL-server...).
  

Drawbacks of PG:

• Windows version ain't as stable & fast as the Linux version
  

• No free BI-tool like SQL-Server-Reporting-Services (ok there is Eclipse BIRT, but it runs on JAVA).
  On the other hand, for a bit of money you can get stimulsoft reports, which has more export formats than SSRS, and actually renders fine in browsers other than IE as well as IE > 8, unlike SSRS (2005 - 2012 so far, and so far I read SSRS 2014 is no different than 2012).

That said, if your only problem is fulltext-search with SQL-Server Express, you should take a look at Lucene.NET (Apache License).

Also, be careful if you're thinking about using SQL-Express in production:

So far we’ve considered only a few of the most obvious limitations, but the point is that what seemed like a great idea driven by the thought of considerable cost savings could end up being an absolute disaster for your business. Worse, any initial cost savings you gained from using a free product could be easily negated down the road by the cost of disaster recovery and the damage to your business’ reputation while an important database is down. So needless to say, despite the appeal of its zero dollar price tag, SQL Server Express is definitely not the right database solution to power your business.

Source: bitwizards.com

Answer 2

This really depends on your DB size and your comfort zone.

SQL Server Express edition is a fully functional free database up to 4GB in size; perfectly adequate for most small to mid-sized apps. (aside - IMHO, MS Licensing was the best thing ever to happen to the open source community!!)

PostgreSQL is a fully functional, free database w/o the 4Gb limitation - however, PostgreSQL follows the Oracle style of SQL and database management. If you're not familiar with that, you can run into some gotchas.

Throwing Dot Net into the mix adds a not insignificant wrinkle. Can you use .Net with PostgreSQL? Yes, but Dot Net will work better with SQL Server. Are you comfortable writing complex ODBC interface code? If yes, PostgreSQL remains a contender. If not, you'll be better off with SQL Server.