Postgres 12.4
I was directed in slack to mention here that we're being impacted by Postgres leaving orphaned pages in /base/<db> after a crash while a CREATE TABLE is being run in transaction.
The issue is the same as the reproduction steps listed here [1], that is:
- Start a CREATE TABLE transaction for a large table
- Terminate the process via kill -9
- Pages are left in /base that have no filenode references anymore, such that `pg_database_size()` grows to be very large while total table+index size remains constant
However in our particular case, we're using a managed/cloud Postgres server and our `CREATE TABLE` transaction was being terminated by the OOM killer. Using a managed service, we don't have filesystem access to go and clear out these orphaned pages. This caused our total db size to grow from 40GB of table+index data to 4TB on-disk (but still only 40GB of table+index data, the other ~3.95TB being orphaned CREATE TABLE pages)
I realize (per a blog post from Robert Haas [2] and from slack conversation) that this is a known issue, but was directed here from slack to just mention that we were impacted by it, and have no resolution due to not having filesystem access, and not having a method internally to Postgres to deal with these orphaned relations. (Our recourse currently is to do something like a pg_dump/pg_restore onto a clean instance in order to escape the orphaned files)
-Jason
