vacuum a lot of data when insert only

From: "Sabin Coanda" <sabin(dot)coanda(at)deuromedia(dot)ro>
To: pgsql-performance(at)postgresql(dot)org
Subject: vacuum a lot of data when insert only
Date: 2007-06-21 16:53:54
Message-ID: f5eaek$19hl$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi there,

Reading different references, I understand there is no need to vacuum a
table where just insert actions perform. So I'm surprising to see a table
with just historical data, which is vacuumed at the nightly cron with a
simple VACUUM VERBOSE on about 1/3 of indexes amount.

Take a look on the fragment log concerning this table:
INFO: vacuuming "public.tbTEST"
INFO: scanned index "tbTEST_pkey" to remove 1357614 row versions
DETAIL: CPU 0.31s/1.38u sec elapsed 4.56 sec.
INFO: "tbTEST": removed 1357614 row versions in 16923 pages
DETAIL: CPU 0.70s/0.13u sec elapsed 2.49 sec.
INFO: index "tbTEST_pkey" now contains 2601759 row versions in 12384 pages
DETAIL: 1357614 index row versions were removed.
5415 index pages have been deleted, 2452 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "tbTEST": found 1357614 removable, 2601759 nonremovable row versions
in 49153 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 29900 unused item pointers.
16923 pages contain useful free space.
0 pages are entirely empty.
CPU 2.12s/1.87u sec elapsed 11.41 sec.
INFO: "tbTEST": truncated 49153 to 32231 pages
DETAIL: CPU 0.23s/0.06u sec elapsed 0.31 sec.

I found the following statistics in pg_stat_user_tables:
n_tup_ins = 11444229
n_tup_upd = 0
n_tup_del = 0

The structure of the table is the following:
CREATE TABLE "tbTEST"
(
"PK_ID" integer NOT NULL DEFAULT nextval('"tbTEST_PK_ID_seq"'::regclass),
"FK_SourceTypeID" integer,
"SourceID" integer DEFAULT -1,
"Message" character varying(500) NOT NULL DEFAULT ''::character varying,
"DateAndTime" timestamp without time zone NOT NULL,
CONSTRAINT "tbTEST_pkey" PRIMARY KEY ("PK_ID"),
CONSTRAINT "tbTEST_FK_SourceTypeID_fkey" FOREIGN KEY ("FK_SourceTypeID")
REFERENCES "tbLISTS" ("PK_ID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)

Postgres version is 8.2.3.

What's happen ?

TIA,
Sabin

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Steven Flatt 2007-06-21 17:09:57 Database-wide VACUUM ANALYZE
Previous Message Campbell, Lance 2007-06-21 16:32:22 Re: Volunteer to build a configuration tool