[PERFORM] vacuum a lot of data when insert only

[Sabin Coanda]

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 



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] vacuum a lot of data when insert only

[Andrew Sullivan]

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  | [EMAIL PROTECTED]
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.
--Scott Morris

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match