How to Control Read Restart Errors in YugabyteDB

Distributed SQL databases like YugabyteDB guarantee correctness across nodes, even when clocks drift, and sometimes that means YSQL will stop a query and ask you to retry it. These read restart errors protect you from accidentally reading stale data.

Starting in YugabyteDB 2024.1.1.0, the YSQL layer introduced new, more flexible ways to tune how strictly this guarantee is enforced.

This Tip explains:

● Why read restart errors happen
● How YugabyteDB guarantees read-after-commit visibility
● The different visibility modes (strict, relaxed, and deferred)
● When to use each mode and when not to
● Real-world examples (production vs. DBA/maintenance scenarios)

🔍 Understanding Read Restart Errors

YugabyteDB promises:

Any read must see all data committed before the read began, even when nodes have clock skew.

To maintain this guarantee, YSQL compares:

● The commit time of each row
● The read time chosen for the transaction

If clock skew makes those timestamps ambiguous, YugabyteDB cannot safely confirm the row should be visible…. it refuses to guess and instead returns:

				
					ERROR:  Query error: Restart read required
SQLSTATE: 40001

This is intentional: returning a stale result would violate correctness.

⚙️ Where the Tuning Happens (Cluster Flags vs. Session GUC)

You can choose how strictly YSQL should enforce the read-after-commit visibility guarantee. The setting exists at two levels:

● Cluster-level setting (masters + tservers)
- ▣ Flag: ysql_yb_read_after_commit_visibility
  - ◦ Default: strict (PG-like safety-first semantics)
  - ◦ Must match on all masters and tservers
  - ◦ Controls the default behavior for every YSQL session
● Session-level GUC (yb_read_after_commit_visibility)
- ▣ Inside any YSQL client:

				
					SHOW yb_read_after_commit_visibility;
SET yb_read_after_commit_visibility = 'relaxed';

- - - ◦ Reasons to use the session-level override:
      - ▪ Avoid read restarts for a specific workload
        ▪ Avoid changing cluster-wide behavior
        ▪ Enable special modes only for DBAs, scripts, or maintenance tools

This Tip focuses mostly on the session-level GUC, which gives you fine-grained control without touching cluster flags.

📘 The Modes: `strict`, `relaxed`, and `deferred`

● strict : the Default (and safest)
- ○ This mode enforces full read-after-commit visibility.
  - - ▪ Ensures no stale reads
    - ▪ Raises read restart errors when timestamp order is ambiguous
    - ▪ Matches PostgreSQL expectations
    - ▪ Used in all OLTP/production systems by default
- ✔️ When to Use strict
  - - ▪ User-facing applications
    - ▪ Banking, financial, and ordering systems
    - ▪ Microservices that assume “commit → then message → then read must show it”
    - ▪ Security and audit workloads
    - ▪ Anything requiring strong external consistency
- ❌ When NOT to change away from strict
  - - ▪ Never change this cluster-wide just to “silence” read restarts
    - ▪ Never use a weaker mode for unpredictable, customer-visible queries

● relaxed — reduce read restarts, accept possible staleness
- ○ Introduced in YugabyteDB 2024.1.1.0-b137
- ○ This mode tells YSQL: “If you can’t guarantee perfect visibility, return a result anyway.”
- ○ In this mode you may miss very recent commits, but you greatly reduce the chance of read restart errors.
- ○ Relaxed mode is only for read only statements/ transactions.
  - ▪ For read-only statements, the user doesn’t have to tag it as read-only, the db auto detects it read-onlyness.

- - Example:

				
					SET yb_read_after_commit_visibility = 'relaxed';

SELECT * FROM large_table WHERE category = 'X';

- ✔️ When relaxed is a good idea
  - ▪ yb_index_check() – The docs explicitly recommend using relaxed here

				
					SET yb_read_after_commit_visibility = 'relaxed';

SELECT *
FROM yb_index_check(
  'public.my_table',
  'public.my_table_idx',
  p_options := 'phase=all'
);

- - - ◇ Index checks do not depend on “latest commit” visibility
    - ◇ You just need a self-consistent snapshot
    - ◇ Avoiding restarts dramatically speeds up checks on large datasets
  - ▪ Large analytical reads
    - ◇ For example:

				
					SET yb_read_after_commit_visibility = 'relaxed';

SELECT customer_id, COUNT(*) 
FROM events
WHERE event_ts >= now() - interval '1 day'
GROUP BY customer_id;

- - - ◇ If these analytics run every few minutes/hours and aren’t customer-facing, a few missing seconds (max allowed clock skew to be precise (default of 500ms) (See: max_clock_skew_usec)) of writes is acceptable.
  - ▪ Background jobs + maintenance
    - ◇ Database cleanup tools
    - ◇ Validation passes
    - ◇ Monitoring collectors
    - ◇ Periodic reports not used for financial/audit purposes
- ❌ When NOT to use relaxed
  - ▪ User-facing reads
  - ▪ APIs depending on strict correctness
  - ▪ Systems interacting with Kafka/CDC events
  - ▪ Any workload where “read-your-own-write” is required
  - ▪ Regulatory or audit reporting
  - ▪ Anything customers see directly

● deferred: advanced (YugabyteDB 2024.2+)
- ○ Introduced in YugabyteDB 2024.2.6.0-b94
- ○ A mode that can defer the actual read time for both read and write transactions, helping avoid read restart errors.
- ○ Designed to reduce restarts by delaying reads until safe
- ○ Best suited for transactions that:
  - ▪ Can tolerate a small latency delay
    - ◇ Note: The latency is always about 500ms (See: max_clock_skew_usec)
  - ▪ Cannot tolerate a restart
- ○ Guidance:
  - ▪ Treat as advanced unless your release notes explicitly endorse it for your version
  - ▪ Test thoroughly before using in production
  - ▪ Avoid using it globally… control it at the session level

🧪 Side-by-Side Scenario: How Each Mode Behaves

You run this reporting query:

				
					SELECT customer_id, COUNT(*)
FROM orders
WHERE created_at >= current_date - interval '1 day'
GROUP BY customer_id;

Mode: strict

● May raise: Restart read required
● You retry the query
● Guarantees no stale reads

Mode: relaxed

● Query avoids restart
● Result may miss a few very recent orders
● Fine for internal dashboards or nightly batch jobs

Mode: deferred

● Query will wait for a safe timestamp
● Avoids restarts while ensuring correctness
● Latency is higher; requires careful testing

🛠️ Practical Recommendations

✔️ 1. Keep the cluster default: strict

- This ensures all applications get strong consistency by default.

✔️ 2. Use relaxed only for safe, isolated workloads

- ● Prefer session-level:

				
					SET yb_read_after_commit_visibility = 'relaxed';

- ● Typical safe workloads:
  - ○ Index checks
  - ○ DBA maintenance tools
  - ○ Backfill jobs
  - ○ Large internal analytics

● Try using the available transaction isolation levels before touching visibility settings
- ○ READ COMMITTED (recommended first)
  - ▪ Greatly reduces restarts caused by retries without sacrificing correctness.
  - ▪ YugabyteDB will also retry automatically under REPEATABLE READ if the statement is the first statement in the transaction block.
- ○ ○ SERIALIZABLE
  - ▪ Avoids all read-restart errors.
  - ▪ However, it effectively locks everything it reads, which can be very expensive for workloads that scan or read large volumes of data.

- ● Use deferred only when recommended by release notes
  - ○ And keep it isolated to specific sessions.

🧭 Summary

In YugabyteDB, read-restart errors are a safety mechanism… not a flaw.

When clock-based ambiguity arises, the database protects correctness by asking the client to retry. With the introduction of yb_read_after_commit_visibility and the new deferred mode, you now have fine-grained control over how strictly the system enforces read-after-commit guarantees.

Keep strict for customer-facing and correctness-critical paths, and selectively relax visibility only for internal jobs or workflows that can tolerate brief staleness. Used thoughtfully, these settings let you reduce noise from read restarts while preserving the strong consistency guarantees that make YugabyteDB dependable at scale.

Special thanks to Piyush Jain, Staff Engineer @ YugabyteDB, for reviewing this tip for accuracy!

Have Fun!