🐘 PostgreSQL vs MySQL 🐬
https://www.youtube.com/watch?v=mg70LYuRS3I
Article on Medium (with pictures) One of the best lectures on the topic of comparing PSQL and MYSQL, and it's also from a conference of my colleagues
In short:
Functionally, MySQL has already caught up with PSQL
PSQL opens a separate process for each connection, while MySQL opens a separate thread, so you need to use PgBouncer and/or PGPool-II to optimize connection handling in PSQL
PSQL and MYSQL build queries similarly, except for the Executor stage, where in MySQL procedures are passed to the engine used for the table, while in PSQL there is only one engine, which allows PSQL to be more optimized (it knows exactly how its engine is designed and accesses it directly)
Statistics in PSQL know much more about the data stored in it (since it can collect it directly from the single engine), which in turn gives the optimizer the ability to build SELECT queries more efficiently
PSQL and MySQL use MVCC for transaction handling
PSQL creates a copy of the record with an indication of the transaction ID when changes are made and marks the old record with the same ID, (2) when deleting, it indicates the transaction ID. Then, during the AutoVacuum process, it clears all data that was previously deleted by the oldest active transaction
MySQL, on the other hand, always keeps one current version of the object and creates a record in a separate place (undo_log) with what was changed (including INSERT, UPDATE, and DELETE)
Accordingly: UPDATE in PSQL requires more disk space and is slower, while INSERT requires less space and is faster, and transaction rollback is also faster. However, overall, MySQL is faster in INSERT, UPDATE, and DELETE operations
- Indexes can be clustered (stored with the data, already sorted by it, and only one can exist) and non-clustered (indexes stored separately)
In PSQL, all indexes are non-clustered + PSQL supports multiple types and kinds of indexes
In MySQL, there is a clustered index (PRIMARY KEY) + it only supports B+ Tree index