[EMAIL PROTECTED] writes: > This is *NOT* a perfect or elegant solution. There is, however, an > important problem. How do you maintain a value that is visable to the > database, but does not incure the cost of a huge number of updates or a > full table scan? I'm talking about systems that need to take thousands of > inserts per minute?
Hm, the real question here is how does postgres handle a particular circumstance, namely a table with a small number of very busy rows taking hundreds or thousands of updates per second, with few rollbacks. In the actual case the busy records are always read in short transactions (single-record reads) so there's a small number of active records at any time, probably usually 1, at any time. Currently postgres handles this by creating a new version for every update and then delaying the cleanup of the old version until a vacuum can be done. Oracle on the other hand creates new versions and stores the old version in a rollback segment. So only the most recent version is in the heap. This incurs cleanup overhead in the critical path of the transaction. I don't think it makes sense to move to the Oracle-style method of course. But perhaps there's some way to move the cleanup closer to the end of the transaction. I'm thinking something along the lines of the bgwriter vacuuming every block as it's written out. That forces every update to generate a vacuum of that same block at some point in the near future. I think something like this has been proposed before, and the objection was that it would force index pages to be read in the midst of bgwriter trying to get pages out. But if we're talking about a small, very busy table then the index will presumably be entirely in memory anyways. Perhaps every page in memory could hold an update count and trigger an immediate vacuum if 2 updates have been performed on the page while still in the shared pool, or some heuristic like that. But I think it would be worth experimenting with things like this. If something like it could relieve a lot of the vacuum related angst then it might be worth some other angst about extra i/o in bgwriter. -- greg ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
