Thread: vacuum a lot of data when insert only

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

On Thu, Jun 21, 2007 at 07:53:54PM +0300, Sabin Coanda wrote:
> Reading different references, I understand there is no need to vacuum a
> table where just insert actions perform.

That's false.  First, you must vacuum at least once every 2 billion
transactions.  Second, if a table is INSERTed to, but then the
INSERTing transaction rolls back, it leaves a dead tuple in its wake.
My guess, from your posted example, is that you have the latter case
happening, because you have removable rows (that's assuming you
aren't mistaken that there's never a delete or update to the table).

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
        --Scott Morris