3 ms·
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
by XCabbage 5y ago
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!
- jugg1es 5y agoI 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.
- petergeoghegan 5y ago> 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): https://www.postgresql.org/docs/devel/explicit-locking.html#LOCKING-TABLES https://www.postgresql.org/docs/devel/explicit-locking.html#... 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.
- XCabbage 5y agoHuh. 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 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.