Обсуждение: Unreasonable size of table pg 8.2.5

Hello list,

I have a table with 135 rows and it still takes up about 360MB with
only small columns. Details below.

db=# vacuum full tbl_archive;
VACUUM
db=# select * from
pg_size_pretty(pg_total_relation_size('tbl_archive'));
  pg_size_pretty
----------------
  360 MB
(1 row)

db=# select * from pg_size_pretty(pg_relation_size('tbl_archive'));
  pg_size_pretty
----------------
  16 kB
(1 row)


Looks like we have a very bloated index.
After reindex
db=# select * from
pg_size_pretty(pg_total_relation_size('tbl_archive'));
  pg_size_pretty
----------------
80 kB
(1 row)

I thought that reindex should not be necessary in 8.2.5? This is not a
big tabel but what I can see is that we have many small updates.
Cheers,
Henke

db=# \d tbl_Archive;
                                                 Table
"public.tbl_archive"
            Column            |            Type
|                          Modifiers
-----------------------------+-----------------------------
+--------------------------------------------------------------
  pk_archive_id               | bigint                      | not null
default nextval(('archive_seq_id'::text)::regclass)
  archive_name                | character varying(255)      |
  archive_backup_type         | character(1)                |
  archive_size                | bigint                      | not null
default 0
  fk_share_id                 | bigint                      |
  archive_complete            | boolean                     | not null
default false
  fk_job_id                   | bigint                      |
  archive_date                | timestamp without time zone | not null
default now()
  archive_nmb_files           | integer                     | not null
default 0
  archive_nmb_folders         | integer                     | not null
default 0
  archive_nmb_file_exceptions | integer                     | not null
default 0
Indexes:
     "tbl_archive_pkey" PRIMARY KEY, btree (pk_archive_id)
     "tbl_archive_idx" btree (archive_complete)
     "tbl_archive_idx1" btree (fk_share_id)
     "tbl_archive_idx2" btree (fk_job_id)
Check constraints:
     "tbl_archive_chk" CHECK (archive_backup_type = 'F'::bpchar OR
archive_backup_type = 'I'::bpchar)
Foreign-key constraints:
     "tbl_archive_fk" FOREIGN KEY (fk_share_id) REFERENCES
tbl_share(pk_share_id) ON UPDATE CASCADE ON DELETE SET NULL
     "tbl_archive_fk1" FOREIGN KEY (fk_job_id) REFERENCES
tbl_job(pk_job_id) ON DELETE SET NULL

Henrik <henke@mac.se> wrote:
>
> Hello list,
>
> I have a table with 135 rows and it still takes up about 360MB with
> only small columns. Details below.
>
> db=# vacuum full tbl_archive;
> VACUUM
> db=# select * from
> pg_size_pretty(pg_total_relation_size('tbl_archive'));
>   pg_size_pretty
> ----------------
>   360 MB
> (1 row)
>
> db=# select * from pg_size_pretty(pg_relation_size('tbl_archive'));
>   pg_size_pretty
> ----------------
>   16 kB
> (1 row)
>
>
> Looks like we have a very bloated index.
> After reindex
> db=# select * from
> pg_size_pretty(pg_total_relation_size('tbl_archive'));
>   pg_size_pretty
> ----------------
> 80 kB
> (1 row)
>
> I thought that reindex should not be necessary in 8.2.5?

VACUUM FULL tends to bloat indexes, which is one of the reasons that it's
not recommended for regular maintenance.

Use plain VACUUM instead.  If you feel the need to run a VACUUM FULL, always
do a REINDEX afterward.

Even still, there are apparently some corner cases around that cause index
bloat.  If it turns out that you've found one, you may want to document it
so the developers can look into possible solutions.

--
Bill Moran
http://www.potentialtech.com

6 dec 2007 kl. 15.25 skrev Bill Moran:

> Henrik <henke@mac.se> wrote:
>>
>> Hello list,
>>
>> I have a table with 135 rows and it still takes up about 360MB with
>> only small columns. Details below.
>>
>> db=# vacuum full tbl_archive;
>> VACUUM
>> db=# select * from
>> pg_size_pretty(pg_total_relation_size('tbl_archive'));
>>  pg_size_pretty
>> ----------------
>>  360 MB
>> (1 row)
>>
>> db=# select * from pg_size_pretty(pg_relation_size('tbl_archive'));
>>  pg_size_pretty
>> ----------------
>>  16 kB
>> (1 row)
>>
>>
>> Looks like we have a very bloated index.
>> After reindex
>> db=# select * from
>> pg_size_pretty(pg_total_relation_size('tbl_archive'));
>>  pg_size_pretty
>> ----------------
>> 80 kB
>> (1 row)
>>
>> I thought that reindex should not be necessary in 8.2.5?
>
> VACUUM FULL tends to bloat indexes, which is one of the reasons that
> it's
> not recommended for regular maintenance.
>
> Use plain VACUUM instead.  If you feel the need to run a VACUUM
> FULL, always
> do a REINDEX afterward.
I usually only do normal vacuum but its good to know that reindex
should be ran after vacuum full.
>
>
> Even still, there are apparently some corner cases around that cause
> index
> bloat.  If it turns out that you've found one, you may want to
> document it
> so the developers can look into possible solutions.
>
I maybe have an idea why its get this big but I'll do some more
testing first!
Thanks,
Henke
> --
> Bill Moran
> http://www.potentialtech.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/