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.
