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:
-
What can I do to get rid of the space taken up by this table's indices (
External Size
) as well? Shouldn'ttruncate
have taken care of everything? -
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 outstanding362 MB
. How do I monitor the outstandingExternal 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.
Best Answer
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.SELECT pg_relation_filepath('links_groupseen');
SHOW data_directory;
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 runlsof
before the truncate too.