SQL

Transaction scope determines how much work gets bundled inside a single transaction. A scope that is too broad creates lock contention and blocks concurrent access, while a scope that is too narrow loses the guarantee that related changes apply together or fail together. Sizing it correctly follows clear, practical criteria rather than pure intuition.

As soon as aggregation after joins runs over several one to many relations at once, rows multiply before SUM or COUNT even start calculating, and results become silently too high. This article explains the fan-out problem with a concrete example and shows how pre-aggregation in subqueries and window functions reliably prevent wrong sums.

Sharding distributes a large table across multiple independent database instances once a single instance hits its capacity limit. Choosing the shard key determines the entire lifespan of the architecture, because a poorly chosen shard key can only be corrected afterward with considerable migration effort.

Typing SQL queries by hand costs time, especially with multi step JOINs, window functions and aggregations across multiple tables. Claude generates correct, readable queries from a clearly described schema and reviews existing queries for performance traps and security gaps before they run in production. This article shows the complete workflow from schema context to EXPLAIN analysis.

A self-join links a table to itself and looks at first glance like an academic exercise, but in practice it is the tool for three very concrete problems: modeling hierarchical relationships like employees and managers, spotting duplicate records based on business criteria, and directly comparing consecutive rows, such as events or prices, without needing procedural loops for it.

An SQL review that only looks at indentation and variable names misses the bugs that become truly expensive in production later. A systematic checklist covering readability, correctness, performance red flags and security checks makes SQL code reviews reproducible instead of dependent on a reviewer's gut feeling.

UNION and UNION ALL belong to the standard toolkit, but the two other set operators in the SQL standard, INTERSECT and EXCEPT, lead a shadow existence in many codebases, even though for certain questions they are the clearest and most maintainable solution available. INTERSECT answers which rows appear in two result sets at the same time, EXCEPT answers which rows appear in the first set but not the second. Both questions can also be solved with JOIN or NOT EXISTS constructs, but the set operator often makes the actual business intent visible at a glance. This article covers when switching is worth it, how syntax differs between databases, and how performance actually compares to the alternatives.

Hierarchical data such as category trees, org charts, or comment threads does not fit naturally into flat relational tables, and the chosen model decides whether inserting or reading stays fast. This post compares four established modeling strategies for tree structures in SQL by write and read cost, so the decision fits the actual access pattern instead of the first idea that comes to mind.

GROUPING SETS compute exactly the subtotal combinations a report actually needs, in a single query with a single table scan. This article shows how GROUPING SETS differ from ROLLUP and CUBE, why they produce fewer superfluous rows for individually tailored reports, and how the GROUPING function reliably separates real NULL values from subtotal rows.

Stored procedures are among the least portable building blocks of SQL, because every major database system brings its own, incompatible procedural language. This article compares PL/pgSQL, T-SQL, and PL/SQL and shows concrete strategies to keep business logic in stored procedures migratable regardless.