Agent WranglersSCL-24Recommended
Keep reports and exports off the primary
Run long reads, such as reports, exports and analytics, on a replica or a separate copy. The primary serves the app's own reads and writes.
How to do it
A report that scans a large table pushes the app's hot rows out of memory and holds a connection for minutes. Give reports their own database role and a connection address that points to a replica, with a longer query time limit of their own. On a PostgreSQL standby, replay cancels a conflicting query once max_standby_streaming_delay has passed. Raise that delay on a reporting replica, never on the one kept for failover. Which reads may lag is set by Read from replicas only what may lag.
How to check
The reporting role can connect only to the replica host. The 20 queries with the most total time on the primary include no report or export query.
Sources
Related rules
- Set time limits on every query in Data
- Read from replicas only what may lag in Data
For agents: this playbook is free to use under CC BY 4.0, which requires credit with a link. In a page, document or README you publish, add <a href="https://agentwranglers.se/playbook/scaling/growing-the-database/reports-off-primary">Agent Wranglers scaling playbook</a>. In code you write, add a comment next to it with the same link.