OSS-DB Silver — knowledge map

The 55 core concepts of OSS-DB Silver and how they connect. Click a node in the map above to explore related terms and prerequisites; the list below indexes every concept with its definition and links to its prerequisites and related concepts.

Concepts (55)

• PostgreSQL

  An open-source object-relational database management system (ORDBMS) led by the PostgreSQL Global Development Group and distributed under the permissive PostgreSQL License (similar to BSD/MIT). The subject product of the OSS-DB Silver/Gold exams.

  Related: PostgreSQL License

• SELECT statement (WHERE/ORDER BY/GROUP BY/HAVING/DISTINCT/LIMIT/OFFSET)

  The DML statement that retrieves data from tables. WHERE filters rows, ORDER BY sorts them, GROUP BY groups them, HAVING filters groups, DISTINCT/SELECT DISTINCT removes duplicates, and LIMIT/OFFSET control the number of rows returned and the starting offset.

• Write DML (INSERT/UPDATE/DELETE)

  DML statements that add, modify, and remove rows. INSERT adds new rows; UPDATE, typically written as UPDATE ... WHERE, changes column values of existing rows matching a condition; DELETE removes rows matching a condition. Inside triggers, the row before and after the change can be referenced as OLD/NEW.

  Prerequisites: SELECT statement (WHERE/ORDER BY/GROUP BY/HAVING/DISTINCT/LIMIT/OFFSET)

  Related: Trigger

• Index (CREATE INDEX)

  An auxiliary data structure created with CREATE INDEX to speed up lookups. Built on a column or combination of columns, it accelerates filtering in WHERE clauses and JOIN conditions. The default index type is B-tree, and adding indexes increases the write cost of INSERT/UPDATE operations, since the indexes must also be updated.

  Prerequisites: Write DML (INSERT/UPDATE/DELETE)、JOIN (INNER/LEFT/RIGHT/FULL/CROSS)、SELECT statement (WHERE/ORDER BY/GROUP BY/HAVING/DISTINCT/LIMIT/OFFSET)

• Locking and concurrency control

  The mechanism that preserves consistency when multiple transactions access the same data concurrently. The LOCK statement acquires an explicit lock; a row lock targets specific rows, while a table lock covers the entire table. PostgreSQL's concurrency control is built around MVCC (multi-version concurrency control), which keeps reads and writes from blocking each other and reduces lock contention.

  Prerequisites: PostgreSQL、Transaction (BEGIN/COMMIT/ROLLBACK)

• Transaction (BEGIN/COMMIT/ROLLBACK)

  A mechanism that bundles multiple operations into a single indivisible unit. BEGIN starts a transaction, COMMIT finalizes all its changes, and ROLLBACK undoes them all. It guarantees the ACID properties (atomicity, consistency, isolation, durability), and SAVEPOINT lets you mark a point within a transaction to roll back to partially.

• COPY statement and \copy

  Mechanisms for bulk data transfer between a table and a file. The SQL COPY statement reads/writes a file on the server directly under the server process's privileges. The psql meta-command \copy handles the file under the client's privileges, internally issuing COPY.

  Prerequisites: psql meta-commands、psql

• Database cluster

  The entire filesystem area managed by a single PostgreSQL server instance, containing multiple databases. It corresponds to the data directory created by initdb and holds multiple databases and roles.

  Prerequisites: PostgreSQL

  Related: initdb

• System catalog (pg_catalog)

  The schema holding metadata in PostgreSQL's native format, storing information about every server object (tables, indexes, types, functions, privileges, etc.). Combined with system information functions such as current_database(), it lets you inspect internal state.

  Prerequisites: Index (CREATE INDEX)、PostgreSQL、Schema

• Procedural language PL/pgSQL (functions, procedures)

  PL/pgSQL is PostgreSQL's built-in procedural language, allowing functions and procedures to be written with control structures (IF, loops, etc.). Functions return a value and can be used inside queries, while procedures execute a sequence of operations—including transaction control—invoked with CALL.

  Prerequisites: PostgreSQL、Sequence、Transaction (BEGIN/COMMIT/ROLLBACK)

• Data types (INTEGER/NUMERIC/VARCHAR/TEXT/BOOLEAN/DATE/TIMESTAMP/JSON/JSONB)

  PostgreSQL's major column data types. INTEGER/BIGINT hold whole numbers, NUMERIC holds arbitrary-precision numbers, VARCHAR holds variable-length strings (optionally length-limited), TEXT holds unlimited-length strings, BOOLEAN holds true/false, DATE/TIMESTAMP hold date and date-time values, JSON stores JSON as text, and JSONB stores JSON in a parsed binary form that supports indexing and faster operations.

  Prerequisites: PostgreSQL

  Related: String functions (char_length/length/lower/upper/substring/replace/trim/`||`/LIKE)

• psql

  The standard interactive terminal (command-line client) for PostgreSQL. Besides executing SQL statements, it supports backslash-prefixed meta-commands to inspect catalog information and change display settings.

  Prerequisites: PostgreSQL

  Related: psql meta-commands

• WAL (write-ahead log)

  A log that records changes before they are written to the actual data files. It underpins crash recovery and, combined with WAL archiving, enables PITR.

  Prerequisites: PITR (point-in-time recovery)、Transaction (BEGIN/COMMIT/ROLLBACK)、WAL archiving (archive_command)

• Aggregate functions (count/sum/avg/max/min)

  Functions that summarize multiple rows into a single value: count (count(*) counts all rows) returns the row count, sum returns the total, avg returns the average, and max/min return the maximum/minimum. Commonly combined with GROUP BY for per-group aggregation.

  Prerequisites: SELECT statement (WHERE/ORDER BY/GROUP BY/HAVING/DISTINCT/LIMIT/OFFSET)

• autovacuum

  A set of background processes that automatically run VACUUM/ANALYZE once a table's update/delete activity crosses a threshold (e.g. autovacuum_vacuum_threshold). In most cases it greatly reduces the need for manual VACUUM and prevents bloat and stale statistics (heavy-update workloads may still require manual VACUUM).

  Prerequisites: Write DML (INSERT/UPDATE/DELETE)

  Related: VACUUM / ANALYZE

• Database roles / users

  PostgreSQL unifies the concepts of "user" and "group" into a single notion of a role. CREATE ROLE ... LOGIN creates a role that can log in (equivalent to a user), with default attributes (whether it can log in, superuser status, etc.) and an optional expiry (VALID UNTIL). ALTER ROLE changes attributes and DROP ROLE removes a role.

  Prerequisites: PostgreSQL

• DBMS (Database Management System)

  Software that centrally manages data storage, retrieval, updates, concurrency control, and recovery. It shields applications from direct file manipulation, guaranteeing consistency, durability, and concurrency control through a standard interface such as SQL.

  Prerequisites: Locking and concurrency control

• Deadlock

  A state in which multiple transactions each wait for a lock held by the other, so none can proceed. PostgreSQL automatically detects deadlocks and resolves them by forcibly rolling back one of the transactions.

  Prerequisites: Locking and concurrency control、PostgreSQL、Transaction (BEGIN/COMMIT/ROLLBACK)

• Filesystem-level backup

  A backup method that copies the entire data directory at the filesystem level, either as a simple copy while the server is stopped, or as a low-level backup performed while the server is running (via pg_backup_start()/pg_backup_stop() as of PostgreSQL 15, or pg_basebackup).

  Prerequisites: COPY statement and \copy、PostgreSQL

• information_schema

  A schema providing access to database metadata (tables, columns, constraints, etc.) in a SQL-standard-compliant form, enabling queries that are not tied to a specific DBMS implementation.

  Prerequisites: DBMS (Database Management System)、Schema

• initdb

  The command that initializes a new database cluster: it creates the data directory and builds the initial system catalogs, including the template1 and template0 databases.

  Prerequisites: System catalog (pg_catalog)、Template databases (template0 / template1)

  Related: Database cluster

• Isolation levels (READ COMMITTED/REPEATABLE READ/SERIALIZABLE)

  A setting that determines how much of other transactions' changes are visible to a concurrent transaction. Standard SQL defines four levels (READ UNCOMMITTED, with dirty reads of uncommitted data allowed; READ COMMITTED; REPEATABLE READ; and SERIALIZABLE), but PostgreSQL treats READ UNCOMMITTED the same as READ COMMITTED, leaving effectively three levels. READ COMMITTED (PostgreSQL's default) sees only committed data but can suffer non-repeatable reads; REPEATABLE READ holds a snapshot from transaction start, preventing not only non-repeatable reads but also phantom reads (a PostgreSQL-specific behavior); SERIALIZABLE is the strictest level, guaranteeing consistency equivalent to serial execution.

  Prerequisites: PostgreSQL、Transaction (BEGIN/COMMIT/ROLLBACK)

• psql meta-commands

  Special backslash-prefixed commands inside psql: \l lists databases, \dt lists tables, \du lists roles, \c switches connection, \? shows help for meta-commands, \h shows help for SQL syntax, and \timing toggles display of statement execution time.

  Related: psql

• pg_ctl

  The utility for controlling the PostgreSQL server process, with subcommands start/stop/restart/reload/status to start, stop, restart, reload configuration, and check status.

  Prerequisites: Table definition DDL (CREATE/ALTER/DROP TABLE, constraints)、PostgreSQL

• postgresql.conf

  The main configuration file for server-wide operating parameters, setting listen_addresses (listen address), port (listen port), max_connections (max connections), and logging_collector/log_destination (log output method). It supports an include directive to pull in other files.

  Prerequisites: PostgreSQL

• Schema

  A namespace within a database that logically groups tables and other objects. The search_path setting specifies the order of schemas searched when resolving unqualified object names.

• Tablespace

  An object that specifies which on-disk directory stores the physical data of tables and indexes. It lets objects within a single database be placed on different storage (e.g., fast disks versus high-capacity disks).

  Prerequisites: Index (CREATE INDEX)

• Template databases (template0 / template1)

  Special databases that serve as templates for CREATE DATABASE. template1 is the default, user-customizable template that ordinary CREATE DATABASE statements copy; template0 preserves the pristine initial state and is used as the source when a clean copy (e.g., a different locale) is needed.

  Prerequisites: COPY statement and \copy

• Trigger

  A mechanism that automatically invokes a specified function when an event such as INSERT/UPDATE/DELETE occurs. It can be scoped to row-level or statement-level and timed as BEFORE or AFTER the event, typically paired with a trigger function written in PL/pgSQL.

  Prerequisites: Procedural language PL/pgSQL (functions, procedures)

  Related: Write DML (INSERT/UPDATE/DELETE)

• Table definition DDL (CREATE/ALTER/DROP TABLE, constraints)

  DDL statements that define, modify, and remove tables. CREATE TABLE defines columns and constraints (PRIMARY KEY, FOREIGN KEY/foreign keys, UNIQUE, CHECK, NOT NULL); ALTER TABLE (e.g., ALTER TABLE ADD COLUMN) changes the structure; DROP TABLE removes the table.

• createuser / dropuser

  Command-line wrappers that create and remove roles (users); internally they issue CREATE ROLE and DROP ROLE statements respectively.

  Prerequisites: Database roles / users

• Date/time functions (age/now/current_date/current_timestamp/extract/to_char)

  Functions for working with date/time values: age computes the difference between two timestamps, now/current_timestamp return the current time, current_date returns the current date, extract pulls a component (year, month, day, etc.) out of a date/time value as a numeric double precision value, and to_char formats a date/time as a string.

  Prerequisites: Data types (INTEGER/NUMERIC/VARCHAR/TEXT/BOOLEAN/DATE/TIMESTAMP/JSON/JSONB)

• GRANT / REVOKE

  DCL statements that grant or revoke privileges on objects such as tables to a role. GRANT SELECT grants a single privilege, while GRANT ALL grants all privileges at once.

  Prerequisites: SELECT statement (WHERE/ORDER BY/GROUP BY/HAVING/DISTINCT/LIMIT/OFFSET)

• JOIN (INNER/LEFT/RIGHT/FULL/CROSS)

  The clause for querying across multiple tables. INNER JOIN returns only rows matching in both tables; LEFT JOIN returns all left-table rows plus matches from the right; RIGHT JOIN is the reverse; FULL JOIN returns all rows from both tables regardless of a match; CROSS JOIN returns the unconditional Cartesian product.

• Normalization and database design

  A design technique that progressively decomposes tables to eliminate redundancy and update anomalies. First normal form (1NF) requires each column value to be atomic (single-valued). Second normal form (2NF) builds on 1NF by removing partial functional dependencies (columns dependent on only part of the primary key), and third normal form (3NF) further removes transitive functional dependencies (indirect dependencies through a non-key column). Database design refers to the overall process of deciding table structure, including this normalization.

  Prerequisites: Write DML (INSERT/UPDATE/DELETE)

• pg_config

  A utility that prints the build configuration of the installed PostgreSQL (install paths, compile options, version, etc.), commonly consulted when building extension modules.

  Prerequisites: PostgreSQL

• pg_controldata

  A utility that displays the control information (the pg_control file) of a database cluster, such as the database state, the latest checkpoint location, and WAL version.

  Prerequisites: Database cluster

• Logical backup and restore (pg_dump/pg_dumpall/pg_restore)

  Tools for SQL-level (logical) backup: pg_dump backs up a single database, while pg_dumpall dumps the whole cluster including roles and tablespaces. pg_restore restores from pg_dump output in custom, directory, or tar format, and its -t option restricts restore to a specific table.

  Prerequisites: Tablespace

• pg_hba.conf

  The configuration file controlling client authentication. Each line specifies an authentication method (trust, md5, scram-sha-256, peer, etc.) for a connection source (address, database, user); rows are evaluated top-down and the first matching row's rule applies.

  Related: Authentication methods (trust / md5 / scram-sha-256 / peer)

• pg_isready

  A lightweight utility that checks whether a PostgreSQL server is ready to accept connections, returning an exit code indicating its status (accepting, rejecting, no response, or unknown) for health checks.

  Prerequisites: PostgreSQL

• pg_resetwal

  A utility that resets the WAL and other control information of a database cluster that has become unstartable (e.g., due to corruption), forcing it into a startable state. It is a last resort that can cause data loss and is not used in normal operation.

  Prerequisites: Database cluster

• PITR (point-in-time recovery)

  A mechanism that combines a base backup with subsequent archived WAL to restore a database to an arbitrary point in the past, enabling flexible recovery such as rolling back to just before an accidental operation or failure.

• PostgreSQL License

  The permissive license applied to PostgreSQL itself. Similar to BSD/MIT-style licenses, it freely allows modification, redistribution, and commercial use as long as the copyright notice and disclaimer are retained. It has no copyleft clause.

  Related: PostgreSQL

• Relational data model

  A data model that represents data as a set of two-dimensional tables (relations) made of rows (tuples) and columns (attributes). Tables are related via keys and manipulated through set-based queries (SQL).

  Prerequisites: Set operations (UNION/INTERSECT/EXCEPT)

• Sequence

  A database object that generates a unique sequential number on each call, commonly used for auto-incrementing primary keys, created with CREATE SEQUENCE.

• Set operations (UNION/INTERSECT/EXCEPT)

  Operations that combine multiple SELECT results as sets. UNION returns the union (removing duplicates by default; UNION ALL keeps them), INTERSECT returns the intersection, and EXCEPT returns the set difference.

  Prerequisites: SELECT statement (WHERE/ORDER BY/GROUP BY/HAVING/DISTINCT/LIMIT/OFFSET)

• Streaming / logical replication basics

  PostgreSQL's replication mechanisms. Streaming replication sends WAL in real time to a standby for physical synchronization, while logical replication publishes and subscribes to changes as logical row-level modifications. Silver covers only conceptual basics (setup, monitoring, and troubleshooting are Gold-level topics).

  Prerequisites: PostgreSQL

• WAL archiving (archive_command)

  A mechanism that copies WAL segment files to a separate storage location before they are recycled, preserving them for later use. The archive_command setting in postgresql.conf specifies the shell command run to archive each WAL segment.

  Prerequisites: PostgreSQL、postgresql.conf

• SQL

  The language for relational databases; split into DDL/DML/DQL=SELECT/DCL.

  Prerequisites: SELECT statement (WHERE/ORDER BY/GROUP BY/HAVING/DISTINCT/LIMIT/OFFSET)

• Authentication methods (trust / md5 / scram-sha-256 / peer)

  Common authentication methods specified in pg_hba.conf: trust allows connections unconditionally, md5 and scram-sha-256 are password-based (scram-sha-256 being the more secure default), and peer authenticates using the OS username for local connections.

  Related: pg_hba.conf

• String functions (char_length/length/lower/upper/substring/replace/trim/`||`/LIKE)

  Functions and operators for manipulating and matching strings: char_length/length return the character count, lower/upper change case, substring extracts part of a string, replace substitutes text, trim strips leading/trailing whitespace (or specified characters), the || operator concatenates strings, and the LIKE predicate does pattern matching (% matches any length, _ matches one character; a case-insensitive ILIKE also exists).

  Prerequisites: Aggregate functions (count/sum/avg/max/min)

  Related: Data types (INTEGER/NUMERIC/VARCHAR/TEXT/BOOLEAN/DATE/TIMESTAMP/JSON/JSONB)

• Subquery

  A SELECT statement nested inside another SQL statement. One returning a single row and column is called a scalar subquery. Subqueries can also be used to test the presence or absence of results, as with NOT EXISTS, the negation of EXISTS.

  Prerequisites: SELECT statement (WHERE/ORDER BY/GROUP BY/HAVING/DISTINCT/LIMIT/OFFSET)

• VACUUM / ANALYZE

  VACUUM reclaims dead tuples left by deletes and updates, preventing table bloat. VACUUM FULL physically rewrites the table under an exclusive lock and returns space to the OS (not generally recommended in normal operation). ANALYZE updates the statistics used by the query planner. The two serve different purposes, and autovacuum runs them automatically on a schedule.

  Prerequisites: Locking and concurrency control

  Related: autovacuum

• Versioning and release cycle

  PostgreSQL distinguishes major versions (new features, released roughly annually) from minor versions (bug and security fixes only). Under its support policy, each major version is supported for about five years from its initial release.

  Prerequisites: PostgreSQL

• OSS-DB / PostgreSQL community

  The distributed community that sustains PostgreSQL development and discussion. Developers communicate on mailing lists such as pgsql-hackers (feature development and patch discussion) and pgsql-bugs (bug reports); anyone can contribute by filing bug reports or joining discussions.

  Prerequisites: PostgreSQL