---

1. Introduction

PostgreSQL and MySQL remain the two dominant open-source relational databases. While both can serve general-purpose workloads, they diverge significantly in:

• SQL correctness
• Data type richness
• JSON/document storage capabilities
• Query planner sophistication
• Transaction semantics
• Developer ergonomics
• Extensibility and advanced features

This document compares them specifically from the angle of software engineers writing their own SQL queries, using Java/Spring or Node.js backends.

---

2. SQL and Standards Compliance

PostgreSQL

PostgreSQL is considered one of the most SQL-compliant databases in the industry.
Benefits for hand-coded SQL:

• Strict typing → predictable behaviour
• Reliable error messages → easier debugging
• CTEs, window functions, lateral joins → powerful for analytics and complex logic
• Consistent date/time semantics
• Predictable boolean, null and collation behaviour
• Full text search built-in

Example of expressive SQL:

SELECT
    device_id,
    avg((payload->>'temp')::numeric) AS avg_temp,
    count(*) FILTER (WHERE payload @> '{"status":"error"}') AS error_count
FROM events
WHERE time > now() - interval '24 hours'
GROUP BY device_id;

MySQL

MySQL traditionally prioritises pragmatic performance over correctness.
Issues relevant to hand-crafted SQL:

• Typing is loose unless strict SQL modes are forced
• Silent truncations and implicit coercions possible
• CTE and window function support exists but is less performant
• Some complex queries require rewriting due to optimiser limitations
• Date/time semantics accept invalid values (0000-00-00, unless blocked)

Frequently encountered MySQL surprises:

• Comparisons are case-insensitive unless explicitly configured
• BOOLEAN is TINYINT(1)
• GROUP BY behaviour differs from ANSI unless ONLY_FULL_GROUP_BY is enabled
• String comparisons follow different collation rules than PostgreSQL

Conclusion:
If you write SQL by hand, PostgreSQL preserves intent; MySQL may accept the query but execute something subtly different.

---

3. Data Types Comparison

PostgreSQL: Rich, expressive type system

Native support for:

• JSONB and JSON
• Arrays (text[], int[], etc.)
• UUID
• Range types (tsrange, int4range)
• INET/CIDR/MACADDR
• Composite types
• HSTORE
• ENUMs (static)
• Full text search types (TSVECTOR)

These types directly improve SQL expressiveness and allow queries that would be impossible or awkward in MySQL.

Example (range types):

SELECT * FROM bookings
WHERE timerange && '[2024-01-02, 2024-01-03)';

MySQL: Simpler type system

Supports:

• JSON (binary-encoded)
• ENUM (integer lookup behind the scenes)
• Spatial types (less advanced than PostGIS)
• No arrays, no composite types, no range types

Many advanced Postgres queries must be rewritten into more verbose logic.

Conclusion:
PostgreSQL’s type system dramatically reduces application-side logic when hand-writing SQL.

---

4. JSON Column Type Support

PostgreSQL JSONB

PostgreSQL’s JSONB is a true document store:

• GIN indexes → extremely fast key/value searches
• Rich operators: @>, ->, #>, ?, ?|, ?&
• Expression indexes
• JSONPath support in v12+

Example of JSONB containment:

SELECT * FROM events
WHERE payload @> '{"source": "device"}';

Example of JSON index:

CREATE INDEX idx_payload_source
ON events USING gin ((payload->'source'));

MySQL JSON

MySQL also stores JSON, but:

• No general-purpose JSON indexing
• Indexing requires generated columns
• Limited operator support
• Complex queries perform significantly slower

Example indexing in MySQL:

ALTER TABLE events
ADD payload_source VARCHAR(50)
    AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.source'))) STORED;

CREATE INDEX idx_payload_source ON events (payload_source);

This is extra schema maintenance, and queries become verbose.

Example query:

SELECT * FROM events
WHERE JSON_EXTRACT(payload, '$.source') = 'device';

Conclusion:
PostgreSQL JSONB can replace MongoDB.
MySQL JSON cannot realistically do so for heavy workloads.

---

5. Partitioning

PostgreSQL

Declarative partitioning:

• Range, List, Hash
• Automatic pruning
• Per-partition indexing
• Native integration with constraints

Hand-written SQL uses standard table names; optimiser routes queries.

Example:

CREATE TABLE events (
    id BIGSERIAL,
    ts TIMESTAMPTZ,
    payload JSONB
) PARTITION BY RANGE (ts);

MySQL

Partitioning exists but is less advanced:

• More restrictions
• Weaker pruning
• Some operations require full table rebuilds
• Foreign keys not allowed in partitioned tables until recently (still limited)

Conclusion:
PostgreSQL partitioning is mature and predictable for large append-only datasets (logs, telemetry, events).

---

6. Query Planner & Execution

PostgreSQL

The planner is exceptionally advanced:

• Multiple join strategies
• Cost-based decisions
• Excellent support for subqueries and CTEs
• JSONB indexes used reliably
• Predictable execution plans

Optimal for hand-coded SQL where developer intent matters.

MySQL

The optimiser is simpler:

• Many subqueries rewritten manually by developers
• CTEs are sometimes slower than equivalent subqueries
• JSON functions rarely use indexes
• Complex joins must be simplified manually

Conclusion:
PostgreSQL runs high-level SQL naturally.
MySQL often requires query rewrites for performance.

---

7. Transaction Semantics

Feature	PostgreSQL	MySQL (InnoDB)
MVCC	True MVCC, no read locks	MVCC but uses gap locks
Serializable isolation	Correct	Emulated, may deadlock
Repeatable Read	ANSI standard	MySQL-specific semantics
Long transactions	Safe	Can cause contention
Advisory locks	Yes	Limited

For Java/Spring or Node.js, PostgreSQL’s isolation levels produce fewer deadlocks and fewer surprises.

---

8. Developer Experience (Java / Spring Boot)

PostgreSQL

• JDBC + R2DBC drivers very mature
• Works perfectly with Flyway / Liquibase
• Stored functions in SQL or PL/pgSQL
• JSONB support via custom mappers or Hibernate Types
• Rich SQL encourages writing logic inside queries
• Sequence-based IDs allow high insert throughput with batching

MySQL

• JDBC stable
• Identity column behaviour reduces batching efficiency
• JSON requires manual parsing and indexing
• Some SQL features (window functions, CTEs) slower or limited
• More boilerplate required in Java code for JSON fields

---

9. Developer Experience (Node.js)

PostgreSQL

• First-class support in pg, Prisma, Knex, Drizzle, Sequelize
• JSONB mapped cleanly in most ORMs
• More expressive SQL → fewer round trips
• Ideal for microservices where payloads are JSON

MySQL

• Good support in mysql2, Prisma, Sequelize
• JSON support is functional but weak
• Devs usually avoid MySQL JSON due to indexing complexity
• Requires more manual SQL optimisation

---

10. Migration Difficulty

MySQL → PostgreSQL

Moderate difficulty due to:

• Differences in implicit casting
• Need to fix invalid MySQL dates
• ENUM semantics differ
• JSON operators differ entirely
• AUTO_INCREMENT → SEQUENCE mapping
• Case sensitivity differences

Hand-written SQL must be reviewed and adapted.

PostgreSQL → MySQL

Often difficult or impossible without losing features:

• No JSONB containment operators
• No GIN indexing
• No arrays
• No range types
• No CTE optimisation
• Must rewrite MANY SQL features

---

11. Do PostgreSQL or MySQL Remove the Need for MongoDB?

PostgreSQL

PostgreSQL’s JSONB + indexes + rich operators allow it to serve as:

• Document database
• Relational database
• Hybrid system

For most applications, PostgreSQL JSONB eliminates the need for MongoDB.

This is especially true for:

• Event payloads
• Telemetry
• Config documents
• User profiles
• Flexible schemas

MySQL

MySQL JSON is usable but cannot match MongoDB:

• No expressive operators
• No universal JSON indexing
• Slower complex queries
• More verbose SQL

If your application stores dynamic JSON-heavy data, MySQL does not remove the need for MongoDB.

---

12. Summary Table

Category	PostgreSQL	MySQL
SQL Compliance	⭐⭐⭐⭐⭐	⭐⭐⭐
JSON Support	⭐⭐⭐⭐⭐ (JSONB + GIN)	⭐⭐ (functional but limited)
Complex Query Capability	⭐⭐⭐⭐⭐	⭐⭐⭐
Transactions & Concurrency	⭐⭐⭐⭐⭐	⭐⭐⭐
Developer Ergonomics (hand-written SQL)	⭐⭐⭐⭐⭐	⭐⭐⭐
Java/Spring Boot Integration	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
Node.js Integration	⭐⭐⭐⭐⭐	⭐⭐⭐
Partitioning	⭐⭐⭐⭐	⭐⭐
Extensions (PostGIS, pgvector, etc.)	⭐⭐⭐⭐⭐	⭐
MongoDB Replacement	Yes, often	Rarely

---

13. Final Recommendation

For teams writing custom SQL in Java/Spring Boot or Node.js, and building systems that rely on:

• JSON documents
• Analytical queries
• Transactional consistency
• High data quality
• Flexible schemas
• Clean developer ergonomics

PostgreSQL is the superior choice.

MySQL remains a valid option for:

• Simpler CRUD workloads
• Very high read throughput
• Legacy systems
• Environments where MySQL is already standardised

---