Temporary Files Triage Runbook¶

This page defines the temporary files triage runbook that pg-logstats enables for humans and AI agents.

Use this workflow when PostgreSQL logs contain temporary file events or the database is under disk pressure from sorts, hashes, or wide intermediate results. The first pass is offline and log-backed: pg-logstats correlates temp file messages with nearby statements by process ID and ranks the query families responsible for disk writes. If live database access is configured and inspect reports log_backed_and_live, the report can include bounded follow-up actions for database counters, pg_stat_statements, or execution plans.

The Agent Triage Runbook¶

When database alerts or logs indicate disk pressure from temporary files, the runbook isolates the likely query family first, then optionally expands into live verification:

sequenceDiagram
    participant Agent as AI Agent (pg-logstats)
    participant Logs as PostgreSQL Logs
    participant DB as Live Database (SQL)
    participant DBA as Database Administrator (DBA)

    rect rgb(20, 20, 30)
        Note over Agent, Logs: Phase 1: Offline Correlation & Attribution
        Agent->>Logs: Parse temp file events & correlate via PID
        Logs-->>Agent: Returns correlated Query Family & SQL statement
    end

    rect rgb(30, 20, 20)
        Note over Agent, DB: Optional Live Verification
        Agent->>DB: Query database counters & temp block activity
        DB-->>Agent: Confirms database-wide disk write pressure
        Agent->>DB: Run EXPLAIN / EXPLAIN ANALYZE on query family
        DB-->>Agent: Returns query execution plan (with buffer statistics)
    end

    rect rgb(20, 30, 20)
        Note over Agent, DBA: DBA Handoff
        Agent->>Agent: Derive plan insights (disk spill / temp buffers)
        Agent->>DBA: Suggest fixes (index sort, reduce width, local work_mem)
    end

Phase 1: Offline Correlation & Attribution (Log-Backed)¶

PostgreSQL logs temporary file creation on a separate line (e.g., LOG: temporary file: path "...", size ... bytes) which does not contain the query text. * PID Correlation: The agent matches the database process ID (PID) of the temp file event and searches nearby log events to attribute the disk pressure to a specific query statement. * Query Family Mapping: The matched query is normalized into a stable query_family_id so the agent can rank and track database-wide temp file volume by pattern.

Phase 2: Optional Live Statistics Verification¶

If the database connection is available (log_backed_and_live mode), the agent queries system views to verify if the disk spill is an ongoing or database-wide concern: * temp_file.pg_stat_database.temp_counters: Verifies database-level cumulative temp file statistics in pg_stat_database. * temp_file.pg_stat_statements.temp_blocks: Queries pg_stat_statements to identify the heaviest queries writing temporary blocks.

Phase 3: Optional Plan Check (EXPLAIN / EXPLAIN ANALYZE)¶

The agent runs query plan verification next: * EXPLAIN (temp_file.explain): Safely retrieves the execution plan without running the query to verify if the planner anticipates a sort or hash spill. * EXPLAIN ANALYZE (temp_file.explain_analyze): Runs the query with buffer statistics to verify actual temp buffers written under execution.

Derived Insights¶

When the agent receives the query plan from a run_sql action, it parses the execution plan text and extracts one of the following structured insights:

query_plan_disk_spill_detected (High Confidence): Parsed from lines containing external merge or Disk: <size>. It confirms that the planner had to spill pages to disk during sorting or hashing.
explain_analyze_temp_buffers (High Confidence): Parsed from buffer lines containing temp read=<N> temp written=<M>. It confirms that temporary buffers were read/written to disk during execution.
query_plan_no_disk_spill (Medium Confidence): Emitted when a plan is successfully retrieved but shows no disk spill. This tells the agent that the query parameters or current database size do not trigger a disk sort in this specific state/environment.

Agent-Suggested DBA Remedial Actions¶

Once the agent completes the diagnostic loop and confirms a temp file issue, it terminates its live exploration branch and hands off three remedial actions to the DBA:

1. Create B-Tree Index¶

Action ID: remedial.create_sort_index
Label: DBA Recommendation: Create B-Tree index on sort/group columns
Runbook: The agent advises creating a B-Tree index on the sorting (ORDER BY) or grouping (GROUP BY) columns. This allows PostgreSQL to scan the index in-order, completely avoiding the dynamic sort node and reducing temp file writes to 0 bytes.

2. Select Fewer Columns¶

Action ID: remedial.reduce_projection_width
Label: Developer Recommendation: Select fewer columns to narrow row width
Runbook: The agent advises developers to avoid SELECT * and retrieve only the exact columns needed. Sorting narrow rows requires significantly less memory, which helps the sort fit entirely within the session's memory buffer.

3. Adjust Session `work_mem` Locally¶

Action ID: remedial.optimize_work_mem
Label: DBA Recommendation: Adjust session work_mem locally
Runbook: The agent advises setting a local, session-level memory override (e.g. SET LOCAL work_mem = '64MB';) before executing this specific query, rather than raising the global work_mem parameter (which increases risk of memory saturation/OOMs under high concurrency).

Safety & Audit Policies¶

[!IMPORTANT] The agent is restricted by the gateway policy. The agent cannot run arbitrary SQL or modify schema/data.

Risk & Verdict Restrictions¶

No Arbitrary SQL: The agent can only request queries by selecting a structured action_id.
Risk Classifications:
Standard EXPLAIN (ExplainWithoutAnalyze class) is classified as Safe and is allowed.
EXPLAIN (ANALYZE, BUFFERS) (ExplainAnalyze class) actually runs the query, carrying a risk of side-effects or heavy database load. It is classified as Bounded risk and is blocked whenever the safety verdict or configuration does not permit that action class.