The Hidden Aftermath of Database Crashes: A Deep Dive into PostgreSQL’s remove_temp_files_after_crash

When a modern database management system suffers a catastrophic failure, administrators and engineers are typically consumed by immediate triage. The primary objectives are straightforward: restore service availability, analyze server logs, identify the root cause of the crash, and ensure data integrity. However, beneath the visible chaos of restarting database services lies a silent, accumulating digital debris field.

For years, PostgreSQL database administrators operated with a blind spot regarding temporary files orphaned during unexpected shutdowns. Introduced in PostgreSQL 14, the configuration parameter remove_temp_files_after_crash was designed to mitigate this storage hazard. Yet, despite its critical function in maintaining server health, this boolean setting remains tucked away in the Developer Options section of the manual—prefaced by a stern warning that it should never be altered on production environments.

Understanding how PostgreSQL handles temporary storage, why queries fail, and what happens when a backend process dies abruptly requires a close examination of database internals, kernel interactions, and operational best practices.

Main Facts: The Anatomy of PostgreSQL Temporary Storage

To comprehend the function and necessity of remove_temp_files_after_crash, one must first understand how PostgreSQL manages operations that exceed memory boundaries.

The Role of work_mem and pgsql_tmp

Operations such as large sorts, complex hash joins, and tuplestores rely heavily on the memory allocated by the work_mem parameter. When a query’s processing demands outgrow this allocated memory buffer, PostgreSQL spills the excess data to disk. These spill files are housed within the pgsql_tmp directory inside the database cluster’s data directory.

Under normal operating conditions, the backend process that creates these temporary files is also responsible for their destruction. Cleanup occurs through one of two mechanisms:

1. Normal Query Completion: Once the query finishes executing, the backend process immediately purges the associated temporary files from disk.
2. On-Exit Callbacks: If the query fails or the session terminates cleanly, an on-exit callback triggers to clear out any remaining storage footprint.

The Catastrophic Nature of Backend Crashes

The lifecycle of a temporary file changes dramatically when a backend process crashes unexpectedly. In PostgreSQL’s architecture, the failure of a single backend process is treated as a severe system event. Because shared memory could potentially be in a corrupted state, the primary postmaster process takes a defensive stance: it issues a SIGQUIT signal to every other active child process.

When these child processes receive SIGQUIT, their internal handler—quickdie()—terminates execution immediately via the standard C library function _exit(2). Crucially, this rapid exit path bypasses all cleanup routines and on-exit callbacks.

Consequently, the moment a single backend crashes, every temporary file across the entire server is instantly orphaned. This includes session spill files, parallel query shared filesets, and the underlying storage for active temporary tables. Verification tests on PostgreSQL versions 18.6 and the 19 release candidate confirm that bystander sessions—even those holding open cursors over spilled sorts—survive a peer process’s SIGKILL with their temporary assets completely intact on disk.

Chronology: The Evolution of Crash Recovery and Orphaned Files

The management of orphaned temporary files has evolved significantly over the lifetime of the PostgreSQL project, moving from passive accumulation to automated, proactive hygiene.

The Pre-PostgreSQL 14 Era: The Accumulation Problem

Before the release of PostgreSQL 14, orphaned temporary files remained untouched on disk until the next full start of the postmaster process. During standard startup routines, the function RemovePgTempFiles() executed unconditionally, wiping out lingering files on the premise that no other processes could safely access the data directory at that stage.

However, this mechanism was frequently undermined by modern database configurations and operational realities:

• In-Place Reinitialization (restart_after_crash): When the default parameter restart_after_crash is set to on, the postmaster does not go through a full restart sequence following a crash. Instead, it reinitializes in place, bypassing the startup routine that would normally trigger RemovePgTempFiles().
• The "Debugging" Fallacy: The historical justification for leaving these files intact was that database engineers might want to inspect them for debugging purposes after a crash.

The practical outcome of this design was deeply problematic. Production servers running heavy analytical workloads—which naturally generate the largest temporary files—frequently encountered out-of-memory (OOM) conditions. When the Linux kernel’s OOM killer terminated a reporting job, tens of gigabytes of sort files were left behind. A subsequent crash doubled the storage footprint, continuing unabated until the host system exhausted its storage capacity and hit the dreaded ENOSPC (No space left on device) error.

The Introduction of PostgreSQL 14

Recognizing that these orphaned files were rarely, if ever, used for actual debugging—and were instead silently consuming terabytes of enterprise storage—core developers introduced remove_temp_files_after_crash in PostgreSQL 14.

Set to on by default, this parameter instructs the postmaster to sweep and destroy all lingering temporary files during the post-crash reinitialization phase, directly addressing the accumulation crisis that had plagued high-throughput analytical environments for decades.

Supporting Data: Visibility and Operational Impact

One of the most insidious aspects of orphaned temporary files is their absolute invisibility to standard database monitoring instrumentation.

The Monitoring Blind Spot

Database administrators typically rely on internal system catalogs and logging parameters to track resource utilization:

• pg_stat_database.temp_files and pg_stat_database.temp_bytes only record statistics transmitted by the owning backend at the exact moment of file deletion. A backend that crashes abruptly never reports its final metrics.
• The log_temp_files configuration setting similarly relies on active, healthy backends to log their temporary file creation and size thresholds.
• The built-in SQL function pg_ls_tmpdir() can inspect plain spill files within the default tablespace, but it fundamentally skips directory structures. As a result, the .fileset trees generated by parallel queries remain entirely hidden from it. Furthermore, storage utilized by temporary tables resides directly within database subdirectories rather than under pgsql_tmp.

Consequently, identifying orphaned files requires direct operating system intervention. An administrator must examine the host shell to locate files matching the pattern pgsql_tmp<PID>.<N> or directories matching pgsql_tmp<PID>.<N>.fileset where the Process ID (PID) corresponds to no active session in pg_stat_activity.

The Performance Dilemma: Single-Threaded Cleanup Stalls

While turning remove_temp_files_after_crash to on prevents disk space exhaustion, it introduces a new operational challenge: recovery latency.

During post-crash reinitialization, the postmaster executes the temporary file cleanup single-threaded while the database engine is completely offline. During the beta testing phase of PostgreSQL 14 in September 2021, cloud infrastructure engineers from AWS reported alarming findings from their large-scale database fleets. They documented instances involving hundreds of thousands—and in extreme cases, over 2.2 million—temporary files.

For customers operating at this scale, the single-threaded deletion process during startup introduced severe delays, significantly extending Recovery Time Objectives (RTO). In response, AWS engineering implemented workarounds, such as programmatically moving the pgsql_tmp directory aside before initiating server startup, thereby avoiding the blocking I/O stall.

In isolated sandbox environments running on standard ext4 filesystems, planting approximately 300,000 empty leftover files imposes a postmaster startup penalty of roughly 2.6 seconds. In production environments housing millions of fragmented, gigabyte-scale temporary files, that delay multiplies dramatically.

Official Responses and Engineering Consensus

The database engineering community, including core PostgreSQL contributors and cloud platform operators, maintains a clear and unified consensus regarding the management of this parameter.

The Danger of Disabling Protection

The manual explicitly categorizes remove_temp_files_after_crash under Developer Options, carrying a prominent warning against production modification. Database architects emphasize two critical realities regarding this setting:

1. The Illusions of off: Turning the parameter off does not permanently preserve orphaned files. It merely retains them until the next traditional postmaster startup, because the full startup path completely ignores this parameter and unconditionally purges temporary storage. If an administrator disables the setting to capture crashed backend files, they must manually copy the data elsewhere before any automated cluster management software or service restart occurs.
2. Shifting the Bottleneck: Disabling the parameter makes post-crash reinitialization appear fast, but it achieves this speed solely by deferring the cleanup burden to the subsequent server restart. The underlying performance stall is not removed; it is merely relocated.

Recommended Best Practices

Rather than manipulating remove_temp_files_after_crash to mask underlying performance issues, engineers recommend addressing the root causes of excessive spilling:

• Evaluate Memory Allocation: Millions of leftover temporary files indicate systemic memory starvation—either through an improperly sized work_mem parameter or inefficient query designs that trigger runaway hash joins and nested loops. Modern configurations should leverage features like hash_mem_multiplier to optimize memory scaling.
• Emergency Intervention: If a production server fails to start due to a massive backlog of temporary files delaying recovery, administrators should emulate cloud provider strategies: bypass the built-in deletion routine by moving the pgsql_tmp directory aside at the operating system level, bring the database online, and purge the accumulated data asynchronously.

Implications: Maintaining Resilient Database Architectures

The existence and function of remove_temp_files_after_crash serve as a vital reminder that database administration requires a holistic understanding of the interaction between software logic and underlying operating system infrastructure.

Summary of Operational Guidelines

• Leave the Default Enabled: For the vast majority of deployments, keeping remove_temp_files_after_crash = on is essential for safeguarding disk volumes against catastrophic ENOSPC failures during cascading crashes.
• Isolate for Debugging Only: If a specific, highly reproducible crash requires forensic analysis of spilled sort data, administrators should temporarily set the parameter to off, reload the configuration, reproduce the failure under controlled conditions, immediately extract the target files to a safe location, and restore the parameter to its default state.
• Treat Symptoms, Not Just Parameters: Persistent accumulations of temporary files signal underlying query optimization and resource allocation flaws. Tuning memory parameters provides a sustainable long-term defense against the conditions that generate massive temporary file backlogs in the first place.

By respecting the architectural warnings surrounding PostgreSQL’s developer settings and maintaining vigilance over system storage health, database engineers can ensure both rapid disaster recovery and long-term infrastructure stability.