What is the difference between PostgreSQL and MySQL for a web application?

For a web application, PostgreSQL and MySQL differ less in what they can do than in what they do by default. The differences you meet every day are string comparison (MySQL is case-insensitive by default, PostgreSQL is case-sensitive), how you get a generated ID back (RETURNING in PostgreSQL, LAST_INSERT_ID() in MySQL), upsert syntax, and how schema migrations behave when one fails. If you are starting fresh, use PostgreSQL: stricter defaults and transactional migrations prevent more bugs. If you are on MySQL, the same safety is available once you know which settings and habits to apply.

This article shows the differences side by side in SQL, for PostgreSQL 18 and MySQL 8.4 with InnoDB. For storage engines, replication and licensing, see PostgreSQL vs MySQL: the differences that matter.

Quick reference

Topic	PostgreSQL	MySQL
Identifier quoting	"name"	`name`
Unquoted identifiers	Folded to lower case	Column names case-insensitive; table names depend on the file system
String equality	Case-sensitive	Case-insensitive and accent-insensitive
Generated keys	GENERATED ... AS IDENTITY, sequences	AUTO_INCREMENT
Read back written rows	RETURNING	LAST_INSERT_ID(), or a second query
Upsert	ON CONFLICT (...) DO UPDATE	ON DUPLICATE KEY UPDATE
Boolean	Real boolean type	TINYINT(1)
DDL inside a transaction	Rolls back	Commits implicitly
Fractional seconds by default	Microseconds	None unless you ask

Identifiers and quoting

PostgreSQL folds unquoted identifiers to lower case and treats double-quoted identifiers as case-sensitive. An ORM or migration tool that quotes mixed-case names creates tables you must quote forever after:

-- PostgreSQL
CREATE TABLE "UserAccounts" ("Id" bigint PRIMARY KEY);

SELECT * FROM UserAccounts;    -- ERROR: relation "useraccounts" does not exist
SELECT * FROM "UserAccounts";  -- works

Use lower-case snake_case names and never quote them.

MySQL quotes identifiers with backticks. Double quotes delimit strings unless the ANSI_QUOTES SQL mode is on. Column names are not case-sensitive on any platform. Database and table names map to files, so they are case-sensitive on most Linux installations and not on Windows or macOS. A query that works on a developer's Mac can fail on the Linux production server:

-- MySQL on Linux
CREATE TABLE user_accounts (id BIGINT PRIMARY KEY);
SELECT * FROM User_Accounts;   -- ERROR 1146: Table 'app.User_Accounts' doesn't exist

The same rule keeps both safe: lower-case names everywhere.

String comparison and collation

This difference produces real bugs in login and uniqueness checks.

-- PostgreSQL: no row for a user stored as 'ada@example.com'
SELECT id FROM users WHERE email = 'Ada@Example.com';

-- MySQL (default collation utf8mb4_0900_ai_ci): returns the row
SELECT id FROM users WHERE email = 'Ada@Example.com';

MySQL's default collation ignores case and accents, so 'resume' = 'résumé' is true and a UNIQUE index treats those two values as duplicates. For case-sensitive columns such as tokens, declare a different collation:

-- MySQL
ALTER TABLE api_keys
  MODIFY token VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL;

PostgreSQL compares strings exactly. For case-insensitive e-mail addresses there are three options: a unique index on lower(email) with queries that also call lower(), the citext extension, or a nondeterministic ICU collation:

-- PostgreSQL
CREATE UNIQUE INDEX users_email_lower_idx ON users (lower(email));
SELECT id FROM users WHERE lower(email) = lower('Ada@Example.com');

-- or
CREATE COLLATION case_insensitive
  (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
ALTER TABLE users ALTER COLUMN email TYPE text COLLATE case_insensitive;

Auto-increment and identity columns

-- PostgreSQL
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total      numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- MySQL
CREATE TABLE orders (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  total      DECIMAL(12,2) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);

PostgreSQL identity columns are backed by a sequence, a separate object that you can also create and share across tables. GENERATED ALWAYS rejects an explicit value for id unless you write OVERRIDING SYSTEM VALUE, which catches a class of import mistakes. In both databases a rolled-back insert still consumes a number, so expect gaps.

Getting the row back: RETURNING

PostgreSQL returns data from the rows it just wrote, in the same round trip:

-- PostgreSQL
INSERT INTO orders (total) VALUES (49.90)
RETURNING id, created_at;

UPDATE orders SET total = total * 0.9 WHERE id = 1001
RETURNING id, total;

DELETE FROM sessions WHERE expires_at < now()
RETURNING user_id;

MySQL has no RETURNING clause (MariaDB does, which is one of the places the two differ). You read the generated key from the connection:

-- MySQL
INSERT INTO orders (total) VALUES (49.90);
SELECT LAST_INSERT_ID();

LAST_INSERT_ID() is per connection, so concurrent sessions do not interfere. After a multi-row insert it returns the value for the first row only. To read other defaults or the result of an update, run a SELECT in the same transaction.

Upsert

-- PostgreSQL
INSERT INTO page_views (page_id, view_date, views)
VALUES (42, CURRENT_DATE, 1)
ON CONFLICT (page_id, view_date)
DO UPDATE SET views = page_views.views + EXCLUDED.views
RETURNING views;

-- MySQL (row alias syntax, 8.0.19 and later)
INSERT INTO page_views (page_id, view_date, views)
VALUES (42, CURRENT_DATE, 1) AS new
ON DUPLICATE KEY UPDATE views = page_views.views + new.views;

Both need a unique index or primary key on (page_id, view_date). PostgreSQL makes you name the conflict target, so a violation of some other unique constraint still raises an error. MySQL's clause fires on a conflict with any unique index, and the manual advises against using it on tables with several unique indexes for that reason. The older VALUES(views) form in the update clause is deprecated in favour of the alias.

For "insert if absent", PostgreSQL has ON CONFLICT DO NOTHING. MySQL's INSERT IGNORE does the same for duplicates but also turns other ignorable errors, such as data conversion problems, into warnings, so use it with care.

Booleans

-- PostgreSQL
ALTER TABLE users ADD COLUMN is_active boolean NOT NULL DEFAULT true;
SELECT count(*) FROM users WHERE is_active;
INSERT INTO users (email, is_active) VALUES ('a@example.com', 2);  -- ERROR: type mismatch

-- MySQL
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;  -- stored as TINYINT(1)
INSERT INTO users (email, is_active) VALUES ('a@example.com', 2);      -- accepted
SELECT COUNT(*) FROM users WHERE is_active = TRUE;                     -- does not count the row with 2
SELECT COUNT(*) FROM users WHERE is_active;                            -- counts it

In MySQL TRUE is the integer 1, so = TRUE matches only 1 while a bare condition matches any non-zero value. A CHECK (is_active IN (0, 1)) constraint, enforced since 8.0.16, closes the gap.

Querying JSON

-- PostgreSQL (jsonb)
SELECT id, data->>'plan' AS plan
FROM accounts
WHERE data @> '{"plan": "pro"}';

CREATE INDEX accounts_data_idx ON accounts USING GIN (data);

-- MySQL (JSON)
SELECT id, data->>'$.plan' AS plan
FROM accounts
WHERE data->>'$.plan' = 'pro';

ALTER TABLE accounts
  ADD COLUMN plan VARCHAR(20) GENERATED ALWAYS AS (data->>'$.plan') VIRTUAL,
  ADD INDEX accounts_plan_idx (plan);

The operators look alike but the path syntax differs: PostgreSQL takes a key, MySQL takes a JSON path starting with $. One GIN index in PostgreSQL serves containment queries on any key. In MySQL you index each path you query, through a generated column as above or a functional index, and arrays through a multi-valued index.

GROUP BY strictness

Both reject a query that selects a column that is neither grouped nor aggregated:

SELECT customer_id, status, COUNT(*)
FROM orders
GROUP BY customer_id;
-- PostgreSQL: ERROR: column "orders.status" must appear in the GROUP BY clause
--             or be used in an aggregate function
-- MySQL:      ERROR 1055 (42000): ... this is incompatible with sql_mode=only_full_group_by

Both also accept ungrouped columns that are functionally dependent on the grouped primary key. The difference is that MySQL's check is the ONLY_FULL_GROUP_BY SQL mode, which is on by default but is switched off on many older installations. With it off, MySQL returns an arbitrary value for status. For "one row per group", PostgreSQL offers DISTINCT ON, and both support window functions such as ROW_NUMBER().

Pagination

LIMIT 20 OFFSET 40 works in both, and MySQL also accepts LIMIT 40, 20. Both must read and discard the skipped rows, so deep pages get slow in the same way. Keyset pagination is the fix in both:

-- PostgreSQL and MySQL
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-03-01 12:00:00', 98231)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Check the plan on your own data. If MySQL does not use the (created_at, id) index for the row comparison, write it out as created_at < ? OR (created_at = ? AND id < ?). See Why does OFFSET pagination get slower on every page?.

Full-text search

-- PostgreSQL
ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;
CREATE INDEX articles_search_idx ON articles USING GIN (search);

SELECT id, title, ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', 'replication lag') AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

-- MySQL
ALTER TABLE articles ADD FULLTEXT INDEX articles_ft (title, body);

SELECT id, title,
       MATCH(title, body) AGAINST ('replication lag' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('replication lag' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

PostgreSQL applies language-specific stemming through the text search configuration, so "replicating" matches "replication". Write STORED explicitly, because generated columns are virtual by default in PostgreSQL 18 and the tsvector should be computed once at write time. MySQL's built-in parser indexes whole words as written, so "replicating" and "replication" are different terms unless you use a boolean-mode prefix search such as replicat*. It skips stopwords and words shorter than innodb_ft_min_token_size, and needs the ngram parser for Chinese, Japanese and Korean text. The columns in MATCH() must match the index definition exactly.

Schema migrations

PostgreSQL runs DDL inside transactions. A migration that fails halfway leaves the schema as it was:

-- PostgreSQL
BEGIN;
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;
ALTER TABLE orders ADD CONSTRAINT orders_shipped_check CHECK (shipped_at >= created_at);
COMMIT;  -- if the second statement fails, the column is not added either

In MySQL every DDL statement commits implicitly, including any open transaction around it. Each statement is atomic on its own, but a migration with several statements can stop half applied. Write MySQL migrations as one schema change per migration file so that a failure leaves a known state.

On large tables the concern is locking, and here MySQL gives clearer control. State the algorithm you expect and MySQL fails with an error instead of silently copying the table:

-- MySQL
ALTER TABLE orders ADD COLUMN shipped_at DATETIME(6) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX orders_shipped_idx (shipped_at), ALGORITHM=INPLACE, LOCK=NONE;

-- PostgreSQL
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;          -- metadata only
CREATE INDEX CONCURRENTLY orders_shipped_idx ON orders (shipped_at);

CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so configure your migration tool to run that step outside one. If it fails it leaves an INVALID index that you must drop and recreate. In both databases a schema change waits behind any long-running transaction on the table and blocks new queries while it waits, which is why the lock timeout matters. More patterns are in How do I add an index or column to a big PostgreSQL table without downtime?.

Connections from app servers and serverless

PostgreSQL starts a process per connection and its default max_connections is typically 100. MySQL uses a thread per connection and handles a larger number more cheaply. In both cases the application should hold a small pool per instance rather than a connection per request.

Serverless functions break that model because each instance has its own pool and instances multiply under load. With PostgreSQL, put PgBouncer or your provider's pooler in between and use transaction pooling. That mode shares server connections between clients, so session state such as SET and session advisory locks does not carry over between transactions. With MySQL, a proxy such as ProxySQL or a managed equivalent does the same job. How do I fix "FATAL: sorry, too many clients already" in PostgreSQL? covers sizing.

Strict mode and character sets in MySQL

PostgreSQL always rejects invalid data: a string too long for varchar(20) or an out-of-range number is an error.

MySQL does the same only in strict SQL mode. STRICT_TRANS_TABLES is part of the default mode in 8.4, but configurations carried over from old servers often remove it, and then MySQL truncates the string or clamps the number and reports a warning. Check what you are running:

-- MySQL
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

Also check the character set. In MySQL utf8 is an alias for the deprecated three-byte utf8mb3, which cannot store emoji. Use utf8mb4 for the server, tables and connection. See Why does MySQL say "Incorrect string value" when I save an emoji?. PostgreSQL's UTF8 encoding covers all of Unicode.

Timestamps and time zones

Type	Stores	Converts with session time zone	Range
PostgreSQL timestamptz	An instant	Yes, on input and output	4713 BC to 294276 AD
PostgreSQL timestamp	Date and time as written	No	4713 BC to 294276 AD
MySQL TIMESTAMP	An instant (UTC)	Yes, on input and output	1970 to January 2038
MySQL DATETIME	Date and time as written	No	1000 to 9999

In PostgreSQL use timestamptz for anything that records when something happened. In MySQL use DATETIME holding UTC, with the application and the connection time zone both set to UTC, unless you are sure the 2038 limit of TIMESTAMP will not affect you (it already does for future dates such as subscription expiry).

MySQL temporal types have no fractional seconds unless you declare them. DATETIME stores whole seconds and DATETIME(6) stores microseconds. PostgreSQL stores microseconds by default.

ORM support

Prisma, Drizzle, Sequelize, TypeORM, Django, Rails Active Record, Laravel Eloquent, SQLAlchemy and Hibernate all support both databases, so the ORM rarely decides the question. The differences above leak through in a few places:

• ORMs use RETURNING on PostgreSQL to fetch generated values in one statement and fall back to LAST_INSERT_ID() on MySQL, which affects bulk inserts that need every new ID.
• PostgreSQL-only types (arrays, jsonb, ranges, native enums) are exposed by many ORMs. Django ships them in django.contrib.postgres. Using them ties the code to PostgreSQL.
• Many migration tools wrap each migration in a transaction on PostgreSQL. On MySQL that has no effect on DDL, so a failed migration needs manual cleanup.
• Tests written against SQLite or one engine do not prove behaviour on the other. Case sensitivity, boolean handling and GROUP BY rules all differ. Run the test suite against the database you deploy.

Checklist

1. Use lower-case snake_case identifiers and never rely on quoting.
2. Decide the case sensitivity of each text column that has a unique index or is used for lookup, and set the collation or index to match.
3. On MySQL, confirm sql_mode includes STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY, and that everything is utf8mb4.
4. On MySQL, declare DATETIME(6) when you need sub-second precision and store UTC.
5. On PostgreSQL, use timestamptz, identity columns and RETURNING.
6. On MySQL, write one DDL change per migration and state ALGORITHM and LOCK on large tables. On PostgreSQL, set lock_timeout and build indexes CONCURRENTLY outside a transaction.
7. Pool connections, and add a server-side pooler before deploying to serverless.
8. Run your tests against the same engine and version as production.

References

• PostgreSQL: Identifiers and Key Words
• PostgreSQL: Collation Support
• PostgreSQL: Identity Columns
• PostgreSQL: Returning Data from Modified Rows
• PostgreSQL: INSERT
• PostgreSQL: Tables and Indexes for Text Search
• PostgreSQL: CREATE INDEX
• PostgreSQL: Date/Time Types
• MySQL 8.4: Identifier Case Sensitivity
• MySQL 8.4: Case Sensitivity in String Searches
• MySQL 8.4: INSERT ... ON DUPLICATE KEY UPDATE
• MySQL 8.4: Information Functions (LAST_INSERT_ID())
• MySQL 8.4: MySQL Handling of GROUP BY
• MySQL 8.4: Server SQL Modes
• MySQL 8.4: Statements That Cause an Implicit Commit
• MySQL 8.4: ALTER TABLE
• MySQL 8.4: Fractional Seconds in Time Values
• MySQL 8.4: The DATE, DATETIME, and TIMESTAMP Types