Free · self-paced · PostgreSQL 17

Prepare for PostgreSQL certification

Working toward the DBA-1 exam? Start with the eight published domains. For each one, learn the reason behind the command, practise on a disposable server, then explain your decision without looking at the answer.

Every reading and practice check on this page is free. No account or email is required. This is an independent study guide aligned to the publicly listed DBA-1 topics (opens in a new tab), not an official course, exam, question bank or guarantee of passing. The exam scope may change; verify it with the certification provider before sitting the exam.

Source links on this page open in a new tab.

Command snippets were re-run on a disposable PostgreSQL 17.11 Docker lab for this draft.

Outline checked on 2026-10-13; re-check the provider page before any exam booking.

How to use this study path

  1. 1. Follow the domains in order. Tick off the topic list against the official exam outline.
  2. 2. Read the linked PostgreSQL 17 manual chapters; reproduce the practice task on a disposable instance.
  3. 3. Close your notes and run the recall checklist first, then answer all explain-before-revealing checks out loud.
  4. 4. At the end, retry missed domains without looking. Self-checks are practice, not a predicted exam score.

Exam availability snapshot (verify before planning)

As of 2026-10-13, OAPE lists the first public DBA-1 exam on 23 October 2026 (PGConf.EU Community Events Day, Valencia) and registration is closed; join the waiting list for the next sitting.

Current source: OAPE certifications page (opens in a new tab) and the waiting list form (opens in a new tab).

The format (logic-first, not question-guessing)

For each domain, use one fixed loop: scope → why it behaves this way → run the command → collect evidence → make and defend the decision. We do not know the actual exam questions, but this loop gives you correct reasoning and defensible answers.

Exam format details (for example exact question count, time limit, or item type) are not listed on this page; therefore each domain includes a recall checklist to strengthen name-level memory alongside reasoning.

Simple study plan (exam prep autopilot)

  1. 1. Week 1: Domains 1-2 (installation + SQL objects). Run every example yourself.
  2. 2. Week 2: Domains 3-4 (security + backup). Finish one verified restore in a disposable environment.
  3. 3. Week 3: Domains 5-6 (architecture + transactions). Diagnose one real lock wait using evidence views.
  4. 4. Week 4: Domain 7 (vacuum/autovacuum). Run VACUUM/ANALYZE/FREEZE comparisons on one lab table.
  5. 5. Week 5: Domain 8 (monitoring/logging) + mixed revision. Build your final logging/metrics recall sheet and do one timed full revision.

Domain 1 of 8

Installation, versioning and basic configuration

Know the scope

Reviewed 0 of 9 topics. Checkmarks are stored only in this browser; they are not an exam-readiness score.

Understand the why

A PostgreSQL installation supplies binaries; initdb creates a data directory for one server instance. PGDG packages and distribution packages can differ in version cadence and layout. Service units, file locations and systemctl availability depend on the operating system and package source.

postgresql.conf controls server settings; pg_hba.conf controls which connections may authenticate and how. A listening server and a successful pg_isready check do not prove the application can authenticate or run queries.

A minor update stays within a major version; a major upgrade changes the on-disk/version compatibility problem. Pick an upgrade method only after checking extensions, downtime and recovery options.

Try it safely

On a disposable PG17 instance, find the data directory and active config files, inspect pg_hba.conf, inspect tablespaces, then restart with pg_ctl or systemctl and verify a real authenticated query. Capture one TLS-related setting and one pg_isready result. For package installation, record the package source and command used on your OS (for example PGDG package manager command versus distribution package command). Include one minor-update evidence line in your notes (installed patch version before/after). Do not practise upgrades on the only copy of data.

SELECT version();
SHOW data_directory;
SHOW config_file;
SHOW hba_file;
SHOW ssl;
SHOW default_tablespace;
SELECT spcname, pg_tablespace_location(oid)
FROM pg_tablespace
ORDER BY spcname;
SELECT ssl, version, cipher
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();
-- Service control examples (shell, not SQL):
-- pg_ctl -D "DATA_DIR" restart
-- systemctl restart postgresql
-- pg_isready -h HOST -p PORT

What to notice: The active paths can differ from package defaults. After changes, use pg_isready for server availability AND a real authenticated query for application access. SHOW ssl reports server setting intent; pg_stat_ssl on your backend proves whether this session is actually encrypted. Tablespace output should distinguish built-in entries from user-created locations.

Collect this evidence

  • Record the exact paths returned by SHOW data_directory/config_file/hba_file.
  • Capture one successful authenticated query after restart, not only pg_isready output.
  • Capture one service-control proof (pg_ctl/systemctl command output) and one TLS setting observation.
  • Capture default_tablespace plus one pg_tablespace row and explain what it means.
  • Record which package source/install command you used in your lab environment.
  • List one major-upgrade option and one rollback constraint for your environment.

Decision rule

If you cannot prove active runtime paths and successful authenticated access after restart, do not mark this domain complete.

Common failure in this domain (and the fix)

Failure pattern: Server starts and pg_isready is green, but the app still fails to connect.

Fix approach: Verify runtime paths and auth together: SHOW data_directory/config_file/hba_file, then run one authenticated query with the same host/port/TLS settings as the app.

  • Triage guide — Fast decision flow when connection checks conflict.
  • SQLSTATE index — Decode authentication and startup errors quickly.

Recall checklist (name + one-line meaning)

  • PGDG repo vs distribution repo
  • postgresql.conf vs pg_hba.conf
  • pg_ctl vs systemctl
  • Minor update vs major upgrade
  • pg_upgrade vs dump/restore vs logical replication

Explain before revealing

pg_isready reports accepting connections, but the app cannot log in. Is PostgreSQL healthy?

The server is responding, but application readiness is unproven. Check the app's host/port, TLS requirements, pg_hba.conf rule, role credentials and server logs; run an authenticated query with the same connection parameters.

Why might a logical-replication migration be preferable to pg_upgrade, and what does it cost?

It can reduce cutover downtime and allow version overlap, but requires replication setup, monitoring, object/sequence handling and a controlled cutover. pg_upgrade is usually simpler when a planned stop is acceptable. Validate both against the actual workload.

What proves a minor update completed safely beyond the package manager saying success?

Prove version, startup and workload access together: confirm server version, inspect logs for startup errors, run authenticated application-like queries, and verify extensions and service units against expected state.

When would you prefer pg_ctl over systemctl in practice?

Use systemctl when PostgreSQL is managed as an OS service. Use pg_ctl for local/non-service clusters, test environments or explicit cluster-level control where the service manager is not the runtime owner.

Read the source

You are ready for this domain when: You can explain package choice, show active runtime config paths, and defend one major-upgrade method with its rollback constraints.

Use our matching resources

  • Architecture atlas — Map how PostgreSQL components connect before tuning anything.
  • SQLSTATE index — Use real error classes while revising config and startup issues.

Domain 2 of 8

Connections, objects and basic SQL

Know the scope

Reviewed 0 of 9 topics. Checkmarks are stored only in this browser; they are not an exam-readiness score.

Understand the why

A database contains schemas; an unqualified object name is resolved through search_path. The same SQL can resolve to a different object when the path or role changes.

An index can make a qualifying access path cheaper, but it adds write and maintenance cost. A materialized view stores a result and needs refreshing; an ordinary view does not store its query result.

Sequences allocate values independently of transaction rollback. Do not infer gap-free IDs from a sequence.

Try it safely

Create a database and two schemas with tables of the same name. Use psql to inspect the active database, role and search_path, and show which table an unqualified query reads. Then run one compact object drill for view/materialized view/sequence/function and one psql meta-command check. Use a fresh disposable database or clean existing lab objects before rerunning.

DROP SCHEMA IF EXISTS dba1_a CASCADE;
DROP SCHEMA IF EXISTS dba1_b CASCADE;
CREATE SCHEMA dba1_a;
CREATE SCHEMA dba1_b;
CREATE TABLE dba1_a.sample (id integer);
CREATE TABLE dba1_b.sample (id integer);
INSERT INTO dba1_a.sample VALUES (101);
INSERT INTO dba1_b.sample VALUES (202);
SET search_path = dba1_b, public;
SELECT current_schema(), current_database(), current_user;
SELECT * FROM sample;
SET search_path = dba1_a, public;
SELECT * FROM sample;
CREATE SEQUENCE dba1_a.seq_demo START 1;
CREATE VIEW dba1_a.v_sample AS SELECT id FROM dba1_a.sample;
CREATE MATERIALIZED VIEW dba1_a.mv_sample AS SELECT count(*) AS n FROM dba1_a.sample;
CREATE OR REPLACE FUNCTION dba1_a.id_passthrough(x integer) RETURNS integer LANGUAGE sql AS $$ SELECT x $$;
-- psql meta-command to run in psql client: \dn+

What to notice: sample resolves using search_path order. With distinct row values, you can prove resolution changed even though SQL text stayed the same. The compact object drill confirms you can create and inspect key exam object types, not only search_path behavior.

Collect this evidence

  • Show current_schema/current_database/current_user from your session output.
  • Demonstrate one query changing result only by changing search_path order.
  • Write one sentence on when an index is the right fix and when it is not.
  • Capture one result proving your view/materialized view/sequence/function drill succeeded.

Decision rule

If you cannot explain object resolution and index trade-offs from evidence, keep practising this domain before moving forward.

Common failure in this domain (and the fix)

Failure pattern: Query returns unexpected rows because object resolution changed silently.

Fix approach: Pin schema-qualified names in critical SQL, and confirm search_path + current_user in the same session before concluding data is wrong.

Recall checklist (name + one-line meaning)

  • psql meta-commands and connection options
  • search_path resolution order
  • Common data types and casts
  • View vs materialized view
  • Sequence behavior under rollback

Explain before revealing

A query works in psql but selects the wrong table in the application. What would you check first?

Compare database, role and search_path in both sessions; inspect schema-qualified table names. Avoid treating the query text alone as the full explanation.

A transaction inserts a row then rolls back. Must its sequence value be reused?

No. Sequence advancement is not rolled back with the row insert. Gaps are normal and are not evidence of missing committed rows.

When should you choose a materialized view over a normal view?

Choose a materialized view when precomputed results are worth storage and refresh management overhead for performance or reporting stability. Use a normal view when fresh underlying data is required each query.

A filter uses text-to-integer comparison and index usage disappears. What should you check?

Check implicit casts and expression sides. Type mismatches can force runtime conversion and prevent index-friendly predicates. Align data types deliberately and verify with EXPLAIN.

Read the source

You are ready for this domain when: Given any SQL snippet, you can identify object resolution via search_path and justify index or view choices without guessing.

Use our matching resources

Domain 3 of 8

Roles and privileges

Know the scope

Reviewed 0 of 4 topics. Checkmarks are stored only in this browser; they are not an exam-readiness score.

Understand the why

Authentication proves who connected; authorization determines what that role may do. A LOGIN role is not automatically the owner of the objects it uses.

Privileges can come through role membership and through grants on objects. Schema USAGE and object privileges are separate checks.

The name PUBLIC represents all roles, not one ordinary login. On our PG17.11 lab, PUBLIC has USAGE but not CREATE on schema public; still verify your own cluster defaults before assuming behavior.

Try it safely

Create a login and a separate owning role in a disposable database. Grant only the schema and table permissions required for a SELECT, test the login, then revoke one permission and explain the failure.

CREATE ROLE dba1_reader NOLOGIN;
DROP ROLE IF EXISTS dba1_lab_login;
CREATE ROLE dba1_lab_login LOGIN PASSWORD 'lab-pass';
CREATE SCHEMA dba1_private;
CREATE TABLE dba1_private.sample (id integer);
INSERT INTO dba1_private.sample VALUES (1);
GRANT dba1_reader TO dba1_lab_login;
GRANT USAGE ON SCHEMA dba1_private TO dba1_reader;
GRANT SELECT ON dba1_private.sample TO dba1_reader;
SELECT has_schema_privilege('dba1_reader', 'dba1_private', 'USAGE');
-- Connect as dba1_lab_login and test:
-- SELECT * FROM dba1_private.sample;
REVOKE USAGE ON SCHEMA dba1_private FROM dba1_reader;

What to notice: An admin in the disposable database can create the objects. Test real access by connecting as dba1_lab_login, proving SELECT success first, then confirming permission-denied behavior after revoke. For real password-auth validation, use a pg_hba.conf rule that enforces password auth (for example scram-sha-256) instead of trust/peer-only local auth.

Collect this evidence

  • Keep one successful has_schema_privilege proof.
  • Capture one permission-denied error after revoking schema USAGE or table SELECT.
  • List which role owns the object and which role only consumes it.

Decision rule

Do not conclude 'auth issue fixed' until you have separate evidence for authentication success and object-privilege success.

Common failure in this domain (and the fix)

Failure pattern: Role can log in but gets permission denied on SELECT.

Fix approach: Separate auth from authz: confirm role identity and membership, then validate schema USAGE and object-level grants independently.

Recall checklist (name + one-line meaning)

  • LOGIN attribute vs object ownership
  • Role membership and inheritance
  • Schema USAGE vs table privileges
  • PUBLIC pseudo-role behavior
  • REVOKE impact verification

Explain before revealing

A role has SELECT on a table but gets permission denied when using a schema-qualified name. Why?

It may lack USAGE on the schema. Check schema permissions and role membership as well as table privileges.

Does changing pg_hba.conf grant a user access to a table?

No. pg_hba.conf determines whether and how a client authenticates; object privileges are managed separately with roles and GRANT/REVOKE.

How do you verify the public schema default without relying on memory?

Query has_schema_privilege for PUBLIC on schema public (USAGE and CREATE separately) in the target cluster, then confirm object creation behavior with a disposable login role.

A user can connect but SELECT fails on one table only. What path narrows root cause fastest?

Confirm role identity and membership, then check schema USAGE and object privileges for that exact relation. Distinguish missing grants from wrong object/schema resolution before changing pg_hba.

Read the source

You are ready for this domain when: You can separate authentication from authorization, then prove effective access with role membership plus schema/object privileges.

Use our matching resources

Domain 4 of 8

Backup and recovery

Know the scope

Reviewed 0 of 6 topics. Checkmarks are stored only in this browser; they are not an exam-readiness score.

Understand the why

A logical dump represents SQL objects and data; a physical base backup copies a cluster-level starting point. They have different restore procedures and coverage.

A base backup by itself is not an arbitrary point-in-time restore. Continuous archiving needs an unbroken, retrievable WAL history covering the target time.

A backup is not evidence of recoverability until you restore it somewhere disposable and check the result, including roles and application dependencies where relevant.

Try it safely

First create a disposable database named dba1_lab with a test table. In a shell, dump and restore it into a second database, then compare objects and data. Also capture archive_mode/archive_command and run one pg_basebackup command in a disposable cluster where replication permissions, connection details and pg_hba rules are prepared.

pg_dump -Fc -f dba1.dump dba1_lab
createdb dba1_restore
pg_restore -d dba1_restore dba1.dump
psql -d postgres -c "SHOW archive_mode; SHOW archive_command;"
# Replace HOST/PORT/REPLICATION_USER with real values (do not paste angle brackets in shell):
# Confirm host/port/user/version and that target directory is suitable before running:
# pg_basebackup may wait for a checkpoint. In a lab, add -c fast to avoid a long pause.
pg_basebackup -h HOST -p PORT -U REPLICATION_USER -D ./basebackup_lab -Fp -Xs -P -c fast

What to notice: Run these in a shell against databases you own, not in psql. Confirm restored row counts and object privileges. If pg_basebackup fails, keep the exact error and required prereqs (replication role/pg_hba/settings/target directory state) as evidence instead of skipping it silently.

Collect this evidence

  • Store backup command output and one restored row-count check.
  • List exactly which method you executed (logical or physical) and what it restores.
  • Document one condition where PITR is impossible (for example, missing WAL range).
  • For pg_basebackup, record server connection parameters, exit status and any prerequisite failure.

Decision rule

If you cannot prove a restore in a separate database, treat backup status as unverified.

Common failure in this domain (and the fix)

Failure pattern: Backup exists, but restore confidence is still unknown.

Fix approach: Treat backup as incomplete until a disposable restore succeeds and required roles/objects are validated in the restored target.

Recall checklist (name + one-line meaning)

  • Logical backup scope (pg_dump/pg_dumpall)
  • Physical backup scope (pg_basebackup)
  • archive_mode and archive_command purpose
  • PITR WAL continuity requirement
  • Restore verification versus backup completion

Explain before revealing

You have a base backup but a missing archived WAL segment before the desired recovery time. Can you promise PITR?

No. PITR needs the WAL sequence from the base backup through the target. Identify the missing segment and test which recovery point is actually reachable.

Why is a successful pg_dump exit code insufficient as a recovery test?

It does not prove the dump restores, that needed globals/roles are available, or that the restored application works. Run and inspect a restore.

What does pg_basebackup give you that pg_dump does not?

It captures a physical cluster state for server-level recovery workflows and replication bootstrapping, while pg_dump captures logical objects/data. They serve different recovery objectives.

archive_mode is on but archive_command is broken. Can you claim PITR readiness?

No. PITR requires retrievable, continuous WAL archives. A failing archive pipeline means future recovery points can be lost even if base backups continue.

Read the source

You are ready for this domain when: You can choose logical versus physical backup for a stated recovery target and prove one restore end-to-end in a disposable environment.

Use our matching resources

Domain 5 of 8

Server architecture and background processes

Know the scope

Reviewed 0 of 6 topics. Checkmarks are stored only in this browser; they are not an exam-readiness score.

Understand the why

Client sessions are handled by backend processes. Shared buffers hold database pages across backends, while work_mem is a per-operation limit, not one fixed pool for the whole server.

A changed page need not be written to the data file before commit. WAL protects recovery by recording changes ahead of the corresponding data-page write.

The checkpointer, background writer and backends can all write dirty data pages for different reasons. An observed write spike needs evidence before changing memory or checkpoint settings.

Try it safely

Draw one write transaction from client backend through shared buffers and WAL to checkpoint. Annotate which step makes a commit durable under the selected settings and which only writes a data page.

SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW synchronous_commit;
SELECT pid, backend_type FROM pg_stat_activity LIMIT 10;

What to notice: Server-wide and per-operation memory settings have different scopes. Process names and the durability setting inform your diagram; they do not prove how much memory an individual query used.

Collect this evidence

  • Capture shared_buffers/work_mem/maintenance_work_mem/synchronous_commit values.
  • Record at least three backend_type rows from pg_stat_activity.
  • Mark durability point and data-page write point separately in your diagram.

Decision rule

If you cannot separate durability from buffering in your explanation, do not tune memory or checkpoint settings yet.

Common failure in this domain (and the fix)

Failure pattern: Memory tuning is changed first, but write pressure root cause remains unclear.

Fix approach: Map durability path first (WAL/commit/checkpoint) and only then tune shared_buffers/work_mem with evidence from workload behavior.

Recall checklist (name + one-line meaning)

  • Postmaster and backend process roles
  • Shared buffers vs per-operation memory
  • work_mem vs maintenance_work_mem
  • Checkpointer/background writer/backend writes
  • WAL buffers and WAL writer

Explain before revealing

Does work_mem=64MB cap total query memory at 64MB for the server?

No. The limit applies to eligible operations; multiple operations in a query and concurrent sessions can each use memory. Estimate concurrency and plan shape before raising it.

Why can a committed change survive a crash even when its dirty data page was not checkpointed?

WAL records the change before the data page is written; recovery replays the required WAL. Durability depends on the actual commit and WAL settings.

What is the practical difference between checkpointer and background writer responsibilities?

Both write dirty buffers, but checkpointer coordinates checkpoint completion and fsync-related consistency targets, while background writer smooths writes between checkpoints to reduce backend write pressure.

If shared_buffers is increased, does that alone guarantee fewer checkpoints?

No. Checkpoint frequency depends on WAL generation and checkpoint settings too. Larger buffers can shift pressure, but evidence from WAL/checkpoint metrics is required before claiming improvement.

Read the source

You are ready for this domain when: You can trace commit durability and distinguish shared versus per-operation memory without conflating them.

Use our matching resources

Domain 6 of 8

MVCC and transaction management

Know the scope

Reviewed 0 of 5 topics. Checkmarks are stored only in this browser; they are not an exam-readiness score.

Understand the why

MVCC lets readers and writers work with different row versions. Long-running transactions can keep old versions visible and delay cleanup.

Isolation choices affect which anomalies a transaction may see. Serializable transactions can fail with a serialization error; the application must be prepared to retry the whole transaction.

Blocking and a deadlock are not the same. Use live session and lock evidence to identify blocker and waiter before canceling work.

Try it safely

Use two disposable psql sessions to hold a row lock in one and wait in the other. Find both sessions and explain the wait using pg_stat_activity and pg_locks before ending either session. Then run one isolation-level comparison (READ COMMITTED vs REPEATABLE READ) and one deliberate deadlock lab with exact two-session steps.

-- One-time setup (run and COMMIT before opening Session A/B):
CREATE TABLE IF NOT EXISTS public.dba1_lock_lab (id integer PRIMARY KEY, v integer);
INSERT INTO public.dba1_lock_lab (id, v) VALUES (1, 10), (2, 20)
ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v;

-- Blocking lab
-- Session A
BEGIN;
UPDATE public.dba1_lock_lab SET v = v + 1 WHERE id = 1;

-- Session B (run while Session A is open)
BEGIN;
UPDATE public.dba1_lock_lab SET v = v + 1 WHERE id = 1;

-- Observer session
SELECT a.pid, a.datname, a.state, a.wait_event_type, a.wait_event,
       pg_blocking_pids(a.pid) AS blockers,
       l.locktype, l.mode, l.granted
FROM pg_stat_activity AS a
LEFT JOIN pg_locks AS l ON l.pid = a.pid
WHERE a.pid <> pg_backend_pid();

SELECT a.pid, a.datname, a.state, l.locktype, l.mode, l.granted
FROM pg_stat_activity AS a
JOIN pg_locks AS l ON l.pid = a.pid
WHERE NOT l.granted;

SHOW deadlock_timeout;

-- Cleanup blocking lab (order matters)
ROLLBACK; -- Session A first: releases the row lock, Session B's UPDATE then completes
ROLLBACK; -- Session B second
-- Alternative: press Ctrl-C in Session B to cancel its waiting UPDATE, then ROLLBACK both.

-- Isolation mini-lab (autocommit in Session B is acceptable):
-- Session A: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT v FROM public.dba1_lock_lab WHERE id = 1;
-- Session B: UPDATE public.dba1_lock_lab SET v = v + 10 WHERE id = 1; COMMIT;
-- Session A: SELECT v FROM public.dba1_lock_lab WHERE id = 1; COMMIT;

-- Deadlock mini-lab
-- Session A: BEGIN; UPDATE public.dba1_lock_lab SET v = v + 1 WHERE id = 1;
-- Session B: BEGIN; UPDATE public.dba1_lock_lab SET v = v + 1 WHERE id = 2;
-- Session A: UPDATE public.dba1_lock_lab SET v = v + 1 WHERE id = 2;
-- Session B: UPDATE public.dba1_lock_lab SET v = v + 1 WHERE id = 1;
-- One session should raise deadlock detected; then ROLLBACK both.

What to notice: Run while Session B is blocked. Correlate blocker PID, lock mode and granted=false rows before action. Multiple lock rows per PID are normal; isolate the waiting lock before deciding. A row-update wait usually appears in pg_locks as a ShareLock on a transactionid, not as a 'row lock' mode. Session B waits for Session A's transaction to end. For deadlock, both sessions must hold one row lock and request the other; a deadlock is a cycle, not plain waiting.

Collect this evidence

  • Capture blocker and waiter PIDs from pg_blocking_pids output.
  • Record one lock wait with wait_event_type = Lock in a controlled lab.
  • Write one safe action sequence: identify → assess impact → cancel/terminate decision.

Decision rule

If you cannot identify the blocker chain with evidence, do not terminate sessions in production.

Common failure in this domain (and the fix)

Failure pattern: A blocked session is treated as deadlock and killed too early.

Fix approach: Prove blocker/waiter chain with pg_blocking_pids + pg_locks first; cancel/terminate only after impact assessment and ownership check.

Recall checklist (name + one-line meaning)

  • MVCC old-row version behavior
  • READ COMMITTED vs REPEATABLE READ vs SERIALIZABLE
  • Row locks vs table locks
  • pg_locks and pg_blocking_pids usage
  • deadlock_timeout role

Explain before revealing

A transaction fails at SERIALIZABLE with a serialization failure. Should you retry just its final statement?

No. Retry the complete transaction because earlier reads and decisions may no longer be valid.

One session waits on another. Does that prove a deadlock?

No. A single wait is ordinary blocking. A deadlock is a cycle of dependencies that PostgreSQL detects and breaks by aborting one transaction.

What evidence from pg_locks confirms who is waiting versus holding?

Use granted=false rows for waiters and granted=true rows for holders on the same relation/lock target, correlated by PID and lock mode with pg_stat_activity context.

Why can READ COMMITTED and REPEATABLE READ return different results within one transaction?

READ COMMITTED can see newer committed versions across statements; REPEATABLE READ keeps a stable snapshot for the transaction, preventing non-repeatable reads but still requiring lock awareness.

Read the source

You are ready for this domain when: You can diagnose blocking with evidence, explain isolation trade-offs, and choose cancel/terminate only after identifying impact.

Use our matching resources

Domain 7 of 8

VACUUM, ANALYZE and autovacuum

Know the scope

Reviewed 0 of 5 topics. Checkmarks are stored only in this browser; they are not an exam-readiness score.

Understand the why

Ordinary VACUUM makes dead-tuple space reusable; it does not usually shrink the table file. VACUUM FULL rewrites the table and takes a stronger lock.

ANALYZE updates planner statistics; it does not remove dead tuples. VACUUM (ANALYZE) combines two distinct jobs.

Autovacuum also protects against transaction ID wraparound. autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor influence vacuum triggers; the corresponding analyze settings trigger statistics refreshes. autovacuum_freeze_max_age is a safety limit, not just a performance knob.

Try it safely

In a disposable table, update many rows, compare table statistics before/after VACUUM and ANALYZE, and explain why dead-tuple estimates and on-disk file size are not interchangeable. Run each phase as separate commands (not one giant pasted block), pause 1-2 seconds after UPDATE before reading stats, and treat statistics as estimates with asynchronous flush timing. Run VACUUM (FREEZE) on a small lab table and compare with VACUUM FULL behavior in terms of lock and rewrite expectations.

CREATE TABLE IF NOT EXISTS public.dba1_vacuum_lab (id integer PRIMARY KEY, payload text);
TRUNCATE public.dba1_vacuum_lab;
INSERT INTO public.dba1_vacuum_lab
SELECT g, repeat('x', 100) FROM generate_series(1, 500) AS g;
UPDATE public.dba1_vacuum_lab SET payload = repeat('y', 100) WHERE id <= 250;
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname = 'public' AND relname = 'dba1_vacuum_lab';
SELECT pg_total_relation_size('public.dba1_vacuum_lab') AS bytes_before;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_freeze_max_age;
VACUUM (ANALYZE) public.dba1_vacuum_lab;
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname = 'public' AND relname = 'dba1_vacuum_lab';
VACUUM (FREEZE) public.dba1_vacuum_lab;
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname = 'public' AND relname = 'dba1_vacuum_lab';
SELECT pg_total_relation_size('public.dba1_vacuum_lab') AS bytes_after;

What to notice: Row counts are estimates and can lag because statistics flush asynchronously. Run steps separately, allow a short pause after UPDATE, then inspect the same schema-qualified table before and after VACUUM/ANALYZE/FREEZE. Compare size signals before/after, and treat VACUUM FULL as a separate rewrite operation with stronger locking that should be tested only in a controlled optional lab.

Collect this evidence

  • Capture before/after n_dead_tup for a table you modified.
  • Capture bytes_before/bytes_after. After ordinary VACUUM the size can stay the same or grow slightly (in our 17.11 run: 180224 to 188416 bytes). Explain why, using pg_relation_size('public.dba1_vacuum_lab', 'fsm') and pg_relation_size('public.dba1_vacuum_lab', 'vm').
  • Record autovacuum threshold, scale factor and freeze max age values.
  • Write one sentence distinguishing space reuse from file shrinkage.
  • Note that n_live_tup/n_dead_tup are estimates and may appear out of order when counters flush asynchronously.

Decision rule

If you cannot show dead-tuple evidence and trigger settings together, avoid claiming autovacuum is misconfigured.

Common failure in this domain (and the fix)

Failure pattern: Dead tuples drop after VACUUM, but file size does not shrink, so VACUUM is assumed broken.

Fix approach: Separate space reuse from file shrinkage; compare n_dead_tup with size signals (including FSM/VM) before claiming vacuum failure.

Recall checklist (name + one-line meaning)

  • VACUUM vs ANALYZE goals
  • VACUUM (ANALYZE) and VACUUM (FREEZE)
  • VACUUM FULL rewrite trade-off
  • autovacuum threshold/scale factor triggers
  • autovacuum_freeze_max_age safety role

Explain before revealing

Dead-tuple estimate drops after VACUUM but the table file stays the same size. Did VACUUM fail?

Not necessarily. Ordinary VACUUM makes space reusable within the table; reclaiming file space is a different operation. Statistics are estimates, so use multiple signals.

A query has a poor plan because row estimates are stale. Would ANALYZE or VACUUM FULL be the first relevant mechanism?

ANALYZE refreshes planner statistics. VACUUM FULL rewrites a table and is not the first tool for stale estimates.

When is VACUUM (FREEZE) the relevant command to discuss in exam prep?

When focusing on transaction ID age and wraparound protection on old tuples. It advances tuple freezing state; it is not a generic substitute for every bloat symptom.

Why should VACUUM FULL be treated as an explicit operational decision?

It rewrites table storage and usually requires stronger locking, affecting availability. Use evidence and maintenance windows rather than applying it as a default reaction.

Read the source

You are ready for this domain when: You can justify VACUUM/ANALYZE choice for a symptom and explain autovacuum trigger settings versus freeze safety settings.

Use our matching resources

Domain 8 of 8

Monitoring and logging

Know the scope

Reviewed 0 of 13 topics. Checkmarks are stored only in this browser; they are not an exam-readiness score.

Understand the why

pg_stat_activity shows current session state; cumulative views show counters since their last reset. Neither is a historical trace of every query.

pg_cancel_backend requests cancellation of a running query; pg_terminate_backend ends the session. Confirm identity, impact and permissions before either action.

pg_stat_statements aggregates query statistics rather than listing live SQL. log_min_duration_statement, log_lock_waits and log_autovacuum_min_duration capture different events. log_statement selects statement classes; log_duration reports durations; log_line_prefix identifies the session. Set log_directory and rotation so logs remain usable without uncontrolled storage or sensitive SQL exposure.

Try it safely

Start a disposable blocking transaction. Identify the blocked session, collect its wait event and blocker PID, then find the corresponding log entry with a useful log_line_prefix. Avoid terminating a session merely because it is waiting.

SELECT pid, datname, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity;
SHOW shared_preload_libraries;
-- If pg_stat_statements is missing from preload, add it (do not overwrite existing entries):
-- ALTER SYSTEM SET shared_preload_libraries = '<existing_entries>,pg_stat_statements';
-- If empty: ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- Restart PostgreSQL, then:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
SHOW log_min_messages;
SHOW log_error_verbosity;
SHOW log_min_duration_statement;
SHOW log_filename;
SHOW log_rotation_age;
SHOW log_rotation_size;
SHOW log_line_prefix;

What to notice: Live wait evidence is not historical evidence. If blockers live in another database, cross-database visibility matters. pg_stat_statements needs shared_preload_libraries + restart before workload rows are queryable; if preload is missing, keep the exact error as lab evidence and then fix configuration safely without dropping existing preload entries.

Collect this evidence

  • Capture one pg_stat_activity row with state, wait_event_type and blocker PID context.
  • Record the active log_min_duration_statement and log_line_prefix settings.
  • Keep one example where live activity and historical logs answer different questions.

Decision rule

If your evidence lacks either live session context or logging context, do not publish root-cause claims yet.

Common failure in this domain (and the fix)

Failure pattern: Incident happened earlier, but only live views are checked now.

Fix approach: Use both live state and retained logs; define logging + statement visibility ahead of incidents so past slow queries remain explainable.

Recall checklist (name + one-line meaning)

  • pg_stat_activity vs cumulative statistics views
  • pg_cancel_backend vs pg_terminate_backend
  • pg_stat_checkpointer and pg_stat_bgwriter roles
  • log_min_messages/log_error_verbosity/log_min_duration_statement
  • log_filename/log_rotation_age/log_rotation_size/log_line_prefix

Explain before revealing

A query was slow yesterday but is not running now. Will pg_stat_activity alone explain it?

No. It describes current activity. Consult retained logs and aggregated workload data if they were enabled, and note the limits of each source.

Would you terminate a blocker immediately on seeing pg_blocking_pids return a PID?

No. Identify the session, transaction and application owner, collect evidence, and assess rollback and availability impact first.

Why can logging configuration alone still leave you blind during an incident?

If pg_stat_statements is not installed/configured or retention/rotation is too short, you may miss workload context even with basic logs enabled. Monitoring and logging coverage must be designed together.

Which settings directly control slow-query capture and log file rollover?

Slow-query capture is controlled by log_min_duration_statement (plus related statement logging choices). File rollover is controlled by log_filename with log_rotation_age and log_rotation_size, under the chosen log destination and directory.

Read the source

You are ready for this domain when: You can prove what happened using both live views and logs, and explain exactly which logging settings were required for that evidence.

Use our matching resources

  • Observability runbooks — Convert metrics and logs into actionable diagnosis steps.
  • Triage — Practice evidence-first incident framing on real PostgreSQL errors.

Mixed revision set (cross-domain recall)

Use this after finishing all domains: answer each prompt in 30-60 seconds without notes, then verify in the linked manual sections.

  1. 1. What does pg_isready prove, and what does it not prove for app readiness?
  2. 2. Why can search_path change query behavior without changing SQL text?
  3. 3. Which two privilege layers must pass before SELECT succeeds on a schema-qualified table?
  4. 4. In one sentence each, contrast pg_dump and pg_basebackup recovery goals.
  5. 5. Where is the durability boundary: dirty data page write or WAL-backed commit?
  6. 6. What evidence in pg_locks distinguishes waiter from blocker?
  7. 7. Why can n_dead_tup drop while file size does not shrink?
  8. 8. When would you use log_min_duration_statement versus log_statement?

What comes after DBA-1?

This junior-DBA exam scope stops before deep engine internals, advanced replication and code contribution. Passing practice checks here does not certify you or prove production cluster ownership. If you want to explore broader progression tracks, use the learning pathways hub.

Explore learning pathways