PostgreSQL vs MySQL: Data Types, JSON, Locking, Replication and When to Use Each

By 0xmote · Aug 4, 2026 · 24 min read · Updated Sep 25, 2026 Intermediate tutorial

Key takeaways

PostgreSQL and MySQL are both excellent relational databases, but they excel in different scenarios. This guide covers concrete differences in types, JSON, full-text search, locking, and replication — so you can make the right choice for your project.

Quick Decision Matrix

Use PostgreSQL when:
  - Complex queries with JOINs and aggregations
  - JSON/JSONB document storage alongside relational data
  - Full-text search without external engine
  - Custom data types, enums, arrays
  - Geospatial data (PostGIS extension)
  - Strict SQL standard compliance matters
  - Strong ACID requirements with complex transactions

Use MySQL when:
  - Simple read-heavy workloads
  - Existing MySQL infrastructure
  - PlanetScale (MySQL-compatible serverless DB)
  - WordPress, Drupal, or other MySQL-native CMS
  - Team expertise is MySQL-based
  - Simpler replication/cluster setup is a priority

Executive summary

If you are choosing a relational engine for a new system, the honest answer is that both PostgreSQL and MySQL are credible choices at scale, but they optimize for different pain points. PostgreSQL tends to win when the database must behave like a programmable analytical engine inside your stack: rich types, standards-oriented SQL, strong indexing for semi-structured data, and extensibility. MySQL (with InnoDB) tends to win when the workload is dominated by simple, predictable primary-key and indexed lookups on a stack where operational familiarity and host ecosystem breadth matter as much as raw SQL feature depth. The sections below are written to be comparative, not competitive: pick the tool whose failure modes and operational model match your team and product.

Dimension	PostgreSQL (typical strengths)	MySQL 8 + InnoDB (typical strengths)
SQL & analytics	Window functions, CTEs, `FILTER`, `LATERAL`, rich planner	Solid core SQL; fewer niceties; optimizer improves steadily
Semi-structured data	`JSONB`, GIN/GiST, powerful containment	`JSON` type + generated columns; path indexes; less flexible
Full-text	Tsearch2 / FTS with dictionaries, weights, rank	`FULLTEXT` indexes; good for “good enough” search
Extensibility	Extensions (PostGIS, `pg_trgm`, etc.)	Server plugins exist; different culture (fewer in-DB extensions)
Ops culture	Patroni, logical replication, strong HA patterns	Mature async replication, Group Replication, wide hosting

Performance: OLTP vs OLAP and what benchmarks actually show

Workload shape matters more than the logo on the box. In practice, OLTP systems are dominated by short transactions, B-tree point lookups, and a stable working set in buffer cache. Both engines perform well when indexes match access paths and the application avoids “chatty” SQL. OLAP and reporting—large scans, complex joins, windowed aggregates—tend to favor PostgreSQL’s query planner and feature set, though either database can be tuned for read replicas and separate reporting schemas.

Public benchmarks (for example, TPC-C-style and sysbench tests you will find in vendor blogs) are useful only as a sanity check because they are sensitive to hardware, buffer pool size, fsync policy, and tuning. A representative pattern in published comparisons is that MySQL can lead on narrow, read-heavy, single-row microbenchmarks when the dataset is small and hot in memory, while PostgreSQL more often leads on query shapes that require sophisticated joins, sorting, and aggregation—but your indexes and statistics will dominate either result.

Scenario	What to measure	Fair interpretation
Key-value-like reads	Point selects by PK, p95/p99 latency	Often similar; verify connection pooling and client batching
Write throughput	Sustained inserts/updates, checkpoint stalls	Both need tuning (`checkpoint_timeout`, `innodb_flush_*`, autovacuum)
Analytical SQL	Multi-way joins, large sorts, CTEs	PostgreSQL frequently easier to express and tune; consider DuckDB/ClickHouse for heavy OLAP
Mixed JSON + SQL	Filter + sort on JSON paths	PostgreSQL `JSONB` + GIN is usually less painful at scale

Rule of thumb for fairness: if performance is a gate, build a benchmark clone of your top five queries and your top two write patterns on identical hardware, then compare p95 and disk bytes written, not a single “QPS” headline number.

Native arrays, constraints, and modeling flexibility

PostgreSQL ships first-class array types and rich constraints (including EXCLUDE for non-overlapping ranges) that are awkward or impossible in stock MySQL. MySQL 8.0+ closes many gaps (check constraints, functional indexes, better JSON functions), but array semantics and exclusion constraints still push people toward PostgreSQL for complex domain modeling (scheduling, tagging, and multi-valued attributes without join tables). Neither choice is “wrong” if you can model the domain cleanly; the cost is where it shows up: join complexity in MySQL vs. more PostgreSQL-specific knowledge on the team.

Feature	PostgreSQL	MySQL 8 (InnoDB)
Arrays	Native `text[]`, `int[]`, operators, GIN	Workarounds: JSON, separate tables, or strings
Range / exclusion	`tstzrange`, `EXCLUDE USING gist`	Manual checks or triggers
Generated columns	`GENERATED` columns (stored)	`GENERATED` columns + functional indexes (good parity)
Check constraints	Long-standing, widely used	Enforced only from MySQL 8.0.16; earlier versions parsed and silently ignored them

The booking table is the example that best shows why exclusion constraints matter:

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- lets GiST handle the plain INT column
CREATE TABLE booking (
    room_id  INT,
    occupied DATERANGE,
    EXCLUDE USING GIST (room_id WITH =, occupied WITH &&)  -- no overlapping bookings per room
);

The constraint says “no two rows may have the same room_id and overlapping occupied ranges”, and the database enforces it atomically, even under concurrent inserts. The room_id WITH = part is essential: writing only occupied WITH && would forbid any two bookings from overlapping across all rooms. Mixing an equality check on an integer with a range overlap in one GiST index requires the btree_gist extension, which ships with PostgreSQL but must be enabled. A conflicting insert fails with ERROR: conflicting key value violates exclusion constraint, which the application can translate into “room already booked”. In MySQL the same guarantee needs locking or SERIALIZABLE transactions in application code, and it is easy to get wrong. If you inherit an older MySQL schema, also remember the CHECK note in the table above: constraints that look enforced may never have been.

Data Types Comparison

-- Auto-increment primary keys
-- MySQL
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ...
);

-- PostgreSQL (modern way)
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ...
);

-- PostgreSQL (traditional, still works)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,  -- shorthand for INTEGER + sequence
  ...
);

-- String types
-- MySQL
VARCHAR(255)    -- variable length, up to 255 chars
TEXT            -- up to 65KB
MEDIUMTEXT      -- up to 16MB
LONGTEXT        -- up to 4GB

-- PostgreSQL
VARCHAR(255)    -- variable length (rarely needed — use TEXT)
TEXT            -- unlimited length (no performance difference from VARCHAR)
-- PostgreSQL has NO size limit on TEXT — use it everywhere

-- PostgreSQL extra types MySQL lacks:
CITEXT          -- case-insensitive text (extension)
UUID            -- native UUID type (MySQL stores as VARCHAR)
INET            -- IP address
MACADDR         -- MAC address
MONEY           -- monetary amounts
BYTEA           -- binary data (MySQL: BLOB)

-- Temporal types
-- MySQL
DATETIME        -- YYYY-MM-DD HH:MM:SS (no timezone)
TIMESTAMP       -- stored as UTC, displayed in server timezone
DATE, TIME

-- PostgreSQL
TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)  -- stores UTC, shows with offset
TIMESTAMP WITHOUT TIME ZONE             -- no timezone info
DATE, TIME, INTERVAL                    -- interval: '2 hours 30 minutes'

-- PostgreSQL is stricter: use TIMESTAMPTZ for any production timestamp
-- MySQL TIMESTAMP has 2038 problem (32-bit Unix timestamp)

JSON Support

PostgreSQL’s JSONB is a significant advantage for mixed relational/document storage.

-- PostgreSQL JSONB
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  attributes JSONB            -- Binary JSON — indexed, queryable
);

-- Insert
INSERT INTO products (name, attributes) VALUES
  ('Laptop', '{"brand": "Dell", "specs": {"ram": 16, "storage": 512}, "tags": ["gaming", "work"]}');

-- Query JSONB fields
SELECT name, attributes->>'brand' AS brand
FROM products
WHERE attributes->>'brand' = 'Dell';

-- Deep path access
SELECT name, attributes->'specs'->>'ram' AS ram_gb
FROM products
WHERE (attributes->'specs'->>'ram')::int > 8;

-- Array containment
SELECT * FROM products
WHERE attributes->'tags' ? 'gaming';      -- contains key/element

SELECT * FROM products
WHERE attributes @> '{"specs": {"ram": 16}}';  -- contains subset

-- Index on JSONB — critical for performance
CREATE INDEX idx_products_brand ON products USING GIN (attributes);
-- Or for specific field:
CREATE INDEX idx_products_brand_btree ON products ((attributes->>'brand'));

-- Update specific field (without replacing entire JSON)
UPDATE products
SET attributes = jsonb_set(attributes, '{specs, ram}', '32')
WHERE id = 1;

-- MySQL JSON (added in 5.7, improved in 8.0)
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  attributes JSON
);

-- Query
SELECT name, JSON_EXTRACT(attributes, '$.brand') AS brand
FROM products
WHERE JSON_EXTRACT(attributes, '$.brand') = '"Dell"';

-- Shorthand
SELECT name, attributes->>'$.brand' AS brand
FROM products;

-- MySQL's JSON type is also stored in a binary format (5.7+),
-- but there is no GIN-style index over the whole document —
-- you index specific paths with a functional index (8.0.13+)
CREATE INDEX idx_brand ON products ((CAST(attributes->>'$.brand' AS CHAR(64)) COLLATE utf8mb4_bin));

Winner: PostgreSQL — JSONB supports GIN indexes over the whole document (fast containment queries) and has richer operators.

The practical difference is when you decide what to index. MySQL can index JSON, but indirectly: a generated column that extracts a value (user_id INT AS (data->>'$.user_id') STORED) with a normal index, or a functional index on the expression. MySQL 8.0.17 added multi-valued indexes for JSON arrays (MEMBER OF, JSON_CONTAINS), which covers the common “tags” case. Either way you choose the paths up front, whereas a GIN index on a JSONB column serves containment queries on any key.

PostgreSQL has its own subtlety here. The default GIN index on a JSONB column accelerates the containment operator @>, but not data ->> 'user_id' = '1234', which is a text comparison on an extracted value. A query that filters only with ->> scans the table even though “the JSON column is indexed”. Either rewrite such conditions as containment (data @> '{"user_id": "1234"}') or create an expression index on (data ->> 'user_id').

Full-Text Search

-- PostgreSQL built-in FTS
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  body TEXT,
  search_vector TSVECTOR GENERATED ALWAYS AS (
    setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(body, '')), 'B')
  ) STORED
);

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Search
SELECT id, title,
  ts_rank(search_vector, query) AS rank
FROM articles,
  to_tsquery('english', 'javascript & (tutorial | guide)') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Highlight matching terms
SELECT title,
  ts_headline('english', body, to_tsquery('javascript'), 'MaxWords=20') AS excerpt
FROM articles
WHERE search_vector @@ to_tsquery('english', 'javascript');

-- MySQL FULLTEXT (simpler, less powerful)
CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  body TEXT,
  FULLTEXT INDEX idx_search (title, body)
);

-- Boolean mode search
SELECT id, title,
  MATCH(title, body) AGAINST('javascript tutorial' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST('+javascript +tutorial' IN BOOLEAN MODE)
ORDER BY score DESC;

-- MySQL FULLTEXT limitations:
-- Minimum word length (default 4 chars)
-- No phrase proximity search
-- English-only stemming
-- No custom ranking control

Winner: PostgreSQL — More powerful with configurable dictionaries, proximity search, rich ranking, and the ability to index JSONB alongside text.

Transactions and Locking

-- PostgreSQL: MVCC (Multi-Version Concurrency Control)
-- Readers never block writers, writers never block readers

-- Isolation levels
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- default
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;    -- strictest

-- PostgreSQL serializable actually detects and prevents anomalies
-- (MySQL serializable uses locks, not true serializability)

-- Explicit row locking
SELECT * FROM orders WHERE id = 1 FOR UPDATE;           -- lock for update
SELECT * FROM orders WHERE id = 1 FOR UPDATE SKIP LOCKED; -- skip locked rows (queue pattern)
SELECT * FROM orders WHERE id = 1 FOR SHARE;            -- shared lock

-- MySQL InnoDB also uses MVCC
-- But has some differences:

-- Phantom reads in REPEATABLE READ:
-- MySQL prevents them with gap locks (can cause deadlocks)
-- PostgreSQL prevents them with MVCC snapshot (no gap locks)

-- Table locking:
-- MySQL MyISAM: table-level locks (avoid for writes)
-- MySQL InnoDB: row-level locks (same as PostgreSQL)

The same isolation name does not mean the same behavior. At REPEATABLE READ, PostgreSQL gives the transaction a snapshot and, if you try to update a row another transaction changed after your snapshot, aborts with ERROR: could not serialize access due to concurrent update — your code must retry. InnoDB’s REPEATABLE READ reads from a snapshot for plain SELECTs but uses current data plus next-key (gap) locks for UPDATE, DELETE, and locking reads, so it rarely aborts but can block, and it can deadlock in ways that surprise people coming from PostgreSQL (ERROR 1213 (40001): Deadlock found when trying to get lock). Both databases therefore need retry logic for some error codes; they just produce them in different situations. Also note the defaults differ: code that assumes a stable snapshot for a whole transaction works on InnoDB’s REPEATABLE READ default but not on PostgreSQL’s READ COMMITTED default, so set the level explicitly when it matters.

With FOR UPDATE (and the SKIP LOCKED queue pattern, available in PostgreSQL 9.5+ and MySQL 8.0+), the usual mistake is doing slow work — an HTTP call, a file upload — between the SELECT ... FOR UPDATE and the COMMIT. The row lock is held the whole time, so concurrent requests queue up behind it; keep locked sections short and do external calls outside the transaction.

Transactional DDL (PostgreSQL only)

-- PostgreSQL: DDL inside a transaction
BEGIN;
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
ROLLBACK;  -- the column is gone

-- MySQL: DDL causes an implicit COMMIT
BEGIN;
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;  -- implicit COMMIT here
ROLLBACK;  -- no effect, the column was already committed

The value shows up when a migration fails halfway. On PostgreSQL a migration that adds a column, backfills it, and adds a constraint either completes or leaves nothing behind. On MySQL, if the third statement fails, the first two are already committed and the migration tool’s bookkeeping may say the migration never ran — the next attempt then fails on “Duplicate column name”. Keep MySQL migrations to one DDL statement each, or make them idempotent. PostgreSQL has exceptions too: CREATE INDEX CONCURRENTLY (the way to add an index to a busy table without blocking writes) cannot run inside a transaction block, so migration tools need a per-migration “no transaction” switch for it. And transactional DDL does not mean lock-free DDL: ALTER TABLE takes an ACCESS EXCLUSIVE lock, and if it waits behind a long-running query, every query queued behind it waits too. Setting lock_timeout (for example SET lock_timeout = '5s') before DDL on busy tables turns a potential outage into a failed, retryable migration.

ACID and “strictness” in the real world

Atomicity, consistency, isolation, durability are not marketing labels—they are the contract your application leans on when you say “one logical operation.” InnoDB and PostgreSQL both offer durable commits with MVCC-style read isolation, but the details of isolation behavior and locking differ enough that you should not assume one engine’s REPEATABLE READ means the same as the other’s in edge cases (phantom reads, write skew, and deadlock patterns).

Topic	PostgreSQL (practical note)	MySQL 8 + InnoDB (practical note)
Default isolation	`READ COMMITTED` (configurable)	`REPEATABLE READ` in InnoDB (snapshot semantics with gap locks)
“Strong” serializability	`SERIALIZABLE` + SSI-style protection (fails fast on dangerous patterns)	`SERIALIZABLE` exists; verify behavior for your specific edge cases in tests
Partial writes	Statement-level atomicity; multi-stmt transactions as expected with proper client usage	InnoDB groups row changes; avoid mixing transactional engines in one statement
Replication vs “truth”	Async replicas can be stale; use sync rep or read-your-writes strategies	Classic async topologies: measure replica lag; avoid assuming instant reads on replicas
Foreign keys	Enforced with clear errors	`FOREIGN_KEY_CHECKS` and engine choice must be right (InnoDB)

A fair take: if your app truly requires serious isolation proofs, the database choice is necessary but not sufficient—you still need clear transaction boundaries, idempotency, and test coverage for race conditions. PostgreSQL’s richer isolation documentation and SKIP LOCKED queue patterns are frequently cited in systems that process ordered work; MySQL is widely proven for very high simple read/write QPS with careful tuning and pooling.

Window Functions and CTEs

Both support these, but PostgreSQL has broader support.

-- Window functions (both support)
SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;

-- CTEs (both support)
WITH ranked_sales AS (
  SELECT
    salesperson_id,
    SUM(amount) AS total,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
  FROM sales
  GROUP BY salesperson_id
)
SELECT * FROM ranked_sales WHERE rank <= 10;

-- Recursive CTEs (both support)
WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, depth + 1
  FROM employees e
  JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart ORDER BY depth;

-- PostgreSQL extras:
-- FILTER clause in aggregations
SELECT
  COUNT(*) FILTER (WHERE status = 'active') AS active_count,
  COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

-- LATERAL joins
SELECT u.name, recent.order_id
FROM users u,
LATERAL (
  SELECT order_id FROM orders WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) AS recent;

Indexes

-- Standard B-tree index (both)
CREATE INDEX idx_users_email ON users (email);

-- Partial index — PostgreSQL (index subset of rows)
CREATE INDEX idx_active_users ON users (email) WHERE active = true;
-- Only indexes active users — smaller, faster for queries with WHERE active = true

-- Composite index
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- GIN index — PostgreSQL (arrays, JSONB, full-text)
CREATE INDEX idx_tags ON posts USING GIN (tags);         -- array column
CREATE INDEX idx_meta ON posts USING GIN (metadata);     -- JSONB column

-- GiST index — PostgreSQL (geometric, range types, full-text)
CREATE INDEX idx_location ON places USING GIST (coordinates);  -- PostGIS

-- MySQL-specific
-- FULLTEXT index
CREATE FULLTEXT INDEX idx_search ON articles (title, body);

-- Invisible index (MySQL 8.0) — test impact without dropping
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

-- Both: covering index (include extra columns to avoid table lookups)
-- PostgreSQL:
CREATE INDEX idx_orders_covering ON orders (user_id) INCLUDE (status, total);
-- MySQL:
CREATE INDEX idx_orders_covering ON orders (user_id, status, total);

The partial index is one of PostgreSQL’s most useful tools for queue-like tables: if 99% of orders are completed and queries only ever look for pending ones, an index WHERE status = 'pending' contains just the pending rows, stays small enough to live in memory, and costs nothing when completed rows are updated. MySQL has no equivalent; the closest workaround is a generated column that is NULL for rows you don’t care about, indexed normally. On the MySQL side, USING HASH is accepted by InnoDB but silently created as a B-tree — true hash indexes exist only for the MEMORY engine (InnoDB’s “adaptive hash index” is an internal cache you don’t create). PostgreSQL hash indexes are real and crash-safe since version 10, but a B-tree is almost always just as good for equality and also supports ranges and ordering.

InnoDB also clusters every table by its primary key. That makes primary-key lookups and range scans very fast, but random UUID primary keys expensive, since every insert lands in a random page of the clustered index; time-ordered IDs (UUIDv7, ULID) or an integer key avoid this.

Replication

PostgreSQL replication options:
  Streaming replication (built-in)
    - Primary → one or more standbys
    - Synchronous or asynchronous
    - Standbys can be used for read queries
  Logical replication (PostgreSQL 10+)
    - Table-level, row-level filtering
    - Replicate to different PostgreSQL versions
  Third-party: Patroni (HA), pgpool-II (connection pooling + HA)

MySQL replication options:
  Classic async replication (simple, widely used)
  Semi-synchronous replication (primary waits for at least one replica)
  Group Replication (multi-primary, built-in)
  InnoDB Cluster (MySQL Shell + Group Replication)
  ProxySQL (connection routing to primary/replicas)

-- PostgreSQL: check replication status
SELECT
  client_addr,
  state,
  sent_lsn - write_lsn AS write_lag,
  sent_lsn - flush_lsn AS flush_lag,
  sent_lsn - replay_lsn AS replay_lag
FROM pg_stat_replication;

-- MySQL: check replication status
SHOW REPLICA STATUS\G
-- Shows: Seconds_Behind_Source, Replica_IO_Running, Replica_SQL_Running

Replication in both is asynchronous by default, which has an application-level consequence many teams discover in production: a user saves a record, the next page load reads from a replica that is a few hundred milliseconds behind, and the record appears to be missing. Route reads that must see the user’s own writes to the primary, or wait for the replica to catch up, rather than treating replicas as transparent copies.

High availability, failover, and what “zero downtime” really costs

Neither database magically eliminates outages. The difference is in patterns your team is comfortable running. PostgreSQL is often paired with Patroni, repmgr, or managed RDS/Aurora/Cloud SQL/Neon-style services that provide automated failover, backups, and PITR. MySQL has decades of experience with async primaries and replicas, ProxySQL routing, and InnoDB Cluster / Group Replication for multi-primary (with operational complexity). A candid comparison: MySQL’s replication ecosystem is ubiquitous; PostgreSQL’s logical replication is powerful for selective upgrades and data movement but requires explicit monitoring of slots and LSNs.

HA need	Common PostgreSQL approach	Common MySQL approach
Automatic failover	Patroni, cloud HA agents	Orchestrator, cloud HA, InnoDB Cluster tooling
Read scaling	Hot standbys, logical subscribers	One primary + N replicas, routes via proxy
Geographic distribution	Logical replication, careful conflict rules	Async replication, multi-region with lag awareness
Online schema changes	`pg_repack`, managed blue/green, careful locks	`pt-online-schema-change`, `gh-ost` culture is strong in MySQL shops

Fair warning: the fastest way to a bad day is a split-brain or a replica that answers stale reads as if fresh. If you are building payments or inventory, invest in explicit session routing and monitor lag p99.

Scalability, sharding, and when to look beyond a single node

Vertical scaling (bigger instance, better disks, more RAM) remains the first lever for most OLTP. Read replicas help read-heavy, eventually-consistent use cases. Sharding (partitioning data across many primaries) is a product decision as much as a database decision. Application-level sharding (Vitess, custom tenant keys) is common in large MySQL estates; in PostgreSQL, Citus and distributed patterns exist but are not universal defaults.

Path	When it fits	Cost / trade-off
Bigger node + good indexes	Sub-ms reads, working set in memory	Simpler ops; eventual ceiling on CPU/IO
Read replicas	Dashboards, search indexes, report queries	Staleness, routing complexity, duplicate cache invalidation work
Application sharding	Very large, tenant-isolated data	Migrations, cross-shard queries, heavy engineering
Specialized store	Pure OLAP, time-series at huge scale	Second system to operate (often worth it)

A balanced judgment: if you are a typical web product, a single well-tuned instance plus replicas takes you very far. If you know you will outgrow it, start with a sharding key in your data model even before you split physical nodes—it is painful to retro-fit.

Ecosystem: drivers, hosting, and operations

Layer	PostgreSQL	MySQL / MariaDB
Drivers (Node, Go, etc.)	`pg` / `pgx` / `libpq` family	`mysql2`, official connectors; massive examples online
ORMs	Prisma, Drizzle, SQLAlchemy, Rails-first-class	Same ORMs, excellent Laravel/WordPress heritage
Migrations	`sqitch`, `flyway`, `liquibase`, Prisma Migrate	Same; MySQL’s DDL + locking behavior differs (measure on large tables)
Managed cloud	RDS, Aurora PG, Cloud SQL, Azure PG, Neon, etc.	RDS, Aurora MySQL, Cloud SQL, PlanetScale (MySQL protocol)
Observability	`pg_stat_statements`, `auto_explain`, great logs	`performance_schema`, `EXPLAIN ANALYZE` (8.0+), slow log

This is an ecosystem tie for many teams: pick what your SREs already run at night. The ecosystem advantage often goes to the engine your hosting provider and framework defaults optimize for.

Use-case-based selection (without tribalism)

You are building	Sensible default	Why it is a reasonable bias
B2B SaaS with complex reporting	PostgreSQL	Rich SQL, `JSONB`, analytics-friendly
High-QPS, simple queries, existing MySQL SREs	MySQL 8 (InnoDB)	Mature playbooks, huge community corpus
Geospatial	PostgreSQL + PostGIS	Industry standard for GIS in SQL
WordPress / PHP CMS	MySQL / MariaDB	Ecosystem and hosting defaults
Serverless/edge	Whichever the vendor nails (e.g. Neon, PlanetScale)	Vendor product can matter more than engine theology

If two cells both seem true, use the one your team can operate safely. A slightly “weaker” database with strong backups and on-call runbooks beats a “stronger” engine with nobody watching replication lag.

Production experience (patterns that survive audits)

What actually breaks in production is rarely a missing SQL function—it is connection storms, long transactions holding locks, N+1 queries, and unbounded background jobs hammering the primary. I have seen PostgreSQL instances shine in organizations that invested in pgbouncer pool sizing, regular VACUUM health checks, and pg_stat_statements to kill regressions. I have seen MySQL fleets carry enormous traffic with disciplined schema discipline (proper InnoDB, no accidental MyISAM), careful replication lag dashboards, and gh-ost for low-drama migrations. Both engines can surprise you after major version upgrades—always run replay tests and check planner changes, not just “it boots.” A fair lesson: standardize on migrations in CI, synthetic canaries for your hottest queries, and automated restore drills. Those practices pay for either database—and they pay for your sleep.

Performance Tips

-- EXPLAIN ANALYZE (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id;

-- Key things to look for:
-- Seq Scan — missing index
-- Hash Join vs Nested Loop vs Merge Join
-- Actual rows vs Estimated rows (large diff = stale statistics)
-- Buffers: hit vs read (cache hit ratio)

-- Update statistics
ANALYZE users;  -- Update table statistics
VACUUM ANALYZE; -- Reclaim space + update statistics

-- EXPLAIN in MySQL
EXPLAIN FORMAT=JSON
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.id;

-- Check index usage
SHOW INDEX FROM users;
SELECT * FROM information_schema.STATISTICS WHERE table_name = 'users';

-- Connection pooling (critical for both)
-- PostgreSQL: PgBouncer
-- MySQL: ProxySQL or MySQL Router

-- PostgreSQL: table bloat (MVCC creates dead tuples)
-- Regular VACUUM prevents bloat
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);  -- More frequent vacuum for large tables

-- PostgreSQL: check table size
SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

To find what to optimize first, both engines keep per-statement statistics:

-- PostgreSQL (needs the pg_stat_statements extension)
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- MySQL: Performance Schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

pg_stat_statements is an extension, not a default view: it must be listed in shared_preload_libraries (which requires a restart) and then created with CREATE EXTENSION pg_stat_statements; — otherwise the query fails with relation "pg_stat_statements" does not exist. Most managed services preload it already. Sorting by total_exec_time rather than mean_exec_time is deliberate: a 2 ms query executed a million times an hour costs more than a 5-second report run once a day. EXPLAIN ANALYZE in both databases actually executes the query, so wrap it in BEGIN; ... ROLLBACK; when analyzing an UPDATE or DELETE. In MySQL’s plan output, type: ALL means a full table scan and Using filesort or Using temporary in Extra are the usual suspects for slow sorts and groupings. MySQL SET GLOBAL changes (such as enabling the slow query log) are lost on restart unless you use SET PERSIST (8.0+) or the config file.

Extensions (PostgreSQL Only)

PostgreSQL’s extension system is a significant differentiator.

-- List available extensions
SELECT name, default_version, comment FROM pg_available_extensions ORDER BY name;

-- PostGIS — geospatial data
CREATE EXTENSION postgis;
SELECT ST_Distance(
  ST_GeographyFromText('POINT(-122.4194 37.7749)'),  -- San Francisco
  ST_GeographyFromText('POINT(-118.2437 34.0522)')   -- Los Angeles
) / 1000 AS distance_km;

-- pg_trgm — trigram similarity (fuzzy search)
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);
SELECT name FROM users WHERE name % 'Alce';  -- typo-tolerant search
SELECT name, similarity(name, 'Alice') AS sim FROM users ORDER BY sim DESC LIMIT 10;

-- uuid-ossp — UUID generation
CREATE EXTENSION "uuid-ossp";
INSERT INTO items (id, name) VALUES (uuid_generate_v4(), 'Widget');

-- pg_stat_statements — query performance tracking
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- timescaledb — time-series data
CREATE EXTENSION timescaledb;
SELECT create_hypertable('metrics', 'time');

Node.js Integration

// PostgreSQL with node-postgres (pg)
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
});

const result = await pool.query(
  'SELECT * FROM users WHERE email = $1',  // Parameterized query (prevents SQL injection)
  ['[email protected]']
);

// MySQL with mysql2
import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  uri: process.env.DATABASE_URL,
  waitForConnections: true,
  connectionLimit: 20,
});

const [rows] = await pool.execute(
  'SELECT * FROM users WHERE email = ?',   // ? for parameters
  ['[email protected]']
);

The pool size is the setting that most often goes wrong. PostgreSQL’s default max_connections is 100, and because it runs one OS process per connection, raising it far beyond a few hundred costs memory and throughput. If you run ten instances of a Node service each with max: 20, you need 200 connections before counting migrations, cron jobs, and admin sessions — and the first sign is FATAL: sorry, too many clients already during a deploy, when old and new instances briefly overlap. Size pools from the database’s limit downward (instances × pool size < max_connections, with headroom), or put PgBouncer in transaction pooling mode between the app and the database. MySQL uses a thread per connection and tolerates more idle connections, but its equivalent error is Too many connections. Also note the placeholders differ ($1 vs ?), and mysql2’s execute uses server-side prepared statements while query interpolates client-side, which matters for both performance and some type conversions.

// Both work with ORMs — Prisma supports both
// prisma/schema.prisma
datasource db {
  provider = "postgresql"  // or "mysql"
  url      = env("DATABASE_URL")
}

On-page FAQ (quick links to details above)

When should I apply this in production?

A. When you are choosing an engine, designing replication, or planning JSON and search. Use the decision matrix and use-case table to align the database with your access patterns, not a generic blog ranking.

Where can I go deeper on internals?

A. The official PostgreSQL documentation and MySQL Reference Manual are authoritative. For performance, prioritize running EXPLAIN (ANALYZE) and measuring p95/p99 on your own data.