mysql-postgresql-comparison.md

MySQL / PostgreSQL comparison

MySQL

Pros

• Somewhat lighter-weight (i.e. does not use as much memory necessarily)

• Widespread support by many apps

• Table partioning simple and built-in

• MyISAM

  • Very fast data reading capability

• InnoDB

  • Transaction support
  • Uses multi-version concurrency for concurrent transactional locking

Cons

• Implementation of triggers is shoddy at best

• Subquery performance is quite sub-optimal

• Extensions must be written in C and compiled, and as a result can be unstable and unsafe

• Extensions require restart of the server and may require rebuilding it from source

• Table partitioning is very limited in scope

• Data integrity difficult to maintain at the database level

  • CHECK constraints are parsed successfully but ignored by every storage engine
  • Foreign keys are only supported in InnoDB tables

• MyISAM

  • Complete lack of transaction support and disregard for ACID principles
  • Only supports table-level locking, resulting in terrible concurrency

• InnoDB

  • Not fully ACID-compliant

PostgreSQL

Pros

• Uses multi-version concurrency for concurrent transactional locking

• Fully ACID-compliant

• Well-documented

• Table partitioning can be accomplished very flexibly

• Optimized and efficient subqueries

• Very feature rich

  • PostGIS extension makes Geospatial data processing superior (useful for map-based searching)
  • Very flexible full-text search capability
  • Flexible replication options
  • Native array datatypes make processing of multi-row records immensely easier

• Supports a variety of languages for server-side programming, increasing efficiency of the database

  • Can use PL/PGSQL (included), PL/Perl, PL/Ruby, among others, in addition to C-level functions
  • Extensions can be loaded/unloaded at runtime
  • Functions can return datasets, allowing for cleaner SQL with no loss in efficiency

• Write-ahead log (WAL) facility

  • Increases stability significantly
  • Larger likelihood of surviving hardware failures/unexpected shutdowns without any data corruption

• Actively developed

  • New versions featuring major feature additions generally released semianually
  • Examples include:
    • Streaming replication with hot standby, allowing distribution of read queries to any number of standby slaves, as well as a simple mechanism for failover
    • Writeable CTEs (allowing inserts/updates across multiple tables in a single query)
    • The ability to group custom functions/datatypes/etc. into an "EXTENSION" for easy loading/unloading via "CREATE EXTENSION"/"DROP EXTENSION"
    • SQL/MED - The ability to query external datasources (MySQL/Oracle/etc.) as though they were tables in the PostgreSQL server, transparently

• Various features supporting strong data integrity-based database design

  • Fully supported CHECK constraints
  • Native support for foreign key checks

Cons

• Differences between PostgreSQL and MySQL may require a transitional period / learning curve
• Requires rewriting scripts such as backups (fortunately backups are easy to do)
• Not as widely supported by third-party applications
• Table partitioning requires more initial effort