Part IV — PostgreSQL and MySQL in Practice

Chapter 17. PostgreSQL and MySQL: A Comparative Study

Chapters 14–16 treated the platforms one at a time; this chapter puts them side by side and keeps them there. Comparison is not trivia — it is the skill that lets you read either codebase, port between them knowingly, argue a platform choice with evidence, and recognize when a bug you are chasing is dialect rather than logic. Every difference cataloged here has already appeared in its home chapter; this chapter assembles the ledger, then closes Part IV with the migration method that uses it.

The book's two-platform strategy earns its payoff here: side-by-side presentation (the TOC's own phrase) turns feature lists into judgment.

After studying this chapter you will be able to:

• Compare the platforms' architectures, processes, memory models, and configuration cultures.
• Translate SQL spellings between dialects on sight.
• Map data types and identifier generators across platforms.
• Translate string, date, and JSON operations.
• State constraint and referential-action differences, including history.
• Compare transaction models, defaults, and DDL semantics.
• Explain heap-plus-indexes versus clustered-PK storage and its optimization consequences.
• Compare JSON and specialized-type support.
• Map each platform's backup tooling to recovery requirements.
• Run a disciplined migration assessment.

17.1 Architecture and configuration differences

The honest summary: PostgreSQL's model buys robustness (a crashed backend is one dead session; the server survives) at connection cost, which modern deployment answers with poolers (Chapter 23) — while MySQL's threads trade isolation for cheap connections. Neither is a reason to choose; both are reasons to pool.

17.2 SQL compatibility and dialect differences

The Chapter 13 table, consolidated with the additions from Chapters 15–16:

Both are relationally complete, standard-first implementations; the divergence is surface vocabulary plus a few mechanism gaps — which is precisely why Chapter 13's dialect layer discipline (confine, catalog, test) works.

17.3 Data types and generated identifiers

Two mappings that decide migrations: TIMESTAMP means different things (zone-stored vs zone-converted — map PostgreSQL timestamptz to MySQL TIMESTAMP only within its range, else DATETIME + explicit zone discipline), and boolean becomes TINYINT(1) with a translation shim at the edge (a view or the driver's dialect setting).

17.4 String and date/time functions

The pattern is consistent: same capabilities, different spellings — except date truncation, where MySQL has no date_trunc and the format-and-cast idiom is the portable bridge. Any migration runs this table as a find-and-replace pass over the query layer; Appendix I prints it expanded.

17.5 Constraints and referential actions

The behavioral gap to memorize is deferral: PostgreSQL's deferred constraints enable in-transaction choreography MySQL simply cannot express — MySQL scripts do it with statement ordering or staging tables. The historical CHECK gap is nearly closed by 8.0.16 but still bites migrations into older fleets.

17.6 Transactions and isolation levels

Two headline differences. Default isolation: PostgreSQL's READ COMMITTED means two reads in one transaction can see committed changes from others; MySQL's REPEATABLE READ means the transaction sees the snapshot it started with (Chapter 18 demonstrates both behaviors in experiments). Applications written on one default and moved to the other change observable behavior without any code change — the quietest migration hazard in this chapter. DDL transactionality: PostgreSQL can roll back a failed migration script mid-flight; MySQL must compensate (restore, or design migrations as idempotent steps) — the reason Chapter 24's migration tooling treats platforms differently.

17.7 Indexing and query optimization

The storage-model difference drives everything else: in PostgreSQL a bad index costs a scan; in MySQL a primary key choice is a physical decision (Chapter 16), and the covering-index payoff is larger because it skips the PK detour. Both optimizers are cost-based and both respond to fresh statistics — the shared maintenance verb is literally ANALYZE on both platforms, a rare naming agreement.

17.8 JSON support and specialized data types

Both engines crossed the JSON Rubicon years ago; the philosophical difference remains: PostgreSQL leans on containment + inverted index (queries that ask "does this document have..."), MySQL on path extraction + function calls. Neither model is richer — they reward different query shapes, which is why Chapter 27's document-store comparison treats them as one family.

17.9 Backup and restoration methods

The conceptual mapping is exact: pg_dump ↔ mysqldump, WAL ↔ binary log, pg_basebackup ↔ physical copies — each platform couples logical dumps with a physical + log-replay story for point-in-time recovery. The full method is Chapter 21's subject; the comparative point is that neither platform lacks any recovery capability the other has. Differences are operational maturity and tooling ergonomics, not model.

17.10 Portability considerations when migrating databases

The ledger becomes a method. Migrating the university database PostgreSQL → MySQL (or back), in the order that works:

1. Assess the schema. Run the type map (17.3): NUMERIC→DECIMAL, text-length rules, boolean shim, timestamptz→TIMESTAMP/DATETIME decisions, identity→AUTO_INCREMENT, and the constraint audit (17.5) — CHECKs need 8.0.16+, deferrables need redesign.
2. Assess the query layer. Extract every query (they live in the Chapter 12/24 query library); run the dialect tables (17.2, 17.4) as transforms; flag the structural gaps — FULL JOIN, FILTER, DISTINCT ON, deferrable choreography — and design replacements once, in one module.
3. Assess the behavior defaults. Isolation default (17.6) — decide explicitly whether the application wants READ COMMITTED or REPEATABLE READ and set it on both platforms; NULL ordering and LIKE case sensitivity (Chapter 10) — pin them in the query layer, not the platform.
4. Move the data. CSV is the neutral format: \copy out (or COPY TO), LOAD DATA in — constraints enforced, counts verified (5, 6, 12, 10, 13, 28), EXCEPT-equality checked table by table.
5. Prove parity. Run the Chapter 11/13 dashboards on both; diff outputs. Differences in rounding or ordering are expected (AVG precision, NULL placement) and must be normalized deliberately — differences in rows are bugs.
6. Cut over with a rollback plan. Dual-run against both databases, compare at the application layer, and keep the old platform warm until the parity window closes — the same discipline Chapter 26 brings to replication.

The closing judgment, stated once for Part IV: PostgreSQL and MySQL are 95% the same model with different accents. The 5% — storage layout, transactional DDL, isolation defaults, deferrable constraints, the richer type system — is where all the thinking happens. Teams that treat the platforms as interchangeable are surprised by exactly this chapter's tables; teams that carry the ledger are never surprised at all.

Chapter Summary

• Architecturally: process-per-connection (isolation, pooling early) versus thread-per-connection (cheap, still pooled); databases-with-schemas versus database-as-schema; extensions versus engines.
• Dialect: the consolidated spelling table (concatenation, LIMIT, ILIKE, INTERSECT/EXCEPT, FULL JOIN, FILTER, DISTINCT ON, string_agg/GROUP_CONCAT, RETURNING, upsert, quoting) — surface vocabulary, same model.
• Types: NUMERIC/DECIMAL, text conventions, boolean shim, timestamptz/TIMESTAMP-DATETIME mapping, identity/AUTO_INCREMENT, PostgreSQL's natives unmatched by MySQL.
• Functions: same capabilities, different names — except date_trunc, the one real capability gap.
• Constraints: CHECK history, deferrable-versus-immediate, InnoDB-only FKs, NOT VALID versus online DDL.
• Transactions: READ COMMITTED versus REPEATABLE READ defaults change observable behavior silently; PostgreSQL's transactional DDL versus MySQL's implicit commit reshapes migration tooling.
• Optimization: heap-plus-indexes versus clustered PK — covering indexes pay differently; partial indexes unmatched; ANALYZE is the shared maintenance verb.
• JSON: containment+GIN versus extraction+functions; full-text and spatial both present, different lineages.
• Backups map exactly: pg_dump/mysqldump, WAL/binlog, basebackup/physical — capability parity, ergonomic differences.
• Migration method: schema → queries → behavior defaults → CSV move → parity proof → dual-run cutover; the 95/5 rule explains every surprise.

Key Terms

Laboratory Exercises

1. Build the dialect transformer: take your Chapter 12 queries.sql library and mechanically port every query to the other platform using Sections 17.2–17.4 — logging each transform in a two-column ledger (original → ported). Expected result: ten queries, most renamed functions only; two to four structural rewrites (FULL JOIN, FILTER, DISTINCT ON) — the ledger documents exactly where.
2. Isolation experiment: on both platforms, run Chapter 18's preview (two sessions, read-commit/see-changes versus repeatable-snapshot) with each platform's default, and record which behavior each showed without any settings. Expected result: PostgreSQL session sees committed changes within its transaction; MySQL default session does not — the silent behavioral difference demonstrated.
3. Storage-model proof: in MySQL, EXPLAIN a query answered by a secondary index and note the PK detour (rows via two ref/range steps or the key_len evidence); in PostgreSQL, show the same query's index scan pointing at the heap. Expected result: both plans correct; MySQL's shows the clustered-PK consequence, PostgreSQL's a plain index scan.
4. JSON parity: store the same course-catalog document on both platforms; answer "which departments offer CSE221" with @> on one and JSON_CONTAINS on the other; compare plans (GIN vs multi-valued/functional index). Expected result: same one row (CSE department); different mechanisms in the plans.
5. Type-migration drill: produce the MySQL DDL for the canonical schema from Appendix H with every type/constraint decision annotated (17.3, 17.5), then load it and verify the six counts. Expected result: a running MySQL university database — DECIMAL for NUMERIC, same CHECKs (8.0.16+), immediate FKs — counts 5, 6, 12, 10, 13, 28.
6. Full migration rehearsal: execute Section 17.10's six steps for university → MySQL (or the reverse), ending with the Chapter 11 dashboard diff and a written parity report of every rounding/ordering difference found. Expected result: identical rows everywhere; documented differences limited to AVG precision (3.4754545454545455 vs 3.4755) and NULL ordering — all normalized.

Review Questions and Exercises

1. Why does PostgreSQL's process model push deployments toward poolers earlier than MySQL's? Each connection is an OS process — memory and spawn cost; threads are cheaper, though pooling is still the right practice on both.
2. Translate: SELECT a \|\| b, LIMIT 10 OFFSET 5, and string_agg(t, ', ') into MySQL. *CONCAT(a, b); LIMIT 5, 10; GROUP_CONCAT(t SEPARATOR ', ').*
3. Which type mapping needs a policy decision, not a mechanical translation, and why? Timestamps with zones — PostgreSQL timestamptz stores UTC; MySQL TIMESTAMP converts but is range-limited, DATETIME stores literals — the semantic intent (event vs wall-clock) picks the target.
4. State the deferrable-constraint gap and the MySQL-side workaround. PostgreSQL can defer checks to COMMIT for choreography; MySQL checks immediately — restructure as statement order or staging tables.
5. An application moves PostgreSQL → MySQL with no code change. Name two behaviors that silently differ. Default isolation (READ COMMITTED → REPEATABLE READ semantics), NULL sort placement, LIKE case sensitivity, AVG decimal precision — any two.
6. Why is a random UUID primary key a graver decision in MySQL than PostgreSQL? Clustered PK: random keys scatter inserts through the B-tree; PostgreSQL's heap tolerates them (indexes point to rows, order-free).
7. What does Extra: Using index mean in MySQL, and why is its payoff structurally bigger than a covering index in PostgreSQL? The secondary index alone answered the query; in InnoDB it also skips the PK B-tree detour that every non-covering secondary hit pays.
8. Compare the two JSON models in one sentence each. PostgreSQL: containment queries (@>) accelerated by GIN inverted indexes; MySQL: path extraction (->) with function predicates and multi-valued indexes.
9. Map the backup triple across platforms. pg_dump ↔ mysqldump (logical); WAL ↔ binary log (replay); pg_basebackup ↔ clone/XtraBackup-style physical.
10. In the migration method, why prove parity with dashboards rather than row counts alone? Counts catch loss; dashboards catch semantics — rounding, ordering, NULL rendering — the differences that change what users see.
11. Which platform feature set in this chapter has no counterpart at all on the other side? PostgreSQL: deferrable constraints, partial indexes, native arrays/ranges/domains; MySQL: functional gaps of date_trunc — each list is short and stable.
12. State the 95/5 rule and its practical consequence for a team choosing between the platforms. 95% shared model, 5% real differences (storage, DDL transactions, isolation defaults, types); choose on the 5% and on operational fit — and carry the ledger either way.

Mini-Project

Write the platform decision dossier for a fictional product — a multi-tenant course-platform serving 200 universities, JSON API payloads per enrollment, transcript reporting, and a 5-year horizon — arguing PostgreSQL or MySQL with evidence drawn only from this chapter's tables and Part IV's chapters: storage model and PK strategy, the JSON query shapes you expect, transaction/DDL needs of your migration pipeline, the constraint choreography your registrar flows require, operational tooling, and hiring pool. Required sections: the 5% that decides (explicit, cited to sections), the 95% you will hold portable (the dialect-layer plan), the migration escape hatch (Section 17.10's method, applied to your schema sketch), and the honest counter-argument — the case for the other platform, steelmanned in one paragraph. Then swap dossiers with a classmate and write the rebuttal. The dossier is the final artifact of Part IV because it is the skill itself: platform judgment on evidence.