6 ms·

Well, from one VERY expensive vendor, to another considerably less expensive vendor Also, MSSQL have few things going for it, and surprisingly no one seem to b

Well, from one VERY expensive vendor, to another considerably less expensive vendor Also, MSSQL have few things going for it, and surprisingly no one seem to be even trying to catch up - Their BI Stacks (PowerBI, SSAS) - Their Database Development (SDK) ( https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver16 ) The MSSQL BI stack is unmatched , SSAS is the top star of BI cubes and the second option is not even close SSRS is ok, SSIS is passable , but still both are very decent PowerBI and family is also the best option for Mid to large (not FAANG large, but just normal large) companies And finally the GEM that is database projects, you can program your DB changes declaratively, there is nothing like this in the market and again, no one is even trying The easiest platformt todo evolutionary DB development is MS SQL I really wish someone will implement DB Projects (dacpac) for Postgresql

manishsharan 2y ago
MS Azure SQL on is very cheap compared to any other self hosted database including PG and MS SQL. Unless they running a super heavy workload , this solution will meet most business requirements. But this is also a gateway drug for cloud addiction.
- kjax 2y ago
  After scanning the Azure and AWS pricing pages and calculators for comparably speced Azure Manged SQL and Amazon RDS for PostgreSQL instances, AWS's RDS comes out at about 1/3 the price of Azure Managed SQL. This matches my experience with deployments on AWS and Azure. Both are managed db instances, but Azure's is consistently more expensive for comparable offerings on AWS or GCP.
  - AlfeG 2y ago
    Is there any analog for Azure SQL Elastic Pools for Postgres anywhere? We pay in total something around 600 bucks to manage around 250 databases in MSSQL servers (with failover for prod databases, DTU based model) We pay for log analytics more then we pay for Sql Servers. Those Elastic Pools is a blocker for us on the way to migrate to Postgres from MSSQL...
    - ilkhan4 2y ago
      It's been a while since I've looked at elastic pools in Azure, but maybe Neon (https://neon.tech/ https://neon.tech/) or recently Nile (https://www.thenile.dev/ https://www.thenile.dev/) might work in terms of spinning up a bunch of separate logical DBs with shared compute/storage.
      - nikita 2y ago
        (neon ceo). We have lots of examples of this. Here is one with Retool. https://neon.tech/blog/how-retool-uses-retool-and-the-neon-api-to-manage-300k-postgres-databases https://neon.tech/blog/how-retool-uses-retool-and-the-neon-a...
      - clarkbw 2y ago
        (neon employee) and Neon will be available on Azure very soon ( https://neon.tech/blog/neon-is-coming-to-azure https://neon.tech/blog/neon-is-coming-to-azure )
  - manishsharan 2y ago
    I do not have experience with AWS RDS so I cannot speak to that. In my experience, GCP Cloud SQL for Postgres has been more expensive than MS Azure SQL. In our tests, CloudSQL also was not comparable to the resiliency offered by Azure SQL. Things like Automated DR and automated failover etc. were not at par with what Azure offered. Not to mention , Column level encryption is standard for Azure SQL.
kragen 2y ago
does 'bi' mean 'olap'? because the literal expansion 'business intelligence' (or the explanation in the Wikipedia article) is hard to interpret as something that makes sense in contexts like this where you're apparently talking about features of software rather than social practices. the reference to 'bi cubes' makes me think that maybe it means 'olap'?
- gigatexal 2y ago
  I think so but it’s overloaded and basically used for reporting and analysis stuff.
- systems 2y ago
  DSS became BI became AI At some point in time, Decision Support Systems became Business Intelligence and nowadays this is switching ti AI BI (formerly DSS) is a set of tools that enable Business Analytics , OLAP and technologies that implements OLAP (Cubes and Columnar databases) enables BI
- setr 2y ago
  bi tooling is usually report/analysis builders, intended to be consumed by.. business users. More importantly, they're usually also meant to be used by business users to build their own reports in the first place -- although in practice it often ends up going back to IT to maintain/update anyways. Basically anything that competes with excel pivot tables is a bi tool. OLAP/cubes usually underpins bi tooling, since the usecase is almost entirely across-the-board aggregations.
- marcosdumay 2y ago
  It basically means "report editors". And no, MS ones aren't miles ahead of the competition. But are bundled with Office, so the competition is mostly going out of business by now.
  - greggyb 2y ago
    Except Microsoft's report editor, Power BI, includes a powerful columnstore database that can handle importing and rapidly aggregating 100Ms or even 1Bs of records with query response times of just a couple seconds. That has been the differentiator. Power BI has lots going for and against it, but its database is miles ahead of competitors' report tools. Edit: clarification: importing large volumes of data takes more than a few seconds. The query performance of the in-memory database after import is complete is what I referred to above in my original comment.
gigatexal 2y ago
What do you love so about PowerBI? I’ve not looked at it very closely. I’ve worked with Tableau and Looker and LookerPro. All of which seemed fine. Is it a home run if the end users aren’t on Windows or using Excel? I’m thinking about the feature where you can use a SQLServer DB as a data source to Excel.
- gigatexal 2y ago
  I got my start in a SQLserver all MS shop. I hated SSIS. I wished I just had something akin to Python and dataframes to just parse things in a data pipeline. Instead I had some graphical tool whose error messages and deployments left a lot to be desired. But SQLServer and Microsoft in general make for good platforms for companies: they’re ubiquitous enough that you won’t find it hard to find engineers to work on your stack, there’s support, etc
  - wredue 2y ago
    SSIS is for integrations, and pandas is definitely not. I’m not sure what you’re trying to do with SSIS that you’re also doing with pandas, but it’s probably wrong. SSIS is far more geared to data warehousing integrations, while pandas would be reading a data warehouse and doing stuff with it. SSIS isn’t really meant for processing incoming data, even if you can kind of hack it together to do that. I will say that when we want “real time” integrations, SSIS is phenomenally bad. But that’s not entirely unexpected for what it is.
    - gigatexal 2y ago
      We don't need to be so pedantic. Python -- as it often is -- would be the glue, it would be the connecting part, and pandas (polars, duckdb, anything really) would be the processing part. Then once processed the outputs would be placed somewhere be it an update to a db table or some other downstream thing.
      - wredue 2y ago
        I was just saying you should most likely not be doing data processing with SSIS. That’s not what it’s for even if it can be cobbled in to doing some.
sroussey 2y ago
At least both of these databases support clustered indexes. For decades.
pphysch 2y ago
> And finally the GEM that is database projects, you can program your DB changes declaratively, there is nothing like this in the market and again, no one is even trying Automatic migration tools have essentially been doing this for a while (e.g. Django migrations). But I agree it would be nice if Postgres had better support built in.
- koolba 2y ago
  Database migrations to arrive at a target state are an interesting beast. It’s not just the destination that matters, it’s how you get there. The naive approach of applying whatever DDL you need to turn database schema A into B is not necessarily the one anybody would actually want to run, and in larger deployments it’s most definitely not the one you’d want to run. Intermediate availability and data migration go well beyond simply adding tables or columns. They’re highly application specific. The main improvements in this space are making existing operations CONCURRENT (e.g., like index creation) and minimizing overall lock times for operations that cannot (e.g., adding columns with default values).
  - pphysch 2y ago
    I agree automating migrations correctly from state A to B without any risk of data loss or downtime is essentially an intractable problem. Still, the popular tools seem to have good heuristics and good DBAs recognize when migrations must be written by hand.
  - tianzhou 2y ago
    (Bytebase co-founder) Bytebase implements this https://www.bytebase.com/docs/change-database/synchronize-schema/ https://www.bytebase.com/docs/change-database/synchronize-sc...
Guillaume86 2y ago
I fully agree on the sql project type, it's amazing, another great side benefit is intellisense on your db schema (find all references, etc), git blame on the schema is also great. Doesn't redgate offer kinda the same tool commercially with support for more than mssql? Never tried it but IIRC it looked similar...
tianzhou 2y ago
>> I really wish someone will implement DB Projects (dacpac) for Postgresql Bytebase did https://www.bytebase.com/docs/change-database/synchronize-schema/ https://www.bytebase.com/docs/change-database/synchronize-sc...

manishsharan 2y ago

MS Azure SQL on is very cheap compared to any other self hosted database including PG and MS SQL. Unless they running a super heavy workload , this solution will meet most business requirements. But this is also a gateway drug for cloud addiction.

kjax 2y ago
After scanning the Azure and AWS pricing pages and calculators for comparably speced Azure Manged SQL and Amazon RDS for PostgreSQL instances, AWS's RDS comes out at about 1/3 the price of Azure Managed SQL. This matches my experience with deployments on AWS and Azure. Both are managed db instances, but Azure's is consistently more expensive for comparable offerings on AWS or GCP.
- AlfeG 2y ago
  Is there any analog for Azure SQL Elastic Pools for Postgres anywhere? We pay in total something around 600 bucks to manage around 250 databases in MSSQL servers (with failover for prod databases, DTU based model) We pay for log analytics more then we pay for Sql Servers. Those Elastic Pools is a blocker for us on the way to migrate to Postgres from MSSQL...
  - ilkhan4 2y ago
    It's been a while since I've looked at elastic pools in Azure, but maybe Neon (https://neon.tech/ https://neon.tech/) or recently Nile (https://www.thenile.dev/ https://www.thenile.dev/) might work in terms of spinning up a bunch of separate logical DBs with shared compute/storage.
    - nikita 2y ago
      (neon ceo). We have lots of examples of this. Here is one with Retool. https://neon.tech/blog/how-retool-uses-retool-and-the-neon-api-to-manage-300k-postgres-databases https://neon.tech/blog/how-retool-uses-retool-and-the-neon-a...
    - clarkbw 2y ago
      (neon employee) and Neon will be available on Azure very soon ( https://neon.tech/blog/neon-is-coming-to-azure https://neon.tech/blog/neon-is-coming-to-azure )
- manishsharan 2y ago
  I do not have experience with AWS RDS so I cannot speak to that. In my experience, GCP Cloud SQL for Postgres has been more expensive than MS Azure SQL. In our tests, CloudSQL also was not comparable to the resiliency offered by Azure SQL. Things like Automated DR and automated failover etc. were not at par with what Azure offered. Not to mention , Column level encryption is standard for Azure SQL.

kragen 2y ago

does 'bi' mean 'olap'? because the literal expansion 'business intelligence' (or the explanation in the Wikipedia article) is hard to interpret as something that makes sense in contexts like this where you're apparently talking about features of software rather than social practices. the reference to 'bi cubes' makes me think that maybe it means 'olap'?

gigatexal 2y ago
I think so but it’s overloaded and basically used for reporting and analysis stuff.
systems 2y ago
DSS became BI became AI At some point in time, Decision Support Systems became Business Intelligence and nowadays this is switching ti AI BI (formerly DSS) is a set of tools that enable Business Analytics , OLAP and technologies that implements OLAP (Cubes and Columnar databases) enables BI
setr 2y ago
bi tooling is usually report/analysis builders, intended to be consumed by.. business users. More importantly, they're usually also meant to be used by business users to build their own reports in the first place -- although in practice it often ends up going back to IT to maintain/update anyways. Basically anything that competes with excel pivot tables is a bi tool. OLAP/cubes usually underpins bi tooling, since the usecase is almost entirely across-the-board aggregations.
marcosdumay 2y ago
It basically means "report editors". And no, MS ones aren't miles ahead of the competition. But are bundled with Office, so the competition is mostly going out of business by now.
- greggyb 2y ago
  Except Microsoft's report editor, Power BI, includes a powerful columnstore database that can handle importing and rapidly aggregating 100Ms or even 1Bs of records with query response times of just a couple seconds. That has been the differentiator. Power BI has lots going for and against it, but its database is miles ahead of competitors' report tools. Edit: clarification: importing large volumes of data takes more than a few seconds. The query performance of the in-memory database after import is complete is what I referred to above in my original comment.

gigatexal 2y ago

What do you love so about PowerBI? I’ve not looked at it very closely. I’ve worked with Tableau and Looker and LookerPro. All of which seemed fine. Is it a home run if the end users aren’t on Windows or using Excel? I’m thinking about the feature where you can use a SQLServer DB as a data source to Excel.

gigatexal 2y ago
I got my start in a SQLserver all MS shop. I hated SSIS. I wished I just had something akin to Python and dataframes to just parse things in a data pipeline. Instead I had some graphical tool whose error messages and deployments left a lot to be desired. But SQLServer and Microsoft in general make for good platforms for companies: they’re ubiquitous enough that you won’t find it hard to find engineers to work on your stack, there’s support, etc
- wredue 2y ago
  SSIS is for integrations, and pandas is definitely not. I’m not sure what you’re trying to do with SSIS that you’re also doing with pandas, but it’s probably wrong. SSIS is far more geared to data warehousing integrations, while pandas would be reading a data warehouse and doing stuff with it. SSIS isn’t really meant for processing incoming data, even if you can kind of hack it together to do that. I will say that when we want “real time” integrations, SSIS is phenomenally bad. But that’s not entirely unexpected for what it is.
  - gigatexal 2y ago
    We don't need to be so pedantic. Python -- as it often is -- would be the glue, it would be the connecting part, and pandas (polars, duckdb, anything really) would be the processing part. Then once processed the outputs would be placed somewhere be it an update to a db table or some other downstream thing.
    - wredue 2y ago
      I was just saying you should most likely not be doing data processing with SSIS. That’s not what it’s for even if it can be cobbled in to doing some.

sroussey 2y ago

At least both of these databases support clustered indexes. For decades.

pphysch 2y ago

> And finally the GEM that is database projects, you can program your DB changes declaratively, there is nothing like this in the market and again, no one is even trying Automatic migration tools have essentially been doing this for a while (e.g. Django migrations). But I agree it would be nice if Postgres had better support built in.

koolba 2y ago
Database migrations to arrive at a target state are an interesting beast. It’s not just the destination that matters, it’s how you get there. The naive approach of applying whatever DDL you need to turn database schema A into B is not necessarily the one anybody would actually want to run, and in larger deployments it’s most definitely not the one you’d want to run. Intermediate availability and data migration go well beyond simply adding tables or columns. They’re highly application specific. The main improvements in this space are making existing operations CONCURRENT (e.g., like index creation) and minimizing overall lock times for operations that cannot (e.g., adding columns with default values).
- pphysch 2y ago
  I agree automating migrations correctly from state A to B without any risk of data loss or downtime is essentially an intractable problem. Still, the popular tools seem to have good heuristics and good DBAs recognize when migrations must be written by hand.
- tianzhou 2y ago
  (Bytebase co-founder) Bytebase implements this https://www.bytebase.com/docs/change-database/synchronize-schema/ https://www.bytebase.com/docs/change-database/synchronize-sc...

Guillaume86 2y ago

I fully agree on the sql project type, it's amazing, another great side benefit is intellisense on your db schema (find all references, etc), git blame on the schema is also great. Doesn't redgate offer kinda the same tool commercially with support for more than mssql? Never tried it but IIRC it looked similar...

tianzhou 2y ago

>> I really wish someone will implement DB Projects (dacpac) for Postgresql Bytebase did https://www.bytebase.com/docs/change-database/synchronize-schema/ https://www.bytebase.com/docs/change-database/synchronize-sc...