EDB PostgreSQL Associate flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Shared buffers

PostgreSQL's main shared memory cache of data pages, sized by shared_buffers and commonly set near 25 percent of RAM; all backends read and write heap pages through it.

Checkpointer process

Background process that periodically writes dirty shared buffers to disk and records a checkpoint in WAL, which bounds crash recovery time by limiting how far back replay must start.

Postmaster process

Parent server process that listens for client connections and forks a backend process for each one; it also starts and monitors the background and auxiliary workers.

PGDATA directory

Filesystem directory holding a cluster's data files, including base tables, pg_wal, pg_xact, and configuration files such as postgresql.conf and pg_hba.conf.

timestamptz

Timestamp with time zone: PostgreSQL stores the value internally as UTC and renders it in the session TimeZone, so it represents an absolute instant rather than local wall-clock time.

NULL comparison

Any comparison with NULL yields NULL rather than true or false; test with IS NULL or IS NOT NULL, and use COALESCE to substitute a default value.

jsonb type

Binary decomposed JSON storage that removes duplicate keys, reorders keys, and supports GIN indexing and containment operators, unlike json which preserves the exact input text.

numeric type

Exact arbitrary-precision decimal type with a configurable scale, used for monetary and other values where binary floating-point rounding from real or double precision is unacceptable.

B-tree index

Default index method supporting equality and ordered range comparisons plus sorting; it suits most primary key, unique, and foreign key columns and the operators =, <, <=, >, >=

GIN index

Inverted index suited to composite values such as arrays, jsonb documents, and full-text search vectors; it indexes individual elements so containment queries can use it.

Partial index

Index created with a WHERE clause so only rows satisfying the predicate are stored, reducing index size and write cost for skewed data or filtered queries.

Declarative partitioning

Table defined with PARTITION BY RANGE, LIST, or HASH together with child partitions; the planner prunes nonmatching partitions when a query filters on the partition key.

ON CONFLICT

INSERT clause implementing upsert: DO NOTHING skips rows that conflict with a unique or exclusion constraint, while DO UPDATE applies the supplied update expression instead.

Window function

Function evaluated over a set of rows related to the current row using OVER with PARTITION BY and ORDER BY; it returns one value per row without collapsing rows.

LATERAL join

Join in which the right-hand subquery or function may reference columns from the left-hand table, enabling per-row subqueries and LIMIT inside a join.

UNION ALL

Set operator that concatenates two result sets and keeps duplicate rows; it is faster than UNION, which must sort or hash to remove duplicates.

pg_dump custom format

Logical backup written with pg_dump -Fc: compressed and restorable selectively with pg_restore, including parallel restore using multiple jobs.

pg_basebackup

Utility that takes a physical base backup of a running cluster and can stream the required WAL, producing a directory that can start as a standby.

Point-in-time recovery

Recovery method that restores a base backup and replays archived WAL up to a target time, transaction ID, LSN, or named restore point using recovery_target settings.

archive_command

Shell command PostgreSQL runs to copy each completed WAL segment to archival storage when archive_mode is on; it must return zero on success.

pg_hba.conf

Client authentication file whose lines list connection type, database, user, address, and method; entries are matched top-down, so order decides which method applies.

SCRAM-SHA-256

Challenge-response password authentication method that avoids sending the password in clear text; selected by password_encryption and preferred over the older md5 method.

Row-level security

Policy mechanism enabled with ALTER TABLE ... ENABLE ROW LEVEL SECURITY that restricts which rows a role can see or modify, defined through CREATE POLICY expressions.

SECURITY DEFINER

Function attribute making the function run with the privileges of its owner instead of the caller; a safe search_path is required to prevent privilege hijacking.

EXPLAIN ANALYZE

Runs the query and reports the actual execution plan with per-node time, row counts, and loops; adding BUFFERS shows shared block hits, reads, and writes.

pg_stat_statements

Extension that tracks normalized query texts with call counts, total and mean execution time, rows processed, and buffer usage, enabling workload-level tuning.

Autovacuum

Background process that removes dead tuples and refreshes statistics; it triggers when dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the table row estimate.

Cache hit ratio

Fraction of block requests served from shared buffers, computed as blks_hit divided by blks_hit plus blks_read using counters from the pg_stat_database view.

Streaming replication

Primary continuously ships WAL records to standbys over a replication connection; standbys replay them, and in hot standby mode they also serve read-only queries.

synchronous_commit

Setting controlling whether a commit waits for local WAL flush and, when synchronous_standby_names is set, for confirmation from synchronous standbys before returning to the client.

Replication slot

Server-side object that retains WAL or logical changes until a consumer confirms receipt; it prevents premature removal but can fill disk if the consumer stalls.

Logical replication

Method that publishes row-level changes from tables through publications and applies them on subscribers through subscriptions; it requires wal_level set to logical.