Re: vacuum internals and performance affect

MX,

> to my understanding, vacuum just marks the dead rows of a table so that from
> that point on that space would be re-used for new inserts and new updates on
> that specific table. however, if there is an open transaction, vacuum can
> only do what is described above up to the point that the open transaction
> was started. so if for example there is a query running for 1 day, no matter
> how many times i will have vacuumed the table (manual or auto), the dead
> rows wont be possible to be marked as re-usable space.
> -is the above correct?

More or less. The transactionID isn't a timestamp, so the "stop point"
is based on snapshots rather than a point-in-time. But that's a fine
distinction.

> -is there something more about vacuum in that case i am describing? would
> for example mark the rows as 'semi-dead' so that when a scan would be made
> these rows wouldn't be checked and so the queries would be faster? is there
> anything else for this specific case?

Well, vacuum does some other work, yes.

> -would there be any effect from the vacuum on the indexes of the table?like
> i said above for the table, would the entries of the index not be scanned
> for a query, due to some reason?

Vacuum also does some pruning dead index pointers.

Otherwise, I'm not sure what you're asking.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com