Matheus

Posted on Feb 21 • Originally published at releaserun.com

PostgreSQL vs MySQL: A 2026 Production Decision Framework

#postgres #database #mysql #devops

Picking a database for a new service in 2026 is less about "features" and more about what fails first in production: write concurrency, query shape drift, operational ergonomics, and how painful the first schema evolution will be.

This framework treats PostgreSQL vs MySQL as an operator decision: score your workload, look for tipping points, then validate with concrete EXPLAIN patterns and early-life metrics.

Production-First Decision Rubric

Start by treating the database choice like capacity planning: define the dominant workload, then pick the engine that degrades more gracefully when your assumptions are wrong.

When Either Is Fine

If you have a classic OLTP service (orders, users, subscriptions), moderate concurrency, and you keep JSON usage light, both PostgreSQL and MySQL will work. The decision is usually driven by team familiarity, managed service maturity in your cloud, and ecosystem.

Tipping Points (Hard Decision Triggers)

JSON is part of your query API (filtering, containment, dynamic attributes) -> default to PostgreSQL. You'll ship fewer schema contortions.
You need row-level security (RLS) as a first-class primitive for multi-tenant isolation -> PostgreSQL.
You expect very high read scaling with simple query patterns (key lookups, small range scans) and want the most standard replication playbook -> MySQL is a safe default.
Your write load is hotspot-heavy -> either can work, but you must test the exact pattern.
You want one engine for "SQL + weird queries" (text search, custom operators, partial indexes, advanced constraints) -> PostgreSQL.

JSON & Indexing in Practice

Both databases can store JSON. The production difference is indexing flexibility and how often you'll have to redesign when product adds "one more filter."

PostgreSQL: JSONB + GIN

Use jsonb when you need to query inside documents. A common pattern is "base columns for stable fields, JSONB for optional attributes."

CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  tenant_id   bigint NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  type        text NOT NULL,
  attrs       jsonb NOT NULL
);

CREATE INDEX events_attrs_gin ON events USING gin (attrs);
CREATE INDEX events_tenant_created_at ON events (tenant_id, created_at DESC);

What to watch:

GIN index size and update cost. Great for read/query flexibility; it's not free for write-heavy workloads.
Operator choice matters (@>, ->, ->>, ?|, etc.). You need query discipline or you'll miss indexes.

MySQL: JSON + Generated Columns

MySQL's pragmatic approach: extract the JSON paths you care about into generated columns, then index those columns. Less flexible than GIN, but predictable.

CREATE TABLE events (
  id         bigint unsigned NOT NULL AUTO_INCREMENT,
  tenant_id  bigint NOT NULL,
  created_at timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  type       varchar(64) NOT NULL,
  attrs      json NOT NULL,
  status     varchar(32)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.status'))) STORED,
  PRIMARY KEY (id),
  KEY events_tenant_created_at (tenant_id, created_at),
  KEY events_status (status)
) ENGINE=InnoDB;

Trade-off: When product adds attrs.customer.segment as a new filter, you're doing a schema change (add generated column + index) instead of relying on a general-purpose JSONB index.

Rule of Thumb for JSON in 2026

PostgreSQL when JSON is "query surface area" (customers filter/sort on it, internal tools build ad-hoc queries, schema evolves weekly).
MySQL when JSON is "payload storage" and you can name the 3-10 JSON paths that matter for indexing.

Concurrency, Locking, and What Hurts at Scale

PostgreSQL: MVCC + Vacuum Is the Price

Postgres uses MVCC; readers don't block writers. Under high churn, dead tuples accumulate and must be reclaimed.

Common gotchas:

Long-running transactions prevent vacuum from cleaning up. A "harmless" open transaction in a job worker can degrade the whole table.
High update rate tables need tuned autovacuum settings per table, not just global defaults.

MySQL/InnoDB: Locking Is Usually Fine... Until It Isn't

InnoDB also uses MVCC, but you'll run into practical locking issues through next-key locks and gap locks.

Common gotchas:

Updates without a good index can lock far more rows than you expect.
Secondary index overhead in write-heavy tables: every secondary index is extra work on inserts/updates.
Online DDL isn't magic. Index builds and schema changes still create load and replica lag.

Concrete Pattern: "Claim a Job" Queue

This is a concurrency litmus test.

PostgreSQL (SKIP LOCKED is first-class):

WITH next_job AS (
  SELECT id FROM jobs
  WHERE run_at <= now() AND locked_at IS NULL
  ORDER BY run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs SET locked_at = now(), locked_by = $1
WHERE id IN (SELECT id FROM next_job)
RETURNING *;

MySQL supports similar patterns in modern versions, but you must validate exact behavior under your isolation level and indexing.

Query Planning: Verify Early with EXPLAIN

Don't decide based on anecdotes. Run representative queries, then validate that the optimizer uses the indexes you expect.

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events
WHERE tenant_id = 42
  AND attrs @> '{"status":"failed"}'
ORDER BY created_at DESC LIMIT 50;

You want: index scans on (tenant_id, created_at), low shared read buffers after warmup, no unexpected sequential scans.

MySQL

EXPLAIN ANALYZE
SELECT id FROM events
WHERE tenant_id = 42 AND status = 'failed'
ORDER BY created_at DESC LIMIT 50;

You want: chosen key matches your intended composite index, rows examined close to rows returned.

Indexing Reality Check

If every query is tenant-scoped, your indexes should be tenant-prefixed. This is the most common "we built indexes but latency still sucks" mistake.

Ops & Reliability

Backups and PITR

PostgreSQL PITR is built around base backups + WAL archiving.
MySQL PITR is typically built from full backups plus binary logs.

Production rubric: pick the managed offering where you can actually test restores in automation. Measure restore time on realistic dataset size.

Schema Migrations

PostgreSQL: CREATE INDEX CONCURRENTLY avoids blocking writes but takes longer and has failure modes.

MySQL: Online DDL capabilities are good in modern MySQL/InnoDB, but large index builds still create load, IO pressure, and replica lag.

First 30 Days: What to Measure

PostgreSQL Metrics

Autovacuum health: dead tuples, bloat indicators
Transaction age: long-running transactions
WAL volume: spikes after deploys/backfills
Lock waits: blocked DDL, hot table contention
Replication lag

MySQL Metrics

Replication lag: seconds behind source
Lock waits / deadlocks: surface missing indexes early
Buffer pool hit rate: memory sizing and working set fit
Redo/binlog volume: spikes from migrations
Rows examined vs rows returned

If you don't have time to instrument both deeply, that's also a signal: pick the engine your team already knows how to run under incident pressure.

Bottom Line

Use PostgreSQL when your service will accumulate query complexity, when JSON is part of the product's query surface, or when multi-tenant isolation needs first-class primitives like RLS. You're signing up to operate vacuum and bloat consciously.

Use MySQL when your workload is predictable, your query patterns are stable, and you want the most common operational playbooks for replication and read scaling.

If you're still undecided after scoring: implement the JSON and concurrency patterns you expect to be painful, run them under load, and pick the engine whose failure mode you can live with.

DEV Community