database/advisors

12 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Database Performance and Security Advisors overview

Supabase provides Database Performance and Security Advisors that automatically check your database for issues such as missing indexes and improperly set-up RLS policies. The advisors can be accessed in the dashboard under the Database section via the Security Advisor and Performance Advisor pages. They run automatically, but can also be manually rerun after resolving issues.

How to access Database Advisors

In the Supabase dashboard, navigate to Security Advisor and Performance Advisor under the Database section to view and run database advisors.

Index Advisor in Supabase Dashboard

Supabase Dashboard offers an Index Advisor that suggests potential indexes. Access it by going to Query Performance page, clicking a query, and selecting the Indexes tab. Enable Index Advisor if prompted.

Index Advisor cost metrics explained

Index Advisor shows startup cost (cost to fetch the first row) and total cost (cost to fetch all rows). Costs are in arbitrary units where a single sequential page read costs 1.0 units.

Query planner may ignore suggested indexes

Indexes suggested by Index Advisor may not be used by Postgres' query planner if it determines the query will be faster without them. For example, on a small table, a sequential scan might be faster than an index scan. The planner may switch to using the index as the table grows.

index_advisor extension overview

index_advisor is a Postgres extension for recommending indexes to improve query performance. It supports generic parameters like $1 and $2, materialized views, identifies tables and columns obfuscated by views, and skips duplicate indexes.

Enable index_advisor extension

To enable index_advisor, run the SQL command: CREATE EXTENSION index_advisor;

index_advisor function signature

index_advisor(query text) is the single exposed function. It returns a table with columns: startup_cost_before jsonb, startup_cost_after jsonb, total_cost_before jsonb, total_cost_after jsonb, index_statements text[] (array of CREATE INDEX DDL statements), and errors text[] (array of error messages).

index_advisor example with single table

Example showing index_advisor on a single table query with filter on unindexed column. The function recommends a B-tree index on the title column to improve performance from total_cost 25.88 to 6.40.

index_advisor example with joins

Example with multiple table joins showing index_advisor recommends three indexes: CREATE INDEX ON public.book USING btree (author_id), CREATE INDEX ON public.book USING btree (publisher_id), and CREATE INDEX ON public.review USING btree (book_id). Performance improves from total_cost 68.48 to 42.37.

index_advisor limitations

index_advisor only recommends single column B-tree indexes. More complex indexes will be supported in future releases. When a generic argument's type is not discernible from context, an error is returned in the errors field; resolve by adding explicit type casting (e.g., $1::int).

index_advisor access via Supabase Studio

index_advisor is accessible through Supabase Studio by navigating to Query Performance Report and selecting a query, then the 'indexes' tab.

Give your agent this brain