# Relational database rules — MySQL and PostgreSQL

Applies when: the data outlives the code. Schema mistakes are the expensive kind, because
by the time they hurt there are millions of rows sitting on top of them.

## Schema and data integrity

### Money is never a float

`FLOAT` and `DOUBLE` are binary approximations. `0.1 + 0.2` is not `0.3`, and rounding on
output does not fix it, because the error is already in the stored value.

The failure mode is not a dramatic outage. It is an invoice total off by one cent, a
reconciliation report that never balances, and a finance team that stops trusting the
system. That distrust is permanent and costs more than the bug.

Use `DECIMAL(p,s)` / `NUMERIC(p,s)`, or integer minor units formatted on display. Pick one
per project and never mix them — a `DECIMAL` column joined against a cents column is a bug
waiting for a deadline. `DECIMAL` is exact and slower than an integer; for money that trade
is always worth it.

### Choose the type, do not accept the default

- **`VARCHAR(255)` is a habit, not a decision** — it is the length that fitted an old index
  limit. An ISO country code is `CHAR(2)`. The type is documentation the database enforces.
- **Timestamps need a stated timezone policy.** PostgreSQL: `timestamptz`, always;
  `timestamp` stores wall-clock text and silently means different instants to different
  readers. MySQL: `DATETIME` stores no zone, `TIMESTAMP` converts via the session timezone
  and its range ends in 2038.
- **MySQL charset: `utf8mb4`, never `utf8`.** Legacy `utf8` is three bytes and cannot hold
  emoji or several CJK characters. The failure mode is truncation or an insert error, found
  by a user, in production.
- **`ENUM` is a schema change waiting to happen.** If the set moves with business rules
  rather than with code, it is a lookup table.

### NOT NULL is the default you should have to argue against

A nullable column is a promise that every consumer handles the null. They will not. `NULL`
propagates through arithmetic, drops rows from inner joins, and compares equal to nothing —
including itself, so `WHERE status != 'active'` silently omits every `NULL` row.

Add `NOT NULL` unless "unknown" is a real state distinct from any value the column can
hold. "We do not have this yet" usually means the column belongs on another table.

### Foreign keys

Without them you get orphans — not maybe, eventually and certainly. A delete that missed a
child table, a job that failed halfway through a cleanup, a manual fix applied at 2am. Rows
end up pointing at IDs that no longer exist, found when a report returns too few rows.

- **MySQL InnoDB requires an index on the referencing column** and creates one if absent.
- **PostgreSQL does not** — it indexes the referenced side only. The failure mode is a
  `DELETE` on the parent scanning the whole child table to check the constraint, turning a
  routine delete into a multi-second lock. Index every FK column yourself.
- **MyISAM parses and silently ignores foreign keys.** On legacy MySQL, check the engine
  before assuming a declared constraint enforces anything.

### Constraints in the database beat checks in application code

The application check is `if (!exists) { insert; }`. Two requests run it at once, both see
"not exists", both insert. The window is microseconds wide and production traffic finds it
reliably. Application checks race; a `UNIQUE` constraint does not, because uniqueness is
decided by the same engine that serialises the write. The same holds for `CHECK`, `NOT
NULL` and foreign keys.

The database is the only place a rule is enforced against every writer — the app, the queue
worker, the one-off tinker command, the reporting tool, the console session.

Keep the application validation: it exists to produce a good error message, not to guarantee
the invariant. Catch the constraint violation and translate it. The narrow exception is a
rule needing data the database cannot see, or changing too often for a migration each time.
Those live in code, and are advisory.

## Indexes

### Composite column order is the whole game

An index on `(a, b, c)` serves `WHERE a`, `WHERE a AND b`, and all three. It does not serve
`WHERE b` or `WHERE c`. That is the leftmost-prefix rule, and it makes
`(tenant_id, created_at)` and `(created_at, tenant_id)` different tools, not stylistic
variants.

The rule of thumb: **equality columns first, then the range or sort column.** Filtering
`tenant_id = ?` and ordering by `created_at` wants `(tenant_id, created_at)`. Reverse it and
the engine can no longer satisfy the ordering from the index, so it reads matching rows and
sorts them — fine at 10k rows, falls over at 10M, with a `filesort` in the plan and no code
change to blame.

### Covering indexes

If the index holds every column the query touches, the engine answers from the index and
never reads the table. MySQL reports `Using index`; PostgreSQL reports `Index Only Scan`,
and has `INCLUDE (...)` since 11 for payload columns that are stored but not sorted on.

**PostgreSQL caveat:** an index-only scan still visits the heap for rows on pages not marked
all-visible. If autovacuum is behind, the index-only scan is not index-only.
`EXPLAIN (ANALYZE, BUFFERS)` shows this as `Heap Fetches`.

### When an index hurts

Every index is a second structure that every `INSERT`, `UPDATE` and `DELETE` must maintain.
Ten indexes on a hot write table means each write does eleven structures' worth of work,
plus WAL/redo, plus buffer pool pressure that evicts pages you needed.

The failure mode is diffuse and hard to attribute: write latency creeps up across the whole
table and nobody connects it to the index added last quarter to fix one report. Audit with
MySQL `sys.schema_unused_indexes` or PostgreSQL `pg_stat_user_indexes` where `idx_scan = 0`,
and drop redundant prefixes — `(a)` is dead weight if `(a, b)` exists.

### Low-cardinality columns

An index on a boolean splitting roughly 50/50 is close to useless. The engine estimates it
would read half the table via random lookups, correctly prefers a sequential scan, and
ignores it — so you pay the write cost for nothing.

Cardinality is not the real question; **selectivity for the query you actually run** is. An
`is_deleted` column true for 0.1% of rows is worth indexing for the query seeking deleted
rows. PostgreSQL: use a partial index, `CREATE INDEX ... WHERE deleted_at IS NULL`. MySQL
has no partial indexes — put the low-cardinality column *first* in a composite
(`(status, created_at)`) so it partitions the index, or use a functional index (8.0.13+)
over a generated column.

### Reading EXPLAIN

Both engines lie a little: the plan is what the optimiser *intends*, from statistics that
may be stale.

- **PostgreSQL:** `EXPLAIN (ANALYZE, BUFFERS)` executes the query and reports real numbers.
  Compare estimated `rows` against actual — an estimate off by orders of magnitude means
  stale statistics or a correlation the planner cannot see, and every join choice above that
  node rests on the wrong number. `ANALYZE` runs the query, so wrap writes in a transaction
  you roll back.
- **MySQL:** `EXPLAIN` estimates only; `EXPLAIN ANALYZE` (8.0.18+) executes. In the tabular
  output `type: ALL` is a full scan, `rows` is an estimate, and `Extra` carries the signal —
  `Using filesort` and `Using temporary` are the two worth reacting to. `EXPLAIN
  FORMAT=JSON` exposes costs the table hides.

Read plans bottom-up and find the node where the row count explodes. That node is the
problem; everything above it is a symptom.

## Migration safety on large tables

"Large" starts where a full rewrite exceeds your deploy window — workload-dependent, not
row-count-dependent. Treat any table you cannot afford to rewrite as large.

### The lock you did not think about

Both engines queue lock requests. On PostgreSQL an `ALTER TABLE` needing `ACCESS EXCLUSIVE`
waits behind any open transaction touching the table, and while it waits every subsequent
query queues behind *it*. The DDL might take 2ms; the outage lasts as long as the
idle-in-transaction session that blocked it. MySQL has the same shape via metadata locks.

Set a timeout so the migration fails instead of taking the table offline:
`SET lock_timeout = '3s'` (PostgreSQL) or `SET lock_wait_timeout = 3` (MySQL), then retry.
A failed migration you can rerun beats a queue of blocked queries.

### MySQL: adding a column with NOT NULL and a default

This is where version matters most, and the common advice is out of date.

- **8.0.12+:** `ADD COLUMN`, including with a `DEFAULT`, is `ALGORITHM=INSTANT` by default.
  Metadata-only, no rewrite, effectively free at any table size.
- **5.6 / 5.7 / 8.0.11 and earlier:** `ADD COLUMN` is `ALGORITHM=INPLACE`. Concurrent DML is
  permitted, but **the whole table is rebuilt** — a full disk copy needing free space equal
  to the table, hours on a large one, and a replica falling behind throughout.
- **Before 5.6:** `ALGORITHM=COPY`, table locked against writes throughout.

Two INSTANT caveats: a table is limited to **64 row versions** and each instant add or drop
consumes one — exceed it and you get `ERROR 4092` plus a forced rebuild. Instant `DROP
COLUMN`, and adding a column at an arbitrary position, arrived in **8.0.29**; before that,
instant adds went to the end of the row only.

State `ALGORITHM=INSTANT` explicitly on MySQL 8. If the operation cannot be done instantly
you get an error rather than a surprise four-hour rebuild.

### PostgreSQL: adding a column with NOT NULL and a default

- **11+:** `ADD COLUMN ... NOT NULL DEFAULT <non-volatile expression>` stores the default in
  the catalog and does **not** rewrite the table. Fast at any size.
- **10 and earlier:** the same statement rewrites every row under `ACCESS EXCLUSIVE`. The
  table is unavailable for reads and writes for the duration.
- **Still rewrites on any version:** a volatile default such as `random()` or
  `clock_timestamp()`, a stored generated column, or an identity column.

### Operations to treat as dangerous on both engines

- **Changing a column type.** MySQL: `CHANGE COLUMN` to a different type is `ALGORITHM=COPY`
  and blocks concurrent DML. PostgreSQL: `ALTER COLUMN TYPE` rewrites the table and its
  indexes under `ACCESS EXCLUSIVE`. The narrow PostgreSQL exception is a binary-coercible
  change (`varchar(50)` → `varchar(100)`, `varchar` ↔ `text` with no collation change).
- **Adding an index.** MySQL 5.6+ builds secondary indexes in place with concurrent DML
  allowed. PostgreSQL's plain `CREATE INDEX` takes a `SHARE` lock — reads continue, **writes
  block** for the whole build. Use `CREATE INDEX CONCURRENTLY`: two table passes, cannot run
  inside a transaction, and on failure leaves an `INVALID` index to drop and rebuild. Laravel
  wraps migrations in a transaction on PostgreSQL, so that migration needs
  `public $withinTransaction = false;`.
- **Adding a foreign key or CHECK constraint in PostgreSQL.** Two steps: `ADD CONSTRAINT ...
  NOT VALID` (no scan, brief lock), then `VALIDATE CONSTRAINT` separately — validation takes
  only `SHARE UPDATE EXCLUSIVE` and does not block writes.
- **`SET NOT NULL` in PostgreSQL** scans the whole table under `ACCESS EXCLUSIVE`. On 12+,
  add `CHECK (col IS NOT NULL) NOT VALID`, validate it, then `SET NOT NULL` — the planner
  proves the constraint and skips the scan.

For rewrites you cannot avoid on MySQL, use `gh-ost` or `pt-online-schema-change` rather
than inventing your own copy-and-swap.

### Expand and contract

Never change a column in the same deploy that changes the code reading it. Old and new
versions coexist during any rolling deploy, and a migration landing before the last old
container drains will break it. Four deploys, in order:

1. **Expand.** Add the new column, nullable, no constraint. Nothing reads it.
2. **Backfill and dual-write.** The application writes both. Backfill history in batches
   with a pause between them — a single `UPDATE` over 10M rows is one transaction, one
   enormous lock, and on PostgreSQL a bloat event autovacuum chases for hours.
3. **Migrate reads.** Switch reads over, then add `NOT NULL` and constraints now that the
   data is known good.
4. **Contract.** Drop the old column, once nothing has referenced it for a full deploy cycle.

Four times the deploys, and not negotiable on a table that matters. Renaming a column is
metadata-only on both engines and still needs this dance — the risk is never the lock, it is
the code that still names the old column.

## Choosing between them

Both are excellent. Most applications would succeed on either, and the team's operational
familiarity outweighs every item below. The real differences:

**PostgreSQL is genuinely better at:**

- **Transactional DDL.** A failed migration rolls back completely. MySQL has none, so a
  migration failing on statement four of six leaves the schema half-changed and someone
  reconciling by hand.
- **Index expressiveness.** Partial indexes, expression indexes, GIN/GiST, `INCLUDE`
  columns — partial indexes alone remove a class of MySQL workarounds.
- **Data types.** Arrays, ranges, `inet`, native `uuid`, `JSONB` with indexable containment.
  PostGIS has no equivalent.
- **Correctness by default.** Strict typing, no silent coercions, `CHECK` constraints
  enforced since forever — MySQL only began enforcing them in 8.0.16.

**MySQL is genuinely better at:**

- **Connection handling under load.** Thread-per-connection against PostgreSQL's
  process-per-connection. PostgreSQL past a few hundred connections needs PgBouncer — an
  extra component to run and reason about.
- **Online schema change tooling.** `gh-ost` and `pt-online-schema-change` are mature and
  battle-tested with no equally established PostgreSQL counterpart.
- **Operational simplicity.** Replication is easier to set up and explain. No autovacuum, no
  transaction-ID wraparound, no bloat. PostgreSQL's MVCC writes a new row version on every
  `UPDATE`, and a hot update path needs vacuum tuning nobody thinks about until it bites.
- **Clustered primary key.** InnoDB stores rows in PK order, so PK range scans are sequential
  reads. The flip side: secondary indexes store the PK, so a wide or random key (a UUIDv4)
  inflates every index and splits pages on insert. Use an ordered key.

The honest rule: **if you need what PostgreSQL uniquely offers — partial indexes, JSONB at
scale, geospatial, transactional DDL — take it and budget for autovacuum and connection
pooling. Otherwise MySQL will not be the reason your project struggles.**

The wrong reason to pick either is that it is the one you have opinions about. The right
reason is which one your team can operate at 3am.

---
MIT licensed. Written by Smit Desai — <https://laravel.org.in>