Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is there a good reason you're running a version of PostgreSQL that's missing 4 YEARS of updates? Note that it's entirely possible you're suffering from a bug here when it comes to reclaiming space from a temp table. Either way, please update to pg 8.2.22 now, and start planning to upgrade to a version that's still receiving support soon. –  Scott Marlowe Sep 26 '11 at 19:51
add comment

1 Answer

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)

share|improve this answer
    
postgresql.org/docs/current/interactive/routine-vacuuming.html does say that disk space is freed. –  DrColossos Sep 7 '11 at 13:11
    
Hmm, I only saw the "However, it will not return the space to the operating system" sentence. But apparently that's not true for vacuum full. Thanks for pointing that out. –  a_horse_with_no_name Sep 7 '11 at 13:20
    
@a_horse_with_no_name: In more recent versions of Postgres (I think since 8.3) you can assign a special tablespace for temporary tables - Can you point me to the exact documentation link/resource on this feature? –  Gnanam Sep 7 '11 at 13:41
    
@Gnanam: See my edit –  a_horse_with_no_name Sep 7 '11 at 13:49
    
+1 but postgres support is sometimes available from a distro after it ceases from postgres - RHEL is a case in point –  Jack Douglas Sep 7 '11 at 15:29
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.