Page 8 - SQL

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.