Can Postgres timestamp be used to implement client side caching?

Question

If I have a pool of connections in a multi-threaded client sending out requests to the database server, can I rely on transaction or statement level timestamps to determine which data is most current?

For example, let's say I have a pair of transactions T1 and T2 which both return the same row which is updated in some way. Suppose T1 starts first. Is it possible for T2 to return stale data or complete earlier despite T1's timestamp being earlier? For instance, if T1 is slow and expensive, can it finish later and/or return data that is somehow more current than the data returned by T2?

Specific use case: Let's say we have a web server, a database server, and two different user agents connected to the web server. Let's say each user agent submits a mutation "simultaneously", and the web server wants to update the cache accordingly. How can we cache the correct result on the web server? By "correct," I mean the final result of these two mutations instead of the intermediate result.

Answer 1

I assume that your problem is that when you see a row with a certain timestamp in the table, you want to to be certain that a later query cannot find a new row with a smaller timestamp.

There is no good way to guarantee that unless you serialize processing, which would be bad for performance.

You might use a workaround: if you know that none of your transactions can take longer than 0.1 seconds, consider rows with a timestamp later than current_timestamp - INTERVAL '1 second' as not yet reliable. This is a heuristic that can go wrong (I/O overload might make your transactions slower than you think), but it should work out most of the time.