OSS-DB Gold — knowledge map
The 54 core concepts of OSS-DB Gold 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 (54)
CLUSTER
A SQL command that physically reorders a table's rows to match a specified index's order. It improves range-scan locality (correlation) and reduces I/O, but takes an exclusive lock on the table while running, and newly inserted rows do not automatically maintain cluster order afterward.
Prerequisites: correlation (statistic)
PostgreSQL process architecture (postmaster/backend/background)
PostgreSQL's process architecture: the postmaster is the parent process started first, handling connection acceptance and process management. A backend is a child process forked per client connection. Background processes (autovacuum launcher, WAL writer, checkpointer, etc.) handle server-wide auxiliary work.
walsender
The backend process on the primary that sends streaming replication data. One process is started per connected standby (or client such as pg_receivewal) and continuously ships WAL; the number of concurrent connections is capped by max_wal_senders.
Prerequisites: pg_receivewal、PostgreSQL process architecture (postmaster/backend/background)
CHECKPOINT
A SQL command that immediately flushes dirty pages to disk and advances the WAL replay starting point. Used to force an immediate checkpoint on demand (e.g., before a backup) rather than waiting for the periodic automatic one. Issuing it too often causes I/O spikes.
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.
auto_explain
An extension that automatically logs the execution plan of any query whose runtime exceeds a configured threshold (log_min_duration). It lets you inspect the plan of a slow query that occurred in production after the fact, without rewriting the query with EXPLAIN; it must be registered in shared_preload_libraries.
Prerequisites: EXPLAIN / EXPLAIN ANALYZE
pg_basebackup
A tool that takes a physical base backup from a running cluster over a streaming-replication connection. It is used to bootstrap a standby for replication or to obtain a starting point for PITR, internally relying on the non-exclusive backup mechanism.
Prerequisites: PITR (point-in-time recovery)、CLUSTER、Non-exclusive backup
correlation (statistic)
A statistic ranging from -1 to 1 that expresses how closely a column's logical value order matches its physical storage order. Values near 1 mean the physical order matches the sort order well, favoring Index Scan since disk reads stay sequential — one input the planner uses when choosing a scan method.
Memory parameters (shared_buffers/huge_pages/work_mem/maintenance_work_mem)
The key memory-related performance parameters: shared_buffers sets the size of the shared buffer (data cache); huge_pages controls whether the OS huge-pages feature is used; work_mem sets the working memory available per operation such as a sort or hash join (governing whether an external merge to disk occurs); maintenance_work_mem sets the memory used for maintenance operations like VACUUM and CREATE INDEX.
Prerequisites: Join strategies (Nested Loop / Hash Join / Merge Join)
Streaming replication error handling
When the connection between the walreceiver and walsender drops, the basic behavior (configuration-dependent) is to attempt automatic reconnection. For transient causes like a network blip, reconnecting is enough; but if the standby has fallen behind beyond the WAL retained by settings like wal_keep_size, reconnection alone cannot recover and a fresh base backup is required.
Prerequisites: WAL and checkpoint parameters (fsync/checkpoint_timeout/max_wal_size/wal_keep_size/checkpoint_completion_target)、walreceiver、walsender
WAL and checkpoint parameters (fsync/checkpoint_timeout/max_wal_size/wal_keep_size/checkpoint_completion_target)
Performance parameters around WAL and checkpoints. fsync determines whether writes are guaranteed to reach disk (off risks data loss on crash). checkpoint_timeout sets the maximum interval between automatic checkpoints. max_wal_size is the target upper bound on WAL growth between checkpoints. wal_keep_size is the minimum amount of WAL retained for standbys. checkpoint_completion_target is the target fraction of the checkpoint interval over which checkpoint writes are spread.
Prerequisites: CHECKPOINT
walreceiver
The process on the standby that receives streaming replication data. It connects to the primary's walsender, writes the incoming WAL to local WAL, and hands it off for apply. If the connection drops, it attempts automatic reconnection depending on configuration.
Prerequisites: walsender
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: CLUSTER
Logging threshold parameters (log_min_duration_statement/log_autovacuum_min_duration/log_lock_waits/log_checkpoints/log_temp_files)
Monitoring parameters controlling what gets logged. log_min_duration_statement logs slow queries exceeding a threshold duration; log_autovacuum_min_duration logs autovacuum runs that take longer than a threshold; log_lock_waits logs when a lock wait exceeds deadlock_timeout; log_checkpoints logs checkpoint occurrences; log_temp_files logs temp-file usage.
Prerequisites: CHECKPOINT、deadlock_timeout
max_parallel_workers
A parameter that sets the cluster-wide cap on worker processes usable for parallel queries. The per-query parallel degree is separately limited by max_parallel_workers_per_gather.
Prerequisites: CLUSTER、Parallel query (max_worker_processes/max_parallel_workers_per_gather)
Non-exclusive backup
A backup mode where the backup label is tracked in server shared memory, so even if the session that called pg_backup_start()/pg_backup_stop() (pg_start_backup()/pg_stop_backup() before PostgreSQL 15) crashes, the backup remains safe. Introduced in PostgreSQL 9.6; the older exclusive mode was removed in 15, so this is now the only mode.
Prerequisites: pg_start_backup() / pg_stop_backup()
pg_stat_bgwriter
A server-wide statistics view (a single row) tallying background-writer activity. As of PostgreSQL 17, checkpoint-related statistics (checkpoint counts, durations, etc.) and buffers_backend (blocks written by backend processes) were split out into the new pg_stat_checkpointer view, so pg_stat_bgwriter now retains only buffers_clean (blocks written by the background writer), maxwritten_clean, and buffers_alloc. In PG16 and earlier, checkpoint stats were still part of this view.
Prerequisites: CHECKPOINT、PostgreSQL process architecture (postmaster/backend/background)
pg_stats
A human-readable view over pg_statistic, exposing columns such as null_frac (fraction of NULLs), n_distinct (estimated number of distinct values), most_common_vals, histogram_bounds, and correlation (correlation with physical row order) — the columns you inspect when investigating why the planner chose a given plan.
Prerequisites: correlation (statistic)
Related: Planner distribution stats (most_common_vals/most_common_freqs/histogram_bounds)、null_frac / n_distinct、pg_statistic
Streaming replication config parameters (wal_level/max_wal_senders/synchronous_standby_names/synchronous_commit/hot_standby_feedback)
The core parameters for setting up streaming replication: wal_level=replica or higher emits the WAL volume needed for replication; max_wal_senders caps the number of concurrent walsender connections; synchronous_standby_names names the standbys used for synchronous replication; synchronous_commit determines how much synchronization is required before a commit is acknowledged; hot_standby_feedback keeps standby queries from blocking VACUUM on the primary.
Prerequisites: synchronous_commit、wal_level
Related: walsender
synchronous_commit
A parameter controlling how much WAL durability is awaited before a commit is acknowledged to the client. With on (the default), it waits for a local fsync and, when synchronous standbys are configured, for the standby to flush (fsync) the WAL (remote_write waits only for the standby OS write, remote_apply waits for apply). Setting it to off improves performance but risks losing the most recent commits on a crash.
Prerequisites: WAL and checkpoint parameters (fsync/checkpoint_timeout/max_wal_size/wal_keep_size/checkpoint_completion_target)
Audit-log and activity-statistics settings (log_statement/track_functions/track_activities)
The core of audit logging is log_statement, which controls which SQL statements get logged (none/ddl/mod/all). By contrast, track_functions (collecting function-call statistics) and track_activities (whether the currently running query appears in pg_stat_activity) are activity-statistics/monitoring settings, not audit logging in the strict sense, and should be distinguished from log_statement.
Prerequisites: pg_stat_activity (wait_event_type/wait_event)
Cluster directories (pg_tblspc/pg_wal/pg_stat_tmp)
Key directories under the data directory: pg_tblspc holds symbolic links to each tablespace, and pg_wal (renamed from pg_xlog in PostgreSQL 10) holds WAL segment files. pg_stat_tmp held temporary statistics files, but it was retired in PostgreSQL 15 when the cumulative statistics system moved to shared memory (an empty directory may still be created for extension compatibility but is no longer used).
Prerequisites: CLUSTER
CREATE STATISTICS / pg_statistic_ext
Extended statistics that capture correlation across multiple columns (columns that are logically strongly linked). CREATE STATISTICS defines the statistics object (registered in the pg_statistic_ext catalog), and after ANALYZE runs the actual statistics data is stored in pg_statistic_ext_data. It improves selectivity estimates for combined WHERE clauses that single-column statistics alone tend to over- or under-estimate. There are three kinds: ndistinct captures the number of distinct combinations of column values, dependencies (functional dependencies) captures functional dependencies between columns, and mcv (most-common-values) captures a list of frequent value combinations.
Prerequisites: correlation (statistic)
deadlock_timeout
A parameter setting how long a transaction waits on a lock before PostgreSQL runs deadlock detection. A larger value reduces detection overhead but delays discovering a deadlock; a smaller value has the opposite trade-off.
Diagnosing from error messages (FATAL/PANIC/ERROR)
PostgreSQL log entries (routed via log_destination) carry severities that escalate: DEBUG (developer-level detail) < LOG (operational information) < NOTICE (mild advisory to the user) < WARNING (unexpected but processing continues) < ERROR (aborts the current statement and its transaction) < FATAL (terminates the current connection/session) < PANIC (the most severe level, which resets the entire server by taking down all backend processes and triggers an automatic restart to avoid suspected shared-memory corruption). Messages like could not write to file (write failure) or out of memory typically signal OS resource exhaustion.
Prerequisites: PostgreSQL process architecture (postmaster/backend/background)
EXPLAIN / EXPLAIN ANALYZE
Commands that visualize the plan the planner chose. EXPLAIN shows only the plan without actually running the query (estimates only). EXPLAIN ANALYZE actually executes the query and reports real row counts and timings alongside the estimates, so a gap between estimated and actual rows can diagnose issues like stale statistics.
Index Only Scan
An execution-plan scan method that returns results using only the columns present in the index, skipping access to the heap (the actual table). The planner can choose it when every column the query needs is covered by the index (a covering index), but whether it actually avoids heap access also depends on the state of the Visibility Map.
Related: Visibility Map
initdb --data-checksums
An initdb option that adds checksums to data pages when the cluster is initialized, making storage corruption easier to detect. Toggling it later requires re-initializing the cluster (or the pg_checksums tool). Through PostgreSQL 17 it was disabled by default and enabling it required explicitly passing --data-checksums, but from PostgreSQL 18 onward it is enabled by default; use --no-data-checksums to disable it.
Prerequisites: CLUSTER
Join strategies (Nested Loop / Hash Join / Merge Join)
The main join methods the planner chooses among. Nested Loop efficiently probes the inner side (e.g., via an index) for each of a small number of outer rows, favoring small joins. Hash Join is for equality joins only, building a hash table on one side to avoid a full cross-comparison. Merge Join merges both sides assuming they are already sorted (or cheaply sortable). Index availability and sortedness are the key factors in the choice.
Planner distribution stats (most_common_vals/most_common_freqs/histogram_bounds)
Distribution statistics in pg_stats. most_common_vals (MCV) lists frequently occurring values, paired with most_common_freqs, their respective frequencies. histogram_bounds gives the boundaries of an equi-frequency histogram for values not in the MCV list, used to estimate selectivity for range predicates (WHERE col > constant).
Related: pg_stats
null_frac / n_distinct
Column statistics in pg_stats. null_frac is the fraction of NULL values in the column; n_distinct is the estimated number of distinct values (a positive value is an absolute count, a negative value indicates a ratio relative to row count).
Related: pg_stats
Parallel query (max_worker_processes/max_parallel_workers_per_gather)
The mechanism for splitting query work across multiple processes. max_worker_processes caps the total background workers available server-wide; max_parallel_workers_per_gather caps how many parallel workers a single query can use at once.
pg_cancel_backend()
A function that cancels only the currently running query of the specified backend, while the session itself continues. To disconnect the entire session instead, pg_terminate_backend() is used (the contrasting function).
Prerequisites: PostgreSQL process architecture (postmaster/backend/background)
pg_receivewal
A dedicated tool that continuously receives and stores just the WAL stream, without setting up a full standby. It serves as an alternative to WAL archiving or for near-real-time WAL backups. Using a replication slot makes the primary retain unsent WAL, so WAL can be received without gaps even across a temporary disconnection.
pg_reload_conf()
A function that immediately applies configuration changes that take effect via SIGHUP, without restarting the server. However, parameters fixed at server startup (PGC_POSTMASTER, e.g. shared_buffers, max_connections) are not applied by a reload and require a full server restart.
Prerequisites: Memory parameters (shared_buffers/huge_pages/work_mem/maintenance_work_mem)
pg_rewind
A tool that re-synchronizes an old primary as a standby of the new one. It overwrites only the diverged data blocks with the new primary's data since promotion, making it faster than retaking a full base backup. It is the correct procedure for re-folding the old primary in without risking a split-brain from running both primaries at once.
Related: Split-brain
pg_stat_activity (wait_event_type/wait_event)
A statistics view showing the state of every currently connected session: the running query, its state (state=active/idle/idle in transaction, etc.), plus wait_event_type indicating the category of wait (e.g., Lock) and the more specific wait_event (e.g., transactionid, meaning it is waiting for another transaction to commit or roll back).
pg_stat_wal_receiver
A replication monitoring view queried on the standby. It shows the state of the WAL stream it is receiving—such as the primary it's connected to, the received LSN, and the connection start time—in a single row, used to check the walreceiver process's status.
Prerequisites: walreceiver
pg_statio_all_tables
A statistics view showing the physical I/O breakdown per table: blocks found in the shared buffer (heap_blks_hit) versus blocks read from disk (heap_blks_read), broken out for the table, its indexes, and TOAST — used to spot tables with a low buffer-hit ratio.
Prerequisites: TOAST
Planner control parameters (enable_*/random_page_cost/hash_mem_multiplier)
Parameters for diagnosing and controlling planner behavior. enable_* (e.g., enable_seqscan) temporarily disables a specific scan/join method to test the planner's decisions — a diagnostic switch. random_page_cost estimates the cost of random I/O, affecting how favorable Index Scan looks (often lowered on SSD-backed storage). hash_mem_multiplier controls how many multiples of work_mem hash-based operations may use.
Prerequisites: Memory parameters (shared_buffers/huge_pages/work_mem/maintenance_work_mem)
REINDEX
A SQL command that rebuilds a bloated or corrupted index. Plain REINDEX takes an exclusive lock on the target table (or the relevant index) before rebuilding, so reads and writes to that table are blocked while it runs. Since PostgreSQL 12, the REINDEX CONCURRENTLY option avoids that exclusive lock, allowing the index to be rebuilt online while reads and writes continue (though it briefly blocks other DDL and cannot run inside a transaction block). Whether an exclusive lock is required is DBMS-dependent behavior.
Related: REINDEX CONCURRENTLY
REINDEX CONCURRENTLY
An index-rebuild command available from PostgreSQL 12 onward. Unlike plain REINDEX, it does not take an exclusive table lock, so it rebuilds the index without blocking writes. However, a failure partway through can leave an INVALID index behind, which must then be dropped and rebuilt manually.
Related: REINDEX
restart_after_crash
A parameter controlling whether the entire server automatically restarts after a backend crash (default on). Setting it to off leaves the server stopped after an abnormal termination, useful when automatic recovery is undesirable until the cause has been investigated.
Prerequisites: PostgreSQL process architecture (postmaster/backend/background)
Failure triage (server stoppage, data loss, OS resource exhaustion, process state)
The standard first-pass triage: check via ps whether the postmaster and backend processes are alive, check disk usage with df/du, and check memory with free. A full disk is especially critical since it directly blocks WAL writes and can lead to a crash, so it is the top priority to clear. When data loss is suspected, check the PostgreSQL log for PGDATA/WAL corruption, and if the server cannot start, consider forcing recovery with pg_resetwal or restoring from the most recent backup.
Prerequisites: pg_resetwal、PostgreSQL process architecture (postmaster/backend/background)
Split-brain
A situation—caused by a network partition or a mistaken restart of the old primary, among others—where two primaries (old and new) accept writes simultaneously. It destroys data consistency and must be avoided at all costs; fencing (STONITH: forcibly isolating the faulty node) prevents dual operation, and afterward the old primary is correctly re-folded in as a standby via pg_rewind or similar.
Related: pg_rewind
pg_start_backup() / pg_stop_backup()
Functions that start and stop an online backup for PITR (valid in versions 12-14; renamed to pg_backup_start()/pg_backup_stop() and the old names removed in 15). pg_start_backup() notifies the server that a backup is beginning and forces a checkpoint; pg_stop_backup() signals the end of the backup and finalizes the backup label file and WAL.
Prerequisites: PITR (point-in-time recovery)、CHECKPOINT
wal_level
A parameter controlling how much information is written to WAL. minimal keeps WAL minimal and supports neither replication nor WAL archiving (PITR/hot standby); replica (the default, consolidating the old archive/hot_standby levels) includes what streaming replication and archiving need; logical adds further information (such as pre-change row data) required by logical replication.
Prerequisites: PITR (point-in-time recovery)
pg_stat_ssl
A statistics view showing whether each backend connection is SSL-encrypted, along with the cipher and protocol version in use. It reflects wire-level SSL status, not encryption of the stored data itself.
Prerequisites: PostgreSQL process architecture (postmaster/backend/background)
pg_stat_statements
An extension that keeps cumulative execution statistics per normalized query string (with literal constants stripped out): call counts, total/average execution time, blocks read, and more — used to identify which queries impose the most load. It must be registered in shared_preload_libraries, and the statistics it exposes as a view can be cleared with pg_stat_statements_reset().
Related: auto_explain、shared_preload_libraries
TOAST
A mechanism (The Oversized-Attribute Storage Technique) that automatically compresses and splits column values larger than the page size (default 8KB) into a separate TOAST table. It transparently handles large variable-length values such as text or JSONB.
Visibility Map
A bitmap recording, per heap page, whether every row on that page is visible to all transactions (and frozen). Because the index itself carries no visibility information, Index Only Scan consults this map: pages marked fully visible skip the heap entirely, while other pages fall back to the heap to check visibility. It is updated by VACUUM.
Related: Index Only Scan
pg_statistic
The system catalog holding the per-column statistics the planner uses to choose execution plans, stored in raw internal format. It is refreshed by running ANALYZE; the values are stored as type-dependent arrays and scalars that are hard to read directly, so it's normally accessed via the human-readable pg_stats view.
Related: pg_stats
ignore_system_indexes / ignore_checksum_failure
Startup troubleshooting flags for emergency access to a corrupted cluster. ignore_system_indexes ignores broken system-catalog indexes, falling back to sequential scans instead. ignore_checksum_failure lets reads proceed past a detected data-checksum mismatch instead of erroring out — a high-risk setting that tolerates data corruption.
Prerequisites: CLUSTER