PostgreSQL Database Administration on Win/Linux-Part 3 (PT)

Learn to install PostgreSQL from source code distribution, gaining control over installation location and file segregation for binaries, data, WAL, temp, and archives.

Install PostgreSQL 16.2 from source by downloading, extracting, configuring options, creating the Postgres user, building with make, installing, initializing data, and starting the server, including contrib.

Stop postgres, move pg_wal files from data directory to a new location with rsync, back up the wal folder, create a symbolic link, and verify startup with a log switch.

Learn how temp files spill to disk during sort and hash operations, why they affect PostgreSQL performance, and how to move them to a dedicated location or temp tablespace.

Shows how to move PostgreSQL temp files from the default pkg data location to a custom temp tablespace, enable log collection, reload configuration, and verify relocation.

Configure the default tablespace to route user objects to pg_default or a custom tablespace, then restart PostgreSQL. Explore scenarios for applying new tablespaces to databases and future objects.

Explore how PostgreSQL uses shared buffers and the OS cache to cache data, manage memory, and tune memory parameters for performance.

Explore how PostgreSQL manages shared buffers, uses the clock sweep algorithm for eviction, tracks usage and pin counts, and protects hot blocks with buffer ring concepts.

Demonstrates how PostgreSQL uses the shared buffer versus disk, with explain analyze buffers to reveal shared read and hit, and explains how a buffer ring protects memory for large tables.

Explore the PG underscore buffer cache extension to monitor the shared buffer in real time, install via the contrib module, and grant the PG_monitor role.

Explore the PG underscore pre-warm extension to pin tables in the shared buffer, reducing disk I/O, with manual pre-warm and auto warm via shared preload libraries.

Explore work_mem tuning in PostgreSQL, from default four MB allocations to in-memory sorting versus disk spillover, and how DBAs balance performance by setting the right value.

Tune work_mem with pgbench data to show how increasing from 64 MB to 128 MB reduces disk sorts, logs temp files, and balances memory across queries.

Adjust maintenance_work_mem to speed vacuum and maintenance tasks like index creation and foreign keys; allocate up to 1 GB for autovacuum, with 512 MB or 2 GB depending on RAM.

Discover how autovacuum work mem creates a separate memory segment from maintenance work mem and how to enable it in postgresql.conf for optimized vacuum performance.

Learn how to tune PostgreSQL max connections by testing with pgbench, balancing business demand with memory and CPU resources, and using connection pooling to prevent leaks and outages.

Learn to tune PostgreSQL connections by configuring idle session timeout and idle in transaction timeout at the user level, while using server-level keepalive and authentication timeout to manage pools.

Explore the pg_stat_activity view to monitor connections, identify idle and active sessions, and use pid-based commands to terminate long-running queries.

Explore how autovacuum mitigates table and index bloating by reclaiming dead tuples, and learn tuning strategies for maintenance_work_mem, autovacuum max workers, and autovacuum nap time.

Autovacuum tuning explains how cost limit and cost delay control autovacuum resource use. It compares aggressive, conservative, and mixed strategies and shows on-the-fly adjustments via alter system and reload conf.

Explore how checkpoints flush dirty buffers to disk and update the write ahead log sequence, enabling wall file recycling and robust crash recovery in PostgreSQL.

Balance checkpoint_timeout to optimize disk writes and recovery time; timed checkpoints speed recovery, and a starting value of 20 minutes yields about three checkpoints per hour.

Explain the checkpoint completion target as the fraction of time between checkpoints, with a default value of 0.9. Setting 0.9 spreads writes over the interval, reducing load compared with 0.5.

Explore max wal size, its default of one gigabyte, and how to prevent excessive requested checkpoints by favoring time-based checkpoints; monitor with pg_stat_bgwriter to balance performance and crash recovery.

Explore the pg_stat_bgwriter view in PostgreSQL and troubleshoot disk write activity by examining checkpoint, background writer, and backend contributions.

Set the effective cache size to 50–70% of system RAM to guide the optimizer's execution plans, accounting for shared buffers and system cache, with emphasis on index vs sequential scans.

Explore asynchronous behavior in PostgreSQL, tuning max worker process and max parallel workers per gather to balance background tasks and query parallelism. Consider standby implications, cpu impact, and throughput.

Explore how PostgreSQL processes queries from parsing to execution, including parser, analyzer, rewrite rules, planner, and executor. Learn how to monitor and tune performance with explain plan insights.

Explore explain and explain analyze to view the execution plan, compare estimates and actuals, understand costs, rows, and node work, and troubleshoot query performance.

Learn how the PostgreSQL planner chooses sequential, index, bitmap index, and bitmap heap scans. It uses data size, index availability, and visibility maps to optimize performance.

Understand nested loop joins in PostgreSQL by reading the outer and inner tables in an execution plan, use explain analyze, and improve performance with indexing on the inner department table.

Understand hash join in PostgreSQL: build a hash table from the inner relation and probe with the outer table, using work_mem and hash_mem_multiplier to improve execution time.

Learn how merge join works with pre-sorted inputs and index scans, its suitability for large data sets, and how to test alternative joins by toggling join methods and examining aggregates.

Discover index optimization in PostgreSQL, covering B-tree, hash, gin, gist, SP-GiST, and block range indexes, along with common mistakes and index maintenance considerations.

Partial indexes create an index on a subset of rows defined by a conditional expression, reducing index size and maintenance while speeding queries like not built and amount under 75,000.

Understand how multi-column (composite) indexes on PostgreSQL speed up queries by covering multiple columns in one index, with leading column selectivity and careful column ordering.

Analyze how null values affect B-tree index behavior, including placement, query plan impact, and options such as alternate indexes, column order, partial indexes, and default values to optimize performance.

Create covering indexes by including additional columns such as tid and t balance with the bid key, enabling index-only scans for faster queries and avoiding table lookups.

Demonstrates PostgreSQL index monitoring with queries to list indexes, check DDL, assess index usage, and identify unused or duplicate indexes; shows size reporting for tables and indexes.

Explore how PostgreSQL uses table and column statistics to guide query plans, including autovacuum analyze, the pg_statistics table and pg_stats view, and extended statistics for cross-column correlation.

Explore data distribution statistics and extended statistics in PostgreSQL, including pg_statistic, pg_stats, and correlation, with a practical demo on creating extended statistics to improve query planning.

Explore query tuning by rewriting subqueries to joins to avoid in operator bottlenecks, compare plans with explain analyze, and boost performance via merge joins and indexing.

Apply query tuning best practices by selecting only the needed columns, indexing order by columns, avoiding wildcards, and using limits to minimize disk I/O.

Explore error reporting and logging in PostgreSQL, covering the logging collector, log file naming, when to log, and what to log, with PG Badger for reports.

Discover PG Badger, an open source Perl-based log analyzer for PostgreSQL that auto detects log formats, produces reports with charts on slow and frequent queries, hourly stats, and pgbouncer data.

Demonstrates installing PG Badger, configuring logs, and generating HTML, JSON, or CSV reports. Learn to filter errors, use last pass and incremental options, and schedule cron-driven reports.

Analyze PG Badger's report to surface global statistics, connections, sessions, and query patterns for troubleshooting PostgreSQL performance, including checkpoints, vacuum, temporary files, and per database, per user, and per host.

Enable the pg_stat_statements extension on a per-database basis to track, monitor, and report SQL statement statistics, and configure max, track, and I/O timing.

Install and configure the pkg_stat_statements extension in PostgreSQL, including adding to shared preload libraries, to monitor queries, long-running, CPU and IO usage, memory, cache hits, and statistics reset.

A DBA learns to monitor server performance with Windows tools—Task Manager, Resource Monitor, and Perfmon—to diagnose CPU, memory, disk, and network bottlenecks and engage the system administrator when needed.

Discover Linux server monitoring with inbuilt commands like top, vmstat, iostat, and iotop to track CPU, memory, swap, and disk I/O while identifying resource bottlenecks for PostgreSQL DBAs.