Solve: When SELECT Queries Hit the Wrong Aurora Node
Solve: When SELECT Queries Hit the Wrong Aurora Node
Originally published on Medium by Aaron Rose
* * *
Amazon Aurora PostgreSQL is a marvel of modern infrastructure. It replicates data across multiple availability zones. It scales up and down with near-instant elasticity. It provides multiple endpoints so your app can route reads and writes efficiently.
But when SELECT traffic starts slamming the writer instance, all that elegance falls apart. Transactions slow down. Analytics jobs pile up. Failovers take longer than they should. Aurora gave us the tools — but somewhere, they got ignored.
Many teams run into this without realizing it. Their ORM uses a single connection string. The DevOps team handed them a default config. And suddenly, an architecture that was built for distributed traffic is running like a single-node PostgreSQL install from 2008.
The Invisible Drain on Your Writer Node
Your writer node is meant to handle writes — INSERTs, UPDATEs, DELETEs, and DDL changes. The reader nodes are there to pick up the load for SELECTs. But what happens when your dashboards, bots, and analytics tools all keep sending SELECT queries to the writer?
You get:
- Increased CPU and memory pressure
- Contention between reporting and transactional logic
- Higher latency on mission-critical writes
- Instability during failover or load spikes
And unless you’re looking for it, it might take you weeks to realize what’s happening.
A Simple Tool to Catch the Pattern
To help uncover this misrouting, we built a lightweight diagnostic script. It runs in your terminal, connects to your Aurora instance, and shows you whether your SELECT queries are hitting the writer or the readers.
It’s beginner-friendly, uses a menu system, and runs cleanly in a virtual environment. Here’s what it looks like in action:
==============================
Aurora Query Routing Checker
==============================
1) Check instance role
2) Show SELECT queries
3) Exit
Select an option: _If you choose option 1:
This instance is: WRITERAnd if you choose option 2:
-------------------------------
SELECT Queries on This Node
-------------------------------
[Instance Role: WRITER]
1. app_user 10.1.4.28 SELECT id, name FROM produ...
2. bot_service 10.1.8.55 SELECT count(*) FROM logs ...
3. metrics_read 10.1.6.14 SELECT * FROM sessions WH...
(3 queries shown — limited view)
Note: SELECTs are hitting the WRITER. Consider routing them to read replicas.The goal isn’t just to report — it’s to highlight a misalignment between architecture and behavior. This is especially helpful for newer teams or those maintaining legacy code where the original database strategy has been forgotten.
Designed for the Real World
You don’t need to run it every day. But when something starts feeling sluggish — and you’ve ruled out obvious problems like CPU saturation or connection pool exhaustion — this can give you a quick gut check.
And yes, it works even if you inherited the cluster and have no idea what’s hitting it.
A Design Problem, Not a Cloud Problem
The deeper point here isn’t just about SELECT queries. It’s about how cloud-native databases give us tools we don’t always use. Aurora offers multiple endpoints for a reason. When we ignore that, we lose the very benefits we’re paying for.
This problem shows up again and again in serverless infrastructure. Logical database design doesn’t automatically translate to operational excellence. You can follow all the best schema advice and still fail if your routing logic isn’t in place.
And that’s not on the cloud. That’s on us.
What To Do Next
You can grab the script, walk through the setup, and run your first diagnostic session by following our full walkthrough and tool download.
Whether you use our tool or build your own, the key takeaway is this: make sure your SELECTs know where to go. Aurora’s trying to help you. Let it.
* * *
Aaron Rose is a software engineer and technology writer.
Comments
Post a Comment