Postgres and the Perils of Abstraction

It only matters whe it affects me
 — Every #CowboyDeveloper Ever

Isolation LevelsYou know about that the default isolation level in Postgres is “Read Committed”, right?
You do? Well good for you Genius, ‘cos I sure as hell didn’t.
But first, a digression.

Isolation Levels, in a database, refer to the various ways in which concurrent transactions can impact each other. Say I’m read-ing a bunch-a data from the database, and database, and during this you update one of the records, what happens?
• Do I see the data before your update?
• Do I see the data after your update?
• If my read is actually a couple of table scans inside a single transaction, what happens? Do I get both the before and after version of the data?
• Does my read just bork with a “somebody is updating this table” error?
• Do I get a NullPointerException? (Sorry. PoorJava humor. My bad)

Thanks to the folks at ANSI, and much more importantly, Atul Adya, there are some pretty good — and rigorous! — definitions of what these isolations levels actually are, and how they map to existing standards. Even more importantly for me, Postgres has codified this since 9.1, and allows you to define which isolation level you want to run with.

/via https://bit.ly/2q3VIvy

And that brings me to the current day. At an (overly! Very overly!) simplified level, Postgres allows three levels of isolation

1. • Read Committed:
   Each read within a transaction will see a consistent view of the database. If, however, a transaction has multiple reads, each of these reads can see different views of the database!
2. • Repeatable Read:
   Each transaction sees a consistent view of the database (this includes all the reads within the transaction)
3. • Serializable:
   Concurrent transactions can only go ahead if the system can prove that if they run in some specific order, they will always have the same result. Or, in other words, it’s as if all the transactions happen one after the other.

The default is “Read Committed”. Remember this, it’s important.

Abstractions
Once upon a time, when dinosaurs used to roam the earth, and SREs were called “Sysadmins” — yeah, this is even before DevOps! — people used to actually give a shit about what the database was actually doing. They actually had to pay attention to things like “SQL statements” and “foreign keys”, and even “indexes”!
Seriously though, being closer to the database meant that you had to actually know what was going on in there — what the sql statements were doing, the impact of changing a transactions, adding an index, and so on.
Nowadays, thanks to the abstractions provided by the MVC frameworks that you mostly rely on (Django, Rails, whatever), you pretty much tend to ignore the databases themselves. At best, you go in and add an index because “things got slow”.

Leaky Abstractions
And thats where life gets interesting. Remember the bit where the default isolation level in Postgres is “Read Committed”? Which meant that a single transaction could see different views of the database each time it scanned a table?

Well, the good news is that this tends — kinda, sorta — work for most of our use cases. After all, most of the MVC stuff we do tends to result in fairly simple “one-shot” accesses to the database. Whats more, with some judicious use of connection pools, and “CQRS-lite” (writes go to this singleton, reads go to those connections), we mostly, avoid any issues.

Mostly, however. Because, if your system stays alive long enough, the complexity growth eventually gets you to a place where you start seeing odd behavior, where an address update over here, caused an inventory item to vanish over there, and how the f**k are addresses and inventory item affecting each other I can’t even …

Whats worse, this is something that only impacts you under load, and even then is a concurrency related item, which means you need some sort of tracing framework (hello OpenTracing!), which means a lot of blood, sweat and tears till you figure out that it’s deep down in Postgres.

Change the Defaults?
So, the fix is easy, right? Just change the default isolation level to Serializable, and you’re good, right?
Well, maybe. The thing is, Read Committed is faster, sometimes much faster, because a lot of the Serializable checks are done via transaction monitoring, predicate checks, and the like (think “lots of overhead”). Whats more, the transactions will happily barf because of possible errors, even if the actualerror doesn’t exist (e.g. a key constraint, even if the actual key isn’t present).

That said, a happy path does exist — develop with Serializable , and run in production with Read Committed. The enforced constraints that you have in your code will enforce a whole bunch of best-practices.

Mind you, this does causes a couple of issues
• Develop and production are now different. Then again, they usually are…
• If you’ve got somebody developing with Read Committed, well, you’ve got a problem again. Testing could help here…
• I’m sure there are edge cases that will bite you that haven’t bitten me yet. Then again, there are always edge cases…