MySQL vs PostgreSQL: Which Database Should You Choose?

MySQL and PostgreSQL are the two most popular open source relational databases. Both are fast, reliable and supported by every major framework and managed database service, so for a typical web application either one is a safe choice. They differ in SQL features, extensibility, how they handle concurrency and connections, and licensing. This guide compares them with runnable examples on Ubuntu 24.04 so you can see the differences yourself before choosing.

Quick comparison

Install both to follow along

Both databases are in the Ubuntu 24.04 repositories. On a test server, install them side by side:

sudo apt update
sudo apt install mysql-server postgresql

Check that both services are running:

systemctl status mysql --no-pager
systemctl status postgresql@16-main --no-pager

Open a MySQL shell as the root user (authenticated through the system root account by default):

sudo mysql

And a PostgreSQL shell as the postgres superuser:

sudo -u postgres psql

Create a test database in each (CREATE DATABASE demo;), connect to it (USE demo; in MySQL, \c demo in psql) and run the examples below.

SQL features

Both support the core of modern SQL: joins, subqueries, common table expressions (including recursive), window functions, CHECK constraints (enforced in MySQL since 8.0.16) and generated columns. The differences show up in the details.

Auto-increment keys

MySQL:

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

PostgreSQL uses standard identity columns:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Upserts

Insert a row, or update it if the email already exists. MySQL:

INSERT INTO users (email) VALUES ('[email protected]') AS new
ON DUPLICATE KEY UPDATE email = new.email;

PostgreSQL, which also returns the resulting row in the same statement:

INSERT INTO users (email) VALUES ('[email protected]')
ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email
RETURNING id, created_at;

In MySQL you would need a second query (or LAST_INSERT_ID()) to get the generated values back.

Transactional schema changes

In PostgreSQL a failed migration can be rolled back completely:

BEGIN;
ALTER TABLE users ADD COLUMN name TEXT;
CREATE INDEX users_name_idx ON users (name);
ROLLBACK;

After the ROLLBACK, neither the column nor the index exists. In MySQL each DDL statement commits the open transaction immediately, so a migration that fails halfway leaves the schema partially changed. Migration tools for MySQL have to account for that.

Materialized views

PostgreSQL can store the result of an expensive query and refresh it on demand:

CREATE MATERIALIZED VIEW daily_signups AS
SELECT date_trunc('day', created_at) AS day, count(*) AS signups
FROM users
GROUP BY 1;

REFRESH MATERIALIZED VIEW daily_signups;

MySQL has regular views only; the usual workaround is a summary table filled by a scheduled job.

JSON support

Both can store and query JSON, which covers most "document inside a relational database" needs.

MySQL stores JSON in a binary format and indexes individual fields through functional indexes:

CREATE TABLE events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    data JSON NOT NULL,
    INDEX idx_event_type ((CAST(data->>'$.type' AS CHAR(32)) COLLATE utf8mb4_bin))
);

SELECT id FROM events WHERE data->>'$.type' = 'login';

PostgreSQL's jsonb type supports GIN indexes over the whole document, so any key or containment query can use the index:

CREATE TABLE events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data JSONB NOT NULL
);
CREATE INDEX events_data_idx ON events USING GIN (data);

SELECT id FROM events WHERE data @> '{"type": "login"}';

If you store semi-structured data and query it in many different ways, PostgreSQL's jsonb is more flexible. If you only look up a few known fields, both work well.

Indexing

MySQL's InnoDB stores each table as a clustered index on the primary key, so primary key lookups and range scans are very fast, and secondary indexes point to the primary key. It supports B-tree, full-text, spatial and functional indexes, plus invisible indexes for testing the effect of dropping one.

PostgreSQL adds several index types that solve specific problems:

• Partial indexes index only the rows you query, for example active users:

  CREATE INDEX users_active_email_idx ON users (email) WHERE deleted_at IS NULL;
  

• GIN for jsonb, arrays and full-text search.

• GiST for geometric data, ranges and PostGIS.

• BRIN for very large tables that are naturally ordered, such as time series, at a tiny fraction of the size of a B-tree.

Use EXPLAIN in both databases to confirm a query uses the index you expect (EXPLAIN ANALYZE in PostgreSQL and MySQL 8.0.18+ also runs the query and shows real timings).

Performance and concurrency

There is no general winner in performance; results depend on the workload, schema, indexes and configuration. Some consistent patterns:

• Simple primary key reads and writes (typical CRUD web apps): both are very fast. MySQL's clustered primary key and thread-per-connection model handle large numbers of short queries efficiently.
• Complex analytical queries with many joins, CTEs and aggregates: PostgreSQL's planner, parallel query execution and richer index types often give it the edge.
• Many connections: PostgreSQL uses one process per connection, which costs more memory than MySQL's threads. Put PgBouncer in front of PostgreSQL when you have hundreds of application connections.
• Update-heavy tables: PostgreSQL writes a new row version on each update and relies on autovacuum to clean up. Keep autovacuum enabled and monitor table bloat.

The most important tuning setting in each is the memory used for caching data. For a server dedicated to the database with 8 GB of RAM, a reasonable starting point for MySQL, in /etc/mysql/mysql.conf.d/mysqld.cnf under [mysqld]:

innodb_buffer_pool_size = 5G

And for PostgreSQL 16, in /etc/postgresql/16/main/postgresql.conf:

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB

Restart the service after editing (sudo systemctl restart mysql or sudo systemctl restart postgresql) and confirm the value:

sudo mysql -e "SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS gb;"
sudo -u postgres psql -c "SHOW shared_buffers;"

Note that MySQL 8.0 removed the query cache; settings such as query_cache_size from old tutorials no longer exist.

Replication and high availability

• MySQL replicates through the binary log. Asynchronous and semi-synchronous replicas are simple to set up, and Group Replication with InnoDB Cluster and MySQL Router provides automatic failover.
• PostgreSQL offers streaming replication of the whole cluster (physical, with read-only hot standbys) and logical replication of selected tables, which also allows upgrades between major versions with little downtime. Automatic failover is handled by external tools such as Patroni.

Both are well supported by managed database services, which take care of replication, backups and failover for you.

Licensing and ecosystem

PostgreSQL is released under a permissive license, is developed by a community with no single owner, and can be embedded or redistributed without restrictions. MySQL is owned by Oracle and dual-licensed: GPLv2 for the community edition, with a commercial license for embedding it in proprietary products. MariaDB, a community fork, is a drop-in replacement for many MySQL use cases but has diverged in features over time.

PostgreSQL's extension system is a major strength: PostGIS for geospatial data, pgvector for AI embeddings and similarity search, TimescaleDB for time series, and pg_stat_statements for query statistics all install into a normal PostgreSQL server.

Migrating between them

Moving from MySQL to PostgreSQL is the more common direction. pgloader copies schema and data in one step and converts types automatically:

sudo apt install pgloader
pgloader mysql://app_user:your_password@localhost/app_db postgresql://app_user:your_password@localhost/app_db

Create the target PostgreSQL database and user first, and replace app_user, your_password and app_db with your values. Then review what pgloader cannot translate: queries using MySQL-specific syntax (ON DUPLICATE KEY UPDATE, backtick quoting, LIMIT x, y), case-insensitive comparisons that relied on MySQL collations, and stored procedures. Test the application against the migrated copy before switching.

Going from PostgreSQL to MySQL is harder when the application uses features MySQL lacks, such as RETURNING, arrays, jsonb operators, partial indexes or extensions.

Which one should you choose?

Choose MySQL when:

• You run software that targets it first, such as WordPress, Magento or many PHP applications and control panels.
• Your workload is mostly simple reads and writes by primary key with many short connections.
• Your team already knows MySQL replication and tooling well.

Choose PostgreSQL when:

• You need advanced SQL: RETURNING, materialized views, transactional migrations, partial or GIN indexes.
• You store JSON documents and query them flexibly, or need geospatial (PostGIS) or vector search (pgvector).
• You run analytical or reporting queries on the same database.
• You want a permissive license with no single vendor behind it.

For a new application with no constraints, PostgreSQL is the more versatile default today; for existing MySQL-based software, stay on MySQL.

Conclusion

MySQL and PostgreSQL are both production-ready and fast when properly indexed and tuned. MySQL is simple and widely supported by existing applications, while PostgreSQL offers richer SQL, better JSON handling and a powerful extension ecosystem. Install both on a test server, load a copy of your real data and compare the queries that matter to you. After choosing, secure the installation, set up automated backups with mysqldump or pg_dump, and tune the memory settings for your server size.