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.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 getting listed from the following query (which shows that as per system catalog tables, it's been dropped):
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 at some point of time/context, for some unknown reasons, the disk space occupied by the temporary table is not being released properly by PostgreSQL server.
What are all the reasons/possibilities that it may fail to free-up disk space occupied by TEMPORARY table? How do I fix/handle in this situation?
