pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (2+ messages)  latest in thread

Message-ID: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com>
Permalink:  ../0eda9b7d13e34cc786d30c602510f3e4@lmco.com/
Also on:    postgresql.org/message-id/0eda9b7d13e34cc786d30c602510f3e4@lmco.com

 · 

reply

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