Course contentsShow
← System Design
Lesson 223 of 1,9196. Database Scaling: Read ReplicasFree lesson

Detecting Read vs Write Queries

Techniques for classifying SQL statements as reads or writes, including edge cases like SELECT FOR UPDATE.

Detecting Read vs Write Queries

You'll learn: How systems automatically classify SQL statements as reads or writes to route them to the right database server.

When you implement read-write splitting, your application or proxy needs to intelligently decide: "Should this query go to the primary or a replica?" This requires query classification—examining the SQL statement to determine if it modifies data (write) or just retrieves it (read).

Basic Classification

Most detection is straightforward:

  • Writes: INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
  • Reads: SELECT statements

A simple parser can look at the first keyword and route accordingly. Writes always go to the primary; reads can go to replicas.

The Tricky Edge Cases

Not all SELECT statements are safe for replicas:

  • SELECT ... FOR UPDATE: Locks rows for modification, must go to primary
  • SELECT ... FOR SHARE: Also uses locks, primary-bound
  • Transactions with mixed operations: Even a SELECT inside a transaction that will later UPDATE should stay on primary for consistency
  • Database-modifying functions: SELECT my_update_function() looks like a read but isn't

These edge cases require deeper SQL parsing, not just keyword matching. Some systems use allowlists (only specific read-only queries go to replicas) rather than trying to catch every exception.

Analogy: It's like sorting mail into "urgent" and "regular" bins—most letters are obvious, but some marked "urgent" require reading the content, not just the envelope.

Key Takeaway: Query detection requires parsing SQL to classify reads vs writes, with special attention to locking clauses like FOR UPDATE and transactional context that make seemingly read-only queries unsafe for replicas.