# SQL Design & Query Reference

> Source: <https://dev.to/andreimerlescu/sql-design-query-reference-67f>
> Published: 2026-07-27 16:34:27+00:00

I have professionally worked with **four different SQL database types** `PostgreSQL`

, `MySQL`

& `MariaDB`

(InnoDB), `MSSQL`

SQL Server 2008+ and `Oracle 19c`

+. Over the course of that time I have been asked:

And being an autodidact software engineer with an electrical engineering background in computer engineering, I would reference [The Library](https://dev.to/andreimerlescu/when-ai-creates-a-closed-source-world-2oin) to complete task A or task B or task C. Many junior engineers are in this boat as well. They may have the formal education that I lacked *not being in computer science as a major in college*, but it doesn't mean that I can't use resources at my disposal to learn and meet each of these requirements and where I've done it.

This document is *Claude's* response to my request for it to act as Staff SQL Architect and write a reference document for these **four database types** as a *knowledge transfer doc* for **junior engineers** who need a *quick reference* of these core concepts.

In all fairness, it's not wrong to have this handy. Instead of it being fragmented on Stack Overflow in the form of dozens of Questions and Answers - its been gathered through the LLM's that have [crossed the line](https://dev.to/andreimerlescu/codeberg-bans-cryptocurrency-and-llm-generated-code-projects-3aj0) with acceptable community behavior. So, before those models are *taken offline* 😅🤫🤣 I wanted to utilize this resource to help give back. It's a format and structure applicable to four distinct types of SQL databases that any developer should be comfortable with. My experience with SQL databases was extensive between 2003 and 2020. After, I became heavily involved with `MongoDB`

and **NoSQL** databases including *Redis* and *PostgreSQL*. Since 2020, I've been on the administration side of the database and less on the SQL query writing sided of the database. The [Apario Database](https://dev.to/andreimerlescu/how-i-processed-666k-pages-of-flattened-pdfs-into-a-full-text-search-engine-4db3) was custom written from the ground up for an [OPEX problem](https://dev.to/andreimerlescu/how-i-reduced-my-opex-by-995-using-go-3ff7).

If you find this document useful, please give it a 💜 before you bookmark it.

**How to read this doc:** Sections marked **[SAME]** behave identically on all four engines — learn the concept once and move on. Sections marked **[DIFFERS]** are where people get burned porting code or scaling up. Version numbers matter a lot here; when a feature is version-gated I say so, and you should still verify against the exact version in your environment before you rely on it.

Before you write anything, answer three questions:

`EXISTS`

). Enriching → join or window function.| Problem | Right pattern | Wrong pattern people reach for |
|---|---|---|
| "Customers who have ≥1 order" | `EXISTS` |
`JOIN` + `DISTINCT`
|
| "Customers with no orders" | `NOT EXISTS` |
`NOT IN` |
| "Each order plus its customer name" | `JOIN` |
correlated scalar subquery |
| "Total per customer" | `GROUP BY` |
correlated aggregate in SELECT |
| "Each order plus its customer's total" | window function | correlated aggregate in SELECT |
| "Latest order per customer (full row)" |
`ROW_NUMBER()` or lateral |
`MAX(date)` + self-join |
| "Top N per group" |
`ROW_NUMBER` /`RANK` /`DENSE_RANK`
|
correlated `COUNT` subquery |
| "Rows in A not in B, both large" | anti-join (`NOT EXISTS` ) |
`EXCEPT` /`MINUS` if you need dupes preserved |

`DISTINCT`

smell `SELECT DISTINCT`

in a query that joins to a child table almost always means you used a join where you wanted a semi-join. The join fans out, then `DISTINCT`

collapses it back — you paid for a sort/hash of the fanned-out set for nothing.

```
-- BAD: fan out 4M rows, then dedupe back to 200k
SELECT DISTINCT c.customer_id, c.name
FROM customer c JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'SHIPPED';

-- GOOD: optimizer stops at first match per customer
SELECT c.customer_id, c.name
FROM customer c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id AND o.status = 'SHIPPED');
```

There are legitimate `DISTINCT`

s (deduping a genuinely duplicated source). But treat every one as a thing you must justify in code review.

A junior writes a CTE thinking "this runs once and gets reused." That is engine-dependent and version-dependent.

| Engine | CTE behaviour |
|---|---|
PostgreSQL ≤ 11 |
Always materialized. Hard optimization fence — predicates do not push in. |
PostgreSQL 12+ |
Inlined if referenced once and not recursive/volatile; otherwise materialized. Force with `WITH x AS MATERIALIZED (...)` / `AS NOT MATERIALIZED` . |
SQL Server |
Effectively always inlined — a CTE referenced three times is executed three times. No hint to materialize. Use a `#temp` table when you need one-shot evaluation. |
Oracle |
Cost-based; may materialize into a temp segment. Hints: `/*+ MATERIALIZE */` , `/*+ INLINE */` . |
MySQL 8.0+ |
Materializes derived tables/CTEs in many cases; merge is possible. Historically weaker at pushing predicates into derived tables than the others. |

**The scale gotcha:** on SQL Server, a "clean" query with a CTE that scans a 500M-row fact table and is referenced in three branches of a `UNION ALL`

scans it three times. It looks elegant and it is a disaster. On PostgreSQL ≤ 11 the opposite failure: your `WHERE tenant_id = 42`

outside the CTE never reaches inside it, so you materialize the whole table first.

**Rule for the team:** if a CTE is referenced more than once *and* the underlying scan is expensive, on SQL Server/MySQL stage it into a temp table explicitly. Don't rely on the optimizer being clever.

Recursive syntax differs cosmetically: PostgreSQL and MySQL require `WITH RECURSIVE`

; SQL Server and Oracle use plain `WITH`

(Oracle additionally has the older `CONNECT BY`

, which is still often faster there for pure hierarchy walks and supports `LEVEL`

, `SYS_CONNECT_BY_PATH`

, `CONNECT_BY_ISLEAF`

).

`EXISTS`

/ `NOT EXISTS`

— and Why `NOT IN`

Keeps Hurting You

```
SELECT * FROM customer
WHERE customer_id NOT IN (SELECT customer_id FROM blacklist);
```

If **one** row in `blacklist.customer_id`

is NULL, this returns **zero rows**. Always. Silently.

Why: `x NOT IN (a, b, NULL)`

expands to `x <> a AND x <> b AND x <> NULL`

. That last term is `UNKNOWN`

, never `TRUE`

. So the whole conjunction can never be `TRUE`

.

`NOT EXISTS`

does not have this problem, because it tests row existence, not value comparison. **Default to NOT EXISTS.** If you must use

`NOT IN`

, the subquery column must be `NOT NULL`

| Engine | `NOT EXISTS` |
`NOT IN` |
|---|---|---|
Oracle |
Anti-join | Handles well — has a null-aware anti-join (`ANTI NA` ) operator (11g+). Roughly parity. |
SQL Server |
Anti semi-join | Usually anti-join too, but nullable columns add an extra probe/filter; plans get uglier. |
PostgreSQL |
Clean anti-join | Historically cannot convert to an anti-join when the column is nullable — degrades to a filtered subplan. Materially worse on large sets. |
MySQL 8.0.17+ |
Anti-join transformation available | Also transformed in 8.0.17+. Pre-8.0 this was a per-row dependent subquery — catastrophic.
|

So `NOT EXISTS`

is correct everywhere *and* the best-performing choice on the two engines where it matters most. There is no reason to write `NOT IN`

against a subquery. Against a short literal list (`status NOT IN ('A','B')`

) it's fine.

`LEFT JOIN ... WHERE right.pk IS NULL`

This is the third anti-join spelling. It is *semantically* safe (no NULL trap) but:

`WHERE`

instead of `ON`

, which silently converts your outer join to an inner join.Prefer `NOT EXISTS`

for readability; recognize the `LEFT JOIN / IS NULL`

form when you meet it.

`EXISTS`

vs `IN`

for the positive case All four modernly transform `IN (subquery)`

into a semi-join, so they're usually equivalent. Two notes:

`IN`

with a subquery returning NULLs is `NOT IN`

) — NULLs just never match.`IN (subquery)`

as a dependent subquery in many cases. If you maintain anything on 5.6/5.7, rewrite `IN`

to `EXISTS`

or a join. On 8.0+ the semijoin strategies (`FirstMatch`

, `LooseScan`

, `MaterializeLookup`

, `DuplicateWeedout`

) handle it.`EXISTS`

micro-details `SELECT 1`

vs `SELECT *`

vs `SELECT NULL`

inside `EXISTS`

makes zero difference on any of the four — the select list isn't evaluated. Don't argue about it in review. Do make sure the correlation predicate is indexed; that's what actually matters.

This is the single most common performance defect in junior-written SQL, and it's identical on all four engines. **[SAME]**

```
SELECT o.order_id,
       o.customer_id,
       (SELECT COUNT(*) FROM orders o2
        WHERE o2.customer_id = o.customer_id)      AS cust_order_count,
       (SELECT SUM(amount) FROM orders o3
        WHERE o3.customer_id = o.customer_id)      AS cust_total,
       (SELECT MAX(order_date) FROM orders o4
        WHERE o4.customer_id = o.customer_id)      AS cust_last_order
FROM orders o
WHERE o.order_date >= DATE '2026-01-01';
```

Three correlated subqueries = up to three extra index range scans **per output row**. At 2M output rows that's 6M extra scans. On a good day the optimizer decorrelates one of them. Don't rely on it.

```
SELECT order_id, customer_id,
       COUNT(*)        OVER (PARTITION BY customer_id) AS cust_order_count,
       SUM(amount)     OVER (PARTITION BY customer_id) AS cust_total,
       MAX(order_date) OVER (PARTITION BY customer_id) AS cust_last_order
FROM orders
WHERE order_date >= DATE '2026-01-01';
```

One scan, one sort/hash by `customer_id`

, all three aggregates computed together. Note the semantic change: the window is now over the *filtered* set. If you need totals over all history, you can't use this form — see Fix 2.

```
WITH cust_agg AS (
  SELECT customer_id,
         COUNT(*)        AS cust_order_count,
         SUM(amount)     AS cust_total,
         MAX(order_date) AS cust_last_order
  FROM orders
  GROUP BY customer_id
)
SELECT o.order_id, o.customer_id, a.cust_order_count, a.cust_total, a.cust_last_order
FROM orders o
JOIN cust_agg a ON a.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-01-01';
```

One aggregate pass over the child, one hash join. This is the workhorse. **Critical rule: aggregate before you join, not after.** Joining first fans out rows and then you're aggregating an inflated set — which is also how you get silently wrong

`SUM`

s.

```
-- WRONG
SELECT c.customer_id, SUM(o.amount), SUM(p.amount)
FROM customer c
JOIN orders o   ON o.customer_id = c.customer_id
JOIN payments p ON p.customer_id = c.customer_id
GROUP BY c.customer_id;
```

If a customer has 3 orders and 4 payments, you get 12 rows; `SUM(o.amount)`

is 4× too large and `SUM(p.amount)`

is 3× too large. This ships to production regularly because the numbers *look* plausible. Fix: aggregate each branch separately in its own subquery/CTE, then join the two aggregates. Or use `COUNT(DISTINCT ...)`

as a diagnostic to prove the fan-out to yourself.

"Give me each customer plus their most recent order's full details."

| Engine | Syntax |
|---|---|
| PostgreSQL | `LEFT JOIN LATERAL (...) t ON true` |
| Oracle 12c+ |
`OUTER APPLY (...)` or `LEFT JOIN LATERAL (...) ON 1=1`
|
| SQL Server | `OUTER APPLY (...)` |
| MySQL 8.0.14+ | `LEFT JOIN LATERAL (...) ON true` |

```
-- PostgreSQL / MySQL 8.0.14+
SELECT c.customer_id, c.name, lo.order_id, lo.order_date, lo.amount
FROM customer c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.order_date, o.amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC, o.order_id DESC
    LIMIT 1
) lo ON true;
```

(SQL Server/Oracle: `TOP 1`

/ `FETCH FIRST 1 ROWS ONLY`

and `OUTER APPLY`

.)

**When lateral beats ROW_NUMBER():** when the outer set is small and the inner table is huge and well-indexed. Lateral does N cheap index seeks.

`ROW_NUMBER()`

sorts the `ROW_NUMBER()`

wins — one sorted pass beats 5M seeks.That trade-off — **N seeks vs. one big sort** — is the mental model. Know your N.

```
SELECT customer_id,
       MAX(order_id) KEEP (DENSE_RANK LAST ORDER BY order_date, order_id) AS last_order_id,
       MAX(amount)   KEEP (DENSE_RANK LAST ORDER BY order_date, order_id) AS last_amount,
       SUM(amount) AS total
FROM orders GROUP BY customer_id;
```

`KEEP (DENSE_RANK FIRST/LAST)`

gets you "the value of column X from the row with the max/min of Y" inside a single `GROUP BY`

— no window pass, no self-join. There is no direct equivalent on the other three; PostgreSQL's closest is `DISTINCT ON`

or ordered-set aggregates.

`ON`

vs `WHERE`

on outer joins 

```
-- These are NOT the same query
FROM a LEFT JOIN b ON b.a_id = a.id AND b.status = 'X'   -- filters b before joining
FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.status = 'X' -- silently becomes an INNER JOIN
```

Any predicate on the null-supplying side placed in `WHERE`

eliminates the NULL-extended rows. If you genuinely want "no matching b," write `WHERE b.a_id IS NULL`

.

Wrapping an indexed column in a function kills the index. Universal.

```
WHERE UPPER(email) = 'A@B.COM'          -- no index use on email
WHERE order_date + 1 > SYSDATE          -- no index use
WHERE YEAR(order_date) = 2026           -- no index use
```

Remedies by engine:

| Engine | Expression/functional index | Case-insensitive strategy |
|---|---|---|
| PostgreSQL |
`CREATE INDEX ON t (upper(email))` — long supported |
`citext` type, or expression index |
| Oracle | Function-based indexes (8i+) | FBI on `UPPER()` , or `NLS_SORT` /`NLS_COMP` linguistic index |
| SQL Server | No true expression index — use a persisted computed column + index |
Column collation is usually CI already (`_CI_AS` ) so it's often a non-issue |
| MySQL 8.0.13+ | Functional indexes; before that, generated column + index | Default collations are CI (`utf8mb4_0900_ai_ci` ) — usually a non-issue |

**Two engine-specific date traps:**

`DATE`

always carries a time component. `WHERE order_date = DATE '2026-07-27'`

misses everything with a nonzero time. Use a half-open range `>= DATE '2026-07-27' AND < DATE '2026-07-28'`

, not `TRUNC()`

.`datetime`

(legacy) has ~3ms precision and rounds `.997`

→`.997`

, `.999`

→next second. `BETWEEN '2026-07-01' AND '2026-07-31 23:59:59.999'`

is a bug. Always use half-open ranges. This applies everywhere but bites hardest here.`NVARCHAR`

parameters against a `VARCHAR`

column forces `CONVERT_IMPLICIT`

on the `NVARCHAR`

wins. This is probably the #1 cause of "it's fast in SSMS but slow from the app." Match your parameter types to your column types.`utf8mb3_general_ci`

legacy table to `utf8mb4_0900_ai_ci`

new table) prevents index use on the join. Also, comparing a string column to a numeric literal converts the `VARCHAR2`

vs `NVARCHAR2`

, and comparing a `NUMBER`

column to a string literal. Oracle converts the character side to number, which is safe, but comparing a `VARCHAR2`

column to a number converts the column → scan.`bigint`

column vs `int`

parameter is fine while `text`

vs `varchar`

collation mismatches can still block index use in less common cases.`GROUP BY`

strictness | Engine | Rule |
|---|---|
| SQL Server, Oracle | Strict ANSI. Every non-aggregated select item must be in `GROUP BY` . |
| PostgreSQL | Strict, except it recognizes functional dependency on a grouped primary key — `GROUP BY c.customer_id` lets you select `c.name` . |
| MySQL |
`ONLY_FULL_GROUP_BY` is on by default since 5.7.5. Similar functional-dependency detection to PG. Legacy code with it disabled returns arbitrary values from arbitrary rows — a correctness bug, not a style issue. Never turn it off. |

`HAVING`

vs `WHERE`

`WHERE`

filters rows before grouping; `HAVING`

filters groups after. Putting a non-aggregate predicate in `HAVING`

is not *wrong* (all four will typically push it down) but it's misleading. Put row filters in `WHERE`

.

```
-- Works everywhere
SELECT customer_id,
       SUM(CASE WHEN status = 'SHIPPED'   THEN amount ELSE 0 END) AS shipped_amt,
       COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END)           AS cancelled_cnt
FROM orders GROUP BY customer_id;
```

PostgreSQL additionally has the cleaner ANSI `FILTER`

clause: `COUNT(*) FILTER (WHERE status = 'CANCELLED')`

. It's PG-only among these four. Use it in PG-only code; use `CASE`

in anything portable.

Note `COUNT(CASE WHEN ... THEN 1 END)`

— no `ELSE`

, so non-matches are NULL and `COUNT`

skips them. `COUNT(CASE WHEN ... THEN 1 ELSE 0 END)`

counts everything and is a classic bug.

| Operation | PG | MySQL | SQL Server | Oracle |
|---|---|---|---|---|
| Union, dedupe | `UNION` |
`UNION` |
`UNION` |
`UNION` |
| Union, keep dupes | `UNION ALL` |
`UNION ALL` |
`UNION ALL` |
`UNION ALL` |
| Difference | `EXCEPT` |
`EXCEPT` (8.0.31+) |
`EXCEPT` |
`MINUS` (`EXCEPT` added in 21c+) |
| Intersection | `INTERSECT` |
`INTERSECT` (8.0.31+) |
`INTERSECT` |
`INTERSECT` |

**Always ask whether you need UNION or UNION ALL.**

`UNION`

performs a full dedupe (sort or hash) over the combined result. If the branches are provably disjoint — different date ranges, different status values — `UNION ALL`

is free and `UNION`

costs you a sort of the entire set. On a 200M-row report this is the difference between 40 seconds and 8 minutes.Also: `EXCEPT`

/`MINUS`

deduplicate the left side. `NOT EXISTS`

does not. They are not interchangeable when duplicates are meaningful.

| Algorithm | PG | MySQL 8.0 | SQL Server | Oracle |
|---|---|---|---|---|
| Nested loop | ✅ | ✅ | ✅ | ✅ |
| Hash join | ✅ | ✅ (8.0.18+) | ✅ | ✅ |
| Merge join | ✅ | ❌ never
|
✅ | ✅ (sort-merge) |

**MySQL's gap is the important one.** Before 8.0.18 MySQL had *only* nested-loop variants (`BNL`

). A three-table analytical join over millions of rows that any other engine resolves with hash joins in seconds could run for hours on MySQL 5.7. If you're on MySQL and a large join is slow, check the version first — this is frequently the whole answer. And MySQL still has no merge join, so pre-sorted large-to-large joins don't get that option.

Practical consequence: **MySQL is a weaker analytical engine than the other three.** Design around it — pre-aggregate into summary tables rather than expecting the optimizer to rescue a 6-table star join.

| Engine | Basic windows | Full frames (`ROWS` /`RANGE` ) |
`GROUPS` / `EXCLUDE`
|
|---|---|---|---|
| Oracle | 8i (ancient, very mature) | ✅ |
`GROUPS` in 21c+ |
| SQL Server | 2005 (ranking only) | 2012+ |
❌ not supported |
| PostgreSQL | 8.4 | ✅ | ✅ (11+) |
| MySQL | 8.0 only |
✅ | ❌ |

`ROW_NUMBER`

, `RANK`

, `DENSE_RANK`

, `NTILE`

, `LAG`

, `LEAD`

, `FIRST_VALUE`

, `LAST_VALUE`

, `SUM/AVG/COUNT/MIN/MAX OVER`

are present and identical in semantics on all four modern versions. **[SAME]**

For values `100, 100, 90, 80`

:

| Function | Result | Use when |
|---|---|---|
`ROW_NUMBER()` |
1, 2, 3, 4 | You need exactly one row per group — deduplication, "latest record" |
`RANK()` |
1, 1, 3, 4 | Competition ranking; gaps after ties |
`DENSE_RANK()` |
1, 1, 2, 3 | "Top 3 distinct salary levels"; no gaps |

**The classic requirement mismatch:** "give me the top 3 earners per department." If two people tie for 3rd, do you want 3 rows or 4? `ROW_NUMBER`

gives 3 (arbitrarily dropping one — nondeterministic and a real bug). `RANK`

/`DENSE_RANK`

give 4. Ask the business. Then make the tiebreaker explicit either way.

**Always add a deterministic tiebreaker to ORDER BY inside the window.**

`ORDER BY order_date DESC`

on a table with same-day orders produces different results run to run. Write `ORDER BY order_date DESC, order_id DESC`

. This is the source of most "the report changed but the data didn't" tickets.

```
WITH ranked AS (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY natural_key
                            ORDER BY loaded_at DESC, id DESC) AS rn
  FROM staging t
)
SELECT * FROM ranked WHERE rn = 1;
```

The CTE wrapper is required — you cannot filter on a window function in `WHERE`

on any of these four, because windows are evaluated after `WHERE`

. **None of PostgreSQL, MySQL, SQL Server, or Oracle support QUALIFY** (that's Teradata/Snowflake/DuckDB). Expect to write the CTE.

PostgreSQL has a shorthand for the `rn = 1`

case:

```
SELECT DISTINCT ON (natural_key) *
FROM staging ORDER BY natural_key, loaded_at DESC, id DESC;
```

PG-only. Often faster than `ROW_NUMBER`

there.

When you write `OVER (PARTITION BY x ORDER BY y)`

with no frame clause, the implicit frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

.

`RANGE`

means **peer rows (ties in y) are all included at once**. So a "running total" over a day-grain column gives every row on the same date the

```
-- What you almost always meant:
SUM(amount) OVER (PARTITION BY customer_id
                  ORDER BY order_date, order_id
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
```

**SQL Server–specific performance angle:** in SQL Server the default `RANGE`

frame uses an **on-disk** worktable spool, while `ROWS`

uses an in-memory spool. Specifying `ROWS`

explicitly is frequently a 5–10× improvement on large partitions — even when the results would be identical. Make `ROWS ...`

mandatory in SQL Server code review.

`LAST_VALUE`

trap [SAME]:`LAST_VALUE(x) OVER (ORDER BY y)`

returns the current row's value, because the default frame ends at the current row. You need `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`

, or better, use `FIRST_VALUE`

with the order reversed.

PostgreSQL, MySQL 8.0, and Oracle support `WINDOW w AS (PARTITION BY ... ORDER BY ...)`

then `OVER w`

. **SQL Server does not** — you repeat the full `OVER`

clause. Minor, but it's a portability paper cut and a source of copy-paste inconsistency in SQL Server code.

`(partition_cols, order_cols)`

lets PostgreSQL, Oracle, and SQL Server skip the sort entirely. This is often the single highest-leverage index on a reporting table.`ORDER BY`

s = three sorts. Consolidate window definitions where you can.If you only need aggregates at the group grain, `GROUP BY`

is cheaper — it collapses rows; windows preserve them. Don't write `SELECT DISTINCT customer_id, SUM(x) OVER (PARTITION BY customer_id)`

; write `GROUP BY`

.

JSON columns are for **genuinely schemaless, sparse, or third-party-shaped payloads**: webhook bodies, per-tenant custom fields, audit snapshots, API response caching. They are *not* a substitute for columns.

The test: **if you filter, join, sort, or aggregate on it regularly, it should be a column.** Every engine's JSON indexing story is worse than a plain B-tree on a scalar column. You also lose type checking, NOT NULL, foreign keys, and check constraints.

Second rule: **do not use JSON as an EAV escape hatch.** "Just put the extra attributes in a JSON blob" is how you get a 400GB table where nobody knows what's in it and every query is a full scan.

| PostgreSQL | MySQL 8.0 | SQL Server | Oracle | |
|---|---|---|---|---|
| Native type |
`json` (text) / (binary)`jsonb` |
`JSON` (binary) |
`NVARCHAR(MAX)` + `ISJSON` check (native `json` type in SQL Server 2025) |
`JSON` type in 21c+; before that `CLOB` /`VARCHAR2` + `IS JSON`
|
| Extract scalar |
`->>` , `#>>` , `jsonb_path_query`
|
`->>` , `JSON_UNQUOTE(JSON_EXTRACT())` , `JSON_VALUE` (8.0.21+) |
`JSON_VALUE` |
`JSON_VALUE` , dot notation |
| Extract object/array |
`->` , `#>`
|
`->` |
`JSON_QUERY` |
`JSON_QUERY` |
| Containment test |
`@>` (indexed!) |
`JSON_CONTAINS` |
`JSON_PATH_EXISTS` (2022+) |
`JSON_EXISTS` |
| Shred to rows |
`jsonb_array_elements` , `JSON_TABLE` (17+) |
`JSON_TABLE` (8.0.4+) |
`OPENJSON` |
`JSON_TABLE` |
| Modify in place |
`jsonb_set` , ` |
`, ` -` |
`JSON_SET/INSERT/REPLACE/REMOVE` |
|
General index |
GIN on — indexes all keys/values at once`jsonb` |
❌ none | ❌ (until 2025's JSON indexes) |
JSON search index (`CREATE SEARCH INDEX ... FOR JSON` ) |
| Targeted index | B-tree on expression | Generated column + index; multi-valued index for arrays (8.0.17+) |
Persisted computed column + index | Function-based index on `JSON_VALUE`
|

`jsonb`

, essentially always
`json`

stores the raw text (preserves whitespace, key order, duplicate keys) and re-parses on every access. `jsonb`

is parsed binary — faster access, supports indexing and containment operators. Cost: slightly slower insert, key order not preserved, duplicate keys collapsed.

```
CREATE INDEX idx_doc_gin ON events USING GIN (payload jsonb_path_ops);
-- supports: WHERE payload @> '{"type":"purchase"}'

-- Narrower + smaller when you know the key:
CREATE INDEX idx_doc_type ON events ((payload->>'type'));
```

`jsonb_path_ops`

GIN indexes are substantially smaller and faster than the default `jsonb_ops`

but only support `@>`

/ `@?`

/ `@@`

, not key-existence (`?`

). Prefer it unless you need key-existence checks.

**PG gotchas at scale:** large `jsonb`

values get TOASTed (out-of-line, compressed), so `payload->>'x'`

on a 2KB document means detoast + decompress *per row*. Extracting one small field from a big document across millions of rows is far slower than people expect. Also, `jsonb_set`

rewrites the entire value — updating one key in a 100KB document writes 100KB and generates that much WAL.

You cannot `CREATE INDEX ON t (json_col->>'$.status')`

directly in the way you'd hope. Two supported routes:

```
-- Route 1: generated column (works 5.7+)
ALTER TABLE events
  ADD COLUMN evt_type VARCHAR(50)
    AS (JSON_UNQUOTE(JSON_EXTRACT(payload,'$.type'))) STORED,
  ADD INDEX idx_evt_type (evt_type);

-- Route 2: functional index (8.0.13+) — same thing, less clutter
ALTER TABLE events ADD INDEX idx_evt_type ((CAST(payload->>'$.type' AS CHAR(50))));

-- Route 3: multi-valued index for arrays (8.0.17+)
ALTER TABLE events ADD INDEX idx_tags ((CAST(payload->'$.tags' AS CHAR(40) ARRAY)));
-- used by: WHERE JSON_CONTAINS(payload->'$.tags', '"urgent"')
```

Multi-valued indexes are MySQL's one genuinely nice JSON feature — one index entry per array element, queryable via `MEMBER OF`

, `JSON_CONTAINS`

, `JSON_OVERLAPS`

.

**MySQL gotchas:** JSON columns can't be used in a primary key; generated columns must be `STORED`

(not `VIRTUAL`

) for some index types; partial in-place updates only happen under narrow conditions (`JSON_SET`

on same-or-smaller value) — otherwise MySQL rewrites the whole document and logs it whole to the binlog. Replication lag from JSON-heavy writes is a real production issue.

Through SQL Server 2022, JSON is `NVARCHAR(MAX)`

— there was no JSON type, no JSON index. You get it via:

```
ALTER TABLE Events ADD EventType AS
  JSON_VALUE(Payload, '$.type') PERSISTED;
CREATE INDEX IX_Events_Type ON Events (EventType);
```

`OPENJSON`

with an explicit `WITH`

schema is dramatically faster than the default key/value form, and gives you typed columns:

```
SELECT j.type, j.amount
FROM Events e
CROSS APPLY OPENJSON(e.Payload)
  WITH (type VARCHAR(50) '$.type', amount DECIMAL(18,2) '$.amount') j;
```

Add `CHECK (ISJSON(Payload) = 1)`

on the column or you *will* accumulate malformed rows.

SQL Server 2025 introduced a native `json`

type with binary storage and JSON indexes; if you're on it, prefer that over `NVARCHAR(MAX)`

. Confirm the specifics against the docs for your build — this is recent.

Oracle's JSON support (especially 19c+ and 21c/23ai) is arguably the most complete:

```
-- Constraint on pre-21c storage
ALTER TABLE events ADD CONSTRAINT ck_json CHECK (payload IS JSON);

-- Dot notation (requires table alias)
SELECT e.payload.type, e.payload.amount FROM events e;

-- General-purpose index over the whole document
CREATE SEARCH INDEX idx_payload ON events (payload) FOR JSON;

-- Targeted
CREATE INDEX idx_type ON events (JSON_VALUE(payload, '$.type'
  RETURNING VARCHAR2(50) ERROR ON ERROR NULL ON EMPTY));
```

The `RETURNING`

clause and error-handling clauses must match exactly between index definition and query predicate or the index isn't used — a common and frustrating gotcha.

Oracle 23ai adds **JSON Relational Duality Views**: relational tables underneath, JSON document API on top, with proper concurrency control. Genuinely the best answer to "the app team wants documents, I want normalization," if you're on that version.

If you must support more than one engine: use JSON as an **opaque payload** — store it, retrieve it whole, parse in the application. The moment you query *inside* it, you've committed to that engine. Extract the fields you query into real columns at write time.

`(a, b, c)`

serves predicates on `a`

, `(a,b)`

, `(a,b,c)`

. It does `b`

alone. All four can do a full index scan in that case, but that's not what you designed for.`WHERE tenant_id = ? AND status = ? AND created_at > ?`

, index `(tenant_id, status, created_at)`

. Putting `created_at`

before `status`

means the index can only seek to the range start and must filter the rest.| Engine | Table storage | Consequence |
|---|---|---|
MySQL/InnoDB |
Always clustered on the PK (or a hidden rowid if none). Secondary indexes store the PK value as the row pointer. |
A wide PK (e.g. `UUID CHAR(36)` ) is duplicated into every secondary index. Random PKs cause page splits on every insert. |
SQL Server |
Clustered index optional; without one the table is a heap. Nonclustered indexes point to the clustering key (or RID for heaps). |
Same PK-width amplification as InnoDB when clustered. Heaps suffer forwarded records on updates. |
PostgreSQL |
Always a heap. No clustered index. `CLUSTER` is a one-time reorganization that doesn't hold. |
Physical order drifts; correlation matters for range scan cost. Index-only scans need the visibility map to be current. |
Oracle |
Heap by default; Index-Organized Tables available for PK-clustered storage. |
IOTs are great for narrow lookup tables, less so for wide ones. |

**The PK choice consequence:** on MySQL and SQL Server, use a **narrow, monotonically increasing** clustering key. `BIGINT`

identity/auto_increment is the default correct answer. Random `UUID`

v4 as a clustered PK on InnoDB is a well-documented performance disaster — insert throughput collapses as the B-tree fragments and every secondary index carries 16–36 bytes per entry.

If you need UUIDs for distributed ID generation, use a **time-ordered** variant (UUIDv7, or SQL Server's `NEWSEQUENTIALID()`

, or ULID) stored as `BINARY(16)`

/ `uniqueidentifier`

/ `uuid`

— never as a 36-char string. PostgreSQL tolerates random UUIDs better (heap storage) but still suffers index bloat and worse cache locality.

| Feature | PG | MySQL 8.0 | SQL Server | Oracle |
|---|---|---|---|---|
| Partial / filtered index | ✅ `WHERE`
|
❌ | ✅ filtered index | ❌ (emulate: function-based index returning NULL — Oracle doesn't index all-NULL entries) |
| Covering non-key columns | ✅ `INCLUDE` (11+) |
❌ (add as key cols) | ✅ `INCLUDE`
|
❌ (add as key cols) |
| Expression / functional index | ✅ | ✅ (8.0.13+) | ⚠️ via persisted computed column | ✅ |
| Descending index | ✅ | ✅ (8.0+; earlier it was parsed and ignored) | ✅ | ✅ |
| Bitmap index | ❌ | ❌ | ⚠️ columnstore serves the use case | ✅ (DW only) |
| Columnstore | ⚠️ extensions | ❌ | ✅ (mature) | ✅ In-Memory Column Store (licensed) |
| Hash index | ✅ (limited) | ✅ MEMORY engine only | ✅ (in-memory OLTP) | ✅ hash cluster |
| GIN / inverted (arrays, JSON, FTS) | ✅ | ⚠️ multi-valued only | ❌ | ✅ search index |
| BRIN (huge, correlated tables) | ✅ | ❌ | ⚠️ columnstore segment elimination | ⚠️ zone maps (Exadata) |
| Invisible index (test before drop) | ⚠️ no | ✅ | ⚠️ via disable | ✅ |
| Online index build | ✅ `CONCURRENTLY`
|
✅ mostly | ✅ Enterprise only | ✅ `ONLINE` (EE) |

**Three engine-specific traps worth memorizing:**

`WHERE deleted_at IS NULL`

cannot use a single-column index on `deleted_at`

. Workaround: make it composite (`(deleted_at, id)`

) or use a function-based index. `''`

as NULL.`col = ''`

will silently return nothing.

```
-- PostgreSQL
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'PENDING';

-- SQL Server
CREATE INDEX IX_Orders_Pending ON Orders (CreatedAt)
WHERE Status = 'PENDING';
```

If 0.1% of a 500M-row table is pending, this index is ~500k entries instead of 500M. Enormous. **MySQL has no equivalent** — the standard workaround is a separate "work queue" table that rows are deleted from when processed. That's not a hack; on MySQL it's the right design.

**SQL Server filtered-index gotcha:** the plan only uses it if the parameter is provably within the filter. Parameterized queries (`WHERE Status = @s`

) often *won't* use a filtered index because the optimizer can't prove `@s = 'PENDING'`

at compile time. `OPTION (RECOMPILE)`

or a literal fixes it. This surprises people constantly.

| Engine | Mechanism | Main failure mode |
|---|---|---|
SQL Server |
Auto-update stats on ~20% change (or sqrt-based on 2016+). Plans cached per parameterized statement. |
Parameter sniffing — the first execution's parameter values shape the cached plan for everyone. A plan built for `tenant_id = <tiny tenant>` is then used for `<huge tenant>` . Remedies: `OPTION (RECOMPILE)` , `OPTIMIZE FOR` , Query Store forced plans, Automatic Tuning. |
Oracle |
Bind peeking + Adaptive Cursor Sharing + SQL Plan Baselines. | Adaptive features mostly help but can cause plan churn. Baselines/SQL Profiles are the stability tool. Stale stats on volatile tables still bite. |
PostgreSQL |
Autovacuum/autoanalyze. Prepared statements switch to a generic plan after 5 executions if it seems no worse. | Skewed data + generic plan = bad plan. `plan_cache_mode = force_custom_plan` is the escape hatch. Also: no cross-column statistics by default — use `CREATE STATISTICS` for correlated columns (`city` /`postcode` ). |
MySQL |
Simplest optimizer of the four. Histograms only since 8.0. No plan cache to speak of. | Weakest cardinality estimation; InnoDB index statistics are sampled (`innodb_stats_persistent_sample_pages` ) and can be badly wrong on large tables. Raise the sample pages on big tables. |

**Team practice:** on SQL Server, when someone says "the app is slow but it's fast when I run it in SSMS," your first hypothesis is parameter sniffing, second is implicit conversion from NVARCHAR parameters. Those two cover the majority of cases.

`LIMIT 20 OFFSET 500000`

(or `OFFSET ... FETCH`

, or `ROWNUM`

/`ROW_NUMBER`

filtering) makes the engine produce and discard 500,000 rows. Cost grows linearly with page number. Use **keyset (seek) pagination**:

```
SELECT ... FROM orders
WHERE (created_at, order_id) < (:last_created_at, :last_order_id)
ORDER BY created_at DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;
```

Row-value comparison works in PostgreSQL, MySQL, and Oracle. **SQL Server does not support row-value constructors in comparisons** — expand manually:

```
WHERE created_at < @last_created_at
   OR (created_at = @last_created_at AND order_id < @last_order_id)
```

(SQL Server may need an index hint or the rewritten `OR`

form to seek properly; check the plan.)

Get to **3NF/BCNF** and stay there unless you have a measured reason not to. Normalization gives you: one place to update each fact, constraints that actually work, smaller rows (more rows per page, better cache hit rate), and a schema that survives requirement changes.

Denormalization is a **performance optimization with a correctness cost**. You are trading guaranteed consistency for speed. Like all optimizations: measure first, and know how you'll maintain it.

Denormalize derived data freely with a rebuild path. Duplicate raw data only with a very clear write-path story.

**1. Immutable historical snapshots — not really denormalization at all.**

An order line must store `unit_price`

and `product_name`

**as of the transaction**. This is not a redundant copy of `product.price`

— it's a different fact ("what we charged") from a different point in time. Joining to `product`

for historical price is a *bug*, not an optimization. Snapshot anything that appears on a legal or financial document.

**2. Counter caches / rollups.**

`post.comment_count`

instead of `COUNT(*)`

on every page load. Worth it when read:write is heavily skewed.

*Gotcha at scale:* every comment insert updates the same post row. A hot post becomes a lock convoy. Mitigations: sharded counters (N rows per post, summed), async batch updates, or accept eventual consistency and recompute nightly.

**3. Precomputed aggregate/summary tables.**

Daily/hourly rollups for dashboards. Almost always the right answer for reporting on a large OLTP table. This is the highest-value denormalization pattern in practice, and the one juniors underuse most.

**4. Flattening a hot join to avoid a two-hop lookup.**

Storing `tenant_id`

on every table in a multi-tenant system — even where it's derivable through a parent — so that every index can lead with it and every query can filter on it directly. This is standard, correct multi-tenant design, and it also lets row-level security work cleanly.

**5. Materialized path / nested sets for deep hierarchies.**

Recursive CTEs are fine for shallow trees. For an org chart read thousands of times a second, store a materialized path (`/1/17/93/`

) alongside the parent FK. Redundant, but a prefix `LIKE '/1/17/%'`

on an indexed column beats a recursion.

| Engine | Mechanism | Notes |
|---|---|---|
Oracle |
Materialized views with query rewrite, `FAST REFRESH ON COMMIT` , materialized view logs |
Best in class. Optimizer transparently rewrites queries to use the MV — the application doesn't change. |
SQL Server |
Indexed views (`WITH SCHEMABINDING` + unique clustered index) |
Maintained synchronously and always correct. Auto-matched by the optimizer in Enterprise Edition; other editions need `NOEXPAND` . Heavy restrictions: no outer joins, no subqueries, no `COUNT(*)` (use `COUNT_BIG(*)` ), `SUM` requires a `COUNT_BIG` column. |
PostgreSQL |
Materialized views — manual `REFRESH MATERIALIZED VIEW`
|
No incremental refresh, no automatic query rewrite. `REFRESH ... CONCURRENTLY` avoids blocking readers but requires a unique index and is slower. For incremental, roll your own with triggers. |
MySQL |
Nothing. No materialized views, no indexed views. |
You build summary tables yourself with triggers, scheduled events, or application logic. Plan for this from day one on MySQL. |

**Consequence for design:** on Oracle you can often denormalize *invisibly* via MVs with query rewrite — the logical model stays clean. On MySQL every denormalization is explicit, hand-maintained, and a permanent maintenance obligation. Weight the trade-off accordingly: the same denormalization is much cheaper on Oracle than on MySQL.

Three approaches, in order of preference:

**Always build #3 regardless of whether you have #1 or #2.** A denormalization without a drift-detection job is a future incident. Make "how do we detect and repair drift" a mandatory question in design review.

Note also: `CHECK`

constraints in MySQL were parsed and ignored until 8.0.16. Any schema older than that may have constraints that never did anything. Verify.

| Engine | Auto-indexes FK columns? |
|---|---|
MySQL/InnoDB |
Yes — automatically creates an index on the child column if one doesn't exist |
PostgreSQL |
No |
SQL Server |
No |
Oracle |
No |

Three of four leave you to it. Unindexed FKs cause:

`DELETE`

/`UPDATE`

on the parent (must scan children to enforce the constraint)**The Oracle-specific severity:** an unindexed foreign key causes Oracle to take a **share lock on the entire child table** during parent deletes and PK updates. Not a row lock — a table lock. On a busy system this is an outage. This is one of the classic Oracle DBA findings, and it's worth running a "find unindexed FKs" audit query on any Oracle schema you inherit.

**Rule: every FK column gets an index, on every engine, unless you can prove it's never traversed and the parent is never deleted from.**

`ON DELETE CASCADE`

Supported on all four. Caveats:

Scenario: `Vehicle`

with subtypes `Car`

, `Truck`

, `Motorcycle`

, each with distinct attributes.

**A. Single Table Inheritance** — one table, all columns, a `vehicle_type`

discriminator.

```
CREATE TABLE vehicle (
  vehicle_id    BIGINT PRIMARY KEY,
  vehicle_type  VARCHAR(20) NOT NULL,
  vin           VARCHAR(17) NOT NULL,
  -- car only
  passenger_cnt INT,
  -- truck only
  payload_kg    INT,
  CONSTRAINT ck_car CHECK (vehicle_type <> 'CAR' OR
                           (passenger_cnt IS NOT NULL AND payload_kg IS NULL))
);
```

`NOT NULL`

impossible on subtype attributes, degrades badly past ~4 subtypes or when subtypes diverge.Use when: subtypes are few, similar, and attributes are mostly shared.

**B. Class Table Inheritance** — common attributes in parent, specifics in child tables sharing the PK.

```
CREATE TABLE vehicle (
  vehicle_id   BIGINT PRIMARY KEY,
  vehicle_type VARCHAR(20) NOT NULL,
  vin          VARCHAR(17) NOT NULL UNIQUE,
  CONSTRAINT ck_type CHECK (vehicle_type IN ('CAR','TRUCK','MOTORCYCLE')),
  CONSTRAINT uq_vehicle_type UNIQUE (vehicle_id, vehicle_type)   -- key trick
);

CREATE TABLE car (
  vehicle_id    BIGINT PRIMARY KEY,
  vehicle_type  VARCHAR(20) NOT NULL DEFAULT 'CAR',
  passenger_cnt INT NOT NULL,
  CONSTRAINT ck_car_type CHECK (vehicle_type = 'CAR'),
  CONSTRAINT fk_car_vehicle FOREIGN KEY (vehicle_id, vehicle_type)
    REFERENCES vehicle (vehicle_id, vehicle_type)
);
```

That composite FK + check constraint pattern is the **standard way to make subtype exclusivity declaratively enforced** — a row in `vehicle`

typed `CAR`

cannot have a `truck`

row. It works on all four engines. Learn it; most people implement this with application logic and get it wrong.

`NOT NULL`

on subtype attributes, clean model, no sparse columns.
− Every full read needs a join; "give me all vehicles with all attributes" needs a `UNION ALL`

of per-subtype joins or several `LEFT JOIN`

s.Use when: subtypes have substantial distinct attributes. **This is the default correct answer for most business domains.**

**C. Concrete Table Inheritance** — one independent table per subtype, no parent.

`UNION ALL`

everywhere. Adding a subtype means touching every polymorphic query.Use when: subtypes barely overlap and are rarely queried together.

```
-- DON'T
CREATE TABLE comment (
  comment_id     BIGINT PRIMARY KEY,
  commentable_id BIGINT,        -- points to post OR photo OR video
  commentable_type VARCHAR(20)
);
```

Popular in Rails/Django-shaped codebases. You **cannot declare a foreign key**, so referential integrity is gone. Joins need `CASE`

or `UNION`

. Indexes are less selective.

Better: an exclusive-arc table with one nullable FK per target plus a check that exactly one is non-null; or a shared `commentable`

supertype table that each concrete type FKs into. The latter is more work up front and correct forever.

`INHERITS`

) exists but is largely a legacy feature. Its traps: unique constraints and foreign keys **Pick the narrowest type that will hold the domain, and pick it correctly the first time.** Changing a PK from `INT`

to `BIGINT`

on a 2-billion-row table is a multi-hour maintenance window on most of these engines.

`INT`

vs `BIGINT`

:`BIGINT`

from day one. The 4 extra bytes cost far less than the migration. Running out of `INT`

identity values in production is a rite of passage you should skip.`DECIMAL`

/`NUMBER`

always. Never `FLOAT`

/`REAL`

/`DOUBLE`

. `0.1 + 0.2 <> 0.3`

in binary floating point, and financial reconciliation will find it.`BOOLEAN`

. MySQL's `BOOLEAN`

is an alias for `TINYINT(1)`

— it accepts any integer. SQL Server has `BIT`

(0/1/NULL, not a true boolean; can't be used directly in `WHERE x`

without comparison). `NUMBER(1)`

or `CHAR(1)`

with a check constraint.`varchar(n)`

/`text`

have identical performance — the length limit is purely a constraint, so use `text`

+ a check if you like. MySQL's `VARCHAR`

length affects temp table and index size, and index prefix limits apply. SQL Server: `NVARCHAR`

is 2 bytes/char (pre-2019 non-UTF8 collations); `CHAR(n)`

blank-pads. `VARCHAR2`

is the one to use (`VARCHAR`

is reserved and may change meaning); default `NLS_LENGTH_SEMANTICS`

is BYTE, so `VARCHAR2(50)`

may hold fewer than 50 multibyte characters — specify `VARCHAR2(50 CHAR)`

.`timestamptz`

(stores UTC, converts on display) — use it, not `timestamp`

. Oracle: `TIMESTAMP WITH TIME ZONE`

or `WITH LOCAL TIME ZONE`

. SQL Server: `datetime2`

(not `datetime`

), `datetimeoffset`

if you need the offset. MySQL: `TIMESTAMP`

converts to UTC but is bounded to 2038; `DATETIME`

doesn't convert but has no timezone awareness. Pick one convention and enforce it across the schema.Row width is the quietest performance factor in the whole system. Every engine reads **pages**, not rows. Halving row width doubles rows per page, which roughly halves I/O for scans and doubles effective buffer cache capacity.

Practical consequences:

`TEXT`

/`CLOB`

/`NVARCHAR(MAX)`

column in a hot table. Move it to a side table keyed 1:1. (PostgreSQL's TOAST does this automatically for large values; Oracle stores LOBs out of line by default above a threshold; SQL Server and MySQL are less automatic — MySQL's `DYNAMIC`

row format stores overflow pages but the 20-byte pointer stays inline.)`SELECT *`

in application code is a design smell for exactly this reason, plus it defeats covering indexes and breaks when columns are added.`NOT NULL`

, `CHECK`

, `UNIQUE`

, and `FOREIGN KEY`

give the optimizer information. All four engines use them:

`NOT NULL`

declaration lets the optimizer skip null-handling logic and enables `NOT IN`

optimizations (see §2).`UNIQUE`

constraint tells the optimizer a join won't fan out, changing cardinality estimates and enabling join elimination.`LEFT JOIN`

to a parent table and select nothing from it, a validated FK lets SQL Server, Oracle, and PostgreSQL remove the join entirely. This is why views over well-constrained schemas perform better than views over "we enforce it in the app" schemas.**Corollary:** disabling constraints for a bulk load and re-enabling them `NOVALIDATE`

(Oracle) or as untrusted (SQL Server) leaves you with constraints the optimizer will not use. Check `sys.foreign_keys.is_not_trusted`

in SQL Server after any bulk load; check `STATUS`

/`VALIDATED`

in Oracle's `USER_CONSTRAINTS`

.

| Engine | Default isolation | Readers block writers? |
|---|---|---|
| PostgreSQL | Read Committed (MVCC) | No |
| Oracle | Read Committed (MVCC, undo-based) | No |
| MySQL/InnoDB |
Repeatable Read (MVCC) |
No |
| SQL Server | Read Committed with locking (unless RCSI is enabled) |
Yes |

**This is the biggest operational difference between SQL Server and the other three.** By default, a long-running report in SQL Server takes shared locks and blocks writers. This is why you see `WITH (NOLOCK)`

scattered through legacy SQL Server codebases — it's a bad fix (it permits dirty reads, missed rows, and duplicate rows from page splits during scans). The correct fix is enabling **Read Committed Snapshot Isolation (RCSI)** at the database level, which makes SQL Server behave like the others. Understand the tempdb implications before you flip it on a live system.

MySQL's Repeatable Read default is also worth knowing: it means a long transaction sees a consistent snapshot for its whole duration, and it uses **gap locks** for range operations, which produces deadlocks in patterns that would be fine elsewhere.

Oracle and PostgreSQL never block readers, but PostgreSQL's MVCC means **long-running transactions prevent vacuum from cleaning up dead tuples**, causing table bloat and eventually transaction-ID wraparound risk. A leaked idle-in-transaction connection is a PostgreSQL-specific production emergency. Monitor `pg_stat_activity`

for it.

| Operation | PG | MySQL 8.0 | SQL Server | Oracle |
|---|---|---|---|---|
| Add nullable column | Instant | Instant | Instant | Instant |
| Add column with default | Instant (11+) | Instant (8.0.12+, `INSTANT` algo) |
Instant (2012+ Enterprise; metadata-only) | Instant (11g+) |
| Add index | Blocks writes unless `CONCURRENTLY`
|
Online, mostly | Online = Enterprise only | Online = Enterprise |
| Widen varchar | Instant | Sometimes rebuilds (charset/length byte boundary) | Instant if same byte-length category | Instant |
| Drop column | Instant (marks it) | Rebuilds table (unless INSTANT) | Instant (marks it) |
`SET UNUSED` then drop |

**Migration rules for the team:**

`SET lock_timeout`

in PG, `lock_wait_timeout`

in MySQL, `SET LOCK_TIMEOUT`

in SQL Server). A migration that queues behind a long query and then blocks every subsequent query is how a 2-second DDL takes down a site.`ALTER TABLE`

takes an `ACCESS EXCLUSIVE`

lock. It waits for existing transactions, and everything queues behind it. Even an "instant" `ALTER`

can cause a total outage if a long-running `SELECT`

is in flight. Always `SET lock_timeout = '2s'`

and retry.`pt-online-schema-change`

/ `gh-ost`

exist for the cases where native online DDL doesn't cover you.`DISTINCT`

used to paper over a fan-out`NOT EXISTS`

, never `NOT IN`

against a subquery`SELECT`

list`ORDER BY`

has a deterministic tiebreaker`ROWS BETWEEN`

on any ordered window aggregate (mandatory on SQL Server)`UNION ALL`

unless dedupe is genuinely required| Engine | Command |
|---|---|
| PostgreSQL |
`EXPLAIN (ANALYZE, BUFFERS, VERBOSE)` — `BUFFERS` is the one people skip and shouldn't |
| MySQL |
`EXPLAIN ANALYZE` (8.0.18+), `EXPLAIN FORMAT=JSON`
|
| SQL Server | Actual execution plan; `SET STATISTICS IO, TIME ON` ; Query Store for history |
| Oracle |
`DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST')` — the actual plan, not `EXPLAIN PLAN` 's prediction |

**What to look for, universally:** estimated vs. actual row counts diverging by more than ~10× (bad stats or a bad predicate), scans where you expected seeks, and any operator whose row count explodes relative to its input.

`DECIMAL`

; timestamps are timezone-explicitEverything else in this doc is roughly portable. These are the ones to internalize:

`NOT IN`

handling`'' IS NULL`

, and single-column B-trees don't index NULLs.`ACCESS EXCLUSIVE`

locks make DDL risky.`RANGE`

is the default everywhere and is almost never what you want.