PostgreSQL idle sessions filled my disk space

We have an internal application using PostgreSQL as backend with application connection pooling. After few days we started seeing a peculiar issue where the disk allocated for database is getting full and application is running too slow. After restarting the application the space got released and application is working as expected.

After checking the tables which are consuming more space we found that temporary tables are consuming more space(to know more about temporary tables visit this). After checking it further, temporary tables will be dropped once the session is closed but as we are using the application connection pooling which is not managed properly causing this issue.

Before closing or establishing the connection it is recommended to execute the “DISCARD ALL” which will drop all the temporary tables or resources allocated in that session.

test_temp=# create temporary table test_emp(id int, name varchar);
 test_temp=# insert into test_emp values (1,'Viswamitra');
 test_temp=# select * from test_emp ;
  id |    name
   1 | Viswamitra
 (1 row)
 test_temp=# discard all;
 test_temp=# select * from test_emp ;
 ERROR:  relation "test_emp" does not exist
 LINE 1: select * from test_emp ;

In the tomcat connection pooling configuration (validationquery) set the command “discard all” which will release the resources occupied by the session before returning the connection back to the pool.


PostgreSQL: Documentation: 11: DISCARD

One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s