[YSQL] Add an option to clamp the read uncertainty window only for long reads

[pao214]

Jira Link: DB-10598

Description

Problem

Task #20336 adds an option to avoid kReadRestarts by removing the uncertainty window entirely. This can lead to observing stale data since the reads may incorrectly miss earlier updates.

Proposal

Instead, provide an option to clamp the window only for long reads. This lets us observe recent updates for small reads.

Impact

Short Read Scenarios

1. Single row/shard reads. Here, the read goes to DocDB and can be restarted there without any help from postgres main loop for restarts.
2. No data is sent back to the client yet. We can restart this query transparently to the client.

Optimize read uncertainty window

Clamping the uncertainty window is undesirable for long reads too because we lose some recency guarantees. In fact, higher latencies are not an issue with longer reads. Hence, we need to identify long read scenarios and prefer picking a high read timestamp to minimize the read uncertainty window (best-effort).

Solutions discussed in #20336, while great, incur too high of a latency. This is undesirable if we are not accurate in determining whether a SQL statement is expected to take a long time or not.

Proposed middle ground:

1. Simply take the maximum of local times in
   (a) participating nodes and
   (b) up to a certain deadline (50ms wait time?)
   This does not prevent all read restarts. Example: tablet splitting, load balancing or leader changes. However, it avoids a read restart in the common case.
2. Do this only when we anticipate that the SELECT statement takes a long time to execute.

Issue Type

kind/enhancement

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.