Disabling MVCC in Postgres

Question

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.

"*Because it seems that MVCC has some considerable overhead which for multiple readers*" not it has not. Don't worry, you don't need to do anything. Reader never block other readers (or writers). — , Dec 15 '21 at 15:05
If your database is read-only, then there cannot be multiple versions of a row (the "MV" in "MVCC") anyway, so the question is moot. — Erwin Brandstetter, Dec 15 '21 at 15:07
What overhead? https://www.postgresql.org/docs/current/mvcc.html — Frank Heikens, Dec 15 '21 at 15:09
OK, I got that wrong big time. I'll do some reading. @Frank Heikens, can you be a little more precise about what you suggest I read from your link? TIA BTW this from another SO answer looks pretty relevant. — user3779002, Dec 15 '21 at 15:38
If your system is read only, you don't have to worry about transactions, locks, mvcc, etc. There is just one single version of a record and that's it. You can't fix a problem you don't have, so don't worry about MVCC, it works fine, also for a read only database. — Frank Heikens, Dec 15 '21 at 15:59
@ErwinBrandstetter: "If your database is read-only..." there's a difference between setting a DB to read-only somehow (so the DB engine would reject any write attempts), or the user/application choosing only to read from, but never write to, that DB. Can you clarify which you mean? — user3779002, Dec 17 '21 at 10:55
@user3779002: Enforced or voluntarily, that makes no difference. No writes, no multiple versions of the same row. — Erwin Brandstetter, Dec 17 '21 at 17:05
@ErwinBrandstetter: Clear, thanks. If you wish, summarise your 2 comments as an answer and I'll accept it as yours is, WRT my question, the most relevant response. — user3779002, Dec 17 '21 at 17:51
Perhaps better asked on sister site, https://DBA.StackExchange.com/ — Basil Bourque, Dec 17 '21 at 18:03
This question doesn't make any sense. It assumes MVCC has a performance penalty in read-only applications and it tries to solve a problem that there is no evidence for existing in the question. — Evan Carroll, Dec 17 '21 at 22:26

score 3 · Accepted Answer · answered Dec 17 '21 at 18:00

"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.

score 0 · Answer 2 · answered Dec 15 '21 at 16:24

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.

Disabling MVCC in Postgres

2 Answers2