Glossary

142 terms from PostgreSQL Beyond the Basics, in plain words, each with the chapter that teaches it.

Search the glossary

#

@@ (text search match operator): The operator that asks whether a tsvector matches a tsquery. For example, `search @@ websearch_to_tsquery('english','chipped mug')` finds the matching reviews.
\gexec: A psql command, written in place of the final semicolon, that runs each value the query returns as its own SQL statement. It is used to generate repetitive statements with a query, such as creating many partitions at once.

A

ACCESS EXCLUSIVE: The strongest table lock mode in Postgres, which conflicts with every other mode, even the `ACCESS SHARE` lock taken by a plain `SELECT`. Most `ALTER TABLE`, `DROP` and `TRUNCATE` statements take it, so they must wait for all other queries on the table and make later queries wait behind them.
Advisory locks: Locks an app takes on a number of its own choosing, not on a table or row. Session-level ones break under PgBouncer's transaction mode.
Aggregate nodes (HashAggregate, GroupAggregate): Plan steps that do GROUP BY work. HashAggregate collects groups in a hash table, while GroupAggregate needs rows already sorted by the group key.
ALTER SYSTEM: A command that saves a server setting to `postgresql.auto.conf` in the data directory, where it overrides postgresql.conf. You apply it with `pg_reload_conf()`.
ANALYZE: A command that samples a table and stores the statistics the planner uses for row estimates. Don't confuse it with the `ANALYZE` option of EXPLAIN, which actually runs the statement.
Append: A plan node that hands on the rows of each child scan in turn. When a query covers two monthly partitions, it sits above their two scans.
archive_mode (continuous archiving): A Postgres setting that makes the server copy each finished WAL (write-ahead log) file to separate, safe storage as it fills. The archived WAL files can be used for backups and point-in-time recovery.
Asynchronous replication: The default replication mode, in which the primary doesn't wait for the replica. The replica can therefore be a moment behind.
auto_explain: A module bundled with Postgres that logs the plans of slow queries.
Autovacuum: A background process that runs VACUUM and ANALYZE on a table once enough rows have changed. The scale factor (0.2 by default) sets what share of the table counts as enough. Never turn it off.

B

B-tree: The default index type, which keeps values sorted. It handles `=`, ranges and sorting, and it can be read forwards or backwards.
Bitmap Index Scan, Bitmap Heap Scan: A two-step scan: the index step marks the pages holding matches, and the heap step reads those pages in page order. It rechecks the condition on each row (`Recheck Cond`). BitmapOr combines two such searches.
BRIN: A very small index that stores only the lowest and highest value for each range of table pages. It works when the column's order matches the table's physical order, like event times in `events`.
Buffers (EXPLAIN BUFFERS): An EXPLAIN option that shows how many pages came from Postgres's memory cache (`hit`) and how many had to be fetched (`read`). Since PostgreSQL 18, EXPLAIN ANALYZE shows it by default.

C

Cache hit ratio: The share of page reads found in Postgres's memory instead of read from disk. A busy shop should stay above about 99%.
Cluster: One Postgres server's whole set of databases and files in a single data directory. Streaming replication and pg_upgrade both work on a whole cluster.
Cost (startup cost, total cost): The planner's own estimate of work, shown as `cost=0.29..8.30`. The first number is the cost before the first row comes out, and the second is the cost to return all rows. It is not in milliseconds.
Covering index: An index that holds every column a query needs, by adding extra columns with `INCLUDE`. Those columns are stored but not sorted or searched, and they let the query use an Index Only Scan.
CREATE EXTENSION: The command that switches on an add-on in one database, such as `CREATE EXTENSION pg_trgm;`.
CREATE INDEX CONCURRENTLY: A way to build or drop an index without blocking writes on the table. It takes longer and can't run inside a transaction block.
CREATE STATISTICS: A command that creates extended statistics on a combination of columns, for example `(dependencies) ON city, state`.

D

Data checksums: A check value stored on every page that catches disk corruption. `initdb` turns it on by default since version 18, and a cluster being upgraded must match the old setting.
Data directory: The folder that holds all of a cluster's files, including `pg_wal` and `postgresql.auto.conf`.
Dead row, bloat: A dead row is an old row version that no transaction needs any more. It stays until VACUUM, and space left unused afterwards is bloat.
Deadlock: Two transactions each waiting for a lock the other holds. After `deadlock_timeout` (1 second), Postgres cancels one so the other can continue. Retry the cancelled one, and lock rows in a fixed order to avoid it.
Default partition: A partition that catches every row that fits no other partition. It is a safety net, but it blocks `DETACH PARTITION ... CONCURRENTLY`.
default_statistics_target: The setting (100 by default) that controls how many rows ANALYZE samples. You can raise it for a single column with `ALTER TABLE ... SET STATISTICS`.

E

effective_cache_size: A planner setting that allocates no memory. It tells the planner how much memory is probably available for caching, so a higher value makes index scans look cheaper.
Estimate vs actual: The comparison of the planner's guessed `rows=` with the real rows in EXPLAIN ANALYZE. A gap of 10× or more is the usual reason for a bad plan.
Exclusion constraint: A rule, supported by GiST indexes, that stops two rows from overlapping, such as two bookings for the same time.
EXPLAIN: A command that shows the plan Postgres chooses for a query, as a tree of nodes. Plain EXPLAIN shows only estimates, and EXPLAIN ANALYZE runs the query and adds the actual results.
Expression index: An index built on a function's result, such as `(lower(email))`. The query's WHERE must use the same expression for the planner to use it.
Extended statistics: Statistics on combinations of columns that tell the planner the columns are related. For example, every Portland customer in Kettle & Crane is in Oregon, so the planner shouldn't multiply the two shares.
Extension: An add-on shipped with PostgreSQL that you switch on per database with CREATE EXTENSION. Examples are pg_trgm and pg_stat_statements.

F

Failover: Switching to the replica when the primary fails. You promote the replica with `pg_promote()` or `pg_ctl promote`, and rebuild the old primary as a replica.
FOR UPDATE: A SELECT clause that locks the rows it returns, so other writers wait for your transaction. It is used to avoid lost updates and, with SKIP LOCKED, to build work queues.
Frozen, wraparound: Every transaction gets a number, and the counter wraps around after about 2 billion. VACUUM marks old rows frozen so they stay visible after wraparound. If VACUUM falls behind, Postgres stops accepting writes.
Full-text search: Searching by words (lexemes) rather than characters. A text search configuration such as `'english'` supplies the language rules.

G

Gather, worker: A worker is an extra process that reads part of a table in a parallel query. Gather collects the workers' rows, and Gather Merge does the same while keeping them sorted.
generated column: A column whose value Postgres computes itself from an expression on the row's other columns, every time the row is inserted or updated. A `STORED` generated column keeps the result on disk, for example a review's `tsvector` computed once so it can be indexed and searched.
GIN: An index type that maps each word, trigram or key to the rows containing it. It is used for jsonb, trigram search and full-text search.
GiST, SP-GiST, hash index: Less common index types. GiST handles overlapping or nearby values such as ranges and geometry, SP-GiST suits data that splits into non-overlapping parts, and a hash index supports only `=`.

H

Hash Join, Merge Join, Nested Loop: The three join methods. A Nested Loop searches the second input once per row of the first, a Hash Join probes a hash table of the second input, and a Merge Join walks two sorted inputs side by side.
Heap: A table's own storage, as opposed to its indexes. An Index Scan finds rows in the index and then fetches them from the heap.
Heap Fetches: The number of rows an Index Only Scan still had to check in the table because the visibility map didn't cover their pages. Zero is ideal.
Histogram: A statistic in `pg_stats` that splits a column into buckets holding equal numbers of rows. The planner uses it for values not among the most common.

I

Idle in transaction: A session that ran BEGIN and then stopped sending commands. It does no work but holds its locks and stops VACUUM from cleaning up.
idle_in_transaction_session_timeout: A setting that lets the server end sessions that sit idle in a transaction for too long, rolling back their work.
Index Only Scan: A scan that answers from the index alone, without visiting the table, when every needed column is in the index. It relies on the visibility map.
Index Scan: A scan that finds matching rows in an index and then fetches each one from the table.
initdb: The program that creates a new, empty Postgres cluster in a data directory.
Invalid index: An index left behind when a concurrent build fails. It slows writes but is never used, so drop it and build it again.
Isolation level: How much a transaction sees of other transactions' work. In read committed each statement sees fresh data, repeatable read gives one snapshot for the whole transaction, and serializable also guarantees a one-after-another result.

K

Keyset pagination: Paging by remembering the last row shown and asking for rows after it, instead of using OFFSET. It reads exactly one page of rows on any page, whereas OFFSET reads and discards all the earlier rows.

L

Lexeme, stemming, stop words: A lexeme is a word's base form, such as `chip` for "chipped". Stemming reduces words to it, and stop words like "the" are dropped because they are too common to help.
Limit: A plan node that stops after the requested number of rows, so the scans below it may read only a few rows.
LISTEN / NOTIFY: A way for sessions to subscribe to and send messages to each other. It breaks under PgBouncer's transaction mode because it belongs to a session.
Lock modes, lock queue: Lock modes are the different strengths of table lock. Locks are granted in order, so a waiting request, such as an ALTER TABLE, makes later queries queue behind it.
lock_timeout: The longest a statement waits for a lock before giving up with an error. Set it in every migration so a waiting ALTER TABLE doesn't block everyone.
log_min_duration_statement: A setting that makes the server log every statement taking at least that long. It is the switch for the slow-query log.
Logical replication: Copying chosen tables as rows, using a publication on the source and a subscription on the target. It works between different major versions, which makes it a near-zero-downtime upgrade method.
Lossy: A bitmap that lists whole pages instead of exact rows, as with BRIN. Postgres must recheck every row on those pages.
Lost update: One transaction silently overwriting another's change. For example, two sales of Speckled Blue Mugs both write back a stale stock number. Fix it with `SET stock = stock - n` or `SELECT ... FOR UPDATE`.
LSN: Log sequence number: a position in the WAL that only ever grows. Subtracting two LSNs shows how many bytes of WAL a replica hasn't replayed.

M

Major, minor version: In 18.6, 18 is the major version (yearly, new features, changed data files) and .6 is the minor version (bug and security fixes about every three months). A minor upgrade only replaces the programs, and a major upgrade converts the data.
max_connections: The setting that caps how many connections the server accepts (100 by default). Past the limit, new clients get `sorry, too many clients already`.
Multicolumn index: An index on several columns, where order matters. Put columns tested with `=` first and the sort or range column last.
MVCC: Multiversion concurrency control: an UPDATE writes a new row version instead of overwriting. Readers and writers therefore don't block each other, and only two writers to the same row wait. `xmin` and `xmax` are hidden columns showing which transactions created and replaced a version.

N

Node: One step in a query plan tree. A node with `->` feeds its rows to its parent, and the lines under it without `->` are its details.
Normalize (pg_stat_statements): How pg_stat_statements replaces constants with placeholders such as `$1`, so calls for customer 42 and customer 7 count as one query.

O

Operator class: A setting on an index column that says how a type is indexed. `gin_trgm_ops` tells GIN to index the trigrams of text.

P

Page: The 8 kB block that tables and indexes are stored in, and the unit Postgres reads and caches.
Parallel query: One query split across worker processes, each reading part of a big table, with Gather collecting their rows. `loops=2` with one worker shows your own process helping too.
Partial index: An index with a `WHERE` condition that covers only some rows, such as Alex's 463 `processing` orders. It is much smaller and is used only when the query includes the same condition.
Partition pruning: Skipping the partitions a query can't need, which requires the partition key in the WHERE clause. It can happen at plan time or at run time.
Partition, partition key: Partitioning splits one big table into smaller tables (partitions) that Postgres treats as one. The partition key is the column that decides where a row goes. Range, list and hash are the ways to split.
Patroni, repmgr: Tools that detect a failed primary and fail over to a replica automatically.
pg_basebackup: A tool that copies a primary's whole data directory. With `-R` it turns the copy into a standby.
pg_blocking_pids(): A function that lists the processes blocking a given process. It is used with `pg_stat_activity` to see who waits for whom.
pg_cancel_backend(), pg_terminate_backend(): Functions that stop a blocker. The first cancels its current statement, and the second closes its whole connection, which is needed for an idle transaction.
pg_ctl: Postgres's own tool for starting, stopping and promoting a server from its data directory.
pg_dump, pg_restore: Command-line tools that export a database or a single table to a file (`pg_dump`) and load such a file back into a database (`pg_restore`, or `psql` for plain SQL files). They are used for backups, for archiving a table such as a detached partition, and for moving small databases between servers or major versions.
pg_hba.conf: The file that says which users may connect from where, including replication connections.
pg_locks: A view listing every lock, with `granted` true or false.
pg_partman: An extension that creates and retires partitions on a schedule for you.
pg_read_all_stats: A built-in role that lets non-superusers see other users' query text in pg_stat_statements.
pg_relation_size, pg_total_relation_size, pg_size_pretty: Functions for sizes. `pg_relation_size` gives the bytes of one table or index, `pg_total_relation_size` adds the table's indexes, and `pg_size_pretty` makes bytes readable.
pg_reload_conf(): A function that makes the running server re-read its settings files without disconnecting anyone.
pg_stat_activity: A view with one row per connection, showing its state, wait event, query and start times. It is how you find blockers, long transactions and idle sessions.
pg_stat_replication: A view on the primary with one row per connected replica. It shows state and `replay_lag`, and you can compute the byte lag from it.
pg_stat_statements: An extension that records every statement the server runs and totals calls and time per normalized query. It must be listed in `shared_preload_libraries` and needs a restart.
pg_stat_user_indexes: A view counting scans per index since statistics were last reset. An index with `idx_scan` 0 after weeks may be unused.
pg_stat_user_tables: A view of per-table counters, including dead rows and when VACUUM and autovacuum last ran.
pg_stats: A view of the statistics ANALYZE collects: distinct values, most common values with their shares, and histograms.
pg_trgm, trigram: A trigram is a group of three characters in a row. The pg_trgm extension splits text into trigrams so a GIN index can speed up `ILIKE '%word%'` and find similar spellings with `similarity()` or `%`.
pg_upgrade: The tool that converts a cluster to a new major version by moving the data files without rewriting them. Run it with `--check` first. `--link`, `--clone`, `--swap` and `--copy` decide how files reach the new cluster.
PgBouncer settings (default_pool_size, max_client_conn): `default_pool_size` is how many server connections PgBouncer opens per database and user. `max_client_conn` is how many clients may connect to it.
PgBouncer, pool modes: PgBouncer is a connection pooler that lets many clients share a few real server connections. The pool mode sets when a client gives its connection back: session (on disconnect), transaction (the usual choice) or statement.
Planner: The part of Postgres that picks the cheapest plan for a query using statistics and cost estimates. EXPLAIN ANALYZE reports Planning Time (choosing the plan) and Execution Time (running it) separately.
postgresql.conf: The server's main settings file. Most changes need only a reload, but a few need a restart.
Prepared statement: A statement a driver parses once and reuses, which belongs to a session. PgBouncer 1.21 and later can track them in transaction mode if `max_prepared_statements` is above 0.
Primary, replica, standby: The primary is the server that takes writes. A replica (or standby) is a read-only copy that follows it by replaying its WAL.
Process (per connection): Postgres starts a separate operating-system process for every connection, each using several megabytes of memory even when idle.

R

random_page_cost: The planner's price for reading a page at random, compared with 1.0 for reading in order. The default of 4.0 suits spinning disks, and about 1.1 is common on SSDs.
Read replica: A replica used to serve read-only work such as Sam's reports. Writes are refused, and results may lag slightly behind the primary.
REINDEX: A command that rebuilds an index that has bloated. With `CONCURRENTLY` it builds a fresh copy, swaps it in and drops the old one without blocking writes.
Reload, restart: A reload re-reads the settings files without disconnecting anyone. A restart stops and starts the server, closing every connection, and only a few settings need one.
Replication lag: How far a replica is behind the primary. With lag, a customer who just saved an address may still see the old one if the next page reads from the replica.
Replication slot: A bookmark that makes the primary keep every WAL file a replica hasn't received yet. An abandoned slot can fill the primary's disk.
Row comparison: A comparison like `(placed_at, order_id) < (a, b)` that compares the first column first and the second only on a tie. It is the core of keyset pagination.
Row version, snapshot: A row version is one version of a row, since each update writes a new one. A snapshot is the set of versions a transaction sees, frozen at a point in time under repeatable read.

S

Sargable: A filter an index can use, from "search argument": the bare column on one side and a value on the other. Wrapping the column in a function, as with `date_trunc('month', placed_at)`, makes it non-sargable.
Seq Scan: A scan that reads every row of a table. It is a problem only when it removes most of what it reads.
Serialization failure: The error (SQLSTATE 40001) raised under repeatable read or serializable when a transaction can't safely continue. It means "try again": roll back and redo the whole transaction.
SET LOCAL: Changes a setting only until the end of the current transaction, unlike a plain `SET`, which lasts for the session. It is safe with PgBouncer's transaction mode.
Shared buffers: Postgres's own memory cache of table and index pages (128 MB by default). A common starting point on a dedicated server is about a quarter of its RAM.
shared_preload_libraries: A postgresql.conf setting that lists modules to load at startup, such as pg_stat_statements. It needs a restart.
SHOW POOLS: A command in PgBouncer's admin console. `cl_active` counts clients with a server connection, `cl_waiting` counts clients queued for one, and `sv_active` counts server connections in use.
SKIP LOCKED, NOWAIT: Options for `FOR UPDATE`. `SKIP LOCKED` skips rows other sessions have locked, so packers each get a different order. `NOWAIT` fails at once instead of waiting.
Slow-query log: The server log of statements that run longer than `log_min_duration_statement`.
Sort Method (quicksort, external merge): A Sort node's report of how it sorted. `quicksort Memory:` means it sorted in memory, and `external merge Disk:` means it spilled to temporary files because `work_mem` was too small.
Spill to disk: What a sort or hash step does when it needs more than `work_mem`: it writes temporary files to disk, shown as `external merge Disk:` or a `Hash` with more than one batch.
SQLSTATE: The five-character error code a driver reports, such as `40001` for a serialization failure, so apps can test for specific errors.
standby.signal: An empty file that tells Postgres to start as a replica. `pg_basebackup -R` creates it.
Statistics target: How many rows ANALYZE samples per column: 300 for each unit of the target (default 100). Raise it for columns with many uneven values.
Streaming replication: A replica receiving the primary's WAL as it is written and replaying it, so it becomes a byte-for-byte copy of the whole cluster.
Synchronous replication: A mode where each COMMIT waits until a named replica also has the WAL. It is safer but slower, and writes stop if that replica goes down.
System columns: Hidden columns on every row, such as `xmin` and `xmax`, that Postgres uses to track row versions.

T

tableoid::regclass: A hidden column, cast to show a table name, that tells which actual partition a row lives in.
ts_headline(): A function that returns text with the matching words marked, such as in `<b>` tags, for a results page. It reads the whole text, so use it only on the rows you show.
ts_rank(), setweight(): `ts_rank()` scores how well a vector matches a query. `setweight()` labels lexemes with a weight from A (most important) to D, so matches on A words rank higher.
tsvector, tsquery: A tsvector is a searchable list of lexemes with positions. A tsquery is a search made of words joined by `&`, `|`, `!` and `<->`.

V

VACUUM: A command that frees dead rows' space for reuse and updates the visibility map. It keeps the file's size and can't run inside a transaction block.
VACUUM FULL: A command that rewrites a table to its smallest size, but holds an ACCESS EXCLUSIVE lock so nobody can even read the table meanwhile.
vacuumdb: A shell tool that runs VACUUM and ANALYZE. After pg_upgrade, it fills in missing planner statistics.
Visibility map: A small per-table record of which pages hold only rows visible to everyone. Index Only Scans use it to skip the table, and VACUUM updates it.

W

WAL, WAL record: The write-ahead log: a description of every change, written to disk before the data files change. It allows crash recovery and replication, and each entry is a WAL record.
websearch_to_tsquery(): A function that turns what a customer types into a tsquery, understanding search-engine style input: plain words mean and, quotes mean a phrase, `or` means or, and `-` means not. It never raises a syntax error.
work_mem: The memory each sort or hash step may use before spilling to temporary files (4 MB by default). Raise it per transaction with `SET LOCAL`, not for the whole server.
Write skew: Two transactions each changing different rows based on the same now-stale read, together breaking a rule. For example, Priya and Alex both go off call and nobody is left. Only serializable catches it.