SQL: Guides and Practical Know-how | Page 11 | mironsoft Blog
SQL
-
Jul 15, 2025
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.
-
Jul 6, 2025
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.
-
Jul 3, 2025
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.
-
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.
-
Jun 29, 2025
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.
-
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.
-
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.
-
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.
-
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.
-
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.