2

In PostgreSQL 9.3.10 (Ubuntu 14.04 OS), I have a table called groupseen whose size is as follows:

              Table               |  Size   | External Size 
----------------------------------+---------+---------------
          links_groupseen         | 394 MB  | 362 MB

I understand that External Size is the size that related objects of this table (like indices) take.

Upon issuing the command truncate links_groupseen;, only 394 MB disk space was released (I checked right after, via df -h). My two questions are:

  1. What can I do to get rid of the space taken up by this table's indices (External Size) as well? Shouldn't truncate have taken care of everything?

  2. I was using the following command to check the size of this table. Even though External Size hasn't yet been released, using this command now doesn't show the outstanding 362 MB. How do I monitor the outstanding External Size related to this table?

    SELECT
       relname as "Table",
       pg_size_pretty(pg_total_relation_size(relid)) As "Size",
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
     FROM pg_catalog.pg_statio_user_tables
     ORDER BY pg_total_relation_size(relid) DESC;
    

In case required, here's how the table looks like:

                            Table "public.links_groupseen"
     Column     |           Type           |                          Modifiers                           
----------------+--------------------------+--------------------------------------------------------------
 id             | integer                  | not null default nextval('links_groupseen_id_seq'::regclass)
 seen_user_id   | integer                  | not null
 seen_at        | timestamp with time zone | not null
 which_reply_id | integer                  | not null
Indexes:
    "links_groupseen_pkey" PRIMARY KEY, btree (id)
    "links_groupseen_seen_user_id" btree (seen_user_id)
    "links_groupseen_which_reply_id" btree (which_reply_id)
Foreign-key constraints:
    "links_groupseen_seen_user_id_fkey" FOREIGN KEY (seen_user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_groupseen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_reply(id) DEFERRABLE INITIALLY DEFERRED

Note: this SO answer doesn't solve my problem because the OP in the question stated how disk space wasn't released for him at all. In my case, the subtlety is that only External Size is outstanding.

  • Possible duplicate of PostgreSQL: Disk space not released after TRUNCATE – Evan Carroll Mar 16 '17 at 6:54
  • Did you vacuum full? – Evan Carroll Mar 16 '17 at 7:00
  • @EvanCarroll: Nope, because I don't need to do I? You yourself told me a couple of months ago that TRUNCATE removes the entire content of the table immediately, without requiring a subsequent VACUUM or VACUUM FULL to reclaim the now-unused disk space. Source: dba.stackexchange.com/questions/160760/… – Hassan Baig Mar 16 '17 at 7:04
  • I don't think you need it, I'm just wondering if you have the backend xid open and if that catches it, no idea. restarting the service should work too not sure what's keeping the file open or why your os isn't reaping it. – Evan Carroll Mar 16 '17 at 7:07
  • Note that while I don't think it's needed, I would be interested to see what VACUUM FULL VERBOSE says if anything when it tries to clean up the relation – Evan Carroll Mar 16 '17 at 7:12
2

I think with a truncate even without a VACUUM FULL the space is released back to the OS. My guess is per the other question that you've got a backend process holding the underlying file open.

  1. Find out what the file is. SELECT pg_relation_filepath('links_groupseen');
  2. Find the data directory SHOW data_directory;
  3. Run truncate.
  4. See what's holding it open. lsof $data_directory/$relation_filepath

I think the most likely bet is something is holding it open if not, we'll have to know whether or not Pg is deleting the underlying relation_filepath before and after the truncate command and if anything has that file open (if it exists). You may have to run lsof before the truncate too.

  • 1
    1 yields base/30137/32955673. 2 yields /var/lib/postgresql/9.3/main. I ran lsof /var/lib/postgresql/9.3/main/base/30137/32955673 before and after running truncate links_groupseen;. Both times, there wasn't any output. Guess I'll next try VACUUM FULL VERBOSE – Hassan Baig Mar 16 '17 at 7:19
  • Hmm, so doing vacuum full verbose links_groupseen; just gave me INFO: vacuuming "public.links_groupseen" INFO: "links_groupseen": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. CPU 0.00s/0.00u sec elapsed 0.01 sec. VACUUM – Hassan Baig Mar 16 '17 at 7:28
  • @HassanBaig if you only truncated the file, it's possible all of those are pointing to the new file, and not the old one. – Evan Carroll Mar 16 '17 at 18:19
  • What's the best way to retrieve the current size of these incidences? (so we can be sure they still exist). This was actually part of my question as well. – Hassan Baig Mar 17 '17 at 5:52
0

The 394 MB is the total size, inclusive of external items like indexes and toast tables. If 394 MB is what you saw getting released, then that is all there is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.