What are all the reasons/possibilities of failure to free-up disk space occupied by TEMPORARY table? - Mailing list pgsql-admin

Hi,

We're using PostgreSQL v8.2.3.  Ours is a web-based application, language is
Java 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 an 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 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.0G Sep  2 21:14 1924478.8
-rw-------  1 postgres postgres  1.0G Sep  2 21:19 1924478.9
-rw-------  1 postgres postgres  876M Sep  6 15:02 1508614
-rw-------  1 postgres postgres  615M Sep  6 15:03 1508904.1
-rw-------  1 postgres postgres  531M Sep  2 21:20 1924478.11
-rw-------  1 postgres postgres  235M Sep  6 15:02 1510463

Though these files are not human-readable, from whatever I was able to read
from the file, I found that the top 10 files created are related to a
particular complex application report.   In this complex report, we're
creating temporary table using "CREATE TEMP TABLE MY_TEMP_TABLE(col1, col2,
...)", 5 columns in this temp table are indexed and it's being heavily used.
Though temporary tables are automatically dropped at the end of a session,
I'm finding that the disk space occupied by these temporary tables are not
being freed-up.  As you can also see, some file names are numbered with
decimal places (1924478.2, 1924478.3, etc.) with a maximum file size of 1
GB.  Particularly, these type of files are related to this complex report
that makes use of temporary tables.

I can also confirm that my temporary tables are not listed from the
following query:
select pn.nspname, pc.relname, pc.relfilenode from pg_class pc, pg_namespace
pn where pc.relnamespace = pn.oid and pc.relname ilike 'my_temp_table';

NOTE: Auto vacuum daemon is already running in the server.  Even a manual
VACUUM FULL ANALYZE, followed by REINDEX command is not able to reclaim the
lost disk space.  Only when we exported and imported, the database size
comes back to the original 6 GB size.

So, based on my observations, it appears that for some reasons the disk
space occupied by the temporary table is not being released properly.

What are all the reasons/possibilities of failure to free-up disk space
occupied by TEMPORARY table?   How do I fix/handle in this situation?

Regards,
Gnanam