Interpreting Supabase Grafana Memory charts

[TheOtherBrian1]

Here are examples of unhealthy memory usage:

• Yellow: represents active memory
• Red: represents SWAP, which is disk storage that the system treats as if it were actually memory
• Green: it is unclaimed (the system will always leave some memory unclaimed)
• Blue: it is cached data and a buffer

The cache in PostgreSQL is important because the database will store frequently accessed data in it for rapid retrieval. If too much active memory is needed, it runs the risk of excessively displacing cache. This will force queries to check disk, which is slow.

Most data in a database is idle, but in cases where there is little available memory or uncached data is rapidly accessed, thrashing can occur.

Ideally, you want queries to hit the cache 99% of the time. You can use the Supabase CLI inspect db cache hit command to check your cache hit rate. Alternatively, you can run the query found in the CLI's GitHub repo in the SQL Editor

# login to the CLI
npx supabase login

# initlize a local supabase directory
npx supabase init

#link your project
npx supabase link

# find cache hit rate
npx supabase inspect db cache-hit --linked

If the cache hit rate begins to drop below the ideal amount, one should consider taking the following actions:

Optimizing:

1. Apply indexes: can reduce the amount of data pulled from disk into memory
2. Increasing the compute size
3. Distribute load by using read-replicas
4. Partitions: Generally should be used on very large tables to minimize data pulled from disk into memory
5. Remove bloat: Bloat can fragment data across pages, causing redundant data to be pulled from disk.
6. Table refactoring: Split tables to isolate columns that are less frequently accessed, so they are not redundantly pulled into memory while accessing hotter data