Read Replica vs Production Database: Where Should Analytics Queries Run?

Run analytics on a read replica whenever the queries are heavy, unpredictable, or written by someone other than the person on call. Point at production only when the dataset is small, the query volume is low, and you have a statement timeout in place. The tradeoff is simple: a replica costs you a few seconds of replication lag and one more thing to run, and it buys you the guarantee that no analytical query can ever slow down the database your customers are using. For most teams past their first few thousand rows, that trade is obviously worth it. For a small app with a quiet database, it is honestly not.

What a read replica actually protects you from

The risk people imagine is a query that corrupts data. That is not the real one. A read-only connection cannot write, so the database itself rejects any statement that tries. The real risk is resource contention: one badly shaped analytical query, a full table scan across two years of orders joined to events, holding CPU, memory, and I/O that your checkout endpoint needed 200 milliseconds ago.

An OLTP database is tuned for thousands of small, indexed, predictable queries. Analytics is the opposite: few queries, each enormous, each touching data no index was built for. Putting both on the same machine means your slowest analytical question and your fastest customer request compete for the same buffer pool. The replica gives the analytical workload its own machine to be slow on.

The decision rule

You do not need a formal framework here. Three questions settle it.

Notice the third one cuts the other way. Replication lag is the price of the replica, and for a small set of questions it genuinely disqualifies it.

What replication lag means in practice

A replica applies changes from the primary asynchronously, so it is always slightly behind. On a healthy Postgres or MySQL setup that is usually well under a second; under heavy write load or a long-running query on the replica it can stretch to seconds or, in bad cases, minutes.

For nearly every analytical question, this does not matter. "How much did we bill last month" does not change if the data is four seconds stale. Where it does matter is operational lookups that pretend to be analytics: a support agent checking whether a payment landed thirty seconds ago, or a fraud check on an account created moments earlier. Those belong on the primary, because they are not analytics; they are part of the transaction.

Lag also fails quietly, which is the uncomfortable part. Nothing errors. The numbers just get older while everyone keeps trusting them, so it is worth watching whether the replica is still keeping up the same way you would watch any other production dependency, rather than finding out from a confused finance question at month end.

Do I need a separate data warehouse instead?

Not necessarily, and this is where teams overspend. A replica and a warehouse solve different problems. A replica isolates the workload. A warehouse (Snowflake, BigQuery) changes the storage model to columnar, which makes large aggregations dramatically faster, and it lets you join data from systems your app database never had.

If your queries are slow because they compete with production traffic, a replica fixes it. If they are slow because you are aggregating 400 million rows in a row-oriented store, a replica just gives you the same slow query on a quieter machine, and a warehouse is the actual answer. Most teams under a few hundred million rows do not need the warehouse yet. If you are weighing that call, we walked through it in do you need a data warehouse to use an AI data analyst.

Can you use a read replica with an AI data analyst?

Yes, and it is the setup we recommend. An AI data analyst produces exactly the workload a replica is designed to absorb: unpredictable, occasionally heavy, written by someone who is not thinking about your index strategy. Pointing it at a replica means the worst case for a badly shaped question is a slow answer rather than a slow checkout.

Agentsql connects to a replica the same way it connects to any database: you give it a read-only credential and a host. It works against Postgres, MySQL, Snowflake, and BigQuery, and a replica endpoint is just a different host. Nothing about the connection changes.

How to set it up safely

The replica is the isolation boundary. The credential is the permission boundary. You want both.

1. Create the replica. On RDS, Cloud SQL, or Aurora this is a managed setting, not a project. Self-managed Postgres uses streaming replication; MySQL uses a replica with binlog replication.
2. Create a dedicated read-only role on the primary. It replicates down to the replica automatically. Grant CONNECT, USAGE on the schemas you want exposed, and SELECT on those tables only. Do not reuse the app's login.
3. Leave out what should not be readable. Password hashes, tokens, raw PII: withhold the grant, or expose a view with those columns removed. A grant you never issue is a control you never have to trust.
4. Set a statement timeout. A few minutes on the analytics role is plenty and it caps the blast radius of a runaway query.
5. Point the tool at the replica host. Restrict access by IP or private networking where you can.

That is the whole job. It is an afternoon at most, and the controls are enforced by the database rather than by anyone's promise. We covered the permission side in more depth in is it safe to give an AI access to your database and in how we handle read-only security.

Is a read replica more expensive?

Usually it roughly doubles the database line item, because a replica is another instance of comparable size. That sounds worse than it is. The replica does not need to match the primary in every dimension, and the cost it offsets is real: a production incident caused by an analytical query is more expensive than a second instance, once you count the engineer hours and the customer impact.

If cost is the binding constraint, a smaller replica is a legitimate middle ground. Analytics tolerates slower answers far better than checkout does.

When pointing at production is genuinely fine

There is a real case for skipping the replica, and it is not laziness. If your database is small enough that any query finishes in a second, your write volume is low, and the handful of people asking questions are the same people who would get paged, the replica is added operational surface for a risk you do not have yet.

Add a statement timeout, use a read-only role anyway, and get on with it. Move to a replica when one of the three answers above flips, which usually happens as soon as people outside engineering start asking their own questions.

Where teams get this wrong

The common mistake is treating the replica as the security control. It is not. A replica isolates load; it does nothing about permissions. Replicas carry the same tables and the same data, so an over-broad grant on the primary is an over-broad grant on the replica too. Anyone connected to a replica with a superuser credential can still read every password hash you have.

The other mistake is the opposite: a locked-down read-only role pointed straight at the primary, with everyone relaxed because it "can't write anything." True, and irrelevant at 9am on the first of the month when someone asks for a year-over-year breakdown and the query takes the API down with it. Isolation and permission are two separate problems. Solve both.

The short version

Use a replica once queries are heavy, unpredictable, or written by people outside the on-call rotation, which describes nearly every team that has started letting non-engineers ask their own questions. Accept a few seconds of lag, keep genuinely real-time lookups on the primary, and scope a read-only role regardless of which one you point at. Then let people ask.