PostgreSQL vs MySQL: correctness or simplicity?

PostgreSQL is the feature-rich, standards-first database: rich types (JSONB, arrays, ranges, geospatial), advanced indexing, powerful concurrency via MVCC, extensions, and strict correctness by default. MySQL is the simpler, extremely popular workhorse tuned historically for fast reads and straightforward web workloads, with a gentler learning curve and huge hosting ubiquity. Neither is "better" — they’re shaped differently.

Note: MySQL and its fork MariaDB have closed much of the historical feature gap, and Postgres has always been fast — treat the old "MySQL is faster, Postgres has more features" shorthand as a rough tendency, not a law.

Both databases use MVCC (multi-version concurrency control) so readers never block writers and vice versa, but they implement it differently, and the difference shows up under real production load. Postgres stores old row versions directly in the same table (a new row version is appended, the old one marked dead) and relies on a background process called VACUUM to reclaim that dead space later. Under heavy write/update churn without tuned autovacuum settings, table bloat is a real, well-known operational concern — dead row versions accumulate faster than vacuum reclaims them, tables grow larger than their live data would suggest, and query performance degrades until you tune or manually intervene.

MySQL's InnoDB engine takes a different approach: it keeps the current row version in the main table and pushes old versions into a separate structure called the undo log, which is purged once no transaction needs those old versions anymore. This avoids Postgres-style table bloat from old row versions living in the main table, but comes with a different sharp edge: a single very-long-running transaction can prevent the undo log from being purged, causing it to grow unboundedly and — in extreme cases — fill available storage.

Say a product team wants per-user feature flags stored as flexible JSON — each user can have an arbitrary set of enabled experiments, and the query pattern is "find all users with experiment X enabled" plus "get user Y's full flag set." This is a genuinely useful test of the two databases' different strengths.

In Postgres, this is a JSONB column with a GIN index on it: CREATE INDEX ON users USING GIN (flags), then WHERE flags @> '{"experiment_x": true}' uses the index directly, because JSONB is a genuinely indexed, queryable binary format — not just a text blob you happen to store JSON in. You get relational guarantees (foreign keys to the user, transactions across flag updates and other user data) plus document flexibility in the same row, in the same query.

In MySQL, JSON support has matured significantly (generated columns can extract and index specific JSON paths), but it's a later addition to the engine rather than a first-class citizen designed in from the start — querying arbitrary nested JSON structure efficiently often means either extracting the specific paths you'll query into indexed generated columns ahead of time (workable, but you have to anticipate the query pattern) or accepting a slower path. For a schema that's genuinely mixed relational-plus-flexible-document, Postgres's JSONB tends to need less workaround engineering.