Backend

PostgreSQL Is Eating the Hard 20% That MySQL Can't Chew

By 苏三说技术 · Jul 13, 2026

Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Teams that treat MySQL as the only relational database end up writing application-layer workarounds for window functions, JSON querying, and full-text search — code that PostgreSQL executes in a single SQL statement. The migration cost is real, but so is the ongoing cost of fighting a database that lacks the features your queries need.

Summary

PostgreSQL's core concurrency model guarantees reads never block writes and writes never block reads — a property MySQL's undo-log-based MVCC cannot match under high write loads. That architectural difference, combined with over 90% SQL:2023 compliance, makes it the default choice for financial systems, complex analytics, and any application where data integrity is non-negotiable.

The index arsenal goes far beyond B-trees: GIN indexes accelerate JSONB containment queries by 300x, partial indexes shrink storage costs by indexing only active rows, and expression indexes prevent function calls from killing index usage. Built-in full-text search with relevance ranking removes the need for an external search engine in many applications.

For Java shops, Spring Boot and MyBatis Plus integrate cleanly with PostgreSQL's native array and JSONB types, letting a single database handle structured, semi-structured, and full-text workloads that would otherwise require multiple systems stitched together in application code.

Takeaways

— PostgreSQL's MVCC uses snapshot isolation so reads and writes never block each other; MySQL's undo-log approach still creates lock contention under high write concurrency.

— A JSONB column with a GIN index runs containment queries up to 300 times faster than the same query without the index.

— PostgreSQL ships six index types — B-tree, Hash, GIN, GiST, BRIN, and Bloom — each targeting a different access pattern, while MySQL relies almost entirely on B-trees.

— Expression indexes let queries on `lower(email)` hit an index directly, and partial indexes index only rows matching a `WHERE` clause, cutting storage and write overhead.

— Built-in full-text search with `tsvector` and `tsquery` plus relevance ranking via `ts_rank` can replace Elasticsearch for many use cases.

— PostgreSQL's SERIALIZABLE isolation level uses Serializable Snapshot Isolation (SSI) and actually performs well; MySQL's equivalent essentially locks entire tables.

— Java integration through Spring Boot and MyBatis Plus maps PostgreSQL arrays to `List<String>` and JSONB columns to native SQL `@>` containment operators without extra tooling.

Conclusions

PostgreSQL's real advantage is not feature count but feature depth: its JSONB, full-text search, and window functions are native and indexable, not bolted-on afterthoughts that degrade under load.

The 300x JSONB query speedup from a GIN index is not a microbenchmark trick — it reflects a storage engine decision to parse JSON into a binary, indexable structure at write time rather than at query time.

MySQL's dominance in simple OLTP creates a skills trap: developers who never leave its ecosystem don't realize how much application code they write to compensate for missing database features.

PostgreSQL's VACUUM maintenance cost is the tradeoff for its MVCC design, and teams migrating from MySQL often underestimate the operational burden of managing dead tuples.

Concepts & terms

MVCC (Multi-Version Concurrency Control)

A concurrency model where each transaction sees a snapshot of the database at a point in time. PostgreSQL implements it via transaction IDs (XID) and hidden XMIN/XMAX columns on each row, so readers never wait for writers and vice versa.

JSONB

PostgreSQL's binary JSON storage format. Unlike the plain JSON type, JSONB is parsed on write into a structure that supports GIN indexing, making containment queries (`@>`) fast enough for production workloads.

GIN Index (Generalized Inverted Index)

An index type built for composite values — arrays, JSONB documents, and full-text search vectors. It stores each distinct key with a list of matching row pointers, enabling fast lookups when querying for rows that contain a specific element.

Serializable Snapshot Isolation (SSI)

PostgreSQL's implementation of the highest SQL isolation level. Unlike older approaches that lock entire tables, SSI tracks dependencies between transactions and aborts only those that would produce serialization anomalies, maintaining both correctness and throughput.

tsvector / tsquery

PostgreSQL's full-text search types. `tsvector` stores a parsed, normalized list of lexemes from a document; `tsquery` represents a search expression. The `@@` match operator checks whether a tsvector satisfies a tsquery, and GIN indexes make this fast.

Source: juejin.cn ↗ Google Translate ↗ Backup ↗