"No Free extents", table using all allocated space but no rows!

From: "Dolan, Sean" <sean(dot)dolan(at)lmco(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: "No Free extents", table using all allocated space but no rows!
Date: 2023-03-16 01:58:18
Message-ID: 0eda9b7d13e34cc786d30c602510f3e4@lmco.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Environment: PostGres 13 on RedHat 7.9.

I am using logical replication (publisher/subscriber) between two databases and there are times where one of our schemas gets to 100% of allocated space (No Free Extents). I went into the schema and did a \dt+ to see the amount of size being used and I could see one of the tables somehow shows 16GB, essentially the amount of allocated size. Wanting to see what is in that table, I did a simple select * from the table and it returns no rows. Doing a count(*) also returns 0 rows.

How can the table be using all that space but there is nothing "in" the table? I don't care about the data (as I am testing) so I can drop and recreate that one table. \dt+ would then show 0 bytes. Later, I will then create a subscription and then I will get a No Free Extents error again and again the table has filled up.

What can I look for?

Thank you

Responses

Browse pgsql-general by date

  From Date Subject
Next Message wangw.fnst@fujitsu.com 2023-03-16 02:43:52 RE: Support logical replication of DDLs
Previous Message Bryn Llewellyn 2023-03-16 01:41:09 Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?