Page 6 - SQL

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.