[antoineleclair]

For projects where I know the team will remain small (less than let's say 15 developers), I usually push to keep the architecture as simple as possible.

I've used something similar in the past, but kept the expiration code in the app code (Python) instead of using "fancy" Postgres features, like stored procedures. It's much easier to maintain since most developers will know how to read and maintain the Python code, that's also commited to the git repository.

Also, instead of using basic INSERT statements, you can "upsert".

INSERT INTO cache_items (key, created, updated, key, expires, value) VALUES (...) ON CONFLICT ON CONSTRAINT pk_cache_items DO UPDATE SET updated = ..., key = ..., expires = ..., value = ...;

And since you have control over the table, you can customize it however you want. Like adding categories of cache that you can invalidate all at once, etc.

Postgres is also pretty good at key/values.

In other words, I agree that using Postgres for things like caching, key/values, and even maybe message queue, can make sense, until it doesn't. When it doesn't make sense anymore, it's usually easy to migrate that one thing off of Postgres and keep the rest there.

Also, one benefit that's not often talked about is the complexity of distributed transactions when you have many systems.

Let's say you compute a value inside a transaction, cache it in Redis, and then the transaction fails. The cached valued is wrong. If everything is inside of Postgres, the cached value will also not be commited. One less thing to worry about.

[VWWHFSfQ]

> Let's say you compute a value inside a transaction, cache it in Redis, and then the transaction fails.

That just sounds like an application bug. Nothing should be done with the query result anyway until the transaction either completes our rolls back.

[paulmd]

As mentioned this is a distributed system though, and realistically most micro services etc aren’t being fully rigorous about multi-phase transactionality and proper rollbacks etc.

Realistically it is the norm to yolo updates at a service and if it fails then the whole thing 500s and things are just in an unexpected state. Often it is not even possible to guarantee successful rollback etc - if your update back to original state fails then what is the application state now? Undefined and potentially invalid, pretty much. Most people just replay the request again and hope it succeeds.

Obviously the right answer is “don’t do that” or “offload that complexity into graphQL or something” but in the real world… people don’t.

[antoineleclair]

Transactions can fail because they conflict with other transactions happening at the same time. It's not an application bug. It's real life transactions happening on a production system. It's normal for that to happen all the time. The app can retry, etc., but it should be expected to happen. Having to deal with distributed transactions is not something easy. Especially when they're part of many different systems. For example, you'd have to wait until the transaction commits successfully before setting the value in the cache, which makes it hard to read. Also, life in general happens. Compute a value, cache it, save things to the database, make API calls, and then a network error happens cancelling everything that you've just done. Having code that handles this kind of possibility is relatively hard to write/read.

[VWWHFSfQ]

Right but postgres isn't going to help with this if the application developer isn't doing safe and proper transaction management in the first place. What you described is a bug in the application logic for when and how to update the cache.

[singron]

It's super hard to get this right. E.g. if you only update the cache after the transaction commits, you might commit without updating the cache, or if 2 writers interleave, the first one to commit might make the final update to the cache with a stale value.