Fix Postgres Autovacuum Found Orphan Temp Table (2026)

Postgres is failing to clean up temporary tables it created during the execution of a query, leading to disk space exhaustion.

The most common culprit is a PostgreSQL server that crashed or was abruptly killed mid-query. When this happens, the temporary tables created for that query are left behind, and the autovacuum process, which is supposed to clean them up, can’t always identify them as orphaned.

Cause 1: Server Crash or Kill

Diagnosis: Check the PostgreSQL logs for FATAL: terminating connection because of crash of backend process or similar messages. Also, look for the presence of temporary table files in your PostgreSQL data directory, typically under base/<db_oid>/. You can often identify these by their large size and names that don’t correspond to regular table OIDs.
Fix: Restart the PostgreSQL server. This will usually prompt a cleanup of orphaned temporary files. If not, you may need to manually remove them (with extreme caution, ensure the server is down first).
```
# On the server where Postgres is running
sudo systemctl restart postgresql
```
Why it works: A clean restart of PostgreSQL triggers a recovery process that identifies and discards unreferenced temporary files.

Cause 2: Long-Running Queries Holding Locks

Diagnosis: If a temporary table is created by a long-running query that hasn’t completed, autovacuum might not yet consider it "orphaned." Check for long-running queries using pg_stat_activity.
```
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start;
```
Fix: Identify and terminate the offending long-running query if it’s safe to do so.
```
SELECT pg_terminate_backend(pid); -- Replace pid with the actual process ID
```
Why it works: Completing the query releases the resources, including temporary tables, allowing autovacuum to clean them up.

Cause 3: Insufficient max_connections

Diagnosis: If your max_connections setting is too low and you’re hitting the limit, new connections (including those needed for autovacuum to check temporary tables) can be refused, or existing processes might be killed unexpectedly. Check pg_settings for max_connections and pg_stat_activity for the current number of connections.
```
SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;
```

Fix: Increase max_connections in postgresql.conf and reload the configuration.

# postgresql.conf
max_connections = 200 # Example: increased from 100

# On the server
sudo systemctl reload postgresql

Why it works: Providing enough connection slots ensures that autovacuum can perform its maintenance tasks without being blocked by connection limits.

Cause 4: Disk Space Exhaustion (The Symptom)

Diagnosis: This is what you’re seeing. The immediate symptom is likely ERROR: could not write to file "base/<db_oid>/<temp_table_file_name>": No space left on device. Check disk usage on the server.
```
df -h
```
Fix: Free up disk space by removing unneeded files, or expand your storage. Once space is available, a server restart (as in Cause 1) will often clear out the temp files.
```
# Example: remove old logs if they are taking up space
sudo rm -rf /var/log/postgresql/*.gz
```
Why it works: Autovacuum needs disk space to operate and to write its own temporary files. Freeing up space allows it to function.

Cause 5: Autovacuum Daemon Not Running or Misconfigured

Diagnosis: Verify that the autovacuum launcher process is running.
```
# On Linux, check systemd status
sudo systemctl status postgresql
```
Also, check postgresql.conf for settings like autovacuum = on and autovacuum_max_workers.
Fix: Ensure autovacuum = on in postgresql.conf and restart or reload PostgreSQL. If autovacuum_max_workers is too low, consider increasing it if you have sufficient CPU resources.
```
# postgresql.conf
autovacuum = on
autovacuum_max_workers = 5 # Example
```
```
# On the server
sudo systemctl restart postgresql
```
Why it works: The autovacuum daemon is responsible for launching worker processes that perform vacuuming and analyze operations, including cleaning up temporary files.

Cause 6: Custom Application Logic

Diagnosis: Some applications might create temporary tables and then explicitly drop them, but if there’s an error in that logic, or if the application itself crashes, the temp tables can be left behind. Review the application code that interacts with temporary tables.
Fix: Correct the application logic to ensure temporary tables are always cleaned up, even in error conditions (e.g., using try...finally blocks or equivalent).
Why it works: Robust application code guarantees that temporary resources are released properly, preventing orphaned files.

Cause 7: Bug in PostgreSQL Version

Diagnosis: If you’re on a very old or a specific minor version of PostgreSQL, there might be known bugs related to temporary table cleanup. Check the PostgreSQL release notes for your version.

Fix: Upgrade to a more recent, stable PostgreSQL version.

# Example: Update package on Debian/Ubuntu
sudo apt update && sudo apt upgrade postgresql

Why it works: Newer versions of PostgreSQL contain bug fixes that resolve issues with resource management and cleanup.

After fixing these issues and restarting PostgreSQL, you might encounter errors related to WAL (Write-Ahead Log) archiving if it was also misconfigured or had issues due to disk space.