Unreasonable size of table pg 8.2.5

Started by Henrik Zagerholmover 18 years ago3 messagesgeneral
Jump to latest
#1Henrik Zagerholm
henke@mac.se

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
#2Bill Moran
wmoran@potentialtech.com
In reply to: Henrik Zagerholm (#1)
Re: Unreasonable size of table pg 8.2.5

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

#3Henrik Zagerholm
henke@mac.se
In reply to: Bill Moran (#2)
Re: Unreasonable size of table pg 8.2.5

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

Show quoted text

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

---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/