How do I run reporting queries without hurting production PostgreSQL?

To run reporting queries on PostgreSQL without hurting production, the main goal is to keep heavy read workloads off the primary and make sure reporting queries can’t compete with OLTP traffic for CPU, memory, I/O, or locks.

Best practice: use a read replica

The safest approach is to run reporting against a physical streaming replica.

Why this helps

• Reporting reads don’t consume primary CPU/I/O
• Long-running queries won’t block writes on the primary
• You can tune the replica for analytics/reporting separately
• You can even tolerate slightly stale data

Common pattern

• Primary handles writes and critical reads
• Replica handles dashboards, BI tools, exports, ad hoc analysis

---

If you can’t use a replica

Then you need to limit reporting impact on the primary:

1. Use a separate reporting role

Give reporting users read-only access only:

GRANT CONNECT ON DATABASE mydb TO reporting_user;
GRANT USAGE ON SCHEMA public TO reporting_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting_user;

2. Set query timeouts

Prevent runaway reports:

ALTER ROLE reporting_user SET statement_timeout = '30s';
ALTER ROLE reporting_user SET lock_timeout = '2s';

3. Limit concurrency

Don’t let many reports run at once. Use:

• application-side throttling
• a connection pooler
• queueing for BI jobs

4. Put reporting in a separate pool

Use PgBouncer or your app to keep reporting connections isolated from production app traffic.

---

Reduce query cost

Even on a replica, bad reports can be expensive.

Make sure queries are index-friendly

• Avoid full table scans if possible
• Use indexes on filters, joins, and sort columns
• Check with EXPLAIN (ANALYZE, BUFFERS)

Pre-aggregate

For dashboards, consider:

• materialized views
• summary tables
• scheduled rollups
• incremental aggregates

Example:

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day, sum(amount) AS total
FROM orders
GROUP BY 1;

Refresh it on a schedule:

REFRESH MATERIALIZED VIEW daily_sales;

If you need concurrent reads during refresh:

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

(Requires a unique index on the materialized view.)

---

Make production safer

1. Set sensible resource limits

On a reporting replica, tune separately:

• work_mem
• shared_buffers
• effective_cache_size
• max_parallel_workers_per_gather

Be careful: too much work_mem with many concurrent queries can blow memory.

2. Avoid large sorts/hashes on primary

Queries with big ORDER BY, GROUP BY, DISTINCT, or joins can be expensive. Prefer:

• precomputed tables
• partial aggregations
• narrower result sets

3. Prevent accidental writes

Use read-only transactions:

BEGIN READ ONLY;
-- reporting query
COMMIT;

Or set on the role:

ALTER ROLE reporting_user SET default_transaction_read_only = on;

---

Protect the primary from replica lag surprises

If using a replica:

• monitor replication lag
• don’t use the replica for reports requiring real-time data
• route only latency-tolerant reporting there

---

Good architecture options

From safest to least safe:

1. Dedicated reporting replica
2. Data warehouse / ETL to separate system
3. Materialized views / summary tables on primary
4. Direct ad hoc reporting on primary only for small/rare queries

---

Practical recommendation

If you want the simplest robust setup:

• Add a physical read replica
• Point BI/reporting tools to it
• Add statement_timeout and default_transaction_read_only
• Use materialized views for common dashboards
• Monitor slow queries and replication lag

If you want, I can help you with:

• a PostgreSQL replica setup
• query tuning patterns
• a safe reporting role configuration
• or a decision tree for when to use replicas vs materialized views vs a warehouse