PostgreSQL vs. SQL Server from the point of view of a data analyst (2014) | Hacker News Reader

PostgreSQL vs. SQL Server from the point of view of a data analyst (2014)

pg-versus-ms.com

87 pointsinsulanian39 comments

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) 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.

Have you tried Valentina Studio? (https://valentina-db.com/en/) It's not SSMS but it's the best I've used for Postgres so far.

I've never heard of it but ill take a look. First blush none of the screenshots show actual sql which would be my first priority - but ill check it out. thanks!

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/

They missed an opportunity to call it PGBartender

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.

You may also like http://explain.depesz.com/

PG Admin 3 was lightyears better than PG Admin 4, for what it's worth. For some reason in 4 they opted to update their UI to be an extremely error prone, buggy, inconsistent experience.

For basic querying I use SQLTabs, for DBA-type stuff I use PG Admin 3.

- Triggers

In PG triggers are row by row. In mssql it's one batch where you can access the inserted/deleted records.

false. in PG triggers are either for each row OR for each statement.

Not false, if you need to access the rows which changed you need to use a per-row trigger in Postgres. There is work being done to support seeing all the affected rows in a per-statement trigger like you can in Sql Server, but it's not committed yet. See: https://www.postgresql.org/message-id/flat/20140616195900.GC...

in PG (such as using an ORDER BY)

You can use order by in a CTE in mssql, you just have to specify a TOP clause.

  ;with bar as (
    select top 100 percent *
    from foo
    order by foo.name
  )
  select *
  from bar

interesting, im pretty certain I couldnt do this with the version I was using at the time, but maybe I just didnt have the right google fu - I wonder what version that came with?

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.

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.

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.

>>> 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.

Postgre wins for people who have no money.

SQL Servers wins for people who have money AND are somewhat invested in the Microsoft ecosystem.

By the way the article is from 2014 and states that he's been doing that for a decade.

An article on the capabilities of postgre (or lack thereof) in 2004 and 2009, that'd be fun.

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.

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.

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.

Previous discussions:

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

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

>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.

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.

Stored procedures in postgres: https://www.postgresql.org/docs/9.6/static/plpgsql-declarati...

> 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.

Any recommendations for a really good PostgresSQL tutorial?

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.

Thanks, that looks great!

Thanks!

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.

> How can I know that this isn't some old, irrelevant comparison from 2001?

The Wayback Machine.

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

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.

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

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.

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).

It's easy to misread MS SQL in the title as MySQL... The article isn't about MySQL.

Wordpress probably accounts for most of Mysql's popularity.

I have been using MySQL for a number of years. I will probably use Postgres for any new projects I start. But my current work is maintaining a system written by someone else, so MySQL it is. Its not that bad a database, but there are enough quirks to it that I feel that PG would be a better choice.

Reply on news.ycombinator.com