Let’s start with the summary and work backwards:
If you’re porting code from Microsoft SQL Server to Postgres, the big surprises are that Postgres stored procedures don’t return result sets. In Postgres, if you wanna return rows, you use a function – which you can also do in Microsoft SQL Server, of course.
It’s one thing to say, “Just convert your Microsoft stored procedures over into functions,” but that means you also have to change the code that calls them, because Microsoft’s T-SQL stored procs are called like this:
|
1 |
EXEC dbo.MyStoredProc @Param1 = 1234, @Param2 = 'abcde'; |
Whereas functions are called in SELECT statements. Furthermore, if you had a single T-SQL stored proc that returns multiple result sets, you’re going to have to refactor that into multiple Postgres functions.
If you’re porting code from Postgres to Microsoft SQL Server, the big surprises are that T-SQL functions aren’t allowed to modify data, nor do they accept multiple languages. Since Postgres started with functions first and added procs later (v11 in 2018), Postgres developers tend to treat functions as first class citizens, and stored procs as second. It’s the opposite in the Microsoft SQL Server world.
Migrating databases is hard.
I’ve seen so many SQL Server shops say they’re interested in migrating their apps over to Postgres back ends in order to save money. If your app purely uses select, insert, update, and delete, then sure, you might stand a chance.
The more SQL features you use, though – and I’m just talking about language features, not platform features – the more surprises you’re going to hit, and the surprises are pretty labor-intensive and expensive. Just this one surprise alone makes migration massively expensive because it means significant application changes for any code that calls SQL Server stored procs or Postgres functions.
Commenters are currently firing up their keyboards to tell me, “All you have to do is ___ and it’ll work better,” but the reality is that ___ costs developer time and effort, plus testing for the migrated code. There’s no set-it-and-forget-it way to move complex enterprise apps across.
2 Comments. Leave new
We made the change from SQL to Postgres but not without a lot of pain. Licensing cost changes were the driving factor.
Our application is very database heavy by which I mean a lot of stored procedures doing a lot of work. Service Broker had to be written into the application functionality. The use of temp tables (which we did a ton of of) had to be refactored out of our procedures (temp tables do not work well in Postgres at our scale). And there other performance issues that are constantly being addressed.
New tools: SSMS to DBeaver, lack of profiling tools and virtually no query plan analysis tools (Come SQL Sentry/Solarwinds…make one for Postgres please!!).
And then porting production data from SQL to Postgres….we had to build our own tool…datatypes aren’t quite the same either (no such thing as a tiny int on Postgres)…And DATES and DATETIMES!!..brutal…..We would have loved to convert everything to UTC but reality is you can’t.
And then to top it off, potential mistakes or inadvertent resyncs of data from SQL to Postgres. Now you are relying on piecing back data that might have gotten wiped out.
And then build pipeline tooling. Red Gate made it easy for SQL..not so much now….
It took 18 months but we made it.
Yep, a lot of what you’re describing here resonates with me and the clients I’ve seen trying to make transitions. It’s really expensive.