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:
SELECTstatements
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 primarySELECT ... FOR SHARE: Also uses locks, primary-bound- Transactions with mixed operations: Even a
SELECTinside a transaction that will laterUPDATEshould 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.