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.

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

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