Comment by aljgz

>> The developer experience around MSSQL is untouchable.

This may be the case for MS-centric, application & human developers, but I'm not convinced moving forward. Microsoft's BI story is pretty thin and out of date. Postgres has some solid columnar support/functions (which probably why Snowflake is writing about it) which means you can potentially use it for both you transactional and analytical workflows. As more development shifts to agentic workflows I'd bet Postgres shines when the overall ecosystem is more important than the human tools that were essential for the past 20 years. I loved Redgate's value-add but I don't think agents care about the UI which was the big win. SQL Server will continue to live in the enterprise andf where MS can sell lucrative support contracts or build for their clients, but I'm not seeing any net-new projects where the builders have any choice to not use SQL Server.

The MSSQL developer experience is ok, until you want to pipe the backup over ssh. Then you understand that the Linux support is a joke, with no stdout(https://linux.die.net/man/3/stdout) support.

>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.

Try Postgresql. I was previously SQL Server and the move has been great.

SSMS doesn't offer much over alternatives, both PGAdmin and others. VS is dying, VSCode is the future.

Redgate doesn't offer anything that is essential or not available elsewhere for postgresql

The money you spend on Microsoft licenses could be put towards more ram on the server.

Can you expand on what is better with MSSQL?

Litestream solves the backup issue. I use it and it’s great.

Clickhouse offers the same through https://clickhouse.com/docs/materialized-view/incremental-ma.... I personally LOVE this feature and concur with the gp that they're really elegant solutions to difficult problems.

Just to give an example, I ingest otel trace spans individually and in a materialized view calculate the total duration of the whole trace among other things.

Oracle makes their money by promising the C Suite "give us money and we'll solve your problems." There aren't actually many companies in a position to credibly make that promise.

The oracle database is also actually pretty nice, according to the people I've talked to who use it.

But mostly they play in the 'nobody ever got fired for suggesting' club. These days AWS is the most egregious landlord of that club, but whatever.

I think we take for granted how few databases ever outgrow vertical scaling needs.

Usually there will be one or two tables that grow at a dramatically faster rate than everything else and I have always found that those belong in a separate data store.

In OLTP, it's very difficult to guarantee correctness with triggers (very easy to have a race condition in concurrent environment). On a flip side, materialized views tend to lock more than you'd expect, especially when aggregates are involved.

The sweet spot is if you have a read-mostly database and use SNAPSHOT transaction isolation for the readers (which is SQL Server's implementation of MVCC). That way, writers may still block writers, but writers can never block readers, even when indexed views are being maintained.

Another neat trick is to "abuse" indexed views as multi-table CHECKs. The idea is to make a JOIN that would produce duplicated rows (and fail the indexed view's key) if some multi-table condition is not met.

Indexed views are much faster than trying to achieve the same result with triggers. Triggers have serious concurrency limitations, and you do recalculations even when the fields you depend on are not touched.

Indexed views are not much worse than indexes. Of course, when they refer to other tables there are underlying data lookups, but in our experience when we moved from triggers to indexed views, large scale data ingestion went way faster.

Where we used it: While revamping a large scale sales program, we stored the warehouse in/out in one table, and several things like current stock were calculated using indexed views.

Bonus: Using Snapshot concurrency control, you can do many things concurrently, and only when they both updates to a certain product in the same store you'll get the second transaction failing (which could be retried on the backend).

The fact that they are completely in-sync with your data is amazing.

These things exist to eliminate the risk of ever serving stale information from a materialised view. I.e., their benefit is political/reputational as much as they are technical in the sense that they save you effort like remembering to invalidate a MV after an ingest operation.

Stale MV is a thing you only ever burn your fingers on once. Like how "It's not DNS" is a common meme in networking.

Materialized views in pg with any incremental updates has timing inconsistencies that SQL Server doesn't have.

What are some of the add-ons that you run on the server? We run ours in a pretty bare-bones manner so I'm interested to hear what you're doing.

Agree about Windows Server. You can run SqlServer on Linux though. I'm not aware of your specific addons, but the Sql Server itself works perfectly well on Linux.

SSMS has been rebuilt as a framework within Visual Studio, now. It includes Copilot AI and a bunch of other niceties.

And although you didn't ask, I'll list what's wrong with Oracle. It's very simple.

Oracle treats empty strings as being NULL.

Anyone who's never used Oracle before in their life is probably wondering if I'm making it up. I'm not. In Oracle, inserting '' in a VARCHAR column is exactly the same as inserting NULL. And if there's a NOT NULL column, you're not allowed to store the empty string in there.

Which means that in Oracle, you do not have any way of distinguishing "I don't know the person's middle name" vs. "I know what the person's middle name is: he/she doesn't have one".

There are apparently historical reasons for this, but I don't care. The empty string is NOT the same as NULL, and any software that treats them as the same IS BUGGY!

Sorry. Had to get that off my chest. I know I'm answering a question you didn't ask, but that has been bothering me for nearly 25 years (I first learned about this misfeature of Oracle's in 2002 or 2003), and I just had to vent to somebody who would understand.

1. No transactional DDL

2. No MERGE statement

3. No partial indices

4. Many ways to lock out instant add table, meaning you can’t add a column without a full table write, which can lock the table for minutes at a time in even moderately sized tables.

5. Dealing with legacy mysql databases often means dealing with utf8mb3, which used to be the default utf8 data type despite not storing all utf8

6. Dealing with all but the most recent mysql databases means dealing with non strict mode which means your NOT NULL column won’t require a value.

Don't know of anything wrong with MariaDB, but there used to be plenty wrong with MySQL. To give the most egregious example (THANKFULLY fixed in MariaDB, but was present in MySQL for the longest time), inserting the value 128 into a TINYINT column (signed 8-bit int) would clamp the value rather than returning an error. Which might be what you want... except if that was a primary key column. Marvel at the following, which used to be how MySQL behaved:

Note: the below taken nearly verbatim from https://sql-info.de/mysql/referential-integrity.html#3_5

  CREATE DATABASE foo;
  USE foo;
  CREATE TABLE one ( id TINYINT NOT NULL PRIMARY KEY ) TYPE=InnoDB ;
  CREATE TABLE two (
    id TINYINT NOT NULL PRIMARY KEY,
    INDEX (id),
    CONSTRAINT id_fkey FOREIGN KEY (id) REFERENCES one(id)
  ) TYPE=InnoDB ;

Now that we've created both tables, let's insert a record into table one:

  INSERT INTO one VALUES (127);

And now let's insert a record with a different primary key into table two:

  INSERT INTO two VALUES (128);

MariaDB will give you an error at this point (ERROR 1264 (22003): Out of range value for column 'id' at row 1), but MySQL (at least back when I tried this about ten years ago, which was the last time I was forced to work with MySQL — and I am so glad I never have to go back!) would return no error message and just say "Query OK, 1 row affected (0.009 sec)".

Now let's select the value we inserted into table "two":

  SELECT * FROM two;

And what do we see? The value 127, even though we inserted 128. Which has created a foreign-key relationship to table "one" that we never intended to put in there.

There are other reasons why MySQL was inadequate, but I no longer remember them. Probably MariaDB has fixed them by now. But I no longer have to use MySQL/MariaDB for anything, and I never want to go back. I have a VERY strong averse reaction, caused by past pain, when I think of using MariaDB. (I actually spun up a virtual machine to test what I wrote here, because there's no way I was going to install MariaDB on my primary work machine).

Just a set of things too minor to move off of it but annoying enough to not want to start with it.

My list:

No `explain (analyze,buffers)`. Instant DDL has some warts (e.g. fk, metadata locks). Query planning bugs (actually... query planning in general is disappointing). Exiting the repl doesn't stop queries. Implicit type casting. Replication lag from large DDL (e.g. creating an index). Lack of two phase DDL (creating constraints NOT VALID and then VALIDATE later). Lack of extensions (e.g. pg_vector). No safe access to inspect buffer cache. AWS Aurora seems to only add shiny new things to Postgres. And more.

Again, none of this is quite enough to migrate off of it for an established system, but certainly enough to avoid it on a new project.

Last I looked, MariaDB still implemented JSON columns as LONGTEXT under the covers, making it a non-starter for any serious use of said type.