Tag: SQL

1. Hybrid Approaches: Combining SQL Plus a Document Store

   Oct 6, 2025

   A hybrid architecture keeps transactional core processes in a relational database, while a document store takes over for variable, heavily read use cases. Whoever deliberately plans data splitting, synchronization, and consistency boundaries gets the advantages of both worlds without settling for an unclean compromise.

   Read more

2. Window Frames: Truly Understanding ROWS vs. RANGE

   Oct 1, 2025

   ROWS and RANGE both define a window frame, but they do it in fundamentally different ways: ROWS counts physical row positions, RANGE looks at logical value ranges of the sort column. With unique sort values, both return the same result, but with ties in the sort column, ROWS and RANGE can produce different numbers for the same query.

   Read more

3. UNION vs. UNION ALL: The Difference That Costs Performance

   Sep 16, 2025

   UNION and UNION ALL both combine the result sets of multiple SELECT statements into a single output, but only UNION deduplicates every single row with an implicit sort or hash step. Anyone who does not know this difference pays unnecessary compute time on every query for a deduplication that in many cases is not needed at all, because the combined result sets are already disjoint.

   Read more

4. Data Warehousing Fundamentals with SQL: Star Schema, Facts, and Dimensions

   Sep 13, 2025

   A data warehouse structures data for fast analysis, not for transactional write operations. The star schema with fact tables and dimension tables is the most proven structure for this, because it makes aggregation queries over billions of rows easy to read and performant, without the complexity of a fully normalized schema.

   Read more

5. Date Functions Compared Across Databases: PostgreSQL, MySQL, SQL Server, Oracle

   Sep 8, 2025

   Date functions are one of the areas where databases differ the most, even though the underlying task seems simple. This article compares date arithmetic, formatting, time zone handling, and extraction of individual date parts in PostgreSQL, MySQL, SQL Server, and Oracle using concrete, runnable examples.

   Read more

6. Row Level Security: Concepts and Practical Implementation

   Sep 8, 2025

   Row Level Security moves access rules out of the application layer and into the database itself, filtering every row automatically according to the current security context. Anyone securing multi-tenancy or tenant-sensitive application logic without Row Level Security relies on disciplined WHERE clauses in every single query, and that breaks sooner or later.

   Read more

7. Soft Deletes: A Modeling Guide

   Sep 7, 2025

   A soft delete marks a row as deleted without physically removing it, sounds like a single extra column, and turns out in practice to be a source of unique constraint conflicts, bloated indexes, and forgotten WHERE clauses. This post shows three established models for soft deletes and explains when each one truly fits.

   Read more

8. Understanding Deferred Constraints

   Sep 5, 2025

   A deferred constraint moves the check of a foreign key, unique, or exclude constraint from the moment of each individual statement to the end of the transaction. This article shows how DEFERRABLE INITIALLY DEFERRED and SET CONSTRAINTS work, how circular foreign keys and primary key swaps can only be implemented cleanly this way, and which vendor differences matter in practice.

   Read more

9. Subqueries vs. Joins: When to Use Which

   Aug 25, 2025

   The choice between a subquery and a join is rarely a matter of pure taste, it depends on the kind of subquery: scalar subqueries return a single value, correlated subqueries reference the outer query, derived tables appear as a complete table in the FROM clause, and each of these forms has its own, often equivalent join, which the optimizer of modern databases in many cases even generates automatically.

   Read more

10. Database Access Auditing: Who Read or Changed What, and When

    Aug 23, 2025

    Without resilient database access auditing, all that remains after a security incident is guessing: which rows were read, who changed a value, and how long a suspicious access has already been going on. Trigger-based logging, pgAudit, and immutable storage turn this uncertainty into a traceable, defensible record that also satisfies compliance requirements such as GDPR or PCI-DSS.

    Read more