You're six months into your SaaS product. The app works. Users are signing up, data is flowing, and someone on the team says, "Can we get a dashboard showing churn by plan tier?" So you write a query. It touches five tables, scans 2 million rows, and takes 14 seconds to run. You slap it on a dashboard. Now every page load is slow and your database CPU spikes to 90% every five minutes.
This is the moment most teams realize their production database and their analytics needs are fundamentally at odds. Transactional databases are optimized for fast writes and point lookups. Analytics workloads are the opposite — broad scans, heavy aggregations, multiple joins. Running them on the same database, without any abstraction, will hurt both.
The fix isn't a full data warehouse migration (not yet). It's building a lightweight analytics layer on top of what you already have — and once that layer is in place, a tool like Draxlr can sit on top of it to turn your SQL into shareable dashboards without extra infrastructure.
What Is an "Analytics Layer"?
It's not a separate product. It's a set of architectural patterns and SQL structures that sit between your raw application data and your reporting queries. The goal is to:
- Isolate analytical queries from production traffic
- Pre-compute expensive aggregations
- Give consistent, fast query surfaces to dashboards and reports
You can implement most of this with three tools you probably already have access to: read replicas, materialized views, and summary tables.
Pattern 1: Route Analytics Traffic to a Read Replica
The simplest first step. If you're on Postgres, MySQL, or most managed databases (RDS, Supabase, PlanetScale), you can spin up a read replica in minutes.
Send all analytics queries there. Your primary database handles writes and critical application reads. Your replica handles reporting.
-- On the replica: safe to run this heavy aggregation
SELECT
DATE_TRUNC('week', created_at) AS week,
plan_tier,
COUNT(*) AS new_subscriptions,
SUM(mrr_cents) / 100.0 AS weekly_mrr
FROM subscriptions
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;
The replication lag is usually under a second. For dashboards showing weekly or monthly trends, that's completely acceptable. You don't need real-time accuracy for a chart that resets daily.
When this alone isn't enough: the replica still has to scan the same unindexed columns, and if your reporting queries are very expensive, they'll slow the replica down. That's where materialized views come in.
Pattern 2: Materialize Your Expensive Aggregations
A materialized view is a stored query result — it looks like a table, reads like a table, but is populated by running a query. You refresh it on a schedule, not on every read.
-- Create once
CREATE MATERIALIZED VIEW daily_revenue_summary AS
SELECT
DATE_TRUNC('day', charged_at) AS day,
plan_tier,
COUNT(DISTINCT user_id) AS paying_users,
SUM(amount_cents) / 100.0 AS revenue
FROM charges
WHERE status = 'succeeded'
GROUP BY 1, 2;
-- Index it like a normal table
CREATE INDEX ON daily_revenue_summary (day DESC);
-- Refresh on a schedule (e.g., via pg_cron every hour)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue_summary;
Now your dashboard query becomes:
SELECT * FROM daily_revenue_summary
WHERE day >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY day DESC;
That query runs in milliseconds instead of seconds. The expensive aggregation happens offline, during the refresh, not on every dashboard load.
CONCURRENTLY is key — it lets Postgres refresh the view without locking reads, so dashboards stay live while the data updates.
Pattern 3: Build Summary Tables for Cross-Tenant Reporting
If you have a multi-tenant app, you often need aggregations at the tenant level — "how many events did each account trigger this week?" Doing this live on a large events table is brutal.
Instead, maintain a tenant_daily_stats table that your background jobs write to:
CREATE TABLE tenant_daily_stats (
tenant_id UUID NOT NULL,
stat_date DATE NOT NULL,
event_count INTEGER DEFAULT 0,
active_users INTEGER DEFAULT 0,
api_calls INTEGER DEFAULT 0,
PRIMARY KEY (tenant_id, stat_date)
);
Your nightly job (or a scheduled SQL function) computes and upserts:
INSERT INTO tenant_daily_stats (tenant_id, stat_date, event_count, active_users, api_calls)
SELECT
tenant_id,
CURRENT_DATE - 1 AS stat_date,
COUNT(*) AS event_count,
COUNT(DISTINCT user_id) AS active_users,
SUM(CASE WHEN source = 'api' THEN 1 ELSE 0 END) AS api_calls
FROM events
WHERE created_at >= CURRENT_DATE - 1
AND created_at < CURRENT_DATE
GROUP BY tenant_id
ON CONFLICT (tenant_id, stat_date)
DO UPDATE SET
event_count = EXCLUDED.event_count,
active_users = EXCLUDED.active_users,
api_calls = EXCLUDED.api_calls;
This table becomes the backbone of your usage dashboards — fast reads, narrow scans, always indexed on tenant_id and stat_date.
Pattern 4: Use CTEs to Build Reusable Query Layers
Even without materializing anything, you can structure your analytics SQL to be more maintainable by using CTEs as named intermediate layers.
WITH
active_users AS (
SELECT DISTINCT user_id
FROM sessions
WHERE started_at >= CURRENT_DATE - INTERVAL '30 days'
),
paid_users AS (
SELECT user_id
FROM subscriptions
WHERE status = 'active'
AND plan_tier != 'free'
),
churned_last_month AS (
SELECT user_id
FROM subscriptions
WHERE status = 'cancelled'
AND cancelled_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
AND cancelled_at < DATE_TRUNC('month', CURRENT_DATE)
)
SELECT
COUNT(DISTINCT au.user_id) AS mau,
COUNT(DISTINCT pu.user_id) AS paying_mau,
COUNT(DISTINCT cl.user_id) AS churned_last_month
FROM active_users au
LEFT JOIN paid_users pu USING (user_id)
LEFT JOIN churned_last_month cl USING (user_id);
This is much easier to debug and hand off to teammates than a single nested query. And when performance becomes a concern, CTEs that are used multiple times are good candidates for materialization.
Common Mistakes to Avoid
Running analytics queries on the primary without indexes. Analytics queries tend to filter and group on columns that aren't indexed for write performance — things like DATE_TRUNC(created_at) or plan_tier. Add indexes on your replica or summary tables for the columns your dashboards actually filter on.
Refreshing materialized views too aggressively. Refreshing a large materialized view every minute defeats the purpose. Match the refresh frequency to the business need — most SaaS metrics dashboards are fine with hourly or even daily refresh.
Not setting query timeouts. An accidental cross-join on a large table can take down a database. Set statement_timeout for your analytics connection:
SET statement_timeout = '30s';
This is especially important if you're giving any external access to your analytics layer.
Mixing OLTP and analytics in the same connection pool. Use a separate connection string (pointing to your replica or analytics DB) for reporting workloads. This prevents a slow report from consuming connections needed for user-facing requests.
Building too much before you need it. Start with a read replica and one or two materialized views. You don't need a full analytics database or a data warehouse until your raw tables are genuinely too large to query reasonably. Most SaaS apps under 50M rows don't.
Key Takeaways
Your analytics layer doesn't have to be complicated:
- Read replica for routing analytics traffic away from production
- Materialized views for expensive aggregations that don't need to be real-time
- Summary tables for per-tenant or per-dimension stats you need fast and often
- CTEs for organizing complex analytical SQL into readable, maintainable stages
- Timeouts and indexes to protect the database and keep queries fast
Most teams can get 80% of the way there with just materialized views and a read replica — no new infrastructure, no new tools, no migration. Just SQL used deliberately.
What's your current setup for separating analytics from production traffic? Are you using a replica, a separate analytics database, or still querying production directly? Drop your approach in the comments — I'd love to know what's working.
