12 ms·

What are the advantages of even using sql server when I can use postgres far more easily on linux ?

iRobbery 10y ago
Depends who you ask, for you probably not that much. For mickeysoft however... new territory to sell licenses to at some point. [edit] https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing https://www.microsoft.com/en-us/sql-server/sql-server-2016-p... It would have been nice if some were able to explain why the above reasoning of selling licenses is incorrect? There is no other reason for MS to do this besides money.
- tedunangst 10y ago
  Maybe next time try without saying "mickeysoft".
- tracker1 10y ago
  Licensing is probably a big reason... integration and migration to azure is probably another reason... MS pricing for SQL is pretty competitive in terms of what cloud offerings are available. I wish their compute would come down a bit across the board though. I keep thinking it would be cool to see an interactive graph of connection latency, and throughput for 1k, 1mb, 1gb xfers between the major data centers for the big cloud providers. If I could get compute from DO/Linode and use services from Azure and have under 20ms latency for data queries, I might use Azure for services, and Linode or DO for compute. I know that's too much for some things, but may be work the cost in latency for many use cases... saving a few hundred a month on web/service vm's and leverage Azure beyond that. (as for downvotes, probably mickeysoft)
icebraining 10y ago
Some discussion here: https://news.ycombinator.com/item?id=9464505 https://news.ycombinator.com/item?id=9464505
- rokosbasilisk 10y ago
  thanks! The sql server integration services seem interesting.
  - danzig13 10y ago
    I have used it recently. The main thing I like is that it organizes code visually as workflows and it has many pre-packaged "tasks" you can use. This means I didn't have to come up with some structure for 900 script files. I did run into several annoying errors when trying to import from excel.
- thomasz 10y ago
  That's one of the cases where the HN discussion is really great, while the original article is rather bad.
callinyouin 10y ago
For you personally there probably are no advantages. For large organizations that are already heavily invested in SQL Server, however, there may be benefit in being able to migrate off of Windows, especially if most of the rest of the server infrastructure is already Linux based.
tuwtuwtuw 10y ago
PostgreSQL has features MSSQL does not have, and vice versa. PG is still very lacking when it comes to multi-node clusters for high availability for example.
thomasz 10y ago
Better tooling a better query optimizer and more reassuring "enterprise support", if that's your kind of kink.
teilo 10y ago
OLAP for one. Initial support for basic OLAP conventions was introduced in 9.5 (grouping sets, rollups, cubes, upserts), but there's still a lot missing (like nested aggregations), and a lot of performance tuning that needs to be done. We cannot run our BI on Postgres or we would. But with Microsoft's latest work, even products like Oracle Hyperion can run against SQL Server running on Linux.
- timClicks 10y ago
  You almost certainly already know this, but PostgreSQL behaves very differently depending on its config parameters. We've been able to get decent OLAP performance at least to the dozens of TB scale. Very interested to hear how to made the decision to use MS SQL and the test cases that drove that decision.
  - teilo 10y ago
    Timing. Postgres 9.5 was not on the horizon when we were building out our solution. Lacking OLAP support, the query complexity to achieve similar functionality would have been very cumbersome, error-prone, and non-performant (excessive use of unions, massive case clauses, lots of repetition, inscrutable window functions - a mess). Further, our finance team later insisted on using Oracle EssBase, where Postgres in any version will never be an option.
  - coderzach 10y ago
    Not OP, but for us, the reasons we switched from postgres to sql server were the "clustered columnstore indexes" and query parallelization. Before switching to sql server, we spent a ton of time optimizing query performance with "clever" use of indexes, ctes, etc. With sql server, we've been able to just use the columnstore indexes and write simple, straightforward queries that run in ~200ms vs 10-20 seconds in postgres. And before you ask, we spent A TON if time and money on configuration, paying for multiple postgres experts to help with optimization.
    - raarts 10y ago
      What version(s) of PostgreSQL were you using?
dogma1138 10y ago
Be able to use the 1000s of commercial products that need MSSQL. Encryption is easier to manage and it has built in solutions for HIPPA and PCI. SQL reporting services and the entire ecosystem of apps that run on top of MSSQL.. Better clustering, ha, revision control and backup. Most likely better enterprise support, and you'll be able to port "legacy" applications that were designed with win/sql in mind without having to rewrite your entire DB access and query code.
- rokosbasilisk 10y ago
  Wouldnt it be easier just to run sql server on windows at that point to take advantage of the ms ecosystem ? Edit: I am not familar with much of anything microsoft related.
  - UnoriginalGuy 10y ago
    You'd never run MS SQL Server and these other servers on the same machine either way, so you could easily run SQL Server on Linux and Reporting Services on Windows Server. Although if you're virtualizing Windows Server, it may be effectively free to run an additional Windows Server instance (since you pay per core regardless); but if your hypervisor's resources are already saturated then you could take SQL Server off of it and put it onto a inexpensive Linux Server.
  - Deinumite 10y ago
    Maybe but this also gives you more hosting options if you are in the cloud. Also tools like puppet and chef for example have an easier time provisioning Unix like machines than Windows (although this has been changing, and the situation could be much better I haven't dealt with it for a bit). Also you don't have to deal with licenses for Windows server instances which can be another huge painpoint.
  - dogma1138 10y ago
    There are quite a few products that require a Linux host for the main app server and a MSSQL or Oracle DB. Oracle support is being dropped left and right MSSQL is there because it was the defacto enterprise DB for ages and it also has a free version via MSSQL express. And lastly having more options is never a bad thing.
sitharus 10y ago
From a development perspective clustered indexes and SSDT are the best features. Clustered indexes vastly improve query performance on tables where you often want the data by something other than insert order. SSDT lets you version your database in a really easy way and generates change scripts on demand.
- snuxoll 10y ago
  You can optionally cluster a table based on index order with PostgreSQL, simply create the index and then `ALTER TABLE foo CLUSTER ON bar_ix`. Clustered indexes are actually an issue I run into with SQL server, because, like MySQL, the table data is always indirectly referenced by the clustered index, so any query that uses a secondary index requires a second pointer deref to find the data in the table heap. This has a benefit to insert/update performance, but I'd argue that more tables are higher on reads than mutations.
  - dx034 10y ago
    You can use INCLUDE to put some columns in the index. Doesn't always help but speeds up many queries. AFAIK Postgres doesn't have that feature, not sure about MySQL.
    - snuxoll 10y ago
      Using INCLUDE comes at the cost of bloating your indexes making scans take a considerable longer time, not having to chase that extra pointer works well enough in most cases (and if you are able to benefit from Heap-Only Tuples you can even have in-place UPDATES without needing to rewrite the pointer in every index).
  - dhd415 10y ago
    SQL Server supports heaps (tables w/o clustered indexes) on which indexes point directly to the referenced row. It's nice that they allow you to choose between those and tables with clustered indexes where any secondary index references the primary key. See https://msdn.microsoft.com/en-us/library/hh213609.aspx https://msdn.microsoft.com/en-us/library/hh213609.aspx
rodgerd 10y ago
I pray for the day when Postgresql clustering is as good/easy as AlwaysOn Availability Groups.
- thijsvandien 10y ago
  Paying works better than praying: https://www.postgresql.org/about/donate/ https://www.postgresql.org/about/donate/ /semi-serious :)
  - rodgerd 10y ago
    My employer spends money with EnterpriseDB so we've got that covered 8)
gigatexal 10y ago
Parallel queries baked in from long back. I think Postgres got this just recently.
nimchimpsky 10y ago
We did a performe comparison on handling Geo data. SQL server came out on top.
- Demiurge 10y ago
  In what way? I can't even tell if you mean features or performance. PostGIS is unmatched as far as features, as far as I know.
  - dx034 10y ago
    Don't know if the comment was edited, but it says performance comparison? (If I interpret the typo correctly)
    - nimchimpsky 10y ago
      it wasn't edited
  - dagw 10y ago
    While it is true the SQL Server is missing a fair number of features compared to PostGIS (and Oracle Spatial), I've always found that the spatial performance of SQL Server is been really great for the common features it does support.
    - Demiurge 10y ago
      Ok, thanks, that makes it worth checking out!
  - nimchimpsky 10y ago
    I said "performe" a typo from my phone, you really didn't know that I meant performance ? Queries returned results faster in sql server, after checking we had set everything up optimally in everything.
- tracker1 10y ago
  That's one area, where at the time (about 4 years ago), Mongo worked out really well for me... iirc there was a bug I experienced with ElasticSearch, and RethinkDB didn't have geo indexing yet. It doesn't surprise me though... MSSQL tends to do very well out of the box for most workloads.
tresil 10y ago
I love Postgres, but lack of parallel query processing (same query leveraging multiple threads) has been a deal-breaker for some projects. Thankfully, Postgres is finally starting to make some progress here. Also of note, In November MS added a number of previously enterprise-only features to all versions of SQL Server 2016 (Service Pack 1 and later). Examples: In-memory OLTP, ColumnStore indexes, are available even in the free Express edition now. Native Postgres, to my knowledge, doesn't have comparables for these yet. https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-serv...
- tracker1 10y ago
  I've always found MS-SQL pretty impressive in terms of features/value, though they get pricey pretty quickly, I've always found it nicer to work with than Oracle or DB2 by comparison... Although, I do wish they'd support the more common LIMIT syntax that many other DBs use. That said, from a devops perspective MSSQL doesn't have any real peers. Some of the analytical tooling around MSSQL is pretty good too. Have had to track performance issues in the past. Even the general query analyzer integrated with Management Studio is great. I do hope they consider going the electron route for cross-platform management studio, it's worked really well for VS Code, and can imagine it would be a great fit for this as well. Even if quite a bit outside their current tooling.
- dspillett 10y ago
  > I love Postgres, but lack of parallel query processing ... for some projects. It has surprisingly little effect on many workloads though - then engine is fairly conservative on when it will use it too (and there are easy ways for you to accidentally make it not consider parallelism). For many workloads you are often better off using the many CPU cores to run different queries than cooperate on a smaller number. Data warehousing and "big data" are a place where this isn't the case though, and other features MS SQL Server has over PG and others shine here too: the column store indexes, in-memory processing, and so forth. And you get these in the free "express" editions too, as of 2016sp1, though that is only really useful for small projects and prototypes due to other limitations in that edition (10Gb/database, max 4 CPU cores & 1.5Gb RAM put to use per instance) so you'll be paying for standard edition at least for real work.
bsg75 10y ago
My common answer to this is query parallelism. For those of us in the DWH/OLAP space it's a big deal that the F/OSS engines are just starting to tackle, and it's not an easy problem to solve. I might not mind paying for MSSQL if I also did also not have to pay for Windows.
- hashhar 10y ago
  Quite a lot of enterprise features made it into the free SQL Server Express this winter. [1]: https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-serv...
  - dspillett 10y ago
    Other limitations (10Gb/database, max 4 CPU cores & 1.5Gb RAM put to use per instance) mean Express is only useful for small projects and/or prototypes though - you'll be paying for standard edition for real work. Those previously enterprise-only features being in standard is a huge and convenient change too though. It is worth noting that developer edition is also free these days (as of 2016RTM IIRC) so for prototyping work (unless you need to support older versions) you are better off with that than express as it has always basically been the full enterprise edition.
lafar6502 10y ago
Well using Postgres on Windows is just as easy as on Linux. Yet still SQL server is the king :)
known 10y ago
https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Fundamental_features https://en.wikipedia.org/wiki/Comparison_of_relational_datab...
gaius 10y ago
Consider that PG only just got fairly rudimentary parallel queries in 9.6 this year, SQL Server had it in (IIRC) 2000. For DW and general DSS workloads that is significant.
koffiezet 10y ago
It will probably allow me to eliminate some Windows VM's that are strictly there to provide some enterpricy Java apps with an MSSQL database.

Depends who you ask, for you probably not that much. For mickeysoft however... new territory to sell licenses to at some point. [edit] https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing https://www.microsoft.com/en-us/sql-server/sql-server-2016-p... It would have been nice if some were able to explain why the above reasoning of selling licenses is incorrect? There is no other reason for MS to do this besides money.

tedunangst 10y ago
Maybe next time try without saying "mickeysoft".
tracker1 10y ago
Licensing is probably a big reason... integration and migration to azure is probably another reason... MS pricing for SQL is pretty competitive in terms of what cloud offerings are available. I wish their compute would come down a bit across the board though. I keep thinking it would be cool to see an interactive graph of connection latency, and throughput for 1k, 1mb, 1gb xfers between the major data centers for the big cloud providers. If I could get compute from DO/Linode and use services from Azure and have under 20ms latency for data queries, I might use Azure for services, and Linode or DO for compute. I know that's too much for some things, but may be work the cost in latency for many use cases... saving a few hundred a month on web/service vm's and leverage Azure beyond that. (as for downvotes, probably mickeysoft)

icebraining 10y ago

Some discussion here: https://news.ycombinator.com/item?id=9464505 https://news.ycombinator.com/item?id=9464505

rokosbasilisk 10y ago
thanks! The sql server integration services seem interesting.
- danzig13 10y ago
  I have used it recently. The main thing I like is that it organizes code visually as workflows and it has many pre-packaged "tasks" you can use. This means I didn't have to come up with some structure for 900 script files. I did run into several annoying errors when trying to import from excel.
thomasz 10y ago
That's one of the cases where the HN discussion is really great, while the original article is rather bad.

callinyouin 10y ago

For you personally there probably are no advantages. For large organizations that are already heavily invested in SQL Server, however, there may be benefit in being able to migrate off of Windows, especially if most of the rest of the server infrastructure is already Linux based.

tuwtuwtuw 10y ago

PostgreSQL has features MSSQL does not have, and vice versa. PG is still very lacking when it comes to multi-node clusters for high availability for example.

thomasz 10y ago

Better tooling a better query optimizer and more reassuring "enterprise support", if that's your kind of kink.

teilo 10y ago

OLAP for one. Initial support for basic OLAP conventions was introduced in 9.5 (grouping sets, rollups, cubes, upserts), but there's still a lot missing (like nested aggregations), and a lot of performance tuning that needs to be done. We cannot run our BI on Postgres or we would. But with Microsoft's latest work, even products like Oracle Hyperion can run against SQL Server running on Linux.

timClicks 10y ago
You almost certainly already know this, but PostgreSQL behaves very differently depending on its config parameters. We've been able to get decent OLAP performance at least to the dozens of TB scale. Very interested to hear how to made the decision to use MS SQL and the test cases that drove that decision.
- teilo 10y ago
  Timing. Postgres 9.5 was not on the horizon when we were building out our solution. Lacking OLAP support, the query complexity to achieve similar functionality would have been very cumbersome, error-prone, and non-performant (excessive use of unions, massive case clauses, lots of repetition, inscrutable window functions - a mess). Further, our finance team later insisted on using Oracle EssBase, where Postgres in any version will never be an option.
- coderzach 10y ago
  Not OP, but for us, the reasons we switched from postgres to sql server were the "clustered columnstore indexes" and query parallelization. Before switching to sql server, we spent a ton of time optimizing query performance with "clever" use of indexes, ctes, etc. With sql server, we've been able to just use the columnstore indexes and write simple, straightforward queries that run in ~200ms vs 10-20 seconds in postgres. And before you ask, we spent A TON if time and money on configuration, paying for multiple postgres experts to help with optimization.
  - raarts 10y ago
    What version(s) of PostgreSQL were you using?

dogma1138 10y ago

Be able to use the 1000s of commercial products that need MSSQL. Encryption is easier to manage and it has built in solutions for HIPPA and PCI. SQL reporting services and the entire ecosystem of apps that run on top of MSSQL.. Better clustering, ha, revision control and backup. Most likely better enterprise support, and you'll be able to port "legacy" applications that were designed with win/sql in mind without having to rewrite your entire DB access and query code.

rokosbasilisk 10y ago
Wouldnt it be easier just to run sql server on windows at that point to take advantage of the ms ecosystem ? Edit: I am not familar with much of anything microsoft related.
- UnoriginalGuy 10y ago
  You'd never run MS SQL Server and these other servers on the same machine either way, so you could easily run SQL Server on Linux and Reporting Services on Windows Server. Although if you're virtualizing Windows Server, it may be effectively free to run an additional Windows Server instance (since you pay per core regardless); but if your hypervisor's resources are already saturated then you could take SQL Server off of it and put it onto a inexpensive Linux Server.
- Deinumite 10y ago
  Maybe but this also gives you more hosting options if you are in the cloud. Also tools like puppet and chef for example have an easier time provisioning Unix like machines than Windows (although this has been changing, and the situation could be much better I haven't dealt with it for a bit). Also you don't have to deal with licenses for Windows server instances which can be another huge painpoint.
- dogma1138 10y ago
  There are quite a few products that require a Linux host for the main app server and a MSSQL or Oracle DB. Oracle support is being dropped left and right MSSQL is there because it was the defacto enterprise DB for ages and it also has a free version via MSSQL express. And lastly having more options is never a bad thing.

sitharus 10y ago

From a development perspective clustered indexes and SSDT are the best features. Clustered indexes vastly improve query performance on tables where you often want the data by something other than insert order. SSDT lets you version your database in a really easy way and generates change scripts on demand.

snuxoll 10y ago
You can optionally cluster a table based on index order with PostgreSQL, simply create the index and then `ALTER TABLE foo CLUSTER ON bar_ix`. Clustered indexes are actually an issue I run into with SQL server, because, like MySQL, the table data is always indirectly referenced by the clustered index, so any query that uses a secondary index requires a second pointer deref to find the data in the table heap. This has a benefit to insert/update performance, but I'd argue that more tables are higher on reads than mutations.
- dx034 10y ago
  You can use INCLUDE to put some columns in the index. Doesn't always help but speeds up many queries. AFAIK Postgres doesn't have that feature, not sure about MySQL.
  - snuxoll 10y ago
    Using INCLUDE comes at the cost of bloating your indexes making scans take a considerable longer time, not having to chase that extra pointer works well enough in most cases (and if you are able to benefit from Heap-Only Tuples you can even have in-place UPDATES without needing to rewrite the pointer in every index).
- dhd415 10y ago
  SQL Server supports heaps (tables w/o clustered indexes) on which indexes point directly to the referenced row. It's nice that they allow you to choose between those and tables with clustered indexes where any secondary index references the primary key. See https://msdn.microsoft.com/en-us/library/hh213609.aspx https://msdn.microsoft.com/en-us/library/hh213609.aspx

rodgerd 10y ago

I pray for the day when Postgresql clustering is as good/easy as AlwaysOn Availability Groups.

thijsvandien 10y ago
Paying works better than praying: https://www.postgresql.org/about/donate/ https://www.postgresql.org/about/donate/ /semi-serious :)
- rodgerd 10y ago
  My employer spends money with EnterpriseDB so we've got that covered 8)

gigatexal 10y ago

Parallel queries baked in from long back. I think Postgres got this just recently.

nimchimpsky 10y ago

We did a performe comparison on handling Geo data. SQL server came out on top.

Demiurge 10y ago
In what way? I can't even tell if you mean features or performance. PostGIS is unmatched as far as features, as far as I know.
- dx034 10y ago
  Don't know if the comment was edited, but it says performance comparison? (If I interpret the typo correctly)
  - nimchimpsky 10y ago
    it wasn't edited
- dagw 10y ago
  While it is true the SQL Server is missing a fair number of features compared to PostGIS (and Oracle Spatial), I've always found that the spatial performance of SQL Server is been really great for the common features it does support.
  - Demiurge 10y ago
    Ok, thanks, that makes it worth checking out!
- nimchimpsky 10y ago
  I said "performe" a typo from my phone, you really didn't know that I meant performance ? Queries returned results faster in sql server, after checking we had set everything up optimally in everything.
tracker1 10y ago
That's one area, where at the time (about 4 years ago), Mongo worked out really well for me... iirc there was a bug I experienced with ElasticSearch, and RethinkDB didn't have geo indexing yet. It doesn't surprise me though... MSSQL tends to do very well out of the box for most workloads.

tresil 10y ago

I love Postgres, but lack of parallel query processing (same query leveraging multiple threads) has been a deal-breaker for some projects. Thankfully, Postgres is finally starting to make some progress here. Also of note, In November MS added a number of previously enterprise-only features to all versions of SQL Server 2016 (Service Pack 1 and later). Examples: In-memory OLTP, ColumnStore indexes, are available even in the free Express edition now. Native Postgres, to my knowledge, doesn't have comparables for these yet. https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-serv...

tracker1 10y ago
I've always found MS-SQL pretty impressive in terms of features/value, though they get pricey pretty quickly, I've always found it nicer to work with than Oracle or DB2 by comparison... Although, I do wish they'd support the more common LIMIT syntax that many other DBs use. That said, from a devops perspective MSSQL doesn't have any real peers. Some of the analytical tooling around MSSQL is pretty good too. Have had to track performance issues in the past. Even the general query analyzer integrated with Management Studio is great. I do hope they consider going the electron route for cross-platform management studio, it's worked really well for VS Code, and can imagine it would be a great fit for this as well. Even if quite a bit outside their current tooling.
dspillett 10y ago
> I love Postgres, but lack of parallel query processing ... for some projects. It has surprisingly little effect on many workloads though - then engine is fairly conservative on when it will use it too (and there are easy ways for you to accidentally make it not consider parallelism). For many workloads you are often better off using the many CPU cores to run different queries than cooperate on a smaller number. Data warehousing and "big data" are a place where this isn't the case though, and other features MS SQL Server has over PG and others shine here too: the column store indexes, in-memory processing, and so forth. And you get these in the free "express" editions too, as of 2016sp1, though that is only really useful for small projects and prototypes due to other limitations in that edition (10Gb/database, max 4 CPU cores & 1.5Gb RAM put to use per instance) so you'll be paying for standard edition at least for real work.

bsg75 10y ago

My common answer to this is query parallelism. For those of us in the DWH/OLAP space it's a big deal that the F/OSS engines are just starting to tackle, and it's not an easy problem to solve. I might not mind paying for MSSQL if I also did also not have to pay for Windows.

hashhar 10y ago
Quite a lot of enterprise features made it into the free SQL Server Express this winter. [1]: https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-serv...
- dspillett 10y ago
  Other limitations (10Gb/database, max 4 CPU cores & 1.5Gb RAM put to use per instance) mean Express is only useful for small projects and/or prototypes though - you'll be paying for standard edition for real work. Those previously enterprise-only features being in standard is a huge and convenient change too though. It is worth noting that developer edition is also free these days (as of 2016RTM IIRC) so for prototyping work (unless you need to support older versions) you are better off with that than express as it has always basically been the full enterprise edition.

lafar6502 10y ago

Well using Postgres on Windows is just as easy as on Linux. Yet still SQL server is the king :)

known 10y ago

https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Fundamental_features https://en.wikipedia.org/wiki/Comparison_of_relational_datab...

gaius 10y ago

Consider that PG only just got fairly rudimentary parallel queries in 9.6 this year, SQL Server had it in (IIRC) 2000. For DW and general DSS workloads that is significant.

koffiezet 10y ago

It will probably allow me to eliminate some Windows VM's that are strictly there to provide some enterpricy Java apps with an MSSQL database.