Detailed Objectives（Ver.3.0）

Detailed Objectives

Version 3.0

OSS-DB is a qualification certification for engineers involved in OSS-DB system. OSS-DB system is commonly used and the number of companies relying on the system is increasing each year.

Objectives for "Version 2.0" adopt "PostgreSQL 10 to 11".
Objectives for "Version 3.0" adopt "PostgreSQL 12 to 14".

You can take Version 2.0 exam "until January 31, 2024" and Version 3.0 exam "from August 1, 2023". Therefore, until January 31, 2024, you can choose to take the exam of either version.
In addition, regardless of the version, if you pass the Silver and Gold exams, you will be certified as OSS-DB Gold.

Silver：Detailed Objectives (Version 3.0)

Silver：Detailed Objectives (Version 2.0)

Gold：Detailed Objectives (Version 3.0)

Gold：Detailed Objectives (Version 2.0)

OSS-DB Silver: Detailed Objectives Ver.3.0

S1 General Knowledge(16%)	S1.1 General features of OSS-DB [Weight: 4] - Description: Candidates should be able to understand PostgreSQL's features, license, and role of the OSS community. - Key Knowledge Areas: General features of PostgreSQL OSS-DB license and OSS general license OSS-DB community, its activities and the way of participation to the community Major version / Minor version Release Cycle / Support Policy / Bug Report
S1.2 General knowledge of relational databases [Weight: 4] - Description: Candidates should be able to understand basic concepts and general knowledge of relational databases. - Key Knowledge Areas: Basic concepts of the relational data model Role of database management system General knowledge about SQL Sorts of SQL (DDL / DML / DCL) Database design and normalization
S2 Server Administration(52%)
S2.1 Installation [Weight: 2] - Description: Candidates should be able to understand how to install PostgreSQL, create a database cluster, etc. - Key Knowledge Areas: Usage of initdb command Concept and structure of database cluster Template database - Important files, terms, and utilities: initdb PGDATA template0 template1
S2.2 Usage of server management tools [Weight: 5] - Description: Candidates should be able to use management tools that can be invoked from the OS command prompt. - Key Knowledge Areas: Usage of database management commands - Important files, terms, and utilities: pg_ctl createuser dropuser createdb dropdb psql pg_config pg_controldata pg_isready pg_resetwal meta-command
S2.3 Configuration files [Weight: 5] - Description: Candidates should be able to use configuration files and basic configuration parameters. - Key Knowledge Areas: Following four items related to postgresql.conf 　　-Setting Parameters 　　-Connections and authentication 　　-Client connection defaults 　　-Error reporting and logging How to set pg_hba.conf Usage of SET / SHOW - Important files, terms, and utilities: postgresql.conf pg_hba.conf pg_ctl reload/restart pg_settings
S2.4 Backup and restore [Weight: 7] - Description: Candidates should be able to understand how to backup PostgreSQL. - Key Knowledge Areas: Usage of various backup commands File system level backup and restore Concepts and procedures of Point-in-Time Recovery (PITR) Transaction log (WAL) and WAL archive Non-exclusive low level backup Following four items related to postgresql.conf - Archive Recovery Usage of COPY statement (SQL) and \copy command (psql) - Important files, terms, and utilities: pg_dump pg_dumpall pg_restore psql pg_basebackup pg_start_backup() pg_stop_backup() backup_label tablespace_map PITR postgresql.conf recovery.signal COPY \copy
S2.5 Basic server administration [Weight: 7] - Description: Candidates should be able to know basic commands for database administration. - Key Knowledge Areas: Starting and stopping PostgreSQL Concept of database role / user Adding, deleting and modifying database role / user Purpose and usage of VACUUM and ANALYZE Autovacuum concepts and behavior System information functions Information schema and system catalogs Table privileges (GRANT / REVOKE) - Important files, terms, and utilities: pg_ctl start / stop CREATE/ALTER/DROP ROLE/USER VACUUM ANALYZE vacuumdb autovacuum current_user version information_schema GRANT REVOKE
S3 SQL and Development(32%)	S3.1 SQL commands [Weight: 13] - Description: Candidates should know basic SQL statements and database components. Candidates should know basic functions, types etc. of the replication feature. - Key Knowledge Areas: SELECT statement INSERT statement UPDATE statement DELETE statement Datatypes Table definition Index View Materialized view Trigger Sequence Schema Table space Partition Function definition / Procedure definition PL / pgSQL Streaming Replication Logical Replication - Important files, terms, and utilities: SELECT/INSERT/UPDATE/DELETE FROM JOIN WHERE INTO VALUES SET LIMIT OFFSET ORDER BY DISTINCT GROUP BY HAVING EXISTS IN NOT INTEGER SMALLINT BIGINT NUMERIC DECIMAL REAL DOUBLE PRECISION CHAR CHARACTER VARCHAR CHARACTER VARYING TEXT BOOLEAN DATE TIME TIMESTAMP INTERVAL GENERATED (AS IDENTITY) SERIAL BIGSERIAL BYTEA JSON JSONB NULL CREATE/ALTER/DROP TABLE CREATE PUBLICATION/SUBSCRIPTION PRIMARY KEY FOREIGN KEY REFERENCES UNIQUE NOT NULL CHECK DEFAULT JSON PATH CREATE/ALTER/DROP INDEX/VIEW/MATERIALIZED VIEW/TRIGGER/SCHEMA/SEQUENCE/TABLESPACE/FUNCTION/PROCEDURE CREATE TABLE PARTITION BY/OF ALTER TABLE ATTACH/DETACH PARTITION
S3.2 Functions and operators [Weight: 2] - Description: Candidates should be able to know functions and operators that are standard in the database. - Key Knowledge Areas: Aggregate functions Mathematical functions Operators String functions String operators Date/Time functions - Important files, terms, and utilities: count sum avg max min char(character)_length lower upper substring replace trim \|\| ~ LIKE SIMILAR TO age now current_date current_timestamp / statement_timestamp / clock_timestamp current_time extract to_char
S3.3 Transactions [Weight: 1] - Description: Candidates should be able to know transaction features. - Key Knowledge Areas: Transaction syntax Transaction isolation level (read committed, repeatable read, serializable) LOCK statement Row-level locks and table locks Deadlock - Important files, terms, and utilities: BEGIN COMMIT ROLLBACK SAVEPOINT SET TRANSACTION LOCK TABLE SELECT FOR UPDATE / SHARE

S1
General Knowledge(16%)

S1.1 General features of OSS-DB [Weight: 4]

- Description:

Candidates should be able to understand PostgreSQL's features, license, and role of the OSS community.

- Key Knowledge Areas:

General features of PostgreSQL

OSS-DB license and OSS general license

OSS-DB community, its activities and the way of participation to the community

Major version / Minor version

Release Cycle / Support Policy / Bug Report

S1.2 General knowledge of relational databases [Weight: 4]

- Description:

Candidates should be able to understand basic concepts and general knowledge of relational databases.

- Key Knowledge Areas:

Basic concepts of the relational data model

Role of database management system

General knowledge about SQL

Sorts of SQL (DDL / DML / DCL)

Database design and normalization

S2
Server Administration(52%)

S2.1 Installation [Weight: 2]

- Description:

Candidates should be able to understand how to install PostgreSQL, create a database cluster, etc.

- Key Knowledge Areas:

Usage of initdb command

Concept and structure of database cluster

Template database

- Important files, terms, and utilities:

initdb

PGDATA

template0

template1

S2.2 Usage of server management tools [Weight: 5]

- Description:

Candidates should be able to use management tools that can be invoked from the OS command prompt.

- Key Knowledge Areas:

Usage of database management commands

- Important files, terms, and utilities:

pg_ctl

createuser

dropuser

createdb

dropdb

psql
pg_config
pg_controldata
pg_isready
pg_resetwal

meta-command

S2.3 Configuration files [Weight: 5]

- Description:

Candidates should be able to use configuration files and basic configuration parameters.

- Key Knowledge Areas:

Following four items related to postgresql.conf

　　-Setting Parameters

　　-Connections and authentication

　　-Client connection defaults

　　-Error reporting and logging

How to set pg_hba.conf

Usage of SET / SHOW

- Important files, terms, and utilities:

postgresql.conf

pg_hba.conf

pg_ctl reload/restart

pg_settings

S2.4 Backup and restore [Weight: 7]

- Description:

Candidates should be able to understand how to backup PostgreSQL.

- Key Knowledge Areas:

Usage of various backup commands

File system level backup and restore

Concepts and procedures of Point-in-Time Recovery (PITR)

Transaction log (WAL) and WAL archive

Non-exclusive low level backup
Following four items related to postgresql.conf

- Archive Recovery

Usage of COPY statement (SQL) and \copy command (psql)

- Important files, terms, and utilities:

pg_dump

pg_dumpall

pg_restore

psql

pg_basebackup

pg_start_backup()
pg_stop_backup()
backup_label
tablespace_map

PITR

postgresql.conf
recovery.signal
COPY
\copy

S2.5 Basic server administration [Weight: 7]

- Description:

Candidates should be able to know basic commands for database administration.

- Key Knowledge Areas:

Starting and stopping PostgreSQL

Concept of database role / user

Adding, deleting and modifying database role / user

Purpose and usage of VACUUM and ANALYZE

Autovacuum concepts and behavior

System information functions

Information schema and system catalogs

Table privileges (GRANT / REVOKE)

- Important files, terms, and utilities:

pg_ctl start / stop

CREATE/ALTER/DROP ROLE/USER

VACUUM

ANALYZE

vacuumdb

autovacuum

current_user

version

information_schema

GRANT

REVOKE

S3
SQL and Development(32%)

S3.1 SQL commands [Weight: 13]

- Description:

Candidates should know basic SQL statements and database components.

Candidates should know basic functions, types etc. of the replication feature.

- Key Knowledge Areas:

SELECT statement

INSERT statement

UPDATE statement

DELETE statement

Datatypes

Table definition

Index

View

Materialized view

Trigger

Sequence

Schema

Table space

Partition

Function definition / Procedure definition

PL / pgSQL

Streaming Replication

Logical Replication

- Important files, terms, and utilities:

SELECT/INSERT/UPDATE/DELETE

FROM

JOIN

WHERE

INTO

VALUES

SET

LIMIT

OFFSET

ORDER BY

DISTINCT

GROUP BY

HAVING

EXISTS

NOT

INTEGER

SMALLINT

BIGINT

NUMERIC

DECIMAL

REAL

DOUBLE PRECISION

CHAR

CHARACTER

VARCHAR

CHARACTER VARYING

TEXT

BOOLEAN

DATE

TIME

TIMESTAMP

INTERVAL

GENERATED (AS IDENTITY)

SERIAL

BIGSERIAL

BYTEA

JSON

JSONB

NULL

CREATE/ALTER/DROP TABLE

CREATE PUBLICATION/SUBSCRIPTION

PRIMARY KEY

FOREIGN KEY

REFERENCES

UNIQUE

NOT NULL

CHECK

DEFAULT

JSON PATH

CREATE/ALTER/DROP INDEX/VIEW/MATERIALIZED

VIEW/TRIGGER/SCHEMA/SEQUENCE/TABLESPACE/FUNCTION/PROCEDURE

CREATE TABLE PARTITION BY/OF

ALTER TABLE ATTACH/DETACH PARTITION

S3.2 Functions and operators [Weight: 2]

- Description:
Candidates should be able to know functions and operators that are standard in the database.

- Key Knowledge Areas:
Aggregate functions
Mathematical functions
Operators
String functions
String operators
Date/Time functions

- Important files, terms, and utilities:
count
sum
avg
max
min
char(character)_length
lower
upper
substring
replace
trim
||
~
LIKE
SIMILAR TO
age
now
current_date
current_timestamp / statement_timestamp / clock_timestamp
current_time
extract
to_char

S3.3 Transactions [Weight: 1]

- Description:

Candidates should be able to know transaction features.

- Key Knowledge Areas:

Transaction syntax

Transaction isolation level (read committed, repeatable read, serializable)

LOCK statement

Row-level locks and table locks

Deadlock

- Important files, terms, and utilities:

BEGIN

COMMIT

ROLLBACK

SAVEPOINT

SET TRANSACTION

LOCK TABLE

SELECT FOR UPDATE / SHARE

OSS-DB Gold: Detailed Objectives Ver.3.0

G1 Advanced Server Administration(30%)	G1.1 Setting up database server [Weight: 2] - Description: Candidates should understand database security. - Key Knowledge Areas: Security 　　-Encryption of communication route (SSL) 　　-Data encryption 　　-Client authentication 　　-Audit log Parameter setting for each user database - Important files, terms, and utilities: Checksum pg_stat_tmp pg_tblspc pg_wal ssl pg_stat_ssl pgcrypto SCRAM-SHA-256 ALTER ROLE ALTER DATABASE initdb --data-checksums (-k) log_statement track_functions track_activities
G1.2 Commands for database administration [Weight: 4] - Description: Candidates should understand advanced database server administration. - Key Knowledge Areas: Exclusive/Non-Exclusive Backup, PITR VACUUM, ANALYZE, REINDEX Autovacuum Checkpoint Server log management Disk space monitoring Difference between automatic VACUUM and manual VACUUM / ANALYZE Default role - Important terms, commands, parameters, etc.: ALTER SYSTEM ANALYZE CLUSTER REINDEX VACUUM CHECKPOINT PITR WAL pg_dump pg_dumpall pg_basebackup pg_start_backup() pg_stop_backup() postgresql.conf vacuumdb pgstattuple pg_cancel_backend() pg_terminate_backend() pg_isready log_connections log_disconnections log_duration postgresql.auto.conf pg_reload_conf() max_parallel_workers / max_parallel_maintenance_workers pg_monitor
G1.3 Database architecture [Weight: 2] - Description: Candidates should understand the internal architecture and the physical file structure of the database. - Key Knowledge Areas: Architecture of database cluster Database processes Physical structure of data and data files - Important terms, commands, parameters, etc.: autovacuum TOAST FILLFACTOR Archive log postmaster process Backend process (postgres process) Background process Cancelling SQL execution Effects on server processes by signal (TERM / INT / HUP) postgres̲ fdw, file̲ fdw CREATE SERVER/USER MAPPING/FOREIGN TABLE
G1.4 Operation of hot-standby database [Weight: 1] - Description: Candidates should understand the settings and the steps of configuring replication environment. This includes replication mechanisms (processes and flows) and monitoring status etc. - Key Knowledge Areas: Overview of streaming replication feature and logical replication feature Synchronous and asynchronous replication Settings of postgresql.conf Definition of publications and subscriptions - Important terms, commands, parameters, etc.: wal_level max_wal_senders synchronous_standby_names synchronous_commit max_logical_replication_workers CREATE/ALTER/DROP PUBLICATION/SUBSCRIPTION pg_stat_replication pg_stat_wal_receiver recovery_min_apply_delay recovery.signal Inquiry conflict on standby hot_standby_feedback max_standby_streaming_delay pg_wal_replay_pause () pg_wal_replay_resume () walsender process walreceiver process pg_receivewal Transaction log (WAL) Processing that is/isn’t propagated to the standby Queries that can be executed on standby hot_standby Processing that is/isn’t propagated to logical replication subscribers
G2 Monitoring Performance(30%)
G2.1 Access statistics [Weight: 3] - Description: Candidates should understand how to use and collect the statistics, that indicates the usage of the database. - Key Knowledge Areas: pg_locks Database level activity statistics - Important terms, commands, parameters, etc.: pg_stat_activity, pg_stat_database Row-level statistics, ex. pg_stat_all_tables Block level statistics, ex. pg_statio_all_tables pg_stat_archiver pg_stat_bgwriter Wait event (pg_stat_activity, wait_event_type, pg_stat_activity.wait_event)
G2.2 Statistics for tables and columns [Weight: 2] - Description: Candidates should understand the statistics for tables and columns used by the planner. - Key Knowledge Areas: pg_class pg_stats Real file and path of table index Statistics and parameters used during execution planning Extended statistics - Important terms, commands, parameters, etc.: pg_statistic pg_stats null_frac n_distinct most_common_freqs histogram_bounds correlation default_statistics_target effective_cache_size CREATE STATISTICS pg_statistic_ext
G2.3 Execution plans [Weight: 3] - Description: Candidates should be able to read the execution plan output by EXPLAIN and perform tuning. - Key Knowledge Areas: EXPLAIN / EXPLAIN ANALYZE output Planning type Tuning from EXPLAIN Type of join (Nested Loop, Hash, Merge) and performance characteristics Execution plan corresponding to SQL syntax (JOIN / GROUP BY / ORDER BY / LIMIT) Execution plan corresponding to SQL with aggregate function (sum / count) Execution plan corresponding to SQL for partition Execution plan corresponding to parallel query Execution plan corresponding to SQL of window function (row_number / rank etc.) - Important terms, commands, parameters, etc.: EXPLAIN / EXPLAIN ANALYZE max̲ worker̲ processes / max̲ parallel̲ workers̲ per̲ gather
G2.4 Other performance monitoring [Weight: 1] - Description: Candidates should know other techniques for performance monitoring. - Key Knowledge Areas: Detecting slow queries Analysis using contrib tools Performance degradation factors (resource exhaustion, lock contention) - Important terms, commands, parameters, etc.: shared_preload_libraries auto_explain auto_explain. * parameter log_min_duration_statement pg_stat_statements log_autovacuum_min_duration log_lock_waits log_checkpoints log_temp_files
G3 Performance Tuning(20%)	G3.1 Parameters related to performance [Weight: 4] - Description: Candidates should understand database configuration parameters, especially those that affect performance and can be useful for performance tuning. - Key Knowledge Areas: RESOURCE USAGE WRITE AHEAD LOG QUERY TUNING RUNTIME STATISTICS LOCK MANAGEMENT Light and heavy locks - Important terms, commands, parameters, etc.: shared_buffers huge_pages effective_cache_size work_mem maintenance_work_mem autovacuum_work_mem wal_level full_page_writes wal_compression fsync synchronous_commit checkpoint_timeout checkpoint_completion_target deadlock_timeout max_wal_size min_wal_size wal_keep_size
G3.2 Tuning database and queries [Weight: 2] - Description: Candidates should understand database and SQL tuning. - Key Knowledge Areas: Tuning parameters Tuning the execution plan Tuning SQL Tuning the table configuration Distributing disk I/O How to reflect parameters (action required for parameter activation) How indexes affect SQL performance Index Only Scan and Visibility Map - Important terms, commands, parameters, etc.: Index Only Scan Index on Function/Index on Expression/Partial Index Partitioning enable̲_* parameter work_mem hash_mem_multiplier
G4 Troubleshooting (20%)	G4.1 Troubleshooting common failures [Weight: 3] - Description: Candidates should be able to take appropriate actions in the event of a failure such as SQL execution timeout in the database, server down, malfunction, data loss, OS resource exhaustion, etc., by identifying the cause from the error messages. - Key Knowledge Areas: Dealing with server down, malfunction and data loss OS resource exhaustion OS parameters Server process status (idle, idle in transaction, active) Effects on server process by signal (TERM / INT / HUP) Server process crashes (segmentation faults, etc.) and scope of impact - Important terms, commands, parameters, etc.: statement_timeout lock_timeout idle_in_transaction_session_timeout Inquiry conflict on standby hot_standby_feedback vacuum_defer_cleanup_age max_standby_archive_delay max_standby_streaming_delay fsync synchronous_commit restart_after_crash pg_cancel_backend() pg_terminate_backend() pg_ctl kill max_locks_per_transaction max_files_per_process
G4.2 Recovering damaged cluster [Severity: 2] - Description: Candidates should be able to take appropriate actions when the data file or transaction log file is damaged, by identifying the cause from the error messages. - Key Knowledge Areas: Transaction log recovery System table index recovery Developer options Real file and path of table index Relfilenode and OID Index corruption and REINDEX recovery Checksum corruption detection and recovery Transaction ID rounding error - Important terms, commands, parameters, etc.: PITR pg_resetwal ignore_system_indexes ignore_checksum_failure Commit log (pg_xact) Single user mode VACUUM FREEZE
G4.3 Failure and Recovery of Replication [Weight: 1] - Description: Candidates should be able to take appropriate action when the primary or standby PostgreSQL stops or fails in a replication environment. - Key Knowledge Areas: Streaming replication and logical replication Error messages in log files Processing that is/isn’t propagated to the standby How to recover primary PostgreSQL at stop/failure How to recover standby PostgreSQL at stop/failure Processing that is/isn’t propagated to logical replication subscribers Conflicts at Logical Replication Subscribers - Important terms, commands, parameters, etc.: pg_ctl promote pg_receivewal pg_rewind

G1
Advanced Server Administration(30%)

G1.1 Setting up database server [Weight: 2]

- Description:

Candidates should understand database security.

- Key Knowledge Areas:

Security

　　-Encryption of communication route (SSL)

　　-Data encryption

　　-Client authentication

　　-Audit log

Parameter setting for each user database

- Important files, terms, and utilities:

Checksum

pg_stat_tmp

pg_tblspc

pg_wal

ssl

pg_stat_ssl

pgcrypto

SCRAM-SHA-256

ALTER ROLE

ALTER DATABASE

initdb --data-checksums (-k)

log_statement

track_functions

track_activities

G1.2 Commands for database administration [Weight: 4]

- Description:

Candidates should understand advanced database server administration.

- Key Knowledge Areas:

Exclusive/Non-Exclusive Backup, PITR

VACUUM, ANALYZE, REINDEX

Autovacuum

Checkpoint

Server log management

Disk space monitoring

Difference between automatic VACUUM and manual VACUUM / ANALYZE

Default role

- Important terms, commands, parameters, etc.:

ALTER SYSTEM

ANALYZE

CLUSTER

REINDEX

VACUUM

CHECKPOINT

PITR

WAL

pg_dump

pg_dumpall

pg_basebackup

pg_start_backup()

pg_stop_backup()

postgresql.conf

vacuumdb

pgstattuple

pg_cancel_backend()

pg_terminate_backend()

pg_isready

log_connections

log_disconnections

log_duration

postgresql.auto.conf
pg_reload_conf()
max_parallel_workers / max_parallel_maintenance_workers
pg_monitor

G1.3 Database architecture [Weight: 2]

- Description:

Candidates should understand the internal architecture and the physical file structure of the database.

- Key Knowledge Areas:

Architecture of database cluster

Database processes

Physical structure of data and data files

- Important terms, commands, parameters, etc.:

autovacuum

TOAST

FILLFACTOR

Archive log

postmaster process

Backend process (postgres process)

Background process

Cancelling SQL execution

Effects on server processes by signal (TERM / INT / HUP)

postgres̲ fdw, file̲ fdw

CREATE SERVER/USER MAPPING/FOREIGN TABLE

G1.4 Operation of hot-standby database [Weight: 1]

- Description:

Candidates should understand the settings and the steps of configuring replication environment. This includes replication mechanisms (processes and flows) and monitoring status etc.

- Key Knowledge Areas:

Overview of streaming replication feature and logical replication feature

Synchronous and asynchronous replication

Settings of postgresql.conf

Definition of publications and subscriptions

- Important terms, commands, parameters, etc.:

wal_level

max_wal_senders

synchronous_standby_names

synchronous_commit

max_logical_replication_workers

CREATE/ALTER/DROP PUBLICATION/SUBSCRIPTION

pg_stat_replication

pg_stat_wal_receiver

recovery_min_apply_delay

recovery.signal

Inquiry conflict on standby

hot_standby_feedback

max_standby_streaming_delay

pg_wal_replay_pause ()

pg_wal_replay_resume ()

walsender process

walreceiver process

pg_receivewal

Transaction log (WAL)

Processing that is/isn’t propagated to the standby

Queries that can be executed on standby

hot_standby

Processing that is/isn’t propagated to logical replication subscribers

G2
Monitoring Performance(30%)

G2.1 Access statistics [Weight: 3]

- Description:

Candidates should understand how to use and collect the statistics, that indicates the usage of the database.

- Key Knowledge Areas:

pg_locks

Database level activity statistics

- Important terms, commands, parameters, etc.:

pg_stat_activity, pg_stat_database

Row-level statistics, ex. pg_stat_all_tables

Block level statistics, ex. pg_statio_all_tables

pg_stat_archiver

pg_stat_bgwriter

Wait event (pg_stat_activity, wait_event_type, pg_stat_activity.wait_event)

G2.2 Statistics for tables and columns [Weight: 2]

- Description:

Candidates should understand the statistics for tables and columns used by the planner.

- Key Knowledge Areas:

pg_class

pg_stats

Real file and path of table index

Statistics and parameters used during execution planning

Extended statistics

- Important terms, commands, parameters, etc.:

pg_statistic

pg_stats

null_frac

n_distinct

most_common_freqs

histogram_bounds

correlation

default_statistics_target

effective_cache_size

CREATE STATISTICS

pg_statistic_ext

G2.3 Execution plans [Weight: 3]

- Description:

Candidates should be able to read the execution plan output by EXPLAIN and perform tuning.

- Key Knowledge Areas:

EXPLAIN / EXPLAIN ANALYZE output

Planning type

Tuning from EXPLAIN

Type of join (Nested Loop, Hash, Merge) and performance characteristics

Execution plan corresponding to SQL syntax (JOIN / GROUP BY / ORDER BY / LIMIT)

Execution plan corresponding to SQL with aggregate function (sum / count)

Execution plan corresponding to SQL for partition

Execution plan corresponding to parallel query

Execution plan corresponding to SQL of window function (row_number / rank etc.)

- Important terms, commands, parameters, etc.:

EXPLAIN / EXPLAIN ANALYZE

max̲ worker̲ processes / max̲ parallel̲ workers̲ per̲ gather

G2.4 Other performance monitoring [Weight: 1]

- Description:

Candidates should know other techniques for performance monitoring.

- Key Knowledge Areas:

Detecting slow queries

Analysis using contrib tools

Performance degradation factors (resource exhaustion, lock contention)

- Important terms, commands, parameters, etc.:

shared_preload_libraries

auto_explain

auto_explain. * parameter

log_min_duration_statement

pg_stat_statements

log_autovacuum_min_duration

log_lock_waits

log_checkpoints

log_temp_files

G3
Performance Tuning(20%)

G3.1 Parameters related to performance [Weight: 4]

- Description:

Candidates should understand database configuration parameters, especially those that affect performance and can be useful for performance tuning.

- Key Knowledge Areas:

RESOURCE USAGE

WRITE AHEAD LOG

QUERY TUNING

RUNTIME STATISTICS

LOCK MANAGEMENT

Light and heavy locks

- Important terms, commands, parameters, etc.:

shared_buffers

huge_pages

effective_cache_size

work_mem

maintenance_work_mem

autovacuum_work_mem

wal_level

full_page_writes
wal_compression
fsync
synchronous_commit
checkpoint_timeout
checkpoint_completion_target
deadlock_timeout
max_wal_size
min_wal_size
wal_keep_size

G3.2 Tuning database and queries [Weight: 2]

- Description:

Candidates should understand database and SQL tuning.

- Key Knowledge Areas:

Tuning parameters

Tuning the execution plan

Tuning SQL

Tuning the table configuration

Distributing disk I/O

How to reflect parameters (action required for parameter activation)

How indexes affect SQL performance

Index Only Scan and Visibility Map

- Important terms, commands, parameters, etc.:

Index Only Scan

Index on Function/Index on Expression/Partial Index

Partitioning

enable̲_* parameter

work_mem
hash_mem_multiplier

Troubleshooting
(20%)

G4.1 Troubleshooting common failures [Weight: 3]

- Description:

Candidates should be able to take appropriate actions in the event of a failure such as SQL execution timeout in the database, server down, malfunction, data loss, OS resource exhaustion, etc., by identifying the cause from the error messages.

- Key Knowledge Areas:

Dealing with server down, malfunction and data loss

OS resource exhaustion

OS parameters

Server process status (idle, idle in transaction, active)

Effects on server process by signal (TERM / INT / HUP)

Server process crashes (segmentation faults, etc.) and scope of impact

- Important terms, commands, parameters, etc.:

statement_timeout

lock_timeout

idle_in_transaction_session_timeout

Inquiry conflict on standby

hot_standby_feedback

vacuum_defer_cleanup_age

max_standby_archive_delay

max_standby_streaming_delay

fsync

synchronous_commit

restart_after_crash

pg_cancel_backend()

pg_terminate_backend()

pg_ctl kill

max_locks_per_transaction

max_files_per_process

G4.2 Recovering damaged cluster [Severity: 2]

- Description:

Candidates should be able to take appropriate actions when the data file or transaction log file is damaged, by identifying the cause from the error messages.

- Key Knowledge Areas:

Transaction log recovery

System table index recovery

Developer options

Real file and path of table index

Relfilenode and OID

Index corruption and REINDEX recovery

Checksum corruption detection and recovery

Transaction ID rounding error

- Important terms, commands, parameters, etc.:

PITR

pg_resetwal