Disabling MVCC in Postgres

1k views Asked by At

I've decades of experience with MSSQL but none with Postgres and its MVCC style of concurrency control.

In MSSQL if I had a very large dataset which was read-only, I would set the database to read-only (for safety) and use transaction isolation level read uncommitted, and that should avoid lock contention, which the dataset didn't need anyway.

In Postgres, is there some equivalent? Some way of setting a database to read-only and reassuring PG that is completely safe not to use MVCC, just read without making row copies? Because it seems that MVCC has some considerable overhead which for multiple readers of very large passive data sets seems potentially expensive.

Edit: comments say I misunderstand that copies are only made when writing occurs, not reading as I assumed.

2

There are 2 answers

0
Erwin Brandstetter On BEST ANSWER

"MVCC" stands for "Multiversion Concurrency Control". Multiple versions of the same table row are only spawned by write activity (mostly UPDATE).

If your database is read-only - enforced or voluntarily, all the same for the purpose of this question - then there cannot be multiple versions of a row, ever. And the question is moot.

0
Laurenz Albe On

No, there is no way to do that, and there is no reason for it either.

Since PostgreSQL, writers will never block readers and vice versa, precisely because of its MVCC implementation that you want to disable. So there is no need for the unsavory crutch of reading uncommitted data.