Safari uses SQLite, and probably other will follow suite with that solution. As for the standard it says:
Quote: The lifetime of a browsing context can be unrelated to the lifetime of the actual user agent process itself, as the user agent may support resuming sessions after a restart.
This means that it is likely to get stored on disk, which means if someone really wanted to snoop they could check the deleted file and restore it, as you do when with recovery software.
However I, personally, hope they also support in memory-only temporary storage, but that's not in the specs right now, so it might get left out.
Edit: I said "hope" in my last sentence there, but that "hope" goes along with an email I sent to the consortium concerning this issue, so I "hope" they take my suggestion into account. After we all have a say in determining standards, so please make your voice be heard, no one, or team, is perfect and all these standard consortiums are actively looking for feedback.