4 years running postgresql on arm in production with 0 issues. (2tb database so kinda small, a lot of indexes tho, a lot of which are on jsonb columns) I can’t think of any feature in sql server I would ever need that PostgreSQL doesn’t have. But sql servers lack of JSON support is a deal breaker for me.
We had issues with postgres on x64 when changing underlying OS, maybe you didn't have to.
Can you encrypt columns or compress data with vanilla postgresql? Why not?
Postgresql is REALLY great, PostGis is awesome, it's not perfect and other RDBMS may have other useful features.
SQL Server has had JSON column types (and supporting functions) since 2016.
I don't know you or your workload, so I can't comment on its suitability for you. I would hazard a guess, though, that your last proper due diligence knowledge is seven years out of date, or more.
You index JSON columns in SQL Server by creating an index on a computed column. I am very open to the strict argument that this is a workaround if you'd like to make that more verbosely. Nevertheless it is very effective and adds minimal IO overhead and is dispatched with one extra line of code (more if you prefer newline-heavy formatting). From a practical perspective this has never been a sticking point for any of numerous clients I have seen using the features, and the performance is as good as any other btree index.
You are correct that there is no formal SQL type in SQL Server for JSON. And I am sorry for implying otherwise. Type safety requires a constraint on the column intended to hold JSON.
There is no equivalent to Postgres's GIN indices which would allow indexing on an array in the JSON column. Such a requirement would need a normalized table holding the array's values in SQL Server. Whether this is a limitation or a lack of support for JSON, full stop, seems to me a matter open to debate.
I have seen many successful projects (and participated in quite a few of those) that utilize JSON in SQL Server databases. I will amend my former statement, though, because it obviously lacked nuance: SQL Server's JSON functionality has covered all use cases that I have had and personally seen, but my experience is obviously much lesser than some others', so you can take this experience with as much salt as you like (:
SQL Server operations for JSON are parsed from text content on demand. In other databases, the JSON content is broken apart and can be queried/indexed on that parsed/stored content. The closest you can get with SQL is a computed column from the JSON that you can then use to index against, it's very messy by comparison. If you use a lot of JSON queries in SQL server it can get excessively sluggish as well compared to PostgreSQL or even MySQL/MariaDB.
It's definitely a shortcoming of MS-SQL, which iirc is being addressed to an extent in the next release.
There are plenty of points where MS-SQL Server is very nice. JSON support is far from one of them. Even if the JSON is still a step above XML support in SQL Server.
