PostgreSQL vs MySQL

The type system is where the difference is most immediate and least discussed. Postgres gives you arrays, ranges, enums, UUIDs, network address types, composite types and domains, which are named types carrying their own constraints, plus the ability to define your own. That sounds like a checklist until you meet the modelling problems it solves. A booking system with overlapping intervals is a range type with an exclusion constraint that makes double-booking impossible at the database level, rather than three columns and a query that hopes. A set of tags is an array with a GIN index rather than a join table you did not want. An email address that must always be valid is a domain, defined once, rather than a check constraint copied into every table that stores one.

MySQL gives you the standard scalar types plus ENUM, SET and JSON. This is enough for the very large majority of schemas, and there is a real argument that a smaller type system produces more portable, more legible tables that any developer can read without learning your database’s vocabulary. What you give up is the ability to express certain shapes directly, so multi-valued and interval data gets modelled around the gap: extra tables, encoded columns, or invariants enforced in application code where a second service, a script or a manual fix can bypass them.

JSON is the sharpest version of this. Postgres has two types and the distinction matters: json stores the text and reparses it, jsonb decomposes it into a binary form that is faster to query, supports containment and path operators, and can be indexed with GIN so you can look inside documents at speed. In practice this means you can keep the structured core of your domain in properly typed relational columns and put the genuinely variable parts, per-tenant custom fields, integration payloads, event bodies, in JSONB beside them, under one transaction, in one database, queryable together. That single capability removes the most common reason teams end up running a document store alongside their relational one and then reconciling two sources of truth forever.

MySQL’s JSON type is real and useful, with a good function library and the ability to index extracted paths via generated columns or functional key parts. For a handful of semi-structured fields it is entirely adequate. It is thinner once documents become a load-bearing part of the model: the operator vocabulary is narrower, indexing inside a document takes more setup, and the ergonomics push you towards extracting values into columns, which is often the right instinct anyway. If your data is genuinely document-shaped in places, that difference will be felt weekly rather than theoretically, and it is one of the clearer reasons to prefer Postgres for a new build.

Extensions are the biggest structural difference between these two databases and the one most likely to change what your whole system looks like. Postgres has a first-class mechanism for loading new types, functions, index methods and background workers into the server, and the ecosystem built on it is serious infrastructure rather than a collection of conveniences. The consequence is architectural: capabilities that would otherwise be separate systems, each with its own deployment, backup, security posture, monitoring and consistency problem, can live inside the database you already run.

PostGIS is the strongest example. It brings geometry and geography types, spatial indexing and a very large library of functions for distance, containment, intersection and transformation, and it turns Postgres into a genuine geospatial database rather than one that can store coordinates. Nearest-branch queries, delivery and catchment zones, geofencing and map layers run against your live operational data, in the same transaction as the rows they relate to. MySQL does have spatial types and spatial indexes, and for simple proximity work they are fine. For anything that is really a GIS problem, the gap is wide and worth knowing about before you commit.

The pattern repeats. pgvector adds vector columns and similarity search with approximate index types, so embeddings sit next to the rows they describe and a single query can filter with ordinary predicates and rank by semantic distance. TimescaleDB makes time-series storage and roll-ups efficient. pg_trgm gives you trigram similarity for fuzzy matching and accelerates pattern searches that would otherwise scan. pg_stat_statements aggregates query statistics so you can find the queries that actually dominate your load instead of the ones you assume do. Built-in full-text search, with tsvector, tsquery, dictionaries, stemming and ranking, covers a large share of what teams reach for a dedicated search engine to do.

The honest counterpoint is that extensions have costs. They must be available on your platform, which constrains managed-hosting choices and occasionally pins you to a provider that supports the one you need. They must be version-compatible when you upgrade the server, and an extension that has not kept up can block a major-version upgrade entirely. And they tempt teams into using the database for jobs it should not have, which is its own failure mode. The discipline is to reach for an extension to avoid adding a system, not to avoid thinking. But used deliberately, this is how a proposed architecture with four data stores becomes one well-configured Postgres, and that is a simplification worth a great deal at three in the morning.

Both databases use multi-version concurrency control, so readers do not block writers and writers do not block readers, and both are pleasant to work with as a result. They implement it differently, and the difference is the single most important operational fact in this comparison.

When Postgres updates a row it writes a new version and leaves the old one in the table, visible to transactions that are still entitled to see it. Those dead versions accumulate and must be reclaimed, which is what vacuum does. Autovacuum runs in the background and handles this automatically, and on a modest workload you will never think about it. On a write-heavy or update-heavy table with default settings you may well think about it a great deal. Dead tuples that are not reclaimed fast enough become bloat: the table and its indexes occupy more space than the live data justifies, scans read more pages, the cache holds less useful data, and queries slow down in a way that looks mysterious if you do not know to look. Vacuum also maintains the visibility map that index-only scans depend on, and it freezes old row versions to prevent transaction-ID wraparound, which is the rare and serious failure mode at the end of this road. Long-running transactions make everything worse, because vacuum cannot clean up anything that an open transaction might still need to see.

InnoDB keeps old row versions in the undo log rather than in the table, and a purge thread removes them once no transaction needs them. The practical effect is that the equivalent housekeeping demands less of your attention, and the characteristic failure is quieter: a long-running transaction causes the undo history to grow, which costs space and slows reads that have to walk it, but you are less likely to discover it as a sudden performance cliff. MySQL asks for less operational vigilance here, and for a team without dedicated database expertise that is a real and underrated advantage.

The other half of Postgres’s bill is connections. Each one is a full operating system process, with its own memory, which means an application server pool that opens several hundred direct connections will exhaust the database long before it exhausts the machine. The answer is well known and not optional: put PgBouncer or an equivalent pooler in front, and size the actual backend count against your cores rather than your application’s optimism. MySQL uses a thread per connection, which is lighter, so it tolerates high connection counts better out of the box. A proxy such as ProxySQL is still the right answer at scale, but you are less likely to be forced into it in month one.

None of this is difficult, and none of it is a reason to avoid Postgres. It is a reason to be honest that Postgres expects a specific set of operational habits: tuned autovacuum on the tables that need it, a connection pooler, monitoring for bloat and for long-running transactions, and someone who knows what those numbers mean. Teams who build those habits find Postgres extremely reliable. Teams who treat it as fire-and-forget get bitten in the same predictable ways, and then tell people the database was the problem.

Most database performance problems in either system are the same three things: a missing or wrong index, a query written as an application loop instead of set-based SQL, and a configuration left at packaged defaults that were chosen to start anywhere rather than to run your workload. Fix those and the choice of database rarely determines whether your application is fast. Where the two differ is what you can do when the ordinary answers have been exhausted.

Postgres gives you several genuinely different index methods. B-tree for equality and ranges, GIN for JSONB documents, arrays and full-text vectors, GiST and SP-GiST for geometric, range and nearest-neighbour work, and BRIN for very large tables with natural physical ordering, where a tiny index summarising block ranges replaces one that would otherwise be enormous. On top of that sit partial indexes, which cover only the rows matching a predicate and are wonderful for the common case of a large table where you only ever query a small active subset, and expression indexes over a computed value. Covering indexes can carry extra columns so a query is answered from the index alone. The point is not that you will use all of these. It is that when a query pattern is awkward, there is usually a purpose-built answer rather than a workaround.

MySQL is B-tree for almost everything, with full-text and spatial index types alongside, and functional key parts for indexing an expression. Fewer choices means fewer ways to be clever and fewer ways to be wrong, which is a legitimate virtue. The structural thing to understand is clustering: InnoDB stores the table itself in primary key order, so primary key lookups are very fast and range scans on the primary key are efficient, while secondary indexes store the primary key rather than a row pointer and therefore take a second step to reach the row. This makes primary key choice a genuine design decision. A wide or randomly ordered primary key inflates every secondary index and scatters inserts, which is why the conventional advice is a compact, monotonically increasing key. Postgres stores tables as unordered heaps with all indexes pointing at row locations, so no index is privileged, at the cost that index-only scans depend on the visibility map being current, which brings you back to vacuum.

On query planning, both have capable cost-based planners and both will occasionally choose something you disagree with. Postgres exposes more information about why, particularly through EXPLAIN with buffers and timing, and pg_stat_statements is one of the best tools in either ecosystem for finding the queries that genuinely dominate load rather than the ones you suspect. MySQL has EXPLAIN, EXPLAIN ANALYZE and the slow query log, which together answer most questions in practice. Postgres has more sophisticated join and aggregation strategies available and it will use them on the analytical queries that appear inside most applications eventually, which is a reasonable part of why it suits reporting-adjacent work better. Neither planner rescues a query that asks for the wrong thing.

Both databases replicate well, and this is one of the few areas where MySQL has a clear structural advantage rather than merely a familiar one. Its binary log replication is among the most thoroughly exercised mechanisms in the industry: GTIDs make failover and topology changes tractable, row-based and mixed formats cover the correctness pitfalls of statement replication, semi-synchronous options let you trade a little latency for a stronger durability guarantee, and multi-source replication is built in. More importantly, MySQL offers an integrated route to automatic failover through Group Replication and InnoDB Cluster, with mature orchestration and proxy tooling around it. If you want a self-healing cluster that a vendor supports as a product, MySQL gets you there with less assembly.

Postgres replicates through the write-ahead log. Physical streaming replication gives you read replicas and hot standbys that are byte-identical to the primary, and it is simple, fast and dependable. Logical replication publishes row-level changes by table, which is the mechanism behind selective replication, replication between different major versions, near-zero-downtime upgrades, blue-green cutovers, and feeding a warehouse or search index without dual writes from the application. That last set of capabilities is genuinely valuable and worth knowing about before you plan an upgrade you assumed needed a long maintenance window.

What Postgres does not ship is automatic failover. Replication is built in, promotion is a decision somebody or something has to make, and building a cluster that fails over safely means adopting Patroni, repmgr, pg_auto_failover or a managed service that has done this work for you. That is real engineering with real ways to get it wrong, and split-brain is not a hypothetical if you improvise it. For many teams the honest answer is to use a managed Postgres and let the provider own that problem, which is a perfectly respectable engineering decision rather than an admission of anything.

Which brings us to the operational skills each database asks for, because that is what this section is really about. Postgres asks you to understand vacuum and bloat, to run a connection pooler and size the backend count deliberately, to plan major-version upgrades rather than assume them, and to choose and operate a failover mechanism. MySQL asks you to understand replication lag and what your application does when a replica is behind, to size the InnoDB buffer pool so the working set stays in memory, to be deliberate about primary key design because of clustering, and to keep strict mode and real constraints in place rather than inheriting a permissively configured server from an older era. Both lists are learnable. Neither is optional. And the list your team has already learned is a legitimate input into the decision, arguably a bigger one than any feature in the table above.

MySQL’s historical reputation for laxness was earned. Older versions would silently truncate values that did not fit, coerce nonsense into columns, accept invalid dates and shrug at data that should have been rejected outright. That is genuinely fixed: strict SQL mode is the modern default, check constraints are enforced, and a properly configured modern MySQL rejects bad data at the door. The residual risk is not the software, it is inheritance. Long-lived MySQL estates carry configuration, schemas and habits from the permissive era, and an application built against a server where strict mode was off has usually accumulated data that will not survive turning it on. That is a migration project rather than a setting.

Postgres has always been strict and has no permissive history to configure away, which is a smaller advantage than it once was but still means the default posture is correct rather than something you have to verify. Where it remains genuinely ahead is in the constraint vocabulary. Exclusion constraints can enforce that no two rows overlap on a range, which is how you make double-booking structurally impossible. Deferrable constraints let a transaction pass through a temporarily inconsistent intermediate state and be checked at commit, which is how you move rows between parents without disabling foreign keys. Partial unique indexes let a uniqueness rule apply only to the rows it should, such as one active record per customer while allowing many archived ones. Domains attach constraints to a named type so a rule is defined once rather than copied. Row-level security enforces tenant isolation inside the database, where an application bug cannot bypass it.

On isolation, the defaults differ in a way that surprises people moving between them. Postgres defaults to read committed, where each statement sees a fresh snapshot. MySQL’s InnoDB defaults to repeatable read, where the whole transaction reads from a consistent snapshot taken at first read, and where locking reads behave differently from plain ones in a way that is worth understanding before you rely on it. Neither default is wrong, but application code that assumes one and runs on the other can behave subtly differently under concurrency, which is the sort of bug that appears at load and never in testing. Postgres also offers a genuinely serialisable isolation level implemented through serialisable snapshot isolation, which detects conflicting transactions and aborts one rather than holding locks, so you get true serialisability with retry logic instead of a lock-heavy execution. Where correctness under concurrency is critical and the alternative is hand-rolled application locking, that is a strong tool.

The most useful thing to take from this section is not a scoreboard. It is that if your invariants matter, put them in the database. Both of these systems will enforce what you declare and neither will invent rules you did not write down. Postgres gives you a richer vocabulary for declaring them, and a schema that expresses its own rules is the single most durable thing you can build, because it keeps holding while services, frameworks and languages come and go around it.

Moving a database between these two is a well-trodden path and considerably more work than the marketing around migration tools suggests. The table structures translate almost mechanically and the tooling handles that part fine. The work is in everything around the tables, and it lands in four places: types and defaults, SQL dialect, application behaviour, and operations.

Going from MySQL to PostgreSQL, the recurring items are consistent. Auto-increment columns become identity columns or sequences, and the sequence values must be reset after the data load or your first insert collides with existing rows. Unsigned integers do not exist in Postgres and need a wider type plus a check constraint. Zero dates and other permissive artefacts have to be cleaned before they will load at all, and that cleanup often surfaces data quality problems the old configuration was hiding. ENUM columns become either a Postgres enum type or a lookup table with a foreign key, and the second is usually the better long-term answer. Case sensitivity changes: MySQL collations are commonly case-insensitive for comparison, Postgres is case-sensitive by default, and queries that relied on the old behaviour will quietly return different results unless you use citext or normalise explicitly. Backtick quoting, LIMIT with two arguments, and MySQL-specific functions all have to be rewritten. Then the application layer: ORMs mostly absorb this, raw SQL does not, and neither does whatever reporting tool, script or spreadsheet connector somebody in finance built four years ago and nobody has inventoried.

Going from PostgreSQL to MySQL is less common and, where the Postgres schema used the features that make Postgres worth choosing, considerably harder. Arrays, ranges, composite types, domains and exclusion constraints have no direct equivalent and must be remodelled, usually into extra tables plus application-enforced rules. JSONB usage translates only partly, and anything relying on GIN containment queries will need rethinking. Extensions do not come with you: if you use PostGIS, pgvector or TimescaleDB, you are not migrating a database, you are replacing a capability. And transactional DDL is gone, which means your migration tooling and its rollback assumptions need revisiting.

Whichever direction you are going, the mechanics should be boring and the plan should be dull. Load a full copy into the target and run your application against it in a non-production environment for long enough to find the dialect issues and the behavioural differences. Then use logical replication or a change data capture tool to keep the target current from the live source, so the eventual cutover is a short pause rather than a long outage while a dump restores. Verify with row counts and, more usefully, with checksums or aggregate comparisons on the tables that matter, because a migration that silently drops rows is worse than one that fails loudly. Keep the old system running and readable for a defined period afterwards, and know in advance what your rollback actually is.

The last point is the one worth saying loudest. Do not migrate a working database because a comparison page told you the other one is better. The risk is real, the effort lands on your best engineers for months, and the benefit is usually smaller than it looked when somebody read a feature list. Good reasons to move exist: you need a capability that only exists on the other side, such as PostGIS or pgvector, your data model has outgrown what your current database can express and the application is carrying invariants it should not be, or your organisation is consolidating on one database and the cost of two is genuinely larger than the cost of one migration. Those are worth doing properly. General dissatisfaction is not a reason, and is usually better answered by fixing the indexes, the queries and the configuration you already have.