Structured Query Language, the standard language for interacting with relational databases. It covers querying data with SELECT, modifying it with INSERT, UPDATE, and DELETE, defining schemas with CREATE and ALTER, and controlling access with GRANT and REVOKE. SQL is the universal interface to databases like PostgreSQL, MySQL, SQLite, and SQL Server.
Temporal tables solve a problem that ordinary tables cannot: preserving every past state of a row while keeping the current state fast to query. The SQL:2011 standard introduced system-versioned temporal tables, and the major relational databases have implemented the concept with different syntax and storage models. SQL Server calls them system-versioned temporal tables and stores…
Relational databases have stored graph-shaped data since before graph databases existed. An org chart is a self-referencing table; a bill of materials is a parent-child relationship; a social network is a many-to-many table with a join table. What has changed is the query interface. For decades, the only portable way to traverse these structures was…
The difference between row storage and columnar storage is a single physical decision—whether the values of a row are stored together or the values of a column are stored together—and that decision cascades into every performance characteristic of an analytical query. A row store writes each tuple contiguously, so fetching one complete record is a…
The choice between a star schema and a snowflake schema is a trade-off between query simplicity and storage normalization, and the trade-off is almost always decided in favor of the star schema for analytical workloads. A star schema has a single fact table surrounded by denormalized dimension tables, one per dimension. A snowflake schema normalizes…
Data Vault 2.0 is not a dimensional model with different names. It is an integration architecture that separates identity, relationship, and descriptive history into three distinct table types—hubs, links, and satellites—and keys them with deterministic hash values rather than sequential surrogate keys. The separation is what makes the model resilient to source-system changes: adding a…
Dimension data changes slowly and unpredictably. A customer moves to a new city, a product is recategorized, a salesperson transfers to a different region. A data warehouse that overwrites the old value on every change cannot answer the question “what region was this salesperson assigned to when the sale was made?” Slowly Changing Dimensions (SCD)…
MPP architecture is the structural answer to a single question: what happens when the data is too large and the query too complex for one machine to process in a reasonable time? The answer is to spread both the data and the work across many independent nodes, each with its own CPU, memory, and disk,…
Distributed SQL engines exist to answer a question that traditional relational databases answer poorly: how do you run ACID transactions with strong consistency across multiple machines, multiple data centers, or multiple continents without sacrificing the relational model? The three systems most commonly discussed in this category—Google Spanner, CockroachDB, and YugabyteDB—share the same architectural lineage. Each…
Two-Phase Commit is the classic atomic commitment protocol for distributed transactions. It exists to solve a single problem: how do multiple independent resource managers—databases, message queues, transactional caches—agree to either commit or abort a transaction as a unit, even when some of them fail during the process? The protocol is not a consensus algorithm in…
Consensus protocols are the mechanism by which a cluster of independent servers agrees on a single ordered sequence of operations, even when some servers fail or messages are delayed. In a distributed database, this agreement is what allows a write to be replicated to multiple nodes without divergence: every replica applies the same operations in…