Simon,
> Another way of doing this is to utilize lazy snapshots: do not take a
> snapshot when a statement starts and only take one at the point that we
> need one. No other changes to the MVCC mechanisms are proposed.
I like your general thinking here, even if this specific idea proves
unworkable. It's along the lines of "how can we take less snapshots?"
I'd love to see other ideas in this direction.
Overall, this sounds like an optimization which would be excellent for
large database performance but would mess with OLTP transaction semantics.
> A floating, yet consistent viewpoint is in my opinion a good thing,
> since it includes a more recent database state in the answer to a query
> than we would otherwise have used. Consider the case where a very large
> table has a "select count(*)" executed on it. The scan begins at block 0
> and continues through the table until the end, which for purposes of an
> example we will say takes 1 hour. Rows are added to the table at a
> constant rate of 100/sec and immediately committed. So by the time the
> scan has finished it will deliver an answer that is wrong by 360000.
> Using a lazy snapshot would give us an answer almost exactly correct,
> though of course Heisenbuggers may dispute the existence of a "correct"
> answer in this case.
You're assuming that all of the new rows are at the end of the table,
which makes sense for inserts. But what about updates? You're also
assuming we begin the scan at the end, which for Synchronized Scans
isn't necessarily true.
> * Scan begins, no snapshot set. A row is inserted and transaction
> commits. Scan progresses until it sees a recent row.
How do we define "recent"?
The bigger issue with this idea, I think, is that it only *delays*
taking snapshots, it doesn't *prevent* taking them. That is, you still
have to take the snapshot eventually, you just do it later. So it helps
the hot standby case, possibly, but I don't see the other benefits
materializing. And we'd have to deal with a lot of complications which
this change would cause.
When we discussed Hot Standby at pgCon 2008, we discussed the
possibility of stopping the replications stream whenever a VACUUM came
through until any queries currently running on the slave completed. Did
that approach turn out to be completely unworkable? Why?
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
