Welcome to End Point’s blog

Ongoing observations by End Point people

PostgreSQL Serializable and Repeatable Read Switcheroo

PostgreSQL allows for different transaction isolation levels to be specified. Because Bucardo needs a consistent snapshot of each database involved in replication to perform its work, the first thing that the Bucardo daemon does when connecting to a remote PostgreSQL database is:


The 'READ WRITE' bit sets us in read/write mode, just in case the entire database has been set to read only (a quick and easy way to make your slave databases non-writeable!). It also sets the transaction isolation level to 'SERIALIZABLE'. At least, it used to. Now Bucardo uses 'REPEATABLE READ' like this:


Why the change? In version 9.1 of PostgreSQL the concept of SSI (Serializable Snapshot Isolation) was introduced. How it actually works is a little complicated (follow the link for more detail), but before 9.1 PostgreSQL was only *sort of* doing serialized transactions when you asked for serializable mode. What it was really doing was repeatable read and not trying to really serialize the transactions. In 9.1, PostgreSQL is doing *true* serializable transactions. It also adds a new distinct 'internal' transaction mode, 'repeatable read', which does exactly what the old 'serializable' used to do. Finally, if you issue a 'repeatable read' on a pre-9.1 database, it silently upgrades it to the old 'serializable' mode.

So in summary, if your application was using 'SERIALIZABLE' before, you can now replace that with 'REPEATABLE READ' and get the exact same behavior as before, regardless of the version. Of course, if you want *true* serializable transactions, use SERIALIZABLE. It will continue to mean the same as 'REPEATABLE READ' in pre-9.1 databases, and provide true serializability in 9.1 and beyond. (I haven't determined yet if Bucardo is going to use this new level, as it comes with a little bit of overhead)

Since this can be a little confusing, here's a handy chart showing how version 9.1 changed the meaning of SERIALIZABLE, and added a new 'internal' isolation level:

Postgres version 9.0 and earlierPostgres version 9.1 and later
Requested isolation levelActual internal isolation levelVersion comparisonActual internal isolation levelRequested isolation level
READ UNCOMMITTEDRead committedExact sameRead committedREAD UNCOMMITTED
REPEATABLE READSerializableFunctionally identicalRepeatable readREPEATABLE READ
 9.1 only!Serializable (true)SERIALIZABLE

Congratulations and thanks to Kevin Grittner and Dan Ports for making true serializability a reality!


David Christensen said...


Was this change made due to just being a backwards-compatible syntax fix that would work with all versions of PG that Bucardo works with, or is there some incompatibility with true SERIALIZABLE mode that you would need to avoid that in 9.1? For that matter, what is the difference between REPEATABLE READ/SERIALIZABLE as far as Bucardo is concerned?


Greg Sabino Mullane said...

The former: I've not had time yet to see exactly if there are cases where true serializability would benefit Bucardo. I suspect not, as the main reason we are using it is to to get a consistent snapshot, not to order transactions. Bucardo 4 had many seriazliable errors on busy systems, but that should be much less in Bucardo 5. It would be nice to know exactly how *much* of an overhead SSI is: the docs only say there is some...