8 ms·

PostgreSQL vs. MS SQL Server

145 points by vince_refiti 11y ago

BrentOzar 11y ago
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 think of to drop a table. It's easier to check sys.all_objects (which will catch anything - functions, views, procs, etc). Operating system choice - well, in 2015, if you're going to mention that, you should be thinking cloud services anyway. They're both available in the cloud - and at which point, who cares what OS it runs on? Goes on and on. I'm a SQL Server guy, and if I was going to make a list of how to choose a relational database platform, here's what I'd probably list: * Up-front cost (license, maintenance) * Ease of finding staff, and their costs * Ease of finding tooling, and its cost * Ease of finding scalable patterns (proven ways to grow it) I don't think either platform is a loser on those counts - it's hard to go wrong with either one.
- lassoman 11y ago
  Fully agree! This article is based on merits that I would hardly consider if making a choice between any relational engine. It is even more disturbing that the author claims to be a data analyst. CSV support.......R U serious? Who in the right mind would spend hundreds of thousands of $$$ for SQL Server Enterprise Edition licences and regret it because PostreSQL can do it better for free? I'm using both and they're both great but not for or in spite of the reasons outlined here. Author needs to educate herself/himself better before publishing this nonsense.
- stevepike 11y ago
  Can you drop a recommendation for your favorite startup-friendly ETL tool?
  - lassoman 11y ago
    Pentaho if you have no or little money or SSIS (comes with SQL Server licence)
    - stevepike 11y ago
      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?
      - lassoman 11y ago
        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.
    - atwebb 11y ago
      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.
      - lassoman 11y ago
        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.
        atwebb 11y ago
        I can't tell if I know you now... Anyways, yes, it's very powerful and an excellent tool but I always have to describe it as awfulsome. SSISDB has made great improvements to the setup and promotion of objects which previously could be a pretty big headache (especially when you're not the one deploying etc). Functions for the various tasks/settings can be hidden and it can take a while to convince newcomers that the options are available, you just have to...poke around a bit. It comes with a substantial amount of controls. The metadata/conversion of data can be frustrating but automation greater improves this with a quick controlling framework and the sys tables, it also makes moving data across hundreds of tables a breeze. It is very fast and very stable and I'm glad it's getting improvements and having focused development.
- tehaugmenter 11y ago
  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 that one. Obviously Windows would be IIS, which means overall the OS choice is a nice one. The fact that you can choose which one to host with, which means you get something comfortable.
  - BrentOzar 11y ago
    > 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.
    - tehaugmenter 11y ago
      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.
    - mistermann 11y ago
      Oh, do you work for SO Brent?
      - BrentOzar 11y ago
        > Oh, do you work for SO Brent? I just do SQL Server consulting for 'em from time to time. They're ridiculously sharp guys, so they don't need me much anymore.
    - 11y ago
- jackmaney 11y ago
  > 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).
  - glogla 11y ago
    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.
ben_pr 11y ago
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.
- silon3 11y ago
  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?
  - anarazel 11y ago
    Not really. Postgres' planner is also statistics based. Yes that approach has problems, but the contrary, where you still use a plan that was appropriate for three rows with a couple million isn't good either. It's just a hard problem.
  - atwebb 11y ago
    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?
- ExpiredLink 11y ago
  It's a one-page propaganda site (from someone from Shepperton, GB). I'm a big fan of PostgreSQL but this isn't helpful.
netcraft 11y ago
Previous discussions: https://news.ycombinator.com/item?id=8615320 https://news.ycombinator.com/item?id=8615320
CSDude 11y ago
Does anybody use PG/PLSQL? I wonder how it compares in practice with Oracle PLSQL.
- mauricemir 11y ago
  Its quiet close I recall being impressed that i could reuse some Oracle Pl/SQL on Postgress
  - jmulho 11y ago
    If you know PL/SQL, all you need is an example of the boilerplate that goes at the top and bottom of a PL/pgSQL procedure and you are good to go.
- cgh 11y ago
  As others have mentioned, they are similar but there are some gotchas. An example is hierarchical data: pgsql lacks start with/connect by. This means you have to write recursive queries using Common Table Expressions (with...as). Not a dealbreaker but it does mean some extra work.
threeseed 11y ago
> Commercial products have support from people who support it because they are paid to. They do the minimum amount necessary to satisfy the terms of the SLA. This is pretty ridiculous and quite a bit insulting to the many people who do work for vendors. I work in a team that has a number of engineers supplied by vendors and they are generally fantastic. Highly qualified, more than happy to assist with tasks that aren't to do with their product and they really care about the overall project outcomes. Open source has forced vendors to make sure that every project that uses their products are a success. > On the other hand, commercial software is often designed by committee, written in cube farms and developed without proper guidance or inspiration Again more nonsense. Not every open source project is some beacon of perfection and neither is every commercial product some poorly designed piece of junk. Anyone that believes otherwise is just being disingenuous. Someone really needs to explain to me why PostgreSQL users in particular seem to always want to bash the competition in order to justify their technology choice. It's been going on for years against MySQL/Oracle first, then MongoDB/NoSQL and now SQL Server. It's odd.
- mgkimsal 11y ago
  Agreed. One could just as easily write something like "Open source products have support from people who've been told they need to 'contribute' to open source, and since they can't code, they just try to look at code and answer questions on a mailing list until they get an interview at Facebook".
- mauricemir 11y ago
  So Oracle backport fixes now do they I remember having trouble getting my Oracle based system through y2k back in 98/99
  - threeseed 11y ago
    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.
Amezarak 11y ago
> 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 absolutely positive there's some way around it, because I've run many, many such scripts on SQL Server without manual intervention. EDIT: Yes, I just fired up a VM and ran this and got the expected results with no errors. CREATE DATABASE HackerNews; CREATE TABLE dbo.Test (id int IDENTITY(1, 1), name varchar(20)); GO INSERT INTO dbo.Test (name) VALUES ('Amezarak'); GO CREATE PROCEDURE dbo.sp_QueryTest AS SELECT * FROM dbo.Test; GO EXEC dbo.sp_QueryTest In my personal opinion, MSSQL (including the tooling around it) is awesome and possibly one of Microsoft's best products. I actually regret not getting to use it anymore since a) my current job doesn't use it and b) I'm not shelling out for a license for my side projects. Postgres is definitely my next pick, though, and both are miles ahead of MySQL. I understand that MySQL is "good enough" for most people, but it's always painful going back to it and inevitably remembering almost all my favorite features don't exist. I'm stuck with it on a side project and it's frustrating.
- moron4hire 11y ago
  The GO statement is an artifact of SQL Server Management Studio. It's not an actual T-SQL construct. So if you are executing scripts in an automated way (like a continuous deployment scenario), you have to have some way to manually split up the batches.
  - Amezarak 11y ago
    GO is also recognized by sqlcmd, so you're (potentially) still good with automation. I did actually run the above script in SSMS, but I ran most of the ones at my old job automated.
    - mistermann 11y ago
      You wouldn't perchance know a good online resource for recommendations on automating SQL Server deployments would you? Yes, you can google and piece things together, but I've yet to find a well written fairly comprehensive resource. (As the author notes, one would think perhaps MS would provide such documentation, but afaik they don't.)
adekmm 11y ago
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. - Included Integration Services, Analysis services are also easy and enugh for my usage. Postgres: - Is running on linux, to it's cheaper. Even when we use SQL Server as Bizspark (for free now), the Azure Windows VM for it costs us much more, than VM with Linux. And of course when we want to cluster our DB, Postgres is even cheaper. - Great JSON support. There are parts of our project where it's helpfull. - Better configurability, like WAL, checkpoints etc. We have much better write performance on postgres than in sql server (probably just our case). The other things really do not much difference. Both DB's can be extended, and extensions may be written in many languages. Both achieve great overall performance, both have strong community and a lot of documentation.
- MagicWishMonkey 11y ago
  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.
  - sleepyhead 11y ago
    If you are on Mac check out https://eggerapps.at/postico/ https://eggerapps.at/postico/
    - MagicWishMonkey 11y ago
      This looks pretty nice, thanks!
  - kidmenot 11y ago
    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.
anarazel 11y ago
As somebody working on postgres, I don't find the comparison to be done in a particularly fair, or helpful, way. MSSQL is a pretty good database and deserves to be fairly evaluated. Don't get me wrong, I have 'political' problems with MSSQL, but those shouldn't be disguised as technical ones.
moron4hire 11y ago
I get the feeling this guy hasn't had to deal with CSV in-the-wild. RFC4180 is a false prophet. I've never had a client give me CSV data that was actual, good CSV, thus I've always had to write a custom parser. Every. Single. Time. Well, not anymore. Now I just refuse to do shit. "We can't change it" turns into "we contacted the original developer and made him fix his broken shit" when I turn into a complete pain in the ass.
blumkvist 11y ago
Huh... a person who compares databases with regards to analytics and doesn't mention OLAP. Doesn't mention ETL. Doesn't mention reporting. What kind of comparison is that? I often read reviews of databases which dismiss MSSQL completely. And that's fine when it is about building applications. HOWEVER, MSSQL comes with very featurefull ETL tools, very featurefull MOLAP and HOLAP and very featurell reporting suite. It also comes with some data mining algorithms for which I am not qualified to talk, but I've heard the functionality is pretty nice for a starting point. It also comes with an IDE for the people who do those things and a DBA tool for people who administer the databases. Out of those things, postgres has a DBA tool out of the box. Yeah... nice comparison. I find it particularly funny that he cites Dunning-Kruger. DK effect is not meant for others. It is meant for self-evaluation.
jmnicolas 11y ago
Postgres is better than SQL Server because with Postgres I always have the "awesome edition" : http://blog.codinghorror.com/oh-you-wanted-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).
- andrewl 11y ago
  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."
breakingcups 11y ago
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.
- thomaskcr 11y ago
  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 (Azure comes with it installed but no license -- why not just sell a bundled license into the hourly cost?) -- eventually gave up and set up OpenERP in like 30 minutes, spent 3 hours adding the feature I needed and was done. NAV is unquestionably better for this project, but I'm not going to enter into a support contract with a company just to get a license.
  - MichaelGG 11y ago
    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.)
    - thomaskcr 11y ago
      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.
alexggordon 11y ago
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 http://www.convert-in.com/mss2pgs.htm [1] https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_Pos...
- ssmoot 11y ago
  PostgreSQL doesn't have clustered indexes, materialized views, partitioned views, or a sane backup/restore procedure. If you're already a licensed MSSQL customer, I'm not sure what advantages PostgreSQL could really have compared to it's slower performance and much higher operational costs.
  - Jweb_Guru 11y ago
    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").
    - ssmoot 11y ago
      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. And if you just wanted to move data from Server A to Server B? You could link servers and just SELECT INTO.
moron4hire 11y ago
>> 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.
meshko 11y ago
Sigh. It's about as profound as writing an article on whether Linux vs Windows -- which one is a better OS? I skimmed the article for some reason and it is full of questionable statements. One thing that jumped out at me is the part about using multiple cores to run a query. We are in 2015 ladies and gentlemen. A RDBMS must be able to parallelize queries. Argument about never being CPU bound is laughable. I am, as we speak, running a performance test on a 36 core box and I already know the result: I NEED MORE CORES. Analytics is moving into SSD world, where data processing will be CPU bound once again.
rdavisau 11y ago
I am inclined to think that you should use an RDBMS for doing what it does best - things like storing and retrieving data efficiently, facilitating the integrity of data while serving multiple users, and enforcing relational constraints. If you're serious about doing analytics and you try to do it in the db you are going to quickly find yourself hampered by the expressive limitations of SQL. I think it's better to skip that and interface with the database using something more powerful - let the database handle getting the data that you want the best way possible, then process it using something better suited to the job.
S_A_P 11y ago
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 use cases that can favor either platform. If so, pick the tool for the job, move on. If you feel compelled to do so, you should document your use case to help others out.
- feld 11y ago
  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.
  - feld 11y ago
    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.
    - kbenson 11y ago
      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.
__database__ 11y ago
let me first state i'm a huge postgres fan, i implement whenever i have the option. it's a great database and i'm a firm believer in open source.. however if a company is willing to shell out the coin to run MSSQL, i'm not complaining. MSSQL is very robust, very fast, and very easy to administer. i hate giving MS their props but they did a good job with it..
darklajid 11y ago
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 equivalent of the following in one of my projects: UPDATE sometable SET someField = @parameter OUTPUT Inserted.field1, Inserted.field2 WHERE ... Now, either I don't get the limitation the author describes or SQL Server can do that - returning information from the affected results. Works with DELETE as well. We can argue that 'inserted' is a crappy name here, but..
- Aushin 11y ago
  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.
- CWuestefeld 11y ago
  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.
  - darklajid 11y ago
    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.
gilrain 11y ago
The whole thing took about a minute to write and a second to run. It confirmed that some of his folders had a problem and told him which ones they were. How would you do this in Windows? I'm so tired of this. Just because you don't already know Powershell and are too lazy to learn doesn't mean it doesn't exist. A know-nothing Windows user might as well say, "If I want to select and move arbitrary files on Windows, I can point and control-click in seconds. How would you do that in the Linux CLI?" In both instances, it comes across as ignorant to anyone who actually knows the ecosystem being derided.
- vdnkh 11y ago
  I work in the Windows sphere at work and I really like Powershell. It has a ton of great features out of the box (especially for parallelism) as well as solid bread and butter operations. Although it's a bit verbose and not as mature as Bash it's essential to know for any Windows developer.
charisma123 11y ago
One of the major attractions of MS SQL Server 2014 is memory optimized tables (Hekaton engine) which removes locks completely from the database through snapshot versioning of rows. Does postgresql has anything similar to that?
romanovcode 11y ago
PostgreSQL has bad C# drivers, other than that I would choose it over MSSQL.
greggyb 11y ago
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.
edgarvm 11y ago
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.
- meshko 11y ago
  Ah, but you can't publish benchmarks about any of the RDBMSs for adults (Microsoft and I think Oracle too) as per their license agreement. So never expect these types of articles to have any substance.
  - kbenson 11y ago
    I was looking for some earlier today, as I often do when one of these discussions comes up, and when I didn't find any, I suspected that might be the case. It's a shame, as it's another case where a company works to restrict user information, which makes the market less efficient.
    - meshko 11y ago
      So I guess this is when libertarian says: if only there was no state to help stupid corporations enforce these ridiculous restrictions on their customers!
loudmax 11y ago
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.
ryanackley 11y ago
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.
MichaelGG 11y ago
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 something. I hate how MSSQL has gone back on their word to let customers benefit from CPU enhancements. They mocked Oracle for charging by type and core... And now they do the same. Also, multiple result sets was a sorely missed feature when porting stuff to PG. But record types made up for it. Of course now, the dominating factor for a lot of people is "Will a hosting provider (Azure, AWS, Google) just run this for me, automatically giving me perfect backups and restore and HA?" SQL Azure, as I understand, not only does backups, but allows you to restore to arbitrary points in time. Sure it's just keeping txlogs, but that sounds hot when sold like that. For many cases, I can see ditching the privacy issues of "cloud" to get those features with zero capex or management overhead.
- glogla 11y ago
  > 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.
  - MichaelGG 11y ago
    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.
apta 11y ago
> I replied "well there are 1.5 billion Muslims and 1.2 billion Catholics. They can't all be right". Ergo, a billion people most certainly can be wrong. (In this particular case, 2.7 billion people are wrong.) Very smug and condescending statement. Feels like an insecurity on the author's part.

145 points by vince_refiti 11y ago

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 think of to drop a table. It's easier to check sys.all_objects (which will catch anything - functions, views, procs, etc). Operating system choice - well, in 2015, if you're going to mention that, you should be thinking cloud services anyway. They're both available in the cloud - and at which point, who cares what OS it runs on? Goes on and on. I'm a SQL Server guy, and if I was going to make a list of how to choose a relational database platform, here's what I'd probably list: * Up-front cost (license, maintenance) * Ease of finding staff, and their costs * Ease of finding tooling, and its cost * Ease of finding scalable patterns (proven ways to grow it) I don't think either platform is a loser on those counts - it's hard to go wrong with either one.

lassoman 11y ago
Fully agree! This article is based on merits that I would hardly consider if making a choice between any relational engine. It is even more disturbing that the author claims to be a data analyst. CSV support.......R U serious? Who in the right mind would spend hundreds of thousands of $$$ for SQL Server Enterprise Edition licences and regret it because PostreSQL can do it better for free? I'm using both and they're both great but not for or in spite of the reasons outlined here. Author needs to educate herself/himself better before publishing this nonsense.
stevepike 11y ago
Can you drop a recommendation for your favorite startup-friendly ETL tool?
- lassoman 11y ago
  Pentaho if you have no or little money or SSIS (comes with SQL Server licence)
  - stevepike 11y ago
    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?
    - lassoman 11y ago
      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.
  - atwebb 11y ago
    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.
    - lassoman 11y ago
      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.
      - atwebb 11y ago
        I can't tell if I know you now... Anyways, yes, it's very powerful and an excellent tool but I always have to describe it as awfulsome. SSISDB has made great improvements to the setup and promotion of objects which previously could be a pretty big headache (especially when you're not the one deploying etc). Functions for the various tasks/settings can be hidden and it can take a while to convince newcomers that the options are available, you just have to...poke around a bit. It comes with a substantial amount of controls. The metadata/conversion of data can be frustrating but automation greater improves this with a quick controlling framework and the sys tables, it also makes moving data across hundreds of tables a breeze. It is very fast and very stable and I'm glad it's getting improvements and having focused development.
tehaugmenter 11y ago
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 that one. Obviously Windows would be IIS, which means overall the OS choice is a nice one. The fact that you can choose which one to host with, which means you get something comfortable.
- BrentOzar 11y ago
  > 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.
  - tehaugmenter 11y ago
    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.
  - mistermann 11y ago
    Oh, do you work for SO Brent?
    - BrentOzar 11y ago
      > Oh, do you work for SO Brent? I just do SQL Server consulting for 'em from time to time. They're ridiculously sharp guys, so they don't need me much anymore.
  - 11y ago
jackmaney 11y ago
> 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).
- glogla 11y ago
  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.

ben_pr 11y ago

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.

silon3 11y ago
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?
- anarazel 11y ago
  Not really. Postgres' planner is also statistics based. Yes that approach has problems, but the contrary, where you still use a plan that was appropriate for three rows with a couple million isn't good either. It's just a hard problem.
- atwebb 11y ago
  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?
ExpiredLink 11y ago
It's a one-page propaganda site (from someone from Shepperton, GB). I'm a big fan of PostgreSQL but this isn't helpful.

netcraft 11y ago

Previous discussions: https://news.ycombinator.com/item?id=8615320 https://news.ycombinator.com/item?id=8615320

CSDude 11y ago

Does anybody use PG/PLSQL? I wonder how it compares in practice with Oracle PLSQL.

mauricemir 11y ago
Its quiet close I recall being impressed that i could reuse some Oracle Pl/SQL on Postgress
- jmulho 11y ago
  If you know PL/SQL, all you need is an example of the boilerplate that goes at the top and bottom of a PL/pgSQL procedure and you are good to go.
cgh 11y ago
As others have mentioned, they are similar but there are some gotchas. An example is hierarchical data: pgsql lacks start with/connect by. This means you have to write recursive queries using Common Table Expressions (with...as). Not a dealbreaker but it does mean some extra work.

threeseed 11y ago

> Commercial products have support from people who support it because they are paid to. They do the minimum amount necessary to satisfy the terms of the SLA. This is pretty ridiculous and quite a bit insulting to the many people who do work for vendors. I work in a team that has a number of engineers supplied by vendors and they are generally fantastic. Highly qualified, more than happy to assist with tasks that aren't to do with their product and they really care about the overall project outcomes. Open source has forced vendors to make sure that every project that uses their products are a success. > On the other hand, commercial software is often designed by committee, written in cube farms and developed without proper guidance or inspiration Again more nonsense. Not every open source project is some beacon of perfection and neither is every commercial product some poorly designed piece of junk. Anyone that believes otherwise is just being disingenuous. Someone really needs to explain to me why PostgreSQL users in particular seem to always want to bash the competition in order to justify their technology choice. It's been going on for years against MySQL/Oracle first, then MongoDB/NoSQL and now SQL Server. It's odd.

mgkimsal 11y ago
Agreed. One could just as easily write something like "Open source products have support from people who've been told they need to 'contribute' to open source, and since they can't code, they just try to look at code and answer questions on a mailing list until they get an interview at Facebook".
mauricemir 11y ago
So Oracle backport fixes now do they I remember having trouble getting my Oracle based system through y2k back in 98/99
- threeseed 11y ago
  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.

Amezarak 11y ago

> 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 absolutely positive there's some way around it, because I've run many, many such scripts on SQL Server without manual intervention. EDIT: Yes, I just fired up a VM and ran this and got the expected results with no errors. CREATE DATABASE HackerNews; CREATE TABLE dbo.Test (id int IDENTITY(1, 1), name varchar(20)); GO INSERT INTO dbo.Test (name) VALUES ('Amezarak'); GO CREATE PROCEDURE dbo.sp_QueryTest AS SELECT * FROM dbo.Test; GO EXEC dbo.sp_QueryTest In my personal opinion, MSSQL (including the tooling around it) is awesome and possibly one of Microsoft's best products. I actually regret not getting to use it anymore since a) my current job doesn't use it and b) I'm not shelling out for a license for my side projects. Postgres is definitely my next pick, though, and both are miles ahead of MySQL. I understand that MySQL is "good enough" for most people, but it's always painful going back to it and inevitably remembering almost all my favorite features don't exist. I'm stuck with it on a side project and it's frustrating.

moron4hire 11y ago
The GO statement is an artifact of SQL Server Management Studio. It's not an actual T-SQL construct. So if you are executing scripts in an automated way (like a continuous deployment scenario), you have to have some way to manually split up the batches.
- Amezarak 11y ago
  GO is also recognized by sqlcmd, so you're (potentially) still good with automation. I did actually run the above script in SSMS, but I ran most of the ones at my old job automated.
  - mistermann 11y ago
    You wouldn't perchance know a good online resource for recommendations on automating SQL Server deployments would you? Yes, you can google and piece things together, but I've yet to find a well written fairly comprehensive resource. (As the author notes, one would think perhaps MS would provide such documentation, but afaik they don't.)

adekmm 11y ago

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. - Included Integration Services, Analysis services are also easy and enugh for my usage. Postgres: - Is running on linux, to it's cheaper. Even when we use SQL Server as Bizspark (for free now), the Azure Windows VM for it costs us much more, than VM with Linux. And of course when we want to cluster our DB, Postgres is even cheaper. - Great JSON support. There are parts of our project where it's helpfull. - Better configurability, like WAL, checkpoints etc. We have much better write performance on postgres than in sql server (probably just our case). The other things really do not much difference. Both DB's can be extended, and extensions may be written in many languages. Both achieve great overall performance, both have strong community and a lot of documentation.

MagicWishMonkey 11y ago
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.
- sleepyhead 11y ago
  If you are on Mac check out https://eggerapps.at/postico/ https://eggerapps.at/postico/
  - MagicWishMonkey 11y ago
    This looks pretty nice, thanks!
- kidmenot 11y ago
  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.

anarazel 11y ago

As somebody working on postgres, I don't find the comparison to be done in a particularly fair, or helpful, way. MSSQL is a pretty good database and deserves to be fairly evaluated. Don't get me wrong, I have 'political' problems with MSSQL, but those shouldn't be disguised as technical ones.

moron4hire 11y ago

I get the feeling this guy hasn't had to deal with CSV in-the-wild. RFC4180 is a false prophet. I've never had a client give me CSV data that was actual, good CSV, thus I've always had to write a custom parser. Every. Single. Time. Well, not anymore. Now I just refuse to do shit. "We can't change it" turns into "we contacted the original developer and made him fix his broken shit" when I turn into a complete pain in the ass.

blumkvist 11y ago

Huh... a person who compares databases with regards to analytics and doesn't mention OLAP. Doesn't mention ETL. Doesn't mention reporting. What kind of comparison is that? I often read reviews of databases which dismiss MSSQL completely. And that's fine when it is about building applications. HOWEVER, MSSQL comes with very featurefull ETL tools, very featurefull MOLAP and HOLAP and very featurell reporting suite. It also comes with some data mining algorithms for which I am not qualified to talk, but I've heard the functionality is pretty nice for a starting point. It also comes with an IDE for the people who do those things and a DBA tool for people who administer the databases. Out of those things, postgres has a DBA tool out of the box. Yeah... nice comparison. I find it particularly funny that he cites Dunning-Kruger. DK effect is not meant for others. It is meant for self-evaluation.

jmnicolas 11y ago

Postgres is better than SQL Server because with Postgres I always have the "awesome edition" : http://blog.codinghorror.com/oh-you-wanted-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).

andrewl 11y ago
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."

breakingcups 11y ago

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.

thomaskcr 11y ago
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 (Azure comes with it installed but no license -- why not just sell a bundled license into the hourly cost?) -- eventually gave up and set up OpenERP in like 30 minutes, spent 3 hours adding the feature I needed and was done. NAV is unquestionably better for this project, but I'm not going to enter into a support contract with a company just to get a license.
- MichaelGG 11y ago
  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.)
  - thomaskcr 11y ago
    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.

alexggordon 11y ago

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 http://www.convert-in.com/mss2pgs.htm [1] https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_Pos...

ssmoot 11y ago
PostgreSQL doesn't have clustered indexes, materialized views, partitioned views, or a sane backup/restore procedure. If you're already a licensed MSSQL customer, I'm not sure what advantages PostgreSQL could really have compared to it's slower performance and much higher operational costs.
- Jweb_Guru 11y ago
  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").
  - ssmoot 11y ago
    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. And if you just wanted to move data from Server A to Server B? You could link servers and just SELECT INTO.

moron4hire 11y ago

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

meshko 11y ago

Sigh. It's about as profound as writing an article on whether Linux vs Windows -- which one is a better OS? I skimmed the article for some reason and it is full of questionable statements. One thing that jumped out at me is the part about using multiple cores to run a query. We are in 2015 ladies and gentlemen. A RDBMS must be able to parallelize queries. Argument about never being CPU bound is laughable. I am, as we speak, running a performance test on a 36 core box and I already know the result: I NEED MORE CORES. Analytics is moving into SSD world, where data processing will be CPU bound once again.

rdavisau 11y ago

I am inclined to think that you should use an RDBMS for doing what it does best - things like storing and retrieving data efficiently, facilitating the integrity of data while serving multiple users, and enforcing relational constraints. If you're serious about doing analytics and you try to do it in the db you are going to quickly find yourself hampered by the expressive limitations of SQL. I think it's better to skip that and interface with the database using something more powerful - let the database handle getting the data that you want the best way possible, then process it using something better suited to the job.

S_A_P 11y ago

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 use cases that can favor either platform. If so, pick the tool for the job, move on. If you feel compelled to do so, you should document your use case to help others out.

feld 11y ago
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.
- feld 11y ago
  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.
  - kbenson 11y ago
    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.

__database__ 11y ago

let me first state i'm a huge postgres fan, i implement whenever i have the option. it's a great database and i'm a firm believer in open source.. however if a company is willing to shell out the coin to run MSSQL, i'm not complaining. MSSQL is very robust, very fast, and very easy to administer. i hate giving MS their props but they did a good job with it..

darklajid 11y ago

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 equivalent of the following in one of my projects: UPDATE sometable SET someField = @parameter OUTPUT Inserted.field1, Inserted.field2 WHERE ... Now, either I don't get the limitation the author describes or SQL Server can do that - returning information from the affected results. Works with DELETE as well. We can argue that 'inserted' is a crappy name here, but..

Aushin 11y ago
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.
CWuestefeld 11y ago
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.
- darklajid 11y ago
  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.

gilrain 11y ago

The whole thing took about a minute to write and a second to run. It confirmed that some of his folders had a problem and told him which ones they were. How would you do this in Windows? I'm so tired of this. Just because you don't already know Powershell and are too lazy to learn doesn't mean it doesn't exist. A know-nothing Windows user might as well say, "If I want to select and move arbitrary files on Windows, I can point and control-click in seconds. How would you do that in the Linux CLI?" In both instances, it comes across as ignorant to anyone who actually knows the ecosystem being derided.

vdnkh 11y ago
I work in the Windows sphere at work and I really like Powershell. It has a ton of great features out of the box (especially for parallelism) as well as solid bread and butter operations. Although it's a bit verbose and not as mature as Bash it's essential to know for any Windows developer.

charisma123 11y ago

One of the major attractions of MS SQL Server 2014 is memory optimized tables (Hekaton engine) which removes locks completely from the database through snapshot versioning of rows. Does postgresql has anything similar to that?

romanovcode 11y ago

PostgreSQL has bad C# drivers, other than that I would choose it over MSSQL.

greggyb 11y ago

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.

edgarvm 11y ago

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.

meshko 11y ago
Ah, but you can't publish benchmarks about any of the RDBMSs for adults (Microsoft and I think Oracle too) as per their license agreement. So never expect these types of articles to have any substance.
- kbenson 11y ago
  I was looking for some earlier today, as I often do when one of these discussions comes up, and when I didn't find any, I suspected that might be the case. It's a shame, as it's another case where a company works to restrict user information, which makes the market less efficient.
  - meshko 11y ago
    So I guess this is when libertarian says: if only there was no state to help stupid corporations enforce these ridiculous restrictions on their customers!

loudmax 11y ago

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.

ryanackley 11y ago

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.

MichaelGG 11y ago

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 something. I hate how MSSQL has gone back on their word to let customers benefit from CPU enhancements. They mocked Oracle for charging by type and core... And now they do the same. Also, multiple result sets was a sorely missed feature when porting stuff to PG. But record types made up for it. Of course now, the dominating factor for a lot of people is "Will a hosting provider (Azure, AWS, Google) just run this for me, automatically giving me perfect backups and restore and HA?" SQL Azure, as I understand, not only does backups, but allows you to restore to arbitrary points in time. Sure it's just keeping txlogs, but that sounds hot when sold like that. For many cases, I can see ditching the privacy issues of "cloud" to get those features with zero capex or management overhead.

glogla 11y ago
> 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.
- MichaelGG 11y ago
  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.

apta 11y ago

> I replied "well there are 1.5 billion Muslims and 1.2 billion Catholics. They can't all be right". Ergo, a billion people most certainly can be wrong. (In this particular case, 2.7 billion people are wrong.) Very smug and condescending statement. Feels like an insecurity on the author's part.