Elixir ecosystem is a dream come true and future proof, except...for...,

I personally disagree with this. Graph databases, document databases, etc. definitely have a place in some applications, but relational databases are a powerful general purpose solution backed by a well developed theory (relational algebra) and lots of development.

Modern relational databases like Postgres even offer document based storage, full-text search, and a lot more for cases when something like that is needed.

I am interested in your argument though. What is making relational databases a thing of the past in your view? What do you mean when you say that SQL feels old? I assume there is something deeper in your argument than just SQL not being trendy

Ehhhh sorta. They were new and cool in the late 00’s through say 2014. Since then there has been a major resurgence in the use of RDBMS both traditional and new (think spanner). Postgres in particular has developed substantially in a way that improves performance, and it opens up new ways of using postgres that can be quite impactful. We’re using Postgres for both timeseries data and as an event store for event sourcing to great effect for example.

The key here is persisting data. Persisting data reliably is flat out hard, and doing so in a way that improves on existing solutions is harder still. Elixir’s characteristics aren’t silver bullets to distributed computing problems either.

A lot of your argument here seems to be oriented around what feels new, what feels old, and what feels cool. There is a lot of fascinating stuff happening in the database world to be sure, I highly recommend Tag: Jepsen for example if you want to have some additional ways of evaluating database technologies beyond whether or not they provide a SQL interface.

First let me tell I’m a true admirer of your skills and libraries.
I know SQL and relational databases are important. I first took the effort to learn about them and personally like PostgreSQL. Have two say that like it even more due to the capability of storing “Document like” data and like Ecto embedded schemas.
But the future is about IoT and Graph N levels relationship, not “Joins” and pre-defined schemas.
And SQL is powerful but every time I look into a Mongo query or a Rethink DB or a…I get win love. Because I like them. I don’t like SQL. I have to live with it.
But apart from personal preferences, Elixir is all about distributed system and built-in horizontal scaling. Would you bet on relational Dbs for that?

Elixir’s team could apply the same magic into the future way of persisting data.

boy do I wish that were true, too, but state is hard, and the elixir team has so much on its plate as it is… I wouldn’t wish that on them. Now if only I had a bunch of cash I could just throw at a skunk team of a couple of smart academics and a couple of smart programmers to work on interesting things…

Again, I would repeat myself in my previous answer.
You are an inspiring member in this ecosystem with Absinthe and the way you love simple and efficient code.
I’m a new software developer that didn’t lear SQL 15 years ago and now I look to GraphQL and feel in love with it, so why REST? But that’s, honestly, a minor preference and efficiency issue when you compare databases query languages, distribution and scaling capabilities and, of course, human readability.
The problem is not SQL or relational databases in itself.
It’s when you compare them with the new possibilities.
Just like when you compare Java with Elixir.
The majority will say Java is the best option and, bla, bla, but it’s not even a discussion for me that started to code recently.
It’s the same with databases.
And, I’m saying this because Elixir “Just works”, Phoenix is a " Peace of mind", and so on.
SQL…well…relational DBs are a dark cloud in this dream. Simplicity wise for a newcomer. How the hell should I think data first? I think “Business first”.

LOL, true, but as far as I know everything is very stable and with low need of attention except for LiveView as of today. But the next big game changer could be this one: databases.
Even authentication that was a no go 2 years ago is close to be handled…so…
“Where there’s a will there’s a way…”

The issue isn’t necessarily databases, they’re an implementation detail for persistence and query requirements. Databases solve the real problem of making sure your data is still there throughout restarts of the system. Databases do a lot and they’re hard. Want to learn more? The issue might instead be an architecture that couples directly to the database through misuse of CRUD semantics. So maybe it would help to discuss the misuse of CRUD?

Have a problem? Let’s add a field! Have a bigger problem? Let’s make a new table! Have a huge problem? Let’s make another service with it’s own database so it can have the same problems but somewhere else but with more coordination costs! Ha ha! Microservices.

The issue isn’t CRUD, but usually exposure of CRUD semantics as the interface rather than domain semantics.

With CRUD we have an entity and it gets created, updated, deleted or read. If that’s our interface what does the business logic? Maybe the CRUD is exposed over JSON via HTTP or even…GraphQL. What? I thought GraphQL was fancy and new and modern and solved all our problems? Well not if you just do CRUD over GraphQL… same issues can still surface because instead of exposing a service that does business things we’re exposing a service that does database things. Because our business logic isn’t involved in the interface to the consumers it means our consumers have to do the business logic! As a result, the business logic: the whole thing you built the app for, tends to get scattered all over the stack.

This isn’t always a bad thing. Sometimes exposing the CRUD is a great way to get moving fast when the real requirements of the problem are unclear. A reasonably designed table can operate like a slightly better spreadsheet. You monitor post-deployment the repeated efforts of some user to discover the real “business logic”. However this benefit of CRUD is a double-edged sword - what if you never notice that real requirement, or the developer leaves and finds a new job? Business has to business and changes will still be made, but will they be good changes?

So real issue is change. More fields? New picklist options? New tables? Migrations? For the database or the existing data? Both?! Good luck migrating that unvalidated text field you tossed in there because it was the quickest option. You’ll just need to write an ETL script that covers every single possible string your sales team could’ve thought up on the spot over the last year. Maybe we should’ve just used a spreadsheet…

So why does everyone build CRUD systems? Why do we have Phoenix generators for CRUD? The reason is because it’s extremely time efficient and if we know what we’re doing we can minimize most of the main issues. Instead of create_user we make register_user. Now our interface to potential downstream consumers is domain-driven rather than CRUD-driven! Problem solved. Mostly. We still have to handle change and data migrations over time. But, if the database model is well defined and we’re validating our data, that change doesn’t have to be that bad.

Finally, just think of all the engineering hours into frameworks, Postgres, etc over the decades. At this point we can run those Phoenix generators and do what would’ve taken teams of database administrators years ago. We’ve taken something that was hard and difficult, thrown hundreds of thousands of engineering hours at it, and now we have mix phx.gen.live.

Can we do the same for other architectures than CRUD?

From a technical perspective we could argue that CQRS and Event-sourcing is “simply better”. It scales! We get an audit log! We can torch that poorly designed read-model and make a new one. That’s awesome. But if it takes more developer time to implement a given CQRS/ES feature it will almost always lose out from the business perspective until it burns through significantly less hours than CRUD. We can run a large profitable business on a $10 / month server using free open source software. Hooray! Infrastructure is cheap! But the developer hours are going to cost you thousands per week. Have a fancy new architecture? Now you have to pay for fancy developers and they cost even more.

So this is a long winded way of saying that instead of investing engineering hours into the diminishing returns of improving CRUD we should be looking at making new and improved architectures as or more productive than CRUD is currently.

/rant

I think there is some truth in this. It’s not easy to horizontally scale a relational database in the way that distributed system do. Indeed, Erlang native solutions like Mnesia adopt a different model. Also, solutions like CouchDB or Riak (both written largely in Erlang) adopted a distributed model from the start.

That said, solid relational databases can take a lot (I am always amazed how much!) before one needs to scale horizontally. Even when scaling horizontally, one still has a choice in what to shard and what not.

On top of that, I believe that most data is inherently relational, and consistency is often more desirable for core data than full distribution. It depends on the case of course, but relational databases, when following established practices, are very versatile (you can model graphs or document-based access with them), and adapt very well to changing requirements as they impose less “sticky” decisions upfront.

You are right that classic relational databases embrace a different model than the Erlang distributed system philosophy. But I don’t think the two are at odds, and in my personal experience distribution at the database level is something very tricky to deal with, that I would only use when necessary, not as a default.

It’s a good conversation to have though I do love to look into different solutions. If you didn’t read it yet, this book is really enjoyable: Seven Databases in Seven Weeks

I’ve always wondered why there isn’t a relational database in the BEAM. You could use otp for the tricky parts like shard management and distributed locks, and drop to NIFs for parts that need to be low level like disk or block access.

Creating a RDBS takes an incredible amount of investment. Postgresql has 50 years of history with some of the brightest minds in computer science and database implementation working on it during that time, which is reflected in its capabilities.

Many newer NoSQL databases are much smaller in scope, and even then they take a long time to create. Neo4J is 13 years old and is still lacking many features that we might expect of a RDBS, especially in the open source version.

As someone who’s used these and also RDBS in prod I would pick the relational database unless I have a clear technical requirement that the RDBS can’t handle (for example complex search, for which I would likely use elasticsearch).

My experience is that NoSQL databases excel at the thing they are made for, but are much less suited to other tasks, so you have to think extremely carefully about your schema design and access patterns, requiring much more design work up front.

Relational databases allow a much more iterative and “agile” style of development as it’s trivial to change the schema in production at any point using migrations, and the optimisation for ad-hoc queries means you don’t need to determine and design for all your access patterns before you start development.