PostgreSQL vs MySQL: The Ultimate Engineering Guide to Database Selection

Why Database Choice Becomes Expensive After Scaling

Both PostgreSQL and MySQL can serve a small system, but once traffic grows to high concurrency, multi‑service interactions, analytical workloads, and multi‑tenant data volumes, architectural differences amplify. A mismatched choice triggers four chain reactions:

Business patches proliferate (e.g., moving uniqueness checks to Redis, splitting complex queries into multiple calls).

Hidden concurrency problems appear (deadlocks, hotspot updates, replication lag, long‑transaction stalls).

Architecture becomes heavier as caches, search services, async queues, sharding middleware, and compensation frameworks are added.

Operations become costlier (data repair, online DDL, backup/restore, CDC sync, cross‑region disaster recovery).

The core question is not “which features are richer” but “how much complexity you want to keep inside the database kernel versus push to the application layer.”

Quick Verdict: When to Prefer MySQL vs PostgreSQL

MySQL

– engineering‑oriented, excels at generic OLTP, point lookups, short transaction chains, has a broad ecosystem and high team familiarity. PostgreSQL – richer semantics, stronger complex‑query capabilities, better for unified data platforms handling JSON/JSONB, window functions, recursive queries, and expressive constraints.

Eight Engineering Questions to Answer Before Choosing

1. Transaction Granularity

Point‑transactions (single‑row PK lookups) favor MySQL; set‑based or aggregate‑then‑update workflows benefit from PostgreSQL’s expressive SQL.

2. Most Feared Concurrency Issue

Deadlocks

Long transactions

Replication lag

Hotspot contention

Large‑transaction replication amplification

DDL impact on live traffic

Multi‑tenant resource contention

Both databases can solve these, but the operational cost differs.

3. Where Does Complexity Reside?

If the complexity is in the data model (JSON fields, hierarchical relationships, heavy analytics), PostgreSQL can absorb it. If the complexity is in service orchestration, MySQL is sufficient.

4. Need for Read‑Write Separation?

Ask whether delayed reads are acceptable, whether you must read your own writes, and how the system degrades when replication lag occurs.

5. Future Partitioning / Sharding Needs?

Consider table size (billions of rows), hotspot tenants, time‑based archiving, cross‑region deployment, and potential domain splitting.

6. Team Strengths

MySQL‑savvy teams may handle lock‑range and gap‑lock nuances better; PostgreSQL‑savvy teams must manage VACUUM, bloat, and execution‑plan tuning.

7. Strict Data‑Constraint Requirements

PostgreSQL natively supports unique, composite, check, foreign‑key, partial, and expression indexes, making it a better fit for strict correctness.

8. Fault‑Recovery Strategy

Design for node failures, data corruption, performance incidents, and change‑induced failures. Define RPO/RTO, read‑old‑data policies, and post‑recovery validation.

Low‑Level Comparison: Storage, MVCC, Locking, WAL/Redo

Storage Layout

MySQL (InnoDB) uses a clustered primary‑key index; secondary indexes store the primary key, making primary‑key design critical and secondary‑index write amplification a concern.

PostgreSQL stores heap pages separately from indexes; indexes point directly to tuple locations, allowing richer index types (GIN, expression, partial) and more flexible query optimization.

MVCC Differences

MySQL InnoDB keeps old versions in an undo log, which is friendly to short OLTP transactions but suffers from long‑transaction blocking and gap‑lock semantics.

PostgreSQL writes a new tuple for each update; old versions await vacuum. This model offers clear multi‑version semantics for mixed read/write workloads but requires vigilant vacuuming and can suffer from table bloat.

Locking and Isolation

MySQL: gap locks, next‑key locks, repeatable‑read nuances; deadlocks are common and must be observable.

PostgreSQL: row‑level locks are straightforward; no gap‑lock mental overhead, but long transactions still impact vacuum.

WAL / Redo Cost

Both use write‑ahead logs for crash recovery. MySQL’s log volume can affect replication speed; PostgreSQL’s unified WAL simplifies logical replication but still incurs I/O cost for large transactions.

Query Capability Differences

PostgreSQL Advantages

Powerful window functions.

Rich aggregation and multi‑table join optimization.

Native recursive and hierarchical queries.

Strong JSON/JSONB handling.

Expression, partial, and function indexes.

Examples: calculating 30‑day latency percentiles per department, risk‑scoring queries mixing structured and JSON fields, multi‑dimensional SaaS analytics.

MySQL Advantages

Mature primary‑key point lookups and secondary‑index paths.

Deep team experience with execution plans, index tuning, and back‑table costs.

Broad ecosystem, cloud‑managed services, extensive community support.

Typical use cases: user tables, payment transaction logs, marketing sign‑up tables.

High‑Concurrency Real Conflict Points

Hotspot Updates

Inventory decrement, balance updates, flash‑sale slots, per‑tenant config changes all suffer if a single row becomes a contention hotspot, regardless of engine.

Deadlocks

Consistent transaction ordering.

Stable index usage.

Short transactions.

Application‑level idempotent retries.

Monitoring deadlock frequency and context.

Long Transactions

Block version cleanup, increase replication lag, degrade plan caching. Combined with high concurrency they amplify failures.

Replication Lag

Read‑after‑write anomalies can cause duplicate payments or missed events. Solutions include write‑then‑read consistency windows, request‑level routing, and fallback to primary reads.

Large‑Transaction Replication Amplification

Batch writes as a single massive transaction cause primary write spikes, replica lag, and CDC bottlenecks. Mitigation: split into small batches, make each batch idempotent, use business cursors.

Replication, HA, and Fault Recovery

MySQL HA Patterns

Primary‑replica replication.

Semi‑synchronous or enhanced sync.

MGR / InnoDB Cluster.

Proxy‑based read/write routing.

Strengths: abundant documentation, high team familiarity, mature cloud‑managed offerings.

Risks: underestimated lag, switch‑over consistency issues, DDL/write spikes during peak.

PostgreSQL HA Patterns

Streaming replication.

Sync/async replica combos.

Connection‑pooling proxies for failover.

Logical replication for downstream data platforms.

Strengths: flexible logical replication, unified data‑platform suitability, good for CDC and analytics.

Risks: vacuum, bloat, and execution‑plan tuning require experienced DBAs.

Fault‑Recovery Design

Four fault classes must be covered:

Node failures (host, container, disk, network).

Data faults (accidental delete/update, bad scripts).

Performance faults (slow SQL, plan drift, hotspot tenants).

Change faults (DDL, index rollout, version upgrade, sharding migration).

Define RPO/RTO, which paths can tolerate stale reads, which require strong consistency, and practice regular recovery drills.

Scalability Roadmap

Start with a well‑tuned single instance (primary‑key strategy, index count, eliminate full scans, limit long transactions, batch processing, proper connection‑pool sizing, cache hot reads, hot‑cold data split).

Prefer partitioning over sharding when the problem is data volume rather than write hotspot.

Sharding should be a last resort due to cross‑shard transaction and uniqueness challenges.

Multi‑tenant isolation can be achieved via tenant_id columns, partitioning, separate schemas, or separate instances depending on tenant size.

Production‑Level Code and Configuration Samples

PostgreSQL Order Table

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    order_no VARCHAR(64) NOT NULL,
    user_id BIGINT NOT NULL,
    status VARCHAR(32) NOT NULL,
    total_amount_cent BIGINT NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'CNY',
    ext JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    version BIGINT NOT NULL DEFAULT 0,
    UNIQUE (tenant_id, order_no)
);
CREATE INDEX idx_orders_tenant_user_created ON orders (tenant_id, user_id, created_at DESC);
CREATE INDEX idx_orders_tenant_status_created ON orders (tenant_id, status, created_at DESC);
CREATE INDEX idx_orders_ext_gin ON orders USING GIN (ext);

Design intent: tenant‑order uniqueness, JSONB for extensible attributes, optimistic‑lock version column, and indexes that support typical tenant‑scoped list queries.

MySQL Payment Table

CREATE TABLE payment_record (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    biz_order_no VARCHAR(64) NOT NULL,
    payment_no VARCHAR(64) NOT NULL,
    account_id BIGINT NOT NULL,
    amount_cent BIGINT NOT NULL,
    status VARCHAR(32) NOT NULL,
    channel_code VARCHAR(32) NOT NULL,
    idempotency_key VARCHAR(64) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    UNIQUE KEY uk_payment_no (payment_no),
    UNIQUE KEY uk_idempotency_key (idempotency_key),
    KEY idx_biz_order_no (biz_order_no),
    KEY idx_account_created (account_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Design intent: strict uniqueness, idempotency key for payment safety, simple short‑transaction path.

Outbox Pattern (PostgreSQL)

CREATE TABLE order_outbox_event (
    id BIGSERIAL PRIMARY KEY,
    aggregate_type VARCHAR(64) NOT NULL,
    aggregate_id VARCHAR(64) NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    payload JSONB NOT NULL,
    headers JSONB NOT NULL DEFAULT '{}'::jsonb,
    status VARCHAR(16) NOT NULL DEFAULT 'NEW',
    retry_count INT NOT NULL DEFAULT 0,
    next_retry_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    published_at TIMESTAMPTZ
);
CREATE INDEX idx_order_outbox_status_retry ON order_outbox_event (status, next_retry_at, created_at);

Ensures business data and outbound events are written atomically, avoiding “transaction succeeded but message lost” scenarios.

Java Transaction Example

@Service
public class OrderApplicationService {
    private final JdbcTemplate jdbcTemplate;

    @Transactional
    public Long createOrder(CreateOrderCommand cmd) {
        Long orderId = jdbcTemplate.queryForObject(
            """
            INSERT INTO orders (tenant_id, order_no, user_id, status, total_amount_cent, ext)
            VALUES (?, ?, ?, ?, ?, ?::jsonb)
            RETURNING id
            """,
            Long.class,
            cmd.getTenantId(),
            cmd.getOrderNo(),
            cmd.getUserId(),
            "CREATED",
            cmd.getTotalAmountCent(),
            cmd.getExtJson()
        );
        jdbcTemplate.update(
            """
            INSERT INTO order_outbox_event (aggregate_type, aggregate_id, event_type, payload, headers, status)
            VALUES (?, ?, ?, ?::jsonb, ?::jsonb, 'NEW')
            """,
            "Order",
            String.valueOf(orderId),
            "OrderCreated",
            cmd.toEventPayloadJson(orderId),
            cmd.toHeadersJson()
        );
        return orderId;
    }
}

Key steps: write order, write outbox in the same transaction, then let an asynchronous publisher deliver the event.

Outbox Publisher (Spring)

@Component
public class OutboxPublisher {
    private final JdbcTemplate jdbcTemplate;
    private final KafkaTemplate<String, String> kafkaTemplate;

    @Scheduled(fixedDelay = 500)
    public void publish() {
        List<OutboxEvent> events = jdbcTemplate.query(
            """
            SELECT id, aggregate_id, event_type, payload
            FROM order_outbox_event
            WHERE status = 'NEW'
            ORDER BY id
            LIMIT 100
            FOR UPDATE SKIP LOCKED
            """,
            (rs, rowNum) -> new OutboxEvent(
                rs.getLong("id"),
                rs.getString("aggregate_id"),
                rs.getString("event_type"),
                rs.getString("payload")
            )
        );
        for (OutboxEvent event : events) {
            try {
                kafkaTemplate.send("order-events", event.getAggregateId(), event.getPayload()).get();
                jdbcTemplate.update("""
                    UPDATE order_outbox_event SET status = 'DONE', published_at = now() WHERE id = ?
                    """, event.getId());
            } catch (Exception ex) {
                jdbcTemplate.update("""
                    UPDATE order_outbox_event SET retry_count = retry_count + 1, next_retry_at = now() + interval '30 seconds' WHERE id = ?
                    """, event.getId());
            }
        }
    }

    record OutboxEvent(Long id, String aggregateId, String eventType, String payload) {}
}

Uses FOR UPDATE SKIP LOCKED to allow multiple publishers; retries are recorded without rolling back the main business transaction.

Go Query Service (PostgreSQL)

type OrderQueryService struct {
    primary  *pgxpool.Pool
    replicas []*pgxpool.Pool
}

type OrderDTO struct {
    ID               int64
    TenantID         int64
    OrderNo          string
    Status           string
    TotalAmountCent  int64
    CreatedAt        time.Time
}

func (s *OrderQueryService) GetOrder(ctx context.Context, tenantID int64, orderNo string, strong bool) (*OrderDTO, error) {
    db := s.primary
    if !strong && len(s.replicas) > 0 {
        db = s.replicas[0]
    }
    row := db.QueryRow(ctx, `
        SELECT id, tenant_id, order_no, status, total_amount_cent, created_at
        FROM orders
        WHERE tenant_id = $1 AND order_no = $2
    `, tenantID, orderNo)
    var dto OrderDTO
    if err := row.Scan(&dto.ID, &dto.TenantID, &dto.OrderNo, &dto.Status, &dto.TotalAmountCent, &dto.CreatedAt); err != nil {
        return nil, errors.New("order not found")
    }
    return &dto, nil
}

The strong flag forces reads from the primary when read‑your‑own‑write consistency is required.

PostgreSQL Configuration Sample

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 32MB
autovacuum = on
autovacuum_max_workers = 6
autovacuum_naptime = 15s
max_connections = 300
idle_in_transaction_session_timeout = 60s
statement_timeout = 30s

MySQL Configuration Sample

[mysqld]
max_connections = 300
innodb_buffer_pool_size = 8G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
binlog_format = ROW
transaction_isolation = READ-COMMITTED
tmp_table_size = 64M
max_heap_table_size = 64M

Decision Matrix (Converted to List)

Dimension: Main Business Type – MySQL: Standard OLTP; PostgreSQL: OLTP + complex analytics.

Typical Query – MySQL: PK point lookups, short transactions; PostgreSQL: Multi‑table joins, aggregates, recursion, windows.

Data Model – MySQL: Regular, stable; PostgreSQL: Complex, fast‑evolving, many extensible fields.

Team Experience – MySQL: Deep MySQL expertise; PostgreSQL: Deep PostgreSQL expertise.

Read/Write Pattern – MySQL: High‑frequency point reads/writes; PostgreSQL: Mixed reads, heavy analytical reads.

Multi‑Tenant Complexity – MySQL: Low‑to‑medium; PostgreSQL: Medium‑to‑high.

JSON Capability Need – MySQL: General; PostgreSQL: Strong.

Unified Data‑Platform Demand – MySQL: General; PostgreSQL: Strong.

Ecosystem & Popularity – MySQL: Very strong; PostgreSQL: Strong.

Complexity Kept in DB – MySQL: Relatively low; PostgreSQL: Relatively high.

Common Pitfalls and Avoidance Checklist

Mis‑judgment 1: Relying Solely on Benchmarks

Benchmarks ignore lock contention, replication lag, failure compensation, change rollout, and data‑recovery costs.

Mis‑judgment 2: Offloading DB Shortcomings to Middleware

Adding caches, message queues, or sharding middleware increases system complexity; if the DB can handle the requirement natively, prefer that.

Mis‑judgment 3: Assuming DBA Skill Equals DB Capability

A database is only as reliable as the team that can read execution plans, monitor locks, manage replication, perform recovery drills, and execute online DDL safely.

Mis‑judgment 4: Premature Sharding

First master single‑instance tuning (index hygiene, connection‑pool sizing, hot‑spot mitigation) before considering sharding.

Mis‑judgment 5: Treating JSON Support as Equal

Assess query, index, update, and aggregation capabilities for JSON rather than just the presence of a JSON column type.

Mis‑judgment 6: Ignoring Future Data Evolution

Plan for table growth, new sub‑domains, and additional CDC pipelines; a short‑term fit may become a long‑term liability.

Final Recommendation

Databases are not interchangeable components; they are integral parts of business architecture. Choose the one that yields the lowest total cost of ownership given your data complexity, team expertise, scalability path, and fault model.

If you need a standard OLTP engine, fast delivery, and a MySQL‑savvy team → MySQL.

If you need rich data semantics, complex queries, strong JSON support, and a team comfortable with PostgreSQL → PostgreSQL.

If both high‑throughput transaction processing and a unified analytical platform are required, adopt a domain‑split approach: MySQL + PostgreSQL with clear ownership and Outbox/CDC for cross‑domain consistency.

One‑Page Quick Decision Guide

Standard OLTP, team familiar with internet‑scale back‑ends, fast delivery → MySQL.

Complex data model, heavy analytics, extensive JSON usage → PostgreSQL.

Transaction‑critical domains coexist with complex data platforms → combine MySQL + PostgreSQL per domain.