OnCallReadyStart learning

PostgreSQL Operations: glossary

143 terms, in plain words, with where each one is taught.

.pgpass (pgpass)
A file in the home directory with hostname:port:database:username:password lines; clients use it so passwords stay off the command line. Must be mode 0600. Lesson 27.4
\copy (COPY (PostgreSQL))
psql's client-side version of COPY: reads or writes the file on the machine where psql runs, with your permissions. COPY to a file runs on the server. Lesson 27.4
ACL (PostgreSQL) (arwdDxtm, access privileges)
The access list on an object, shown as grantee=letters/grantor: a INSERT, r SELECT, w UPDATE, d DELETE, D TRUNCATE, x REFERENCES, t TRIGGER, m MAINTAIN. Lesson 27.7
auto_explain
A module that writes the plan of every statement slower than a threshold into the log. Lesson 27.35
autovacuum
The background launcher and workers that VACUUM and ANALYZE each table when enough of it has changed (by default 50 rows + 20% of the table). Lesson 27.24
B-tree (btree)
The default index type: sorted, for equality and range conditions and ORDER BY; can cover several columns. Lesson 27.32
backend (PostgreSQL) (backend process, client backend)
The server process that serves one client connection, from login until the client disconnects - busy or idle. Lesson 27.1
backfill (batched backfill)
Filling a new column for existing rows - in small committed batches, not one huge UPDATE. Lesson 27.38
bloat
Space in tables and indexes taken by dead tuples or reusable empty space; makes scans read more pages. Lesson 27.24
blocking chain (root blocker, lock chain)
Sessions waiting for sessions waiting for one session. The root is the blocker that is not blocked itself. Lesson 27.21
checkpoint (checkpointer, max_wal_size)
Writing all changed pages to the data files so older WAL is not needed for crash recovery. Every checkpoint_timeout or max_wal_size of WAL. Lesson 27.47
cluster (PostgreSQL) (PostgreSQL cluster, database cluster, 18/main)
One PostgreSQL server instance: one data directory, one port, one set of roles, holding several databases. Not a group of machines. Ubuntu names them version/name: 18/main, 18/replica. pg_lsclusters lists them. Lesson 27.1
composite index (multi-column index, multicolumn index)
An index on several columns. Equality columns go first, then the range or ORDER BY column. Lesson 27.32
conf.d (PostgreSQL)
A directory included at the end of postgresql.conf; your own settings go in a file there so the shipped file stays untouched. Lesson 27.2
connection budget
The sum of every client's instances x pool size plus headroom, which must fit in max_connections minus the reserved slots. Lesson 27.15
conninfo (connection URI (PostgreSQL), libpq)
The connection string every libpq-based client understands: host=... port=... dbname=... user=..., or the postgresql://user@host:port/db URI form. Lesson 27.4
CREATE INDEX CONCURRENTLY (CONCURRENTLY, REINDEX CONCURRENTLY)
Builds an index without blocking writes. Slower, cannot run inside a transaction block, and leaves an INVALID index if it fails. Lesson 27.38
data directory (PGDATA, data_directory)
The directory with a cluster's files: tables, indexes, WAL, the control file. /var/lib/postgresql/18/main on Ubuntu; never edited by hand. Lesson 27.2
database (PostgreSQL)
A separate namespace inside a cluster. A connection is always to exactly one database; tables in different databases cannot be joined. Lesson 27.1
dead but not yet removable
What VACUUM VERBOSE reports for dead tuples held back by the xmin horizon: a long transaction, a prepared transaction, a replica or a slot needs them. Lesson 27.24
dead tuple (dead tuples, n_dead_tup)
A row version no running transaction can see any more - space only VACUUM reclaims. Lesson 27.21
deadlock (PostgreSQL) (deadlock detected, deadlock_timeout)
Two transactions each waiting for a lock the other holds. After deadlock_timeout (1 s) PostgreSQL cancels one of them with "deadlock detected". Lesson 27.21
default privileges (ALTER DEFAULT PRIVILEGES)
Grants that apply automatically to objects a given role creates in the future - what keeps the app working when a migration adds a table. Lesson 27.7
expand and contract (expand/contract, parallel change)
Changing a schema in steps old and new code can both live with: add the new thing, migrate data and code, remove the old thing. Lesson 27.38
expanded display (\x)
psql's one-field-per-line output (\x), for rows too wide for the screen. Lesson 27.4
EXPLAIN (EXPLAIN ANALYZE, query plan)
Shows the plan the planner chose for a query; with ANALYZE it runs the query and adds real times, rows and buffers. Lesson 27.32
expression index (functional index)
An index on an expression such as lower(email), used by queries that filter on exactly that expression. Lesson 27.32
fast shutdown (smart shutdown, immediate shutdown)
The default way to stop: roll back open transactions, disconnect everyone, checkpoint, exit. Smart waits for clients to leave; immediate exits without a checkpoint. Lesson 27.2
fencing (fence)
Making sure the old primary cannot come back and accept writes: stop it, disable its start, cut it off. Lesson 27.52
freezing (freeze, datfrozenxid, relfrozenxid)
Marking old rows as visible to everyone so their transaction ID no longer matters. age(datfrozenxid) shows how close a database is to needing it. Lesson 27.24
GUC (grand unified configuration)
PostgreSQL's name for a setting such as work_mem or max_connections. Lesson 27.2
HikariCP (Hikari, maximum-pool-size)
The Java connection pool Spring Boot uses; maximum-pool-size per application instance, and by default it keeps that many connections open. Lesson 27.15
HOT update (heap-only tuple)
An update that changes no indexed column and fits on the same page, so no index has to change. Extra indexes make updates lose this. Lesson 27.32
hot_standby_feedback
A replica setting that tells the primary which rows its queries still need, so VACUUM keeps them - fewer query cancellations, more bloat on the primary. Lesson 27.52
idle in transaction
A session that opened a transaction and is now waiting for its client, holding every lock it took and the xmin horizon. Lesson 27.21
idle_in_transaction_session_timeout
Ends a session that has been idle inside a transaction for longer than this - the cure for forgotten open transactions. Lesson 27.21
Index Scan (Index Only Scan, Bitmap Heap Scan, Bitmap Index Scan)
Plan steps that use an index: walk it and fetch rows, answer from the index alone, or collect row locations first and read their pages in order. Lesson 27.32
INVALID index
An index left behind by a failed or cancelled concurrent build: updated on every write, never used. Drop it and build again. Lesson 27.38
io worker (io workers, io_method)
Background processes new in PostgreSQL 18 that read data files ahead of time for the backends (asynchronous I/O, io_method = worker). Lesson 27.1
isolation level (READ COMMITTED, REPEATABLE READ, SERIALIZABLE)
How much a transaction sees of others' changes: READ COMMITTED (default, a snapshot per statement), REPEATABLE READ (one per transaction), SERIALIZABLE (as if one at a time). Lesson 27.21
listen_addresses
Which network interfaces the server listens on (default localhost). A restart setting - remote clients get "connection refused" until it is changed. Lesson 27.7
lock mode (ACCESS EXCLUSIVE, ACCESS SHARE, ROW EXCLUSIVE)
One of eight table lock strengths. ACCESS SHARE (any SELECT) conflicts only with ACCESS EXCLUSIVE (most ALTER TABLE, DROP, TRUNCATE), which conflicts with everything. Lesson 27.21
lock queue
Lock requests are granted in order: one waiting ALTER TABLE makes every later query on that table wait behind it. Lesson 27.38
lock_timeout
How long a statement may wait for a lock before it is cancelled (canceling statement due to lock timeout). The seatbelt for migrations. Lesson 27.21
log_line_prefix
What starts every server log line. Ubuntu's is a timestamp, the PID and user@database. Lesson 27.2
log_min_duration_statement (slow-query log, slow query log)
Logs every statement that takes longer than this, with its duration. 0 logs everything. Lesson 27.35
logical backup (pg_dump)
The data as SQL or an archive of rows (pg_dump): portable across versions, one database, as fresh as the last dump. Lesson 27.43
LSN (log sequence number, pg_lsn)
A position in the WAL, written like 0/3A01F28. pg_wal_lsn_diff gives the bytes between two. Lesson 27.47
major upgrade (PostgreSQL) (pg_upgrade, pg_upgradecluster)
Moving to a new major version (17 to 18). The data format changes, so it needs pg_upgrade, a dump and restore, or logical replication. Lesson 27.1
managed PostgreSQL (Azure Database for PostgreSQL, Flexible Server, Amazon RDS)
PostgreSQL run by a cloud provider: they patch, back up and fail over; you still own connections, credentials, network, capacity, queries and drills. Lesson 27.61
max_connections
The most connections the server accepts at once (default 100), including the reserved superuser slots. A restart setting. Lesson 27.15
max_slot_wal_keep_size
The most WAL a slot may hold before it is given up (wal_status lost) instead of filling the primary's disk. Lesson 27.52
meta-command (meta-commands, backslash command)
A psql command starting with a backslash (\dt, \x, \copy): psql handles it itself instead of sending it to the server as SQL. Lesson 27.4
minor release (PostgreSQL) (minor version (PostgreSQL))
A bug-fix and security release of a major version (18.1, 18.2 ... 18.6), about every three months. Same data format: install and restart. Lesson 27.1
MVCC (multi-version concurrency control)
How PostgreSQL lets readers and writers work at once: an update writes a new version of the row and leaves the old one for transactions that still need it. Lesson 27.1
normalized query (queryid)
A query with its constants replaced by $1, $2..., so every call of the same statement counts as one entry; queryid is its number. Lesson 27.35
NOT VALID (VALIDATE CONSTRAINT)
Adds a constraint without checking existing rows; VALIDATE CONSTRAINT checks them later with a lock that lets reads and writes continue. Lesson 27.38
ON_ERROR_STOP
A psql variable: stop a script at the first error and exit with code 3. Without it psql runs every statement and exits 0. Lesson 27.4
partial index
An index on only the rows matching a WHERE condition (e.g. not processed) - small, and exactly the rows a job looks for. Lesson 27.32
Patroni (CloudNativePG, pg_auto_failover)
Tools that run PostgreSQL high availability: they watch the nodes, promote a replica and repoint clients, with a consensus store so only one primary exists. Lesson 27.52
peer authentication (peer)
Over the Unix socket the server asks the kernel which OS user connected and lets them in as the role of the same name. Why only the OS user postgres gets in as postgres. Lesson 27.2
pending_restart
A column of pg_settings: true when a changed setting is waiting for a restart to apply. Lesson 27.2
pg_basebackup (base backup, backup_label)
Takes a consistent copy of a running cluster's data directory; with archived WAL after it, the base of PITR and of new replicas. Lesson 27.47
pg_blocking_pids
A function returning the PIDs a session is waiting for - the way to walk a blocking chain to its root. Lesson 27.21
pg_ctlcluster
Ubuntu's start/stop/restart/reload/promote command for one cluster (pg_ctlcluster 18 main restart); as root it hands over to systemctl. Lesson 27.2
pg_dumpall (globals, --globals-only)
Dumps the whole cluster as SQL; with --globals-only just the roles (with password verifiers) and tablespaces that pg_dump leaves out. Lesson 27.43
pg_file_settings
A view of what the config files say, line by line, and whether each line was applied - where typos and overridden values show up. Lesson 27.2
pg_hba_file_rules
A view that parses pg_hba.conf as it is on disk now, with an error column - check it before you reload. Lesson 27.7
pg_hba.conf (host-based authentication, hba)
The ordered rules for who may connect from where to which database, and how they authenticate. The first rule that matches decides. Changes need a reload. Lesson 27.7
pg_isready
A small client that only checks whether a server accepts connections; exit 0 accepting, 1 rejecting, 2 no response, 3 bad arguments. What probes use. Lesson 27.1
pg_locks
The view of every lock held or waited for; granted = f marks a waiter. Lesson 27.21
pg_lsclusters
Lists every PostgreSQL cluster on the machine with version, name, port, status, owner, data directory and log file. Lesson 27.2
pg_monitor
A built-in role allowed to read every statistics view and setting monitoring needs, without superuser rights. Lesson 27.58
pg_repack
An extension that rebuilds a bloated table or index online, with only short locks at the start and end - the alternative to VACUUM FULL. Lesson 27.24
pg_restore (custom format, -Fc)
Restores pg_dump archives in custom, directory or tar format; can list (-l), pick (-L) and restore in parallel (-j). Lesson 27.43
pg_rewind
Rewinds a former primary's divergent changes so it can rejoin as a replica of the new primary without a full copy. Lesson 27.52
pg_settings
The view of every setting the running server has: value, unit, context, where it came from and whether it waits for a restart. Lesson 27.2
pg_stat_activity
The view with one row per server process: who is connected, from where, in which state, running which query and waiting for what. The first place to look in almost every PostgreSQL incident. Lesson 27.1
pg_stat_replication
The primary's view of its connected replicas: state, sent/written/flushed/replayed LSNs and lag times. Lesson 27.52
pg_stat_statements
An extension keeping running totals per normalized query: calls, total and mean time, rows, buffers. Needs shared_preload_libraries and a restart. Lesson 27.35
pg_terminate_backend (pg_cancel_backend)
pg_terminate_backend(pid) ends a whole session (its transaction rolls back); pg_cancel_backend(pid) only cancels its current query. Lesson 27.21
pg_up
postgres_exporter's gauge: 1 if its last attempt to connect to the server worked, 0 if not. Lesson 27.58
pg_wal
The subdirectory of the data directory holding the WAL segments. Never delete files in it to free space. Lesson 27.2
PgBouncer (pgbouncer)
A lightweight connection pooler in front of PostgreSQL: many client connections share a small pool of real server connections. Port 6432; config in /etc/pgbouncer/pgbouncer.ini. Lesson 27.15
PGlite
(simulator) PostgreSQL 18.3 compiled to WebAssembly. The lab's SQL runs on it, so plans, errors and VACUUM output are real PostgreSQL. Lesson 27.1
physical backup
A copy of the data files (pg_basebackup) plus the WAL after it: fast to restore, same major version, any point in time with archived WAL. Lesson 27.43
PITR (point-in-time recovery, recovery_target_time)
Restoring a base backup and replaying archived WAL up to a chosen moment, then stopping - to just before a mistake. Lesson 27.47
planner statistics (pg_stats, ANALYZE (PostgreSQL), n_distinct)
Per-column samples ANALYZE collects; the planner's row estimates come from them. Stale statistics cause bad plans. Lesson 27.32
pool mode (transaction pooling, session pooling, statement pooling)
When PgBouncer takes a server connection back: at disconnect (session), at the end of each transaction (transaction) or after each statement (statement). Lesson 27.15
postgres_exporter (prometheus-postgres-exporter)
The Prometheus exporter for PostgreSQL: runs catalog queries on each scrape and exposes the results on port 9187. Lesson 27.58
postgresql-common
Ubuntu's and Debian's wrapper around PostgreSQL: several versions and clusters side by side, the pg_*cluster commands and the systemd units. Lesson 27.2
postgresql.auto.conf (ALTER SYSTEM)
The file ALTER SYSTEM writes into the data directory; it is read after postgresql.conf, so it wins. Lesson 27.2
postgresql.conf
The main configuration file. On Ubuntu in /etc/postgresql/18/main/, with a conf.d/ directory included at its end. Lesson 27.2
postmaster
The main postgres process: it listens on the port and starts one backend process for every new connection, plus the background processes. Lesson 27.1
privilege (PostgreSQL) (GRANT, REVOKE)
What a role may do with one object: CONNECT on a database, USAGE on a schema, SELECT/INSERT/UPDATE/DELETE on a table. Given with GRANT, taken with REVOKE. Lesson 27.7
promotion (promote, pg_promote)
Ending recovery on a replica so it becomes a writable primary, on a new timeline. Lesson 27.52
psql
PostgreSQL's command-line client, on every server and image: runs SQL and backslash meta-commands, interactively or in scripts. Lesson 27.4
PUBLIC (PostgreSQL)
The pseudo-role meaning every role. By default every role may connect to every database - revoke it for private databases. Lesson 27.7
random_page_cost
The planner's cost of a random page read compared to a sequential one (default 4.0; about 1.1 on SSDs and cloud disks). Lesson 27.32
RDS Proxy
AWS's managed connection pooler in front of RDS and Aurora; Azure Flexible Server has a built-in PgBouncer instead. Lesson 27.61
recovery.signal (standby.signal, restore_command)
Empty files in the data directory telling the server to start in recovery: recovery.signal for archive recovery and PITR, standby.signal for a replica. Lesson 27.47
reload (PostgreSQL) (pg_reload_conf, SIGHUP (PostgreSQL))
Telling the postmaster to re-read the config files (SIGHUP, systemctl reload, select pg_reload_conf()): what can change while running changes, nobody is disconnected. Lesson 27.2
reloptions (autovacuum_enabled, storage parameter)
Per-table settings such as autovacuum_enabled or autovacuum_vacuum_scale_factor, set with ALTER TABLE ... SET (...) and shown by \d+. Lesson 27.24
replica (PostgreSQL) (standby, hot standby, read replica)
A second server that continuously replays the primary's WAL: read-only, a copy ready to be promoted if the primary dies. Lesson 27.1
replication lag (replay_lag, replay_lsn)
How far a replica is behind: bytes (pg_wal_lsn_diff of current and replay LSN) or time (pg_stat_replication's *_lag columns). Lesson 27.52
replication slot (slot, pg_replication_slots)
A record on the primary of how far a consumer has received; the primary keeps all WAL the slot still needs - forever, if the consumer is gone. Lesson 27.52
restore test (restore drill)
Regularly restoring the newest backup somewhere safe and checking the data - the only proof that a backup works. Lesson 27.43
role (PostgreSQL) (database role, database user)
A user or a group: PostgreSQL has one concept for both. A role with LOGIN is what other systems call a user. Lesson 27.1
role attribute (LOGIN, NOLOGIN, SUPERUSER)
What a role may do in the cluster as a whole: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, CONNECTION LIMIT, VALID UNTIL. Lesson 27.7
row lock
A lock on individual rows taken by UPDATE, DELETE and SELECT ... FOR UPDATE, held until the transaction ends; waiters wait on the holder's transaction ID. Lesson 27.21
row version (tuple, tuples, heap tuple)
One version of a row stored in the table. An UPDATE writes a new version; the old one stays until VACUUM removes it. Lesson 27.21
Rows Removed by Filter
In EXPLAIN ANALYZE: rows a step read and threw away - the waste an index would avoid. Lesson 27.32
RPO (recovery point objective)
How much data you may lose in a disaster: "at most 5 minutes". Lesson 27.43
RTO (recovery time objective)
How long recovery may take before the service is back: "within an hour". Lesson 27.43
schema (PostgreSQL) (public schema)
A folder for tables and other objects inside a database. public is the default one. Lesson 27.1
SCRAM-SHA-256 (scram-sha-256, SCRAM verifier)
The password method to use: the server stores a salted verifier and the password never crosses the network. md5 is the deprecated older method. Lesson 27.7
Seq Scan (sequential scan)
A plan step that reads every page of a table. Fine for small tables or most of a table; the usual culprit for a slow selective query. Lesson 27.32
server parameters
The managed-service replacement for postgresql.conf, changed through the portal, CLI or Terraform; static ones still need a restart. Lesson 27.61
setting context (context (PostgreSQL setting), postmaster context, sighup context)
When a setting can change: postmaster = only at restart, sighup = on reload, superuser/user = on reload and per session, internal = never. Lesson 27.2
shared_preload_libraries
Libraries loaded when the server starts (pg_stat_statements, auto_explain). One list, a restart setting. Lesson 27.35
SHOW POOLS (cl_waiting, maxwait)
The PgBouncer admin console command showing clients active and waiting per pool, server connections in use and idle, and the longest wait. Lesson 27.15
snapshot (PostgreSQL)
The set of committed transactions a statement (or a REPEATABLE READ transaction) sees, fixed when it starts. Lesson 27.21
split brain
Two servers both accepting writes for the same data after a failover - what fencing the old primary prevents. Lesson 27.52
statement_timeout
How long a statement may run before it is cancelled. Set per role or session, not for the whole server. Lesson 27.21
streaming replication (walsender, walreceiver)
The primary sends WAL to the replica as it is written (walsender to walreceiver) and the replica replays it continuously. Lesson 27.52
superuser_reserved_connections (reserved_connections, reserved slots)
Connection slots only superusers may use (default 3), so an admin can still get in when the apps have used everything else. Lesson 27.15
synchronous replication (synchronous_commit, synchronous_standby_names)
A commit waits until a standby has the WAL: no data loss on failover, more latency on every write. Lesson 27.52
table rewrite
A change that copies a whole table into a new file (most column type changes, volatile defaults), holding ACCESS EXCLUSIVE for the whole copy. Lesson 27.38
timeline (PostgreSQL) (timeline ID, .history file)
A numbered history of WAL. Ending recovery or promoting a replica starts the next timeline so old and new WAL never mix. Lesson 27.47
too many clients (sorry, too many clients already)
The FATAL error when max_connections is reached. Usually caused by instances x pool size growing past it after a scale-out. Lesson 27.15
total_exec_time (mean_exec_time)
pg_stat_statements columns: all the time a query has taken in total, and per call. Sort by total to find the load. Lesson 27.35
transaction (PostgreSQL) (BEGIN, COMMIT, ROLLBACK)
Statements that commit together or not at all. Without BEGIN, every statement is its own transaction. Lesson 27.21
transaction ID wraparound (XID wraparound, wraparound, XID)
Transaction IDs are 32-bit. If old rows are not frozen in time, PostgreSQL first warns and then refuses new transactions to protect the data. Lesson 27.24
userlist.txt (auth_file)
PgBouncer's list of users and their passwords or SCRAM verifiers, one "user" "secret" pair per line. Lesson 27.15
VACUUM
Removes dead tuples, marks their space reusable, updates the visibility map and freezes old rows. Does not shrink the file and does not block reads or writes. Lesson 27.24
VACUUM FULL
Rewrites a whole table to give space back to the operating system - under an ACCESS EXCLUSIVE lock that blocks every use of the table. Lesson 27.24
VALID UNTIL
A role attribute: the password stops working after this time ("User has an expired password" in the server log). Lesson 27.7
WAL (write-ahead log, write ahead log)
The journal PostgreSQL writes every change to before changing the data files. Crash recovery, replicas and point-in-time restores all replay it. Lesson 27.1
WAL archiving (archive_command, archive_mode, pg_stat_archiver)
Copying every finished WAL segment to safe storage. A failing archive_command keeps WAL in pg_wal until the disk fills. Lesson 27.47
WAL segment (WAL file, segment (WAL))
One 16 MB file in pg_wal, named by timeline and position. Lesson 27.47
work_mem
Memory per sort or hash step, per query, before it spills to disk ("external merge"). Raise it per role for big reports, not globally. Lesson 27.32
xmin (xmax, ctid)
Hidden columns of every row version: the transaction that created it (xmin), the one that ended it (xmax), and its physical place (ctid). Lesson 27.21
xmin horizon (removable cutoff, backend_xmin)
The oldest transaction anything may still need. VACUUM cannot remove dead tuples newer than it, however often it runs. Lesson 27.24