How to choose correct database system?

Tags: #<Tag:0x00007f11423d1080>


I am going to start new Phoenix project, but i am not clear about choosing proper database for my application. For example, My app fully based on relational database but in Relational Database we have mysql, postgresql , mariadb ,etc, In Phoenix they use PostgreSQL as default, So which is the best database ?

Would you recommend using MongoDB with Phoenix with current driver status in mind?

This highly depends on your workload. Most web applications are suited
very well with using PostgreSQL, but there are cases where it might make
more sense to use something different. So as a rule of thumb: use
PostgreSQL if you are planning to write a basic CRUD application.

IMHO, of course.


From my point of view Postgres is the better choice. It behaves more like a database should and is built on a sound foundation.

MySQL might have more tooling, better support in the cloud and there are likely more people with MySQL knowledge out there.

My advice is if you don’t have a strong preference for a particular database: choose postgres.


If you need a relational database and have to have deeply nested datastructures, Postgres will get the job done. As for best that would vary from project to project. also because you kan turn postgres to a KV store.

If you should find you didn`t need relational data. or that at one point you need flat filesystem, or a in memory system for perfomance. Maybe you even want to combine disk based and memory based storage for optimized read speeds.

Starting with the database decision at the beginning might lock you into that decision later ,especially if other people are involved.
Use ecto or make a layer that wraps data to any data source and decide what to use sometime before deployment.

Especially with larger projects the designed software is usually never the actually built software.


thanks a lot, I started using mongodb as database, later i find lack of transactions in mongo, so i plan to shift relational database, my app involves lot of read operations, as per your suggestion i will try postgres


I have seen big applications work fine with both postgresql and mysql. And I have seen a lot of failure with MongoDB. Using anything besides the big sql dbms is an experiment that can work fine or fail. Just my $0.02 to that topic :slight_smile:
Of course there are challenges with postgresql/mysql in production, like migrations of big tables but there are solutions as well. I personally use postgresql with some jsonb to store structured data but beeing preprod I cannot say how well that will work.


we are using mongodb successfully. and there are probably many failures with postgres and mysql, too.

but if any one want to use elixir and can choose the database one should not choose mongoDB in the moment, as the driver support is subpar.

Would you recommend using MongoDB with Phoenix with current driver status in mind?

My intention was not to say that MongoDB is not a proper database and you cant fail while using mysql/postgresql. I think it all depends on what you are about to to do.


Hi, @svsdehh
I’m starting a new project using mongoDB and Phoenix.
Do you have any experience or path you can share?


13 posts were split to a new topic: Would you recommend using MongDB with Phoenix with current adapter status in mind?

Would you recommend using MongoDB with Phoenix with current driver status in mind?

It definitely depends on your needs but Postgres is the best starting point for most web applications. It is flexible enough to handle the needs of just about any style of web application without needing to reach for outside tooling, can be tuned endlessly, has custom data types for just about everything and as you grow there are EXCELLENT scaling options like Citus (for multitenant) and Aurora (for most everything else).

If you have an application that would heavily depend on one specific piece of functionality, it will make sense to pull in a 3rd party tool but one of the other great things about PG is that is makes it extremely easy to hook to outside systems and push data to them directly from the database.

It’s harder and harder to justify anything else as a starting point for a web project.


Thanks for the important information you just shared. After reading all the comments I can clearly see that the Elixir community is heavily inclined towards Postgresql. I, being new to Elixir and Postgresql, have no idea why. But I trust your advice and have started trying out Postgresql with Phoenix. And here the first problem appears. I want to use full text search. I went through your blog post but didn’t succeed to make it work. The schema complains invalid or unknown type :tsvector for field. I have done the indexing for full text search following the official postgresql documentation page. It would be great if you can help me out with some sample code or point me to some other place on internet where I can get some more details about how to do full text search in postgresql + Phoenix?


The actual Postgres documentation is the ideal starting point. If I remember correctly it’s about 4 pages that walks through setting up full text search.

You should definitely go through it with straight SQL the first time you do it just to see how all of the various parts work. I’ll see if I can find a Elixir focused write up.

My blog post isn’t really a walk through, so definitely don’t use that as a working basis.

In terms of the community, PG is the default for Phoenix but your database is all about your project needs. PG just happens to cover so many of those cases that it’s an ideal starting point MOST of the time.

Learning a new database and language at the same time can be asking a lot though. I’d opt for one at a time, personally.


I think the first step is trying to figure out if you need a database to begin with. then when the answer is yes you need to figure out what storage solution best fits your problem domain (Document, key-value, relational, etc.) only after you answered these questions can you make an informed choice about what DB storage solution to choose. And I think you can only realistically know this after you have at least implemented some of your business logic.

Some nice insight from someone working a lot longer than me in this inustry :slight_smile:

Uncle Bob -No DB