confusting results from pg_database_size

From: AutoVHC Dev Team <autovhcdev(at)googlemail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: confusting results from pg_database_size
Date: 2010-02-04 09:52:36
Message-ID: 008401caa57f$c8dee180$5a9ca480$@com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

As part of an testing an archive solution I've updated and deleted 3 or 4
million rows in different tables. I wanted to see how much this shrunk the
database size by running, so I ran a VACUUM FULL FREEZE ANALYZE on both
databases - this took a long time, which is ok considering the number of
rows I updated/deleted

To get the db size I ran the following:

SELECT pg_size_pretty(pg_database_size('deleted_rows_db')),
pg_size_pretty(pg_database_size('original_db'));

Obviously you'd expect the database that is minus 3 million rows to be
smaller, however it was 300Mb larger. (This is a test environment where no
one else has access to the database, both databases were restored from the
same backup. Running a count on the tables shows the rows have definitely
been deleted. - This is the second time I've done this as I considered that
I must have done something wrong the first time).

Does anyone know why the database with fewer rows is larger? how I can find
the real size?, or do something to the database to lose this bloat? (It is
as if the vacuum didn't work - though it appeared to).

Thanks in advance.

-Dan Shoubridge

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jignesh Shah 2010-02-04 10:30:30 Setting permissions to access schema and language
Previous Message Marek Lewczuk 2010-02-04 09:47:32 Re: using new Hstore in PG8.4