sql – Page 3 – Türker Yıldırım

The foreign key we added six years late

NoteMay 17, 20232 min read

A column named customer_id with no constraint, and 312 rows pointing at customers that had been deleted. Deciding what to do with the orphans is the work —…

A CHECK constraint that documents a JSON shape

NoteApril 23, 20232 min read

A JSON column accepts anything, which is the feature and is also how a column ends up with four different shapes in it. The constraint is enforced on…

One collection in MongoDB, and the reasons that did not hold

NoteApril 13, 20232 min read

A document store added for one collection of variable-shaped supplier payloads, in an application whose other forty tables are relational. The migration back was two days and the…

Multi-valued indexes on a JSON array, and the CAST

NoteMarch 27, 20232 min read

Filtering on a value inside a JSON array was a full scan until 8.0.17 added multi-valued indexes, and the syntax is unforgiving. The ARRAY keyword in the cast…

A read replica, and the six queries that could not use it

Deep diveMarch 16, 202314 min read

A primary at 80% CPU and reports running during the working day. Setting up the replica takes an afternoon; the consistency model takes a month.

Aggregate boundaries drawn by transaction, not by noun

NoteMarch 14, 20232 min read

An order aggregate containing its lines, its shipments, its refunds and its notes, loaded in full to change a delivery address. Drawing the boundary by what belongs together…

Extracting a module without extracting a service

Deep diveFebruary 3, 202314 min read

A billing module everybody agrees should be separate, a team of four, and 88 classes that reference it. The boundary, drawn in code rather than in conversation.

CHECK constraints are enforced from 8.0 and ignored before

NoteOctober 20, 20221 min read

MySQL 5.7 parses a CHECK constraint, stores it and never evaluates it, which is worse than rejecting it. A constraint that exists and does nothing is the worst…

A generated column with an index, for a JSON path

NoteMay 11, 20221 min read

An expression in a WHERE clause cannot use an index, and a generated column moves the expression somewhere indexable. The automatic substitution is the good part and requires…

LATERAL, for a top-N per group without a window function

NoteJanuary 21, 20221 min read

A correlated derived table can reference columns from the row it is joined against, which expresses top-N-per-group directly. A window function with ROW_NUMBER reads more clearly and computes…