new·The score now tells you which way it movedA brain's exam only ever grows: its own material writes questions, and so does every question a real caller asked and did not get answered. The score is a percentage over that growing set, so a brain that learned more could post a smaller number — and this week three did. One of them answered two MORE questions than the week before and showed eighteen points less. Printed as a single percentage, that reads as decline to a reader and as punishment to anyone who contributes material.all news →
mozg.beta
Sign in

Supabase · Database · all subjects

indexes & migrations

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

Common causes of poor database performance

Poor database performance is commonly caused by: 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; and large amount of bloat on tables causing poor query planning.

Supabase CLI inspect db command for database diagnostics

The Supabase CLI provides database inspection tools under the 'inspect db' command. These tools are Postgres agnostic and compatible with any Postgres database, not just Supabase projects. You can get a full list of available commands by running 'supabase inspect db help'. Most inspection commands can run on any Postgres database by providing a connection string via the --db-url flag, for example: 'supabase inspect db bloat --db-url postgresql://postgres:postgres@localhost:5432/postgres'.

Linking Supabase CLI to a project

To link the Supabase CLI with your project, run 'supabase link --project-ref <project-id>'. After linking, the CLI will automatically connect to your Supabase project whenever you are in the project folder and you no longer need to provide the --db-url parameter.

Disk storage inspection commands

Supabase CLI provides these commands for disk storage inspection: 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); table-index-sizes (shows the sizes of indexes for each table).

Query performance inspection commands

Supabase CLI provides these commands for query performance investigation: 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 (show number of sequential scans recorded against all tables); long-running-queries (shows long running queries that are executing right now); 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

Supabase CLI provides these commands for lock inspection: locks (shows statements which have taken out an exclusive lock on a relation); blocking (shows statements that are waiting for locks to be released).

Cache hit rate concept and importance

For most applications, a small percentage of data is accessed more regularly than the rest. Postgres tracks data access patterns and keeps this data in its shared_buffers cache. Applications with lower cache hit rates perform more poorly since they have to hit the disk to get results rather than serving them from memory. Very poor hit rates can cause bursts past Disk IO limits causing significant performance issues.

SQL query for cache and index hit rate

To view your cache and index hit rate, execute this SQL query: 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 the Postgres shared_buffers cache against the data blocks that were read from disk/OS cache. If either your 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.

Using EXPLAIN and EXPLAIN ANALYZE for query optimization

Postgres has built-in tooling to help optimize poorly performing queries. You can use the query plan analyzer by running 'explain analyze <query-statement-here>;'. When you include 'analyze', the database attempts to execute the query and provides a detailed query plan along with actual execution times. Be careful using 'explain analyze' with insert/update/delete queries because the query will run and could have unintended side-effects. If you run 'explain' without the 'analyze' keyword, the database will only perform query planning without executing the query, which is beneficial when you want to inspect the query plan without affecting the database or if you encounter timeouts.

Give your agent this brain