PostgreSQL vs MySQL

PostgreSQL and MySQL are the two most widely used open-source relational databases. Both store data in tables, speak SQL, support transactions and run on any server. For most apps, either one works.

The differences show up in the details: data types, index types, transaction behavior and the syntax of a few common commands. This guide compares what changes in practice, with code, so you can choose with clear criteria.

What is each one?#

PostgreSQL is an object-relational database that prioritizes SQL standard compliance and extensibility. Each major version gets 5 years of support. See what is PostgreSQL for the basics.

MySQL is a relational database maintained by Oracle, known for simplicity and for being everywhere in shared hosting and CMSs like WordPress. Its default engine is InnoDB, which provides ACID transactions, row-level locking, foreign keys and MVCC. MySQL ships on two tracks: LTS, which only receives fixes, and Innovation, which may change behavior. For stable production, start on a supported LTS series. See what is MySQL.

How do they differ at a glance?#

How does upsert differ?#

Insert-or-update is a common task, and the syntax differs. In PostgreSQL:

INSERT INTO users (id, name)
VALUES (1, 'Ana')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
RETURNING id, name;

In current MySQL versions, use a row alias (the VALUES() function is deprecated):

INSERT INTO users (id, name)
VALUES (1, 'Ana') AS new_row
ON DUPLICATE KEY UPDATE name = new_row.name;

PostgreSQL's RETURNING hands back the written row in the same statement. In MySQL you run a SELECT afterwards.

How do they handle transactions and concurrency?#

Both follow the ACID model and use MVCC. In PostgreSQL, the documentation states that reading never blocks writing and writing never blocks reading for ordinary queries. Explicit locks such as SELECT ... FOR UPDATE still wait.

The difference that surprises people most is in migrations. In PostgreSQL you can run ALTER TABLE inside a transaction and ROLLBACK if something fails. In MySQL, an ALTER TABLE ends the active transaction with an implicit commit: if the migration fails halfway, the earlier steps are already committed.

What about JSON and advanced indexes?#

Both store JSON. In PostgreSQL, a jsonb column can be indexed with GIN to search keys and values inside the document. In MySQL, a JSON column can't be indexed directly: you index an extracted value, through a generated column or a functional key part.

The partial index is another PostgreSQL advantage. It covers only the rows that matter:

CREATE INDEX open_orders ON orders (created_at) WHERE paid IS NOT TRUE;

Fewer indexed rows means a smaller index and cheaper writes. In MySQL, an index always covers every row in the table.

Is one of them faster?#

Benchmarks that crown a universal winner rarely match your workload. In both, the biggest wins come from the same place: an index that matches your WHERE and ORDER BY, queries that fetch only the columns you need, and connection pooling so you do not open a connection per request. Run EXPLAIN on your slowest queries in each engine before blaming the database. If a test with your real data shows a clear gap, that result outweighs any generic benchmark.

When should you pick PostgreSQL, and when MySQL?#

Pick PostgreSQL if you want safe migrations, queryable JSON, partial indexes, extensions (such as geospatial) or more complex analytical queries. It is a good default for new projects and APIs.

Pick MySQL if your stack already requires it (WordPress, legacy systems), your team knows it well, or the app is a simple CRUD. Switching engines without a reason costs more than any theoretical gain.

Either way, the right index and a good schema matter more than the engine. If you are still unsure about database types, read which database to choose.

How to do this on Square Cloud#

Square Cloud offers PostgreSQL and MySQL hosting from the Standard plan, always on recent versions. You create the database in the dashboard, and it is provisioned in seconds in the same datacenter as your applications (about 1 ms average latency), with mandatory TLS. PostgreSQL is native PostgreSQL, without extensions, so features such as PostGIS are not available.

You pick the engine, connect your application with the connection URL the dashboard hands you and control the rest. Both engines can live on the same plan, each using at least 1024 MB of the plan's RAM. You take manual snapshots whenever you want, and every plan that includes databases also has an automatic daily snapshot. The Hobby plan does not include database hosting.

See PostgreSQL hosting and MySQL hosting.