PostgreSQL vs. MS SQL Server • Hacker News Board

Re: PostgreSQL vs. MS SQL Server

#31

Post by breakingcups » Thu, Apr 30, 2015, 1:28 PM UTC

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.

Re: PostgreSQL vs. MS SQL Server

#32

Post by atwebb » Thu, Apr 30, 2015, 1:30 PM UTC

lassoman wrote: post #22

stevepike wrote: post #16

Earlier quoted context omitted.

Can you drop a recommendation for your favorite startup-friendly ETL tool?

Pentaho if you have no or little money or SSIS (comes with SQL Server licence)

SSIS is the most frustrating, worthwhile bundled app I've seen. It's gotten significantly better but for what is essentially a giant XML writer they sure do like to hide all the options and configurations. I love the API though, so handy.

Re: PostgreSQL vs. MS SQL Server

#33

Post by alexggordon » Thu, Apr 30, 2015, 1:31 PM UTC

This website is great. However, this doesn't actually touch on the real issue.

I work at a MSSQL shop, and all of us know and are convinced that PG is better. Most of us use PG for our side projects and some of the dev's don't even use windows that much, with some custom MSSQL plugins we've built for linux. However, the problem still exists, of how do you port a ton of Databases over to Postgres? We're a multi-tenancy shop, so close to zero downtime is very important, and it would get really complicated if we ran multiple production versions of our app, one with a PG adapter and one with a MSSQL adapter.

A cursory Google search will show that you aren't going to get a ton of help converting them[0][1], not to mention the overhead of switching 20 developers from MSSQL to PG overnight.

This website is however excellent at convincing people to use PG over MSSQL. Perhaps, given the direction that Microsoft is going, they'll open source MSSQL overnight and it will become something competitively similar to PG in the long run.

[0] http://www.convert-in.com/mss2pgs.htm

[1] https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_Pos...

Re: PostgreSQL vs. MS SQL Server

#34

Post by tehaugmenter » Thu, Apr 30, 2015, 1:32 PM UTC

BrentOzar wrote: post #27

tehaugmenter wrote: post #24

Earlier quoted context omitted.

I mean you have a point on the OS, being in the cloud is a definite bonus. However caring what OS it's running on comes down to something he was brief about in his article. For me, I can manage an Linux server no issue with just a bash prompt, I can't honestly say the same about a Windows Server. Other people might argue Apache and running that on a Windows server is kind of pointless? I don't know, just spit-balling…

> For me, I can manage an Linux server no issue with just a bash prompt, I can't honestly say the same about a Windows Server. Have you seen Windows Core and PowerShell? An increasing number of admins are doing just that - it's how we work at Stack Overflow, for example.

I've used PowerShell quite a bit actually, even wrote a few script a couple years ago to work with Active Directory. I still can't see myself managing a server from there. I know configurations and everything are possible through it, like the registry and such. However config files are just so much more friendly to me. Which is probably why I would pick Linux on that. Permissions are also a little bit more basic and harder to screw up in Linux IMO.

Re: PostgreSQL vs. MS SQL Server

#35

Post by moron4hire » Thu, Apr 30, 2015, 1:33 PM UTC

>> PostgreSQL supports DROP SCHEMA CASCADE, which drops a schema and all the database objects inside it. This is very, very important for a robust analytics delivery methodology, where tear-down-and-rebuild is the underlying principle of repeatable, auditable, collaborative analytics work.

Actually, if you drop the constraints first, you don't have to worry about the table-drop order. I do that on both Postgres and SQL Server, because I use a tool that generates the specific scripts for me.

I don't think manually writing DDL scripts is any way to manage an RDBMS in the modern era, especially if you care about "repeatability and auditability" like he claims.

Queries, yes, but schema constructs, no. SQL is just a terrible language for it. I'm not saying you have to use a full ORM, but every SQL engine I've ever encountered will let you do a lot of nutty things and won't complain about it, or won't complain until you actually try to query it.

Re: PostgreSQL vs. MS SQL Server

#36

Post by atwebb » Thu, Apr 30, 2015, 1:34 PM UTC

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…

Yes, you can absolutely do that in MSSQL as long as there's a batch separator (it's not implicit at a line break,;, or something else. It's not uncommon to write large deployment scripts with proc creations/alters.

There's some other things like that in the post, some coming down to what seems like syntactic sugar and feels more like a difference that doesn't really need to be noted (like the types/dropping piece).

Re: PostgreSQL vs. MS SQL Server

#37

Post by atwebb » Thu, Apr 30, 2015, 1:39 PM UTC

silon3 wrote: post #9

ben_pr wrote: post #3

After seeing the title I assumed the verdict would be that MS SQL is way better. If I wrote a comparison that's how the result would end up. I've used MS SQL from 1998 and PostgreSQL from 2001 and have found MS SQL much easier to use. Different strokes for different folks I guess.

I use MSSQL on a current project and really don't like it's query optimizer. It relies too much on statistics which means that slightly complex queries get random behavior really quickly, especially in the beginning as the database if growing. IMO, completely unsuitable for web apps where DBA is not sitting around 24/7. Is PostgreSQL better at this?

How would you suggest optimizing queries except on stats? Do you find instances where the stats are incorrect? If you're trying to do small things in a big table, have you used indexing to help target the small sets/fields you're looking at?

Re: PostgreSQL vs. MS SQL Server

#38

Post by lassoman » Thu, Apr 30, 2015, 1:40 PM UTC

atwebb wrote: post #32

lassoman wrote: post #22

Earlier quoted context omitted.

Pentaho if you have no or little money or SSIS (comes with SQL Server licence)

SSIS is the most frustrating, worthwhile bundled app I've seen. It's gotten significantly better but for what is essentially a giant XML writer they sure do like to hide all the options and configurations. I love the API though, so handy.

I don't think so. I used it since it was DTS, not SSIS and whilst Informatica is better at all things ETL, SSIS is great for what it can do out of the box. Besides, if it can't, you can always do it via custom C#or VB extension.

Just finished a multi-terabyte data warehouse project where all data is loaded using SSIS and ControlM and it works great! The dev process was also easy and trouble-free.

Re: PostgreSQL vs. MS SQL Server

#39

Post by lassoman » Thu, Apr 30, 2015, 1:42 PM UTC

stevepike wrote: post #28

lassoman wrote: post #22

Earlier quoted context omitted.

Pentaho if you have no or little money or SSIS (comes with SQL Server licence)

I tried Pentaho, but our ETL tasks are being written by engineers (at least right now), and it seemed much clunkier than just writing ruby scripts. Is it worth it in the long run?

If you just need to move data from A 2 B than no. If your transformations are complex and will grow overtime than yes, the time and learning curve are justified IMO.

Re: PostgreSQL vs. MS SQL Server

#40

Post by kidmenot » Thu, Apr 30, 2015, 1:43 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.

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.