Skip site navigation (1) Skip section navigation (2)

Plz help: PostgreSQL takes too much disk-space

From: "Gaffga, Stefan" <SGaffga(at)ElectronicPartner(dot)de>
To: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>
Subject: Plz help: PostgreSQL takes too much disk-space
Date: 2003-06-18 12:56:00
Message-ID: A16CCF0F0DA1D5119FFF000255A0CDB7011231C2@NT-EXCHANGE (view raw, whole thread or download thread mbox)
Lists: pgsql-admin

We use the PostgreSQL Version 7.2.2 bundled with SuSE Linux 8.2
Our database server contains 20 databases, many smaller and some larger

10 of these databases contain most of the data. These data need to be 
updated every day. We delete all rows of the each table using "delete from
and then we fill them again using the COPY-command. The data that we insert
the COPY command is converted from a text file we receive every evening.

The complete operation takes about 1 hour. 

After we updated all of our tables, we do a "vacuumdb --all --full".

Now the problem: We encountered that the database is growing far more
rapidly than
the data! 

Our database currently consumes 20GB (!) of disk space! 

The strange effect is: When we do a "pg_dumpall > db.dmp" and insert this
into a clean DB-server environment, the consumed disk space is 2.1 GB!!
So you see, that we do not have that much data to really fill anything
around 20GB...

When I do a "select sum(relpages) from pg_class where relname not like
'pg_%';" to get
the used pages from all non-system tables the result is: 849034.
As we did not change the block size, we get 849034 * 8k = 6792272 k = 6,5 GB

Then for the system tables: "select sum(relpages) from pg_class where
relname like 'pg_%';"
The result is: 267 => 267 * 8k = 2 MB

As you see, PostgreSQL thinks it is using 6,5 GB, but "du -sh" says 20GB ...

Any help / hints / links / ideas are VERY appreciated!

Thank you all in advance


pgsql-admin by date

Next:From: scott.marloweDate: 2003-06-18 13:32:32
Subject: Re: Plz help: PostgreSQL takes too much disk-space
Previous:From: Peter EisentrautDate: 2003-06-18 12:23:29
Subject: Re: Create Rule/trigger

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group