Tag: SQL

1. Conditional Aggregation: Building KPI Reports with CASE WHEN

   Jul 3, 2025

   Conditional aggregation combines CASE WHEN or the FILTER clause with aggregate functions to compute several conditional metrics in a single pass over a table, instead of writing one query per metric. This article shows how conditional aggregation makes dashboards and reports faster, more maintainable, and clearer, and which pitfalls around NULL values and data types typically show up.

   Read more

2. Using Statistical Functions in SQL: Median, Percentiles, Spread

   Jun 22, 2025

   AVG alone rarely tells the full story of a data distribution. With statistical functions like PERCENTILE_CONT, STDDEV, and CORR, median, spread, and relationships can be computed directly in the database, without having to export raw data to Python or R.

   Read more

3. Analyzing Wait Events and Wait Statistics

   Jun 19, 2025

   A database with low CPU utilization can still respond drastically slowly, because sessions are actually waiting on locks, disk IO, or the network, not on free compute time. Wait events make exactly that invisible wait time measurable and categorizable. Systematically distinguishing lock waits from IO waits leads to the actual root cause, instead of optimizing past CPU or memory metrics.

   Read more

4. Multi Tenancy Data Models Compared

   Jun 19, 2025

   Multi tenancy decides whether a SaaS application safely separates data from hundreds of tenants or one day risks a data leak between customers. This post compares shared schema with tenant_id, schema-per-tenant, and database-per-tenant by isolation, migration effort, and resource isolation, so the decision rests on real criteria instead of gut feeling.

   Read more

5. Domain Types for Reusable Constraints

   Jun 18, 2025

   A domain type bundles a base data type together with one or more constraints into its own, named type that any number of tables and columns can reuse. This article covers CREATE DOMAIN and ALTER DOMAIN in detail, practical examples for email addresses and positive amounts, migrations of existing columns, and the limits of this approach compared to per-column constraints.

   Read more

6. Distributed Transactions Across Services: The Saga Pattern

   Jun 17, 2025

   As soon as a business operation changes data across multiple microservices with their own databases, a classic ACID transaction no longer works. Distributed transactions across services do not solve this problem with global locks, but with the saga pattern: a chain of local transactions with defined compensation steps for the failure case.

   Read more

7. String Functions Compared Across Databases: Concat, Substring, Trim

   Jun 5, 2025

   Joining two strings or extracting a substring sounds like the simplest task in SQL, yet MySQL, PostgreSQL, SQL Server, and Oracle differ in operators, function names, and even how character positions are counted. Anyone who wants to keep string functions portable across these systems needs to know these differences before Unicode and multibyte characters add further pitfalls.

   Read more

8. Pessimistic Locking with SELECT FOR UPDATE

   Jun 3, 2025

   Pessimistic locking locks a row already at read time with SELECT FOR UPDATE, so no other transaction can change it concurrently until your own transaction commits or rolls back. Combined with SKIP LOCKED, this builds robust, concurrent queue processing, while NOWAIT and lock timeouts prevent requests from waiting indefinitely on a locked row.

   Read more

9. Writing Database-Agnostic SQL: Portability in Practice

   May 29, 2025

   SQL is a standard, but every database system interprets that standard with its own extensions and deviations. Quoting rules for identifiers, the syntax for paging, the notation for auto-generated IDs, and even basic data types differ so significantly between MySQL, PostgreSQL and SQL Server that real database-agnostic SQL requires deliberate design rather than accident.

   Read more

10. Regression Testing Database Schemas

    May 25, 2025

    A database schema changes over years through many small migrations, and without systematic regression tests it stays unclear whether a new change silently breaks an existing application. Schema snapshots, contract tests and automated diff checks make exactly that visible before a deployment causes damage.

    Read more