Postgres glossary
PostgreSQL terms, explained with SQL you can run
Each entry gives a short definition, an example that runs on stock PostgreSQL and links to the official documentation it is based on.
Row-level security (RLS)
Row-level security lets a table decide, row by row, which rows each role can see or change, using policies created with CREATE POLICY.
Sequential scan
A sequential scan reads every row of a table from start to end. EXPLAIN shows it as Seq Scan; it is the normal choice for small tables or queries that return many rows.
Index scan
An index scan uses an index to find the matching rows and then fetches them from the table. EXPLAIN shows it as Index Scan; an Index Only Scan can skip the table.
Connection pooling
Connection pooling keeps a set of database connections open and shares them among many clients, because each PostgreSQL connection is served by its own server process.
VACUUM and autovacuum
VACUUM reclaims the space taken by dead row versions that updates and deletes leave behind, updates the visibility map and prevents transaction ID wraparound. Autovacuum runs it for you.
Multiversion concurrency control (MVCC)
MVCC lets reads and writes run at the same time: each statement sees a snapshot of the data, and updates write new row versions instead of overwriting rows in place.
Write-ahead log (WAL)
The write-ahead log records every change before it reaches the data files, so PostgreSQL can recover after a crash. WAL also feeds replication and point-in-time recovery.
EXPLAIN and EXPLAIN ANALYZE
EXPLAIN shows how PostgreSQL intends to run a query; EXPLAIN ANALYZE also runs it and reports the actual row counts and timings of each step.
Primary key
A primary key is a column or set of columns that uniquely identifies each row. PostgreSQL enforces it with a unique B-tree index and NOT NULL.
Foreign key
A foreign key requires values in one table to match a row in another, so you cannot point at a row that does not exist. ON DELETE decides what happens when that row goes away.
Transaction isolation levels
The isolation level decides how much a transaction sees of concurrent transactions' changes. PostgreSQL defaults to Read Committed and also offers Repeatable Read and Serializable.
Deadlock
A deadlock happens when two transactions each wait for a lock the other one holds. PostgreSQL detects it and cancels one of them; taking locks in a consistent order prevents it.
B-tree index
The B-tree is PostgreSQL's default index type. It handles equality and range comparisons on sortable data and can return rows already sorted.
GIN index
A GIN (generalized inverted) index maps each element inside a value, such as an array element, a JSONB key or a text search word, to the rows that contain it.
Partial index
A partial index covers only the rows that match a WHERE condition, which keeps it small and fast when queries target a well-defined subset of a table.
Common table expression (CTE)
A common table expression, written with WITH, gives a subquery a name so the rest of the statement can use it like a table. WITH RECURSIVE handles trees and hierarchies.
JSONB
jsonb stores JSON in a decomposed binary format that is fast to query and can be indexed. It is the JSON type most applications should use.
Materialized view
A materialized view stores the result of a query so it can be read like a table. Reads are fast, but the data only changes when you run REFRESH MATERIALIZED VIEW.
Table partitioning
Partitioning splits one logical table into smaller physical tables by range, list or hash, so queries and maintenance touch only the partitions they need.
Upsert (INSERT ... ON CONFLICT)
INSERT ... ON CONFLICT inserts a row or, if that would violate a unique constraint, updates the existing row or skips it, as one atomic step.
Window functions
A window function calculates a value across a set of related rows, such as a rank or a running total, without collapsing those rows into one as GROUP BY does.
Schema
A schema is a namespace inside a database that holds tables, views, functions and other objects. search_path decides where unqualified names are looked up.
Roles and privileges
In PostgreSQL, users and groups are both roles. GRANT gives a role privileges on objects, and a role that is a member of another role can use that role's privileges.
Identity column
An identity column gets its values automatically from an implicit sequence. It is the SQL-standard way to create an auto-incrementing key in PostgreSQL.
Full text search
Full text search matches documents by words rather than exact substrings, using tsvector, tsquery and the @@ operator. A GIN index makes it fast.