[aljgz]

I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player.

These days, I do myself a favor and always avoid Oracle and MySql/MariaDB.

Postgres is amazing, and the two big things I wished it had:

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

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.

Sql Server can be costly, but in many cases the benefits it provides are totally worth the cost.

Choosing the data store carefully prevents lots of future trouble.

[bob1029]

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.

[skeeter2020]

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

[khurs]

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

[saila]

> VS is dying

I don't think this is true at all. If anything, it's the opposite, in that MS has greatly improved recent versions of Visual Studio.

> VSCode is the future

Maybe for some types of projects, but I have both VS and VS Code on my work machine and hardly ever use VS Code. There's just no comparison when working on .NET projects where VS is the clear winner.

[pjmlp]

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

Only for UNIX minded folks.

Have you ever debugged stored procedures in PGAdmin, by the way it is still a Electron junk app?

[colechristensen]

Can you expand on what is better with MSSQL?

[bob1029]

One of the biggest advantages is that a lot of people in the business are already comfortable with the ecosystem. I know that from the perspective of HN that SSMS, pgAdmin, DBeaver, DB Browser for SQLite, et. al. are mostly isomorphic, but from the perspective of everyone else in the business, these are substantially different things to think about.

Whether the popularity of the MS ecosystem is good or not is a separate problem. If we are solving for "make the business go well and I get paid more", the strategy is usually obvious. We can still advocate for OSS and not-so-many-eggs in the Microsoft basket while we get paid for using these technologies. Again, SQLite is the preferred engine in my tool belt. I don't want to have to manage a hosted sql machine. But, sometimes the problem absolutely insists upon it.

Also, if you are using C#/.NET, integrating with MSSQL is always a little bit easier than the other providers. SQLite (and some others) have lackluster types for things like time. MSSQL has DateTime2 and DateTimeOffset that map exactly into the CLR types.

[Grombobulous]

I don’t mean to be annoyingly contrarian or argumentative with this statement: I’m definitely not convinced by both of your comments on this subject.

“Comfort” is not really a tangible benefit.

For example, I am personally very comfortable with Chef software for configuration management of VM-deployed applications, but I am quite aware that my own comfort doesn't make it a superior solution versus containerizing applications. We just have Chef around because that’s what was chosen 10 years ago.

Obviously, developer/ops familiarity is still valuable, I don’t mean to say it’s not, but there is a point where the band-aid needs to be ripped off.

I have a hunch that all these great tools that make MS SQL convenient are more like band-aids to a mediocre experience. E.g., RedGate replacing multi-million dollar consultancies sounds great, but what’s even better is not needing a multi-million dollar consultancy in the first place.

I will also give you the context that I used to be a defacto DBA for MS SQL. That doesn’t necessarily mean I know what I’m talking about, but I do know that I personally do not miss SSMS or any of the Microsoft ecosystem.

The whole vibe of that ecosystem is “put your life in Microsoft’s hands and do everything Microsoft’s way and we hold your hand and make it easy, except that making everything easy for everyone causes everything to be massively complicated and brittle.”

I still occasionally get StackOverflow notifications for an answer I wrote which was just “try restarting Visual Studio” and even though the version I wrote it for is over a decade old I still get comments that say “this worked for me.”

[colechristensen]

"It's better because Microsoft makes it and people in the MS Tech Bubble prefer MS things" is not a convincing argument.

It is in fact a detractor because inviting a tool from the MS fandom invites people from the MS fandom who want to change out everything for the Microsoft version based only on who made it.

[olavgg]

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.

[bendangelo]

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

[pbronez]

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.

[hotsauceror]

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.

[FuriouslyAdrift]

The linux version runs very cleanly, too.

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux...

[aljgz]

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.

[FuriouslyAdrift]

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

[joinjune]

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.

[cduzz]

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.

[zepearl]

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

[gandreani]

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.

[ddorian43]

Its not transactional or atomic.

[khurs]

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.

[kev009]

This hasn't been true for quite a long time, because it's domain of other products for both.

i.e. MySQL/Maria's MM is one of the worse options compared to anything else.. like TiDB or Aurora on AWS for MySQL compat and Yugabyte or CockroachDB for Postgres compat (or Aurora on AWS or AlloyDB on GCP).

[brightball]

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.

[roncesvalles]

You need replication for HA. Otherwise the only HA you have is the RAID setup on the one machine. The reason people go horizontal is rarely scale. And if you're doing that you might as well use the passive replica as a read replica.

[brightball]

Read replication is easy.

Horizontal scaling for DB typically means distributing the write load across instances either via sharding or master-master. I could be wrong of course, but that’s how I read it.

MySQL is very good at master-master out of the box.

[roncesvalles]

To be pedantic, master-master implies the same row can be committed to at the same time at different master nodes (without an immediate causal connection between the two). Very few databases allow this.

[timacles]

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.

[zbentley]

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

[SigmundA]

Each PG connection being a whole process does not scale like MSSQL that uses a thread per connection which has a max of 32k per instance.

There are no need for connection poolers in front of MSSQL although it is normal to pool connections in the client application which may hold hundreds open typically in a web server.

This also allows MSSQL to more easily share cached query plans between connections since its just sharing executable code between threads.

For PG to do plan caching it would need to serialize the plan between processes and that would require some significant work since it was never designed that way.

PG has it obvious unix roots using processes instead of threads, MSSQL coming from Windows where new process are expensive and there was no real fork, but threads are cheap uses that approach instead.

[CraigJPerry]

>> Each PG connection being a whole process does not scale like MSSQL that uses a thread per connection

There's no free lunch id think, the PG model is more robust. Unsafe extensions can take down the whole instance in the threaded model, processes contain the blast radius to that connection (also typically easier to debug since this type of issue is thankfully rare, it's also gnarly to get on top of).

Further, on linux (not on windows) a lot of the lines between a thread and a process get blurry (copy on write, shared memory mappings etc). They're both handled very similarly in the kernel, theyre both scheduled using similar machinery.

>> For PG to do plan caching it would need to serialize the plan between processes and that would require some significant work since it was never designed that way.

Is that true? I'm thinking the buffer cache and locks and WAL coordination are just as fast - it's just mmap'd SHM into each process. It's not like every access needs IPC?

[SigmundA]

>Unsafe extensions can take down the whole instance in the threaded model, processes contain the blast radius to that connection.

MSSQL has its own task scheduler with the ability to kill etc, its like its own OS internally in that respect but yes there is no memory protection there for unmanaged code.

It doesn't really have unsafe extensions other than it is possible to create extended stored procedures which could do bad things, very frowned upon. If you were concerned about that you would run that database in its own instance (process).

https://learn.microsoft.com/en-us/sql/relational-databases/s...

https://learn.microsoft.com/en-us/sql/t-sql/language-element...

https://learn.microsoft.com/en-us/sql/relational-databases/e...

>I'm thinking the buffer cache and locks and WAL coordination are just as fast - it's just mmap'd SHM into each process.

Plan caches contain pointers to executable code and sometimes jitted code, this cannot be just shared to another process with a different address space and work, this is code not data, to transport between processes it needs to be serialized in a way that is not tied to process memory including things like ASR, certainly doable but since it wasn't designed that way much needs to be changed vs just sharing between threads.

https://en.wikipedia.org/wiki/Position-independent_code

[d125q]

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.

[aljgz]

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.

[switchbak]

PG has had incremental view maintenance on the horizon for many years. I expect it to remain on the horizon for a long time.

What you're describing is amazing, and I wish I had it available to us. We've hand rolled far too many triggers to achieve the same thing, with all the expected problems you'd assume. I'm sure it could be abused/misused, but a batteries-included approach like that would be huge.

[munk-a]

If you are hand-rolling them then I might suggest https://github.com/trickle-labs/pg-trickle#pg_trickle which at least reduces the labor involved in trying to get it up and running and appears to address a lot of the correctness concerns I've traditionally had (like a sync not firing correctly due to a mid-operation outage).

[branko_d]

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.

[mickeyp]

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.

[asah]

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.

[ballon_monkey]

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

[brightball]

> 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

[ksec]

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

So what's wrong with MySQL or MariaDB?

[rmunn]

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.

[SigmundA]

Interesting I have definitely used the distinction between null and empty string quite a bit in MSSQL and matches most programming languages.

Another issue that used to exist was Oracle table name were limited to like 12 characters or something so I have seen horrible abbreviated table names to fit in the Oracle limitation even in other DB's due to some systems being able to run on both MSSQL and Oracle even though SQL Server has a 255 char limit for table names.

[kjs3]

I'll list what's wrong with Oracle

Very interesting (and hopefully cathartic). I never got past "it involves doing business with Oracle".

[johnisgood]

> Oracle treats empty strings as being NULL.

That is ridiculous. Do not they know the difference between "" and NULL?

[rmunn]

I'm pretty sure the historical reasons I mentioned involved creating the software before the SQL standard came out, meaning they were treating NULL as meaning "nothing", instead of meaning "unknown" as the SQL standard requires. But that's as far as I care to go digging into a database system I will never use of my own free will.

[ksec]

>Sorry. Had to get that off my chest. I know I'm answering a question you didn't ask,

Not at all. Thank You for sharing it and didn't put another morale rant about Oracle. Hopefully someone reads it and provide some interesting back stories.

[rmunn]

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

[Macha]

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.

[ksec]

These issues are similar to a comment below. But a lot of these are fixed in recent version of MySQL or handled with Vitess.

[hilariously]

If you are talking about SQL Server, the MERGE statement is basically a bug minefield, I wouldn't use it unless I wanted to deadlock myself.

[phamilton]

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.

[SigmundA]

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

[cwnyth]

What's wrong with MariaDB?

[chuckadams]

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.