System resources used when using Elixir with MySQL vs PostgreSQL

Until you grow to the size, where you will be able to hire someone to handle that for you, then you will probably do not see any difference.

It is hard to tell what will work better in your particular use case, so you will need to do benchmarking on your own. However I think that you will still see no difference, and if so, it will be indistinguishable from noise.

This is from here: PostgreSQL vs. MySQL: 9 key criteria to drive your database decision

PostgreSQL starts a new process with its own memory allocation for each connection it establishes, so it requires a lot of memory on systems with a high number of client connections. MySQL uses a single process and maintains one thread (or path of execution) per connection, which results in less memory pressure.

In most sane environments there is a connection pool so the number of connections are bounded. The per process in PG overhead is not a big deal.

If I have to some layman comparison between PG and MySQL I would say that PG is more dev friendly, and MySQL is more ops friendly.

Thanks, I think I will go with MySQL then.

The scaling in the future factor is a huge plus of MySQL.

Also Uber went back to MySQL from Postgres. So, if MySQL can handle Uber it will be ok for me, I guess. MySQL also seems to be better configured for common web usage out of the box.

Postgres seems to need much more research and learning how to configure stuff out of the box.

The advantage of being able to embed Postgres with my app because of it’s liberal license is not important for me because it’s a web service hosted on a server and not a stand alone program. So, the MySQL license is ok for me for my web project meaning the SaaS is what I am after anyway.

Also, again, MySQL seems to be focused on web usage a lot, meaning much more reads than writes, as mentioned here: PostgreSQL vs MySQL: The Critical Differences | Integrate.io

PostgreSQL is known to be faster while handling massive data sets, complicated queries, and read-write operations. Meanwhile, MySQL is known to be faster with read-only commands.

Perhaps some realtime data input recording would be a better case for Postgres, I think in my case MySQL is the better option. 98 - 99% of everything in my service will be reads, only a tiny fraction will be writes to the database.

This shouldn’t be a problem until you really grow.

Because for their particular use it was better. And their usage was quite specific as they are basically using DB as a KV store.

It also could handle Google. On the other hand Heat, Algolia, Netflix, Instagram, etc. use PostgreSQL. Using some piece of technology just because “X uses it and it works for them” isn’t wise. It is following trend, which isn’t always good thing.

I am on the PostgreSQL side, mostly because of it broader support for the SQL standard and also some useful things that make life much easier. For example tuple comparisons, support for array columns, better support for CTEs, more index types, better defaults, better UTF-8 support, etc.

Then maybe you do not need DB at all, and instead, for example, single file that would be loaded into memory would be better solution?

For example tuple comparisons, support for array columns, better support for CTEs, more index types, better defaults, better UTF-8 support, etc.

Thanks. I am curious in this part. I will now read about the utf8 support you linked, but could you explain the other things, or link to explanations if you don’t have time to explain that? Thank you.

Then maybe you do not need DB at all, and instead, for example, single file that would be loaded into memory would be better solution?

My web is using relationships, I use joins. Table structure is perfect for me. I think in tables. One file would be weird. Especially with that amount of data present there.

Also, isn’t the MySQL or Postgres DB loaded in the memory as well? For example, the more DBs I have and use the more RAM the mysql/postgres is using on my computer.

Maybe try Clickhouse for high speed “selects”, it is very very fast for reads; updates and deletes (mutations) not designed for frequent use. It has Ecto support, but I did not try it, I wrote plain SQL queries in my service.

Wow, thanks for the tip. Why isn’t this Clickhouse DB more widely known and promoted? Is there a catch? Can you recommend some nice summary or presentation on Youtube about this DB?

It is useful primarily for stats and analytics, when you collect and insert user data, and later read it for analysis, but you may use it for any kind of content that requires often reads and almost no updates/deletes. Sorry, I don’t know any presentation or video.

This is how I used it in my microservice:

1. user visits some content like posts or comments or other users’ profiles - inserts;
2. later user opens his history with paginated ids and types of content he visited in past (pagination param is a timestamp) - selects;

Thanks. Great. Does Ecto support Clickhouse DB?