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.
The storage engine is the component of a database that decides how data is physically written to disk, how it is read back, and how concurrent access is managed. The query planner and the SQL parser sit above it; the operating system and the disk sit below it. When you run SELECT * FROM users…
Every relational database that claims ACID compliance has to solve the same problem: how do you guarantee that a committed transaction survives a crash, and how do you undo a transaction that was interrupted mid-flight? The answer, across every major engine, is the same: write the change to a log before you write it to…
The buffer pool is the database’s cache of disk pages in memory. Every query that reads a row, every index lookup, and every update touches a page in the buffer pool before it touches the disk. When the requested page is already in memory, the read is served without I/O. When it is not, the…
Multi-Version Concurrency Control is the mechanism that lets a database serve readers and writers simultaneously without locking. A reader sees a consistent snapshot of the data as it existed when its query or transaction started, while a writer modifies the current version. The two never block each other because they are looking at different versions…
Lock escalation, latching, and two-phase locking are three mechanisms that operate at different layers of the database engine, and confusing them is the source of many operational misunderstandings. Lock escalation is a memory optimization: when a single statement accumulates too many row-level locks, the engine converts them into a single table-level lock to reduce the…
The cost-based optimizer is the component of a database that decides how to execute a query. It does not guess. It reads statistics about the tables, columns, and indexes involved, estimates how many rows each step of the plan will produce, and assigns a cost to every candidate plan. The plan with the lowest cost…
A join is the operation that combines rows from two tables based on a condition. The SQL statement says what to join; the optimizer chooses how to join it. That choice is one of three physical algorithms: nested loop, hash join, or merge join. Each has a different cost profile, a different set of conditions…
The query execution engine is the component that takes the physical plan chosen by the optimizer and actually runs it. The optimizer decides what to do—which join algorithm, which index, which order. The execution engine decides how fast to do it. Two execution models dominate the landscape: the Volcano iterator model and vectorized execution. The…
A B-tree index answers one question well: does this value fall within this ordered range? It is the correct index for equality and range predicates on scalar columns, and it is the default because most queries ask exactly that question. But a query that asks “which documents contain the word ‘index’” or “which geometric shapes…
Spatial data represents information about the physical location and shape of geometric objects—points, lines, polygons, and the relationships between them . A database that can store coordinates is not automatically a spatial database. A spatial database must understand the geometry’s structure, support the operations that answer spatial questions (does this point fall inside that polygon?…