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
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.... |