It sounds like you may have uncontrolled index growth. This happens when
you update indexes by adding information to one end, and removing it from
the other. The index becomes lop sided (they're btrees) and begins to
grow.
you can either reindex the indexes, drop and recreate them, or wait for
7.4 which has a fix for that in it. (Thanks to Tom.)
On Wed, 18 Jun 2003, Gaffga, Stefan wrote:
> Hello!
>
> We use the PostgreSQL Version 7.2.2 bundled with SuSE Linux 8.2
> Professional.
> Our database server contains 20 databases, many smaller and some larger
> ones.
>
> 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
> tab"
> and then we fill them again using the COPY-command. The data that we insert
> using
> 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
> dump
> 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
> Stefan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
