From: Dolan, Sean <sean.dolan@lmco.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: "No Free extents", table using all allocated space but no rows!
Date: Thu, 16 Mar 2023 01:58:18 +0000
Message-ID: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: sean.dolan@lmco.com
Subject: Re: "No Free extents", table using all allocated space but no rows!
In-Reply-To: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com>
References: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox