When your self-hosted Supabase application starts slowing down, the usual suspects are missing indexes or unoptimized queries. But what happens when you've already added indexes and your queries are as efficient as they can get? For dashboards, reports, and analytics—where you're aggregating millions of rows—there's a better answer: materialized views.
Materialized views let you pre-compute expensive queries and store the results on disk, turning complex aggregations that take seconds into instant lookups. For self-hosted Supabase deployments, this technique is especially valuable because you have full control over your PostgreSQL instance and can schedule refreshes exactly when your application needs them.
What Are Materialized Views and Why Do They Matter?
A materialized view is a database object that stores the result of a query physically on disk. Unlike regular views—which execute their underlying query every time you access them—materialized views cache the results until you explicitly refresh them.
Here's the fundamental difference:
-- Regular view: executes the query every time
CREATE VIEW daily_sales AS
SELECT
date_trunc('day', created_at) as sale_date,
COUNT(*) as order_count,
SUM(total_amount) as revenue
FROM orders
GROUP BY 1;
-- Materialized view: stores precomputed results
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT
date_trunc('day', created_at) as sale_date,
COUNT(*) as order_count,
SUM(total_amount) as revenue
FROM orders
GROUP BY 1;
When you query daily_sales, PostgreSQL processes every row in the orders table. When you query daily_sales_mv, PostgreSQL simply reads the precomputed results from disk. For a table with millions of rows, that's the difference between waiting 5 seconds and getting results in 5 milliseconds.
When to Use Materialized Views
Materialized views shine in specific scenarios. They're ideal when:
Your queries aggregate large datasets. Dashboards showing daily/weekly/monthly totals, reports with complex JOINs across multiple tables, and analytics queries that scan millions of rows are perfect candidates.
Data freshness isn't critical. If your users can tolerate data that's 5 minutes, an hour, or a day old, materialized views make sense. Real-time accuracy requirements make them unsuitable.
Read performance matters more than write performance. Materialized views introduce storage overhead and refresh costs. If your application is write-heavy with minimal reads, they may not help.
For PostgreSQL performance tuning, materialized views are one of the most effective techniques when indexes alone aren't enough.
Creating Materialized Views in Self-Hosted Supabase
Let's build a practical example. Imagine you're running a SaaS application on self-hosted Supabase, and your analytics dashboard shows usage metrics that currently take 3-4 seconds to load.
First, connect to your database. With Supascale, you can access your PostgreSQL instance directly through the connection details in your project settings.
Basic Materialized View
-- Create a materialized view for user activity metrics CREATE MATERIALIZED VIEW user_activity_summary AS SELECT u.id as user_id, u.email, COUNT(DISTINCT a.id) as total_actions, COUNT(DISTINCT DATE(a.created_at)) as active_days, MAX(a.created_at) as last_active, SUM(CASE WHEN a.action_type = 'purchase' THEN 1 ELSE 0 END) as purchases, SUM(CASE WHEN a.created_at > NOW() - INTERVAL '30 days' THEN 1 ELSE 0 END) as actions_last_30_days FROM auth.users u LEFT JOIN public.user_actions a ON a.user_id = u.id GROUP BY u.id, u.email WITH DATA; -- Populate immediately (use WITH NO DATA to create empty)
The WITH DATA clause tells PostgreSQL to populate the view immediately. Use WITH NO DATA if you want to create the structure without the initial computation.
Adding Indexes for Faster Lookups
Materialized views support indexes just like regular tables. This is crucial for point lookups:
-- Index for looking up specific users CREATE INDEX idx_user_activity_user_id ON user_activity_summary(user_id); -- Index for sorting by activity CREATE INDEX idx_user_activity_last_active ON user_activity_summary(last_active DESC); -- Index for filtering active users CREATE INDEX idx_user_activity_active_days ON user_activity_summary(active_days) WHERE active_days > 0;
Without these indexes, your materialized view queries would still scan the entire cached dataset. With them, PostgreSQL can jump directly to the rows you need.
Refreshing Materialized Views
The data in a materialized view becomes stale immediately after creation. You need a refresh strategy.
Manual Refresh
-- Basic refresh (locks the view during refresh) REFRESH MATERIALIZED VIEW user_activity_summary; -- Concurrent refresh (allows reads during refresh) REFRESH MATERIALIZED VIEW CONCURRENTLY user_activity_summary;
The CONCURRENTLY option is essential for production. Without it, any query hitting the materialized view will block until the refresh completes. The tradeoff? Concurrent refresh requires a unique index and uses more resources.
-- Required for CONCURRENTLY: add a unique index CREATE UNIQUE INDEX idx_user_activity_pk ON user_activity_summary(user_id);
Automated Refresh with pg_cron
For self-hosted Supabase, you have pg_cron available to schedule automatic refreshes. This is one of the advantages of self-hosting—full control over your PostgreSQL extensions and scheduling.
First, ensure pg_cron is enabled:
CREATE EXTENSION IF NOT EXISTS pg_cron;
Then schedule your refresh:
-- Refresh every hour SELECT cron.schedule( 'refresh_user_activity', '0 * * * *', -- Every hour at minute 0 'REFRESH MATERIALIZED VIEW CONCURRENTLY user_activity_summary' ); -- Refresh every 15 minutes during business hours SELECT cron.schedule( 'refresh_user_activity_business', '*/15 9-17 * * 1-5', -- Every 15 min, 9am-5pm, Mon-Fri 'REFRESH MATERIALIZED VIEW CONCURRENTLY user_activity_summary' );
For more on pg_cron setup, check our guide on scheduling cron jobs for self-hosted Supabase.
Triggered Refresh
For views that need fresher data, you can trigger refreshes based on data changes:
-- Function to refresh the view
CREATE OR REPLACE FUNCTION refresh_user_activity()
RETURNS TRIGGER AS $$
BEGIN
-- Use a queue or background job in production
PERFORM pg_notify('refresh_materialized_views', 'user_activity_summary');
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Trigger on significant changes
CREATE TRIGGER trigger_refresh_activity
AFTER INSERT OR UPDATE OR DELETE ON user_actions
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_user_activity();
Warning: Don't call REFRESH MATERIALIZED VIEW directly in a trigger—it will block the triggering transaction. Instead, send a notification and handle the refresh in a background worker.
Using Materialized Views with the Supabase API
Here's where things get interesting for self-hosted Supabase users. PostgREST (the API layer) treats materialized views like tables, meaning you can query them through your client libraries:
// Query the materialized view just like a table
const { data, error } = await supabase
.from('user_activity_summary')
.select('*')
.order('last_active', { ascending: false })
.limit(100);
However, there are important limitations:
No real-time subscriptions. Materialized views don't support Supabase Realtime. If you need live updates, use regular views or tables.
No RLS on materialized views. PostgreSQL doesn't support Row Level Security on materialized views. You'll need to handle authorization differently.
Workaround for RLS
For applications requiring authorization, wrap the materialized view in a function:
-- Function that filters results based on authenticated user
CREATE OR REPLACE FUNCTION get_team_activity_summary(team_uuid UUID)
RETURNS SETOF user_activity_summary AS $$
BEGIN
-- Verify the requesting user belongs to this team
IF NOT EXISTS (
SELECT 1 FROM team_members
WHERE team_id = team_uuid
AND user_id = auth.uid()
) THEN
RAISE EXCEPTION 'Unauthorized';
END IF;
RETURN QUERY
SELECT uas.*
FROM user_activity_summary uas
JOIN team_members tm ON tm.user_id = uas.user_id
WHERE tm.team_id = team_uuid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
Call it through the RPC endpoint:
const { data, error } = await supabase
.rpc('get_team_activity_summary', { team_uuid: 'your-team-id' });
Practical Examples for Common Use Cases
Dashboard Metrics
CREATE MATERIALIZED VIEW dashboard_metrics AS SELECT account_id, DATE(created_at) as metric_date, COUNT(*) as total_events, COUNT(DISTINCT user_id) as unique_users, SUM(CASE WHEN event_type = 'signup' THEN 1 ELSE 0 END) as signups, SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) as purchases, SUM(CASE WHEN event_type = 'purchase' THEN amount ELSE 0 END) as revenue FROM events WHERE created_at > NOW() - INTERVAL '90 days' GROUP BY account_id, DATE(created_at); CREATE UNIQUE INDEX idx_dashboard_metrics_pk ON dashboard_metrics(account_id, metric_date); CREATE INDEX idx_dashboard_metrics_date ON dashboard_metrics(metric_date DESC);
Leaderboards
CREATE MATERIALIZED VIEW user_leaderboard AS SELECT user_id, username, avatar_url, SUM(points) as total_points, COUNT(DISTINCT game_id) as games_played, RANK() OVER (ORDER BY SUM(points) DESC) as rank FROM game_scores JOIN profiles ON profiles.id = game_scores.user_id GROUP BY user_id, username, avatar_url; CREATE UNIQUE INDEX idx_leaderboard_pk ON user_leaderboard(user_id); CREATE INDEX idx_leaderboard_rank ON user_leaderboard(rank);
Search Aggregations
CREATE MATERIALIZED VIEW product_search_data AS
SELECT
p.id,
p.name,
p.description,
p.price,
c.name as category_name,
COALESCE(AVG(r.rating), 0) as avg_rating,
COUNT(r.id) as review_count,
to_tsvector('english', p.name || ' ' || p.description || ' ' || c.name) as search_vector
FROM products p
JOIN categories c ON c.id = p.category_id
LEFT JOIN reviews r ON r.product_id = p.id
WHERE p.active = true
GROUP BY p.id, p.name, p.description, p.price, c.name;
CREATE UNIQUE INDEX idx_product_search_pk ON product_search_data(id);
CREATE INDEX idx_product_search_vector ON product_search_data USING gin(search_vector);
Monitoring and Maintenance
Track the size and staleness of your materialized views:
-- Check materialized view sizes
SELECT
schemaname,
matviewname,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || matviewname)) as total_size
FROM pg_matviews
ORDER BY pg_total_relation_size(schemaname || '.' || matviewname) DESC;
-- Check when views were last refreshed (requires tracking)
SELECT
job_name,
last_run_time,
CASE
WHEN status = 'SUCCEEDED' THEN 'OK'
ELSE 'FAILED'
END as last_status
FROM cron.job_run_details
WHERE job_name LIKE 'refresh_%'
ORDER BY last_run_time DESC;
For comprehensive observability, see our guide on monitoring self-hosted Supabase.
Trade-offs to Consider
Materialized views aren't free. Consider these factors:
Storage costs. Materialized views duplicate data. A summary of a 10GB table might only be 100MB, but complex views with many columns can grow large.
Refresh overhead. Refreshing a materialized view runs the full query. For views based on billions of rows, this can take minutes and consume significant resources.
Data freshness. Your users see cached data. For some applications, showing "last updated 5 minutes ago" is fine. For others, it's unacceptable.
Index maintenance. Like tables, materialized view indexes need maintenance. VACUUM and ANALYZE still apply.
Getting Started with Supascale
Managing materialized views becomes simpler when you have proper tooling. With Supascale, you get direct PostgreSQL access to your self-hosted instances, making it straightforward to create, refresh, and monitor materialized views. The one-time purchase model means you can run as many projects as you need without worrying about per-project costs eating into your optimization experiments.
Materialized views are just one piece of the performance puzzle. Combined with proper indexing, connection pooling, and query optimization, they can transform a sluggish application into a responsive one. The key is understanding when cached data is acceptable and building refresh strategies that match your users' expectations.