What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table?

We're using PostgreSQL v8.2.3. Ours is a web-based application and we're using pgpool-II v 2.0.1 purely for connection pooling (we
don't use other features of pgpool like Replication, Load Balancing, etc.).

Recently, in our Production server there was a drastic and unexpected growth in
database disk space. In just 2 days, database size has grown from 6 GB to
14 GB.

I then ran the following query to find the size of the top 20 biggest
relation in the database:

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class
C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN
('pg_catalog') ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;

I didn't find any issues here. Even I could say that the sum of
"total_size" of the above command is less than the size occupied by the
database itself. I'm using the following command to find the size of
database:

select oid, datname, pg_database_size(datname) as actualsize,
pg_size_pretty(pg_database_size(datname)) as size from pg_database order by
datname;

I also used to physically check the database size occupied using the following
command:

du -sh /usr/local/pgsql/data/base/2663326

I then physically listed the file size in descending order from the location
"/usr/local/pgsql/data/base/2663326". Here, "2663326" is the OID of my
database.

```
[root@dbserver 2663326]# ll -lhS |head -15
total 14G
-rw------- 1 postgres postgres 1.0G Sep 6 15:03 1508904
-rw------- 1 postgres postgres 1.0G Sep 2 21:16 1924478.10
-rw------- 1 postgres postgres 1.0G Sep 2 21:17 1924478.2
-rw------- 1 postgres postgres 1.0G Sep 2 21:19 1924478.3
-rw------- 1 postgres postgres 1.0G Sep 2 21:17 1924478.4
-rw------- 1 postgres postgres 1.0G Sep 2 21:18 1924478.5
-rw------- 1 postgres postgres 1.0G Sep 2 21:20 1924478.6
-rw------- 1 postgres postgres 1.0G Sep 2 21:20 1924478.7
-rw------- 1 postgres postgres 1.

In more recent versions of Postgres (I think since 8.3) you can assign a special tablespace for temporary tables which might help you. This is documented here:

http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-TEMP-TABLESPACES

Given the fact that 8.2 will be de-supported at the end of the year it might be a good idea to upgrade. There have been numerous enhancements to VACUUM and the handling of temporary files since 8.2 so you might benefit from those.

Edit:

The reason why I think this (separate tablespace) could help you, is that you can simply drop and recreate the tablespace (files) to reclaim the spaces occupied.

But then I'd assume that due to all the improvements that were implemented in the last 5 years the current version might release the space without any further action from your side (especially because VACUUM FULL has been completely rewritten in 9.0)

StackExchange Database Administrators Q#5475, answer score: 3