Postgres vs MySQL in 2016: A Practical Comparison

April 12, 2016

The PostgreSQL versus MySQL debate has persisted for two decades. Both are excellent open-source relational databases with massive user bases, extensive documentation, and proven production track records. Choosing between them for a new project requires understanding their practical differences.

Having deployed both in production across various projects, here’s a comparison grounded in operational reality rather than theoretical benchmarks.

Philosophy and History

Understanding each database’s philosophy helps predict how they’ll behave in unfamiliar situations.

PostgreSQL prioritizes correctness and standards compliance. Its developers take the SQL standard seriously, implementing features according to specification. When facing a tradeoff between performance and correctness, PostgreSQL usually chooses correctness.

MySQL prioritizes practicality and ease of use. Its developers optimize for common cases and operational simplicity. MySQL has historically been more willing to accept non-standard behaviors if they serve user needs.

These philosophies influence everything from query optimization to error handling to data type behavior.

Data Integrity

PostgreSQL’s strictness catches errors that MySQL would silently accept.

Type Enforcement

PostgreSQL enforces data types strictly. Inserting a string into an integer column fails. Inserting a value that exceeds column precision fails.

MySQL’s default behavior is more forgiving—sometimes too forgiving. A value too large for a column might be silently truncated. A string inserted into an integer column might be converted to zero without error.

MySQL’s strict mode addresses many of these issues, and modern deployments should always enable it. But the default behavior reveals the philosophical difference.

Check Constraints

PostgreSQL has supported check constraints since version 7.0 (2000). MySQL added check constraint support in 8.0 (2016), and even then, earlier versions would accept the syntax but silently ignore it.

For applications requiring database-enforced business rules, PostgreSQL’s long-standing support for constraints matters.

Transactional DDL

PostgreSQL supports transactional DDL: schema changes can be wrapped in transactions and rolled back if needed. A migration that fails partway through doesn’t leave the schema in an inconsistent state.

MySQL doesn’t support transactional DDL for most operations. Schema changes commit immediately and can’t be rolled back. Migrations require more careful planning and potential manual intervention on failure.

Query Capabilities

PostgreSQL offers richer querying capabilities, while MySQL focuses on essential operations executed efficiently.

JSON Support

Both databases now support JSON, but PostgreSQL’s implementation is more mature and performant.

PostgreSQL’s JSONB (binary JSON) stores JSON documents in a decomposed binary format, enabling efficient querying and indexing. You can index specific JSON paths and query deeply nested structures efficiently.

MySQL’s JSON support is functional but less flexible. Indexing JSON values requires generated columns, and some operations are less performant than PostgreSQL equivalents.

Full-Text Search

PostgreSQL includes robust full-text search out of the box: stemming, ranking, phrase matching, and custom dictionaries. It’s not Elasticsearch, but it handles moderate search requirements without additional infrastructure.

MySQL’s full-text search is more limited. It works for basic keyword search but lacks PostgreSQL’s linguistic features and flexibility.

Window Functions and CTEs

PostgreSQL has supported window functions and Common Table Expressions (CTEs) for years. MySQL added window functions in 8.0 and CTEs in 8.0 as well.

If you’re using a recent MySQL version, these features are available. If you’re on an older version (common in production), you’ll miss them.

Custom Types and Functions

PostgreSQL allows defining custom types, operators, and functions in multiple languages (PL/pgSQL, PL/Python, PL/Perl, and others). This extensibility enables sophisticated data modeling and application-specific optimizations.

MySQL’s extensibility is more limited. User-defined functions exist but with more constraints.

Performance Characteristics

Neither database is categorically faster. Performance depends on workload, schema design, and configuration.

Read-Heavy Workloads

Both databases handle read-heavy workloads well with proper indexing. MySQL’s query optimizer is simpler but effective for common patterns. PostgreSQL’s optimizer is more sophisticated, which helps complex queries but occasionally produces suboptimal plans for simple ones.

For simple CRUD operations, performance differences are typically negligible with proper configuration.

Write-Heavy Workloads

PostgreSQL uses MVCC (Multi-Version Concurrency Control) that creates new row versions for updates. This means writes never block reads, but it requires periodic vacuuming to reclaim space from old row versions.

MySQL with InnoDB also uses MVCC but handles row versions differently. Its purge process is generally less intrusive than PostgreSQL’s vacuum.

For write-heavy workloads, both require attention to maintenance processes. PostgreSQL’s autovacuum needs tuning for high-write tables; MySQL’s purge lag needs monitoring.

Complex Queries

PostgreSQL generally handles complex queries better: multi-way joins, subqueries, aggregations, and analytical queries. Its query planner considers more execution strategies and its execution engine handles complex operations efficiently.

For applications with complex reporting or analytical requirements alongside OLTP workloads, PostgreSQL often performs better.

Connection Handling

MySQL handles many concurrent connections more gracefully out of the box. PostgreSQL’s connection model (one process per connection) consumes more memory and scales less well to thousands of connections.

PostgreSQL deployments typically use connection poolers (PgBouncer, PgPool) for high-connection scenarios. MySQL can often handle the same scenarios without pooling.

Replication and High Availability

Both databases support replication, but with different approaches.

PostgreSQL Replication

PostgreSQL’s native streaming replication is straightforward: a primary streams WAL (write-ahead log) records to replicas. Replicas can serve read queries while replicating.

Replication is physical (block-level), which is reliable but inflexible. You can’t replicate a subset of tables or transform data during replication.

Logical replication (replicating row changes rather than blocks) arrived in PostgreSQL 10, enabling more flexible replication scenarios.

MySQL Replication

MySQL offers multiple replication methods: traditional statement-based, row-based, and mixed. Row-based is generally preferred for reliability.

MySQL replication is more flexible: you can replicate specific databases or tables, and replicas can have different schemas than the primary.

However, MySQL replication has historically had more edge cases and failure modes. Careful monitoring is essential.

High Availability

Both databases require additional tooling for automatic failover. PostgreSQL typically uses Patroni, repmgr, or cloud provider solutions. MySQL uses MySQL Group Replication, Orchestrator, or cloud solutions.

Neither provides true automatic high availability out of the box. Plan for additional infrastructure.

Operational Considerations

Backup and Recovery

PostgreSQL’s pg_dump and pg_basebackup provide logical and physical backup options. Point-in-time recovery using WAL archiving is well-documented and reliable.

MySQL’s mysqldump provides logical backups; Percona XtraBackup provides physical backups. Point-in-time recovery is supported but requires more configuration.

Both have mature backup ecosystems. Neither is dramatically easier.

Upgrades

PostgreSQL major version upgrades traditionally required pg_dump/pg_restore or pg_upgrade, which could mean downtime for large databases. Logical replication now enables online upgrades between versions.

MySQL upgrades have historically been smoother, with in-place upgrades supported between many versions. However, MySQL 8.0 introduced breaking changes that complicated some upgrades.

Cloud Support

Both databases have excellent cloud support. AWS offers managed versions (RDS PostgreSQL, RDS MySQL, Aurora PostgreSQL, Aurora MySQL). Google and Azure provide similar offerings.

If you’re deploying to a cloud provider’s managed service, operational differences diminish—the provider handles many concerns.

Ecosystem and Community

MySQL Ecosystem

MySQL has a larger installed base and more hosting options. Nearly every hosting provider supports MySQL; fewer support PostgreSQL.

The MySQL ecosystem includes multiple forks and derivatives: MariaDB (community fork), Percona Server (enhanced MySQL), and various cloud variants. This creates choice but also fragmentation.

PostgreSQL Ecosystem

PostgreSQL has a smaller but technically sophisticated community. The extension ecosystem is strong: PostGIS for geospatial, TimescaleDB for time series, Citus for distributed tables.

PostgreSQL’s reputation for technical excellence attracts contributions from organizations with advanced requirements.

Making the Choice

Choose PostgreSQL When:

Choose MySQL When:

It Often Doesn’t Matter

For many applications, both databases work fine. The code you write matters more than the database underneath. Schema design, indexing, and query optimization affect performance more than database choice.

If your team knows MySQL, use MySQL. If they know PostgreSQL, use PostgreSQL. Don’t switch databases to chase theoretical benefits when practical familiarity has real value.

Conclusion

PostgreSQL and MySQL are both excellent choices for most applications. PostgreSQL offers more features, stricter correctness, and better handling of complex workloads. MySQL offers operational simplicity, broader hosting support, and good-enough performance for typical use cases.

The “best” choice depends on your requirements, team experience, and deployment environment. Make a decision, commit to it, and invest in understanding your chosen database deeply. That investment matters more than which database you chose.

Key Takeaways