yes, the entire storage backend is based on MVCC [1]. there currently is no lock-based option. objects are MVCC in memory for concurrency (see section "concurrent transactions" [2]), and on disk to achieve atomic updates. when an update is safely synced to disk the old object versions are discarded.
At least from MVCC is great. Others may want lock based solution. POSTGRESQL uses MVCC only or by default. That is how I first heard about it. Unfortunately after going though all that work, POSTGRESQL doesn't give you access to the historical versions. All you can do is VACUUM, discard, the data. There currently is no query and VACUUMTO data warehouse options. Personally, I think MVCC is better than Microsoft SQL Server's change tracking but POSTGRESQL has been stuck at 99.999999% since the beginning. Long story short, historical versions is a feature and if you are already collecting it then could you provide an API for your developer users to access that valuable information. [Maybe an optional API in case there is a usecase to go away from it in the future.] I have worked for a lot of companies, small and great, that require access to historical data. I have used SQLite also. Don't sell your PERSISTENT short. SQLite like MySQL is stuck on SQL92 though developers needs some more of the recent SQL standards. Also JavaDB, Derby, H2 and HyperSQL might not be able to compete with Microsoft SQL Server, MySQL, POSTGRESQL and Oracle but these small databases might have wider circulation among developers and testers that never gets counted. Everything must start somewhere.