SQLite and MSSQL are my two solutions for relational storage problems.

If I am going to use a "free" provider, SQLite is impossible to beat. They cover a majority of use cases today. SQLite starts to fall apart with backup, replication and tooling. If I am on the hook for things like system availability and disaster recovery, I don't have a problem spending money to cover my ass.

If I am going to pay any amount of money at all, I am going all the way. The developer experience around MSSQL is untouchable. SSMS and VS with sql projects runs circles around contemporary entity framework crap. Sprinkle in 3rd party tools from vendors like RedGate and you can replace multi-million dollar consulting packages.

I wouldn't ever advocate for standing up a new Oracle or DB2 machine, but if one was already in place I'd probably die on the hill of not trying to refactor it away. These databases typically come with multi-volume ghost stories attached. Reinventing all those weird effects on a new engine will typically kill the business if there are no other options available.

I think even Microsoft has abandoned SQL Server and spends more time improving their various Postgres Azure offerings. Their last major version release since 2022 added some AI features and thats it.

As a DBA, who does a lot of very heavy DBA like stuff, Postgres is in another league from SQL Server. Because Postgres is linux native and open source, its flexibility, introspection and operability, just doesnt have a comparison in SQL Server.

IMO, in the current tech landscape. SQL Server is essentially dead. Only companies using it are legacy windows shops which there are fewer and fewer of.

> Synchronously updated materialized views...

Oh yes, I'd love them too (if you're referring to, in Oracle slang, "...update on commit") - and it would be cool to have as well the option for a lazy update ("on demand" by taking into consideration only the records that have been changed since the last refresh, to handle multiple updates in a single pass - not sure how Oracle can achieve that technically...). This would be in my opinion a fantastic added functionality compared to basically all other (OLTP?) opensource DBs.

And: I'm really curious about the "OrioleDB" project... ( https://github.com/orioledb/orioledb/releases ) as a few years ago I was struggling a lot with "vacuum" of a kind-of-temporary table that had quite high amounts of continuous random inserts & deletes (problem solved by accumulating more changes in RAM before flushing them to the table therefore increasing amount of rows changed per "page", but I had to sweat a lot to find a good balance...).

Oracle = Pain, Suffering, High Costs, Litigation, and Human Misery. If it wasn't for non-technical middle management that likes the perks of buying high cost software from vendors that throw nice parties they'd be out of business.

Postgresql is the better product, but doesn't have the horizontal scaling of MySQL/Maria though, so if you want an easy to setup cluster MySQL for high volume online retail store or similar has a use case still.

> 2. Synchronously updated materialized views (Sql Server calls them indexed views). These are incredible tools in complex data situations. I saw a project struggle with complex technical implementations that would be elegant, trivial and always correct with indexed views.

I believe you can do this with the pg-trickle extension.

https://github.com/trickle-labs/pg-trickle

> lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurrent connection.

Pg connections are definitely heavy, but usually on resources other than memory in my experience. If you configure reasonable dirty reclamation and recycling, the memory numbers are often overstated due to Linux tools’ deceptive fork accounting and shared buffers. Ofc, if you’re averaging lots of heavy queries per connection it’ll be truly heavy, but many times the numbers overstate the impact.

Care to share some examples where SQL Server's indexed views would shine?

In my eyes they're similar to triggers, which incur a high performance overhead in OLTP systems and are shunned by developers. In OLAP systems custom ETL code will likely outperform them.

two techniques I use with pg:

1. "materialize" the view as a full table, then index that. Any reasonable pipeline/ETL tool can provide incremental updates between tables. Obviously, anything materialized requires considerations around storage, replication, backup/restore, I/O, etc.

2. use a regular VIEW and index (precisely) the underlying expressions mentioned in the view, i.e. so when the view is used, then the indexes get used.

Both require rewriting SQL, though I've used VIEWs to make the change transparent.

I am currently fighting my way off SQL Server towards PostgreSQL.

Windows Server is a real pain to operate and the SQL Server ecosystem expects you to run a lot of add-ons on the server alongside your database. Those don’t translate to managed database services, so you lose a lot of functionality if you jump to RDS or similar.

The first party tools are also aging poorly. SSIS and SSRS are not fun. SSMS is ok for what it is but can’t compete with the ecosystem around PostgreSQL.

Maybe I’m missing something but I can’t wait to ditch it.

>I do myself a favor and always avoid Oracle and MySql/MariaDB.

So what's wrong with MySQL or MariaDB?

What's wrong with MariaDB?

I also miss clustered indexes, datetimeoffest, plan caching and query hints from MSSQL.