Craft 3 : What are the advantages to use PostgreSQL and when should I use it instead of MySQL?

Question

Craft 3 now supports PostgreSQL.

I know this a very general question, but:

• I have absolutely no experience with PostgreSQL
• I don't know when should I use it instead of MySQL

To quote Bob Olde Hampsink:

PostgreSQL is the most innovative and hot database engine at the moment. It has better UTF-8 support and better overall performance.

Also, for example, on Heroku it is much cheaper to host with Postgres than with MySQL. Thus it becomes more attractive for people to pick a CMS/framework with Postgres support.

Is there another good reasons to know, to choose one instead of the other when starting a new project? For example, in general what would be the gains in terms of performance, compared to MySQL?

Answer 1

This isn't actual first-hand experience talking, but from what I've read, Postgres will start to see gains when we're talking about very large data sets, either in terms of the size of the columns, or the number of rows in a particular table.

As mentioned in the quote above, Postgres also has some other nicities as well. The downside is that there is less tooling around Postgres than there is MySQL, so while you'll be able to find GUI clients, they aren't there in quantity or quality compared to MySQL.

I have had devMode.fm up and running with Craft 3 and Postgres since day 1, in part to get Craft-Scripts working with it (which it does) since it's a supported database type by Craft now, and then also in part just to kick the tires.

What I can tell you is that everything "just works" from a Craft POV. I haven't had any issues at all with the Craft APIs, and since I do the database syncing with Craft-Scripts, the tooling hasn't matter either (but the editor I user is PhpStorm, which can connect to Postgres databases anyway).

You will most likely have to learn a bit about about Postgres handles access permissions, because it's quite different from MySQL, but that's not so bad really.

After using it for a while, my conclusion is that it works fine; but on a small site like devMode.fm, it probably makes absolutely no difference. Because it doesn't have any large db columns or massive numbers of rows of the like. You'd never notice the difference from a frontend/AdminCP POV in terms of which db driver you're using, which is a testament to the work that P&T and the Yiisoft folks have done.