Customer engagement dashboards have a peculiar performance profile: they are read-heavy, analytically complex, and used most heavily exactly when the core transactional system is under peak load. Running them on the same database as your write-heavy production workload is asking for trouble. This is the story of how we separated the two in ConnexxIQ.
The Problem: Competing Workloads
Our reporting queries were the kind that aggregate across millions of interaction records, join several tables, and compute rolling metrics over configurable date windows. On a shared database, they consistently caused query-plan contention and, occasionally, lock escalation on tables that the transactional system was actively writing to. Support tickets about slow dashboards would arrive precisely during the morning hours when sales teams were logging calls.
Analytic queries and transactional writes are fundamentally different workloads. The database does not know this. You have to tell it, structurally.
The Architecture: Streaming to a Read Replica
PostgreSQL's logical replication made the core solution straightforward. We promoted an existing warm standby to a dedicated analytics replica and configured it to receive replication from the primary. The critical configuration choices were:
- Route all reporting queries to the replica connection string — enforced at the ORM layer, not left to individual developers to remember.
- Set the replica's hot_standby_feedback to on, which prevents long-running analytics queries from being cancelled by vacuum on the primary.
- Accept a replication lag of up to 30 seconds for dashboard reads — explicitly documented in the UI as "data refreshes every 30 seconds" rather than hidden.
- Never write to the replica; configure the ORM to raise an exception if a write is attempted on the analytics connection.
Dealing with Replication Lag in the UI
The trickiest product decision was how to communicate replication lag to users without alarming them. "Data may be up to 30 seconds old" sounds like a bug if you do not frame it correctly. We reframed it as a feature: the dashboard shows a "last updated" timestamp and a quiet indicator that data is refreshing in the background. In user testing, this framing was universally accepted — users understood it as the same pattern as their email inbox.
Materialized Views for Pre-Aggregation
For the heaviest queries — the ones computing engagement scores across all contacts for a given account — we added a second optimisation: materialized views on the replica, refreshed on a schedule rather than on demand. This reduced the median dashboard load time from 4.2 seconds to 340 milliseconds for accounts with over 50,000 contacts. The tradeoff is a defined staleness window, which we expose in the product.
What the Primary Gained
The immediate effect on the primary database was measurable. Average lock wait time dropped by 62%. The p99 query latency for transactional operations — logging calls, updating contact records, sending messages — dropped by 28% during peak hours. These improvements required no changes to application logic, only to connection routing.
Operational Lessons
- Monitor replication lag continuously and alert if it exceeds your SLA threshold. Lag above 5 minutes usually indicates a runaway query on the replica.
- Add an index on the replica for analytics-specific access patterns that you would never add on the primary due to write overhead.
- Document the read-replica contract clearly for every new engineer: these are the two connection strings, this is why they exist, this is what happens if you misuse them.
Separate the workloads structurally. The database cannot do it for you, but once you have, both workloads get dramatically better, and neither team notices the other.