EDB PostgreSQL Associate key terms

The utility that creates a new PostgreSQL data directory, generating the system catalogs, template databases, and configuration files needed before the server can start for the first time.

Command-line utility used to start, stop, restart, reload, and check the status of a PostgreSQL server, and to send signals to it without connecting to the database.

The server parameter that sets how much memory PostgreSQL reserves for caching data pages in shared memory; it is the primary cache consulted before reading blocks from disk.

A log of every change made to data files, written to disk before the changes themselves; it enables crash recovery and is the basis for replication and point-in-time recovery.

A server operation that writes all dirty buffers to disk and records a log position, so crash recovery only has to replay WAL written after that point.

A concurrency design in which an update writes a new row version instead of overwriting the old one, so readers see a consistent snapshot without blocking writers, and writers do not block readers.

A storage mechanism that moves large variable-length column values into a separate side table, compressing them, so the main table's rows stay within the page size limit.

A data type that stores JSON documents in a decomposed binary form, slightly slower to write than json but able to support indexing and fast operators for querying keys and values.

A marker for an unknown or missing value; comparisons with it return unknown rather than true or false, so rows are tested with IS NULL and IS NOT NULL.

A column definition that fills values automatically from an owned sequence, offering the SQL-standard alternative to the older SERIAL pseudo-type when generating surrogate key values.

A named filesystem location, outside the data directory, where tables and indexes can be stored, letting a database spread its objects across different disks or mount points.

The collection of tables in the pg_catalog schema that describe every object, user, and setting; the server reads them to plan queries and enforce rules.

The default index type, keeping keys in sorted order so the planner can answer equality and range conditions, and produce ordered output, with a few page reads instead of a full scan.

An index built with a WHERE clause so only rows matching the condition are indexed, which shrinks the index and speeds up writes when queries always filter on that condition.

A view whose query result is physically stored and must be refreshed on demand, trading freshness for fast reads of expensive aggregations or joins.

A constraint requiring each value in a column to exist in the referenced table's key, enforcing referential integrity between parent and child rows and controlling which deletes are allowed.

A unit of work opened with BEGIN and ended with COMMIT or ROLLBACK, in which all statements take effect or none do, and reads see a consistent snapshot.

A named subquery introduced with WITH and referenced in the main statement; it improves readability and, when marked MATERIALIZED, is evaluated once rather than inlined.

A function that computes a value over a set of rows related to the current row while still returning one output row per input row, using OVER with PARTITION BY and ORDER BY.

An INSERT statement with an ON CONFLICT clause that inserts a row or, when a uniqueness conflict occurs, updates the existing row or does nothing instead of failing.

A clause on INSERT, UPDATE, or DELETE that outputs values from the affected rows, avoiding a follow-up SELECT, for example to read generated key values.

A client utility that copies a running cluster's data directory and WAL block by block, producing a physical backup used to start a standby or restore a server.

Recovery that restores a base backup and then replays archived WAL up to a chosen time, LSN, or transaction, so you can stop just before a damaging event.

A client utility that writes a consistent logical backup of one database, as a SQL script or archive file, containing object definitions and data without blocking normal activity.

The configuration, through archive_command, that copies each completed WAL segment to a safe location, so recovery can replay changes made after the base backup.

A backup and restore tool often used with EDB Postgres that provides full, differential, and incremental backups, compression, parallel processing, and retention policies.

The client authentication file that lists which hosts, users, and databases may connect and by which method, such as trust, password, or certificate, checked at every new connection.

An account object that can own database objects and hold privileges; group roles can be granted to other roles, and a role with LOGIN privilege acts as a user.

SQL commands that add or remove privileges, such as SELECT or INSERT on a table, or membership in a role, defining what each role is permitted to do.

A table feature that attaches policies deciding which rows a given role may read or change, adding per-row rules on top of table-level privileges.

EXPLAIN shows the planner's chosen plan and cost estimates; adding ANALYZE executes the statement and reports actual times and row counts for each plan node.

An extension that records normalized query texts with call counts, total and mean execution times, and rows processed, making it easy to find the costliest statements.

A background process that removes dead row versions left by updates and deletes and refreshes planner statistics, preventing table bloat and transaction ID wraparound.

A command that samples a table and updates planner statistics in the system catalog, such as value distribution and correlation, so the optimizer estimates row counts better.

A system view with one row per connection showing user, database, state, current query, and wait event, used to find blocking sessions and long-running statements.

A setup where a standby continuously receives and applies WAL records from a primary over a replication connection, staying nearly current for read-only queries and failover.

Replication that publishes row-level changes from selected tables and applies them on a subscriber, working across major versions and allowing selective or bidirectional configurations.

A server-side object that tracks how far a standby or subscriber has consumed WAL, so the sender keeps the required segments instead of recycling them.

A standby server that accepts read-only connections while applying WAL from the primary, so it can serve queries and be promoted when the primary fails.

The process of promoting a standby to become the primary after the original fails and repointing clients, often automated by a cluster manager to shorten downtime.