PostgreSQL vs. MS SQL Server • Hacker News Board

Re: PostgreSQL vs. MS SQL Server

#91

Post by feld » Thu, Apr 30, 2015, 3:00 PM UTC

feld wrote: post #48

S_A_P wrote: post #45

I expect better from the front page of HN. I like PostgreSQL and MSSQL Server and to a much lesser degree mySql and Oracle. But in my experience I use the tool that exists and makes sense for the project. Since most of my work is enterprise its MSSQL or Oracle. For personal projects I used PostgreSQL and got on quite well. None of the tools prevented me from getting the data I needed. There may be scaling issues or u…

It's very click-baity, but to provide some anecdotal evidence I've never had corrupted data/tables in Postgres, but dealt with it very regularly in MySQL under almost identical environments. MySQL doesn't do a great job of protecting your data compared to Postgres, MSSQL, Oracle, etc.

Go ahead, kill -9 your production MySQL servers. I dare you.

I can do that to Postgres all day and the on-disk data integrity is preserved.

Re: PostgreSQL vs. MS SQL Server

#92

Post by edgarvm » Thu, Apr 30, 2015, 3:02 PM UTC

I was expecting something more convincing like benchmarks, PostgreSQL has a lot of nice features and that's is a good thing for development, but just for development.

Re: PostgreSQL vs. MS SQL Server

#93

Post by dorfsmay » Thu, Apr 30, 2015, 3:04 PM UTC

Someone1234 wrote: post #66

dorfsmay wrote: post #57

Earlier quoted context omitted.

And lack of publication date, what was true in 2010 might not be true in 2015. How do we know which versions he is comparing? Both PostgreSQL and MS SQL make very significant progress every year.

Actually he says in the first line of the third paragraph: > Unless otherwise stated I am referring to PostgreSQL 9.3 and MS SQL Server 2014 Which makes his opinions timeless and gives context. A date on it would be welcome, but just from the software version you can figure out if it applies to you or your scenario.

Thanks, you're right.

I read this months ago, so just went over it very quickly this time, and the first thing I did was to look for a date at the top and bottom.

Re: PostgreSQL vs. MS SQL Server

#94

Post by [deleted] » Thu, Apr 30, 2015, 3:07 PM UTC

swasheck wrote: post #86

salibhai wrote: post #75

Earlier quoted context omitted.

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

why would you submit and terminate a batch halfway through a transaction? that doesnt really make a whole lot of sense so i cant really take this criticism seriously.

[deleted]

Re: PostgreSQL vs. MS SQL Server

#95

Post by andrewl » Thu, Apr 30, 2015, 3:09 PM UTC

jmnicolas wrote: post #30

Postgres is better than SQL Server because with Postgres I always have the "awesome edition" : http://blog.codinghorror.com/oh-you-wanted-awesome-edition/ And I don't need a certification to understand the crazy MS licensing (I discovered recently that yes there is a MS certification program for their licenses as it's so byzantine).

I remember Jeff Atwood talking about this in a Stackoverflow podcast. With Postgres or other free software, you not only don't have to lay out cash, you also don't have to spend time trying to understand arcane licensing rules. (And worrying that you might have violated one of them.) Whether the time (and time is money) saved balances out the cost of the software when the commercial product offers better functionality will vary with the situation.

For what it's worth, Atwood chose Postgres for his Discourse product. The FAQ says the product is "Uncompromisingly open source." And "There is only one version of Discourse--the awesome version. There's no super secret special paid commercial version with better or more complete features."

Re: PostgreSQL vs. MS SQL Server

#96

Post by chris_wot » Thu, Apr 30, 2015, 3:12 PM UTC

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…

No clustered index? What about CLUSTER?

http://www.postgresql.org/docs/9.4/static/sql-cluster.html

CLUSTER instructs PostgreSQL to cluster the table specified by table_name based on the index specified by index_name. The index must already have been defined on table_name.

When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order. (If one wishes, one can periodically recluster by issuing the command again. Also, setting the table's FILLFACTOR storage parameter to less than 100% can aid in preserving cluster ordering during updates, since updated rows are kept on the same page if enough space is available there.)

When a table is clustered, PostgreSQL remembers which index it was clustered by. The form CLUSTER table_name reclusters the table using the same index as before. You can also use the CLUSTER or SET WITHOUT CLUSTER forms of ALTER TABLE to set the index to be used for future cluster operations, or to clear any previous setting.

Re: PostgreSQL vs. MS SQL Server

#97

Post by loudmax » Thu, Apr 30, 2015, 3:12 PM UTC

This is the second time this site has appeared on HN, and both times I've mis-read the title as "PostgreSQL vs MySQL", which I think would be a much more interesting comparison. If you're already working in an MS shop, then MS SQL will work best with what you have. If you're working in an open source environment, then you'll be better off taking advantage of what Postgres or MySQL have to offer. Postgres and MySQL are so different in terms of their features that comparing them would make an interesting article.

Re: PostgreSQL vs. MS SQL Server

#98

Post by ryanackley » Thu, Apr 30, 2015, 3:15 PM UTC

When developing on a Microsoft stack with Visual Studio, I have always found SQL Server to be the most painless DB system. You can't beat the Visual Studio integration and tools. Also, their SQL Server drivers for .NET do a bunch of transparent optimizations like automatic connection pooling behind the scenes so you don't have to worry about this kind of thing.

OTOH, when not on a Microsoft stack, it has been a constant source of hard to track down problems and bugs. I remember trying to use Microsoft's JDBC driver a few years ago in a Java web app and running into all kinds of nasty and unbelievable bugs in the actual driver they shipped.

Re: PostgreSQL vs. MS SQL Server

#99

Post by kbenson » Thu, Apr 30, 2015, 3:17 PM UTC

feld wrote: post #91

feld wrote: post #48

Earlier quoted context omitted.

It's very click-baity, but to provide some anecdotal evidence I've never had corrupted data/tables in Postgres, but dealt with it very regularly in MySQL under almost identical environments. MySQL doesn't do a great job of protecting your data compared to Postgres, MSSQL, Oracle, etc.

Go ahead, kill -9 your production MySQL servers. I dare you. I can do that to Postgres all day and the on-disk data integrity is preserved.

It makes little sense to put it like that, since for MySQL it greatly depends on the storage engine used. I've found InnoDB to be quite robust, but I'm not sure how it compares to what Postgres uses.

Re: PostgreSQL vs. MS SQL Server

#100

Post by dphase » Thu, Apr 30, 2015, 3:22 PM UTC

MagicWishMonkey wrote: post #20

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

I've been a fan of Aqua Data Studio for years and it doesn't seem to get mentioned often. Cross platform and supports a myriad of dbs. http://www.aquafold.com/aquadatastudio.html