PostgreSQL 17 vs MySQL 8.4 LTS: Where the Feature Gaps Are in 2026
Most online comparisons cite Postgres 13-15 / MySQL 5.7-8.0. Here is the 2026 picture, version-stamped. Verified against postgresql.org/docs/17 and dev.mysql.com/doc, April 2026.
JSON / JSONB
Postgres leadsJSONB with GIN indexing, jsonb_path_query, -> ->> @> ? operators, partial indexes on JSON fields
JSON type (5.7+), JSON_TABLE / JSON_VALUE / JSON_QUERY (8.0+), generated columns + indexes for query support
MySQL closed most of the gap with 8.0. Ergonomics still trail (no JSONB binary format, fewer native operators). For JSON-heavy workloads, Postgres JSONB indexing is a real advantage.
Window functions
ParityROW_NUMBER / RANK / LAG / LEAD / NTILE with OVER clause, FILTER, EXCLUDE
ROW_NUMBER / RANK / LAG / LEAD / NTILE with OVER clause (parity since 8.0)
Parity reached in MySQL 8.0. Syntax is identical for standard window functions. Postgres has FILTER and GROUPS frame mode as extensions.
CTEs and recursive CTEs
Near parityWITH (MATERIALIZED / NOT MATERIALIZED hint), recursive CTE, CTE in UPDATE/DELETE
WITH, recursive CTE (parity since 8.0), no materialisation hint
Parity for most workloads. Postgres MATERIALIZED hint gives finer planner control. Both handle tree traversal with recursive CTEs.
Partitioning
Postgres aheadDeclarative partitioning (range, list, hash) matured 12-17. Partition pruning, join improvements through 16-17.
Native partitioning (range, list, hash, key, columns). Limits: no FK to/from partitioned tables until recent fixes.
Postgres declarative partitioning (PG 10+) matured significantly through 12-17. MySQL partitioning has documented limits around foreign key compatibility.
Full-text search
Postgres aheadtsvector / tsquery, GIN indexes, language-specific stemmers, ranking (ts_rank), trigram search (pg_trgm)
InnoDB FULLTEXT indexes, MATCH / AGAINST, natural language mode
Both lose to dedicated search (Elasticsearch, Typesense, Meilisearch) at scale. Postgres FTS is more flexible for mid-tier use cases. MySQL FULLTEXT is sufficient for basic search.
GIS / Spatial
Postgres + PostGISPostGIS extension: industry standard. Geometry, geography, raster, topology. Every major open GIS tool uses PostGIS.
Native spatial types, basic ST_ functions. Adequate for simple geometry.
Not even close. PostGIS is the industry standard for geospatial in SQL databases. MySQL spatial support is adequate for simple point-in-polygon; PostGIS handles complex geospatial operations that MySQL cannot.
Replication
Different models; both production-gradeStreaming (physical) + logical replication (PG 10+, mature by 16-17). Logical failover (PG 17). Patroni / Stolon for HA.
Binlog replication (canonical, most battle-tested). Group Replication (5.7+). InnoDB Cluster. Vitess for sharded.
MySQL binlog replication is the most battle-tested replication system on the planet (Booking.com, Shopify, Slack at scale). Postgres logical replication reached parity for most patterns in PG 16-17. Jepsen (2023) had critical findings on MySQL Group Replication consistency under partition.
Upsert / MERGE
Near parityON CONFLICT DO UPDATE (idiomatic), MERGE statement (PG 15+, SQL:2016 compliant)
ON DUPLICATE KEY UPDATE (canonical idiom), MERGE (8.0+)
Both work. Postgres MERGE is more flexible and SQL:2016 compliant. MySQL ON DUPLICATE KEY is more concise for simple upserts.
Stored procedures
Postgres aheadPL/pgSQL (default), PL/Python, PL/V8, PL/Perl, PL/R. Multi-language capability.
SQL stored procedures only. No embedded scripting languages.
If stored procedures with Python, JavaScript (V8), Perl, or R are part of your stack, Postgres is the only option. MySQL SP capability is adequate for SQL-only business logic.
Constraints
Postgres aheadRich CHECK constraints (enforced), exclusion constraints (unique with operators), deferrable FKs, concurrent index
CHECK enforcement since 8.0 (was syntax-only before). FK with InnoDB. Online DDL with limits.
Postgres exclusion constraints have no MySQL equivalent. Deferrable foreign keys are uniquely useful for bulk loads and complex transaction patterns. MySQL CHECK enforcement before 8.0 was a longstanding gap now closed.
Feature summary table
| Feature | Verdict |
|---|---|
| JSON / JSONB | Postgres leads |
| Window functions | Parity |
| CTEs and recursive CTEs | Near parity |
| Partitioning | Postgres ahead |
| Full-text search | Postgres ahead |
| GIS / Spatial | Postgres + PostGIS |
| Replication | Different models; both production-grade |
| Upsert / MERGE | Near parity |
| Stored procedures | Postgres ahead |
| Constraints | Postgres ahead |