Page 9 - SQL

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.

Dynamic data masking solves a problem that classic access control only partially covers: a support agent needs access to a customer table to find an order, but not the full credit card number or the private email address behind it. Instead of maintaining two separate copies of the table or overloading application logic with visibility rules, the database itself masks certain column values at runtime, depending on the role of the current connection. This article explains the mechanics, shows practical rules for different data types, and makes clear where the limits of the technique lie.

Few SQL habits are as convenient and simultaneously as contentious as SELECT *. For a quick query in the console, the asterisk saves typing and immediately returns every available piece of information. But once the same query lands in application code that runs repeatedly and has to be maintained for years, the calculus flips: needlessly transferred data, application code that silently breaks on schema changes, and wasted optimization potential from blocked covering indexes. This article works through the concrete problems, names the legitimate exceptions, and shows how the pattern can be reliably caught in code review.

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.

FROM orders o, customers c WHERE o.customer_id = c.id looks at first glance like a harmless shorthand for a join, and syntactically it is exactly that. The implicit comma syntax dates back to before today's common JOIN keyword and still works unchanged in most database systems. The decisive difference from explicit JOIN syntax isn't the result of a correctly written query, it's what happens when a developer makes a mistake: with comma syntax, a forgotten WHERE condition silently turns into a cartesian product, with no error at all. This article explains why that's structurally riskier, where comma syntax still shows up, and why explicit JOIN meaningfully improves readability on complex queries too.

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.

Constraints enforce data integrity where it can be enforced most reliably: directly in the database. This article shows CHECK, UNIQUE and NOT NULL with real syntax, explains named constraints, migrations on existing data, and why database-level rules stay essential even with clean validation in the application code.

The data type of a column can only be changed with considerable effort once a table has grown, which is why the initial decision deserves particular care. This article covers INT versus BIGINT for primary keys, realistic VARCHAR lengths, DECIMAL instead of FLOAT for monetary amounts, and the most common time zone pitfalls with timestamps, with concrete SQL for every decision.