Writing / 2016
Postgres vs MySQL in 2016: A Practical Comparison
A grounded look at PostgreSQL and MySQL as of April 2016, focusing on integrity, query power, and operational tradeoffs rather than benchmark hype.
Pick Postgres. If your workload is dead-simple CRUD and your team already bleeds MySQL, fine, stay there. For everything else in 2016, PostgreSQL gives you more database and fewer workarounds.
The data-ingestion system I’m working on runs on PostgreSQL: JSONB documents, full-text search across thousands of sources, and strict schema enforcement. I evaluated MySQL seriously. Postgres won on every axis that matters to me.
That doesn’t make MySQL bad. It makes the decision obvious for my workload. Here is how I see the tradeoffs.
The comparison
| Capability | PostgreSQL | MySQL (InnoDB, 5.7) |
|---|---|---|
| Data integrity | Strict by default. Type violations, overflows, and constraint breaches are errors. CHECK constraints enforced. | Strict mode is on by default only since 5.7. Older installs and many hosted configs still run lenient and silently truncate or coerce. CHECK constraints parsed but not enforced. |
| Transactional DDL | Yes. Failed migrations roll back cleanly. | No. DDL auto-commits. A failed migration leaves you half-changed. |
| JSONB | First-class. GIN-indexed, queryable with operators, fast. | Native JSON type since 5.7 (binary storage), but no direct indexing. Indexing a field means a generated column. |
| Full-text search | Built in. Dictionaries, ranking, language support. Good enough to skip Elasticsearch for many cases. | Basic keyword matching. Serviceable for simple search, but you will add Solr or Elastic quickly. |
| Window functions | Yes, mature. | No, and not in any shipping release. Analytics queries become subquery nightmares. |
| CTEs | Yes. Recursive CTEs too. | No. Same story as window functions. |
| Custom types/operators | Yes. You can build domain-specific behavior inside the database. | Limited UDFs. No custom operators or types. |
| Concurrency model | MVCC with new row versions. Requires vacuum. | MVCC via undo logs. Purge is less visible operationally. |
| Connection handling | Process-per-connection. Needs PgBouncer at scale. | Thread-per-connection. Handles high connection counts more easily out of the box. |
| Replication | Streaming (physical). Reliable, simple, but replicates the whole cluster. Logical replication is third-party in 2016. | Row-based, statement-based, or mixed. More flexible for partial replication. More edge cases. |
| Ecosystem/hosting | Smaller managed ecosystem. RDS supports it well. Fewer one-click options. | Everywhere. Every cheap host, every managed platform. Largest install base. |
| Upgrades | Major version upgrades need planning. pg_upgrade helps but it isn’t seamless. | Generally smoother in-place upgrades. |
Where Postgres pulls ahead
Correctness is the default. I don’t want my database silently truncating a decimal or accepting a string where an integer belongs. Postgres refuses bad data. MySQL only turned strict mode on by default in 5.7, and plenty of existing servers and hosted configs still let bad data through. For a system that ingests data from thousands of sources, a strict database is the minimum.
JSONB changes what you can do. We store semi-structured events alongside relational data. Postgres lets me index into JSONB, query nested fields, and join it with relational tables in one query. MySQL 5.7’s new JSON type narrows the gap, but indexing a nested field means adding a generated column for each path, and there is no equivalent of a GIN index over the whole document.
Full-text search removes a dependency. We search across document text from every source we ingest. Postgres full-text search with tsvector, dictionaries, and ranking handles this without bolting on a separate search cluster. One fewer service to operate, monitor, and keep in sync.
Window functions and CTEs aren’t optional. If you do any reporting or analytics, you need them. MySQL not having them in 2016 means your choices are ugly subqueries, dumping data into a separate analytics tool, or doing the math in application code. Postgres just does it.
Where MySQL wins
I’ll give MySQL its due.
Connection scaling. Postgres forks a process per connection. At a few hundred connections, memory adds up fast and you need a pooler. MySQL handles thousands of threads without breaking a sweat. If your architecture has many direct database connections and you don’t want to manage PgBouncer, that matters.
Operational simplicity for upgrades. MySQL major version upgrades tend to be less painful. Postgres upgrades have gotten better, but they still require more planning and occasionally downtime.
Ubiquity. MySQL is everywhere. Every shared host, every tutorial, every legacy system. If you’re inheriting a MySQL codebase and the schema is simple, migrating to Postgres just because you prefer it is a waste of time. Use what is there.
My decision framework
Three questions:
Does your data need to be correct, or just present? If correctness matters (financial data, health records, billing), Postgres. Its strictness is protection.
Do you need more than basic SELECT/INSERT/UPDATE? If you need JSONB, full-text search, window functions, CTEs, or custom types, Postgres gives you those today. MySQL will make you bolt on external tools or wait for features that aren’t shipping in 2016.
Is your team already deep in MySQL? Then stay. Database expertise matters more than database features. A well-tuned MySQL with a team that knows it will outperform a poorly operated Postgres every time.
My pick for 2016
Most of the “Postgres vs MySQL” content online bends over backward to be balanced. I won’t. For my workloads (high-volume ingestion, mixed relational and document storage, search, reporting), Postgres is the obvious choice.
MySQL is a fine database for simpler workloads and teams that know it well. But if you’re starting fresh in 2016 and your needs are anything beyond basic CRUD, pick Postgres. You will thank yourself when the first complex query lands and you don’t have to rewrite it as three subqueries and an application-side join.