Glossary
The PostgreSQL terms the documentation assumes you already know
One term per page, defined in the first paragraph, usually shown with one query, and linked to the guide that says what to do about it.
The PostgreSQL documentation is thorough and it is written for people who already speak its language. A release note says a slot was invalidated because the horizon moved; a wait event is named after a lightweight lock on a buffer mapping partition; a vacuum log line mentions a failsafe. Each of those words has one precise meaning, and the manual defines it somewhere, usually not on the page you were reading when you met it.
Every term here is answered in its first paragraph, in the sense an operator needs rather than the sense a source comment would give. Then a short section on why it matters at three in the morning, for most terms one query that shows the thing on a live server with the output pasted in, and a pointer to the guide that carries the full treatment. Where a version moved the view a term lives in, the entry links to the page for that version. Nothing here sells anything; the terms are grouped by the part of the server they belong to.
Vacuum and freezing
- autovacuum versus VACUUM
- VACUUM is a statement you issue against tables you name.
- bloat
- Bloat is the difference between the size of a relation on disk and the size its currently visible rows would need.
- dead tuple
- A dead tuple is a row version that no current or future transaction can see.
- freeze, relfrozenxid
- Freezing is how PostgreSQL retires a transaction id from a row version.
- multixact wraparound
- A multixact is the identifier PostgreSQL issues when more than one transaction holds a lock on the same row at the same time.
- VACUUM FULL
- VACUUM FULL writes a new copy of a table containing only its live rows, rebuilds every index on it, then swaps the new files in and drops the old ones.
- visibility map
- The visibility map is a small file beside every table holding two bits for each of its heap pages.
- xmin horizon
- The xmin horizon is the oldest transaction id that anything in the cluster might still need to see.
Write-ahead log and checkpoints
- checkpoint, timed vs requested
- A checkpoint writes every dirty buffer to disk so that recovery can start from a known point.
- full-page image
- A full-page image is a complete copy of an 8 kB page written into the write-ahead log instead of a description of the change.
- pg_wal growth
- The directory holding the write-ahead log grows when segments are created faster than old ones are released.
- synchronous_commit
- synchronous_commit decides how far a transaction's log record must get before the server reports success to the client.
- write-ahead log
- The write-ahead log is a sequential record of every change made to the database, written and flushed to durable storage before the pages those changes affect are.
Replication
- physical and logical replication
- Physical replication ships the write-ahead log as it stands and replays it block by block, so the standby's data files are identical to the primary's and cannot diverge in any respect.
- replication lag
- Replication lag is how far a standby is behind its primary, and PostgreSQL reports it as three separate distances because a record passes three milestones on the standby.
- replication slot
- A replication slot is a named marker on the sending server recording how far one consumer has read.
- replication slot invalidation
- A replication slot is a server-side bookmark recording how far a consumer has read, and the primary keeps every write-ahead log segment from that point onward so the consumer can resume.
Statistics views
- query fingerprint, query_id
- A query fingerprint is an identifier that collapses every execution of the same statement shape into one row of statistics.
- statistics reset
- A statistics reset sets PostgreSQL's cumulative activity counters back to zero and records when it happened.
- toplevel vs nested statements
- A toplevel statement is one the client sent.
Planner
- bitmap heap scan
- A bitmap heap scan is the plan the executor uses when an index identifies many rows scattered across a table.
- effective_cache_size
- effective_cache_size tells the planner roughly how much memory the buffer pool and the operating system's cache between them are likely to have available for this database's pages.
- generic and custom plans
- A prepared statement is planned with its actual parameter values the first several times it runs, producing a custom plan each time.
- n_distinct
- n_distinct is the planner's estimate of how many distinct values a column contains, recorded per column when the table is analysed.
- sequential scan versus index scan
- A sequential scan reads every page of a table in file order and discards the rows that do not match.
- work_mem
- work_mem is how much memory a single executor node may use before it finishes its work on disk instead.
Indexing
- index-only scan
- An index-only scan answers a query from the index without reading the table, which it can do when every column the query needs is in the index.
Connections
- connection storm
- A connection storm is a feedback loop in which an application responds to database slowness by opening more connections, and the additional connections make the database slower still.
- idle in transaction
- Idle in transaction is the state of a session that opened a transaction, ran at least one statement, and then went quiet without committing or rolling back.
Locking
- deadlock
- A deadlock is a cycle in the graph of who is waiting for whom: each transaction in the cycle holds a lock another one needs, so none of them can move.
- lock modes
- Every statement takes a lock on each table it touches, in one of eight modes.
Storage
- double buffering
- Double buffering is the ordinary situation where the same 8 kB page is held twice in memory: once in PostgreSQL's own shared buffer pool and again in the operating system's page cache.
- HOT update, fillfactor
- A HOT update writes the new row version onto the same heap page as the old one and touches no index.
- MVCC
- Multiversion concurrency control is how PostgreSQL lets readers and writers run at the same time without waiting for each other.
- shared_buffers
- shared_buffers is a single block of shared memory, reserved in full when the server starts and divided into slots the size of a page.
- SLRU
- An SLRU is one of a handful of small fixed-size caches PostgreSQL keeps for data that is not table data: transaction commit status, multixact offsets and members, subtransaction parents, commit timestamps, notify queues and predicate locks.
- temporary files
- A temporary file is what an executor node writes when it needs more memory than it has been granted and has to finish the job on disk.
- TOAST
- TOAST is what happens when a row will not fit in the eight-kilobyte page that has to hold it.
Monitoring
- cache hit ratio
- The cache hit ratio is the share of block requests PostgreSQL answered from its own shared buffer pool rather than asking the operating system for the block.
- wait event
- A wait event names the specific thing a PostgreSQL process is blocked on at the instant you look.
Most of these terms name a signal that fleet observability reads on every cluster, wherever the version keeps it.
Put every Postgres you run on autopilot.
We onboard teams in small batches. Tell us about your fleet and we will reach out when a seat opens. One email, no drip campaign.