SQL glossary

Plain-language definitions of common SQL and PostgreSQL terms.

• ANALYZE

  Modifier that makes EXPLAIN actually run the query and report measured execution times alongside the planner's estimates.

  Command

• Append

  Plan node that concatenates rows from multiple child plans, used for UNION ALL and partitioned tables.

  Plan operator

• Autovacuum

  Background process that automatically runs VACUUM and ANALYZE on tables that accumulate dead tuples or stale statistics.

  Internals

• B-tree

  Default PostgreSQL index type: a balanced search tree of sorted keys that supports equality and range lookups.

  Index

• Bitmap Heap Scan

  PostgreSQL builds a bitmap of matching tuple IDs from one or more indexes, then visits the heap pages once each.

  Operator

• Bitmap Index Scan

  Plan node that builds a bitmap of matching tuple IDs from one or more indexes before fetching rows from the heap.

  Plan operator

• BRIN

  Block Range Index: a tiny PostgreSQL index that stores summary statistics for ranges of heap pages.

  Index

• Composite index

  Index built on two or more columns, useful for queries that filter or sort on the leading columns.

  Index

• Correlated subquery

  Subquery that references columns from the outer query, so it is logically re-evaluated for every outer row.

  Query

• Covering index

  Index that contains every column a query needs, allowing an Index Only Scan with no heap fetch.

  Index

• CTE

  Common Table Expression: a named subquery introduced with WITH that can be referenced like a table inside the same statement.

  Concept

• Deadlock

  Two or more transactions blocked waiting for locks held by each other, broken by PostgreSQL aborting one of them.

  Concept

• EXPLAIN

  PostgreSQL command that shows the planner's intended execution plan for a query without running it.

  Command

• Gather

  Plan node that collects rows from parallel worker processes back into the leader for the final result.

  Plan operator

• GIN

  Generalized Inverted Index: a PostgreSQL index type optimised for columns where each value contains many components, like arrays, JSONB documents, and tsvectors.

  Index

• GiST

  Generalized Search Tree: an extensible PostgreSQL index framework for geometric, range, and full-text data.

  Index

• GroupAggregate

  Plan node that groups rows assuming the input is already sorted on the GROUP BY keys.

  Plan operator

• Hash Join

  Join algorithm that builds an in-memory hash table from one input and probes it with rows from the other.

  Join

• HashAggregate

  Plan node that groups rows by hashing the GROUP BY keys into an in-memory hash table.

  Plan operator

• Index Only Scan

  Plan node that returns rows using only the index, skipping the heap entirely when the visibility map allows.

  Plan operator

• Index Scan

  PostgreSQL walks an index to find matching rows and then fetches them from the heap.

  Operator

• Isolation level

  Setting that controls how transaction changes become visible to concurrent transactions.

  Concept

• LATERAL join

  Join where the right-hand subquery can reference columns from earlier FROM-clause items, evaluated once per outer row.

  Query

• Limit

  Plan node that stops requesting rows from its child once a row count has been produced.

  Plan operator

• Materialize

  Plan node that caches its child's output so it can be re-read multiple times without re-executing.

  Plan operator

• Materialized view

  View whose result is physically stored on disk and refreshed on demand, trading freshness for query speed.

  Concept

• Memoize

  Plan node introduced in PostgreSQL 14 that caches inner-side lookups during a Nested Loop join.

  Plan operator

• Merge Join

  Join algorithm that consumes two sorted inputs in lockstep and emits matching rows.

  Join

• MVCC

  Multiversion Concurrency Control: PostgreSQL's mechanism that lets readers see a consistent snapshot of the database without blocking writers.

  Concept

• Nested Loop

  Join algorithm that scans the inner relation once for every row of the outer relation.

  Join

• Partial index

  Index built with a WHERE clause so only rows matching the predicate are stored, keeping the index small and selective.

  Index

• Query Plan

  The tree of operators PostgreSQL chose to execute a query, including scans, joins, sorts, and aggregations.

  Concept

• Recursive CTE

  Common Table Expression that references itself, used to walk hierarchies, graphs, and generated sequences.

  Query

• Seq Scan

  Sequential scan: PostgreSQL reads every row in a table from disk to find matches.

  Operator

• Sort

  Plan node that orders its input rows, used for ORDER BY, merge joins, and some grouping operations.

  Plan operator

• TOAST

  The Oversized-Attribute Storage Technique: how PostgreSQL stores values too large to fit in a regular 8 KB heap page.

  Internals

• Transaction

  Group of SQL statements treated as a single atomic unit: either every change commits or every change rolls back.

  Concept

• VACUUM

  PostgreSQL maintenance command that reclaims space from dead tuples and updates the visibility map so MVCC stays efficient.

  Command

• WAL

  Write-Ahead Log: append-only stream of every change PostgreSQL makes, used for durability, crash recovery, and replication.

  Internals

• Window Function

  Function that computes a value over a window of related rows defined by an OVER clause, without collapsing rows like a GROUP BY.

  Concept