Solve: Diagnosing Aurora PostgreSQL Query Routing Issues

Solve:  Diagnosing Aurora PostgreSQL Query Routing Issues

I. The Quiet Cost of Misrouted Queries

When your Aurora PostgreSQL cluster starts slowing down, the first thing many engineers think about is scaling. Maybe your app grew. Maybe you need a bigger instance. But sometimes, the issue isn't size—it's traffic routing.

Aurora is designed for performance, but only if you use its features the way AWS intended. One of the most important—but often overlooked—tools in your belt is the reader endpoint. Aurora clusters come with both a writer endpoint and a read-only endpoint, and each serves a different role:

• The writer handles all data changes—INSERTs, UPDATEs, DELETEs, etc.
• The readers are for SELECTs—reporting dashboards, analytics, background jobs, and any operation that doesn't change data.

If your application sends everything to the writer—reads and writes—you’re unintentionally turning your write-optimized instance into a bottleneck. You’re stacking up SELECTs where they don’t belong, and in return you get:

• Higher CPU and memory use on the writer
• Slower transaction times
• More risk during failover or peak load

Many teams don’t realize this is happening. They’ve inherited infrastructure. They’re using a single database connection string. Or their ORM doesn’t split read/write traffic. That’s not a failure of knowledge—it’s a failure of visibility.

So let’s fix that.

II. A Tool to Show You What’s Really Happening

Instead of guessing, you need a quick way to confirm:

• Are SELECT queries going to the writer instance?
• How many are running right now?
• What apps or users are sending them?

This post introduces a diagnostic tool—command-line based, menu-driven, and beginner-friendly—that shows you exactly where your traffic is going. You don’t need to install anything complicated. Just connect to your Aurora cluster and get a clean report in under 10 seconds.

Here’s what the user menu looks like: 

bash

==============================
 Aurora Query Routing Checker
==============================

1) Check instance role
2) Show SELECT queries
3) Exit

Select an option: _

If you select Option 1: 

bash

This instance is: WRITER

Option 2 gives you a focused report like this: 

bash

-------------------------------
  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.

This tool doesn’t just show you a problem—it helps you understand it. That’s especially important when you’re not sure what "normal" looks like yet, or when you’re learning how Aurora architecture works in production.

III. How Do We Fix It?

Once the diagnostic report confirms that SELECT queries are hitting your writer instance, it’s time to fix the routing. Aurora gives you the tools—you just have to apply them correctly.

There are three common ways to separate read and write traffic in Aurora PostgreSQL. You only need to choose one that fits how your application is set up today.

1. Point SELECT Traffic to the Reader Endpoint

Aurora clusters expose a dedicated read-only endpoint that automatically load-balances across all available replicas. You can fetch it like this: 

bash

aws rds describe-db-clusters \
  --query "DBClusters[?DBClusterIdentifier=='your-cluster-name'].ReaderEndpoint" \
  --output text

Sample output: 

bash

your-cluster.cluster-ro-abc123xyz.us-east-1.rds.amazonaws.com

Action: Update your application to use this endpoint for reads

bash

export READ_DB_HOST=your-cluster.cluster-ro-abc123xyz.us-east-1.rds.amazonaws.com
# Then point your read-only DB pool, reporting job, or ORM config here

2. Use RDS Proxy with Read-Only Database Users

Amazon RDS Proxy doesn’t automatically inspect queries, but it can route connections based on the database user. That means you can create a user that only runs SELECTs, connect it through the proxy, and keep write traffic separate.

Step 1: Create a read-only user inside the database 

sql

CREATE USER app_reader WITH PASSWORD 'strongpassword';
GRANT CONNECT ON DATABASE your_db TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;

Step 2: Point read traffic to the proxy endpoint

bash

aws rds describe-db-proxies \
  --query "DBProxies[].Endpoint" \
  --output text

Action: Use this endpoint with the read-only user for all SELECT operations 

bash

export READ_DB_USER=app_reader
export READ_DB_PASS=strongpassword
export DB_HOST=rds-proxy-read-endpoint.proxy-xyz.rds.amazonaws.com

3. Use PgBouncer to Route Based on Connection Pools

If your app doesn’t natively support read/write splitting, you can run PgBouncer locally or on a sidecar container to do it for you. PgBouncer doesn’t inspect queries by default, but it can run separate instances or connection pools for each role.

Sample config for read pool (``): 

.ini

[databases]
readonlydb = host=your-cluster-ro-abc123xyz.rds.amazonaws.com port=5432 dbname=your_db

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5

Start PgBouncer: 

bash

pgbouncer /etc/pgbouncer/pgbouncer.ini

Action: Connect your SELECT-only workloads to port 6432 

bash

export DB_HOST=127.0.0.1
export DB_PORT=6432

This works well in microservices, containerized workloads, or even on EC2 jump boxes where you want local traffic control without rewriting app logic.

Each of these fixes is simple once you see it, but hidden if you don’t know where to look. That’s what our diagnostic tool is for: to help you see clearly and take action with confidence.

IV. Summary: A Checklist for Fixing Aurora Query Routing

Now that you’ve seen the report and understand the cause, here’s a quick summary you can use with your team to plan the fix. Copy it, paste it into Slack or Notion, and go from red to green in one short session.

Aurora Query Routing Checklist

• Run the diagnostic script and check if SELECTs are hitting the writer
• Identify the services or users responsible for the SELECT traffic
• Choose one of the following solutions:
  
  - Use Aurora’s read-only endpoint for SELECTs
  - Create a read-only DB user and route through RDS Proxy
  - Configure PgBouncer to split read/write traffic

• Test routing with pg_is_in_recovery() to verify which instance is receiving the traffic
• Monitor replica lag (CloudWatch → AuroraReplicaLag) before moving latency-sensitive queries

Even if you’re still learning, this is a great first project to take ownership of. The difference it makes in real-world performance is immediate—and measurable.

Download the Aurora Query Routing Checker

You can grab the full Python script from our GitHub Gist. Just click the link below, copy the contents into a local file, and run it from your Python virtual environment:

👉 Download the script — aurora_query_check.py

Closing: Help Your Database Help You

You don’t need to be a senior engineer to solve a real production problem. What you need is visibility. This diagnostic tool doesn’t just help you debug—it helps you grow.

By letting Aurora’s writer do what it does best—handle changes—and letting the readers carry the SELECT load, you reduce risk, lower costs, and keep your services healthy under pressure.

If you have questions, suggestions, or real-world results, feel free to reach out—we're here to help, and this post was made with teams like yours in mind.

* * * 

Written by Aaron Rose, software engineer and technology writer at Tech-Reader.blog.