Skip to content

The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.

5.9 KB
Raw Blame
---
id: 'inspect'
title: 'Inspect the database'
description: 'Read live Postgres statistics such as bloat, cache hit rate, locks, and slow queries from the CLI, Explorer, or MCP.'
---

This guide explains how to read live database statistics using the CLI, MCP, or Explorer.

Read database statistics from:

- Studio: [Explorer](/dashboard/project/_/explorer) with query source **Database**
- MCP: `execute_sql`
- CLI: [`supabase inspect db`](/docs/reference/cli/supabase-inspect-db)

Use this page to:

- Run [CLI inspection commands](#using-the-cli)
- Run [SQL checks](#using-sql)

To pick up a signal from these checks, see [Detecting](/docs/guides/observability/detecting). For the other sources, see [Observability](/docs/guides/observability).

## Using the CLI

The [Supabase CLI](/docs/guides/local-development/cli/getting-started) reads live statistics from [Postgres internals](https://www.postgresql.org/docs/current/internals.html). Most commands work on any Postgres database, not only a Supabase project.

### The `inspect db` command

The inspection tools for your Postgres database are under the `inspect db` command. You can get a full list of available commands by running `supabase inspect db help`.

```
$ supabase inspect db help
Tools to inspect your Supabase database

Usage:
  supabase inspect db [command]

Available Commands:
  bloat                Estimates space allocated to a relation that is full of dead tuples
  blocking             Show queries that are holding locks and the queries that are waiting for them to be released
  cache-hit            Show cache hit rates for tables and indices

...
```

### Connect to any Postgres database

Most inspection commands are Postgres agnostic. You can run inspection routines on any Postgres database even if it is not a Supabase project by providing a connection string via `--db-url`.

For example you can connect to your local Postgres instance:

```
supabase inspect db bloat --db-url postgresql://postgres:postgres@localhost:5432/postgres
```

### Connect to a Supabase instance

Working with Supabase, you can link the Supabase CLI with your project:

```
supabase link --project-ref <project-id>
```

Then the CLI will automatically connect to your Supabase project whenever you are in the project folder and you no longer need to provide `--db-url`.

### Inspection commands

Below are the `db` inspection commands provided, grouped by different use cases.

<Admonition type="note">

Some commands might require `pg_stat_statements` to be enabled or a specific Postgres version to be used.

</Admonition>

#### Disk storage

These commands are handy if you are running low on disk storage:

- [bloat](/docs/reference/cli/supabase-inspect-db-bloat) - estimates the amount of wasted space
- [vacuum-stats](/docs/reference/cli/supabase-inspect-db-vacuum-stats) - gives information on waste collection routines
- [table-record-counts](/docs/reference/cli/supabase-inspect-db-table-record-counts) - estimates the number of records per table
- [table-sizes](/docs/reference/cli/supabase-inspect-db-table-sizes) - shows the sizes of tables
- [index-sizes](/docs/reference/cli/supabase-inspect-db-index-sizes) - shows the sizes of individual index
- [table-index-sizes](/docs/reference/cli/supabase-inspect-db-table-index-sizes) - shows the sizes of indexes for each table

#### Query performance

The commands below are useful if your Postgres database consumes a lot of resources like CPU, RAM or Disk IO. You can also use them to investigate slow queries.

- [cache-hit](/docs/reference/cli/supabase-inspect-db-cache-hit) - shows how efficient your cache usage is overall
- [unused-indexes](/docs/reference/cli/supabase-inspect-db-unused-indexes) - shows indexes with low index scans
- [index-usage](/docs/reference/cli/supabase-inspect-db-index-usage) - shows information about the efficiency of indexes
- [seq-scans](/docs/reference/cli/supabase-inspect-db-seq-scans) - show number of sequential scans recorded against all tables
- [long-running-queries](/docs/reference/cli/supabase-inspect-db-long-running-queries) - shows long running queries that are executing right now
- [outliers](/docs/reference/cli/supabase-inspect-db-outliers) - shows queries with high execution time but low call count and queries with high proportion of execution time spent on synchronous I/O

#### Locks

- [locks](/docs/reference/cli/supabase-inspect-db-locks) - shows statements which have taken out an exclusive lock on a relation
- [blocking](/docs/reference/cli/supabase-inspect-db-blocking) - shows statements that are waiting for locks to be released

#### Connections

- [role-connections](/docs/reference/cli/supabase-inspect-db-role-connections) - shows number of active connections for all database roles (Supabase-specific command)
- [replication-slots](/docs/reference/cli/supabase-inspect-db-replication-slots) - shows information about replication slots on the database

## Using SQL

Open [Explorer](/dashboard/project/_/explorer), select **Run SQL**, and choose **Database** as the query source. You can also run read-only diagnostics through MCP `execute_sql`.

Use [Performance checks](/docs/guides/observability/detecting#performance) for active sessions, blockers, expensive statements, and cache hit rates. Use [Capacity checks](/docs/guides/observability/detecting#usage) for relation sizes and connection counts.

`pg_stat_activity` is a live snapshot. `pg_stat_statements` and cache counters are cumulative since their last reset; they do not describe an arbitrary historical window. Compare saved snapshots with the same reset interval when measuring changes. Check the [pg_stat_statements guide](/docs/guides/database/extensions/pg_stat_statements) for extension requirements.

When a check identifies a statement, inspect its [query plan](/docs/guides/database/query-optimization#analyze-the-query-plan). A long-running session or high cumulative query time is evidence to investigate, not a reason by itself to cancel a query or reset statistics.