What makes PostgreSQL better than SQL Server?

In addition to cost and cross-platform, see below list of advantages: http://datachomp.com/archives/top-10-reasons-i-like-postgres-over-sql-server/ SQL Server still to this day deploys pessimistic concurrency out of the box. Anyone not aware of this “feature” starts out very disadvantaged on performance. This person will soon get an internet history filled with locking/blocking/deadlocking links. Postgres defaults to optimistic concurrency via its MVCC feature and is a joy to work with. Compression out of the box. With SQL Server, compression is an “Enterprise Edition and Up” 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. You are going to find a recurring theme there…this is yet another feature that SQL Server is capable of doing, but only if you are able to afford the elite and affluent company of Enterprise Edition. Postgres has your back on this even if you left your wallet at home. Partitioning out of the box. Our data is growing and we need to do something about it, but perhaps we don’t want to take on the drama of sharding yet. Partitioning can be a pretty good fix for this and with Postgres, unsurprisingly, you get it out of the box. As for SQL Server, basic table partitioning is only available in Enterprise Elite edition. Yep, you’re writing huge checks to pull this off. Indexable functions – In Postgres, you can actually index certain functions and maintain sargability. With SQL Server, you’re stuck in the cruel world of table scans when this happens. Modules/Extensions – Tech moves fast. The fact that Postgres has an extensions system means that your DB platform can now match the innovations on your web stack. SQL Server has releases every 3 years… It quickly becomes your grandparents’ database. Works everywhere – Linux, Mac, BSD, Windows… Postgres comes to you on the platform you want to code on. SQL Server is flexible in that it works on all 50 versions of Windows Vista, Windows 7, Windows 8. Arrays – Everyone loves arrays and they are a core part of programming…except in SQL Server, where they don’t exist. JSON / V8 support – You can write/use JSON and Javascript on the client, server and even your database if you are using Postgres. SQL Server? I believe they are still fully invested in thinking that XML is the future. Unicode by default – No longer do you have to play the nvarchar / varchar implicit conversion game or have horrible string datatype mapping like you do in SQL Server. Postgres is UTF-8/varchar out of the box and wants nothing more than to give your app gigantic, unconditional data hugs.

For me this is much more subjective than objective. First I enjoy programming functions and views in postgres much more than in SQL Server.  This is probably because postgres is much closer to Oracle in design than the unfortunate Sybase mutation SQL Server has become.  Often programming on SQL Server feels like a lot of hacking than creating elegant solutions.  Note: I can always get SQL Server to do what I want eventually I just don't always enjoy the gyrations required to get the same results.  I am not afraid of command lines when interacting with an OS and see Windows as very limited when thinking about performance, scalability and security when compared to linux.  I find Postgres much more interesting and robust with its opensource architecture for plugins.  For example how it does encryption or geo spacial extensions just to name a few examples.  If I wanted to make my own extension I could easily do that.  In SQL Server this ends up being a lot of .Net hacking that requires code to be much farther away from the data than I prefer.  I am very fond of modeling Key Value Stores in a database (Heigharchy/Tree Data Models.)  I can only really do this in databases where the optimizer is sophisticated enough to do recursive SQL effectively.  Both Oracle and postgres have lots of options that perform very well for me with a billion row self joining table.  Oracle is even better with the most fun options and windowing functions.   SQL Server just doesn't have this yet to the same degree.  It doesn't suck but it is just not as fast and the viable max row count of such a table is much lower.  I suspect other will talk about more technical implementation details that I would agree with as well but but I don't really care about those as much as the above.  There are also a few things I do like about SQL Server better than postgres but as soon as we go down that rabbit whole and we talk about paying for a RDBMS I will stop the conversation and go buy Oracle when given a choice.

PostgreSQL can fit in every hardware, just like Firebird. And still support some Oracle-like features. I use PostgreSQL in a small vmware machine (CentOS, 2Gb RAM) with a Tomcat frontend in the same server(about 80 users, not at same time). People feel impressed how it run fast.