goagentdata

Should your dashboards read from the production database?

TL;DR. Not with the application's own login. Before launch, give the dashboard tool its own read-only database user with a time limit on every query. Once dashboards refresh on a schedule, or more than a couple of people ask questions, give analytics its own copy of the database, called a read replica. A warehouse can wait until you need to join billing and CRM data, or keep history your app overwrites. The replica has two quiet failures of its own, and both have a check below.

Three ways a dashboard can read your production database Panel 1: the app and a dashboard share one database and one login; a heavy dashboard query slows the app, and the login can also change data. Panel 2: the dashboard gets its own read-only user with a 30 second limit; the heavy query is stopped at 30 seconds and the dashboard cannot change data. Panel 3: the dashboard reads a copy of the database, a read replica, which receives every change from the main one; the copy shows how far behind it is, and a long report on it can be cancelled. Illustration of the setup, not measured data. 1. One database, one login App Dashboard Production A heavy report slows the app. The same login can also change or delete data. 2. Its own read-only user App Dashboard read only Production Dashboard queries stop at 30 s. The user cannot change data. Enough before launch. 3. Its own copy App Dashboard Production Replica data as of 09:02 Watch two quiet failures: long reports cancelled halfway, and a copy that falls behind. Start with panel 2. Move to panel 3 when dashboards run on a schedule.
Illustration of the setup, not measured data.

What is the real problem?

Your production database is the one your product writes to. It was set up to answer many small questions fast: load this order, save this payment. A dashboard asks a different kind of question: add up every order since January, by customer. That question reads a large part of the table, and while it runs it competes with your customers for the same machine.

Picture an online store with one engineer. She connects a dashboard tool to the app database with the same login the app uses, because it is the login she has. The dashboards work. A month later five of them refresh every morning at nine, checkout slows down at nine, and nobody connects the two. The same login can also delete rows, so one wrong query from the tool, or from an AI agent given that login, can change customer data.

We tested the first half once, on a small machine. With the app writing alone, a write took 53 milliseconds on average. With six heavy report queries running beside it, the same write took 248. That is one run on a two-core test machine with made-up data, so read it as an illustration of the direction, not a number to plan with.

What should you do at each stage?

I think the trigger is not company size. It is who else reads the database, and how often.

Before launch. Keep one database. Create a separate user for the dashboard tool and for any agent, allowed to read and nothing else, with a time limit of 30 seconds on every query and a cap on how many connections it may open. This takes ten minutes and removes the two worst outcomes: a tool that can change data, and a query that runs for an hour.

First paying customers. Once dashboards refresh on a schedule, or more than a couple of people ask questions, give analytics its own copy. Hosted PostgreSQL services such as Supabase, Amazon RDS and Azure offer a read replica: a second database that receives every change from the first and accepts only reads. Supabase, which sells replicas, lists analytics queries that hurt production as the first reason to add one, in a guide dated 15 January 2026. A nightly export to a separate database also works if yesterday's numbers are good enough.

Growing, with a first data person. A warehouse (a database built for large reports) earns its place when the work asks for it: you need billing, CRM and product data in one query, or you need history the app does not keep. If your app overwrites a customer's plan when it changes, a replica copies the overwrite too. No copy brings back what was never kept.

What goes wrong, and how do you catch it?

A replica moves the load. It also brings two failures that look like nothing happened. A third applies with or without a replica.

A long report is cancelled halfway. The replica has to keep applying changes from the main database. When a long report still needs rows the main database has already cleaned up, PostgreSQL waits, by default 30 seconds, and then cancels the report. Microsoft's guide for its hosted PostgreSQL describes the same default and the same error. In our test, a report built to run 75 seconds was cancelled after 34, with the message "canceling statement due to conflict with recovery". A scheduled report that fails this way can leave yesterday's number on the dashboard, and nobody sees an error.

GuardrailTurn on the setting that tells the main database which rows the replica still needs, or allow a longer wait on a replica used only for reports. The setting has a cost: the main database keeps old rows, and uses more disk, while long reports run. With it on, the same 75 second report finished.

CheckAsk your host for the count of cancelled queries on the replica, once a week. A count that grows means reports are failing quietly.

The copy falls behind. Two people read the same number at the same minute, one from the app and one from the dashboard, and the numbers differ, because the replica has not caught up.

GuardrailShow "data as of" on every dashboard that reads a copy.

CheckLook at replica lag in your host's console during your busiest hour.

The dashboard login can write. A tool or an agent runs a delete, and it works.

GuardrailA read-only user that owns nothing.

CheckLog in as the dashboard user and try to change one row. It must fail.

For your data team

For your data team

This part gets technical. The SQL below ran on PostgreSQL 16.

-- A user for BI tools and agents: read only, time-limited, capped.
create role bi_reader login connection limit 5;
grant connect on database app to bi_reader;
grant usage on schema public to bi_reader;
-- If any table holds passwords, tokens or personal data, grant table by table instead.
grant select on all tables in schema public to bi_reader;
-- Tables your app creates later: run this as, or for, the role that runs your migrations.
alter default privileges for role app_owner in schema public grant select on tables to bi_reader;
alter role bi_reader set statement_timeout = '30s';
alter role bi_reader set idle_in_transaction_session_timeout = '60s';
alter role bi_reader set default_transaction_read_only = on;

The grants are the lock, and as written they open every table in the schema, so check what is in there first. default_transaction_read_only is a seat belt the session can switch off, and in the test an update then failed with "permission denied", which is what you want. The time limit works the same way: a session, or an agent, can raise it. The connection cap and, later, the replica are what hold the load. Without the for role line, a table your app creates next month gives the dashboard user "permission denied", and the dashboard breaks.

-- Who reads the primary, and for how long?
select usename, application_name, state,
       count(*) as connections,
       max(now() - query_start) as longest_running
from pg_stat_activity
where datname = current_database() and pid <> pg_backend_pid()
group by 1, 2, 3
order by connections desc;

Set application_name in each tool's connection string, so this query names the tool and not only the user.

-- On the replica: cancelled queries, and how far behind it is.
select confl_snapshot, confl_lock, confl_bufferpin
from pg_stat_database_conflicts
where datname = current_database();

select pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() as caught_up,
       now() - pg_last_xact_replay_timestamp() as since_last_change;

Read the two lag columns together. In the test, since_last_change showed 49 seconds on a replica that was fully caught up, because the main database had been idle for 49 seconds. Alone, that column raises false alarms.

For reports that run longer than 30 seconds on a replica, the PostgreSQL documentation gives two remedies. hot_standby_feedback = on stops these cancellations and can leave dead rows on the primary for as long as the report runs. A larger max_standby_streaming_delay lets the report finish and lets the replica fall behind while it does. I would pick by what the replica is for: feedback when the replica also serves fresh reads, a long delay when it serves only reports.

Check it in 10 minutes

  1. Ask which login your dashboard tool and your agents use. If it is the app's login, create a separate one today.
  2. Log in as that user and try to change one row. It must fail.
  3. Ask whether that user has a time limit. If not, set 30 seconds.
  4. No coding agent and no repository? This step is for you. Open the SQL editor in your database host's console, or any database client, as the admin user. Paste the first block from "For your data team" and change app to your database's name, and app_owner to the user that runs your migrations (often the same user your app connects with). Your host's monitoring page for the replica shows its lag.
  5. If you have a replica, ask for the count of cancelled queries and for the lag at your busiest hour. The engineer who set up the dashboard tool owns this user and these checks.

See where your setup stands with the two minute self-check, then read the draft checks your coding agent can run.

Spotted a mistake or a newer source? Tell us and we check it against the source. How we work.

Check your setup in 2 minutesAll postsGet new posts by email