Re: Problems with database bloat - Mailing list pgsql-general

From Alex Turner
Subject Re: Problems with database bloat
Date
Msg-id 33c6269f0606081614m71b3cb75g83e48818419537a8@mail.gmail.com
Whole thread Raw
In response to Re: Problems with database bloat  ("Jim C. Nasby" <jnasby@pervasive.com>)
Responses Re: Problems with database bloat
List pgsql-general
Yeah - I just did a reindex, that fixed the indexes at least.

Alex

On 6/8/06, Jim C. Nasby <jnasby@pervasive.com > wrote:
On Thu, Jun 08, 2006 at 06:03:23PM -0400, Alex Turner wrote:
> I hope I'm reading this query wrong:
>
> trend=# select relname, relpages*8192/reltuples from pg_class where
> reltuples>0 order by relpages desc limit 10;
>            relname            |     ?column?
> -------------------------------+------------------
> property                      | 19935.4468376195
> result_entry_pkey             | 1611.15654062026
> result_entry                  | 1417.71707157196
> person                        | 7107.41684585612
> property_feature_pkey         | 98.7810833557521
> property_feature              | 60.2035684051268
> person_name_i                 | 3358.93641334398
> property_price_i              | 1978.89907374882
> property_mls_listing_number_i | 1923.61833274788
> property_spatial              | 1784.73493686332
> (10 rows)
>
> If I have this query right in my head, this means that the average tuple in
> the property relation is taking up 19k? and the average tuple in the
> result_entry_pkey index is take 1.5k?!
>
> Is there a way I can get the database to coalesce free space?  A vacuum
> verbose shows that I have enough entries in the free space map...

A lazy vacuum won't reclaim empty space, only a VACUUM FULL will.

If that drops the size of the relations substantially, you'll probably
want to REINDEX everything to reclaim lost space in the indexes as well.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

pgsql-general by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: Problems with database bloat
Next
From: Agent M
Date:
Subject: Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model