Skip to content
Tutorials Center
My learning

Topics

Lesson 2 of 75 ยท Getting Started with MySQL

MySQL vs PostgreSQL: Choosing

Compare MySQL and PostgreSQL: governance, SQL features, JSON, replication, concurrency design and typical use cases, with the syntax differences that bite when you switch.

"Should we use MySQL or PostgreSQL?" is one of the most common questions at the start of a project, and one of the most over-heated. Both are mature, free, relational databases that run enormous production systems. For a typical web application either one will serve you well. The differences matter at the edges: which features you need, which people and tools you already have, and which surprises you are willing to learn.

This lesson compares the two honestly. You will see how they are governed, where their SQL differs, how each handles JSON, replication and concurrency, and when each is the natural pick. The syntax differences that trip people up when they move between them are demonstrated at the end. For the shorter overview that also covers MariaDB and SQLite, see What Is MySQL?.

Two good databases

MySQL Simple to run, huge hosting and tooling support.
PostgreSQL Rich SQL and types, very strict and extensible.

Neither is the best. Each fits some projects better.

1 / 2

Governance and licensing

MySQL is developed by Oracle. The Community Edition is open source under the GPL, and Oracle also sells commercial editions with extra features such as an enterprise audit plugin and a thread pool. Development is driven by Oracle's roadmap, which is stable and well funded but not decided in public. Forks exist (MariaDB is the best known) because the community wanted an independent option.

PostgreSQL is developed by a worldwide community of companies and individual contributors. There is no single owner, no "enterprise edition", and the license is a permissive BSD-style license that lets anyone build products on top of it. Features arrive through a public mailing list process, and every major release comes about once a year.

This matters less day to day than people claim, but it affects two decisions: how comfortable your legal team is with a GPL product that Oracle controls, and which managed services you can choose from. Both databases are offered by every large cloud provider.

What is the same

For most applications the overlap is large:

  • Tables, rows, primary and foreign keys, transactions with ACID guarantees, and row-level locking.
  • Standard SQL for SELECT, joins, subqueries, GROUP BY, CTEs (WITH, including recursive ones) and window functions. MySQL gained CTEs and window functions in 8.0, so modern MySQL is no longer the "simple" engine it was.
  • B-tree indexes, composite indexes, covering indexes and an EXPLAIN command.
  • Replication to read replicas, point-in-time recovery from logs, and a healthy ecosystem of drivers for every language.

If your queries use only these, you can move between the two with modest changes. The hard parts are the places below where they differ.

Where PostgreSQL goes further

PostgreSQL has a larger feature list, and some of it is hard to live without once you have used it:

  • Richer types. Real boolean, arrays, range types, uuid, inet and cidr network types, and user-defined composite types. MySQL has none of the array and range types, and its BOOLEAN is just TINYINT(1).
  • Transactional DDL. In PostgreSQL you can wrap CREATE TABLE and ALTER TABLE in a transaction and roll them back. In MySQL, DDL statements are atomic on their own since 8.0, but they implicitly commit the current transaction and cannot be rolled back as part of a larger one.
  • More SQL. INSERT ... RETURNING, the FILTER clause on aggregates, ILIKE, FULL JOIN, partial indexes, materialized views and a long list of built-in functions. MySQL has none of these (FULL JOIN is emulated, see Joins in MySQL).
  • Extensions. PostgreSQL is designed to be extended. Geospatial work (PostGIS), time series, vector search and full-text options are available as add-ons that plug in deeply.

Where MySQL goes further

MySQL's strengths are less about syntax and more about operations:

  • Ubiquity. Almost every shared host, CMS and framework tutorial assumes MySQL or a compatible server. Skills and tooling are easy to find.
  • A clustered primary key. InnoDB stores each table as a B-tree ordered by the primary key, so lookups by key and range scans on the key are very fast. Choosing the primary key is therefore a performance decision (How Indexes Work).
  • Lightweight connections. MySQL serves each connection with a thread. PostgreSQL uses a separate process per connection, which is heavier, so large PostgreSQL deployments usually place a connection pooler in front.
  • Replication tooling. Statement or row-based binary log replication has been used at huge scale for decades, and Group Replication and InnoDB Cluster give you built-in high availability (Binary Log and Replication, High Availability).
  • Simple operations. Old row versions live in InnoDB's undo logs and are purged in the background. PostgreSQL stores old row versions in the table itself, so it relies on VACUUM (usually automatic) to reclaim space, and tables can bloat when it falls behind.

JSON

Both support JSON well, with different philosophies.

MySQL has a single binary JSON type. You query it with path expressions (JSON_EXTRACT or the -> operator on a column), update parts of it in place, and index it indirectly: either with a generated column that extracts a value, or with a multi-valued index for arrays (JSON Data Type, JSON Tables and Indexing).

PostgreSQL has json (stored as text) and jsonb (binary, the one you normally want). It adds a rich set of operators for containment (@>) and key existence, and a GIN index can speed up queries over a whole document without choosing keys in advance. For document-heavy workloads with unpredictable query shapes, PostgreSQL's jsonb is generally the more flexible tool. For JSON that sits beside a relational core, MySQL's type is entirely sufficient.

Concurrency and consistency

Both use MVCC, which lets readers and writers work without blocking each other, but the details differ:

  • MySQL's default isolation level is REPEATABLE READ; PostgreSQL's is READ COMMITTED. The same code can therefore behave differently on the two, for example in how phantom rows and lost updates appear (Isolation Levels and MVCC).
  • InnoDB uses gap locks and next-key locks at REPEATABLE READ, which can cause surprising lock waits and deadlocks on range queries (Locking and Deadlocks).
  • MySQL's default collation compares strings case-insensitively ('a' = 'A' is true). PostgreSQL compares strings case-sensitively unless you ask otherwise.
  • MySQL's behavior depends on the sql_mode setting: a lenient mode can accept bad data that PostgreSQL would reject. Modern defaults (strict mode) close most of the gap (SQL Modes).

Syntax differences that bite

The same idea is often spelled differently. Here are the ones you meet first:

Task MySQL PostgreSQL
Join strings CONCAT(a, b) a || b (or concat)
Quote an identifier `name` "name"
Auto-numbered key AUTO_INCREMENT GENERATED ... AS IDENTITY (or serial)
Upsert ON DUPLICATE KEY UPDATE ON CONFLICT (...) DO UPDATE
Get the inserted row LAST_INSERT_ID() INSERT ... RETURNING
Case-insensitive match LIKE (collation does it) ILIKE
Limit rows LIMIT n LIMIT n

The most dangerous entry is the first one. In standard SQL and PostgreSQL, || joins strings. In MySQL's default mode, || is a logical OR. This was checked on a MySQL 8.4 server:

SQL
SELECT 'a' || 'b';
-- Result: 0
-- Warning 1292: Truncated incorrect DOUBLE value: 'a'

Both strings were converted to the number 0, and 0 OR 0 is false, so you get 0 instead of ab. MySQL also prints a deprecation warning for this meaning of ||. A query that "works" after a copy from PostgreSQL can therefore silently return nonsense. The portable choice is CONCAT().

The three statements below run in the in-browser SQLite engine, which follows PostgreSQL's spelling in these places (||, ON CONFLICT, RETURNING). They show the PostgreSQL-style forms, so you can see what you would have to rewrite for MySQL. The MySQL spelling is in the comments.

SQL PostgreSQL-style syntax, with the MySQL rewrite (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

The first statement prints Hello, Ada. The two upserts leave one row for Ada with visits equal to 2, the RETURNING statement prints Ben's new row, and the final query shows both users.

Everything standard keeps working in both. A common-table expression with a window function, for example, is written identically in MySQL 8 and PostgreSQL:

SQL SQL that is portable to both (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

The running total for north is 100, 240 and 330. In month 1 the north region ranks first, and in month 3 the south region does. This statement is valid MySQL 8.0+ and PostgreSQL without a change.

Which one should you choose?

Use these as tie-breakers, not rules:

  • Choose MySQL when your team or host already knows it, you run a mainstream web or SaaS application, you want the simplest path to read replicas, or your software expects it (WordPress, Magento and many PHP applications are built for MySQL).
  • Choose PostgreSQL when you need advanced types or SQL (arrays, ranges, RETURNING, partial indexes, materialized views), geospatial analytics with PostGIS, heavy jsonb querying, or strict data rules enforced by the database from the start.
  • Do not choose on benchmarks. Published numbers depend on configuration, hardware and workload. For your own decision, test your real queries on both with realistic data.
  • Do not switch for fashion. Moving a working system between engines is a large project (types, SQL dialect, replication and operational habits all change). Pick well once, and learn the one you have deeply.

If you are building something new and have no constraint either way, both are safe. This tutorial teaches MySQL, and almost everything you learn about relational thinking, indexing and transactions transfers directly to PostgreSQL.

What ran live here

MySQL and PostgreSQL cannot run in the browser. The two examples ran in SQLite: its ||, ON CONFLICT and RETURNING follow PostgreSQL's spelling, which is why they appear here as the "other side" of the comparison, and the CTE with window functions is valid unchanged in MySQL 8 and PostgreSQL. The statement about 'a' || 'b', and the claims that MySQL lacks RETURNING, FILTER and ILIKE and that DDL implicitly commits, were checked on a real MySQL 8.4 server. Statements about PostgreSQL come from its documentation and were not run here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In MySQL's default SQL mode, what does 'a' || 'b' do?

    In MySQL's default mode || is the logical OR operator, so both strings become the number 0 and the result is 0. Use CONCAT() to join strings.
  2. 2. Which of these is a real difference in how the two databases treat DDL inside a transaction?

    PostgreSQL has transactional DDL. In MySQL, DDL is atomic on its own but ends the current transaction with an implicit commit.
  3. 3. Which statement describes the default transaction isolation levels?

    This difference can make identical application code behave differently on the two systems.
  4. 4. Which reason is a sound basis for choosing PostgreSQL over MySQL?

    Feature needs are a good tie-breaker. Public benchmarks depend heavily on configuration and workload and do not predict your results.
  5. 5. How does MySQL usually let you index a value inside a JSON document?

    MySQL indexes JSON indirectly, through a generated column that extracts a value or a multi-valued index for arrays. GIN indexes are a PostgreSQL feature.

Summary

  • MySQL (Oracle, GPL plus commercial editions) and PostgreSQL (community, permissive license) are both mature and fit most applications.
  • PostgreSQL has the longer feature list: richer types, transactional DDL, RETURNING, FILTER, partial indexes, materialized views and extensions.
  • MySQL's strengths are ubiquity, lightweight connections, a clustered primary key and battle-tested replication.
  • Defaults differ: isolation level, case sensitivity of strings, and strictness depend on sql_mode in MySQL.
  • Watch the dialect traps: || is OR in MySQL, upsert and auto-numbering are spelled differently, and MySQL has no RETURNING or ILIKE.
  • Choose by your constraints and team skills, test with your own workload, and avoid switching engines without a strong reason.

Next: Installing MySQL and Connecting.