Page 11 - SQL

Classic tables store data row by row: each row sits physically together on disk, which makes single-record access fast. Analytical queries, by contrast, often read only a handful of columns, but across millions of rows, and with row storage still have to fetch every complete row from disk regardless. Columnstore indexes flip the storage model, lay out values column by column, and thereby achieve a speed gain on large aggregations that classic B-tree indexes can barely match.

A Common Table Expression names a subquery with the WITH clause and makes it referenceable in the main query like a table. Instead of nesting subqueries multiple levels deep, you get a query that reads top to bottom, states its logic once, and can be traced step by step, whether in PostgreSQL, MySQL, SQL Server, or Oracle.

A natural key like an email address or a product code looks tempting during the first schema draft: it is already unique, it seemingly saves an extra column, and it makes foreign keys more readable at first glance. The problem only shows up later, when that exact business value needs to change, a case that occurs in practically every long-lived system eventually. This article explains why natural keys are structurally fragile as a foreign key target, how a stable surrogate key resolves that fragility, and walks through a realistic migration to show how expensive the fix becomes later.

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.

PhpStorm ships with a full database client, complete with SQL autocompletion, EXPLAIN PLAN visualization, schema diff and query analysis. Anyone who knows these tools no longer needs to open TablePlus, DBeaver or Sequel Pro.

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.