Unreasonable size of table pg 8.2.5

From: Henrik <henke(at)mac(dot)se>
To: pgsql-general(at)postgresql(dot)org
Subject: Unreasonable size of table pg 8.2.5
Date: 2007-12-06 14:18:25
Message-ID: 5E3240DA-EDE1-48DD-94C2-1921F3F10889@mac.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2007-12-06 14:25:58 Re: Unreasonable size of table pg 8.2.5
Previous Message Alvaro Herrera 2007-12-06 14:14:30 Re: autovacuum is stopping automatically....