debugging

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

explain() method for Postgres execution plans

The explain() method provides the Postgres EXPLAIN execution plan of a query. It is a powerful tool for debugging slow queries and understanding how Postgres will execute a given query. This feature is applicable to any query, including those made through rpc() or write operations.

explain() is disabled by default

The explain() method is disabled by default to protect sensitive information about database structure and operations. It is recommended to use explain() only in a non-production environment.

Enable explain() with SQL commands

To enable explain(), run: alter role authenticator set pgrst.db_plan_enabled to 'true'; followed by notify pgrst, 'reload config';

Use explain() method in supabase-js

To get the execution plan of a query, chain the explain() method to a Supabase query: const { data, error } = await supabase.from('instruments').select().explain()

explain() response format

By default, the execution plan is returned in TEXT format. The response shows a hierarchical execution plan with cost estimates and row counts, such as: Aggregate (cost=33.34..33.36 rows=1 width=112) -> Limit (cost=0.00..18.33 rows=1000 width=40) -> Seq Scan on instruments (cost=0.00..22.00 rows=1200 width=40). You can also retrieve the execution plan as JSON by specifying the format parameter.

Production explain() protection with pre-request function

If you need to enable explain() in a production environment, protect your database by restricting access using a pre-request function that filters requests based on IP address. The pre-request function should check the 'cf-connecting-ip' header and only allow requests from your IP address. Raise an insufficient_privilege error for requests with 'application/vnd.pgrst.plan' accept header from unauthorized IPs. Set the function with: alter role authenticator set pgrst.db_pre_request to 'filter_plan_requests'; notify pgrst, 'reload config';

Disable explain() after use

To disable the explain() method, execute: alter role authenticator set pgrst.db_plan_enabled to 'false'; If you used a pre-request function, also run: alter role authenticator set pgrst.db_pre_request to ''; Finally, run: notify pgrst, 'reload config';

Common causes of poor database performance

Poor database performance typically results from one or more of these factors: an inefficiently designed schema, inefficiently designed queries, a lack of indexes causing slower than required queries over large tables, unused indexes causing slow INSERT, UPDATE and DELETE operations, not enough compute resources such as memory causing the database to go to disk for results too often, lock contention from multiple queries operating on heavily used tables, or large amount of bloat on tables causing poor query planning.

Supabase CLI inspect db command for database inspection

The Supabase CLI provides the `inspect db` command with tools to inspect Postgres instances for potential issues. The available subcommands include: bloat, blocking, cache-hit, calls, index-sizes, index-usage, locks, long-running-queries, outliers, replication-slots, role-connections, seq-scans, table-index-sizes, table-record-counts, table-sizes, unused-indexes, and vacuum-stats. Run `supabase inspect db help` to see the full list.

Connect Supabase CLI to any Postgres database with --db-url

Most Supabase CLI inspection commands are Postgres agnostic and can run on any Postgres database, even if it is not a Supabase project. Provide a connection string via the `--db-url` flag to connect to a non-Supabase database. Example: `supabase inspect db bloat --db-url postgresql://postgres:postgres@localhost:5432/postgres`

Link Supabase CLI to a Supabase project

Link the Supabase CLI to your Supabase project by running `supabase link --project-ref <project-id>`. After linking, the CLI will automatically connect to your Supabase project when you are in the project folder and you no longer need to provide the `--db-url` flag.

Disk storage inspection commands

The following CLI commands help inspect disk storage usage: bloat (estimates the amount of wasted space), vacuum-stats (gives information on waste collection routines), table-record-counts (estimates the number of records per table), table-sizes (shows the sizes of tables), index-sizes (shows the sizes of individual indexes), and table-index-sizes (shows the sizes of indexes for each table).

Query performance inspection commands

The following CLI commands help investigate query performance and resource consumption: cache-hit (shows how efficient your cache usage is overall), unused-indexes (shows indexes with low index scans), index-usage (shows information about the efficiency of indexes), seq-scans (shows number of sequential scans recorded against all tables), long-running-queries (shows long running queries that are executing right now), and outliers (shows queries with high execution time but low call count and queries with high proportion of execution time spent on synchronous I/O).

Lock inspection commands

The locks command shows statements which have taken out an exclusive lock on a relation. The blocking command shows statements that are waiting for locks to be released.

Connection inspection commands

The role-connections command shows the number of active connections for all database roles (this is a Supabase-specific command). The replication-slots command shows information about replication slots on the database.

Grant pg_read_all_stats for Query Performance page access

If you see an 'insufficient privilege' error when viewing the Query Performance page from the Supabase dashboard, run the command: `grant pg_read_all_stats to postgres;`

Most frequently called queries SQL example

Use this query to identify frequently executed queries and their performance metrics: select auth.rolname, statements.query, statements.calls, statements.total_exec_time + statements.total_plan_time as total_time, statements.min_exec_time + statements.min_plan_time as min_time, statements.max_exec_time + statements.max_plan_time as max_time, statements.mean_exec_time + statements.mean_plan_time as mean_time, statements.rows / statements.calls as avg_rows from pg_stat_statements as statements inner join pg_authid as auth on statements.userid = auth.oid order by statements.calls desc limit 100; Note: For Postgres 13, 14, or 15, use the total_time, min_time, max_time, and mean_time calculations shown. For Postgres 12 or earlier, use the commented-out single column references instead.

Slowest queries by execution time SQL example

Use this query to identify queries with high maximum execution times: select auth.rolname, statements.query, statements.calls, statements.total_exec_time + statements.total_plan_time as total_time, statements.min_exec_time + statements.min_plan_time as min_time, statements.max_exec_time + statements.max_plan_time as max_time, statements.mean_exec_time + statements.mean_plan_time as mean_time, statements.rows / statements.calls as avg_rows from pg_stat_statements as statements inner join pg_authid as auth on statements.userid = auth.oid order by max_time desc limit 100; This shows queries ordered by maximum execution time and highlights outliers with high execution times. Note: For Postgres 12 or earlier, use the commented-out single column references instead of the combined calculations.

Most time consuming queries SQL example

Use this query to identify queries consuming the most cumulative execution time and their proportion of total time: select auth.rolname, statements.query, statements.calls, statements.total_exec_time + statements.total_plan_time as total_time, to_char(((statements.total_exec_time + statements.total_plan_time) / sum(statements.total_exec_time + statements.total_plan_time) over ()) * 100, 'FM90D0') || '%' as prop_total_time from pg_stat_statements as statements inner join pg_authid as auth on statements.userid = auth.oid order by total_time desc limit 100;

Cache hit rate SQL example

Use this query to view cache and index hit rates: select 'index hit rate' as name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read), 0) * 100 as ratio from pg_statio_user_indexes union all select 'table hit rate' as name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100 as ratio from pg_statio_user_tables; This shows the ratio of data blocks fetched from shared_buffers cache against data blocks read from disk/OS cache.

Cache hit rate interpretation

If either index or table hit rate is less than 99%, this can indicate your compute plan is too small for your current workload and you would benefit from more memory. Upgrading your compute can be done from your project dashboard.

EXPLAIN ANALYZE for query optimization

Use the query plan analyzer by running `explain analyze <query-statement-here>;` to get a detailed query plan with actual execution times for expensive queries. When include `analyze` in the explain statement, the database executes the query, so be careful using it with insert/update/delete queries as it will run and could have unintended side-effects. Use `explain` without the `analyze` keyword to perform query planning without executing the query.

Shared buffers and data access

Postgres tracks data access patterns and keeps regularly accessed data in its shared_buffers cache. Applications with lower cache hit rates generally perform worse since they have to hit the disk to get results. Very poor hit rates can cause you to burst past your Disk IO limits causing significant performance issues.

Check for blocked queries

Query pg_locks and pg_stat_activity to see currently active queries and queries waiting for locks. The Supabase CLI provides commands to view these metrics: supabase inspect db locks and supabase inspect db blocking

Identify timed out queries in Logs Explorer

Use the Logs Explorer to find timed-out events and long-running queries by searching for 'statement timeout' in event_message or checking duration exceeding a threshold. Filter by parsed.user_name to scope to specific roles. The query uses: regexp_contains(event_message, 'duration|statement timeout') to match timeout events.

Filter logs by parsed.user_name to find role-specific events

Filter database logs by role using the parsed.user_name field in log queries to retrieve events from specific API servers or user roles. This helps identify which service or tool generated timeout or long-running query events.

Query Performance page shows successful slow queries only

The Query Performance page in the Supabase Dashboard identifies slow-running but successful queries, filtered by role and query speed. Unlike the Logs Explorer, it does not show timed-out queries.

Test function to verify global timeout

To verify that a global timeout has been applied, create a test function that sleeps longer than the timeout: create or replace function myfunc() returns void as $$ select pg_sleep(601); $$ language sql; This function sleeps for 601 seconds and should trigger a statement timeout if the global timeout is set to a lower value.

Give your agent this brain