
PostgreSQL vs. SQL Server from the point of view of a data analyst (2014) - insulanian
https://www.pg-versus-ms.com/
======
netcraft
My previous job(s) were primarily MSSQL and I was excited to get to use PG in
my current position. In general I agree with the premise of the article - for
just about everything PG is better. For me, the only places where MSSQL has an
edge is:

\- Management Studio is superior to any standard query manager ive found for
PG. I currently use what is in intellij (which is similar to their datagrip
product) which I find perfectly acceptable and in some ways superior to
Management Studio, but PGAdmin isn't in the same ballpark.

\- MSSQL visual query explainer is great - ive found some alternatives
([http://tatiyants.com/pev/#/plans](http://tatiyants.com/pev/#/plans)) and in
general ive gotten much better at reading and understanding the text output of
PG's explain, but I do miss MSSQL in this regard.

\- MSSQL CTE's not being optimization fences meant that I got to use them in
more places. But I can do more things with CTE's in PG (such as using an ORDER
BY) - this is relatively minor but annoying - readability with CTE's vs
subqueries is much improved.

\- Snapshot and transaction logs with MSSQL is much better than any
alternative ive found with PG, but tbf I haven't looked into it very far. When
it comes up though it would be really nice to be able to restore to point in
time easily with PG.

\- I do occassionally come across missing functions in PG, but thats getting
more and more rare.

\- Cloud offerings - they exist for PG and are great for what they offer, but
many extensions are not available or not fully configurable which is also
sometimes hard to even determine.

All that said - if you aren't already invested in the MS ecosystem, PG would
be my preference for sure. JSON/hstore/arrays alone make so many things
possible even if you arent using those datatypes in your tables.

~~~
zie
pgbarman [0] will handle the restore to point in time use case above. There
are other solutions as well, but I've found pgbarman to work great, and can't
speak for others.

[0] [http://www.pgbarman.org/](http://www.pgbarman.org/)

~~~
snuxoll
Barman is awesome, have been using it in production for two years now - I
cannot reccomend it enough. Funny enough, I found it easier to work with than
trying to set up a maintenance plan with SSMS and restores are dead-easy too.

------
WesBrownSQL
This is a completely anecdotal analysis. Every time this comes up it makes me
cringe. Yes, I use MSSQL. Yes, I really like MSSQL. I know loads more about
MSSQL than I do PostgreSQL and that plays a huge part in it. Like this person
I am qualified to tell you how totally awesome MSSQL is. Also, like this
person I AM NOT qualified to compare it to PostgreSQL even though I do use PG
too. This isn't a problem of bias, this is a lack of knowledge on the part of
the author. I don't know how to do X in MSSQL so PostgreSQL is clearly better.
What needs to happen is experts on both sides sit down and do a true
comparative analysis and then the reader can decide which product suits their
needs best.

~~~
teh_klev
Thanks, saved me the bother of saying this as well.

That section on "Reliability" is pure comedy, the only time I had SQL Server
"crash" was due to faulty hardware. And despite the best efforts of our
previous data centre company (who we've since ditched) dropping the ball and
losing power across the site multiple times over two years our MS SQL servers
never lost any data, despite the rug being pulled whilst under some fairly
heavy workloads. I should add that neither did any of the MySQL fleet, even
the ones running replication.

One day when I get time I'll get around disembollocking this flawed article. I
don't say that as a MS SQL "fanboy", but as a DBA with coming on for 20 years
experience managing and programming SQL Server in banks, blue chips and ISP's
(yeah I know, "appeal to authority"-fail, but who the hell is this anonymous
author, and what are his credentials?).

Edit: sorry I should add I quite like PostgreSQL, and I'm hoping to roll it
out as a service offering to our client base in the next few months, so no axe
to grind from me with regards to its features and capabilities.

~~~
j_s
My experience: SQL Server kicks its NAS off the network due to the NAS's own
flakiness, but SQL Server has always recovered fine. I've had to boot it in
single-user mode many times to cut the thundering herd down to size on
startup/recovery, but then it's back to business.

------
breakingcups
I'll repeat my comment from the last time this was posted:

The last time this article came up the consensus was that the author was
pretty biased towards Postgres and had little to no experience with actual MS
SQL Server use. Also, the lack of author identity was frowned upon. Lastly,
the conjecture and attitude towards Microsoft lacks some substance.
Conclusion: The author is free to write whatever he likes, but take this
resource with a pinch of salt. I use both Postgres and MS SQL Server
professionally and whilst philosophically I prefer Postgres, for practical
reasons I truly prefer MS SQL Server, if only because of its excellent
development tools.

~~~
gaius
What - other than fanaticism - would motivate an individual to register the
domain name etc to put this up?

I've been doing databases professionally for 20+ years. I've used all of the
big names apart from DB2, just never encountered it. Whether it's SQLite, PG,
MariaDB, SQL Server, Sybase, Informix, Oracle, there's a right tool for the
job.

Also a "data analyst" who never encounters a situation where parallel query
will help is a person who works on very small datasets on a single disk or
maybe a small RAID5 array.

------
dhd415
The fact that the author just dismisses columnar indexes because he doesn't
use them leads me to question his credibility as a serious critic of tools for
typical analytics workloads since columnar indexes make a huge difference in
performance on many of those workloads. SQL Server has them built in and
they're available as extensions for PostgreSQL.

------
okket
Previous discussions:

[https://news.ycombinator.com/item?id=9464505](https://news.ycombinator.com/item?id=9464505)
(2 years ago, 135 comments, "dupe")

[https://news.ycombinator.com/item?id=8615320](https://news.ycombinator.com/item?id=8615320)
(2.3 years ago, 103 comments)

------
iblaine
>But I have MS SQL Server skills, not PostgreSQL skills!

>You'd rather stick with a clumsy, awkward, unreliable system than spend the
trivial amount of effort it takes to learn a slightly different dialect of a
straightforward querying language? Well, just hope you never end up in a job
interview with me.

I imagine anyone who easily dismisses MSSQL would be an easy interview. A good
engineer will recognize the pros and cons of each database and make their
decision based on their requirements. To dismiss MSSQL outright is pretty
noobish, even for a lowly data analyst.

------
juliangoldsmith
I'm a fan of Postgres, but I have to take issue with point 1.4. Last I knew,
Postgres had no support for stored procedures, which makes integration with
procedural languages almost useless.

~~~
dragonwriter
> Last I knew, Postgres had no support for stored procedures, which makes
> integration with procedural languages almost useless.

So, going through old docs, Postgres seems to have had stored procs using
procedural languages since at least version 7.1, released in April 2001. It
clearly has had them for quite some time, at any rate.

------
jpster
Any recommendations for a really good PostgresSQL tutorial?

~~~
rallycarre
[https://pgexercises.com/](https://pgexercises.com/)

I've found it one of the best tutorials of anything on the web. It's
challenging and you learn through example.

~~~
nallerooth
Thanks, that looks great!

------
sankyo
I cannot stand it when a post is not dated. How can I know that this isn't
some old, irrelevant comparison from 2001? It doesn't take much effort to add
a posting date.

~~~
anshou-
There doesn't appear to be any date on the site to indicate when it was
written or updated.

------
frik
MSSQL's SQL syntax like that found in MSSQL 2012 is very outdated. Many old
rusty parts of MSSQL date back to the Sybase, MSSQL is a fork of it. Also
WinNT (incl Win10), MSSQL and most other MSFT software is UCS-2 which they
often mislabel as UTF-16. UCS-2 cannot manage emoticons and other newer
unicode characters. Whereas everyone on the planet decided on UTF-8. So
everything OP mentioned in the article is true.

------
krystiangw
Base on the stats below seems that Mysql is still more popular than
PostgreSQL.

# Name | Popularity | Avg salary global

1\. Mysql | 7.44% | $56,506

2\. PostgreSQL | 4.32% | $61,505

3\. MongoDB | 3.2% | $64,206

4\. Sql Server | 2.71% | $65,959

5\. Oracle | 2.09% | $52,312

6\. Redis | 1.92% | $51,728

More stats and details here
[https://jobsquery.it/stats/databases/group](https://jobsquery.it/stats/databases/group)

~~~
netcraft
not sure if it was intended, but the article is discussing MSSQL not MYSQL. I
think MYSQL has a popularity advantage over PGSQL because of a long tail of
historical reasons, but this is just my opinion.

~~~
collyw
Its a bit like PHP, its easy to install and get started compared to PG (I need
to look up the docs every time I do a PG install to get the initial users
started - Mysql often offers me that from the OS package manager).

