
Top Reasons I like Postgres Over SQL Server - craigkerstiens
http://datachomp.com/archives/top-10-reasons-i-like-postgres-over-sql-server/
======
endersshadow
"Top Reasons I like Postgres over SQL Server _as a Database for Web
Applications_."

SQL Server has some neat features, and yes, it's more expensive than a free
tool (and yet, wildly less expensive than Oracle, its main competitor). What
Postgres doesn't have is the entire BI suite of tools that comes with SQL
Server: Analysis Services, Integration Services, and Reporting Services. It
also comes with MDM, Data Quality Services, a complex event processor
(StreamInsight), etc, etc. It's not just a database.

I like SQL Server, and I use it all the time--but again, I do BI. That being
said, if I were creating a web application, I'd use Postgres in a heartbeat.
SQL Server's defaults are aimed at different applications than Postgres is.

~~~
DataChomp
Keep in mind I've been a SQL Server DBA for the past 10 years, and while it
does come with builtin tools like SSIS/SSRS and stuff, it isn't all sunshine
and lollipops. For example, how well does SSRS works for people that don't use
Internet Explorer? Or any type of reasonable debugging in SSIS. Yes, there are
tools that come with it, but many of them feel like crap thrown together by an
MS intern. and yes, some are really nice and refined as well.

That being said, this post was intended for the web app crowd.

~~~
cpher
Yeah, we have issues with SSRS _and_ Crystal Reports in cross-browser
environments. i.e. the "preview" never looks like what you intend. Instead, we
work around it by exporting directly to disk, which seems to keep the WYSIWYG
aspect.

I'm more interested to know how MS people have integrated PostGres into their
environments. Which ORMs work? What are the gotchas, etc?

~~~
hp50g
Nhibernate. No issues.

To be honest we're shifting to java. Our license fees are well into 7 figures
for MS stuff for no gain. This is the first step. Some subsystems are moving
from asp.net web forms+mvc to spring, tomcat, hibernate, postgres. Cost
savings are huge, staff available are orders of magnitude better and
reliability is higher as to be honest, windows has been a piece of shit to
manage over the last 10 years.

Fortunately we've got a pretty well designed system so we can move it over
piecemeal as and when we update major subsystem features.

~~~
tracker1
Wow, my experience has been a bit different. Every Java project I've worked on
has been an exercise in frustration... Though a lot of the .Net projects I've
worked on I can say the same.

I think that NodeJS + MongoDB has been the least resistance I've seen so
far... though some of its' limitations are really frustrating (non-indexed
result limitations). I like Postgres at least as much as MS-SQL Server. And
the Entity Framework generators have been close to a blessing. That said, I'm
much more of a fan of the DB as dumb storage, and not a big fan of SSIS. So
changing from one DB to another is mostly transparent when things are how I
prefer them.

------
gav
Just to play devil's advocate:

5\. SQL Server supports indexes on computable columns [1]

6\. CLR Integration allows you to write a lot of things in C# (or VB.NET) [2]

10\. Unicode is there by default if you use nchar/nvarchar/ntext columns,
there's no problems unless you choose to use char/varchar/text instead

[1] [http://msdn.microsoft.com/en-
us/library/ms189292(v=sql.90).a...](http://msdn.microsoft.com/en-
us/library/ms189292\(v=sql.90\).aspx)

[2] [http://msdn.microsoft.com/en-
us/library/ms254498(v=vs.80).as...](http://msdn.microsoft.com/en-
us/library/ms254498\(v=vs.80\).aspx)

~~~
marcosdumay
You did lose the point here:

> 6\. CLR Integration allows you to write a lot of things in C# (or VB.NET)
> [2]

You can also use a Mono plugin at Postgres, just like Perl, Python, C, C++,
Lisp, Haskel, R-Cran... But it's not that, #6 was about the core functionality
of the DBMS changing. For example, you can add new index types at Postgres
with plugins, and people do add them. Once somebody wrote a plugin for
manipulating SQL queries at what was at the time an object oritented database
called Postgres. That's how deep you can go.

> 10\. Unicode is there by default if you use nchar/nvarchar/ntext columns,
> there's no problems unless you choose to use char/varchar/text instead

Yeah, Unicode is used by default if you tell the DBMS to use Unicode... Thus,
it's not the default. I'd agree that this is not a big problem.

~~~
josteink
> Yeah, Unicode is used by default if you tell the DBMS to use Unicode...
> Thus, it's not the default.

False. Positively false. Because there is _no_ default. Whenever you create a
new column, which incidentally should contain text, you need to choose a type
for that column. There is no default column-type. You need to be specific
about how your data gets stored. That's why it's called a schema.

And when making that choice, for your column which will contain text, you must
ask yourself two questions:

1\. What sort of capacity is needed?

2\. Are you living in the 1980s or in the year 2013? If your answer is the
latter, you _always choose Unicode_ unless you can argue for why it wont ever
be needed. That's all.

In 2013 you don't have to argue for the need for Unicode. You have to argue
for the right to use antiquated, primitive 8-bit text-types. And then you
choose (because you and noone else will have to choose that column-type)
Unicode text-types by default.

Anything else is madness. If any tool you rely on to do this for you chooses
non-Unicode by default it is broken and needs to be fixed.

There might be a million other things SQL Server should address, but this is
not one of them. Nothing is wrong with text-support.

------
stormbrew
What I haven't seen recently is an explanation of "Reasons I like Postgres
Over Mysql." It seems like after a long period of stagnation, postgres
suddenly became hugely popular among startups again in spite of some of the
things that I consider its core failings not being fixed (mainly: an even more
expensive per-connection model than mysql and extremely painful replication,
largely offloaded to third party tools).

Is it because of HStore? Heroku using it? The Oracle buyout of Inno and then
Mysql?

Mysql 3.x was quite awful (other than replication), but by 5.x it had started
getting good, and now the forks are doing some fantastic work. So it surprises
me.

~~~
lobster_johnson
By "a long period of stagnation" I assume you are referring to public exposure
-- because it's not true that Postgres itself has stagnated.

Postgres has been developed steadily the last decade, and has been a serious
competitor to major RDBMSes since version 8.1 (which added two-phase commit,
table partitioning, index bitmap scan and shared row locking, among other
things.)

I say _public exposure_ because Postgres has clearly been popular among many
developers for a long time It's ben beneath the radar for a lot of people,
partly due to pervasive myths ("Postgres is slow"), partly due to the fact
that, well, a product doesn't sell itself.

I think the reason Postgres has shown up on HN a lot is simply that people are
finally discovering it and recognizing its technical brilliance, careful
engineering and high performance, and it's snowballing. I myself have used
Postgres for web apps since 2003 myself, and I have evangelized among
colleagues and acquaintances to the best of my abilities, so I am very happy
about this progress.

I don't see why it should surprise you. While I'm sure MySQL 5.0 and its
various forks have gotten better over the years, it's hard to argue that
Postgres is not technically superior in pretty much every way. Even if you
care solely about performance, which used to be MySQL's main selling point,
Postgres has overtaken MySQL a long time ago.

> _an even more expensive per-connection model than mysql_

This is not a practical issue for most people, in my experience. At least
nobody is really complaining about it. Rails apps happily pool connections,
for example.

If you can't pool connections in the app, there are several excellent, mature
connection-pooling proxies around (including a very cool one, pgPool-II, which
can also load-balance and replicate at the same time).

> _and extremely painful replication_

Is it possible that you have not been following the developments? If you don't
need per-table/per-database replication, then streaming replication is
actually incredibly _painless_. It is by all accounts awesome. And it scales
very well.

If you do need fine-grained replication, there are several mature third-party
projects that are fairly painless to operate, including Bucardo and Londiste.
(I would not count Slony as "painless", although it's probably the most
comprehensive solution right now. Avoid it unless you are a bank.)

There is also a multimaster-replicating fork (PostgreSQL-XC) that is
apparently working very well, and implements true distributed ACID
replication, as opposed to the kind of "de facto multimaster replication" you
set up in MySQL, where you have to make sure primary keys don't overlap
between hosts.

------
MichaelGG
On compression: > Once you have paid for that ability, you still have to
figure out how to implement it.

Postgres has lots of advantage, but "figuring out how to implement it" is
rarely an issue on SQL Server. SQL Server must be the easiest-to-use complete
database system on the market.

------
polskibus
All that is missing in Postgres is materialized views feature. I would happily
contribute to a kickstarter project or similar for implementing them in
Postgres.

~~~
rtfeldman
Good news! It's slated for the next release. :)
[http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-...](http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-
highlight-materialized-views/)

~~~
fdr
I think most people used to a materialized view features in proprietary
databases will find the incarnation of Materialized Views as committed quite
limiting...nevertheless, it seems important to me that this foundational work
has been integrated.

In particular, incremental updates of the materialized view are not yet a
feature. That said, a lot of hang wringing has been going on in the lists on
how to build on this work, so it looks like this whole subsystem will receive
some attention.

~~~
jacques_chester
That's how the Postgres team works. They tend to start with the primitives and
build a bit more of the niceties in each release. And they do so very
carefully, which is important.

People forget that this is true of every software system ever. No successful
system ever starts with every feature it's going to have.

------
rubinelli
The main reason why my company uses SQL Server is that HR can find DBAs to
hire here in São Paulo. Finding good DBAs to take care of our few MySQL
servers was hard enough.

But I still sigh every time I have to convert perfectly valid UTF-8 before a
bulk insert...

------
holri
Well he missed the main point:

Posgresql is free software.

And therefore for example no company can force you into their lock in for
their profit and your loss.

~~~
marcosdumay
That's my main argument for using Postgres:

1 - At the start of your project's life, you'll want it because it's free as
in beer. Except for ease of developping (where Postgres is also great),
nothing else matter.

2 - Later, when you need some performance, even if you have a usage patter
where Postgres is slower, you'll discover that the price of any of the other
DBMS is highter than just adding hardware. Thus, free as in beer wins again.

3 - If you get huge, you'll discover that free software is a big deal, and the
limitations of the proprietary DBMS add a huge amount of drag. Free as in
speech wins now.

~~~
avenger123
I like your reasoning and its a large driver for my I'm moving away from SQL
Server.

If I get to a point where I actually need the features, that SQL Server or
another commercial RDBMS has, then I have a _good_ problem. It means my
business is doing well and I _may_ be in a position to consider a changeover.

I used to fret about features that frankly don't really matter when you are
starting out. Coming from an Oracle and SQL Server background, I wouldn't
touch MySQL but Postgres hits the 80% mark quite easily.

~~~
holri
freedom is more valuable than features

------
SjuulJanssen
SqlServer issues I've encountered or found on the interwebs:

* Microsoft SQL Server 2005 (the release version and service packs) and earlier versions of SQL Server are not supported on Windows Server 2012 or Windows 8. * SQL Server 2008 only runs on W2012 /Win8 from If you install R2. * Importing .bak files from SQL2005 to 2012 Express (Not sure about the full version) will not import. * Copying a database from SQL2005 to 2012Express in management studio will not work either. * SqlServer 2012 Will not run on Server 2003 * SQL Server 2012 requires a minimum of 6 GB of available hard-disk space. * SQL Server 2012 will not run on Server 2008, Vista, Win7 without a Service Pack

<http://msdn.microsoft.com/en-us/library/ms143506.aspx>

------
sklivvz1971
90% of the points are irrelevant. The real value of a database is _how well
does it scale up and out_. All the rest is minutiae.

Why do most companies that scale out (and thus massively benefit from free-as-
in-beer software) actually choose MySql over PostgreSQL? Because of the
features? Nope...

Why do most companies that scale up (and thus massively benefit from
expensive, but highly performant software) actually choose MSSQL or Oracle
over PostgreSQL? Because of the tag price of the Enterprise features? Nope...

~~~
jacques_chester
> _Why do most companies that scale out (and thus massively benefit from free-
> as-in-beer software) actually choose MySql over PostgreSQL?_

<http://en.wikipedia.org/wiki/Path_dependence>

------
jamescun
While I could probably write a long list of my bad experiences with MSSQL (or
just any database really), I think a lot of the authors grievances come from
it really solely being an enterprise application while ignoring some of the
good points that comes with enterprise applications; such as support (MS
support is a mixed bag, some amazing support reps and some terrible ones) and
business integration.

~~~
jeltz
You can buy excellent support for PostgreSQL at quite cheap prices actually.
Especially since you just have to pay for the support and not any license.

------
est
Just learned sargable/function index for Postgres. Looks really cool feature.

[http://www.postgresql.org/docs/9.1/static/indexes-
expression...](http://www.postgresql.org/docs/9.1/static/indexes-
expressional.html)

One question: Does this work for bitwise operators?

------
don_draper
One of my top reasons: You get the latest and greatest with all the
scalability features without having to pay for the production version.

------
g8oz
How does Postgres performance rank on Windows?

~~~
gtaylor
It varies wildly based on platform, filesystem, hardware, and usage cases.

Is your workload write-heavy? Is it more read-heavy than write-heavy? How much
query throughput are you talking?

At the smaller scale, it doesn't really matter. At the larger scale, your
app's characteristics determine how much (if any) you are disadvantaged over
running a more performant Postgres+Linux combo.

~~~
g8oz
Ok I'll try to be more specific. How much slower is it than SQL Server for a
web app that does a 70%-30% split between reading and writing. 10,000 users a
day each spending about an hour on it. I've no idea how to measure query
output, will have to look that up. Maybe this is more of a Stack Exchange
question.

~~~
marcosdumay
That still depends. It'll depend on the size of query X number of queries,
type of data, coverage of your indexes (what depend on your actual data), and
a ton of other factors.

I'm not even sure there is somebody out there capable of correctly measuring
this. You may need to go with a fast biased study anyway.

Also, why are you assuming Postgres is slower?

~~~
Avshalom
I'm not the parent but I sort of operate on the assumption that Windows isn't
Postgres primary target when they make optimizations. While SQL Server is not
only purely focused on Windows they have have access to knowledge about (and
in server editions, probably influence on) the low level internals that
Postgres people never will. As pointed out in on of the top comments SQL
Server has a bunch of extra junk built in(ish) but still I too would, having
not tested anything, assume SS would be faster than Pg on Windows.

~~~
gtaylor
I think you're correct here. Postgres has had lots of optimization work
specifically for Linux and Unix/BSD. The Linux kernel has also had some
optimization done to improve hot paths that Postgres hits.

I'm sure the Postgres team has kept an eye on Windows, but I doubt it gets
nearly as much attention as the *nix environments.

~~~
jeltz
Indeed, most of the benchmarking of the core PostgreSQL team is done on Linux.
Some of those optimizations also benefit Windows but there is little dedicated
performance work for platforms outside Linux as far as I can see from reading
the mailing lists.

------
guard-of-terra
I would like that everyone call it "Microsoft SQL Server" over "SQL Server".

Or else Microsoft has successfully squatted a common, generic term as a name
of their product. Not a good thing if you ask me.

~~~
FireBeyond
Databases have always been known as relational, RDMS, etc.

In the "old days" I never heard of people referring to Oracle as a "SQL
Server", nor DB2, etc.

Even now, people talk of Postgres as a "RDBMS", not, "Postgres, a SQL server".

So I'd say that I don't particularly agree with your postulation.

~~~
Bill_Dimm
"RDBMS" does not necessarily imply that it speaks SQL, correct? My company
produces document clustering software that can interact with virtually any
database (via ODBC) that speaks SQL to examine any stored documents and
cluster them. It is extremely annoying to try to convey to a prospective
customer that it works with "virtually any SQL server" without them thinking
that means _just_ Microsoft SQL Server. I agree with guard-of-terra -- we
should not be ceding common/descriptive terms to Microsoft.

~~~
pjmlp
The name comes from Sybase, not Microsoft.

Microsoft SQL Server is an offspring from Sybase SQL Server.

