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.

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.

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.

The choice between NoSQL and a relational database is too often made based on popularity instead of requirements. Whoever instead evaluates access patterns, scaling needs, consistency requirements, and team expertise concretely quickly recognizes in which cases NoSQL actually brings advantages and where a relational database remains the far more robust choice.

The CAP theorem is often quoted as an abstract formula, but above all it is a practical decision tool for the moment a network actually fails. During a partition, a distributed system has to decide whether it keeps giving consistent answers and sacrifices availability for it, or stays available and accepts temporarily inconsistent data in return.