Postgres UUIDv7 and per-back end monotonicity

I maintain that people are too eager to use UUIDv7 to begin with. It's a dessert topping and a floor wax.

Let's say you need an opaque unique handle, and a timestamp, and a monotonically increasing row ID. Common enough. Do they have to be the same thing? Should they be the same thing? Because to me that sounds like three things: an autoincrementing primary key, a UUIDv4, and a nanosecond timestamp.

Is it always ok that the 'opaque' unique ID isn't opaque at all, that it's carrying around a timestamp? Will that allow correlating things which maybe you didn't want hostiles to correlate? Are you 100% sure that you'll never want, or need, to re-timestamp data without changing its global ID?

Maybe you do need these things unnormalized and conflated. Do you though? At least ask the question.

Re-timestamp would be a new one for me. What’s a conceivable use case? An NTP fault?

Also if you have a nanosecond timestamp, do you actually need a monotonically increasing row ID? What for?

You can keep all three and still use UUIDv7 as a performance improvement in certain contexts due to data locality.

Perhaps as a tie breaker if you insert multiple rows in a table within one transaction? In this situation, the timestamp returned by e.g. `now()` refers to the start of the transaction, which can cause it to be reused multiple times.

I meant in a context with a random ID and a timestamp.

If you need to retrieve rows by time/order, you use the timestamp. If you need a row by ID, you use the ID.

The use-cases where you actually need to know which row was inserted first seem exceedingly rare (mostly financial / auditing applications), and even then can probably be handled with separate transactions (as you touch on).