Page 15 - SQL

The same query can get two completely different execution plans on two databases with an identical schema but a different data distribution, without a single character of the SQL text changing. Responsible for this is the cost-based optimizer, which selects the cheapest option, in its own estimation, from among many theoretically possible access strategies, using statistics, cardinality estimates, and a cost model. Understanding how this decision process works internally lets you diagnose optimizer misjudgments precisely instead of merely guessing at them.

Returning a limited number of rows from a query sounds like a trivial task, yet MySQL, PostgreSQL, SQL Server, and Oracle write this limit with four completely different syntax forms. Anyone who wants to keep pagination code portable across these systems needs an overview of LIMIT, TOP, ROWNUM, and the common ANSI standard denominator OFFSET FETCH.

Database replication distributes data from a primary instance to one or more replica instances, in order to scale read load and increase fault tolerance. Anyone who does not clearly distinguish synchronous from asynchronous replication risks either unnecessary latency or silent data loss during failover.

Every new database connection costs memory, CPU time, and time for the connection setup itself. Connection pooling keeps a limited number of reusable connections open and prevents an application from opening more connections under load than the database can handle. Get pool size wrong and you either produce connection errors or waste capacity.

Few data modeling mistakes come back to bite you as reliably, months later, as a sloppy approach to time zones: a server in Europe/Berlin, a customer in New York, a daylight saving transition right in the middle of a recurring appointment, and suddenly timestamps no longer add up. This article explains the technical difference between TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE, walks through the most common pitfalls in everyday database work, and gives a clear, battle-tested recommendation for handling time zones cleanly.

String aggregation folds multiple rows of a group into a single, comma separated text list, for example every tag of a product in one row instead of several rows. MySQL uses GROUP_CONCAT for this, PostgreSQL and SQL Server use STRING_AGG, Oracle uses LISTAGG. This article shows syntax, ordering, separators and length limits of all four systems side by side.

A normalized OLTP schema is a poor fit for fast analysis across millions of rows. Claude helps design fact and dimension tables, slowly changing dimensions and dbt models that deliver a clean analytical layer. This article covers the path from a relational source system to a performant star schema.

Snapshot isolation rightly enjoys a reputation as a comfortable compromise: it prevents dirty reads, non-repeatable reads, and most phantom reads, without the locking overhead of a full serializable level. Even so, it still allows a subtle but dangerous anomaly known as write skew, where two transactions independently make decisions that each look consistent on their own but together violate a business rule. Serializable snapshot isolation, or SSI, closes exactly that last gap. This article explains both isolation levels in detail and walks through a classic write skew example step by step.

MVCC lets a database show every transaction a consistent snapshot of the data without reads blocking writes or vice versa. Instead of locking rows, the database maintains several versions of each row at once, PostgreSQL and InnoDB solve this in different ways, with direct consequences for vacuum, purge, and the choice of isolation level.

ETL patterns can largely be expressed directly in SQL instead of pushing every transformation into application code. Treating extract, transform and load as set based operations produces pipelines that are idempotent, traceable and easy to test, rather than fragile chains of scripts with hidden side effects.