On Wed, Aug 20, 2003 at 10:31:25 -0400, Vivek Khera <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]