5 ms·

I so badly wish we could change a big Windows business application to use PostgreSQL rather than Microsoft SQL Server just because of the licensing costs. SQL

by m_st 2y ago

I so badly wish we could change a big Windows business application to use PostgreSQL rather than Microsoft SQL Server just because of the licensing costs. SQL Server is a fantastic product, but restricted to 128GB RAM and a few CPU cores or you have to start paying so much, that not even our biggest customers can justify it. Migration isn't easy, as this venerable application uses ADO.NET Datasets with TableAdapters and plenty of stored procedures. The syntax is almost compatible though. But not enough unfortunately. For our next product, we're sure to bet on PostgreSQL instead.

kylecordes 2y ago
Yes, this is such a big challenge for commercial database products. PG is so great that it often makes the most sense to reallocate the money to more CPU/RAM/SSD, rather than licenses.
- scarab92 2y ago
  I just wish it had columnstore support.
  - kdmtctl 2y ago
    Citus can do this. Postgres is good but extensibility makes it best.
pmarreck 2y ago
LLM's might be able to translate the stored procedure code without too much difficulty, and assuming you have test coverage. Might also do a good job of translating DDL code such as index definitions etc. I too was impressed with SQL Server last time I used it (big note: SQL Server is one of the few commercial DB's that does real nested transactions; PG does not), but I get it.
- werdnapk 2y ago
  What's a real nested transaction? What's postgresql missing? https://www.postgresql.org/docs/current/subxacts.html https://www.postgresql.org/docs/current/subxacts.html
  - pmarreck 2y ago
    Savepoints are not considered legitimately isolated nested transactions. To the best of my knowledge. Note: Am not a DBA, merely a full stack dev that has worked with various databases over the years.
  - pmarreck 2y ago
    Had a fascinating conversation with Claude about this (don't worry about the unhinged personality I gave it with preprompting): https://gist.github.com/pmarreck/970e5d040f9f91fd9bce8a4bceee6972 https://gist.github.com/pmarreck/970e5d040f9f91fd9bce8a4bcee... Pasted it there for brevity here. Also includes an easy way to export a Claude conversation (made with the help of Claude, of course).
    - jiggawatts 2y ago
      What on Earth are you using as your system prompt!!?
      - pmarreck 2y ago
        LOL, it's amazing
    - ants_a 2y ago
      LLMs are not a great source for this type of information. This mess in no way articulated in what way savepoints and transactions are different. Savepoints are fully equivalent to nested transactions with the constraint that only one is concurrently active. This limitation is just from the SQL standard programming model. At least in PostgreSQL implementation it would be reasonably simple to support multiple concurrent subtransactions. They are even called subtransactions internally.
  - 2y ago
- BrentOzar 2y ago
  > SQL Server is one of the few commercial DB's that does real nested transactions Not sure where this myth keeps coming from, but no, it does not: https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/ https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a...
  - pmarreck 2y ago
    Interesting. Is this still up-to-date? The link you posted is 15 years old
    - BrentOzar 2y ago
      Yep, nothing's changed there around transactions.
- dspillett 2y ago
  > SQL Server is one of the few commercial DB's that does real nested transactions More or less. The default ROLLBACK behaviour is to roll back the whole nest. You have to use SAVE TRANSACTION instead of BEGIN TRANSACTION and specify the name in ROLLBACK. If doing this in a procedure that may or may not be part of a nested transaction (an explicit transaction wasn't started before it was called) you have to test to see if you need to BEGIN or SAVE¹ and what to do if a rollback is needed (you likely don't want to ROLLBACK everything if you used SAVE, but have no option if you used BEGIN). Not exactly intuitive. And saved transactions can cause excessive lock escalation, impacting concurrent performance. SQL Server is generally a damned fine product overall, both generally and compared to a lot of other things emitted by Microsoft, but it isn't even close to perfect in some areas. ---- [1] ref: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver16#examples https://learn.microsoft.com/en-us/sql/t-sql/language-element...
  - pmarreck 2y ago
    Thanks for the clarification. I'm not one to compliment Microsoft software products, but Analysis Services and SQL Server, at least when I used them at the time over 10 years ago, seemed like darn fine products (which they purchased from someone else, I believe...)
    - dspillett 2y ago
      SQL Server itself came from Sybase originally, with them and MS working on it as partners for a number of years when it was an OS/2 product, so it wasn't a purchase-a-working-product-and-rebrand-it deal. As Windows NT gained ground, MS & Sybase parted ways (amicably IIRC, when the partnership agreements expired MS licensened the source that Sybase owned and bought out exclusive use of the product name in relation to products for Windows – for a time MS's SQL Server still carried Sybase copyright messaging along with MS's own). SQL Server v7 was a rewrite of significant parts of the internals, so from that point it is probably fair to call it an MS product quite distinct from Sybase's (which itself continued to be separately developed including significant but different improvements/reengineering of the internals), though still showing some signs of its heritage.
- cpursley 2y ago
  You're getting downvoted by gatekeepers but LLMs are amazing with SQL and this is a great use-case.
  - hnthrow90348765 2y ago
    Anecdata, but I agree. I went from a recursive CTE (that occasionally would take 1GB+ of memory for zero rows) to a loop and used AI to do it, and it did a pretty good job
  - pmarreck 2y ago
    Possibly related, talked to an LLM about the whole "who actually does nested transactions" thing and commented about it above: https://news.ycombinator.com/item?id=42702610 https://news.ycombinator.com/item?id=42702610
belter 2y ago
Test this: "Goodbye Microsoft SQL Server, Hello Babelfish" - https://aws.amazon.com/blogs/aws/goodbye-microsoft-sql-server-hello-babelfish/ https://aws.amazon.com/blogs/aws/goodbye-microsoft-sql-serve... Or the Opensource project: https://babelfishpg.org/ https://babelfishpg.org/
- bdcravens 2y ago
  I evaluated this for our database and ran into many cases where it wasn't a 1:1 replacement, especially if you lean heavily on stored procedures. Additionally, our db library (it's a Rails app; the database was originally used with another language) needed features Babelfish didn't support. It may be worth another look for us, but I assume it still shouldn't be considered a 100% drop-in.
chasil 2y ago
An enormous flaw of Sybase/Microsoft SQL Server is that it does not implement the SQL/PSM standard. https://en.wikipedia.org/wiki/SQL/PSM https://en.wikipedia.org/wiki/SQL/PSM The syntax for this does come largely from Oracle. "SQL/PSM is derived, seemingly directly, from Oracle's PL/SQL. Oracle developed PL/SQL and released it in 1991, basing the language on the US Department of Defense's Ada programming language." In any case, I have thousands of lines of PL/SQL written by many people which are currently useless for SQL Server applications. SQL Server should implement SQL/PSM. The sooner, the better. For those trying to escape the licensing costs of SQL Server, Babelfish may be an option. https://babelfishpg.org/ https://babelfishpg.org/
- munchler 2y ago
  Sybase SQL Server hasn't been a thing for at least a decade. The Microsoft fork happened more than 30 years ago.
  - sbuttgereit 2y ago
    And yet.... it lives still.... https://www.sap.com/products/technology-platform/sybase-ase.html https://www.sap.com/products/technology-platform/sybase-ase.... https://en.wikipedia.org/wiki/Adaptive_Server_Enterprise https://en.wikipedia.org/wiki/Adaptive_Server_Enterprise
  - deleted 2y ago
    [deleted]
- hodgesrm 2y ago
  > An enormous flaw of Sybase/Microsoft SQL Server is that it does not implement the SQL/PSM standard. Why is this a problem? I've always enjoyed T-SQL. Right from the start it had a "scripty" feel and stored procedures were easy to code with it. We thought about implementing PSM at Sybase in the 1990s but there was little user demand. (Unlike row level locking, the lack of which undid Sybase SAP implementations...) Internally many of the engineers thought PSM was pretty awful to use. I never liked it myself.
masfoobar 2y ago
> I so badly wish we could change a big Windows business application to use PostgreSQL This ^^^ A small businness I (no longer) work for was using Windows Servers, SQL Server, Classic ASP, .NET and other things. It was expensive! I tried sooo hard to migrate to get them to realise the savings moving over to Linux and Postgres, and get their DATED software over over afterwards! Well, it was Linux and MySQL/MariaDB but I have slowly grown fond of Postres over the last couple of years. I will always remember (and find funny) when we purchased a server a third-party no longer wanted to support for us anymore (linux+php) and my boss said "they only pay £300 a year for that server" -- yep.
cheema33 2y ago
We are in the same boat. An older application with lots of dependencies on MS SQL Server. We are slowly chipping away at it with our migration effort.
accra4rx 2y ago
Go Babelfish

by m_st 2y ago

kylecordes 2y ago

Yes, this is such a big challenge for commercial database products. PG is so great that it often makes the most sense to reallocate the money to more CPU/RAM/SSD, rather than licenses.

scarab92 2y ago
I just wish it had columnstore support.
- kdmtctl 2y ago
  Citus can do this. Postgres is good but extensibility makes it best.

pmarreck 2y ago

LLM's might be able to translate the stored procedure code without too much difficulty, and assuming you have test coverage. Might also do a good job of translating DDL code such as index definitions etc. I too was impressed with SQL Server last time I used it (big note: SQL Server is one of the few commercial DB's that does real nested transactions; PG does not), but I get it.

werdnapk 2y ago
What's a real nested transaction? What's postgresql missing? https://www.postgresql.org/docs/current/subxacts.html https://www.postgresql.org/docs/current/subxacts.html
- pmarreck 2y ago
  Savepoints are not considered legitimately isolated nested transactions. To the best of my knowledge. Note: Am not a DBA, merely a full stack dev that has worked with various databases over the years.
- pmarreck 2y ago
  Had a fascinating conversation with Claude about this (don't worry about the unhinged personality I gave it with preprompting): https://gist.github.com/pmarreck/970e5d040f9f91fd9bce8a4bceee6972 https://gist.github.com/pmarreck/970e5d040f9f91fd9bce8a4bcee... Pasted it there for brevity here. Also includes an easy way to export a Claude conversation (made with the help of Claude, of course).
  - jiggawatts 2y ago
    What on Earth are you using as your system prompt!!?
    - pmarreck 2y ago
      LOL, it's amazing
  - ants_a 2y ago
    LLMs are not a great source for this type of information. This mess in no way articulated in what way savepoints and transactions are different. Savepoints are fully equivalent to nested transactions with the constraint that only one is concurrently active. This limitation is just from the SQL standard programming model. At least in PostgreSQL implementation it would be reasonably simple to support multiple concurrent subtransactions. They are even called subtransactions internally.
- 2y ago
BrentOzar 2y ago
> SQL Server is one of the few commercial DB's that does real nested transactions Not sure where this myth keeps coming from, but no, it does not: https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/ https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a...
- pmarreck 2y ago
  Interesting. Is this still up-to-date? The link you posted is 15 years old
  - BrentOzar 2y ago
    Yep, nothing's changed there around transactions.
dspillett 2y ago
> SQL Server is one of the few commercial DB's that does real nested transactions More or less. The default ROLLBACK behaviour is to roll back the whole nest. You have to use SAVE TRANSACTION instead of BEGIN TRANSACTION and specify the name in ROLLBACK. If doing this in a procedure that may or may not be part of a nested transaction (an explicit transaction wasn't started before it was called) you have to test to see if you need to BEGIN or SAVE¹ and what to do if a rollback is needed (you likely don't want to ROLLBACK everything if you used SAVE, but have no option if you used BEGIN). Not exactly intuitive. And saved transactions can cause excessive lock escalation, impacting concurrent performance. SQL Server is generally a damned fine product overall, both generally and compared to a lot of other things emitted by Microsoft, but it isn't even close to perfect in some areas. ---- [1] ref: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver16#examples https://learn.microsoft.com/en-us/sql/t-sql/language-element...
- pmarreck 2y ago
  Thanks for the clarification. I'm not one to compliment Microsoft software products, but Analysis Services and SQL Server, at least when I used them at the time over 10 years ago, seemed like darn fine products (which they purchased from someone else, I believe...)
  - dspillett 2y ago
    SQL Server itself came from Sybase originally, with them and MS working on it as partners for a number of years when it was an OS/2 product, so it wasn't a purchase-a-working-product-and-rebrand-it deal. As Windows NT gained ground, MS & Sybase parted ways (amicably IIRC, when the partnership agreements expired MS licensened the source that Sybase owned and bought out exclusive use of the product name in relation to products for Windows – for a time MS's SQL Server still carried Sybase copyright messaging along with MS's own). SQL Server v7 was a rewrite of significant parts of the internals, so from that point it is probably fair to call it an MS product quite distinct from Sybase's (which itself continued to be separately developed including significant but different improvements/reengineering of the internals), though still showing some signs of its heritage.
cpursley 2y ago
You're getting downvoted by gatekeepers but LLMs are amazing with SQL and this is a great use-case.
- hnthrow90348765 2y ago
  Anecdata, but I agree. I went from a recursive CTE (that occasionally would take 1GB+ of memory for zero rows) to a loop and used AI to do it, and it did a pretty good job
- pmarreck 2y ago
  Possibly related, talked to an LLM about the whole "who actually does nested transactions" thing and commented about it above: https://news.ycombinator.com/item?id=42702610 https://news.ycombinator.com/item?id=42702610

belter 2y ago

Test this: "Goodbye Microsoft SQL Server, Hello Babelfish" - https://aws.amazon.com/blogs/aws/goodbye-microsoft-sql-server-hello-babelfish/ https://aws.amazon.com/blogs/aws/goodbye-microsoft-sql-serve... Or the Opensource project: https://babelfishpg.org/ https://babelfishpg.org/

bdcravens 2y ago
I evaluated this for our database and ran into many cases where it wasn't a 1:1 replacement, especially if you lean heavily on stored procedures. Additionally, our db library (it's a Rails app; the database was originally used with another language) needed features Babelfish didn't support. It may be worth another look for us, but I assume it still shouldn't be considered a 100% drop-in.

chasil 2y ago

An enormous flaw of Sybase/Microsoft SQL Server is that it does not implement the SQL/PSM standard. https://en.wikipedia.org/wiki/SQL/PSM https://en.wikipedia.org/wiki/SQL/PSM The syntax for this does come largely from Oracle. "SQL/PSM is derived, seemingly directly, from Oracle's PL/SQL. Oracle developed PL/SQL and released it in 1991, basing the language on the US Department of Defense's Ada programming language." In any case, I have thousands of lines of PL/SQL written by many people which are currently useless for SQL Server applications. SQL Server should implement SQL/PSM. The sooner, the better. For those trying to escape the licensing costs of SQL Server, Babelfish may be an option. https://babelfishpg.org/ https://babelfishpg.org/

munchler 2y ago
Sybase SQL Server hasn't been a thing for at least a decade. The Microsoft fork happened more than 30 years ago.
- sbuttgereit 2y ago
  And yet.... it lives still.... https://www.sap.com/products/technology-platform/sybase-ase.html https://www.sap.com/products/technology-platform/sybase-ase.... https://en.wikipedia.org/wiki/Adaptive_Server_Enterprise https://en.wikipedia.org/wiki/Adaptive_Server_Enterprise
- deleted 2y ago
  [deleted]
hodgesrm 2y ago
> An enormous flaw of Sybase/Microsoft SQL Server is that it does not implement the SQL/PSM standard. Why is this a problem? I've always enjoyed T-SQL. Right from the start it had a "scripty" feel and stored procedures were easy to code with it. We thought about implementing PSM at Sybase in the 1990s but there was little user demand. (Unlike row level locking, the lack of which undid Sybase SAP implementations...) Internally many of the engineers thought PSM was pretty awful to use. I never liked it myself.

masfoobar 2y ago

> I so badly wish we could change a big Windows business application to use PostgreSQL This ^^^ A small businness I (no longer) work for was using Windows Servers, SQL Server, Classic ASP, .NET and other things. It was expensive! I tried sooo hard to migrate to get them to realise the savings moving over to Linux and Postgres, and get their DATED software over over afterwards! Well, it was Linux and MySQL/MariaDB but I have slowly grown fond of Postres over the last couple of years. I will always remember (and find funny) when we purchased a server a third-party no longer wanted to support for us anymore (linux+php) and my boss said "they only pay £300 a year for that server" -- yep.

cheema33 2y ago

We are in the same boat. An older application with lots of dependencies on MS SQL Server. We are slowly chipping away at it with our migration effort.

accra4rx 2y ago

Go Babelfish