Skip to content

PostgreSQL vs MySQL

1. Decision

Choose PostgreSQL when building complex applications requiring rich data types (JSONB, GIS, Arrays), advanced indexing (GiST, GIN, BRIN), strict SQL compliance, and complex concurrency (MVCC); choose MySQL (InnoDB) for simpler transactional workloads requiring extreme read-heavy throughput, straightforward replication topology, and widespread operational familiarity.

2. 30-Second Answer

PostgreSQL is an advanced, standards-compliant object-relational database optimized for complex queries, high concurrency, and extensible data types (native JSONB indexing, PostGIS). It uses process-per-connection architecture and multi-version concurrency control (MVCC) without rollback segments. MySQL (InnoDB) is a thread-per-connection relational database optimized for high-throughput, straightforward read/write workloads. MySQL uses undo logs for MVCC and clustered primary key indexes (B+ Tree), making primary key point lookups extremely fast, whereas Postgres requires secondary index lookups to hit heap pages.

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
PostgreSQL Complex queries, JSON/document storage, Geospatial, Financial systems Extreme connection counts without proxy, minimal footprint Low (<5ms); fast complex joins Scale-up; Read replicas; Logical replication sharding Strict ACID, MVCC append-only tuple design Medium
MySQL (InnoDB) E-commerce, web applications, high connection volume, read-heavy workloads Native JSON query indexing, complex window functions/CTEs Low (<3ms point lookups via clustered index) Scale-up; GTID replication; Group Replication Strict ACID, MVCC via Undo Log (This is because it uses in-place update) Low to Medium

4. Selection Rules

Requirement Best Choice Rationale / Example
JSON Indexing & Querying PostgreSQL Native JSONB with GIN indexing for sub-millisecond nested field queries
Geospatial Data Processing PostgreSQL (PostGIS) Industry standard spatial indexing (R-Tree / GiST) for radius/polygon queries
Extreme Point Lookups by PK MySQL (InnoDB) Clustered index stores row data directly in primary B+ Tree leaf nodes
High Dynamic Connection Volume MySQL Lightweight thread-per-connection architecture (vs Postgres process fork model)
Extensible Data Types / Custom Index Types PostgreSQL Supports BRIN, GIN, GiST, SP-GiST, vector embeddings (pgvector)
Simple Replication setup MySQL Built-in GTID-based async/semi-sync replication with instant failover tooling

5. Tradeoffs

  1. Heap Storage (Postgres) vs Clustered Index (MySQL)
  2. Gain (Postgres): Primary key updates don't require rewriting entire row structures; fast secondary index creation.
  3. Lose (Postgres): Secondary index lookups require double hops (Index -> Tuple ID -> Heap Page read).
  4. Process-Per-Connection (Postgres) vs Thread-Per-Connection (MySQL)
  5. Gain (Postgres): Memory isolation; process crash won't bring down entire DB cluster.
  6. Lose (Postgres): High memory overhead per connection (~2-10MB/conn); requires connection pooler (PgBouncer).
  7. Vacuum / MVCC Strategy
  8. Gain (Postgres): Simple append-only WAL writes; no complex rollback segment corruption risk.
  9. Lose (Postgres): Table bloat risk; requires autovacuum maintenance overhead to reclaim dead tuples.
  10. Undo Logs (MySQL) vs Tuple Duplication (Postgres)
  11. Gain (MySQL): In-place page updates; no table bloat from un-vacuumed dead rows.
  12. Lose (MySQL): Long-running transactions cause Undo Log bloat and severe performance degradation.
  13. Extensibility vs Core Engine Simplicity
  14. Gain (Postgres): Third-party extensions (pgvector, Citus, TimescaleDB, PostGIS).
  15. Lose (Postgres): Larger engine footprint, more tuning parameters required for optimal I/O.

6. Interview Counter Questions

  1. Why does PostgreSQL require PgBouncer while MySQL can handle thousands of direct connections?
  2. How does MVCC implementation differ between Postgres (Heap + Vacuum) and MySQL (Undo Logs)?
  3. What is PostgreSQL "Table Bloat" and how do you mitigate autovacuum degradation under high write load?
  4. How do primary key lookups differ at the storage page level between InnoDB and Postgres heap?
  5. When should you use a GIN index vs B-Tree index in PostgreSQL?
  6. How does PostgreSQL handle vector search compared to specialized vector databases?
  7. What is HOT (Heap-Only Tuples) optimization in PostgreSQL and when does it break?
  8. How does Replication lag differ between MySQL GTID semi-sync and Postgres WAL streaming replication?
  9. Why can a COUNT(*) without a WHERE clause be slow in PostgreSQL but fast in MySQL MyISAM (or cached)?
  10. How would you choose between MySQL Group Replication and PostgreSQL Patroni/etcd for HA?

7. Gold Standard Answers

  1. PostgreSQL forks a full operating system process per client connection (~2-10MB RAM per process), causing severe CPU context switching above ~500 connections. MySQL creates lightweight OS threads. PgBouncer pooler maintains a small set of persistent server connections mapped to incoming client requests.
  2. Postgres writes new row versions directly into heap pages, creating dead tuples cleaned up asynchronously by VACUUM. MySQL InnoDB updates rows in-place and stores historical versions in an Undo Log, reconstructing older views on demand.
  3. Table bloat occurs when row updates/deletes outpace Autovacuum's ability to clean dead tuples, bloating disk pages and degrading index scans. Mitigate by tuning autovacuum_vacuum_scale_factor, increasing maintenance_work_mem, or using pg_repack for lock-free table rebuilds.
  4. InnoDB uses clustered indexes: leaf nodes contain full row data, making primary key lookups a single B+ tree traversal. Postgres uses heap storage: primary and secondary index leaf nodes store a Tuple ID (ctid), requiring a second read to fetch the heap block.
  5. Use B-Tree for scalar comparison operations (=, <, >). Use GIN (Generalized Inverted Index) for composite, array, JSONB, or full-text element matching where key-to-multi-value mappings exist.
  6. pgvector adds HNSW and IVFFlat index types directly to Postgres, letting applications query vector embeddings alongside relational data in single ACID transactions. Suitable up to ~10M vectors; beyond that, dedicated vector DBs (Milvus, Qdrant) scale better.
  7. HOT allows Postgres to update a row on the same heap page without updating secondary indexes if the update doesn't modify indexed columns and free space exists on the page. It breaks if indexed columns are modified or page is full.
  8. Postgres streams raw WAL bytes synchronously or asynchronously to standby instances. MySQL uses GTID binlogs; semi-sync requires at least one replica to acknowledge receiving binlog events to disk before committing on the primary, balancing durability and write latency.
  9. Postgres MVCC requires checking visibility horizon for every tuple per transaction snapshot, forcing a full table scan for COUNT(*). MySQL MyISAM kept a static row counter (not MVCC), whereas InnoDB also scans index pages for visibility check.
  10. Use Patroni with etcd for PostgreSQL for battle-tested leader election and WAL-based failover. MySQL Group Replication uses Paxos consensus natively within InnoDB engine; select based on team expertise with etcd vs native MySQL cluster configuration.

8. Common Mistakes

  • Running PostgreSQL in production without PgBouncer or native connection pooling, causing memory exhaustion under traffic spikes.
  • Ignoring Autovacuum metrics in PostgreSQL, resulting in runaway disk bloat and query slowdowns.
  • Assuming MySQL JSON data type has equivalent indexing power to PostgreSQL JSONB with GIN.
  • Using default PostgreSQL configurations (e.g., shared_buffers = 128MB) in high-memory production nodes.
  • Choosing Postgres solely for features without evaluating operating experience, leading to misconfigured failover clusters.

9. Keywords

MVCC, Heap Storage, Clustered Index, Undo Log, Autovacuum, PgBouncer, GIN Index, GiST Index, HOT (Heap-Only Tuples), ctid, WAL Streaming, GTID, Patroni, pgvector, PostGIS.

10. Cheat Sheet

  • Architecture: Postgres = Process-per-connection (needs PgBouncer); MySQL = Thread-per-connection.
  • MVCC: Postgres = Tuple duplication in Heap (needs Vacuum); MySQL = In-place update + Undo Log.
  • PK Lookup: MySQL InnoDB = Single B+ tree read (Clustered Index); Postgres = 2 Hops (Index B-Tree → Heap Page).
  • JSON/Document: Postgres JSONB + GIN Index outperforms MySQL JSON field capabilities.
  • Extensibility: Postgres supports native extensions (pgvector, PostGIS, Citus).
  • Connection Pools: Always deploy PgBouncer in front of Postgres for workloads > 200 concurrent connections.
  • High Availability: Patroni + etcd for Postgres; Orchestrator / Group Replication for MySQL.

11. Related Decisions