3 ms·
As much as I like PostgreSQL's features, MySQL does have advantages in some situations. The copy on write/mvcc type stuff in particular has bit me hard in the
by Xamayon 7y ago
As much as I like PostgreSQL's features, MySQL does have advantages in some situations.
The copy on write/mvcc type stuff in particular has bit me hard in the past. It's great in most cases but can cause some serious issues with huge tables where every row is updated many times. Even with frequent table cleanup/analyze/etc, the tables bloat up and can become completely unworkable whereas in MySQL there are no problems at all. Symptoms are things like the table sizes growing hundreds of GB larger than the data, queries which should use indexes becoming table scans, etc. The existence of multiple versions of a row seems to really upset the query planner in some cases. I have a table which takes several hours to do a basic conditional count in PostgreSQL, which will finish in milliseconds on MySQL.
- paulryanrogers 7y agoMySQL's InnoDB is MVCC as well, at least when used with transaction isolation. That said, MySQL does have more mature replication options built-in.
- Xamayon 7y agoI wasn't aware of that, but given the transaction capabilities, it makes sense. I primarily use InnoDB and have never had any similar performance issues, so I havn't looked into it heavily... MySQL InnoDB tables handle the use case much much better, so just guessing but it's probably a difference in how the indexes handle updates/multiple versions and how the query planner works for dirty indexes.