Postgres developer Bruce Momjian joins Robert Blumen for a discussion of the SQL query optimizer in the Postgres RDBMS. They delve into the internals of query planning and look at how developers can make it work for their apps.

This week, Postgres server developer Bruce Momjian joins host Robert Blumen for a discussion of multi-version concurrency control (MVCC) in the Postgres database. They begin with a discussion of the isolation requirement in database transactions (I in ACID); how isolation can be achieved with locking; limitations of locking; how locking limits concurrency and creates variability in query runtimes; multi-version concurrency control as a means to achieve isolation; how Postgres manages multiple versions of a row; snapshots; copy-on-write and snapshots; visibility; database transaction IDs; how tx ids, snapshots and versions interact; the need for locking when there are multiple writers; how MVCC was added to Postgres; and how to clean up unused space left over from aged-out versions.