Re: Orphaned Files in PostgreSQL

Hi All,

I am revisiting this thread to propose a possible fix for this known issue.

Issue:
=====
When PostgreSQL undergoes an unclean shutdown while a transaction is
creating a relation and loading a large amount of data into it, the
transaction is considered aborted during recovery. However, the
relation files created by that transaction can remain in the data
directory, occupying disk space without any visible catalog entry.

This causes several problems:

1) It can fill up the disk space and take the server down.
2) It can increase backup size and backup duration.
3) It adds unnecessary file system scanning, syncing, and maintenance overhead.
4) It forces manual work to tell real orphaned files apart from valid ones.
5) It wastes disk space until an admin finds and removes the files.

At present, when a transaction creates WAL-logged relation storage,
PostgreSQL adds it to a backend-local "pending delete" list, so the
files get cleaned up if the transaction aborts normally. The problem
is that this list only lives in memory. If the server crashes
mid-transaction, that list is lost and since WAL recovery replays
actions forward (redo) rather than undoing them, there may be no abort
record left to trigger cleanup of those files.

Proposed solution:
==============
To address this, I propose maintaining a durable relation-creation
marker for every transactionally created permanent relation.

The marker would be created under a new pg_relcreate directory and
store the following information:

1) The complete RelFileLocator.
2) The XID of the transaction that created the storage.
3) A marker format version.
4) A magic value identifying the file type.
5) A CRC protecting the marker contents.

typedef struct RelationCreateMarker
{
uint32 magic;
uint32 version;
RelFileLocator rlocator;
TransactionId xid;
pg_crc32c crc;
} RelationCreateMarker;

The marker filename would be derived from the tablespace OID, database
OID, and relfilenumber. For example it would be something like:
<tblspc_oid>_<dbid>_<relfilenode>. This marker is created, used, and
removed at different points in a relation's lifecycle - creation,
commit, abort, and recovery. Let's look at how each step would work.

A) Relation creation would proceed in this order:
----------------------------------------------------------------
For every persistent relation that needs storage, would go through
following steps inside RelationCreateStorage():

1) Insert an XLOG_SMGR_CREATE record marked as requiring a creation marker.
2) Create and fsync the marker file.
3) Create the physical relation file.
4) Register the existing delete-on-abort pending-delete entry.

This ordering guarantees that relation storage cannot become durable
without either a durable marker or WAL capable of reconstructing that
marker.

B) WAL replay:
--------------------
The XLOG_SMGR_CREATE payload would include a flag indicating whether a
marker is required. The creating XID remains in the common WAL record
header.

During redo, smgr_redo() would:

1) Obtain the XID from the WAL record header.
2) Recreate or validate the marker.
3) Recreate the relation fork as it does today.

If an identical marker already exists, redo accepts it. A conflicting
or corrupt marker may cause the recovery to fail rather than overwrite
unresolved cleanup state.

C) Normal commit:
-------------------------
For a committing transaction:

1) Force the transaction's commit WAL record to local durable storage.
2) Keep the relation.
3) Remove the marker durably when processing the "pending-delete" list.

Forcing synchronous commit is important here. Otherwise, PostgreSQL
could remove the marker and then crash before the asynchronous commit
record reaches disk. Recovery would subsequently consider the
transaction aborted, but no marker would remain to identify its
relation files.

The end result of the successful commit is as follows:

relation file: present
catalog row: committed
marker: removed

One subtle but important case: if the server crashes after the commit
record is durable but before the marker gets removed, that's fine.
When PostgreSQL restarts, its recovery process sees that this
transaction committed, keeps the table file, and simply cleans up the
now-unnecessary leftover marker.

D) Normal abort:
----------------------
For a transaction that aborted:

The existing pending-delete mechanism remains responsible for deleting
relation storage. It calls mdunlink() which truncates the main-fork to
make it a tombstone file and lets the next checkpoint remove the
tombstoned file and the marker file. The subsequent checkpoint unlinks
both the relation file and the marker file.

The end result is:

relation file: removed
catalog row: aborted / invisible
marker: removed

E) End-of-recovery cleanup:
-------------------------------------
After WAL replay and prepared-transaction recovery have completed,
PostgreSQL scans pg_relcreate.

For every valid marker:

1) If the creating XID committed, retain the relation and remove the
stale marker.
2) If the XID belongs to a prepared transaction, retain both the
relation and marker.
3) Otherwise, treat the transaction as crash-aborted and remove all
relation forks and the marker.

If PostgreSQL crashes after deleting the relation but before deleting
the marker, the next recovery attempts the relation deletion again and
then removes the marker.

F) Prepared transactions:
----------------------------------
Markers belonging to prepared transactions must survive recovery.

1) COMMIT PREPARED retains the relation and removes its marker after
the commit record is durable.
2) ROLLBACK PREPARED deletes the relation through the existing
two-phase pending-delete information, with marker removal following
physical tombstone cleanup.

G) Subtransactions:
---------------------------
1) On a subtransaction commit, its pending-delete entry transfers to
the parent transaction, so the marker remains until the top-level
transaction finishes.
2) On subtransaction abort, relation deletion starts immediately, but
the marker is retained until checkpoint processing removes the
relation tombstone.

Performance considerations:
---------------------------------------
The principal cost is additional I/O for transactional permanent
relation creation:

1) Writing and fsyncing a small marker file.
2) Fsyncing the marker directory.
3) Forcing local synchronous commit for transactions that created
marked storage.

This affects operations that create new permanent relfilenumbers, such
as CREATE TABLE, CREATE INDEX, REINDEX, VACUUM FULL, CLUSTER, and some
relation rewrites. Ordinary DML does not incur this cost.

The solution described above is implemented in the attached patch,
please take a look and share your feedback.

--
With Regards,
Ashutosh Sharma.

On Tue, Feb 18, 2025 at 4:16 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
>
> Hi All,
>
> While investigating one of our customer issues, we discovered several
> orphaned data files on the disk that do not have corresponding entries
> in the pg_class table. Upon further analysis, we identified specific
> scenarios in PostgreSQL where this issue can occur. One such scenario
> is as follows:
>
> Consider a situation where a table is being created within a
> transaction, and data is being loaded into it. If PostgreSQL
> unexpectedly crashes while the transaction is still in progress, an
> orphaned file may be left behind on the disk. In cases where multiple
> such transactions occur, this can lead to the accumulation of numerous
> orphaned files, resulting in significant disk space consumption.
> Unfortunately, these files are not cleared during PostgreSQL's restart
> process.
>
> We have discussed this issue internally, and one proposed solution
> involves adding a marker file to the disk for any table created within
> a transaction, immediately upon its creation. This marker file would
> then be removed during the commit process. If the transaction is
> aborted due to a server crash, the marker file and the corresponding
> disk file would be cleared at the end of the recovery process during
> server startup.
>
> I would appreciate your thoughts on this solution. Should you have any
> suggestions or alternative approaches, I would be grateful to hear
> them.
>
> Additionally, I am unsure if this issue has already been reported or
> if it is currently being addressed. If that is the case, I would be
> grateful if you could point me to the relevant discussion thread so I
> can follow the progress and contribute if needed.
>
> Thank you for your time and assistance.
>
> --
> With Regards,
> Ashutosh Sharma.