Sheet ⁨08⁩ · ⁨Glossary⁩Surveyed ⁨2026⁩

Glossary

Databases & SQL

Foundational relational database and SQL terms: tables and keys, the join types, indexes and query plans, transactions and isolation levels, normalization, and the managed-service vocabulary you meet in the cloud.

38 TermsPublished: 16 Sept 2026Updated: 19 Sept 2026

Databases #sql #databases #postgresql #indexes #transactions #normalization

Markdown for AI

Most confusion about SQL is vocabulary rather than syntax. People know how to write a join and still get caught by a LEFT JOIN that quietly behaves like an INNER JOIN, because nobody explained that a WHERE clause on the right-hand table filters away the very NULL rows the LEFT JOIN was there to keep.

The terms below cover structure and keys, the join types, indexes and query plans, transactions and isolation, and the operational vocabulary you meet once a database leaves your laptop.

The five joins as set operations, a worked example of primary and foreign keys with a junction table, the three normal forms in one line each, and what an index actually is.

A handful of these terms only start to matter once someone else runs the database for you. Managed services rename things, and two of the renames cause real outages: a Multi-AZ standby is not a read replica, and a replica that lags is a property to design around rather than a bug to fix.

Where each term lands in a real deployment: synchronous standby for failover, asynchronous replica for read scaling, a proxy holding the connection pool, and the read-your-own-writes problem that appears the moment you split reads from writes.

If you only take two things from the list, take these. An index is a trade, not a free improvement, so measure with EXPLAIN ANALYZE before adding one and check afterwards that the planner is actually using it. And a transaction’s isolation level is a real decision with a default that differs between PostgreSQL and MySQL, which is why the same code can be correct on one and subtly wrong on the other.

Design

Normalization

Organising data so each fact is stored once, which removes update anomalies. First normal form means one value per column. Second means every non-key column depends on the whole key. Third means no non-key column depends on another non-key column.

Example

Storing a customer's address on every order row means an address change requires updating many rows and risks disagreement between them.

Denormalization

Deliberately duplicating data to avoid joins or recomputation at read time. Valid as an optimisation after measuring a real problem, and it makes the duplicate a cache that needs an invalidation strategy.

Example

A stored order_count on customers is fast to read and wrong the moment an order is deleted without updating it.

View vs materialized view

A view is a stored query that runs every time you select from it, so it is always current and costs the same as the query. A materialized view stores actual result rows, so reads are fast and the data is stale until refreshed.

Example

REFRESH MATERIALIZED VIEW CONCURRENTLY avoids locking readers during the refresh, at the cost of needing a unique index.

Common table expression (CTE)

A named subquery declared with WITH, used to break a complex query into readable steps. RECURSIVE variants walk hierarchies such as trees and graphs.

Example

In older PostgreSQL a CTE was always materialised, which made it an optimisation fence. Since version 12 it can be inlined, and MATERIALIZED or NOT MATERIALIZED lets you choose.

Code Snippet

1
WITH RECURSIVE subordinates AS (
2
  SELECT id, name, manager_id FROM employees WHERE id = 1
3
  UNION ALL
4
  SELECT e.id, e.name, e.manager_id
5
  FROM employees e
6
  JOIN subordinates s ON e.manager_id = s.id
7
)
8
SELECT * FROM subordinates;

Window function

A function computed across a set of rows related to the current row, without collapsing them into one row as GROUP BY does. Useful for running totals, rankings, and comparisons to the previous row.

Example

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) to pick each customer's latest order.

Code Snippet

1
SELECT
2
  customer_id,
3
  total,
4
  sum(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total,
5
  lag(total)  OVER (PARTITION BY customer_id ORDER BY created_at) AS previous_total
6
FROM orders;

Joins

INNER JOIN

Returns only rows with a match on both sides. Unmatched rows from either table disappear from the result.

Example

Customers with no orders are absent from customers INNER JOIN orders.

LEFT JOIN (LEFT OUTER JOIN)

Returns every row from the left table, with NULLs where the right table has no match. The standard way to ask "all X, plus their Y if any."

Example

All customers with their order count, including the zeroes.

Code Snippet

1
-- Correct: condition on the right table goes in ON
2
SELECT c.name, count(o.id) AS paid_orders
3
FROM customers c
4
LEFT JOIN orders o
5
  ON o.customer_id = c.id AND o.status = 'paid'
6
GROUP BY c.name;
7

8
-- Wrong: this silently becomes an INNER JOIN, because
9
-- NULL = 'paid' is never true, so unmatched rows are filtered out.
10
-- WHERE o.status = 'paid'

RIGHT JOIN / FULL OUTER JOIN

RIGHT JOIN keeps every row from the right table; FULL OUTER JOIN keeps unmatched rows from both sides. RIGHT JOIN is rare in practice because swapping the table order gives a LEFT JOIN, which most people find easier to read.

Example

FULL OUTER JOIN is useful for reconciliation, finding records present in one system and not the other.

CROSS JOIN (Cartesian product)

Pairs every row on the left with every row on the right, producing rows equal to the product of both counts. Occasionally intentional, for generating date series or combinations, and otherwise the result of a forgotten join condition.

Example

A missing ON clause turning 5,000 by 5,000 rows into 25 million.

Self join

Joining a table to itself using different aliases, to relate rows within one table.

Example

Finding each employee's manager when both live in the same employees table.

Code Snippet

1
SELECT e.name AS employee, m.name AS manager
2
FROM employees e
3
LEFT JOIN employees m ON e.manager_id = m.id;

Keys

Primary key

The column or set of columns that uniquely identifies each row. It is implicitly NOT NULL and unique, and a table can have at most one.

Example

id BIGSERIAL PRIMARY KEY, or a composite PRIMARY KEY (order_id, product_id) on a junction table.

Code Snippet

1
CREATE TABLE customers (
2
  id         BIGSERIAL PRIMARY KEY,
3
  email      TEXT NOT NULL UNIQUE,
4
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
5
);

Surrogate key vs natural key

A surrogate key is a meaningless generated identifier such as an integer or UUID. A natural key is real-world data used as the identifier, such as an email or ISBN. Natural keys tend to become a problem because real-world values change, and a primary key change cascades to every referencing row.

Example

Using email as a primary key works until someone changes their email address.

Foreign key

A column that references the primary key of another table, and a constraint the database enforces. It prevents rows pointing at records that do not exist, and defines what happens when the referenced row is deleted.

Example

ON DELETE CASCADE deletes the children; ON DELETE RESTRICT blocks the parent delete; ON DELETE SET NULL orphans them deliberately.

Code Snippet

1
CREATE TABLE orders (
2
  id          BIGSERIAL PRIMARY KEY,
3
  customer_id BIGINT NOT NULL
4
    REFERENCES customers(id) ON DELETE RESTRICT,
5
  total       NUMERIC(10,2) NOT NULL CHECK (total >= 0)
6
);

Referential integrity

The guarantee that every foreign key value points at a row that exists. Enforced by the database, it makes a whole class of orphaned-data bug impossible rather than merely unlikely.

Example

Disabling foreign keys for bulk-load speed and never re-enabling them is how databases quietly accumulate rows referencing nothing.

Composite key

A primary key made of more than one column, where the combination is unique even though each column individually is not.

Example

A order_items table keyed on (order_id, product_id), so a product appears at most once per order.

Operations

Connection pool

A set of reused database connections. Establishing a connection is expensive, and databases have a hard connection limit, so a pool bounds concurrency and removes per-request setup cost.

Example

Serverless functions scaling to 500 concurrent instances will exhaust a database's connection limit without an external pooler such as PgBouncer or RDS Proxy.

Replication lag

How far behind a read replica is from its primary, because replication is asynchronous. A read from a replica may not include a write that already committed on the primary.

Example

Write then immediately read from a replica and the row may be missing. Route reads to the primary briefly after a write, or read from the primary for data the same request changed.

Multi-AZ standby vs read replica

A Multi-AZ standby exists for durability and automatic failover, replicates synchronously, and serves no queries. A read replica exists to scale reads, replicates asynchronously, and can return stale data. They solve different problems and are not substitutes.

Example

Adding read replicas does not improve your recovery objective, and adding a standby does not reduce load on the primary.

Sharding vs partitioning

Partitioning splits one logical table into pieces inside a single database, usually by range or list, so queries touch fewer rows and old data can be dropped cheaply. Sharding splits data across separate databases, which scales writes and makes cross-shard joins and transactions hard.

Example

Partitioning events by month lets you DROP an old partition instantly instead of running a slow DELETE.

OLTP vs OLAP

Online transaction processing handles many small reads and writes with low latency, which suits a row store. Online analytical processing scans large volumes to aggregate, which suits a column store. Running heavy analytics against your transactional primary is how you cause an incident.

Example

PostgreSQL and MySQL are OLTP; Redshift, BigQuery and Snowflake are OLAP.

Performance

Index

A separate sorted structure, usually a B-tree, holding some columns plus a pointer back to the row. It makes matching rows findable without scanning the table, at the cost of slower writes and more disk, since every INSERT and UPDATE must also maintain the index.

Example

An index on orders(customer_id) turns "all orders for this customer" from a full scan into a lookup.

Code Snippet

1
CREATE INDEX idx_orders_customer ON orders (customer_id);
2

3
-- Composite: order matters. This serves queries filtering on
4
-- customer_id, or on customer_id AND created_at, but NOT
5
-- queries filtering on created_at alone.
6
CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at DESC);

Covering index

An index containing every column a query needs, so the database answers from the index alone and never touches the table. In PostgreSQL this is an index-only scan.

Example

With INCLUDE (status), a query selecting only customer_id and status never visits the heap.

Code Snippet

1
CREATE INDEX idx_orders_cover
2
  ON orders (customer_id) INCLUDE (status, total);

Full table scan (sequential scan)

Reading every row to find matches. Not automatically bad: on a small table, or when a query genuinely needs most rows, it beats an index lookup because sequential reads are cheaper per row than random ones.

Example

The planner will correctly ignore your index when a query returns 80% of the table.

Query plan / EXPLAIN

The database's chosen strategy for executing a query. EXPLAIN shows it, EXPLAIN ANALYZE actually runs the query and reports real timings and row counts alongside the estimates.

Example

A large gap between estimated and actual row counts usually means stale statistics, and ANALYZE is the fix.

Code Snippet

1
EXPLAIN (ANALYZE, BUFFERS)
2
SELECT c.name, count(o.id)
3
FROM customers c
4
LEFT JOIN orders o ON o.customer_id = c.id
5
GROUP BY c.name;
6

7
-- Read it inside out. Watch for: Seq Scan on a big table,
8
-- rows=1 estimated vs rows=90000 actual, and Nested Loop
9
-- over a large outer relation.

Cardinality

The number of distinct values in a column. High cardinality columns such as email make selective, useful indexes; low cardinality columns such as a boolean flag usually do not, because a lookup still matches half the table.

Example

An index on is_active rarely helps. A partial index WHERE is_active = false might, if false is rare.

N+1 query problem

Running one query to fetch a list, then one more query per item in that list. Each query is fast and the total is not, and it is the single most common performance bug in ORM-backed applications.

Example

Fetching 100 posts then loading each post's author separately is 101 queries. Eager loading, such as Rails' includes or Django's select_related, makes it two.

SQL basics

SELECT

Retrieves rows. Logical evaluation order is FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT, which explains why a SELECT alias cannot be used in WHERE but can be used in ORDER BY.

Example

SELECT total * 2 AS doubled FROM orders WHERE doubled > 10 fails; ORDER BY doubled works.

WHERE vs HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Putting an aggregate in WHERE is an error, and putting a row condition in HAVING works but does more work than necessary.

Example

WHERE status = 'paid' filters rows; HAVING count(*) > 5 filters groups.

Code Snippet

1
SELECT customer_id, count(*) AS orders
2
FROM orders
3
WHERE status = 'paid'      -- rows, before grouping
4
GROUP BY customer_id
5
HAVING count(*) > 5;       -- groups, after aggregating

Security

SQL injection

Interpolating untrusted input into SQL text so an attacker can change what the query does. The fix is parameterised queries, which send the SQL and the values separately so values are never parsed as SQL. Escaping by hand is not a fix.

Example

Never build SQL with string concatenation, not even for an integer, and not even for an internal tool.

Code Snippet

1
// Vulnerable: the input becomes part of the statement
2
db.query(`SELECT * FROM users WHERE email = '${email}'`);
3

4
// Safe: the value is sent separately and never parsed as SQL
5
db.query('SELECT * FROM users WHERE email = $1', [email]);

Structure

Table

A collection of rows sharing the same columns. The column definitions and their types are the schema; the rows are the data.

Example

A customers table with columns id, email, name, and created_at.

Row (record / tuple)

A single entry in a table. In relational theory rows have no inherent order, which is why a query without ORDER BY can return results in any order and is allowed to change that order between runs.

Example

Relying on "the order rows were inserted" without ORDER BY is a bug waiting for a query plan change.

Column (field / attribute)

A named, typed slot present in every row. The type is a constraint, not just documentation, and the database will refuse values that do not fit.

Example

total NUMERIC(10,2) refuses the string 'free' and, unlike a float, will not silently lose cents.

Transactions

Transaction

A group of statements treated as a single unit. Either all of them take effect or none do. BEGIN starts one, COMMIT makes it permanent, ROLLBACK discards it.

Example

Debiting one account and crediting another must be one transaction, or a crash between them loses money.

Code Snippet

1
BEGIN;
2
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
3
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
4
COMMIT;  -- or ROLLBACK, and neither update happened

ACID

Atomicity, all or nothing. Consistency, constraints hold before and after. Isolation, concurrent transactions do not observe each other's partial work. Durability, a committed transaction survives a crash.

Example

Consistency here means your declared constraints, which is narrower than the everyday sense of the word.

Isolation level

How much concurrent transactions may see of each other. From weakest to strongest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Stronger levels prevent more anomalies and increase contention and retries.

Example

PostgreSQL defaults to READ COMMITTED; MySQL InnoDB defaults to REPEATABLE READ. Porting queries between them without noticing this causes real bugs.

Dirty read, non-repeatable read, phantom read

The three classic anomalies. A dirty read sees another transaction's uncommitted change. A non-repeatable read gets a different value when re-reading the same row. A phantom read gets different rows when re-running the same query because someone inserted or deleted matching rows.

Example

READ COMMITTED prevents dirty reads only. REPEATABLE READ also prevents non-repeatable reads. SERIALIZABLE prevents all three.

Optimistic vs pessimistic locking

Pessimistic locking takes a lock up front so nobody else can interfere, using SELECT FOR UPDATE. Optimistic locking takes no lock, and instead checks at write time that nothing changed, usually with a version column, then retries on conflict. Pessimistic suits short high-contention updates; optimistic suits low contention and long think-time.

Example

An editing form a user has open for ten minutes wants optimistic locking, not a ten minute row lock.

Code Snippet

1
-- Pessimistic: block others until commit
2
BEGIN;
3
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
4
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
5
COMMIT;
6

7
-- Optimistic: no lock, detect the collision instead
8
UPDATE documents
9
   SET body = $1, version = version + 1
10
 WHERE id = $2 AND version = $3;
11
-- 0 rows affected means someone else won. Re-read and retry.

Deadlock

Two transactions each holding a lock the other needs, so neither can proceed. The database detects the cycle and aborts one with an error. The standard prevention is to acquire locks in a consistent order everywhere.