Implementing Stripe-Like Idempotency Keys in Postgres

182 41 9 years ago

aejnsn• 9 years ago

Craig, attended your talk at ATO earlier this week--good talk. I was the guy who asked about performance considerations of joins in Postgres using UUIDs.

This post reads like it's vaguely implementing what should be in a queue backend. There's a locked_at field in the schema, furthermore should this not be performed via ...FOR UPDATE?

skrebbel• 9 years ago

Care to share the answer to your question about uuid join performance? I've had a hard time finding much about that on the internet.

craigkerstiens2• 9 years ago

Sure, there is definitely a little extra overhead on the join performance, though my experience I've seen so many other issues become the biggest bottleneck before joins of UUIDs. We regularly used UUID as identifies at Heroku Postgres and use them at Citus as well and they work extremely well for us.

It is of note that we're actually using the UUID datatype though and not just generating a UUID and throwing it into a text field.

craigkerstiens2• 9 years ago

Hi online :)

I'll wait a bit to see if Brandur (author chimes in first), otherwise happy to pile in some thoughts.

brandur• 9 years ago

This post reads like it's vaguely implementing what should be in a queue backend. There's a locked_at field in the schema, furthermore should this not be performed via ...FOR UPDATE?

Yeah, I'm relying on transactional semantics to guarantee no contention during the lock, but the part that I'd intended as important here is really just the idea of locking the key — not the particular implementation. I think alternatively using a `FOR UPDATE` is totally plausible and likely even to be more performant than what I've written here.

Try to imagine this as more of a "ideas guide" rather than a dictation on precise implementation.

poorman• 9 years ago

I always throughly enjoy reading Brandur's articles.

Dowwie3• 9 years ago

Why not use Twitter's snowflake id? Instagram blogged about a Postgres function that generates them

http://rob.conery.io/2014/05/28/a-better-id-generator-for-po...

bgentry• 9 years ago

Which of the problems covered in this post would be solved by Snowflake?

naasking6• 9 years ago

"Idempotency keys", sheesh. Just use standard terminology: these are just futures/promises.

pbreit• 9 years ago

Futures/promises means nothing to me.

But I always thought an idempotency key was just sending in a time stamp or something else unique.

naasking6• 9 years ago

Time stamps are not unique, you definitely need something unique, like a GUID to distinguish it from such tokens bound to other client requests.

And you'd be better served learning about the more general abstraction rather than learning a single domain-specific trick. This solution is obvious once you know about promises.

dboreham2• 9 years ago

Wait...what? I'm all for "standard terminology" but they're for sure just not the same thing. At all.

naasking6• 9 years ago

Sure they are. The idempotency key is simply a representation of a promise. The E language and the Waterken server implement promise semantics exactly like this: bind the results of a local or remote computation to a durable representation, and that representation is used to store the result upon completion. All future requests return the that stored result, ie. idempotency.

In case of network partition where the client doesn't receive a reply, it can safely and simply retry the operation on the promise, which returns the stored result.

The E language popularized futures/promises, and it's still the gold standard.

koolba• 9 years ago

This is a fantastic article.

sandGorgon• 9 years ago

how do you guys implement atomic_phase ? im not a ruby guy, so am not able to figure out the definition.

It looks like a atomic check-and-update, but more sophisticated.

no1youknowz2• 9 years ago

I'm using Memsql and it doesn't seem to have a UUID type, but after a quick search with Mysql and UUID, the binary type is being used.

Can this be used with Memsql with the binary type and will I get the same benefits?

Thanks

positr0n• 9 years ago

Yes I've seen uuids in databases in both binary and varchar(36) forms. Interacting with them with your tools/ORM won't be as nice as the native type, but it's definitely doable and you get most of the benefits.

no1youknowz2• 9 years ago

Don't use an ORM, but otherwise thanks for the clarification!

ianamartin• 9 years ago

This is a really great article. I especially like that the author pointed out some of the pitfalls with non-acid systems.

I started wondering at the beginning of the article how many people were going to read it, try to implement it in mongo, and faceplant.

The only thing that strikes me as odd about the hypothetical example is that I can’t imagine a universe where you would want a request for a ride to complete 72 hours after it initially failed.

I get the point of keeping the information around for 3 days in case of errors. And perhaps my imagination is lacking, but most of the cases where I would want to implement this model are time-sensitive. I need a ride now. I need a message guaranteed to deliver exactly once.

Actually, the message queue aspect is how I think of this in general. It’s an application/database layer that takes an at least once mq system and forces it to be at most once. The combination then seems to imply exactly once.

Actually, after writing that out l, I can now think of a really great use case for this that isn’t time sensitive. ETL systems.

Let’s say you are streaming live updates from some third party system to some staging area and triggering processes from there through a broker to transform and load data into a warehouse.

Your team pushes an update that breaks something on Friday night. Nobody works that much on the weekend, so no ones cares. The dev lead gets notifications, but gets to take it easy until Monday morning because they (trigger warning: intentional singular they) know that everything can be replayed and resolved after the bug is fixed. Even in the case of frequently changing api data, you have the entire mutation history stored in your table and can accurately replay it.

I can get on board with this.

And yeah. Sorry for thinking out loud. Now I’m thinking of all kinds of place in my web apps this can be applied, as the article suggests. I think I need to write a Python wrapper around this concept and make it dead simple to include in projects.

Thank you again to the author for this very clear and excellent article.

mazerackham• 9 years ago

This is a really great article that lays out a proper implementation of idempotent requests, something that many people (even very experienced developers) regularly mess up. Thanks for sharing

Implementing Stripe-Like Idempotency Keys in Postgres

Related Stories