Re: Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 5:44 PM, Dragan Zubac <zubac(at)vlayko(dot)tv> wrote:
> Hello
>
> I have some similar situation like Yours,we're using at the moment PG 8.2.0.

As Gregory above mentioned, update NOW to 8.2.7. It only takes minutes to do.

> At the moment we do manually vacuum (one or more times to minimize 'dead'
> data/tuples),and if necessary we do 'full' vacuum.

Think of vacuum as regular maintenance, and full vacuum as emergency
fixing because regular vacuum wasn't run often enough.

Also, look at running the autovacuum daemon. It does a great job for
most people.

> 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),

Regular vacuum does not lock the table (well for more than a split
second anyway).

However, vacuum without a cost delay may chew up so much of your I/O
that performance suffers . Which is why you've got cost delay
settings in postgresql.conf to adjust it.

> 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 ?

No, inserts (at least the ones that don't fail) don't create dead
tuples. Updates and deletes do.

> 2. Multi-insert command
>
> insert into foo (column1,column2) values
> (val1,val2),(val3,val4),(val5,val6);
>
> 1 transaction,guess it means 1 'dead' tuples ?

No, for the reasons above.

However, if they were updates, like this:

begin;
update...
update...
update...
commit;
and each one updated one row, you'd have three dead tuples.

> 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 ?

each tuple replaced by an update, or deleted by a delete, or created
by a failed insert will be a single dead tuple. The number of
transactions means nothing.

But the point about vacuuming stands. More frequent updates /
deletes / failed inserts require more frequent vacuums. The
autovacuum daemon can handle this for ya for the most part, but some
busy systems will outrun it sometimes.

Also, make sure you have enough free space map entries to cover all
your dead tuples or they can't be reclaimed.