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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
