Skip to content
Tutorials Center
My learning

Topics

Lesson 2 of 90 ยท Getting Started with PostgreSQL

PostgreSQL vs MySQL

Compare PostgreSQL and MySQL: licensing, storage engines, SQL features, strictness, case sensitivity, replication and the traps to avoid when migrating between them.

PostgreSQL and MySQL are the two most widely used open-source relational databases, and a very common first question is "which one should I use?". The honest answer is that both are excellent, both run huge systems, and for a typical web application either will work. They differ in defaults, strictness and depth of features, and those differences matter most at two moments: when you choose one for a new project, and when you move an existing application from one to the other.

This lesson compares them fairly, then lists the traps that catch people who bring MySQL habits to PostgreSQL (or the reverse). Everything that depends on a particular server is shown as a read-only block. The live examples run in your browser: one Python program simulates the behaviors that differ, and one SQLite query shows the parts of SQL that are portable. For the MySQL side in depth, see the MySQL tutorial; for the vendor-neutral view, see SQL Dialect Differences.

The big picture

Two great open-source databases

PostgreSQL Rich types, strict rules, extensible.
MySQL Simple, fast reads, huge ecosystem.

Both are mature, free and run very large sites.

1 / 2
Area PostgreSQL MySQL (InnoDB)
License PostgreSQL License (permissive, community owned) GPL with a commercial option, owned by Oracle (MariaDB is a community fork)
Architecture One storage engine, process per connection Pluggable engines (InnoDB is the default), thread per connection
Data types Arrays, ranges, jsonb, uuid, network, geometric, composite, enum, domains Fewer: json, enum, set, spatial; no arrays or ranges
Indexes B-tree, hash, GiST, SP-GiST, GIN, BRIN, partial and expression indexes B-tree (clustered primary key), full-text, spatial, functional indexes
Querying FILTER, LATERAL, DISTINCT ON, RETURNING, full MERGE, recursive CTEs, window functions CTEs and window functions (8.0+), LATERAL (8.0.14+), no RETURNING, no FULL JOIN
Transactions DDL is transactional; true SERIALIZABLE (SSI) DDL commits implicitly; SERIALIZABLE uses locking
Procedural code PL/pgSQL, plus Python, Perl and others as extensions Stored procedures in one SQL/PSM-style language
Replication Streaming (physical) and logical, both built in Binary-log based, simple setup, group replication
Extensions PostGIS, pgvector, TimescaleDB, Citus and hundreds more Plugins exist, but far fewer

The table hides nuance. MySQL 8 closed many gaps (window functions, CTEs, JSON), and PostgreSQL keeps getting easier to run. Use the table to guide a conversation, and check the current documentation of both before a big decision.

Where PostgreSQL tends to win

  • Complex queries and correctness. Rich SQL, mature planner, real SERIALIZABLE isolation and transactional DDL make it a strong fit for reporting, analytics on operational data, and anything where wrong data is costly. See Isolation Levels and Transactions and ACID.
  • Flexible data. jsonb with GIN indexes, arrays, range types and exclusion constraints let you model awkward domains in the database. See JSONB Basics and Range Types.
  • Extensibility. Geospatial data (PostGIS), vector search (pgvector), time series and sharding extensions make PostgreSQL a platform. See Extensions Overview.
  • Strict by default. Bad input is rejected rather than coerced (details below).

Where MySQL tends to win

  • Simplicity and ubiquity. It is installed everywhere, shared hosts offer it, and almost every PHP application and many CMSs (WordPress, for example) are written for it. Tooling and hosting choices are vast.
  • Simple read-heavy workloads. InnoDB's clustered primary key makes lookups by primary key very quick, and a plain web application with simple queries performs very well.
  • Easy replication setups for read scaling, and a large pool of operators experienced with it.
  • Pluggable engines (for example, MyRocks for write-heavy storage in some deployments).

Neither list is a verdict. A team that knows one system deeply will usually do better with it than with a theoretically superior choice it does not know.

Migration traps

If you move SQL between the two systems, the same query can behave differently, or fail. These are the differences that bite most often.

1. Case sensitivity of comparisons. With MySQL's default collations (for example utf8mb4_0900_ai_ci), WHERE name = 'ALICE' also matches alice. PostgreSQL compares text exactly. Port a login lookup carelessly and users with [email protected] stop matching. Use lower(email) with an expression index, ILIKE, or the citext extension (see Useful Extensions).

2. Quoting identifiers. MySQL quotes with backticks (`order`), PostgreSQL with double quotes ("order"). Strings use single quotes in both. PostgreSQL folds unquoted names to lower case, so CREATE TABLE Users creates users; if you quote "Users" you must always quote it. Avoid quoted mixed-case names.

3. Strictness and silent conversion. PostgreSQL raises an error for a value that does not fit (varchar(5) receiving six characters, an invalid date such as 2026-02-30, text in an integer column). MySQL, depending on sql_mode, may truncate or substitute a default with only a warning. Code that relied on that leniency will fail in PostgreSQL, which is a good thing once fixed.

4. GROUP BY and integer division. PostgreSQL requires every selected column to be grouped or aggregated (unless functionally dependent on a grouped primary key). MySQL's ONLY_FULL_GROUP_BY is on by default in 8.0, but older code often relies on it being off. Also, 5 / 2 is 2 in PostgreSQL when both are integers, while MySQL returns 2.5000 and has a separate DIV operator. Cast explicitly: 5::numeric / 2.

5. Auto-increment. MySQL has AUTO_INCREMENT; PostgreSQL uses GENERATED ... AS IDENTITY (or older serial) backed by sequences, which can leave gaps. See UUIDs and Identity Columns. To get the new id back, MySQL uses LAST_INSERT_ID(), PostgreSQL uses INSERT ... RETURNING id.

6. Upsert and other syntax.

SQL
-- MySQL
INSERT INTO counters (name, hits) VALUES ('home', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;

-- PostgreSQL
INSERT INTO counters (name, hits) VALUES ('home', 1)
ON CONFLICT (name) DO UPDATE SET hits = counters.hits + 1;

More pairs: LIMIT 10, 20 becomes LIMIT 20 OFFSET 10; IFNULL(a, b) becomes COALESCE(a, b); CONCAT works in both, and || concatenates strings in PostgreSQL (in MySQL it is logical OR unless PIPES_AS_CONCAT is set); NOW() works in both; DATE_FORMAT(d, '%Y-%m') becomes to_char(d, 'YYYY-MM'); TINYINT(1) flags become boolean; DATETIME becomes timestamp or, better, timestamptz; ENGINE=InnoDB clauses are dropped.

7. DDL and transactions. In PostgreSQL a failed migration inside BEGIN ... COMMIT rolls back all its DDL. In MySQL each DDL statement commits implicitly, so a half-applied migration leaves a half-changed schema. Migration tools exploit this (see Schema Migrations).

8. Maintenance. MySQL's InnoDB cleans old row versions in the background without much attention. PostgreSQL needs vacuum, which is automatic but needs monitoring on busy tables. Connection handling differs too: PostgreSQL's process-per-connection model means you should use a pooler (see Connection Pooling).

The first program simulates three of these behaviors so you can see them side by side:

Python Three behaviors that differ

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The simulation encodes documented default behavior, not a live server, so check your own sql_mode and collation settings when you test a real migration.

The second example shows the common ground. Most everyday SQL (joins, GROUP BY, COALESCE, LIMIT ... OFFSET, the ON CONFLICT upsert) is the same in PostgreSQL and SQLite, so this runs on the site's SQLite engine and would run unchanged in PostgreSQL:

SQL Portable SQL: upsert, COALESCE and paging

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

Never migrate by translating syntax alone. Load a copy of the real data into the new system, run the application's test suite and compare query results (see Copying and Comparing Tables). Tools such as pgloader can move the data, but they cannot tell you that a query now returns different rows because of case sensitivity.

How to choose

Ask these questions in order:

  1. Does the software you must run require one? WordPress and many PHP applications assume MySQL or MariaDB. Many Python, Ruby and Go projects treat PostgreSQL as their default.
  2. Do you need a feature only one offers? Geospatial queries, vector search, range types, row-level security and transactional DDL point to PostgreSQL.
  3. What does your team already know how to operate? Backups, monitoring and incident response are skills, and they transfer poorly.
  4. What does your cloud offer? Both are available as managed services on every large provider.

If none of these decides it, choose PostgreSQL for new applications that will grow in complexity, and do not worry too much: moving later is possible, if effortful.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A login query WHERE email = '[email protected]' worked on MySQL but returns no rows on PostgreSQL for a stored value [email protected]. Why?

    Use lower(email) with an expression index, ILIKE, or citext to get case-insensitive lookups in PostgreSQL.
  2. 2. Which is the PostgreSQL equivalent of MySQL's INSERT ... ON DUPLICATE KEY UPDATE?

    PostgreSQL uses ON CONFLICT ... DO UPDATE (or MERGE in version 15 and later).
  3. 3. What happens when a migration script with several ALTER TABLE statements fails halfway inside a transaction?

    PostgreSQL has transactional DDL. MySQL commits DDL statements implicitly one by one.
  4. 4. In PostgreSQL, SELECT 5 / 2 with two integer literals returns:

    Integer division truncates. Cast one operand to numeric to get 2.5.
  5. 5. A team knows MySQL deeply, ships a simple CRUD application on a MySQL-only CMS. What is the best advice?

    Both are production-grade. Requirements of the software and the team's operating experience usually decide the question.

Summary

  • PostgreSQL emphasizes rich types, strictness, extensibility and transactional DDL; MySQL emphasizes simplicity, ubiquity and pluggable engines. Both are mature and free.
  • The traps when migrating are case-sensitive text comparison, identifier quoting, strict rejection of bad data, GROUP BY and integer division, auto-increment versus identity with RETURNING, and different upsert syntax.
  • ON CONFLICT replaces ON DUPLICATE KEY UPDATE, COALESCE replaces IFNULL, LIMIT n OFFSET m replaces LIMIT m, n, and to_char replaces DATE_FORMAT.
  • Migrate with real data and tests, never by translating syntax alone.
  • Choose by software requirements, needed features and your team's experience.

Next: psql: The Command-Line Client.