On Wed, Aug 20, 2003 at 10:31:25 -0400,
Vivek Khera <khera@kcilink.com> wrote:
>
> I care for the performance. And how are you so sure that it was
> faster the way it is now? Are you sure it was not done this way
> because of ease of implementation?
>
> Seriously, how much slower can it be if the backend were to do the
> checking for external references upon updating/deleting a row? The
> cost would be distributed across time as opposed to concentrated at
> once within a vacuum process. I am fairly certian it would reduce
> disk bandwidth requirements since at least one necessary page will
> already be in memory.
It would probably be a lot slower. Any transaction that has started
but not yet finished would need to lock all rows that exist at during
the transaction (for serialized transaction isolation you would only
need to worry about rows that existed at the start of the transaction
or that were modified by the transaction). Immediately that is a big
deal since a typical query may need to lock a bunch of rows that it
will never actually touch (but you don't know that when the transaction
starts). Managing all those locks would take up a lot of semiconductor
memory or a lot of disk writes and be a major source of contention.
The current system just has to mark rows when they are created and
deleted (an update does both operations). The normal vacuum clean up
actually isn't going to be much worse than what you would need to do
at both the start and end of each transaction. The overhead of letting
dead rows hang around for a while after they aren't needed isn't that high.
Also, since at least 7.3, normal vacuums aren't normally going to affect the
performance of your database server that much.
The main issue against the current vacuum system is that it requires the
DBA knowing what vacuum does and figuring out how it should be used in
their situation to get reasonable performance. This makes it a bit harder
for non-DBAs to jump right in to Postgres without running into problems.
However, the work on autovacuum seems to be providing a reasonable solution
to that problem.
