Comparison: MySQL vs PostgreSQL
Modified date: Monday, June 30, 2025
Table of Contents
General
PostgreSQL and MySQL are two of the most widely used open-source relational database management systems, but they differ significantly in features, architecture, and use cases. PostgreSQL is known for its full SQL compliance, robust ACID, and support for a wide range of data types including its ORDB extensions. PostgreSQL also offers advanced indexing options such as GiST, GIN, and BRIN, making it efficient for complex queries and analytical workloads. MySQL, on the other hand, is often preferred for high-performance read-heavy web applications, with broad support across web hosting providers. While MySQL’s InnoDB storage engine provides ACID-compliant transactions, it generally offers fewer advanced features compared to PostgreSQL when handling complex queries, or extensibility requirements.
In recent years, after Oracle's acquisition of MySQL, PostgreSQL has gained increasing popularity and market share over MySQL, particularly in modern cloud-native and serverless architectures. Platforms like Supabase have built entirely on PostgreSQL, leveraging its native real-time change feeds, row-level security, stored procedures, and extensibility to deliver scalable backend-as-a-service solutions. PostgreSQL’s support for complex queries, materialized views, and custom procedural languages has made it a preferred choice for developers and enterprises seeking flexibility, strong data integrity, and advanced query capabilities in a relational database system.
| Feature | MySQL | PostgreSQL | Definition |
|---|
| intro | MySQL is an open source relational database management system (RDBMS) that’s used to store and manage data. | PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. | in their own words - but I reserved the rights to remove some bold claims like "the best", unless it is widely recognized. |
| vendor | Oracle | PostgreSQL Committees | |
| initial release | 1995 | 1998 | |
| latested version | 9 | 17 | We don't put a release date here as the software is patching frequently. So tracking it is not much useful. |
| db-engines ranking | 2 | 4 | ranks from https://db-engines.com/en/ranking (06/25) |
| relational? | yes | yes | Is it a relational database? (1) Most database are actually with some extensions, for example, nested data types, graph support, etc, which we usually called "multi-model". (2) Some of them are product family, meaning they have more than one database. Here we focus on the main one but explain others when needed. |
| open source? | yes | yes | mainly the engine code |
| license | commerical, GNU a dual-license model: an open-source license (GPL) and commercial licenses | PostgreSQL PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses -- allows for commercial use and modification of the software. | |
| price: box software | $0 ~ $32,100 (2023) MySQL pricing by edition (annual subscriptions): （1）Community Edition: Open-source and free — from $0 (2) Standard Edition: Starts at $2,140/year for a two-core server; can scale up to $12,840/year as you add more cores portable.io (3) Enterprise Edition: Starts at $5,350/year for two cores; increases up to $32,100/year depending on core count | 0 free download | |
| on-premise offering | yes | yes | if no means you can't buy "box" software from them |
| cloud offering | None | cloud vendors PostgreSQL’s cloud offerings are diverse, with many managed services available across major providers. For example, Amazon RDS for PostgreSQL. Technically, there are many efforts on improving PostgreSQL's native cloud implementation. The most famous one is Neon, which is acquired by DataBricks on 2025. | |
| technical doc | https://dev.mysql.com/doc/ | www.postgresql.org/docs | |
| supported platforms | | x86, PowerPC, S/390, SPARC, ARM, MIPS, RISC-V, and PA-RISC | supported OS/CPU platforms |
Data Types
SQL
In terms of SQL capabilities, PostgreSQL offers far more extensive standards compliance and advanced SQL features than MySQL. PostgreSQL supports full window functions, common table expressions (CTEs), recursive queries, lateral joins, set-returning functions, filtered aggregates, and rich subquery optimization, all conforming closely to SQL:2023 standards. It also provides advanced transactional semantics with serializable snapshot isolation and robust support for complex constraints, including partial indexes, exclusion constraints, and expression-based indexes. MySQL, while improving over time, has more limited support in several areas: recursive CTEs were only introduced in version 8.0, window functions arrived late, and features like lateral joins, filtered indexes, and full standards-compliant CHECK constraints remain incomplete or absent. Additionally, PostgreSQL’s powerful procedural languages (PL/pgSQL, PL/Python, PL/Perl, etc.) allow deeply embedded logic within the database, while MySQL primarily relies on a more limited procedural SQL dialect with far fewer control structures and extensibility options.
| Feature | MySQL | PostgreSQL | Definition |
|---|
| SQL: standard complaince | medium | high | |
| PL: main | SQL + SP | SQL + PL/PgSQL | main programming lanage: most database suports SQL because SQL is a well established standard. However, each database would like to extend SQL more or less. |
| PL: other language support | no | yes | PL lanaguage other than PL/SQL, like PL/Java, PL/Rust etc |
| Object-Relational? | no | yes | |
| vector search | no native | no native Extensions like pgvector supports it. | does it support vector search |
| max SQL length | | undefined The actual limit may depends on limits of the SUT, for example, its memory/system swap settings: PostgreSQL internally use a struct named "StringInfo" to hold the SQL string, which is with MAX_INT length. | maximal SQL statement length |
| SP: max parameters | | 100 | |
| UDF: max parameters | | 100 | |
| SQL: max parameters | | 65535 | number of parameters in a PREPARED query |
| SQL: query hints | | GUC only | if it allows use query hints to guide the optimizer |
| SQL: explicit locking | | yes: row, page, table level | Locking is usually an internal matter - so does it allow explicit locking? What levels do they support? |
| Triggers? | | yes | If support triggers |
| Triggers: scope | | tables, views, foreign tables | What objects can have triggers |
| Triggers: type | | BEFORE, AFTER, INSTEAD OF | Types of triggers supported |
| Extension Mechanism | | C programming, link with engine | |
Storage and System
| Feature | MySQL | PostgreSQL | Definition |
|---|
| arch: server | C/S | C/S | Embedded or traditional C/S? |
| arch: run in browser? | no | no/yes Officially not supported, but there are some good OSS projects like Pglite. | It also known as a client-side database, is a database that is stored and managed within a user's web browser, rather than on a remote server. |
| arch: Multi-master support? | no | no | if multi-master support? |
| arch: primary/read replica? | yes | yes | if primary + mulitiple read replica supported |
| replication: sync/async | both | both | Can commits wait or w/o wait for replicas to acknowledge |
| ACID | yes/atomic DDL (non-transactional) Atomic DDL is not transactional DDL. DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement. This means that DDL statements cannot be performed within another transaction, within transaction control statements such as START TRANSACTION ... COMMIT, or combined with other statements within the same transaction. | yes/yes | for DML and DDL |
| ACID: durability | yes | yes | |
| Materialized View: support? | no | yes | |
| arch: in-memory support | | no | |
| replication: WAL shipping | | yes | Uses write-ahead log (WAL) shipping for replication |
| replication: quorum-based commit | | no | Multiple synchronous replicas with quorum for commit |
| database: max number | | 4,294,950,911 Note that 4G-4294950911=16385. The reason is that a database needs assign an object ID (you see the db folder is a number), which is starting from 16385 because smaller are reserved by boostrap. | |
| tables: max number per database | | 1,431,650,303 | |
| partitions: methods | | Range, List, Hash; limited composite (multi-level partitioning more limited than Oracle/SQL Server). | Supported partitioning strategies (range, list, hash, etc.). |
| partitions: global index | | no | index across partitions |
| rows: max rows per table | | 32T/row_size | The actual number depends on storage etc |
| index: max allowed index | | rows per table is restricted by the number of rows that can fit onto MAX_UINT*8K pages. | Max number of indices allowed per table |
| ACID: max isolation level | | Serializable PostgreSQL implements Serializable Snapshot Isolation (SSI) in version 9.1. | |
| ACID: max ANSI isolation level | | Serializable | |
Benchmarking
| Feature | MySQL | PostgreSQL | Definition |
|---|
| any official TPC benchmarks? | no | no | The TPC benchmark includes a set of tests simulating real-world scenarios to evaluate database performance. |
| Feature | MySQL | PostgreSQL | Definition |
|---|
| command line client | mysql | psql | it means "sql client" for database supporting SQL. For embedded atabase, the client includes the server together. |
| admin(GUI) | MySQL workbench | pgAdmin | |
Export Regulations
Both are open source databases, so they are easy with export regulations.
| Feature | MySQL | PostgreSQL | Definition |
|---|
| Jurisdiction | US | US | Which country controls export |
| ECCN | None | None | An Export Control Classification Number (ECCN) is a five-character alphanumeric code used to categorize items on the Commerce Control List (CCL) for export control purposes.
Most database may fall into 5D992.c category, "mass market encryption", which means it has some ordinary encryption related code, for example, the SSL connection code. |
| Eligible License Exception / CCATS | Not required There is no ECCN for open source software | Not required There is no ECCN for open source software | A License Exception is an authorization that allows you to export or reexport items subject to the EAR without needing to obtain a specific export license, provided certain conditions are met.
CCATS stands for Commodity Classification Automated Tracking System. The BIS assigns a CCATS number to products that it has classified under the Commerce Control List (CCL). |
| Encryption Components | SSL | SSL | Crypto functionality that triggers control |
Internal
| Feature | MySQL | PostgreSQL | Definition |
|---|
| concurrency control | InnoDB: MVCC | MVCC | |
| MVCC: implemented? | yes | yes | if implement MVCC for concurrency control |
| MVCC: rollback segment | yes MySQL supports multiple storage engines, with InnoDB is popular. So we focus on InnoDB here. | no PostgreSQL uses VACUUM to clean up old versions: it is more difficult than in-memory database because the old versions are mixed with new versions on database pages. | if uses rollback segment (RS) to store old versions. Without RS, old versions and new versions are mixed stored, then the database engine has to find a way to efficiently drop the old versions at certain point. |
| Implementation language | C++ | C | A DBMS may use mulitple programming languages, for example, supports its stored procedure. The major programming language used to implement the engine. |
Internal - Optimizer
Though both are cost based optimizer, PostgreSQL has a more advanced implementation than MySQL. MySQL 8.0 has introduced improvements like derived table optimization, improved subquery handling, and better histogram collection, its planner remains more constrained compared to PostgreSQL’s highly extensible, deeply cost-driven optimizer.
| Feature | MySQL | PostgreSQL | Definition |
|---|
| CBO? | yes | yes | if it employees a cost based optimizer |
| framework | System-R | System-R | System-R is more like a dynamic programming, bottom up optimizer, while Cascades/Volcano gebaseerd optimizer is more like top-down optimizer. |
| stats: multi column | no | yes | Multi-column stats may cause storage space bloat: for example, if one dimension has 100 buckets, then three dimensions will require 1M buckets - but reducing the total number of buckets will result in reduced accuracy. |
| plan guide? | no | no | Can we use plan guide to correct the plan? This is a more systematic and accurate way to repair the plan than plan hints. Especially during system upgrades, if the plan becomes worse, we can use plan guide to force a query to use a previous plan. |
| join order search | | dynamic programming + Generic Algorithm (GEQO) It also allows to extend the join order algorithm. | How join order permutations are explored during plan generation. |
| query hints? | | GUC only | if it allows use query hints to guide the optimizer |
Internal - Runtime
| Feature | MySQL | PostgreSQL | Definition |
|---|
| spilling support | yes | yes | "spilling" refers to the process of writing temporary data or intermediate results of a query to disk when the available memory is exhausted. This is crucial for handling large datasets or complex queries that require more memory than available. |
| model | Volcano | Volcano + push for parallel run | The operator scheduling model: pull-gebaseerd (Volcano), push etc. |
| support intra-parallel query? | no | yes | meaning a single query can utilize multi hardware threads to run it |
| adaptive execution (AQP)? | no | no | Traditionally, after the optimizer determines the plan, the runtime must execute it completely without any room for adjustment, such as which of the two tables should be built. The adaptive method allows the runtime to make some adjustments based on the actual situation, and the optimizer must also prepare for this uncertainty, such as preparing an alternative plan. |
| vectorization | no native. HeatWave supports it (not open source). | no native Several extensions allowed it. For example, pg_duckdb and Hydra. | Speed up OLAP queries using vectorized execution. A vectorized runtime exchange a bunch of rows between iterators, and these rows are physically sotre with column-oriented order. |
| iterator: join methods | except MJ | all 3 | Hash Join (HJ), Sort-Merge Join (MJ) and Nested loop Join (NLJ) are 3 major ones |
| Error: out-of-range and overflow | strict mode: abort. Non-strict mode: continue with warning. | abort the transaction | To maintain atomic requirement of ACID, database engine usually fail the statement and abort the transaction. |
| resource management | | simple: work_mem controls per-operator memory use | How execution memory is allocated and limited. |
Internal - Storage Engine
| Feature | MySQL | PostgreSQL | Definition |
|---|
| Page Size | 4K, 8K, 16K, 32K, 64K Every tablespace in a MySQL instance has the same page size. By default, all tablespaces have a page size of 16KB; you can reduce the page size to 8KB or 4KB by specifying the innodb_page_size option when you create the MySQL instance. You can also increase the page size to 32KB or 64KB. | 8K default configurable with the --with-blocksize=BLOCKSIZE option | The physical disk block size for storage and I/O. |
| Storage Compression | ROW_FORMAT=COMPRESSED | TOAST compression (within a row) | |
| ACID: durability mechanism | InnoDB: WAL | WAL | How is it implements durability. Database's classic way is write ahead logging (WAL). |
| Row Overflow / Large Row Handling | | Row within a page and large columns pointing to TOAST storage | How rows larger than page size are handled. Usually a row must be within one page, but big columns can only save a pointer with the row and pointing to the actual storage. This is also why there is a limit of maximal columns per table. |
| Index: primary cluster | | Heap storage by default; CLUSTER command for static clustering - if modification happens after, order is not maintained. | Whether physical rows are ordered by primary index. |
| Index: secondary | | B+Tree, GIN, GiST, SP-GiST, BRIN, Hash, Fulltext (via extensions). | Supported index types beyond the primary key. |
| index: column store as primary | | No native support; available via extensions (e.g. cstore_fdw, citus). | if column-store as primary storage supported |
| index: column store as secondary | | No native support; available via extensions (e.g. cstore_fdw, citus). | if column-store as secondary index supported |
| partition table | | declarative partition table is implemented via inheritance table, since V11 | Partitioning capabilities for large tables. |
| partition table: prunning | | mostly at compile time, some runtime | Ability to eliminate partitions at query compile time and runtime |