Jepsen: PostgreSQL 12.3
jepsen.io
jepsen.io
Around June 4th, the article's author comes in with a bug report that basically says "I hammered Postgres with a whole bunch of artificial load and made something happen" [1].
By the 8th, a preliminary patch is ready for review [2]. That includes all the time to get the author's testing bootstrap up and running, reproduce, diagnose the bug (which, lest us forget, is the part of all of this that is actually hard), and assemble a fix. It's worth noting that it's no one's job per se on the Postgres project of fix this kind of thing — the hope is that someone will take interest, step up, and find a solution — and as unlikely as that sounds to work in most environments, amazingly, it usually does for Postgres.
Of note to the hacker types here, Peter Geoghegan was able to track the bug down through the use of rr [4] [5], which allowed an entire problematic run to be captured, and then stepped through forwards _and_ backwards (the latter being the key for not having to run the simulation over and over again) until the problematic code was identified and a fix could be developed.
---
[1] https://www.postgresql.org/message-id/CAH2-Wzm9kNAK0cbzGAvDt...
[2] https://www.postgresql.org/message-id/CAH2-Wzk%2BFHVJvSS9VPP...
[3] https://www.postgresql.org/message-id/CAH2-WznTb6-0fjW4WPzNQ...
[4] https://en.wikipedia.org/wiki/Rr_(debugging)
[5] https://www.postgresql.org/message-id/CAH2-WznTb6-0fjW4WPzNQ...
Since distributed systems are so difficult and complicated, it enables salespeople and zealots to both deny issues and overstate capability.
Your work is a shining star in that darkness. Thank you.
And I agree. For me, one of the most important measures of the reliability of a system is how that system responds too information that it might be wrong. If the response is defensiveness, evasiveness, or persuasive in any way, i.e. of the "it's not that bad" variety, run for the hills. This, on the other hand is technical, validating, and prompt.
Every system has bugs, but depending on these cultural features, not every system is capable of systematically removing those bugs. With logs like these, the pg community continues to prove capable. Kudos!
This resonates with me with teams inside the company as well.
We have a few teams that just deflect issues. Find any issue in the bug report, be it an FQDN in a log search, and poof it goes. Back to sender, don't care. Engineers in my team just don't care to report bugs there anymore, regardless how simple. Usually, it's faster and less frustrating to just work around it or ignore it. You could be fighting windmills for weeks, or just fudge around it.
Other teams, far more receptive with bugs.. engineers end up curious and just poke around until they understand what's up. And then you have bug reports like "Ok, if I create these 57 things over here, and toggle thing 32 to off, and then toggle 2 things indexed by prime numbers on, then my database connection fails. I've reproduced this from empty VMs. If 32 is on, I need to toggle two perfect squares on, but not 3". And then a lot of things just get fixed.
Which is obviously not true.
A better response might be something like "I understand the problem is X however the fix might take Y months and the next release will deprecate that feature anyway. Can you quantify or guesstimate the impact on our product if we allow this bug to stay unfixed?"
I think if I were to try and salvage the quote, I'd change it to "It's probably not that bad". In this telling, I'm meaning to imply that the responder has made an apriori value judgement about a bug without determining scope and cause.
I agree with your ideal response. The only caveat I'd personally make, is that I'd recommend "planting the flag" by providing your own guesstimate of product impact, and ask the reporter for their opinion on moving it.
In my perspective, it's important to acknowledge that is has an impact, and it's especially important to convey your sense of impact concretely with some napkin math. If you're terribly wrong, the reporter will try to convince you of it[1], and you'll learn something along the way. It also conveys empathy (i.e. we're both trying to solve your problem, even if it's only a problem for you).
I'd say that the storage overhead is unlikely to be a concern in almost all cases. It's just something that you need to keep an eye on.
section 4.4 talks about disk requirements:
> Memory-mapped files are almost entirely just the executables and libraries loaded by tracees. As long as the original files don’t change and are not removed, which is usually true in practice, their clones take up no additional space and require no data writes
> Like cloned files, cloned file blocks do not consume space as long as the underlying data they’re cloned from persists.
they conclude the section with:
> In any case, in real-world usage trace storage has not been a concern
I imagine that over "longer debugging sessions" the metadata footprint would expand linearly, but probably with a constant smaller than the logs for the average program.
However, there is one more takeaway here. I've heard too many times "just use Postgres", repeated as an unthinking mantra. But there are no obvious solutions in the complex world of databases. And this isn't even a multi-node scenario!
I don't think the "just use Postgres" mantra takes any hits at all from this. (If anything, I feel better about it).
I've used maybe a dozen (?) databases/stores over the years - graph databases, NoSQL databases, KV stores, the most boring old databases, the sexiest new databases - and my general approach is now to just use Postgres unless it really, really doesn't fit. Works great for me.
I'm happy Postgres works for you. It works for me, too, in a number of setups. But one should never accept advice like "just use Postgres" without thinking and careful consideration. As the Jepsen article above shows.
There is no such thing as "use x.y.z unless" this statement does not make any sense.
In simple cases, what people usually need is what I would call "operational" HA anyway; database servers going down unintended is (hopefully!) not a common occurrence, and even with a super simple 2-node primary/failover asynchronous replication setup, you can operate on the database servers with maybe a few seconds of downtime, which is about the same you'd get from "natively" clustered solutions.
In true failure scenarios, you might get longer downtime (though setting up automatic failover with eg. pacemaker is also possible), but in situations where that ends up truly mattering you likely have the resources to make it work anyway.
https://www.postgresql.org/docs/current/high-availability.ht...
I used CouchDB in a project because the high availability was much better on the paper, but I think postgresql would have been fine after all.
Good managed postgresql options with high availability are in many public clouds.
No one claims it to be, hence the "unless there's a good reason not to" part. However, it is a good default that works well for a wide range of – though hardly all – use cases.
Replication in general is not PostgreSQL's strongest point and if you require advanced replication then PostgreSQL may indeed not be the choice for you, but most projects don't need this. That being said, "no HA support" is just not true.
Why? because when `n` is small (tables, rows, connections), postgres works well enough, and if `n` should ever become large, we'll have interest in funding the work to do a migration, if that's appropriate - and we'll be able to evaluate the system at scale with real data.
The only real justification I've heard is postgres' story on replication still lags mysql, and the write amplification bit which they've done a lot of work on for pg13 (see [0])
Even for many very large companies I've seen, noSQL databases are mostly used as caches that can afford to be delayed or a bit out of sync, not as the database of record.
- the story of replication
- ultra-high volume write contention
- high volume timeseries data streams
For #3 there, I was consulting on a project a few years ago where there was about 1 gigabyte of timeseries data in Postgres/RDS. Some ad hoc queries were really running into slowness - COTS RDS isn't great at speed. I want to say 1 billion rows, but that seems a off. Anyway, we were talking data sharding, etc. Didn't go anywhere, because the project was canceled, etc. But we could have done a nice job if we wanted to.
Point being, there are design limits on postgres, particularly regarding distributed systems. Which is fine. I just have this allergy to designing for scale on Very Small Project.
Also worth noting that the anti-monolith dogma plays out here, a few well built monoliths probably are better for a SQL database than "one bajillion microservices".
You use "unthinking" pejoratively, but being able to skip past some decisions without over-analyzing is really important. If you are an 8-person startup, you don't have time for 3 of the people to spend weeks discussing and experimenting with alternatives for every decision.
Databases are really important, but people make tons of important decisions based on little information. If you have little information other than "I need to pick a database", then Postgres is a pretty good choice -- a sensible default, if you will.
Everyone wants to be the default, of course, so you need some criteria to choose one. It could be a big and interesting discussion, but there are relatively few contenders. If it's not free, it's probably out. If it's not SQL, it's probably out. If it's not widely used already, it's probably out. If it's tied to any specific environment/platform (e.g. Hadoop), it's probably out (or there will be a default within that specific platform). By "out", I don't mean that it's unreasonable to choose these options, just that they would not make a sensible default. It would be weird if your default was an expensive, niche, no-SQL option that runs inside of Hadoop.
So what's left? Postgres, MySQL, and MariaDB. SQL Server and MongoDB, while not meeting all of the criteria, have a case for being a default choice in some circles, as well. Apparently, out of those options, many on HN have had a good experience with Postgres, so they suggest it as default.
But if you have additional information about your needs that leads you to another choice, go for it.
Many immature databases with not much wide use are better avoided though, we manage to break datomic three times during development, the first two bugs were fixed in a week, the third took a month, which they called in their changelog "Fix: Prevent a rare scenario where retracting non-existent entities could prevent future transactions from succeeding" so yeah, we went back to "just use postgres", who wants to go through the nightmare of hitting those bugs in production and who knows how many more?scary situation.
And that first email, my god, that should be titanium-and-gold-plated standard of a bug report.
It's a thing of beauty. It even includes versions of software used!
My daily experience with bug reports are that they 50/50 won't even include a description, just a title. It's such a cliche already, but "project name is broken" makes my blood boil. What environment? What were you doing? Is this production? How do I test this bug? (from an Ops perspective) When did you notice this? Has anything changed recently to possibly cause an error?
Arg, my blood pressure!
/offtopic, sorry.
Finding effectively only a single obscure and now fixed issue where real-world consistency did not match the promised consistency is pretty impressive.
They also admitted, that testing framework cannot evaluate more complex scenarios with subqueries, aggregates and predicates. So it is possible, that PG consistency promises are spot on or maybe even overpromising.
Zookeeper is impressive in many ways. However, unless something changed drastically in the last two years, Zookeper throughput will always limit it to configuration/metadata/control-plane rather than a primary/data-plane use cases.
This is super interesting. Jepsen seems to be like Hypothesis for race conditions: you specify the race condition to be triggered and it generates tests to simulate it.
Yesterday, Gitlab acquired a fuzz testing company[1]. I wonder if Jepsen was envisioned as a full CI integrated testing system
Or is it specific to the people who build things like databases, api frameworks,etc.
> "I cannot begin to convey the confluence of despair and laughter which I encountered over the course of three hours attempting to debug this issue. We assert that all keys have the same type, and that at most one integer type exists. If you put a mix of, say, Ints and Longs into this checker, you WILL question your fundamental beliefs about computers" [1].
I feel like Jepsen/Elle is a great argument for Clojure, reading the source is actually kind of fun. Not what you'd expect for a project like this.
[1]: https://github.com/jepsen-io/elle/blob/master/src/elle/txn.c...
I've considered spec as well, but spec has a weird insistence that a keyword has exactly one meaning in a given namespace, which is emphatically not the case in pretty much any code I've tried to verify. Also its errors are... not exactly helpful.
That said, I've found core.typed helpful in managing complex state transformations, especially in namespaces which have, say, five or six similar representations of the same logical thing. What do you do when a "node" is a hostname, a logical identifier in Jepsen, an identifier in the database itself, a UID, and a UID+signature pair? Managing those names can be tricky, and having a type system really helps.
-- Given (roughly) the following transactions:
-- Transaction 1 (SELECT, T1)
with all_employees as (
select sum(salary) as salaries
from employees
),
department as (
select department, sum(salary) as salaries
from employees group by department
)
select sum(all_employees.salaries) - sum(department.salaries);
-- Transaction 2 (INSERT, T2)
insert into employees (name, department, salary)
values ('Tim', 'Sales', 70000);
-- G2-Item is where the INSERT completes between all_employees and department,
-- making the SELECT result inconsistent
They say Elle found 6 such cases in 2 min, which I'm guessing is a "very big number" of transactions, but can't figure out exactly how big that number is based on the included logs/results.
Also, "Elle has found unexpected anomalies in every database we've checked"
This parameter prevents Patroni from switching off the synchronous replication on the primary when no synchronous standby candidates are available. As a downside, the primary is not be available for writes (unless the Postgres transaction explicitly turns of synchronous_mode), blocking all client write requests until at least one synchronous replica comes up.
https://patroni.readthedocs.io/en/latest/replication_modes.h...
edit: seems I missed this discussion on twitter: https://twitter.com/jepsen_io/status/1265626035380346881
Can be reproduced even on a single node postgres. Just hammer it with inserts and maintain a local counter for inserts performed. Then, kill9 the postgres process. You'd expect your local counter to match the actual rows inserted, but you'll find that your counter will always be "less" than the actual rows inserted. Like any "networked" system, it is possible to lose commit acknowledgments even if the commit itself was successful.
So yes, you've not "lost" transactions per se. You've "gained" them, but it is still a data issue in either case.
People like to criticise NoSQL databases like MongoDB etc but at least they took on the challenge of making clustering easy enough to use and safe enough to rely on. Especially because it such a complex and error prone challenge.
MongoDB has definitely come a long way in terms of HA, but yes, they are still have a long way to go. A good primer talk on the differences can be found here: https://www.scylladb.com/tech-talk/mongodb-vs-scylla-product...
Cassandra is one of if not the best since it's multi-master but it's a little bit more complex to setup.
Does anyone here know how Amazon RDS's HA setup, particularly their multi-AZ option, works? That seems to be a switch that the AWS customer can just turn on. Do they have a proprietary implementation, even for non-Aurora Postgres?
They basically do replication at the storage layer. Each write has to be acknowledged by both the primary and secondary EBS volume.
And on top of this they have layered PostgreSQL, MySQL, MongoDB, Cassandra etc.
I doubt they will never release the code for it since it's very much a competitive advantage.
That seems inferior to having multiple sync replicas ready to take over without having to start a process and replay the WAL.
Also, such an HA block store seems very easy to replicate ( I'd guess there would be something open source already), not much of a competitive advantage.
- Single instance, if the instance dies they start a new one and mount the same storage. This can take some time, in general under 5min but I have seen it take 45min, especially for the large instance types.
- Multi A-Z, they run a hot standby with replicated and physically separated storage. Failover takes about a minute. The replication happens at storage level, every write has to be acknowledged by both availability zones. I'm not sure if Postgres is always running or if it gets started when failing over.
I guess you could replicate this using drbd.
- block-level replication can be more reliable in the long run operationally than some types of database replication, especially MySQL back in the day
- block-level replication has more scalable support staff available than hiring DBAs to fix database replication problems
- programming for all the edge cases is something that is a competitive advantage
- no licensing required for it
- you can probably guess which Open Source project it's based on
Source: DBA, worked there.
NB: It is Free software, don't be alarmed by the domain name.
The difference though is the reaction from the vendor.
PostgreSQL is quite the opposite on that front, confident yet open to critics and abble to admit mistakes. Hell, I've even them present their mistakes at conferences and ask for help.
Always read the footnotes!
I have wondered more than once and my browsing and searching skills are failing me on this one.
Edit: The closest link I can find is "Call me maybe" but I am not able to find a causation or even a direct link or mention for now.
To my defence I did stop immediately once I understood.
Oh, and by the way: thanks for your work!
https://aphyr.com/posts/281-jepsen-on-the-perils-of-network-...
https://aphyr.com/posts/281-call-me-maybe-carly-rae-jepsen-a...
I dimly recall that either Aphyr's blog or the jepsen blog was called "call me maybe" in the earlier days.
Actually, it looks like the original talk (Slides: https://aphyr.com/media/jepsen-ricon-east.pdf has multiple references) and the original blog post has a slug referring to the song https://aphyr.com/posts/281-call-me-maybe-carly-rae-jepsen-a...
(you can still see it in the url)
I've always suspected he changed it for legal reasons, and his comment elsewhere in this thread pretty much confirms it.
It's just extraordinary to me that it's 2020 and it still does not have a built-in, supported set of features for supporting this use case. Instead we have to rely on proprietary vendor solutions or dig through the many obsolete or unsupported options.
That's what other DBs have but it seems to be missing from postgres. If it now exists could you point me to the doc explaining how to do this?
Also, same for a multi-master solution.
If you're looking for HA or need to shard then it's reliability is in question since it's never been tested.
Specifically - if running a transaction as SERIALIZABLE there was a very small chance that you might not see a rows inserted by another transaction that committed before you in the order. Many applications don’t need this level of transaction isolation - but for those that do it’s somewhat scary to know this was lurking under the bed.
Every implementation of a “bank” system where you keep track of deposits and withdrawals is a use-case for SERIALIZABLE, and this means a double-spend could happen because the next transaction didn’t see an account just had a transaction that drained the balance, for example.
Props to Jepsen for finding this.
For the vast majority of the history of banking, local branches (which is a very loose term here, e.g. a family member of the guy you know in your hometown, rather than an actual physical establishment) would operate on local knowledge only. Consistency is achieved only through a regular reconciliation process.
Even in more modern, digital times, banks depend on large batch processes and reconciliation processes.
Noteworthy: "In most respects, PostgreSQL behaved as expected: both read uncommitted and read committed prevent write skew and aborted reads."
-No just a single huge instanced, managed on Azure
Any insights into why we should want repeatable read to block that? It feels like blocking that is specifically the purpose of serializable isolation.
The ANSI definitions are bad: they allow multiple interpretations with varying results. 25 years ago, Berenson, O'Neil, et al. published a paper showing the ANSI definitions had this ambiguity, and that what the spec meant to define should have been a broader class of anomalies. They literally say that the broad interpretation is the "correct" one, and the research community basically went "oh, yeah, you're right". Adya followed up with generalized isolation level definitions, and pretty much every paper I've read has gone with these versions since. That didn't make its way back into the SQL spec though: it's still ambiguous, which means you can interpret RR as allowing G2-item.
Why prevent G2-item in RR? Because then the difference between repeatable read and serializable is specifically phantoms, rather than phantoms plus... some other hard-to-describe anomalies. If you use the broad/generalized interpretation, you can trust that a program which only accesses data by primary key, running under repeatable read, is actually serializable. That's a powerful, intuitive constraint. If you use the strict interpretation, RR allows Other Weird Behaviors, and it's harder to prove an execution is correct.
For a very thorough discussion of this, see either Berenson or Adya's papers, linked throughout the report.
In PostgreSQL's case, if they somehow made repeatable read to prevent G2-item without sacrificing the phantom reads, would that mean repeatable read is then "serializable" according to the ANSI definition?
Well... it's not quite so straightforward. SI still allows some phantoms. It only prohibits some of them.
In PostgreSQL's case, if they somehow made repeatable read to prevent G2-item without sacrificing the phantom reads, would that mean repeatable read is then "serializable" according to the ANSI definition?
I'm not quite sure I follow--If you're asking whether snapshot isolation (Postgres "Repeatable Read") plus preventing G2-item is serializable, the answer is no--that model would still allow G2 in general--specifically, cycles involving non-adjacent rw dependencies with predicates.
> This behavior is allowable due to long-discussed ambiguities in the ANSI SQL standard, but could be surprising for users familiar with the literature.
Should that be "not familiar"? And which literature - the standard or the discussions?
BTW, nice to see one of of my lecturers (Fekete) among the names!
Jokes aside, Marklogic is welcome to pay me. Each one of these reports takes weeks to months of full-time work.
Curious to hear your thought on it! Would love a Jepsen style analysis of kdb
Of course maybe this has already happened - and you are not able to discuss because of NDA. Which would be perfectly fine I think.
It is a basic honesty test, because the uncertainty and difficult to reproduce things can be used for denialism by the proponents / salespeople.