Op 01-09-11 13:31, Scott Marlowe schreef:
On Thu, Sep 1, 2011 at 4:32 AM, Rik Bellens<rik.bellens@xxxxxxxxxxxxxx> wrote:
Hello,
I have two large tables in a database, one containing original data and the
other one derived from the first table. The first table contains several
columns and indexes, while the second table has less columns and only one
index. Both tables have the same number of rows. Nevertheless, the second
table is much larger in disk size than the first one. How can this be
explained?
This is most likely due to table bloat. In PostgreSQL when you update
or delete a row, a dead version gets left behind. Vacuum eventually
comes along and reclaims the empty space to be reused. If you delete
/ update a LOT of rows at once, then you'll have a lot of dead rows
which can only be reused after vacuuming when you do more updates or
deletes later on.
A few salient questions. What version of PostgreSQL are you running?
Is autovacuum running? Do you do a LOT of bulk deletes / updates? If
you do a lot of bulk deletes on this table, and you delete everything,
can you switch to using the truncate command instead?
I use version 8.3. I see the 'autovacuum launcher process' and
'autovacuum worker process' in the process list, so I suppose autovacuum
is running.
Rows in the measurement table are added once and never deleted or
updated. Adding a row to this table triggers a function that adds a row
to the stats_count table. Normally rows are added chronologically. So
rows in the stats_count table are normally not updated either. If
however, for some reason, a measurement is added from an older time, all
rows of that device which come after this time, are updated, but I don't
think this will happen very often.
The table 'stats_count' was created in a later stage, so the first 45M
rows were added at once and chronologically. However, because the
function to initialize this table took a long time and the client
application crashed a few times, I had to restart this function several
times. Can it be that there is some trash left from running this
function several times without finishing it? Would it be a solution to
run 'VACUUM FULL' to reclaim some disk space?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
