Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL vs. MS SQL Server

pg-versus-ms.com

Post reply on HN
Print view View original

71–80 of 141 posts

Re: PostgreSQL vs. MS SQL Server

#71

Post by [deleted] »

ssmoot wrote: post #62
adekmm wrote: post #12

I've been SQL Server user for many years. And for last two years I started also using PostgreSQL. Today I use both of them in my project, and as a developer/dba I see pros and cons: MS SQL: - The tools included like Management Studio are just great. This is totally next level to the Postgres tools. - Using multiple CPU cores for a single query is really helpfull in my scenario. - Easy continous backup to the cloud. -…

The write performance comes down to lack of clustered indexes on PostgreSQL. So yeah, it'll have better INSERT performance, but the queries will be slower. There's really no way around that. A large data set on disk that's out of order will always be slower than the one that's in-order. IMO MSSQL makes the right call for the vast majority of use-cases. PostgreSQL has -no- materialized views (I stand corrected! Introd…

[deleted]

Re: PostgreSQL vs. MS SQL Server

#72

Post by mmgutz »

kidmenot wrote: post #40
MagicWishMonkey wrote: post #20

Earlier quoted context omitted.

The closest thing to SQL Server Management Studio I've found is Navicat. It's not as solid as Management Studio, and it's incredibly expensive, but it is much better than most of the other tools out there. If you can expense the cost of the license you should definitely check it out.

Agreed on Navicat. pgAdmin doesn't quite cut it just yet. Which sometimes made me wonder if, with Postgres becoming more popular by the day, there would be a market for a management tool cheaper than Navicat.

Last I tried Navicat it didn't offer much over pgAdmin. The UI was more streamlined but that wasn't reason enough to switch to it. What features in navicat are must have and not found in pgAdmin3?

Re: PostgreSQL vs. MS SQL Server

#73

Post by keithwarren »

coldtea wrote: post #54
breakingcups wrote: post #31

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 a…

> 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. So there was not any substancial critique and responce to the specific points he makes?

There was ample breakdown of the article and lots of credible resources pointing towards places he was wrong.

Re: PostgreSQL vs. MS SQL Server

#74

Post by Jweb_Guru »

ssmoot wrote: post #68
Jweb_Guru wrote: post #56

Earlier quoted context omitted.

Postgres absolutely has materialized views (I don't know what makes a backup / restore procedure "sane", but having had to do it for both SQL Server and Postgres I would definitely call Postgres's "saner").

re: Materialized Views: I stand corrected! Thanks. (CREATE MATERIALIZED VIEW was introduced in v9.3) The backup/restore procedure in PostgreSQL (last I looked, latest I've used is 9.2) is just statement generation. It's not a binary backup. So it's bound by INSERT performance. Which is rage inducing when you have even just tens of millions of rows. MSSQL'97 could do a backup/restore in a small fraction of the time. A…

Have you looked at http://www.postgresql.org/docs/9.4/static/app-pgbasebackup.h...?

Re: PostgreSQL vs. MS SQL Server

#75

Post by salibhai »

Amezarak wrote: post #8

> In MS SQL Server, a CREATE PROCEDURE statement cannot appear halfway through a batch of SQL statements. There's no good reason for this, it's just an arbitrary limitation. It means that extra manual steps are often required to execute a large batch of SQL. Manual steps increase risk and reduce efficiency. It's been a while, but I am pretty sure all you have to do is put GO before/after the CREATE PROCEDURE. I'm abs…

GO creates a lot of headaches actually. If you are trying to do something in a transaction it breaks it

Re: PostgreSQL vs. MS SQL Server

#76

Post by greggyb »

It took a while, but he got there (emphasis mine):

>This is an advantage for MS SQL Server whenever you're running a query which is CPU-bound and not IO-bound. In real-life data analytics this happens approximately once every three blue moons. On those very rare, very specific occasions when CPU power is truly the bottleneck, you almost certainly should be using something other than an RDBMS. RDBMSes are not for number crunching.

As a data analyst, the tools to be comparing shouldn't be RDBMSs.

>As I said in the banner and the intro, I am comparing these databases from the point of view of a data analyst, because I'm a data analyst and I use them for data analysis. I know about SSRS, SSAS, in-memory column stores and so on, but I haven't mentioned them because I don't use them (or equivalent features). Yes, this means this is not a comprehensive comparison of the two databases, and I never said it would be. It also means that if you care mostly about OLTP or data warehousing, you might not find this document very helpful.

As for this part, data warehousing, OLAP services, and reporting services (lower case on purpose here) are a very large sub-domain within data analytics. I am not saying that these are everything in analytics, but especially from an enterprise standpoint, these make up the bulk of it. From a tooling and full-stack standpoint, Microsoft is quite strong in this segment.

Re: PostgreSQL vs. MS SQL Server

#77

Post by Aushin »

darklajid wrote: post #55

Can someone explain this one to me? PostgreSQL supports the RETURNING clause, allowing UPDATE, INSERT and DELETE statements to return values from affected rows. This is elegant and useful. MS SQL Server has the OUTPUT clause, which requires a separate table variable definition to function. This is clunky and inconvenient and forces a programmer to create and maintain unnecessary boilerplate code. So I have the equiva…

Honestly I don't know where he got the idea, but as someone who worked for five years as a SQL Server database admin/developer, I for some reason also thought you could only do OUTPUT Inserted.field1, ... INTO @TableVar

But I just tested what you claimed here and it definitely works exactly the way the RETURNING clause would.

Weird.

Re: PostgreSQL vs. MS SQL Server

#78

Post by hahainternet »

ssmoot wrote: post #62
adekmm wrote: post #12

I've been SQL Server user for many years. And for last two years I started also using PostgreSQL. Today I use both of them in my project, and as a developer/dba I see pros and cons: MS SQL: - The tools included like Management Studio are just great. This is totally next level to the Postgres tools. - Using multiple CPU cores for a single query is really helpfull in my scenario. - Easy continous backup to the cloud. -…

The write performance comes down to lack of clustered indexes on PostgreSQL. So yeah, it'll have better INSERT performance, but the queries will be slower. There's really no way around that. A large data set on disk that's out of order will always be slower than the one that's in-order. IMO MSSQL makes the right call for the vast majority of use-cases. PostgreSQL has -no- materialized views (I stand corrected! Introd…

> No view update support

9.3

> No partitioned view support

Inheritance (ish)

> No sane backup/restore process

wat

> doesn't support backing up and restoring the database in binary format

http://www.postgresql.org/docs/9.4/static/app-pgbasebackup.h... is half of what you want, but I expect you're wanting something I don't expect.

Re: PostgreSQL vs. MS SQL Server

#79

Post by ssmoot »

Jweb_Guru wrote: post #74
ssmoot wrote: post #68

Earlier quoted context omitted.

re: Materialized Views: I stand corrected! Thanks. (CREATE MATERIALIZED VIEW was introduced in v9.3) The backup/restore procedure in PostgreSQL (last I looked, latest I've used is 9.2) is just statement generation. It's not a binary backup. So it's bound by INSERT performance. Which is rage inducing when you have even just tens of millions of rows. MSSQL'97 could do a backup/restore in a small fraction of the time. A…

Have you looked at http://www.postgresql.org/docs/9.4/static/app-pgbasebackup.h... ?

That's really just for setting up replication IME (and even then, that's pretty complex compared to MSSQL).

What if I fat finger a table drop for Client A? With pgbasebackup the best I could do is set up an entirely new server and jump through some hoops. With MSSQL I could just restore the single client database to a new database, very quickly, and SELECT INTO the missing data from clientdb_a_backup to clientdb_a.

It's just a lot simpler, a lot quicker, and a lot more flexible.

Re: PostgreSQL vs. MS SQL Server

#80

Post by dragonwriter »

ssmoot wrote: post #62
adekmm wrote: post #12

I've been SQL Server user for many years. And for last two years I started also using PostgreSQL. Today I use both of them in my project, and as a developer/dba I see pros and cons: MS SQL: - The tools included like Management Studio are just great. This is totally next level to the Postgres tools. - Using multiple CPU cores for a single query is really helpfull in my scenario. - Easy continous backup to the cloud. -…

The write performance comes down to lack of clustered indexes on PostgreSQL. So yeah, it'll have better INSERT performance, but the queries will be slower. There's really no way around that. A large data set on disk that's out of order will always be slower than the one that's in-order. IMO MSSQL makes the right call for the vast majority of use-cases. PostgreSQL has -no- materialized views (I stand corrected! Introd…

> PostgreSQL has -no- materialized views (I stand corrected! Introduced in v9.3). No view update support.

Like materialized views, automatic update support for simple views was introduced in 9.3.

Post reply on HN