EDB PostgreSQL Professional flashcards

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

MVCC row visibility

A row version is visible when its xmin transaction is committed and its xmax is uncommitted, aborted, or absent. This lets readers avoid blocking writers.

Autovacuum trigger formula

Autovacuum fires when dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times reltuples. Defaults are 50 and 0.2, so large tables need many dead tuples.

Transaction ID wraparound

The 32-bit xid counter wraps; VACUUM FREEZE marks old tuples frozen. Monitor age(datfrozenxid) and keep it below autovacuum_freeze_max_age (200 million by default) to avoid shutdown.

pg_hba.conf matching rule

Lines are evaluated top-down and the first match wins. Each line specifies connection type, database, user, client address, and authentication method such as scram-sha-256 or peer.

VACUUM versus VACUUM FULL

VACUUM reclaims dead tuple space for reuse inside the table without shrinking the file. VACUUM FULL rewrites the table under an ACCESS EXCLUSIVE lock and returns space to the OS.

EXPLAIN ANALYZE BUFFERS

EXPLAIN (ANALYZE, BUFFERS) runs the query and reports actual rows, loops, and shared buffer hits versus reads. Comparing estimated to actual rows reveals stale statistics or bad selectivity.

random_page_cost tuning

random_page_cost tells the planner the relative cost of a random page fetch versus a sequential one. Lowering it from 4.0 to about 1.1 on SSD favors index scans.

work_mem semantics

work_mem is the memory limit per sort or hash node, not per session. A plan with several hash nodes and parallel workers can consume work_mem multiplied by nodes and workers.

pg_stat_statements setup

Add pg_stat_statements to shared_preload_libraries, restart, then CREATE EXTENSION. It aggregates calls, total and mean time, rows, and buffer hits per normalized query text.

GIN versus BRIN indexes

GIN indexes arrays, jsonb, and full-text search with many keys per row. BRIN stores min/max summaries per block range and suits very large tables with natural ordering, at minimal size.

Hash join memory need

A hash join builds an in-memory hash table from the smaller input. If it exceeds work_mem it spills to temporary files, which shows as temp blocks and slower execution.

pg_blocking_pids function

pg_blocking_pids(pid) returns the process IDs currently blocking a given backend. Join pg_locks with pg_stat_activity to identify the lock type and the blocking statement.

Streaming replication prerequisites

The primary needs wal_level of replica or logical, max_wal_senders above zero, and a replication entry in pg_hba.conf. The standby needs primary_conninfo and a standby.signal file.

Synchronous commit levels

synchronous_commit values from weakest to strongest are off, local, remote_write, on, and remote_apply. remote_apply waits until the standby replays the commit, giving the strongest guarantee.

Replication slot purpose

A physical replication slot retains WAL until the standby consumes it, preventing removal. Monitor pg_replication_slots because an inactive slot can fill the disk with WAL.

Point-in-time recovery setup

Set archive_mode to on with archive_command or archive_library, take a base backup, then restore with restore_command and a recovery_target_time, name, xid, or immediate setting.

pg_rewind use case

pg_rewind resynchronizes a former primary with the new primary after failover by copying only diverged blocks. It requires wal_log_hints or data checksums and is faster than a full base backup.

pg_basebackup -R option

pg_basebackup -R writes the standby configuration automatically, including primary_conninfo and standby.signal. Combined with -X stream it also streams the WAL needed to start recovery.

Logical replication limits

Logical replication copies table data changes only. It does not replicate DDL, sequences, large objects, or schema changes, so those must be applied separately on the subscriber.

pg_upgrade link mode

pg_upgrade --link hard-links relation files instead of copying them, making the upgrade fast but leaving the old cluster unusable. Run --check first and analyze the new cluster afterward.

EDB Migration Toolkit

EDB Migration Toolkit (MTK) migrates Oracle, SQL Server, and MySQL schemas and data to EDB Postgres Advanced Server, converting PL/SQL and DDL where possible and reporting migration issues.

Oracle compatibility in EPAS

EDB Postgres Advanced Server provides Oracle-compatible features such as DUAL, ROWNUM, NVL, DBMS_OUTPUT, and PL/SQL packages, controlled by edb_redwood_* parameters absent in community PostgreSQL.

Sequence migration step

Logical dumps and replication do not carry sequence state. After migration, call setval on each sequence to its current maximum value so new inserts do not collide with existing rows.

scram-sha-256 authentication

scram-sha-256 is the default password method in PostgreSQL 14 and later. Set password_encryption to scram-sha-256 before creating or changing roles so stored hashes use the strong format.

Row-level security policy

Enable RLS on a table, then create policies with USING for visibility and WITH CHECK for writes. The table owner bypasses policies unless FORCE ROW LEVEL SECURITY is set.

pgaudit extension

pgaudit logs statement, role, and object-level access controlled by pgaudit.log settings such as read, write, ddl, and role. It supports compliance auditing beyond basic log_statement output.

TLS verify-full mode

sslmode=verify-full makes the client validate the server certificate against the CA and confirm the hostname matches. It prevents man-in-the-middle attacks that encrypt-only modes allow.

Public schema hardening

By default PUBLIC has CREATE on the public schema in older versions. Revoking CREATE from PUBLIC prevents unprivileged users from creating objects that could hijack search_path resolution.

EDB Postgres Distributed

EDB Postgres Distributed provides multi-master replication with configurable conflict resolution, group commit, and commit scopes. It uses the bdr extension and a proxy layer for connection routing.

EDB PEM architecture

EDB Postgres Enterprise Manager uses a central server storing metrics in a database, with a PEM agent on each monitored host. It provides dashboards, alerts, and capacity reporting.

pgvector distance operators

The vector extension supports <-> for L2 distance, <#> for negative inner product, and <=> for cosine distance. HNSW and IVFFlat indexes accelerate approximate nearest-neighbor search.