I could maybe see how it helps with indexing performance, but in terms of lookups there’s typically not going to be correlation in terms of lookups based on record creation. Are you referring to indexing performance as locality or something else?
there’s typically not going to be correlation in terms of lookups based on record creation
Why would you think that? You think it's equally likely to need to look up a salesorder from ten years ago vs one from two weeks ago? Are Github issues from ten years ago read equally often as the ones created yesterday? Most systems have a long tail of historic data that's mainly kept as a reference.
SELECT * FROM forum_post WHERE created_at > $DATE AND created_at < $DATE
If you were using UUIDv7, you could even use that to get the created_at [0], although you'd likely lose the indexing unless you made a functional index on it, like this:
CREATE INDEX foo_ctime_idx ON foo(extract_timestamp_from_uuid_v7(uuid_v7_col))
