MySQL vs PostgreSQL: an honest comparison for app developers

For most web applications, either database will do the job, and the choice matters less than the schema, the indexes and the queries you write. That said, they are not interchangeable. PostgreSQL has the richer SQL: transactional DDL, RETURNING, partial indexes, real BOOLEAN and UUID types, arrays, range types, a deeper JSON story with GIN indexes, and an extension system that adds things like PostGIS. MySQL is simpler to run, cheaper per connection, and is what a large part of the PHP world expects - WordPress requires it. If your framework supports both and nothing pulls you either way, PostgreSQL is the stronger default for new projects. If you are running WordPress or another MySQL-first application, or you want the database to be the least interesting part of your stack, MySQL is a perfectly good choice and nobody should talk you out of it.

The rest of this post goes through the differences that actually show up while building and running an application, with MySQL 8.4 LTS and PostgreSQL 17 as the reference points.

The short version#

Neither column is a list of wins. Several MySQL entries - cheap connections, no vacuum to tune - are operational advantages that matter more on a small server than the SQL features do.

SQL features you will notice#

The features that change how you write application code:

`RETURNING`. PostgreSQL returns the inserted or updated rows from the statement itself: INSERT ... RETURNING id, created_at. MySQL gives you LAST_INSERT_ID() for an auto-increment key and nothing for the rest, so fetching defaults or trigger-set values takes a second query. ORMs hide this, at the cost of the extra round trip.

Transactional DDL. In PostgreSQL, CREATE TABLE, ALTER TABLE and CREATE INDEX can run inside a transaction and roll back with it, so a migration that fails halfway leaves nothing behind. In MySQL, every DDL statement commits implicitly. Since 8.0 each individual DDL statement is atomic - it completes or does not - but a migration of five statements that fails on the fourth leaves three applied. Migration tools on MySQL compensate by recording progress per statement; you still clean up by hand when something fails.

Partial indexes. PostgreSQL can index a subset of rows: CREATE INDEX ON orders (customer_id) WHERE status = 'open'. It is small and fast when most rows are closed. MySQL has no equivalent; you index the whole column or use a generated column trick.

Upserts. Both have them, with different semantics. MySQL's INSERT ... ON DUPLICATE KEY UPDATE fires on any unique key conflict, which is surprising on tables with several unique keys. PostgreSQL's ON CONFLICT (column) DO UPDATE names the constraint it is about, and MERGE is available since version 15.

Joins and set operations. PostgreSQL has FULL OUTER JOIN; MySQL does not and needs a UNION of a left and right join. Both have window functions and common table expressions, including recursive ones, since MySQL 8.0. MySQL added INTERSECT and EXCEPT in 8.0.31.

Constraints. MySQL enforces CHECK constraints since 8.0.16 - before that it parsed and ignored them, which is the origin of a lot of distrust. PostgreSQL also has exclusion constraints (no two bookings for the same room may overlap) and deferrable constraints, which have no MySQL equivalent.

Materialised views, sequences, `LISTEN`/`NOTIFY`, row-level security. PostgreSQL has all four. MySQL has none of them natively. If you need one, it is a strong reason on its own.

Types and strictness#

PostgreSQL's type system is larger: boolean, uuid, inet and cidr, arrays of any type, range types (tstzrange for a booking period), interval, enums that are real types, and user-defined composite types. MySQL covers the essentials and maps a few onto others: BOOLEAN is TINYINT(1), UUIDs are BINARY(16) with UUID_TO_BIN() or a CHAR(36), and arrays are a JSON column.

Time is the type with a trap in it. MySQL's TIMESTAMP stores UTC, converts to the session time zone, and ends at 2038-01-19 03:14:07 UTC - a date already inside the lifetime of mortgages, warranties and long-running subscriptions. Use DATETIME for anything that may go beyond it, and store UTC there yourself. PostgreSQL's timestamptz runs to the year 294276.

MySQL's reputation for silently accepting bad data comes from older versions and lax SQL modes. MySQL 8's default sql_mode includes STRICT_TRANS_TABLES, ONLY_FULL_GROUP_BY, NO_ZERO_IN_DATE, NO_ZERO_DATE and ERROR_FOR_DIVISION_BY_ZERO: inserting a string that is too long, or a date of 0000-00-00, is an error, as it should be. Some old applications set a looser mode on connect to keep working; check yours with SELECT @@SESSION.sql_mode.

Text comparison differs by default. MySQL's default collation, utf8mb4_0900_ai_ci, compares case- and accent-insensitively, so WHERE email = 'Ana@Example.com' finds ana@example.com. PostgreSQL compares exactly, and you use lower(), ILIKE or the citext extension for insensitive matching. Neither is wrong, but porting an application between them changes the results of queries that never mention case. utf8mb4 and collations explains MySQL's side.

JSON#

Both store and query JSON, and both are good enough to keep semi-structured fields beside relational data. The difference is indexing.

PostgreSQL's jsonb can take a GIN index over the whole document, which then serves containment queries (WHERE attrs @> '{"colour": "red"}') on any key. MySQL's JSON type is indexed by extracting the values you care about into generated columns, or with multi-valued indexes for arrays (8.0.17+):

-- MySQL: index one path through a generated columnALTER TABLE products  ADD COLUMN colour VARCHAR(20) AS (attrs->>'$.colour') STORED,  ADD INDEX idx_colour (colour);-- PostgreSQL: one index for any keyCREATE INDEX idx_attrs ON products USING gin (attrs jsonb_path_ops);

The MySQL approach is explicit and fast for the paths you planned for; the PostgreSQL approach is flexible for queries you did not plan. If the application is mostly ad-hoc filtering over JSON documents, that tips toward PostgreSQL. MySQL JSON and generated columns covers the MySQL side in depth.

Concurrency, locking and housekeeping#

Both use multi-version concurrency control: readers do not block writers and writers do not block readers. They implement it differently, and the difference shows up in operations.

MySQL's InnoDB updates rows in place and keeps old versions in the undo log for transactions that still need them; a background purge thread removes them once nobody does. There is little to tune. PostgreSQL writes a new row version on every update and leaves the old one in the table until VACUUM reclaims it; autovacuum does this automatically, but on write-heavy tables it needs tuning, and a forgotten long-running transaction stops it entirely, so tables bloat. Postgres vacuum and bloat is the full story. This is a genuine operational cost of PostgreSQL that its feature list does not mention.

Default isolation differs. MySQL defaults to REPEATABLE READ, using gap and next-key locks on indexed ranges to prevent phantoms - which also means more lock waits and occasional deadlocks on concurrent inserts into the same range. PostgreSQL defaults to READ COMMITTED, where each statement sees the latest committed data. Application code that reads, computes and writes back behaves differently under the two; transactions, locking and deadlocks goes through MySQL's behaviour.

InnoDB's clustered primary key stores rows in key order, so range scans on the primary key are very fast and random primary keys (UUIDv4) are expensive. PostgreSQL stores rows in a heap in no particular order and every index, including the primary key, points into it.

Connections and memory#

MySQL serves each connection with a thread. An idle connection costs a few hundred kilobytes, and a server can hold hundreds of them comfortably. PostgreSQL forks a process per connection, each with its own memory; a few hundred idle connections are a real cost, and the usual answer is a pooler like PgBouncer in front. On a small server this is one of the more practical differences: an application with several workers, each with its own pool, reaches PostgreSQL's comfortable limit much sooner. Both still punish unbounded pools, and the advice in connection pools and limits applies to each.

For tuning, both come down to a cache size and keeping per-query memory under control. MySQL's main knob is innodb_buffer_pool_size, usually half or more of memory; PostgreSQL's is shared_buffers at about a quarter, relying on the operating system cache for the rest. Compare InnoDB tuning for small servers with PostgreSQL tuning for small servers to see how differently the same memory is spent.

Ecosystem and portability#

Some software decides for you. WordPress requires MySQL (or a compatible server). A long tail of PHP applications, plugins and themes writes MySQL-specific SQL and is only tested against it. Minecraft plugins and FiveM resources that store data in a SQL server usually target MySQL. In the other direction, PostGIS makes PostgreSQL the standard for geographic data, and tools like Supabase, Hasura and PostgREST are built around it.

Frameworks are mostly neutral: Django, Rails, Laravel, Prisma, SQLAlchemy, Entity Framework Core and Hibernate support both. The non-neutral bits are features: Django's ArrayField and its django.contrib.postgres search tools are PostgreSQL-only, and any raw SQL in a project ties it to the engine it was written for.

Moving between them later is possible but not free. Data moves with tools like pgloader (MySQL to PostgreSQL); queries need review for case sensitivity, LIMIT syntax inside updates, date functions, identifier quoting (backticks against double quotes) and upserts. An ORM-only application ports in days; one with hand-written SQL, triggers and procedures takes much longer.

On licensing: MySQL Community Server is GPLv2 and owned by Oracle, which also sells an Enterprise edition with extra components. PostgreSQL is under a permissive licence and developed by a community with no single owner. For an application connecting over the network, the server's licence places no obligations on your code either way.

How to choose#

Pick MySQL when:

• The software you are deploying requires or prefers it: WordPress, most PHP CMS and forum software, game server plugins.
• You want the database to need as little attention as possible, and your SQL is standard CRUD through an ORM.
• You expect many connections from many small processes and do not want to run a pooler.

Pick PostgreSQL when:

• You want the richer SQL - RETURNING, transactional migrations, partial indexes, arrays, ranges, exclusion constraints.
• Your data includes geography (PostGIS) or heavy ad-hoc JSON querying.
• You are starting fresh with a framework that supports both, and you have no reason to prefer MySQL.

Pick whichever your team already knows when the project is ordinary and the deadline is real. Familiarity with one engine's failure modes is worth more than the feature gap for most applications. Comparisons with document and key-value stores are in which database should I use and Postgres or MongoDB.

RE:NODE sells both as separate database lines. MySQL plans run MySQL 8.4 LTS, from 1 GB of memory and 10 GB of NVMe at $4 a month, with a root password, an application database and an application user generated. PostgreSQL plans start at 1 GB and 20 GB from $6, with credentials generated per server. Both are reached on the plan's host and port and come with backup slots.

FAQ#

Is PostgreSQL better than MySQL?

It has more SQL features and a larger type system, which makes it the better default for new applications that will use them. MySQL is simpler to operate and is required by a lot of existing software. "Better" depends on which of those matters to your project.

Is MySQL faster than PostgreSQL?

Not in general. Both are fast at the simple indexed reads and writes that make up most web traffic. MySQL handles large numbers of connections more cheaply; PostgreSQL's planner often does better on complex queries. Measure your own workload before deciding on speed.

Can WordPress run on PostgreSQL?

Not in any supported way. WordPress core and most plugins are written for MySQL. Experimental compatibility layers exist, but plugins that write their own SQL break on them. Run WordPress on MySQL.

How hard is it to switch from MySQL to PostgreSQL later?

The data is the easy part. The work is in queries that rely on MySQL behaviour: case-insensitive comparisons, ON DUPLICATE KEY UPDATE, backtick-quoted identifiers, MySQL date functions and loose GROUP BY in old code. An application that uses only an ORM ports quickly; one with raw SQL needs a careful review and a test suite.

Do I need a connection pooler with MySQL?

Usually not a separate one. Application-side pools are enough for most deployments, because MySQL connections are threads and idle ones are cheap. You still need sensible pool sizes; hundreds of busy connections on two CPU cores are slow on any database.