The session table is a different issue, but has the same problems. You have an active website, hundreds or thousands of hits a second, and you want to manage sessions for this site. Sessions are created, updated many times, and deleted. Performance degrades steadily until a vacuum. Vacuum has to be run VERY frequently. Prior to lazy vacuum, this was impossible.
Both session tables and summary tables have another thing in common, they are not vital data, they hold transitive state information. Yea, sure, data integrity is important, but if you lose these values, you can either recreate it or it isn't too important.
Why put that is a database at all? Because, in the case of sessions especially, you need to access this information for other operations. In the case of summary tables, OLAP usually needs to join or include this info.
PostgreSQL's behavior on these cases is poor. I don't think anyone who has tried to use PG for this sort of thing will disagree, and yes it is getting better. Does anyone else consider this to be a problem? If so, I'm open for suggestions on what can be done. I've suggested a number of things, and admittedly they have all been pretty weak ideas, but they were potentially workable.
There is another as-of-non-feasible and hence rejected approach. Vacuum in postgresql is tied to entire relations/objects since indexes do not have transaction visibility information.
It has been suggested in past to add such a visibility to index tuple header so that index and heaps can be cleaned out of order. In such a case other backround processes such as background writer and soon-to-be integrated autovacuum daemon can vacuum pages/buffers rather than relations. That way most used things will remain clean and cost of cleanup will remain outside crtical transaction processing path.
However increasing index footprint seems to be a tough sell. Besides FSM would need some rework to accomodate/autotune it's behaviour.
I am quoting from memory, so don't flame me if I misquote it. Just adding to make this complete. Only from performance point of view, it could solve quite some problems, at least in theory.
Shridhar
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
