EDB PostgreSQL Professional key terms

Command-line utility used to initialize, start, stop, restart, and reload a PostgreSQL server instance, and to check its status. It can run in foreground or background mode and accepts a wait timeout for controlled shutdowns.

Lock file written into the data directory by the running postmaster, recording its PID, data directory path, start timestamp, port, socket directory, listen address, and shared memory segment ID. Its presence signals an active or uncleanly terminated instance.

Utility that prints the contents of the pg_control file, including database cluster state, latest checkpoint location, redo start point, and catalog version. Useful for diagnosing crash recovery status and verifying backup consistency.

Concurrency model where updates create new row versions instead of overwriting data, so readers see a consistent snapshot without blocking writers. Old versions persist until vacuum removes them, which drives bloat and tuning decisions.

Condition where the 32-bit transaction counter exhausts its range and older rows appear to be in the future, causing data loss or shutdown. Prevented by routine VACUUM and monitored through age columns in pg_class and pg_database.

Maintenance operation that reclaims storage occupied by dead row versions and updates the visibility map. Plain VACUUM does not return space to the operating system; VACUUM FULL rewrites the table under an exclusive lock.

Background daemon that automatically runs VACUUM and ANALYZE on tables whose dead-tuple or modification counts exceed configured thresholds. Per-table settings let administrators tune aggressiveness for high-churn tables.

Named storage location outside the default data directory where database objects such as tables and indexes can be placed. Used to distribute I/O across filesystems or mount points and to separate hot data from archival data.

Mechanism that stores large variable-length attribute values in a side table and keeps only a compact pointer in the main heap tuple. Compression and out-of-line storage thresholds are configurable per column.

The primary physical storage structure for a table, holding row versions in an unordered set of pages. Indexes point into heap tuples using item identifiers, and sequential scans read the heap directly.

System view exposing per-table activity counters, including sequential and index scan counts, live and dead tuple estimates, and timestamps of last vacuum and analyze. A primary source for identifying maintenance and indexing problems.

Command that executes a query and reports the actual plan with per-node execution time, row counts, and loop counts alongside planner estimates. Comparing estimated versus actual rows reveals selectivity and statistics problems.

Cost-based component that enumerates access paths and join orders, estimates their costs from statistics and configuration constants, and selects the cheapest plan. Its choices depend heavily on accurate ANALYZE data and cost parameters.

Session or per-operation memory limit for sorts and hash tables before spilling to temporary disk files. Set per query or per role rather than globally, because a single complex query can allocate it many times.

Amount of shared memory the server dedicates to caching data pages. A common starting point is roughly one quarter of system RAM, with the operating system page cache handling the remainder.

Extension that aggregates execution statistics for normalized SQL statements, including calls, total and mean time, rows, and shared block reads and writes. Central to identifying the highest-impact queries to tune.

Plan node that answers a query entirely from an index without visiting the heap, provided the visibility map marks pages as all-visible. Requires the query's columns to be covered by the index definition or INCLUDE list.

Per-table bitmap tracking which pages contain only tuples visible to all transactions and which are all-frozen. It enables index-only scans and lets VACUUM skip already-clean pages, so aggressive vacuuming keeps it current.

Index defined with a WHERE predicate that includes only rows satisfying the condition. It is smaller and cheaper to maintain than a full index and can support queries whose filters match the predicate.

Durable append-only log of every data change, written and flushed before the corresponding heap pages. It underpins crash recovery, physical replication, and point-in-time restore.

Process of copying completed WAL segment files to a durable external location using archive_command or a replication slot. Archived segments form the incremental input needed to replay changes during point-in-time recovery.

Restoring a base backup and replaying archived WAL up to a chosen target time, transaction ID, or named restore point. Requires recovery settings that specify the stop condition and a reachable archive source.

Physical copy of an entire data directory taken while the server runs, using pg_basebackup or the low-level backup API. It serves as the starting point for PITR and for building new replicas.

Client utility that streams a physical base backup from a running server over a replication connection, optionally compressing or tarring output and configuring the copy as a standby. It can also create a replication slot automatically.

Physical replication where a standby continuously receives and applies WAL records from a primary over a replication connection. Standbys can serve read-only queries and be promoted during failover.

Configuration where a commit on the primary waits for confirmation that specified standbys have flushed or applied the WAL. It reduces data loss risk at the cost of commit latency and availability if standbys fail.

Server-side object that retains WAL or logical change information until a named consumer confirms receipt. Slots prevent required WAL from being recycled but can fill disk if a consumer stops advancing.

Replication of row-level changes decoded from WAL and published per table, allowing selective replication and cross-version upgrades. It requires a publication on the source and a subscription on the target.

Failure state in which two nodes independently believe they are the primary and accept writes, causing divergent data. Prevented by fencing, quorum-based cluster managers, and witness nodes in high-availability designs.

Process of promoting a standby to primary after the original primary fails, then redirecting client traffic. Automated failover requires a cluster manager that detects failure and prevents the old primary from accepting writes.

Utility that produces a logical backup of a single database as plain SQL or a custom archive format, using a consistent snapshot without blocking writes. Custom-format output can be restored selectively with pg_restore.

Utility that dumps all databases of a cluster plus cluster-wide objects such as roles and tablespaces. It emits plain SQL text only and is commonly used to capture globals alongside per-database dumps.

Utility that restores archives produced by pg_dump in custom or directory format, allowing selective object restoration, parallel workers, and data-only or schema-only modes. It can also emit SQL to standard output.

Utility that upgrades a cluster between major versions by creating new system catalogs and reusing existing data files, then transferring statistics. It requires matching binaries for old and new versions and a careful link or copy decision.

Feature that attaches policies to tables so the server filters rows per user or role, independent of application code. Policies combine permissively for reads and restrictively for writes unless configured otherwise.

Client authentication configuration file listing connection rules by connection type, database, user, and address, each mapped to an authentication method. Rules are evaluated top-down and the first match wins.

Challenge-response authentication method that avoids sending passwords in clear text and does not require the server to store a directly usable secret. It is the default password method in recent PostgreSQL releases.

SQL commands that assign or remove privileges on database objects and roles. Privileges can be inherited through role membership, and default privileges control what newly created objects grant automatically.

EDB's commercial platform combining PostgreSQL management, observability, and AI-oriented capabilities such as vector data support and model integration. Operational tasks include provisioning clusters, monitoring, and configuring extensions through platform tooling.

Open-source extension adding vector column types and distance operators, enabling similarity search with exact or approximate nearest-neighbor indexes. It is commonly used to store embeddings for retrieval-augmented generation workloads.