Having used both MySQL and PostgreSQL enough to have been annoyed by features of both of them and fall into a bunch of their traps, I feel inclined to point out a few ways in which MySQL is better, most of which relate to the initial learning curve.
1. MySQL has the better CLI tool. psql is full of minor annoyances, like its cryptic and unmemorable backslash commands like \d for schema inspection, or having a pager turned on by default for query results that are more than one screen length.
2. Postgres has a deeply unintuitive concept of "USER"s and "ROLE"s where actually those are the same thing, and commands like CREATE USER and CREATE ROLE are thus basically synonymous (albeit with slight differences in default values). Worse, lots of the docs pages relating to specific commands don't highlight this fact at all. A majority of web devs I've talked to about Postgres have at some point complained about how they don't understand the permissions system or how ROLEs work.
3. While MySQL enforces that a foreign key from column A -> column B requires an index on both A and B, Postgres only requires a foreign key on column B. Maybe this freedom is nice if you know what you're doing, but it's a major footgun otherwise, since it means that deleting or updating rows in B has a time cost that scales linearly with the number of rows in the table A belongs to.
4. MySQL has a handy ON UPDATE current_timestamp shorthand for creating updated_at timestamp columns, which Postgres lacks, requiring you to use a trigger to implement such a column or move the logic to your application layer.
5. MySQL's "online DDL" features allow making lots of kinds of schema changes without ever holding a lock on the table being updated. In Postgres, by contrast, any schema change, even adding an index with the CONCURRENTLY option, needs to at least momentarily hold a totally exclusive lock that blocks all writes and reads on the table. Worse, as soon as an ALTER TABLE statement starts waiting for that lock, it blocks all new reads and writes against the table. This makes all schema changes in Postgres much more dangerous to the naive; even one that's theoretically able to happen concurrently with queries will hang your application if there's already a long-running query going against the table to be modified. It also means that at scale you need to roll a bunch of your own tooling to apply schema migrations safely, where you run them from a connection with a very short lock_timeout and wrap them in some code that retries on failure to acquire the lock. I don't remember any of this crap being necessary in MySQL.
Maybe Postgres is still better; in particular, maybe it's more performant in a way that outweighs all these nuisances. I don't really know because I've never done any head-to-head comparison to see how the performance of the two would differ in a given scenario, and so the performance differences aren't something I've had a chance to witness as an ordinary dev using both databases. But I just want to make clear that there absolutely is another side to the story!
> In Postgres, by contrast, any schema change, even adding an index with the CONCURRENTLY option, needs to at least momentarily hold a totally exclusive lock that blocks all writes and reads on the table.
It's true that every DDL statement requires a relation-level lock. Though the same is true for even a simple SELECT statement. The important detail is the lock strength for each variety of DDL, and how that affects and interacts with other queries (including other DDL):
CREATE INDEX (even without CONCURRENTLY) will not block reads, even for a moment (it just blocks write DML). CREATE INDEX (without CONCURRENTLY) won't even block other CREATE INDEX statements that run against the same table.
My guess is that your application appeared to exhibit this behavior, but the true problem was actually how several conflicting locks accumulated, which had the effect of blocking SELECTs for an unreasonably long time. A combination of CREATE INDEX and some other conflicting DDL that really does block reads (e.g., certain kinds of ALTER TABLE) can create the false impression that CREATE INDEX blocks reads in general. But that's not really the case at all. At worst, CREATE INDEX is only one part of the "traffic jam" that caused SELECTs to block in this scenario.
Huh. I was sure I was right about this but I tested and realised you are correct. CREATE INDEX (even with CONCURRENTLY) gets blocked by reads while waiting to acquire the lock it needs, but it doesn't block reads during that time.
I was getting confused because I'd seen my attempts to CONCURRENTLY add indexes time out waiting for locks, and had also experienced some of the other kinds of DDL that block reads while waiting for their lock (see https://dba.stackexchange.com/q/293992/18607), and I guess I just fallaciously assumed that index creation therefore also blocked reads without ever having tested it.
~Will edit my prior post to add a note about the error!~ Nope, doesn't seem I'm allowed to edit it any more.
CONCURRENTLY needs to wait for old transactions to go away, including those that haven't (and won't ever) touch the table that you're building an index on. So it's not waiting for a lock as such - it's waiting for older transactions to go away without conflicting in a way that can cause these "traffic jams". This can be a problem for the obvious reason, though generally only for the index build itself.
Tricky problems with relation-level locks tend to come from a combination of one lock request that is "generally non-disruptive but long-lived", and another lock request that is "generally disruptive/blocking but short-lived". I have heard of quite a few problem scenarios where these locks conflict with each other (usually by chance), leading to "generally disruptive/blocking and long-lived" -- which can be very dangerous. But that's fundamental to how lock managers work in general.
The Postgres implementation tries to make it as unlikely as reasonably possible. For example, autovacuum usually notices when something like this happens, and cancels itself.
While I personally favour Postgres the biggest thing I think that MySQL has better is the "undo" log vs MVCC vacuum paradigm of Postgres. In a normal running system bad transactions should be significantly rarer than successful transactions - so making sure the normal path generates less garbage is nice. It means less garbage in the database. Tables with high levels of updates don't need high levels of locking or index updates. It also would make things in Postgres more useful like BRIN index - where an update doesn't necessarily need to change physical table order causing index fragmentation. Would make using BRIN index with things like Sequential UUID's on extremely large tables actually viable vs duplicated indexes that cause insert speed issues.
I second the complaint about the user and role design in Postgres. The first time I encountered it, I convinced myself I just didn't understand it well enough yet since it didn't make a lot of sense to me that it works like it does.
1. MySQL has the better CLI tool. psql is full of minor annoyances, like its cryptic and unmemorable backslash commands like \d for schema inspection, or having a pager turned on by default for query results that are more than one screen length.
2. Postgres has a deeply unintuitive concept of "USER"s and "ROLE"s where actually those are the same thing, and commands like CREATE USER and CREATE ROLE are thus basically synonymous (albeit with slight differences in default values). Worse, lots of the docs pages relating to specific commands don't highlight this fact at all. A majority of web devs I've talked to about Postgres have at some point complained about how they don't understand the permissions system or how ROLEs work.
3. While MySQL enforces that a foreign key from column A -> column B requires an index on both A and B, Postgres only requires a foreign key on column B. Maybe this freedom is nice if you know what you're doing, but it's a major footgun otherwise, since it means that deleting or updating rows in B has a time cost that scales linearly with the number of rows in the table A belongs to.
4. MySQL has a handy ON UPDATE current_timestamp shorthand for creating updated_at timestamp columns, which Postgres lacks, requiring you to use a trigger to implement such a column or move the logic to your application layer.
5. MySQL's "online DDL" features allow making lots of kinds of schema changes without ever holding a lock on the table being updated. In Postgres, by contrast, any schema change, even adding an index with the CONCURRENTLY option, needs to at least momentarily hold a totally exclusive lock that blocks all writes and reads on the table. Worse, as soon as an ALTER TABLE statement starts waiting for that lock, it blocks all new reads and writes against the table. This makes all schema changes in Postgres much more dangerous to the naive; even one that's theoretically able to happen concurrently with queries will hang your application if there's already a long-running query going against the table to be modified. It also means that at scale you need to roll a bunch of your own tooling to apply schema migrations safely, where you run them from a connection with a very short lock_timeout and wrap them in some code that retries on failure to acquire the lock. I don't remember any of this crap being necessary in MySQL.
Maybe Postgres is still better; in particular, maybe it's more performant in a way that outweighs all these nuisances. I don't really know because I've never done any head-to-head comparison to see how the performance of the two would differ in a given scenario, and so the performance differences aren't something I've had a chance to witness as an ordinary dev using both databases. But I just want to make clear that there absolutely is another side to the story!