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:

  • Choosing the right query pattern for the problem
  • Using NOT EXISTS / EXISTS where appropriate
  • Avoiding unnecessary nested aggregate subqueries
  • Understanding joins, filtering, grouping, and set-based logic
  • Using window functions like DENSE_RANK() and similar tools when appropriate
  • Working with JSON data in the database
  • Good Database and Table Design:Indexing strategy
  • Normalization vs selective denormalization
  • When some redundancy improves performance
  • Parent/child and subtype/subclass-style table design
  • Understand how schema choices affect performance and maintainability

And being an autodidact software engineer with an electrical engineering background in computer engineering, I would reference The Library 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 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 was custom written from the ground up for an OPEX problem.

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

Knowledge Transfer Doc — PostgreSQL · MySQL (InnoDB) · SQL Server 2008+ · Oracle 19c+

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.

PART I — WRITING QUERIES

1. Choosing the Right Query Pattern

Before you write anything, answer three questions:

  1. What is the grain of my result? One row per what? If you can't say it in a sentence, you will produce duplicates.
  2. Am I filtering, or am I enriching? Filtering → semi/anti-join (EXISTS). Enriching → join or window function.
  3. Do I need one value per group, or one row per group? One value → aggregate. One row → window function or lateral.

The pattern decision table [SAME across all four]

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

The DISTINCT smell [SAME]

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');

Enter fullscreen mode Exit fullscreen mode

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

CTEs: the biggest cross-engine trap [DIFFERS — important]

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).

2. EXISTS / NOT EXISTS — and Why NOT IN Keeps Hurting You

The NULL trap [SAME — all four, it's ANSI] 

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

Enter fullscreen mode Exit fullscreen mode

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 declared in the DDL — not "we're pretty sure it's never null."

The optimizer angle [DIFFERS]

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 [SAME semantics, [DIFFERS] on plan quality]

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

  • It's easy to break by adding a second predicate on the right table in WHERE instead of ON, which silently converts your outer join to an inner join.
  • Oracle and SQL Server usually recognize it and produce the same anti-join plan. PostgreSQL does too. MySQL historically preferred this form (pre-8.0.17 it was the only way to get a decent anti-join plan) — you'll see it a lot in legacy MySQL code.

Prefer NOT EXISTS for readability; recognize the LEFT JOIN / IS NULL form when you meet it.

EXISTS vs IN for the positive case [SAME, mostly]

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 dangerous (unlike NOT IN) — NULLs just never match.
  • MySQL 5.x executed 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 [SAME]

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.

3. Avoiding Unnecessary Nested Aggregate Subqueries

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

The anti-pattern 

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';

Enter fullscreen mode Exit fullscreen mode

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.

Fix 1 — one window pass 

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';

Enter fullscreen mode Exit fullscreen mode

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.

Fix 2 — pre-aggregate then join 

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';

Enter fullscreen mode Exit fullscreen mode

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 SUMs.

The double-counting bug [SAME] 

-- 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;

Enter fullscreen mode Exit fullscreen mode

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.

Fix 3 — lateral / apply, when you need the whole row [DIFFERS in syntax only]

"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;

Enter fullscreen mode Exit fullscreen mode

(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 entire child table. For 100 customers against a 500M-row orders table, lateral wins by orders of magnitude. For "top 3 per customer across all 5M customers," 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.

Oracle-only shortcut 

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;

Enter fullscreen mode Exit fullscreen mode

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.

4. Joins, Filtering, Grouping, and Set-Based Logic

ON vs WHERE on outer joins [SAME] 

-- 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

Enter fullscreen mode Exit fullscreen mode

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.

Predicate sargability [SAME principle, [DIFFERS] in remedies]

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

WHERE UPPER(email) = '[email protected]'          -- no index use on email
WHERE order_date + 1 > SYSDATE          -- no index use
WHERE YEAR(order_date) = 2026           -- no index use

Enter fullscreen mode Exit fullscreen mode

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:

  • Oracle: 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().
  • SQL Server: 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.

Implicit conversion — the silent index killer [DIFFERS in how it manifests]

  • SQL Server: an ORM sending NVARCHAR parameters against a VARCHAR column forces CONVERT_IMPLICIT on the column, producing a full scan. Data type precedence means 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.
  • MySQL: joining columns with different collations or charsets (e.g. 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 column to a number → full scan. Extremely common after a partial charset migration.
  • Oracle: 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.
  • PostgreSQL: stricter typing means fewer silent conversions, but 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 [DIFFERS]

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 [SAME]

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.

Conditional aggregation [DIFFERS slightly] 

-- 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;

Enter fullscreen mode Exit fullscreen mode

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.

Set operators [DIFFERS in naming and defaults]

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.

Join algorithms [DIFFERS — matters at scale]

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.

5. Window Functions

Availability [DIFFERS]

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]

The three ranking functions [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.

Deduplication idiom [SAME] 

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;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

PG-only. Often faster than ROW_NUMBER there.

The default frame — the biggest window-function gotcha [SAME semantics, [DIFFERS] in cost]

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 full day's total, not a true row-by-row running sum. People find this months later.

-- 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)

Enter fullscreen mode Exit fullscreen mode

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.

Named windows [DIFFERS]

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.

Performance notes [DIFFERS]

  • An index on (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.
  • SQL Server 2019+ has batch-mode window aggregates on rowstore, which is dramatically faster for large partitions — but it's cost-threshold-gated. SQL Server 2022 improved the window aggregate operator further.
  • MySQL sorts for every distinct window specification. Three windows with three different ORDER BYs = three sorts. Consolidate window definitions where you can.
  • Oracle's window implementation is the most mature of the four and generally the fastest at large scale, especially with parallel query.

When not to use a window function

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.

6. JSON in the Database

First, the design rule [SAME]

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.

Capability comparison [DIFFERS heavily — this is the least portable area in SQL]

PostgreSQL MySQL 8.0 SQL Server Oracle
Native type json (text) / jsonb (binary) 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 jsonb — indexes all keys/values at once ❌ 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

PostgreSQL: use 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'));

Enter fullscreen mode Exit fullscreen mode

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.

MySQL: no direct JSON index — this catches everyone

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"')

Enter fullscreen mode Exit fullscreen mode

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.

SQL Server: text with functions (historically)

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);

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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: strongest declarative story

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));

Enter fullscreen mode Exit fullscreen mode

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.

Portable JSON advice

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.

PART II — SCHEMA & PHYSICAL DESIGN

7. Indexing Strategy

Universal principles [SAME]

  1. Leading-column rule. A composite index on (a, b, c) serves predicates on a, (a,b), (a,b,c). It does not efficiently serve b alone. All four can do a full index scan in that case, but that's not what you designed for.
  2. Equality columns first, then range, then include-only columns. 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.
  3. Covering indexes eliminate the table lookup. If the index contains every column the query needs, the engine never touches the base table.
  4. Every index is a tax on writes. Ten indexes = ten structures to maintain per insert/update. Measure before adding.
  5. Selectivity matters. An index on a boolean or a 3-value status is usually useless alone; it's useful as a trailing column or as a partial index.
  6. Index your foreign keys. See below — this one differs, and it matters.

The structural difference: clustering [DIFFERS — foundational]

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; corre
Read the original source Next news
#Backend
Dev.to

Publisher

Originally by Andrei Merlescu

0 Comments

Log in to join the conversation.

No comments yet. Be the first to share your thoughts.