Tag: SQL

1. Sizing Transaction Scope Correctly

   May 9, 2026

   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.

   Read more

2. Aggregation After Joins: Solving the SQL Fan-Out Problem

   May 3, 2026

   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.

   Read more

3. Sharding Strategies for Large Tables: Planning Horizontal Partitioning Right

   May 2, 2026

   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.

   Read more

4. Self-Join: Practical Use Cases Beyond the Theory

   Apr 25, 2026

   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.

   Read more

5. SQL Code Review Checklist

   Apr 21, 2026

   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.

   Read more

6. Modeling Hierarchical Data: Four Approaches Compared

   Apr 12, 2026

   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.

   Read more

7. GROUPING SETS for Flexible Report Combinations

   Apr 7, 2026

   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.

   Read more

8. Writing Portable Stored Procedures: PL/pgSQL, T-SQL, PL/SQL

   Apr 6, 2026

   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.

   Read more

9. When NoSQL Really Is the Better Fit Than a Relational Database

   Mar 30, 2026

   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.

   Read more

10. Understanding the CAP Theorem in Practice

    Mar 29, 2026

    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.

    Read more