Monitor and optimize Supabase project

How to Monitor Slow Queries in Supabase

Supabase provides powerful, built-in tools to help you identify and diagnose slow queries. The key is to leverage the pg_stat_statements extension, which is enabled by default on all Supabase projects [1, 4].

---

Using the Supabase Dashboard

The most straightforward way to find slow queries is through the Supabase Dashboard.

1. Navigate to your project in the Supabase Dashboard.
2. Go to the Database section and click on Query Performance.
3. This page provides a visual overview of your database's performance. It uses the data from pg_stat_statements to show you:
   • Queries with the highest total execution time.
   • Queries with the highest number of calls.
   • The average execution time of your queries.

You can click on a specific query to see its details and get recommendations from the Index Advisor.

Using SQL Directly

For a more granular view, you can query the pg_stat_statements view directly from the SQL Editor in your Supabase Dashboard [1, 4].

-- Find the top 10 slowest queries by total execution time
SELECT
    query,
    calls,
    total_time,
    mean_time,
    (total_time / calls) AS avg_time
FROM
    pg_stat_statements
ORDER BY
    total_time DESC
LIMIT 10;

This gives you a clear list of the queries that are consuming the most resources and should be your primary targets for optimization.

---

Does Supabase Expose PostgreSQL Performance Stats?

Yes, Supabase exposes a comprehensive set of PostgreSQL performance statistics. In addition to pg_stat_statements, you can inspect other views to get a full picture of your database's health:

• pg_stat_activity: Shows all currently running queries. This is useful for finding long-running queries that might be causing a bottleneck in real time [1].
• pg_stat_user_tables: Provides statistics on table usage, including the number of sequential scans (seq_scan) versus index scans (idx_scan). If a table has a high number of sequential scans and is large, it may be a good candidate for a new index.
• pg_stat_user_indexes: Offers statistics on index usage, including cache hit rates. A low cache hit rate might indicate that you need to scale your database's compute resources.

The Supabase Dashboard and CLI also provide easy-to-use tools to visualize and access this information [4].

---

Can I Create Indexes Manually to Improve Performance?

Yes, creating manual indexes is one of the most effective ways to improve query performance in Supabase. You can create indexes directly in the SQL Editor of the Supabase Dashboard or via the Supabase CLI as part of your database migrations [3].

How to Decide What to Index

1. Analyze the Query Plan: Use the EXPLAIN ANALYZE command to see how PostgreSQL executes a query. Look for a Seq Scan (sequential scan) on large tables, as this is a strong indicator that an index could help.

   EXPLAIN ANALYZE
   SELECT *
   FROM articles
   WHERE author_id = 'c13c7a36-3a78-430c-8438-e6c10b7a8d9a';

2. Create an Index: Based on the query plan, create an index on the relevant column(s). In the example above, you would create an index on the author_id column.

   CREATE INDEX idx_articles_author_id ON articles (author_id);

3. Rerun the Query Plan: Rerun EXPLAIN ANALYZE to confirm that the new index is being used and that the query cost has been reduced.

Important: While indexes speed up reads, they can slow down write operations (INSERT, UPDATE, DELETE) because the index must also be updated. It's crucial to find a balance between read and write performance [3].

---

Does Supabase Support Materialized Views or Partitioned Tables?

Yes, since Supabase is a full PostgreSQL database, it supports both Materialized Views and Partitioned Tables [4].

• Materialized Views: A materialized view is a view that caches the result of a query in a physical table. They are perfect for complex, read-heavy queries that you run frequently but don't need real-time data for. You can create one and then refresh it periodically.

  CREATE MATERIALIZED VIEW popular_authors AS
  SELECT
    a.name,
    COUNT(p.id) AS total_posts
  FROM
    authors a
    JOIN posts p ON a.id = p.author_id
  GROUP BY
    a.name
  WITH NO DATA;
  
  REFRESH MATERIALIZED VIEW popular_authors;

• Partitioned Tables: Table partitioning is a database design pattern that splits a single large table into smaller, more manageable pieces based on a key (e.g., a date range or a user ID). This can significantly improve performance for very large tables, especially with time-series or multi-tenant data, as queries only need to scan a subset of the data [4].

---

How to Profile and Optimize Supabase Queries

When you're working with client libraries (e.g., supabase-js), the queries are abstracted away, making them harder to profile. Here's a workflow to bridge that gap:

1. Generate the SQL: Use a debugger or console logs in your application to print the exact query that the Supabase client library is sending to the server. The Supabase client libraries translate your methods (like .select(), .eq()) into a RESTful API request to PostgREST, which then generates the final SQL [5].
2. Analyze the SQL: Once you have the raw SQL, take it to the Supabase SQL Editor and run it with EXPLAIN ANALYZE. This will give you a detailed breakdown of the query's execution plan and help you identify bottlenecks.
3. Optimize the SQL and Schema: Based on your EXPLAIN analysis, optimize the query. This might involve:
   • Adding an index.
   • Rewriting the query to be more efficient.
   • Using a Materialized View for complex queries.
   • Avoiding SELECT * and only selecting the columns you need.
4. Implement the Fix: If you made a schema change (like adding an index), apply it as a migration. If you rewrote the query, update your client-side code accordingly or, better yet, wrap the complex logic in a PostgreSQL function and call it via an RPC from your client.

Sources

1. Supabase. "Query Performance Monitoring". https://supabase.com/docs/guides/database/extensions/pg_stat_statements
2. Supabase. "Performance Tuning". https://supabase.com/docs/guides/platform/performance
3. Supabase. "Managing Indexes in PostgreSQL". https://supabase.com/docs/guides/database/postgres/indexes
4. Supabase. "Debugging and monitoring". https://supabase.com/docs/guides/database/inspect
5. Reddit. "How are you supposed to use the Supabase client library in client components?". https://www.reddit.com/r/Supabase/comments/1im9392/how_are_you_supposed_to_use_the_supabase_client/