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

111–120 of 141 posts

Re: PostgreSQL vs. MS SQL Server

#111

Post by CWuestefeld »

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…

As a long-time SQL Server (and C#/.Net) developer, I can say that while SQL Server is a great database engine, TSQL is perhaps the worst language ever invented.

Between the overloading of "WITH" to seemingly every new feature they add, to the way semicolon terminators are only sometimes required (so I just automatically start all CTE declarations as ";WITH" to be sure it works), to the ways they break encapsulation by incorrectly scoping cursor names and limiting INSERT...EXEC so that it can't be nested, programming it at an advanced level is an acquired taste at best.

Re: PostgreSQL vs. MS SQL Server

#112

Post by dragonwriter »

MichaelGG wrote: post #103
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").

It has a feature named materialized views, but it isn't close to being the real feature that MSSQL calls it.

> It has a feature named materialized views, but it isn't close to being the real feature that MSSQL calls it.

MSSQL doesn't have a featured called materialized views, it has a feature called indexed views which happens to implement a fairly robust version of the general DB pattern called materialized view.

PG has less mature materialized views (which are called materialized views) starting from 9.3, with additional features in 9.4, and which can be expected to mature further over at least the 9.5 and 9.6 cycles, from what I've seen various places. Certainly, SQL server is ahead on materialized views, but its not an "SQL server has them, Postgres doesn't" kind of thing.

Re: PostgreSQL vs. MS SQL Server

#113

Post by MichaelGG »

glogla wrote: post #109
MichaelGG wrote: post #107

HA and clustering. MSSQL makes this dead easy. Point and click (and it'll display the script for you to learn/reuse) and you're done. Async, sync, HA (with automatic fail over), mirroring, several types of replication - and it just works and is easy. If PG ever ships with an out-of-the-box shared nothing system, yee haw! Maybe they could package up DRDB and heartbeat into one easy script and monitoring system or some…

> SQL Azure, as I understand, not only does backups, but allows you to restore to arbitrary points in time. It does, but they take about hour to hour. Restore takes about that much time. Changing performance level of DB takes similar time. I'm not sure hot quick Amazon or Google is, but I know lot of ops guys who are sorely disappointed by Azure slowness.

True. Azure is slow slow slow. Even their new portal is just laggy (JS/layout overload). But the service, yikes! Create a new VM, wait minutes. Then find out it failed. Or try to add a port mapping, and wait a minute, unable to do another operation cause one is in progress. It's infuriating. And the VMs take forever to start. And the SSD options are late, under performing and downright janky (they actually tell you to do RAID on the SSDs, since they cannot figure out how to scale storage for you.)

I tried Google Compute Engine on a whim and I'm totally in love (despite a deep distrust of Google). Everything is fast. VMs load in seconds. And it's simple - no inane UI, no crazy leftover bits from being a PaaS. No idiotic design for SSD. And as a kicker, it's half the price for compute. Oh, and SSH client right there in the portal? It's such a small thing but really made me happy.

It's just that Google doesn't do startup outreach and give us cash and court us. Unless you're in an "established" incubator or yc or something. Whereas MS is super friendly and does everything they can.

Re: PostgreSQL vs. MS SQL Server

#114

Post by MichaelGG »

viggity wrote: post #44
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. -…

what I find sad is how much better Sql Server Management Studio is compared to the competition and yet how completely effin' frustrating it is. If They spent 10% as much effort on SSMS as they do on Visual Studio it would be ridiculously amazing.

Yeah, like the Outlook team, someone could learn how to make the UI continue to pump messages, while waiting for remote operations to complete...

Re: PostgreSQL vs. MS SQL Server

#115

Post by glogla »

jackmaney wrote: post #102
BrentOzar wrote: post #2

If I was going to pick a relational database system, I'm not sure these would be the criteria I'd use: CSV support - if you do that much CSV extract/transform/load (or indeed, any kind of ETL work), use an ETL tool. SQL Server comes with SQL Server Integration Services for that kind of thing. Ergonomics of dropping and creating tables and stored procedures - the author's example is probably the toughest way I can thi…

> CSV support - if you do that much CSV extract/transform/load (or indeed, any kind of ETL work), use an ETL tool. Why? CSVs are portable and much easier to deal with (when exported correctly, which PostgreSQL does and MS SQL does NOT ).

Yes. I have seen more than few 100k+ employees companies using CSV for data transfer between their internal systems.

They were usually processed using Oracle external tables though.

Re: PostgreSQL vs. MS SQL Server

#116

Post by MichaelGG »

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.

It's try-able. But tell me how to setup and configure IIS ARR as a reverse proxy without using the GUI.

SQL Server is probably the best case, since the UI is a wrapper around TSQL and they expose the scripts at any point.

Then there's fun stuff. Like Invoke-WebRequest going super slow because it has a terribly implemented progress bar. That's right: out of the box, you can't download files with the shipped downloader, because it shows progress (like curl) and increases transfer times by a couple orders of magnitude. Come on.

Re: PostgreSQL vs. MS SQL Server

#117

Post by mauricemir »

threeseed wrote: post #19
mauricemir wrote: post #10

Earlier quoted context omitted.

So Oracle backport fixes now do they I remember having trouble getting my Oracle based system through y2k back in 98/99

Not sure what you're point is. (a) Not all vendors are the same, (b) Not all situations are the same, (c) Open source isn't exactly world renowned for porting back fixes to older releases. Making broad generalisations is never helpful.

I used to work for a MAJOR MAJOR Oracle customer and they would not back port y2k fixes to relatively recent oracle products ie the version before.

This caused a lot of BT employes a lot of pain you had to get a very senior manager to sign off on missing your y2k deadline which was the year before y2k - and you probably got dinged on your apr for it

Re: PostgreSQL vs. MS SQL Server

#118

Post by thomaskcr »

MichaelGG wrote: post #110
thomaskcr wrote: post #47

Earlier quoted context omitted.

My biggest frustration with MS is their licensing is infuriating. Doing cleanup during the audit took a ridiculous amount of resources and every company gave us a different answer on what needs to be licensed differing by 1000s of dollars sometimes. The other is the barrier to entry. I was trying to get NAV2015 working for a demo using some data for a project, I spent at least 20 hours trying to figure out who to pay…

That's neat though. Shop around for the cheapest reseller, let them make the mistakes or miscalculation, enjoy? I'm guessing it comes down to virtual cores and such, right? Although I'm surprised how many stupid-simple mistakes people make just by not reading even the basics. (Wait MSDN includes several Office keys, so obviously they don't want us to buy Office licenses for administrative assistants.)

See, even you're a bit confused by MSDN -- You're not even supposed to use the MSDN license for your own everyday use is what I've been told. So if you have outlook for email and you're using an MSDN license, that's not correctly licensed. MSDN is ONLY for development. Also it's one MSDN license per developer apparently.

And no because you still get nailed -- it's not like your reseller is the one being audited, it's you.

You're supposed to get CALs for printers accessing MS servers.

Don't ever use an MS server for DHCP, every single client needs a CAL. How am I supposed to even predict that if we allow personal devices on the guest network?

It's beyond frustrating - and the products are great, they just make it impossible to actually ever be in compliance and you need to spend an insane amount of time just handling licenses. It's really the reason I avoid MS as much as possible, and I really love their dev tools (Visual Studio is beyond awesome, I love C#.net) its just too much hassle.

Re: PostgreSQL vs. MS SQL Server

#119

Post by asnyder »

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

EMS SQL Manager for PostgreSQL is much better than SQL Management Studio IMHO. Same goes for their MSSQL tool.

Re: PostgreSQL vs. MS SQL Server

#120

Post by darklajid »

CWuestefeld wrote: post #111
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…

As a long-time SQL Server (and C#/.Net) developer, I can say that while SQL Server is a great database engine, TSQL is perhaps the worst language ever invented. Between the overloading of "WITH" to seemingly every new feature they add, to the way semicolon terminators are only sometimes required (so I just automatically start all CTE declarations as ";WITH" to be sure it works), to the ways they break encapsulation b…

But that's not the point of this subthread. I wouldn't consider myself as a fan of SQL Server (or TSQL for that matter). I am not defending it.

1) I asked about a specific thing in the article. I think the author is wrong.

2) If 1 holds true I think that the article might be questionable - at least I don't trust the rest now. I am no expert on All Things SQL Server and if I spot a flawed point as a random dev in something that was supposedly written by someone working with databases for a decade [1], maybe people that actually know a lot more spot .. more flaws.

1: I'm a dev for longer than that and certainly know my way around sql and databases, but focus matters. The author claims "I know a fair bit about these things" and considers databases his main area of expertise it seems.

Post reply on HN