5 ms·

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 n

by 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?
    - coderzach 10y ago
      9.5, however we did experiment with 9.6 when it was in beta, due to the parallel sequential scan feature, but it didn't seem to help much.
  - snuxoll 10y ago
    I mean, if you were already jumping over to using columnstore tables why not use Cassandra? I guess CQL has a lot of limits that SQL doesn't, but if your use-case matches a columnstore it's an option to consider.
    - tracker1 10y ago
      My guess would be the integration with everything that wasn't a columnstore... Being able to do both in the same dbms is an advantage. Also, doing everything in Cassandra is hard, and takes a lot of planning and effort that may have not been available. I can't speak to their specific use cases though. I've been a bit of a RethinkDB fan myself in terms of straddling paradigms. I hope they shake out into a foundation structure with as much as possible in tact. I do think that once PostgreSQL gets some of the clustering/HA features in the box and shaken out, it will become the default option for most uses. As it stands MSSQL does have some compelling advantages, some of which you pay for.
      - snuxoll 10y ago
        > I do think that once PostgreSQL gets some of the clustering/HA features in the box and shaken out, it will become the default option for most uses. As it stands MSSQL does have some compelling advantages, some of which you pay for. Or even a add-on that's not so difficult to configure. EnterpriseDB has made strides here, but justifying the license cost is harder for me now that they've moved to their new Unicore model. `repmgr` from 2ndQuadrant is getting really close to ideal and it's included in the PGDG yum and apt repositories, my biggest issue is the manual plumbing involved to get clients to automatically pick up the new master after a failover and automatically rewinding a failed master and bringing it back up as a standby (both are done out of the box by EDB's solution).
toyg 10y ago
> like Oracle Hyperion can run against SQL Server running on Linux. Do I know you? :) Did you actually try HFM or Planning with realistic workloads? I'm very curious... I expect Oracle will soon say SQLServer on Linux is not supported, any day from now. Their Linux support is mostly geared towards Exalytics, which is geared towards Oracle licenses. Then again, these days they're so desperate to sell extra licenses, they might even accept it.
- arethuza 10y ago
  It's been a couple of years since I had to deal with Hyperion FM but even back then I thought it was reasonably well known that SQL Server support was being removed in future versions?
  - toyg 10y ago
    Nah, waaay too many Hyperion EPM customers are on MSSQL for that to ever happen. In fact, despite all claims that products are tested "oracle-first", they still work better with SQLServer... It doesn't matter anyway, the whole suite is slowly being strangled in favour of crappy cloud versions.
- teilo 10y ago
  They may not "support" it, but they would have to do a lot of work to prevent it, since the Linux and Windows implementations are identical. An application cannot tell the difference.

by teilo 10y ago

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?
  - coderzach 10y ago
    9.5, however we did experiment with 9.6 when it was in beta, due to the parallel sequential scan feature, but it didn't seem to help much.
- snuxoll 10y ago
  I mean, if you were already jumping over to using columnstore tables why not use Cassandra? I guess CQL has a lot of limits that SQL doesn't, but if your use-case matches a columnstore it's an option to consider.
  - tracker1 10y ago
    My guess would be the integration with everything that wasn't a columnstore... Being able to do both in the same dbms is an advantage. Also, doing everything in Cassandra is hard, and takes a lot of planning and effort that may have not been available. I can't speak to their specific use cases though. I've been a bit of a RethinkDB fan myself in terms of straddling paradigms. I hope they shake out into a foundation structure with as much as possible in tact. I do think that once PostgreSQL gets some of the clustering/HA features in the box and shaken out, it will become the default option for most uses. As it stands MSSQL does have some compelling advantages, some of which you pay for.
    - snuxoll 10y ago
      > I do think that once PostgreSQL gets some of the clustering/HA features in the box and shaken out, it will become the default option for most uses. As it stands MSSQL does have some compelling advantages, some of which you pay for. Or even a add-on that's not so difficult to configure. EnterpriseDB has made strides here, but justifying the license cost is harder for me now that they've moved to their new Unicore model. `repmgr` from 2ndQuadrant is getting really close to ideal and it's included in the PGDG yum and apt repositories, my biggest issue is the manual plumbing involved to get clients to automatically pick up the new master after a failover and automatically rewinding a failed master and bringing it back up as a standby (both are done out of the box by EDB's solution).

toyg 10y ago

> like Oracle Hyperion can run against SQL Server running on Linux. Do I know you? :) Did you actually try HFM or Planning with realistic workloads? I'm very curious... I expect Oracle will soon say SQLServer on Linux is not supported, any day from now. Their Linux support is mostly geared towards Exalytics, which is geared towards Oracle licenses. Then again, these days they're so desperate to sell extra licenses, they might even accept it.

arethuza 10y ago
It's been a couple of years since I had to deal with Hyperion FM but even back then I thought it was reasonably well known that SQL Server support was being removed in future versions?
- toyg 10y ago
  Nah, waaay too many Hyperion EPM customers are on MSSQL for that to ever happen. In fact, despite all claims that products are tested "oracle-first", they still work better with SQLServer... It doesn't matter anyway, the whole suite is slowly being strangled in favour of crappy cloud versions.
teilo 10y ago
They may not "support" it, but they would have to do a lot of work to prevent it, since the Linux and Windows implementations are identical. An application cannot tell the difference.