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?