PostrgeSQL vs. MySQL - VittorioDeMarzi/hero-beans GitHub Wiki

Resource

Both MySQL & PostgreSQL

  • relational database management system where you can store tables with columns and rows
  • use SQL
  • open source
  • built-in data backup, replication, access control
  • suitable for most cases

MySQL

  • offers ACID compliance only when you use it with InnoDB and NDB Cluster storage engines or software modules.
  • concurrency control (MVCC) depends on the engine
    • MVCC is fully supported when you use the InnoDB storage engine
  • B-Tree indexing
  • MySQL is a purely relational database -> does not understand objects
  • shorter learning curve, simple to set up
  • faster read operations -> single process for multiple users
  • slower write operation with usage of locks
  • supports enum type, but it gets mapped to varchar or int

PostgreSQL

  • object-relational database
  • has more features than MySQL
  • more flexibility with datatypes, scalability, concurrency and data integrity
  • fully ACID-compliant
  • PostgreSQL supports MVCC in all configurations
  • more index types
  • in PostgreSQL, you can store data as objects with properties
  • supports other additional data types like arrays and XML
  • you can precompute some values in advance (like the total value of all orders over a given period) to create materialized views; materialized views improve database performance for complicated queries
  • PostgreSQL supports the INSTEAD OF trigger, so you can run complex SQL statements using functions
  • better suited for frequent write operations and complex queries
    • has built-in concurrency control that does not require read-write locks
  • supports custom enumerated data type

Why did we chose MySQL?

  • We use InnoDB and it offers ACID compliance
  • MySQL offers faster read operations -> single process for multiple users
  • We don't need enhanced features like complex indexing, custom data/enum types, storing objects in columns or inheriting from one table to another
  • With MySQL 8.0 (the version we chose for our RDS), the practical gap with PostgreSQL for locking + reads is not so big
    • Both support:
      • MVCC for non-blocking reads
      • Row-level pessimistic locks (FOR UPDATE, NOWAIT, SKIP LOCKED) -> a simple read is still possible on the last version of locked items and there is no dead-locks, because locked items are skipped
      • Differences still exist in granularity, deadlock handling, and concurrency performance — Postgres is more flexible and resilient under high contention
      • You would use PostgreSQL for example for a flight ticketing system, where there is a limited amount of seats and it is crucial to handle that, but for our small e-commerce, where we sell coffee and the stock is often updated and our local Berlin-business is small, we are not expecting such a high load and critical situations

Amazon RDS

~: Amazon RDS (Relational Database Service) is a managed database service provided by AWS that makes it easy to set up, operate, and scale relational databases in the cloud

  • tasks: managing time-consuming database administration tasks, including backups, upgrades, software patching, performance improvements, monitoring, scaling, and replication

What does it do?

  • Provisioning: Creates and configures database instances automatically.
  • Maintenance: Handles OS patching, database patching, and upgrades.
  • Backup: Provides automated backups, snapshots, and point-in-time recovery.
  • Scaling: Supports vertical scaling (bigger instance) and read replicas for horizontal scaling (read-heavy apps).
  • Monitoring: Integrated with Amazon CloudWatch for metrics and alarms.
  • High Availability: Offers Multi-AZ deployment for failover and durability.
  • Security: Supports encryption (at rest and in transit) and integrates with IAM for access control.

Our initial setup