Glossary

ACID

ACID is the set of guarantees a relational transaction provides: Atomicity, Consistency, Isolation, and Durability. Atomicity makes a transaction all-or-nothing; Consistency moves the database from one valid state to another respecting all constraints; Isolation keeps concurrent transactions from interfering; Durability persists committed data across crashes. These properties are the foundation of correctness in SQL databases like PostgreSQL, MySQL/InnoDB, and Oracle.

Aggregate Function

An aggregate function computes a single value over a set of rows, such as COUNT, SUM, AVG, MIN, and MAX. Aggregates are usually paired with GROUP BY to collapse groups of rows into one row per group. Rows can be filtered before aggregation with WHERE and after aggregation with HAVING.

B-tree Index

A B-tree is the default index structure in most relational databases, keeping keys sorted in a balanced tree so lookups, range scans, and ordered retrieval run in logarithmic time. It accelerates equality and range predicates (=, <, >, BETWEEN) and queries that need ORDER BY on the indexed columns. It does not help with leading-wildcard LIKE '%x' searches.

CAP Theorem

The CAP theorem states that a distributed data store can provide at most two of Consistency, Availability, and Partition tolerance at the same time. Because network partitions are unavoidable in practice, a distributed system effectively chooses between consistency and availability when a partition occurs. It frames the trade-offs of distributed SQL and NoSQL stores.

Cardinality

Cardinality describes the number of distinct values in a column or the number of rows a query or join is expected to produce. High-cardinality columns (such as a primary key) are highly selective and index well; low-cardinality columns (such as a boolean flag) are poor index candidates on their own. The query planner uses cardinality estimates to choose join order and access methods.

CHECK Constraint

A CHECK constraint restricts the values allowed in a column or row to those satisfying a boolean expression, for example CHECK (price >= 0). The database enforces it on every insert and update, rejecting rows that violate it. It pushes domain rules into the schema so invalid data cannot be persisted.

Clustered Index

A clustered index determines the physical storage order of the table's rows, so the table data is stored in the index leaf pages. A table can have only one clustered index. In SQL Server and MySQL/InnoDB the primary key is the clustered index by default; PostgreSQL has no permanent clustered index, only a one-time CLUSTER command.

Column

A column is a named, typed attribute of a table that holds one value per row. Each column has a data type (such as INTEGER, TEXT, TIMESTAMP), an optional default, and optional constraints like NOT NULL. The set of columns defines the shape of every row in the table.

Common Table Expression (CTE)

A Common Table Expression is a named, temporary result set defined with WITH that exists for the duration of a single statement. CTEs improve readability by naming intermediate steps and enable recursion with WITH RECURSIVE for hierarchical data. A CTE referenced once is logically equivalent to a derived table but easier to read.

Composite Index

A composite (multi-column) index covers more than one column in a defined order, for example (customer_id, created_at). It serves queries that filter on a leftmost prefix of the columns, so column order matters: an index on (a, b) helps WHERE a = ? and WHERE a = ? AND b = ? but not WHERE b = ? alone. Choosing the right order is one of the highest-leverage tuning decisions.

Connection Pool

A connection pool keeps a bounded set of open database connections that application threads borrow and return, avoiding the cost of opening a new TCP and authentication handshake per query. Pool size must be tuned: too small starves throughput, too large overwhelms the database with concurrent sessions. HikariCP and pgbouncer are common implementations.

Constraint

A constraint is a rule the database enforces on table data, including PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK. Constraints protect data integrity at the storage layer so application bugs cannot persist invalid state. The database rejects any statement that would violate a constraint and rolls back the offending transaction.

Cursor

A cursor is a database object that lets a client iterate over the rows of a result set one at a time or in batches, rather than materialising the whole set in memory. Server-side cursors are useful for streaming large results, while client-side iteration is common in application drivers. Holding cursors open inside long transactions can pin resources and should be avoided.

Deadlock

A deadlock occurs when two or more transactions each hold a lock the other needs, so none can proceed. Databases detect deadlocks and resolve them by aborting one transaction (the victim), which the application must retry. Acquiring locks in a consistent order across transactions is the standard way to reduce deadlock frequency.

Denormalization

Denormalization deliberately introduces redundancy (such as duplicated columns or precomputed aggregates) into a normalized schema to speed up reads at the cost of more complex writes. It trades the integrity guarantees of normalization for query performance and is typically applied to read-heavy reporting paths. Redundant data must be kept consistent, often via triggers, materialized views, or application logic.

DDL (Data Definition Language)

DDL is the subset of SQL that defines and changes database structure, including CREATE, ALTER, DROP, and TRUNCATE. DDL statements operate on schema objects like tables, indexes, and views rather than on row data. In many databases DDL is transactional in PostgreSQL but causes an implicit commit in MySQL and Oracle.

DML (Data Manipulation Language)

DML is the subset of SQL that reads and modifies row data: SELECT, INSERT, UPDATE, DELETE, and MERGE. DML statements run inside transactions and can be rolled back. They are the everyday operations an application issues against the data.

Foreign Key

A foreign key is a column or set of columns that references the primary key (or a unique key) of another table, enforcing referential integrity. The database prevents inserting a child row that points to a non-existent parent and can cascade or restrict deletes and updates. Foreign keys document relationships and keep them consistent at the storage layer.

Functional Dependency

A functional dependency exists when one set of columns uniquely determines another, written A -> B, meaning each value of A is associated with exactly one value of B. Functional dependencies are the formal basis of normalization: normal forms are defined in terms of which dependencies are allowed. Identifying them correctly drives a clean schema design.

GROUP BY

GROUP BY partitions rows into groups that share the same values in the listed columns so aggregate functions compute one result per group. Every non-aggregated column in the SELECT list must appear in the GROUP BY clause (under standard SQL). It runs after WHERE filtering and before HAVING and SELECT projection.

HAVING

HAVING filters groups produced by GROUP BY using conditions on aggregate values, for example HAVING COUNT(*) > 5. It differs from WHERE, which filters individual rows before grouping. Because it runs after aggregation, HAVING can reference aggregate functions that WHERE cannot.

Index

An index is an auxiliary data structure that speeds up row lookup at the cost of extra storage and slower writes, since the index must be maintained on every change. Common types include B-tree (ordered lookups and ranges), hash (equality only), GIN/GiST (full-text, arrays, geometry in PostgreSQL), and bitmap. The query planner decides whether using an index is cheaper than a sequential scan.

Isolation Level

An isolation level defines how visible the effects of concurrent transactions are to each other, trading consistency for concurrency. The SQL standard defines Read Uncommitted, Read Committed, Repeatable Read, and Serializable, which progressively forbid dirty reads, non-repeatable reads, and phantom reads. Most databases default to Read Committed; Serializable gives the strongest guarantee at the highest contention cost.

JOIN

A join combines rows from two or more tables based on a related column, producing a single result set. INNER JOIN returns only matching rows; LEFT/RIGHT OUTER JOIN keeps unmatched rows from one side with NULLs; FULL OUTER JOIN keeps unmatched rows from both; CROSS JOIN produces the Cartesian product. The join condition (usually in ON) determines which rows pair up.

Materialized View

A materialized view stores the precomputed result of a query physically, unlike a regular view which is recomputed on every reference. It speeds up expensive aggregations and joins at the cost of staleness, since it must be refreshed (manually or on a schedule). PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY to avoid locking readers during refresh.

MVCC (Multi-Version Concurrency Control)

MVCC lets readers and writers proceed without blocking each other by keeping multiple versions of a row and showing each transaction a consistent snapshot. PostgreSQL and Oracle use MVCC so a SELECT never waits on an UPDATE of the same row. The cost is the need to clean up obsolete row versions, handled by vacuuming in PostgreSQL.

Normalization

Normalization is the process of structuring a relational schema to reduce redundancy and avoid update, insertion, and deletion anomalies by decomposing tables according to functional dependencies. The common normal forms are 1NF, 2NF, 3NF, and BCNF, each removing a specific class of anomaly. A well-normalized schema stores each fact once, simplifying writes.

NULL

NULL represents the absence of a value, not zero or an empty string, and follows three-valued logic where comparisons with NULL yield UNKNOWN. Because of this, column = NULL is never true; you must use IS NULL or IS NOT NULL. Aggregate functions like COUNT(column) skip NULLs, while COUNT(*) counts all rows.

OLAP

OLAP (Online Analytical Processing) describes workloads that scan and aggregate large volumes of historical data for reporting and analytics, favouring read throughput over transactional latency. OLAP systems often use columnar storage, star schemas, and denormalized fact/dimension models. They contrast with OLTP, which handles many small concurrent transactions.

OLTP

OLTP (Online Transaction Processing) describes workloads of many short, concurrent read/write transactions, such as order processing or banking, where low latency and ACID correctness matter most. OLTP schemas are typically normalized and heavily indexed for point lookups. They contrast with OLAP analytical workloads.

ORM (Object-Relational Mapping)

An ORM maps relational tables to objects in an application language, generating SQL for common operations so developers work with entities instead of raw queries. ORMs like Hibernate, Prisma, and SQLAlchemy speed up CRUD development but can hide performance problems such as the N+1 query pattern. Knowing the SQL the ORM generates is essential to using it well.

Partitioning

Partitioning splits one logical table into multiple physical pieces (partitions) by range, list, or hash on a partition key, so queries and maintenance touch only relevant partitions. It improves manageability (dropping a whole partition is fast) and can improve query performance through partition pruning. It is distinct from sharding, which spreads partitions across separate servers.

Prepared Statement

A prepared statement is a SQL template parsed and planned once, then executed many times with different bound parameters. It improves performance by reusing the plan and, crucially, prevents SQL injection because parameter values are never concatenated into the SQL text. Drivers expose it through parameter placeholders like ? or $1.

Primary Key

A primary key is the column or set of columns that uniquely identifies each row in a table and cannot be NULL. A table has at most one primary key, which the database enforces with a unique index. It is the anchor that foreign keys reference to express relationships.

Query Optimizer

The query optimizer is the database component that chooses an execution plan for a SQL statement, estimating the cost of alternative join orders, access methods, and algorithms using table statistics. Because SQL is declarative, two equivalent queries may produce very different plans. Reading EXPLAIN output and keeping statistics fresh are how you collaborate with the optimizer.

Referential Integrity

Referential integrity is the guarantee that every foreign key value refers to an existing row in the referenced table, so there are no dangling references. The database enforces it by rejecting orphaning operations or by cascading them according to the foreign key's referential action. It keeps relationships between tables consistent.

Replication

Replication copies data from a primary database to one or more replicas, providing read scaling, high availability, and disaster recovery. Synchronous replication waits for replicas to acknowledge before committing (stronger durability, higher latency); asynchronous replication does not (lower latency, possible data loss on failover). Read replicas can serve reporting traffic to offload the primary.

Row

A row (or tuple) is a single record in a table, holding one value for each column. Rows are the unit of data that DML statements insert, update, and delete. In set-based SQL thinking, a table is an unordered set of rows and queries operate on whole sets at once.

Savepoint

A savepoint marks a point within a transaction to which you can roll back without aborting the entire transaction, using SAVEPOINT name and ROLLBACK TO SAVEPOINT name. It enables partial rollback of a multi-step operation while keeping earlier work. It is useful for retry logic and for isolating risky sub-operations.

Schema

A schema is the formal definition of a database's structure, including tables, columns, types, constraints, indexes, and views. In PostgreSQL and others, schema is also a namespace that groups objects within a database. A migration changes the schema in a controlled, versioned way.

Sequence

A sequence is a database object that generates a monotonically increasing series of numbers, commonly used to populate surrogate primary keys. It is independent of any table and guarantees uniqueness even under concurrency. PostgreSQL exposes it via nextval()/currval(), and SERIAL/IDENTITY columns are syntactic sugar over sequences.

Sharding

Sharding horizontally partitions data across multiple independent database servers (shards) by a shard key, so each shard holds a subset of the rows. It scales writes and storage beyond a single machine but adds complexity: cross-shard joins and transactions are hard, and the shard key choice is critical. It differs from replication, which copies the same data.

SQL Injection

SQL injection is a vulnerability where attacker-controlled input is concatenated into a SQL statement, letting the attacker alter its meaning to read, modify, or destroy data. It is prevented by using parameterised (prepared) statements so input is always treated as data, never as code. It remains one of the most common and dangerous web vulnerabilities.

Stored Procedure

A stored procedure is a named, precompiled block of SQL and procedural logic stored in the database and invoked by name. It can encapsulate complex multi-statement operations close to the data, reducing round trips, but it moves business logic into the database where it is harder to test and version. Use it for data-intensive operations, not as a general application layer.

Surrogate Key

A surrogate key is an artificial, system-generated identifier (such as an auto-increment integer or UUID) used as the primary key instead of a natural business value. It is stable, compact, and never needs to change when business attributes do, which keeps foreign keys simple. The trade-off is an extra column with no business meaning.

Transaction

A transaction is a unit of work executed as a single atomic operation, bracketed by BEGIN and COMMIT (or ROLLBACK). It provides the ACID guarantees so partial work is never visible and a failure leaves the database unchanged. Grouping related statements into one transaction is how you keep multi-step operations consistent.

Trigger

A trigger is procedural code the database executes automatically in response to an INSERT, UPDATE, or DELETE on a table, before or after the event. Triggers are used for auditing, derived-column maintenance, and enforcing complex rules. They run implicitly, which makes side effects easy to overlook, so they should be used sparingly and documented clearly.

UNIQUE Constraint

A UNIQUE constraint guarantees that no two rows share the same value in the constrained column(s), allowing one or more NULLs depending on the database. It is enforced with a unique index. Unlike a primary key, a table can have multiple unique constraints and they may permit NULLs.

View

A view is a named, stored query that behaves like a virtual table, computed on each reference. It encapsulates complex joins and filters behind a simple name, supports access control by exposing a restricted projection, and decouples consumers from the underlying schema. Unlike a materialized view, a plain view stores no data and always reflects the current base tables.

Window Function

A window function computes a value across a set of rows related to the current row (the window) without collapsing them into a single output row, using the OVER (...) clause. Functions like ROW_NUMBER(), RANK(), LAG(), LEAD(), and running SUM() enable rankings, running totals, and period-over-period comparisons. Unlike aggregates with GROUP BY, window functions preserve every input row.

Write-Ahead Log (WAL)

A write-ahead log records every change before it is applied to the data files, so the database can recover a consistent state after a crash by replaying or undoing logged changes. It underpins durability (the D in ACID) and is the basis for point-in-time recovery and physical replication. PostgreSQL calls it the WAL; other systems call it the redo log or transaction log.