OSS-DB GoldStudy guide

Go deep on the encryption, authentication, and audit foundations laid down when building a server: SSL communication and data encryption with pgcrypto, checking encryption status with pg_stat_ssl, client authentication via SCRAM-SHA-256, the audit-log core log_statement plus the activity-statistics settings track_functions, track_activities, pinning parameters per user/database with ALTER ROLE/ALTER DATABASE, checksum-enabled initialization with initdb --data-checksums, and key cluster directories pg_tblspc, pg_wal, pg_stat_tmp.

Go deep on the backup/recovery and daily-maintenance command set: non-exclusive backups, PITR, and WAL mechanics; pg_dump, pg_dumpall, pg_basebackup; pg_start_backup()/pg_stop_backup(), valid in versions 12-14; VACUUM, vacuumdb, ANALYZE, CLUSTER, REINDEX, CHECKPOINT; autovacuum internals; bloat investigation with pgstattuple; pg_cancel_backend()/pg_reload_conf(); parallelism via max_parallel_workers; and the monitoring role pg_monitor.

Go deep on the cluster's physical layout and process internals: physical file placement and the process architecture (postmaster, backend processes, background processes), TOAST for storing oversized values out-of-line, FILLFACTOR for controlling a table's reserved free space, autovacuum internals, and foreign data access via postgres_fdw, file_fdw, CREATE SERVER, USER MAPPING, FOREIGN TABLE.

Go deep on building and monitoring streaming and logical replication: streaming setup via wal_level, max_wal_senders, synchronous_standby_names, synchronous_commit, hot_standby_feedback; logical replication's CREATE/ALTER/DROP PUBLICATION and SUBSCRIPTION; the monitoring views pg_stat_replication and pg_stat_wal_receiver; the sender/receiver processes walsender/walreceiver; and the dedicated receiving tool pg_receivewal.

Learn the family of statistics views that reflect the server's "right now": pg_stat_activity (wait_event_type/wait_event) for connected sessions, pg_locks for lock contention, pg_stat_database for per-database aggregates, pg_stat_all_tables for table access patterns, pg_statio_all_tables for physical I/O, pg_stat_archiver for WAL archiving, and pg_stat_bgwriter for checkpoint/buffer writeback activity.

Learn pg_statistic/pg_stats, the basis on which the planner chooses execution plans: what per-column null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds, and correlation mean; default_statistics_target, which controls statistics precision; effective_cache_size, used to estimate cache size; and extended statistics (CREATE STATISTICS, pg_statistic_ext) for capturing multi-column correlation.

Learn to read execution plans: EXPLAIN (visualizes the plan) versus EXPLAIN ANALYZE (actually executes and reports real measurements), join strategies Nested Loop, Hash Join, and Merge Join, scan types Seq Scan, Index Scan, and Bitmap Scan, window functions, and parallel query (max_worker_processes, max_parallel_workers_per_gather) that splits work across multiple processes.

Learn additional server-wide and per-query monitoring tools: shared_preload_libraries, which loads extensions at server startup, auto_explain, which automatically logs execution plans, pg_stat_statements, which holds cumulative per-query statistics, and the logging parameters log_min_duration_statement, log_autovacuum_min_duration, log_lock_waits, log_checkpoints, and log_temp_files, which record slow queries, autovacuum activity, lock waits, checkpoints, and temp-file usage.

Learn PostgreSQL's key performance parameters: memory-related shared_buffers, huge_pages, work_mem, maintenance_work_mem; WAL/checkpoint-related wal_level, fsync, checkpoint_timeout, max_wal_size, wal_keep_size, checkpoint_completion_target; lock-related deadlock_timeout; and how planner/statistics/resource-usage parameters interact.

Learn execution-plan and SQL-level tuning: the relationship between Index Only Scan and the Visibility Map, function indexes and partial indexes, partitioning, planner-control enable_* parameters (e.g. enable_seqscan), random_page_cost, hash_mem_multiplier, and non-disruptive operations via CREATE INDEX CONCURRENTLY, FILLFACTOR, and REINDEX.

Learn the basics of failure triage: identifying issues from error messages, server stoppage, data loss, OS resource exhaustion, and process-state analysis; the timeout parameters statement_timeout, lock_timeout, and idle_in_transaction_session_timeout; synchronous_commit and restart_after_crash which govern post-crash consistency; server control via pg_ctl; and the lock-management ceiling max_locks_per_transaction.

Learn the procedures for recovering a corrupted database cluster: PITR (point-in-time recovery) as the central technique, pg_resetwal which rebuilds WAL control information, the startup troubleshooting flags ignore_system_indexes and ignore_checksum_failure, pg_xact which holds the commit log, emergency single-user mode, and VACUUM FREEZE as the countermeasure for transaction ID wraparound.

Learn how to handle failures while running replication: pg_ctl promote, which promotes a standby to primary; pg_rewind, which re-synchronizes an old primary as a standby of the new one; pg_receivewal, which continuously receives WAL directly; the conflicts unique to logical replication; and error handling for streaming replication.