--- title: "MySQL vs PostgreSQL: Which Database Should You Choose?" description: "Compare MySQL and PostgreSQL on SQL features, JSON, indexing, replication, concurrency and licensing, with side by side examples to pick the right database." category: "Comparison Guide" language: en source: https://cubepath.com/docs/comparison-guide/mysql-vs-postgresql-which-to-choose updated: 2026-11-03 --- # 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 | Aspect | MySQL (InnoDB) | PostgreSQL | |---|---|---| | License | GPLv2 or commercial license from Oracle | PostgreSQL License (permissive, BSD-like) | | Current releases | 8.4 LTS, plus 9.x innovation releases | 17 and 18 (a new major version every year) | | Ubuntu 24.04 package | `mysql-server` | `postgresql` (version 16) | | Connection model | One thread per connection | One process per connection (use a pooler such as PgBouncer at scale) | | Concurrency control | MVCC with undo logs, purged in the background | MVCC with row versions in the table, cleaned up by autovacuum | | JSON | `JSON` type, functional indexes on extracted values | `json` and `jsonb`, GIN indexes on whole documents | | Index types | B-tree, full-text, spatial, functional, invisible | B-tree, hash, GIN, GiST, SP-GiST, BRIN, partial, expression | | Materialized views | No | Yes | | `RETURNING` clause | No | Yes | | Transactional DDL | No (DDL is atomic but commits implicitly) | Yes (`CREATE`/`ALTER` can be rolled back) | | Extensions | Plugins, limited | Rich ecosystem: PostGIS, pgvector, TimescaleDB, pg_stat_statements | | Replication | Binary log replication, Group Replication / InnoDB Cluster | Streaming (physical) and logical replication | | Forks and compatibles | MariaDB, Percona Server | Many PostgreSQL-compatible products and extensions | ## Install both to follow along Both databases are in the Ubuntu 24.04 repositories. On a test server, install them side by side: ```bash sudo apt update sudo apt install mysql-server postgresql ``` Check that both services are running: ```bash 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): ```bash sudo mysql ``` And a PostgreSQL shell as the `postgres` superuser: ```bash 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: ```sql 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: ```sql 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: ```sql INSERT INTO users (email) VALUES ('ana@example.com') AS new ON DUPLICATE KEY UPDATE email = new.email; ``` PostgreSQL, which also returns the resulting row in the same statement: ```sql INSERT INTO users (email) VALUES ('ana@example.com') 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ; 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]`: ```ini innodb_buffer_pool_size = 5G ``` And for PostgreSQL 16, in `/etc/postgresql/16/main/postgresql.conf`: ```ini 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: ```bash 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: ```bash 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.