Re: Vacuuming on heavily changed databases

Hello

I have some similar situation like Yours,we're using at the moment PG
8.2.0. At the moment we do manually vacuum (one or more times to
minimize 'dead' data/tuples),and if necessary we do 'full' vacuum. On
heavy-updated PG,one surely must think of this procedures because they
are considered to be 'daily maintenance routine'. Still haven't migrated
to PG 8.3,which is planned,but one way to look at this problem is to
have 'timeout aware applications',meaning when You fire up vacuum or
some other command that will lock some (or all data), You application
does not stop operating but put itself into a little 'sleep' until data
became available again. Therefore Your frontend
(apps,clients,whatsoever) will observe only a small glitch and not a
corruption in database connectivity,operations,etc.

Also bear in mind that more TPS,more 'dead' data/tuples You will
have,meaning the following:

1. Ordinary inserts

insert into foo (column1,column2) values (val1,val2);
insert into foo (column1,column2) values (val3,val4);
insert into foo (column1,column2) values (val5,val6);

3 separated transaction,guess it means 3 'dead' tuples ?

2. Multi-insert command

insert into foo (column1,column2) values
(val1,val2),(val3,val4),(val5,val6);

1 transaction,guess it means 1 'dead' tuples ?

I'm not sure about this,but guess somebody will correct me if I'm wrong :)

The questions are:

Is number of transactions related to the number of 'dead' rows in PG ?
Meaning less transactions,means less frequently vacuum needed for same
amount of data ?

Sincerely

Dragan

Bohdan Linda wrote:
> Hello,
>
> I would like to ask an opinion on vacuuming general. Imagine situation
> that you have single table with 5 fields (one varchar). This table has
> during the day
>
> - cca 620 000 inserts
> - 0 updates
> - cca 620 000 deletes
>