I85PREP
⌘ K
EDB PostgreSQL Professional study materials
EDB PostgreSQL Professional study resources

EDB PostgreSQL Professional practice questions

One question at a time. Build your EDB PostgreSQL Professional understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1PostgreSQL Core Database Administration

Which PostgreSQL process is responsible for writing dirty shared buffers to disk during normal operation?

  1. The WAL writer process
  2. The stats collector process
  3. The autovacuum launcher process
  4. The checkpointer process Correct answer.

Explanation

The checkpointer performs periodic checkpoints, flushing all dirty shared buffers to the data files and updating the control file with the checkpoint location. The WAL writer only flushes WAL buffers, and the autovacuum launcher schedules vacuum workers.

Question 2PostgreSQL Core Database Administration

What is the primary purpose of the pg_wal directory in a PostgreSQL data directory?

  1. To store write-ahead log segments used for crash recovery and replication Correct answer.
  2. To store temporary sort and hash files for large queries
  3. To hold configuration files such as postgresql.conf and pg_hba.conf
  4. To cache query plans for the planner

Explanation

pg_wal contains the write-ahead log segments that record every change before data pages are modified, enabling crash recovery and streaming replication. Temporary work files live in the base/pgsql_tmp area, and configuration files reside in the data directory root.

Question 3PostgreSQL Core Database Administration

A database administrator wants a backup that is consistent without stopping the server. Which built-in mechanism enables this?

  1. pg_dump with the --single-transaction flag
  2. pg_basebackup using the replication protocol Correct answer.
  3. pg_checksums --enable during operation
  4. COPY of the entire data directory while the server runs

Explanation

pg_basebackup performs an online physical base backup using the replication protocol, which coordinates with the server to produce a consistent snapshot. Copying the data directory of a running server risks torn pages and is not a valid backup method.

Question 4PostgreSQL Core Database Administration

Which parameter controls the maximum number of concurrent client connections the server accepts?

  1. max_connections Correct answer.
  2. shared_buffers
  3. max_worker_processes
  4. superuser_reserved_connections

Explanation

max_connections sets the maximum number of simultaneous client sessions the postmaster allows. superuser_reserved_connections only reserves a subset of those slots for superusers and does not raise the total.

Question 5PostgreSQL Core Database Administration

What does the pg_stat_activity view expose for each backend?

  1. The contents of the write-ahead log
  2. The physical file offsets of each relation
  3. The query plan chosen for the last statement
  4. The current query text, state, and wait event of active sessions Correct answer.

Explanation

pg_stat_activity reports per-session details such as the current query, connection state, wait event type, and backend start time. It does not reveal physical file offsets or WAL contents.

Question 6PostgreSQL Core Database Administration

A table accumulates dead tuples faster than autovacuum can remove them. Which setting directly raises the threshold before autovacuum triggers on that table?

  1. autovacuum_naptime
  2. vacuum_cost_delay
  3. autovacuum_max_workers
  4. autovacuum_vacuum_scale_factor Correct answer.

Explanation

autovacuum_vacuum_scale_factor determines the fraction of live tuples that must become dead before autovacuum triggers on a table. Raising it delays triggering; naptime and worker counts affect scheduling frequency, not the per-table threshold.

Question 7PostgreSQL Core Database Administration

Which command correctly creates a physical replication slot that will retain WAL for a standby?

  1. CREATE REPLICATION SLOT standby1 PHYSICAL;
  2. SELECT pg_create_logical_replication_slot('standby1', 'pgoutput');
  3. ALTER SYSTEM SET replication_slot = 'standby1';
  4. SELECT pg_create_physical_replication_slot('standby1'); Correct answer.

Explanation

The function pg_create_physical_replication_slot creates a physical slot that prevents WAL removal until the standby consumes it. Logical slots are created with pg_create_logical_replication_slot and require an output plugin.

Question 8PostgreSQL Core Database Administration

What is the effect of setting wal_level to logical?

  1. It enables synchronous commit by default
  2. It compresses WAL segments automatically
  3. It adds the information needed for logical decoding to the WAL Correct answer.
  4. It disables crash recovery to reduce WAL volume

Explanation

wal_level=logical includes the extra row-level information required for logical decoding and logical replication. It does not disable crash recovery or turn on synchronous_commit.

Question 9PostgreSQL Core Database Administration

Which statement about a tablespace in PostgreSQL is correct?

  1. A tablespace stores only indexes, never tables
  2. A tablespace is a logical grouping of schemas
  3. A tablespace can only be created inside the base directory
  4. A tablespace is a directory location outside the data directory where relations can be stored Correct answer.

Explanation

A tablespace maps to a filesystem location, typically outside the main data directory, where relations of any kind can be placed. It is not a schema grouping and can hold both tables and indexes.

Question 10PostgreSQL Core Database Administration

A maintenance window requires reclaiming disk space from a heavily updated table without blocking reads. Which command is appropriate?

  1. VACUUM (without FULL) Correct answer.
  2. REINDEX
  3. VACUUM FULL
  4. CLUSTER

Explanation

A plain VACUUM marks dead tuples reusable without taking an exclusive lock, so reads continue. VACUUM FULL rewrites the table and holds an ACCESS EXCLUSIVE lock, blocking reads for its duration.

Question 11PostgreSQL Core Database Administration

Which file must be reloaded to apply changes to host-based authentication rules?

  1. postgresql.auto.conf
  2. pg_hba.conf Correct answer.
  3. postgresql.conf
  4. pg_ident.conf

Explanation

pg_hba.conf holds host-based authentication rules and must be reloaded, for example with pg_reload_conf(), for changes to take effect. pg_ident.conf maps user names and does not define connection rules.

Question 12PostgreSQL Core Database Administration

What does the pg_ctl stop -m fast command do?

  1. It kills all backends immediately without a checkpoint
  2. It waits for clients to disconnect on their own
  3. It restarts the server immediately
  4. It disconnects clients and shuts down cleanly after a checkpoint Correct answer.

Explanation

Fast shutdown disconnects existing clients, performs a checkpoint, and exits cleanly. Immediate mode terminates without a checkpoint, and smart mode waits for clients to disconnect.

Question 13PostgreSQL Core Database Administration

Which view reports the number of sequential and index scans for each table?

  1. pg_stat_activity
  2. pg_stat_user_tables Correct answer.
  3. pg_stat_database
  4. pg_locks

Explanation

pg_stat_user_tables includes seq_scan and idx_scan counters per table, useful for spotting missing indexes. pg_stat_activity reports sessions, and pg_locks reports lock state.

Question 14PostgreSQL Core Database Administration

What is the role of the postmaster process in PostgreSQL?

  1. It listens for connections and forks a backend for each client Correct answer.
  2. It executes all user queries in a single thread
  3. It performs autovacuum on all tables
  4. It writes WAL records for every transaction

Explanation

The postmaster listens on the configured ports and forks a dedicated backend process for each accepted connection. Query execution happens in the backend processes, not in the postmaster.

Question 15PostgreSQL Core Database Administration

Which setting determines the maximum size of the WAL segment files?

  1. wal_segment_size, fixed at initdb time Correct answer.
  2. wal_keep_size
  3. max_wal_size
  4. min_wal_size

Explanation

wal_segment_size sets the WAL segment file size and is fixed when the cluster is initialized. max_wal_size and min_wal_size control how much WAL is retained, not the segment size.

Question 16PostgreSQL Core Database Administration

A read-only standby is configured with hot_standby=on. What happens when a user runs a long query on the standby while the primary replays a conflicting WAL record?

  1. The query is silently rewritten to run on the primary
  2. The conflicting query is canceled after max_standby_streaming_delay elapses Correct answer.
  3. The WAL replay pauses indefinitely until the query finishes
  4. The standby promotes itself to primary

Explanation

When replay conflicts with a running query, the standby waits up to max_standby_streaming_delay and then cancels the query to keep replay moving. The standby does not promote itself or redirect queries to the primary.

Question 17PostgreSQL Core Database Administration

Which parameter should be set on the primary to ensure a commit waits for a standby to acknowledge WAL?

  1. synchronous_standby_names Correct answer.
  2. hot_standby_feedback
  3. synchronous_commit
  4. wal_sender_timeout

Explanation

synchronous_standby_names lists the standbys whose acknowledgements count for synchronous commit. synchronous_commit controls the level of durability but not which standbys are considered synchronous.

Question 18PostgreSQL Core Database Administration

What does pg_dump -Fc produce?

  1. A physical base backup
  2. A custom-format archive restorable with pg_restore Correct answer.
  3. A directory-format dump
  4. A plain SQL text script

Explanation

The -Fc option produces a custom-format archive that pg_restore can read selectively. Plain SQL output is the default format, and directory format is selected with -Fd.

Question 19PostgreSQL Core Database Administration

Which action is required after changing shared_buffers in postgresql.conf?

  1. Restart the server Correct answer.
  2. Reinitialize the cluster
  3. Run pg_reload_conf() and nothing else
  4. Reload the configuration only

Explanation

shared_buffers allocates shared memory and can only be changed with a server restart. A reload does not resize shared memory segments.

Question 20PostgreSQL Core Database Administration

What is the purpose of the pg_stat_statements extension?

  1. To record every row change for logical replication
  2. To enforce row-level security policies
  3. To validate page checksums during reads
  4. To aggregate execution statistics for normalized SQL statements Correct answer.

Explanation

pg_stat_statements tracks normalized query texts with cumulative timing and call counts, helping identify expensive statements. It does not enforce security or record row changes.

Question 21PostgreSQL Core Database Administration

A cluster runs out of disk space because pg_wal keeps growing. Which condition most likely prevents WAL recycling?

  1. The synchronous_commit setting is off
  2. The checkpoint_timeout is too large
  3. An inactive replication slot is retaining WAL segments Correct answer.
  4. The autovacuum_vacuum_cost_delay is too low

Explanation

An inactive or lagging replication slot holds back the restart_lsn, so WAL segments cannot be removed and accumulate. Checkpoint timeout and vacuum cost settings do not directly prevent WAL recycling.

Question 22PostgreSQL Core Database Administration

Which lock mode does an UPDATE statement acquire on the target rows?

  1. ACCESS EXCLUSIVE on the table
  2. SHARE UPDATE EXCLUSIVE on the table only
  3. No locks at all
  4. ROW EXCLUSIVE on the table and row-level locks on modified rows Correct answer.

Explanation

An UPDATE takes a ROW EXCLUSIVE lock on the table and row-level locks on the rows it modifies, allowing concurrent reads and non-conflicting writes. ACCESS EXCLUSIVE would block all access and is used by operations like VACUUM FULL.

Question 23PostgreSQL Core Database Administration

What does the pg_rewind utility do?

  1. It restores a logical dump to a new database
  2. It rolls back a completed checkpoint
  3. It resynchronizes a former primary's data directory with a new primary after failover Correct answer.
  4. It rewinds a transaction using a savepoint

Explanation

pg_rewind efficiently resynchronizes a data directory that has diverged, typically a former primary, against a new primary by copying changed blocks. It does not roll back transactions or restore logical dumps.

Question 24PostgreSQL Core Database Administration

Which setting controls how long a client can remain idle before authentication completes before the server closes the connection?

  1. idle_in_transaction_session_timeout
  2. tcp_keepalives_idle
  3. statement_timeout
  4. authentication_timeout Correct answer.

Explanation

authentication_timeout limits the time allowed for a client to complete the authentication handshake. statement_timeout limits query execution time, and idle_in_transaction_session_timeout targets idle transactions.

Question 25PostgreSQL Core Database Administration

Which statement accurately describes a checkpoint's effect on recovery time?

  1. Frequent checkpoints lengthen crash recovery but reduce I/O load
  2. Checkpoints have no effect on recovery time
  3. Frequent checkpoints shorten crash recovery but increase I/O load Correct answer.
  4. Checkpoints eliminate the need for WAL during recovery

Explanation

A checkpoint records a point from which replay can begin, so more frequent checkpoints mean less WAL to replay after a crash, at the cost of more frequent write I/O. They never remove the need for WAL.

Question 26PostgreSQL Core Database Administration

Which PostgreSQL object stores cluster-wide metadata such as roles and databases, making it impossible to copy individual databases between clusters with a plain file copy?

  1. The pg_global tablespace Correct answer.
  2. The pg_filenode.map file
  3. The postgresql.auto.conf file
  4. The base directory of each database

Explanation

Cluster-wide catalogs such as pg_database and pg_authid live in the pg_global tablespace, which is shared by every database in the instance. Because these catalogs span databases, a database cannot be relocated by copying only its own directory; logical or physical backup methods must be used.

Question 27PostgreSQL Core Database Administration

Which catalog view should an administrator query to list every table in the current database together with its owning role and tablespace?

  1. information_schema.columns
  2. pg_tables Correct answer.
  3. pg_class joined with pg_roles
  4. pg_stat_user_tables

Explanation

The pg_tables view exposes one row per table with columns such as schemaname, tablename, tableowner, and tablespace. It is a convenient readable projection over pg_class and related catalogs.

Question 28PostgreSQL Core Database Administration

A database is created with CREATE DATABASE sales TEMPLATE template0. What is the primary reason to choose template0 rather than template1?

  1. template0 automatically grants CONNECT to PUBLIC
  2. template0 is guaranteed to contain no user-defined objects or local settings Correct answer.
  3. template0 can be modified freely while template1 is read-only
  4. template0 uses a smaller default encoding that speeds creation

Explanation

template0 is a pristine template that is never modified, so databases cloned from it contain only the original system objects. Using template1 risks inheriting any objects or database-level settings that were added to it.

Question 29PostgreSQL Core Database Administration

Which pg_dump option produces a plain SQL script that can be replayed with psql?

  1. -Fc
  2. -Fd
  3. -Fp Correct answer.
  4. -Ft

Explanation

The -Fp (or --format=plain) option writes a text file of SQL commands, which is the default format and can be fed directly to psql. The other format letters select custom, directory, or tar archives.

Question 30PostgreSQL Core Database Administration

What does the SQL command REINDEX TABLE orders do in a running database?

  1. It recomputes table statistics used by the planner
  2. It validates foreign key constraints on orders
  3. It rebuilds the indexes on orders, replacing them with freshly built copies Correct answer.
  4. It removes dead tuples from the orders heap

Explanation

REINDEX rebuilds the specified indexes from the table data, which is the standard remedy for index corruption or bloat. In current versions it can do this without holding a lock that blocks writes for the whole operation.

Question 31PostgreSQL Core Database Administration

Which configuration parameter controls the directory where PostgreSQL writes its transaction log segments?

  1. data_directory
  2. wal_segment_size
  3. log_directory Correct answer.
  4. archive_command

Explanation

The log_directory parameter names the directory, relative to the data directory unless absolute, where WAL segments are written. It is distinct from data_directory, which locates the whole cluster.

Question 32PostgreSQL Core Database Administration

Which role attribute is required for a role to bypass row-level security policies on tables?

  1. REPLICATION
  2. BYPASSRLS Correct answer.
  3. CREATEROLE
  4. CREATEDB

Explanation

The BYPASSRLS attribute allows a role to ignore row-level security policies, and it is normally reserved for superusers or trusted maintenance accounts. It is granted with ALTER ROLE ... BYPASSRLS.

Question 33PostgreSQL Core Database Administration

An administrator wants the server to accept connections from a new subnet without restarting the instance. Which sequence is correct?

  1. Edit postgresql.conf and run pg_ctl reload
  2. Edit pg_hba.conf and restart the postmaster
  3. Edit pg_hba.conf, run pg_ctl reload, and confirm the change took effect Correct answer.
  4. Edit pg_ident.conf and run pg_ctl reload

Explanation

Changes to pg_hba.conf are applied by a configuration reload, which re-reads the file without dropping existing sessions. A restart is unnecessary and would interrupt service.

Question 34PostgreSQL Core Database Administration

A table receives heavy INSERT traffic and autovacuum is disabled. Which consequence is most likely?

  1. The transaction ID counter is reset automatically
  2. The visibility map stops being updated and the table bloats Correct answer.
  3. The table is converted to unlogged mode
  4. The table's indexes are automatically rebuilt

Explanation

Without vacuum, dead tuples accumulate and the visibility map is not refreshed, so the table grows and index-only scans become ineffective. Vacuum is also what freezes old transaction IDs, so the wraparound risk increases.

Question 35PostgreSQL Core Database Administration

A query filters on a column with a highly selective condition but the planner chooses a sequential scan. Which action is most likely to correct the plan?

  1. Lower the value of random_page_cost to reflect fast storage Correct answer.
  2. Increase the value of work_mem
  3. Run VACUUM FULL on the table
  4. Disable the enable_seqscan parameter permanently

Explanation

On fast storage such as SSDs, the default random_page_cost overstates the penalty of random reads, so the planner undervalues index scans. Reducing it, or lowering effective_io_concurrency accordingly, can make the index path cheaper.

Question 36PostgreSQL Core Database Administration

Which command produces a consistent logical backup of a single database while other sessions continue to write to it?

  1. pg_dump -Fc mydb Correct answer.
  2. pg_basebackup -D /backup
  3. pg_checksums --check
  4. cp -r $PGDATA /backup

Explanation

pg_dump takes a repeatable snapshot for the duration of the dump, so concurrent writes do not corrupt the output and the database remains available. The custom format also supports selective restore with pg_restore.

Question 37PostgreSQL Core Database Administration

A role needs to read all current and future tables in the reporting schema without being a superuser. Which grant is most appropriate?

  1. ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO analyst Correct answer.
  2. GRANT SELECT ON SCHEMA reporting TO analyst
  3. GRANT ALL ON SCHEMA reporting TO analyst
  4. GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analyst

Explanation

ALTER DEFAULT PRIVILEGES sets privileges that apply automatically to objects created later by the role that runs it, which covers future tables. A one-time GRANT ON ALL TABLES applies only to tables that already exist.

Question 38PostgreSQL Core Database Administration

What is the effect of running CHECKPOINT manually on a busy primary?

  1. It resets all statistics counters
  2. It forces WAL archiving to complete immediately
  3. It flushes all dirty buffers and records a consistent recovery point in the WAL Correct answer.
  4. It terminates long-running transactions

Explanation

A checkpoint writes dirty shared buffers to disk and records the redo location in the WAL, so crash recovery can begin from that point. It does not cancel sessions or reset statistics.

Question 39PostgreSQL Core Database Administration

Which statement correctly describes the behavior of TRUNCATE compared with DELETE on a large table?

  1. TRUNCATE cannot be rolled back inside a transaction
  2. TRUNCATE requires an exclusive lock and is not MVCC-safe for concurrent readers Correct answer.
  3. TRUNCATE preserves the table's indexes and statistics unchanged
  4. TRUNCATE removes rows by scanning and logging each one

Explanation

TRUNCATE takes an ACCESS EXCLUSIVE lock and is not MVCC-safe, so concurrent transactions see the table as empty after the truncate commits. It is fast because it does not scan or log individual row deletions.

Question 40PostgreSQL Core Database Administration

An administrator wants a role to be able to create objects in the app schema but not to create databases or roles. Which setup is correct?

  1. ALTER ROLE appuser CREATEDB CREATEROLE
  2. ALTER DATABASE appdb OWNER TO appuser
  3. GRANT CREATE, USAGE ON SCHEMA app TO appuser Correct answer.
  4. GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser

Explanation

Schema-level CREATE combined with USAGE allows the role to create objects inside the schema without cluster-wide privileges. Database ownership or CREATEDB would grant broader rights than required.

Question 41PostgreSQL Core Database Administration

Which parameter limits the number of concurrent connections the postmaster will accept?

  1. reserved_connections
  2. max_connections Correct answer.
  3. superuser_reserved_connections
  4. max_worker_processes

Explanation

max_connections sets the total number of concurrent client connections allowed. Some slots within that total can be reserved for superusers or replication.

Question 42PostgreSQL Core Database Administration

Which command shows the replication slots currently defined in the cluster and whether they are active?

  1. SELECT * FROM pg_stat_replication
  2. SELECT * FROM pg_stat_activity
  3. SELECT * FROM pg_replication_slots Correct answer.
  4. SELECT * FROM pg_stat_wal_receiver

Explanation

pg_replication_slots lists each slot with its type, active flag, and retained WAL position. pg_stat_replication reports connected senders rather than slot definitions.

Question 43PostgreSQL Core Database Administration

A large table has a foreign key referencing a small lookup table, and deletes on the lookup table are slow. Which index is likely missing?

  1. An index on the foreign key column in the referencing table Correct answer.
  2. A unique index on the lookup table's primary key
  3. A hash index on the referencing table
  4. A partial index on the lookup table

Explanation

Deleting or updating a referenced row requires checking the referencing table, so an index on the foreign key column makes those checks fast. Without it, each delete on the parent performs a sequential scan of the child table.

Question 44PostgreSQL Core Database Administration

Which statement about the pg_stat_activity view is accurate?

  1. It requires superuser privileges to see any rows
  2. It shows one row per database, not per session
  3. It is cleared whenever a checkpoint occurs
  4. It displays one row per server process, including backend type and wait event Correct answer.

Explanation

pg_stat_activity has a row for each backend process with columns such as pid, state, backend_type, and wait_event. Non-superusers see limited columns for other users' sessions.

Question 45PostgreSQL Core Database Administration

A table is partitioned by range on a date column, and queries always filter on the current month. Which configuration most directly helps the planner exclude irrelevant partitions?

  1. constraint_exclusion Correct answer.
  2. enable_partitionwise_join
  3. enable_seqscan
  4. jit

Explanation

constraint_exclusion, set to partition or on, lets the planner use partition constraints to skip partitions that cannot satisfy the query predicate. This is the mechanism behind partition pruning at plan time.

Question 46PostgreSQL Core Database Administration

A standby is promoted, and the old primary must rejoin as a standby. Which tool is designed to resynchronize it efficiently by copying only changed blocks?

  1. pg_resetwal
  2. pg_dumpall
  3. pg_rewind Correct answer.
  4. pg_basebackup

Explanation

pg_rewind compares the two data directories and copies only the blocks that diverged after the timeline split, which is much faster than a full base backup. It requires that the target has either wal_log_hints enabled or data checksums.

Question 47PostgreSQL Core Database Administration

Which condition causes a query to wait on a heavyweight lock that appears in pg_locks as mode ShareLock on a relation during a CREATE INDEX operation?

  1. A concurrent ANALYZE holds RowExclusiveLock
  2. Autovacuum is running on an unrelated table
  3. The WAL sender is lagging behind
  4. A concurrent transaction holds a conflicting lock such as AccessExclusiveLock on the same relation Correct answer.

Explanation

Lock acquisition waits occur when a requested mode conflicts with a mode already held on the same object. An AccessExclusiveLock held by another session conflicts with the ShareLock requested by index creation, so the statement blocks.

Question 48PostgreSQL Core Database Administration

An administrator sets synchronous_commit to remote_apply on the primary. What guarantee does a committing transaction obtain?

  1. The commit returns once the local WAL record is written to disk
  2. The commit returns once the standby has received the WAL record
  3. The commit returns once the standby has flushed the WAL record to disk
  4. The commit returns once the standby has applied the change and it is visible to queries there Correct answer.

Explanation

remote_apply waits until the synchronous standby reports that it has replayed the commit record, so the change is visible to read queries on the standby. This is the strongest of the synchronous_commit levels.

Question 49PostgreSQL Core Database Administration

A cluster shows steadily increasing disk usage in pg_wal while archiving appears enabled. Which diagnosis is most consistent with the evidence?

  1. wal_level is set to minimal
  2. checkpoint_timeout is too short
  3. max_wal_senders is too high
  4. archive_command is failing, so segments cannot be removed Correct answer.

Explanation

When archiving is enabled, WAL segments are retained until archive_command reports success, so a failing command causes pg_wal to grow without bound. Checking pg_stat_archiver for failed attempts confirms this.

Question 50PostgreSQL Core Database Administration

An administrator wants PostgreSQL to reclaim storage from a table that has accumulated dead tuples, but must keep the table readable and writable throughout the operation. Which command accomplishes this?

  1. CLUSTER on the table
  2. TRUNCATE on the table
  3. VACUUM on the table Correct answer.
  4. VACUUM (FULL) on the table

Explanation

A plain VACUUM marks dead tuple space as reusable by future inserts while leaving the table available for concurrent reads and writes; it does not return the space to the operating system. VACUUM FULL rewrites the table into a new file, which reclaims disk space but takes an ACCESS EXCLUSIVE lock and blocks all access for the duration.