PostgreSQL vs MySQL

What is PostgreSQL?

PostgreSQL (often called Postgres) is an open-source object-relational database system with over 35 years of development. It began as the POSTGRES project at UC Berkeley in 1986 and became fully open-source in 1996. It is governed by the PostgreSQL Global Development Group and released under the permissive PostgreSQL Licence, which imposes no restrictions on commercial use or distribution.

Postgres uses MVCC (Multi-Version Concurrency Control) throughout its storage engine. Every row written creates a new version; old versions are retained until a VACUUM pass reclaims space. This means readers never block writers and writers never block readers — a significant advantage for mixed read/write workloads.

Its extension system is one of its most valued features. Extensions such as PostGIS (geospatial), pgvector (vector similarity search for AI applications), TimescaleDB (time-series), and pg_cron (scheduled jobs) can be installed into the database with CREATE EXTENSION. This makes Postgres a foundation for specialised workloads without leaving the relational model.

PostgreSQL's JSONB type stores JSON in a parsed binary format. Unlike plain text JSON columns, JSONB supports GIN indexes, key-existence operators (?), containment operators (@>), and path queries — making it competitive with document databases for many semi-structured data use cases. This is particularly relevant when teams debate SQL vs NoSQL for new projects.

What is MySQL?

MySQL is an open-source relational database originally developed by MySQL AB in 1995. It was acquired by Sun Microsystems in 2008 and then by Oracle in 2010. The Community Edition is released under the GPL (GNU General Public Licence); the Enterprise Edition requires a commercial licence from Oracle. A popular fork, MariaDB, was created by MySQL's original authors after the Oracle acquisition and is largely compatible.

MySQL's architecture supports pluggable storage engines. The default and most important is InnoDB, which provides ACID transactions, row-level locking, foreign key enforcement, and its own MVCC implementation. Older engines such as MyISAM lack transactions but were historically faster for read-only workloads. The Memory engine stores data in RAM for temporary tables.

performance_schema is a MySQL facility that instruments the server's internal execution events — query timing, lock waits, I/O, and memory usage. It is MySQL's primary tool for performance analysis, complemented by the sys schema, which provides human-readable views over performance_schema data.

MySQL's binary log (binlog) records every change made to the database and drives replication. GTID-based replication assigns a globally unique identifier to each transaction, simplifying failover and topology changes. MySQL 8.0 also introduced Group Replication for synchronous multi-primary clusters.

Key differences in depth

Licensing: PostgreSQL Licence vs GPL

The PostgreSQL Licence is a permissive open-source licence similar to the MIT or BSD licences. You can use, modify, and distribute PostgreSQL in proprietary products without obligation to release your source code. This makes it particularly attractive for SaaS companies and cloud providers.

MySQL Community Edition is under the GPL version 2. The GPL's "copyleft" provisions mean that if you distribute software that links against MySQL, you must either release that software under the GPL or purchase a commercial licence from Oracle. For internal use — the most common scenario — this is not an issue. However, it has influenced some companies to prefer PostgreSQL or to use MariaDB as a drop-in replacement.

Full-text search

PostgreSQL's full-text search is based on tsvector (a sorted list of normalised lexemes) and tsquery (a search expression). The to_tsvector() and to_tsquery() functions handle stemming, stop words, and ranking via ts_rank(). GIN indexes make queries fast. It handles multiple languages via configurable dictionaries.

MySQL provides FULLTEXT indexes on InnoDB tables (since MySQL 5.6). Queries use MATCH … AGAINST syntax in BOOLEAN or NATURAL LANGUAGE mode. It is serviceable for basic search but lacks the linguistic depth and composability of PostgreSQL's approach. For production search, both databases are often complemented by Elasticsearch anyway.

Tablespaces

Both databases support tablespaces — the ability to map database objects to specific directories or disk volumes. In PostgreSQL, CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd' lets you place hot tables on faster storage. InnoDB tablespaces work similarly, with per-table files (innodb_file_per_table) or shared tablespace files.

Code side-by-side

Query JSON data stored in a metadata column:

-- Find products where metadata->>'category' = 'books'
SELECT id, name, metadata->>'price' AS price
FROM products
WHERE metadata @> '{"category": "books"}'
ORDER BY (metadata->>'price')::numeric DESC;

-- GIN index for fast key lookups
CREATE INDEX idx_products_meta
  ON products USING GIN (metadata);

-- Find products where category = 'books'
SELECT id, name,
  JSON_EXTRACT(metadata, '$.price') AS price
FROM products
WHERE JSON_EXTRACT(metadata, '$.category') = 'books'
ORDER BY JSON_EXTRACT(metadata, '$.price') DESC;

-- Functional index on JSON path (MySQL 8.0+)
ALTER TABLE products
  ADD INDEX idx_category
    ((CAST(metadata->>'$.category' AS CHAR(50))));

Window function: rank users by order total per region:

SELECT user_id, region, total,
  RANK() OVER (
    PARTITION BY region
    ORDER BY total DESC
  ) AS regional_rank
FROM orders;

SELECT user_id, region, total,
  RANK() OVER (
    PARTITION BY region
    ORDER BY total DESC
  ) AS regional_rank
FROM orders;

Window function syntax is identical — MySQL 8.0 added full SQL:2003 window function support, catching up with PostgreSQL.