postgresql version is 10

\l+ showing db size to be 7228 GB

i checked all tables sizes those comes out to be 60 GB only using

SELECT relname AS "relation", 
       pg_size_pretty(pg_relation_size(C.oid)) AS "size"  
FROM pg_class C 
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)  
WHERE nspname NOT IN ('pg_catalog', 'information_schema')  
ORDER BY pg_relation_size(C.oid) DESC 

Then i went to see filesystem in which my database base directory is:


I see around 70k

t20_909090 files in this directory which are taking the space.

I have checked in my local postgres db instance these files are created only after i create a new temp table and got deleted after i close the session.

files in base directory from my local db:

    pwd ; ls  | grep  t

from my local postgres db the temp files and above file nodes:

    tertw=# SELECT c.oid, 
n.nspname as SchemaName
,c.relname as RelationName
,CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as RelationType
,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner               
,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n               
                ON n.oid = c.relnamespace
WHERE  c.relkind IN ('r','s') 
AND  (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC
  oid   | schemaname | relationname | relationtype | relationowner | relationsize 
 150704 | pg_temp_3  | tesst        | table        | postgres      | 0 bytes
 150710 | pg_temp_3  | tesst1       | table        | postgres      | 0 bytes
 150716 | pg_temp_3  | tesst2       | table        | postgres      | 0 bytes
 150722 | pg_temp_3  | tesst3       | table        | postgres      | 0 bytes
(4 rows)

according to above obersvation i concluded that these are temp tables but in prodcution system in pg_class these oid (e.g.: i checked 909090 as oid from t20_909090 ) are not present

how do I check what files they are or how to clean them up.

Also all these files got cleaned up automatically after i restarted the postgres db.

Extension on my DB :

tertw=# select * from pg_extension ;
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
 plpgsql |       10 |           11 | f              | 1.0        |           | 
(1 row)

tertw=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

tertw=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

  • @a_horse_with_no_name i have updated the question with the extensions which are installed into my system - only plpgsql is there which dosen't looks like the one. Is there any other way i should check for the installed extension Commented Nov 20, 2020 at 13:15
  • You are correct, those are from temp tables. (it is temp files for sorting and hashing and such that are in pgsql_tmp). Did restarting the server clear up all of these, or was it only the ones you created for testing purposes that were cleared up?
    – jjanes
    Commented Nov 20, 2020 at 15:24
  • @jjanes yes those got cleaned up after restarting postgres and in my case these files are not in pgsql_tmp , question is updated with the ls output of db directory these files are present there Commented Nov 23, 2020 at 8:28


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.