PostgreSQL Shared Buffers Workshop

This repository provides an interactive, hands-on workshop environment to visualize and test PostgreSQL shared buffers internals. It is designed to demonstrate key concepts surrounding database memory management, cache utilization, Buffer Access Strategies, and eviction algorithms.

Prerequisites

• Docker
• Docker Compose
• make

Getting Started

1. Start the workshop environment and run the interactive demo:

   make demo

   Note: Initialization generates ~500MB of data (1 million rows) resulting in a longer start time (10-20s).

2. Alternatively, start the containers manually in the background:

   make up

3. Open a psql session to explore manually:

   make psql

4. Clean up the environment:

   make down

Workshop Content & Interactive Examples

The interactive demo (run_workshop.sh) guides you step-by-step through several critical concepts of PostgreSQL's shared buffers architecture:

1. Inside Shared Buffers (pg_buffercache)

The workshop initializes a shared_buffers configuration of 2GB and loads a table (large_random_data) of ~500MB.

By directly querying native PostgreSQL source views (such as pg_buffercache joined with pg_class), you can observe how PostgreSQL divides shared buffers into 8kB pages. The demo highlights the avg_usage_count, which corresponds to PostgreSQL's Clock-Sweep eviction algorithm, illustrating the difference between "cold," "warm," and "hot" cache pages.

2. Inspecting Cache Hits and Misses

We demonstrate the lifecycle of data blocks entering the cache. By executing an index-driven query with EXPLAIN (ANALYZE, BUFFERS), the demo shows:

• First Run: Incurs disk read operations (cache misses) as data is brought from the OS cache or disk into PostgreSQL's shared memory.
• Subsequent Runs: Show pure buffer hits, proving the data is explicitly pinned in the shared buffers via Hash Table lookups, avoiding costly disk I/O.

3. Buffer Access Strategy (BAS) & Sequential Scans

Normally, you might expect a ~500MB table to be fully cached in a 2GB shared buffer after a sequential scan.

However, PostgreSQL protects its working cache using the Buffer Access Strategy (BAS). The demo executes a full table scan and then uses pg_buffercache to mathematically prove that PostgreSQL allocates only a tiny 256KB Ring Buffer (exactly 32 pages) for bulk reads. This brilliant architectural design prevents pathological queries from filling up your cache with data that might only be read once.

4. Linux-level Memory Mapping (/proc/PID/smaps and /proc/PID/smaps_rollup)

As discussed in the presentation slides, PostgreSQL allocates shared_buffers as anonymous shared memory via mmap(). Because it's not backed by a real file on the virtual filesystem, the Linux kernel displays this region as an internal tmpfs object named /dev/zero (deleted). The demo utilizes PostgreSQL's COPY command to parse its own backend's /proc/self/smaps file, explicitly isolating and displaying this exact memory map and its properties (Size, Rss, Pss, etc.). It then leverages /proc/self/smaps_rollup to aggregate and display the overall total memory metrics for the entire PostgreSQL backend process.

5. Internal PostgreSQL Memory Structures (pg_backend_memory_contexts)

Complementing the OS-level smaps view, the workshop uses pg_backend_memory_contexts to inspect the connection's session-level memory consumption. As highlighted in the presentation's analysis of connection-level RAM, this directly maps to the Linux [anonymous] and [heap] allocations in smaps. Additionally, it queries pg_shmem_allocations to show how PostgreSQL internally subdivides the large OS anonymous shared memory (/dev/zero (deleted)) into Buffer Descriptors, Buffer Blocks, Lock Tables, and WAL Buffers.

6. Background Writer and Dirty Pages (pg_stat_bgwriter)

To visualize the lifecycle of memory pages, the demo simulates a write workload that updates thousands of rows, marking memory blocks as "dirty". It observes pg_stat_bgwriter (which in PG 17+ strictly tracks the bgwriter process) alongside the custom pg_buffercache queries to show how PostgreSQL initially avoids expensive disk I/O. The script then pauses execution, allowing attendees to literally watch the asynchronous bgwriter process wake up, sweep through shared buffers, and proactively write dirty pages to disk in the background.

7. Checkpointer Diagnostics (pg_stat_checkpointer)

Starting with PostgreSQL 17, checkpointer statistics are isolated in their own view. The 07_pg_stat_checkpointer.sql script explores this, helping you diagnose I/O bottlenecks and checkpoint triggers (timed vs. requested) to optimize checkpoint_timeout and max_wal_size.

8. WAL Generation Volume (pg_stat_wal)

The 08_pg_stat_wal.sql script provides insight into PostgreSQL 18's simplified WAL statistics view. It focuses on WAL records, generated bytes, and Full Page Images (FPI) to help analyze cluster write activity and WAL buffer efficiency.

9. Byte-Level I/O Monitoring (pg_stat_io)

PostgreSQL 18 brings byte-level tracking to pg_stat_io. The 09_pg_stat_io.sql script leverages these new metrics (read_bytes, write_bytes, extend_bytes), providing both aggregated per-backend I/O summaries and detailed context-specific breakdowns (e.g., normal cache access vs. bulkread operations).

10. LRU Usage Counts Analysis

The 10_lru_usage_counts.sql script provides an advanced look at shared buffers by grouping the loaded 8KB blocks by their usagecount (0 to 5). This visually demonstrates the "heat map" of your cache and helps evaluate if your shared_buffers sizing is appropriate for your active working set under the Clock-Sweep eviction algorithm.

Project Directory

• docker-compose.yml: Defines the PostgreSQL 18 node using constrained shm_size.
• config/postgresql.conf: Custom PostgreSQL configuration containing tweaked resource hints and reduced shared_buffers for clearer test results.
• scripts/init.sql: Sets up extensions and generates the 1M row test dataset.
• scripts/: Directory housing the isolated .sql example files used during the workshop execution.
  • scripts/bash/: Contains practical Bash scripts extracted from the slides to inspect Linux memory management, tmpfs configurations, Huge Pages, and NUMA distributions.
  • scripts/sql/: Contains raw SQL examples for querying native PostgreSQL 18 source views (pg_settings, pg_buffercache, pg_shmem_allocations) to analyze buffer usage, dirty pages, and NUMA node distribution.
• run_workshop.sh: The interactive bash wrapper orchestrating the visual tutorial experiences.