2Performance monitoring

• 2.1Access Statistics Views

  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.

• 2.2Table and Column Statistics

  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.

• 2.3Query Execution Plans

  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.

• 2.4Additional Performance Monitoring Tools

  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.