Repository: supabase/cli
Pull request #2138 (2024-04-10 07:50:51)

fix: exclude supabase managed schemas from inspect subcommand
## What kind of change does this PR introduce?

Feature/Fix: moving queries for inspect to the root `inspect` module and removing Supabase schemas from queries


## What is the current behavior?

Queries are within the command and show results from Supabase schemas that could cause concern or make the user take actions on schemas they do not own

## What is the new behavior?

- Queries moved into a single place (could be further developed to template similar queries)
- Supabase schemas removed from relevant queries
- Explanation of hit rates added to `cache-hit` command and a column explains whether action might be needed

## Additional context

<img width="574" alt="image" src="https://github.com/supabase/cli/assets/517923/1b8d6de5-e94e-4168-a7f1-98c627087822">




coveralls: ## Pull Request Test Coverage Report for [Build 8627805831](https://coveralls.io/builds/66821833)


### Details

* **0** of **31**   **(0.0%)**  changed or added relevant lines in **17** files are covered.
* **5** unchanged lines in **1** file lost coverage.
* Overall coverage decreased (**-0.09%**) to **57.727%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [internal/inspect/bloat/bloat.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Fbloat%2Fbloat.go#L30) | 0 | 1 | 0.0%
| [internal/inspect/blocking/blocking.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Fblocking%2Fblocking.go#L32) | 0 | 1 | 0.0%
| [internal/inspect/calls/calls.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Fcalls%2Fcalls.go#L31) | 0 | 1 | 0.0%
| [internal/inspect/index_sizes/index_sizes.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Findex_sizes%2Findex_sizes.go#L27) | 0 | 1 | 0.0%
| [internal/inspect/index_usage/index_usage.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Findex_usage%2Findex_usage.go#L28) | 0 | 1 | 0.0%
| [internal/inspect/locks/locks.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Flocks%2Flocks.go#L32) | 0 | 1 | 0.0%
| [internal/inspect/long_running_queries/long_running_queries.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Flong_running_queries%2Flong_running_queries.go#L28) | 0 | 1 | 0.0%
| [internal/inspect/outliers/outliers.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Foutliers%2Foutliers.go#L31) | 0 | 1 | 0.0%
| [internal/inspect/replication_slots/replication_slots.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Freplication_slots%2Freplication_slots.go#L30) | 0 | 1 | 0.0%
| [internal/inspect/role_connections/role_connections.go](https://coveralls.io/builds/66821833/source?filename=internal%2Finspect%2Frole_connections%2Frole_connections.go#L28) | 0 | 1 | 0.0%
<!-- | **Total:** | **0** | **31** | **0.0%** | -->

|  Files with Coverage Reduction | New Missed Lines | % |
| :-----|--------------|--: |
| [internal/gen/keys/keys.go](https://coveralls.io/builds/66821833/source?filename=internal%2Fgen%2Fkeys%2Fkeys.go#L101) | 5 | 11.76% |
<!-- | **Total:** | **5** |  | -->

|  Totals | [![Coverage Status](https://coveralls.io/builds/66821833/badge)](https://coveralls.io/builds/66821833) |
| :-- | --: |
| Change from base [Build 8615072750](https://coveralls.io/builds/66800965): |  -0.09% |
| Covered Lines: | 6376 |
| Relevant Lines: | 11045 |

---
##### 💛  - [Coveralls](https://coveralls.io)


Patch:
diff --git a/internal/db/reset/reset.go b/internal/db/reset/reset.go
index 27ccd3d9..24aaab2c 100644
--- a/internal/db/reset/reset.go
+++ b/internal/db/reset/reset.go
@@ -289,7 +289,7 @@ func resetRemote(ctx context.Context, version string, config pgconn.Config, fsys
 }
 
 func ListSchemas(ctx context.Context, conn *pgx.Conn, exclude ...string) ([]string, error) {
-	exclude = likeEscapeSchema(exclude)
+	exclude = LikeEscapeSchema(exclude)
 	if len(exclude) == 0 {
 		exclude = append(exclude, "")
 	}
@@ -300,7 +300,7 @@ func ListSchemas(ctx context.Context, conn *pgx.Conn, exclude ...string) ([]stri
 	return pgxv5.CollectStrings(rows)
 }
 
-func likeEscapeSchema(schemas []string) (result []string) {
+func LikeEscapeSchema(schemas []string) (result []string) {
 	// Treat _ as literal, * as any character
 	replacer := strings.NewReplacer("_", `\_`, "*", "%")
 	for _, sch := range schemas {
diff --git a/internal/inspect/bloat/bloat.go b/internal/inspect/bloat/bloat.go
index d8040863..6bb2c57c 100644
--- a/internal/inspect/bloat/bloat.go
+++ b/internal/inspect/bloat/bloat.go
@@ -8,73 +8,13 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-WITH constants AS (
-  SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma
-), bloat_info AS (
-  SELECT
-    ma,bs,schemaname,tablename,
-    (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
-    (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
-  FROM (
-    SELECT
-      schemaname, tablename, hdr, ma, bs,
-      SUM((1-null_frac)*avg_width) AS datawidth,
-      MAX(null_frac) AS maxfracsum,
-      hdr+(
-        SELECT 1+count(*)/8
-        FROM pg_stats s2
-        WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
-      ) AS nullhdr
-    FROM pg_stats s, constants
-    GROUP BY 1,2,3,4,5
-  ) AS foo
-), table_bloat AS (
-  SELECT
-    schemaname, tablename, cc.relpages, bs,
-    CEIL((cc.reltuples*((datahdr+ma-
-      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
-  FROM bloat_info
-  JOIN pg_class cc ON cc.relname = bloat_info.tablename
-  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
-), index_bloat AS (
-  SELECT
-    schemaname, tablename, bs,
-    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
-    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
-  FROM bloat_info
-  JOIN pg_class cc ON cc.relname = bloat_info.tablename
-  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
-  JOIN pg_index i ON indrelid = cc.oid
-  JOIN pg_class c2 ON c2.oid = i.indexrelid
-)
-SELECT
-  type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste
-FROM
-(SELECT
-  'table' as type,
-  schemaname,
-  tablename as object_name,
-  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat,
-  CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste
-FROM
-  table_bloat
-    UNION
-SELECT
-  'index' as type,
-  schemaname,
-  tablename || '::' || iname as object_name,
-  ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat,
-  CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste
-FROM
-  index_bloat) bloat_summary
-ORDER BY raw_waste DESC, bloat DESC`
-
 type Result struct {
 	Type        string
 	Schemaname  string
@@ -88,7 +28,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.BLOAT_QUERY, reset.LikeEscapeSchema(utils.InternalSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
diff --git a/internal/inspect/blocking/blocking.go b/internal/inspect/blocking/blocking.go
index 3d418bf6..2367cf12 100644
--- a/internal/inspect/blocking/blocking.go
+++ b/internal/inspect/blocking/blocking.go
@@ -9,35 +9,17 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-// Ref: https://github.com/heroku/heroku-pg-extras/blob/main/commands/blocking.js#L7
-const QUERY = `
-SELECT
-	bl.pid AS blocked_pid,
-	ka.query AS blocking_statement,
-	now() - ka.query_start AS blocking_duration,
-	kl.pid AS blocking_pid,
-	a.query AS blocked_statement,
-	now() - a.query_start AS blocked_duration
-FROM pg_catalog.pg_locks bl
-JOIN pg_catalog.pg_stat_activity a
-	ON bl.pid = a.pid
-JOIN pg_catalog.pg_locks kl
-JOIN pg_catalog.pg_stat_activity ka
-	ON kl.pid = ka.pid
-	ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
-WHERE NOT bl.granted
-`
-
 type Result struct {
-	Blocked_pid        string
+	Blocked_pid        int
 	Blocking_statement string
 	Blocking_duration  string
-	Blocking_pid       string
+	Blocking_pid       int
 	Blocked_statement  string
 	Blocked_duration   string
 }
@@ -47,7 +29,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.BLOCKING_QUERY)
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -67,7 +49,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 		re = regexp.MustCompile(`\|`)
 		blocking_statement = re.ReplaceAllString(blocking_statement, `\|`)
 		blocked_statement = re.ReplaceAllString(blocked_statement, `\|`)
-		table += fmt.Sprintf("|`%v`|`%v`|`%v`|`%v`|%s|`%v`|\n", r.Blocked_pid, blocking_statement, r.Blocking_duration, r.Blocking_pid, blocked_statement, r.Blocked_duration)
+		table += fmt.Sprintf("|`%d`|`%s`|`%s`|`%d`|%s|`%s`|\n", r.Blocked_pid, blocking_statement, r.Blocking_duration, r.Blocking_pid, blocked_statement, r.Blocked_duration)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/cache/cache.go b/internal/inspect/cache/cache.go
index 88f3c65a..eb16a7bb 100644
--- a/internal/inspect/cache/cache.go
+++ b/internal/inspect/cache/cache.go
@@ -8,24 +8,12 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-// Ref: https://github.com/heroku/heroku-pg-extras/blob/main/commands/cache_hit.js#L7
-const QUERY = `
-SELECT
-  'index hit rate' AS name,
-  (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) 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) AS ratio
-FROM pg_statio_user_tables;
-`
-
 type Result struct {
 	Name  string
 	Ratio float64
@@ -36,7 +24,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.CACHE_QUERY)
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -45,9 +33,19 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 		return err
 	}
 	// TODO: implement a markdown table marshaller
-	table := "|Name|Ratio|\n|-|-|\n"
+	table := "|Name|Ratio|OK?|Explanation|\n|-|-|-|-|\n"
 	for _, r := range result {
-		table += fmt.Sprintf("|`%s`|`%.6f`|\n", r.Name, r.Ratio)
+		ok := "Yup!"
+		if r.Ratio < 0.94 {
+			ok = "Maybe not..."
+		}
+		var explanation string
+		if r.Name == "index hit rate" {
+			explanation = "This is the ratio of index hits to index scans. If this ratio is low, it means that the database is not using indexes effectively. Check the `index-usage` command for more info."
+		} else if r.Name == "table hit rate" {
+			explanation = "This is the ratio of table hits to table scans. If this ratio is low, it means that your queries are not finding the data effectively. Check your query performance and it might be worth increasing your compute."
+		}
+		table += fmt.Sprintf("|`%s`|`%.6f`|`%s`|`%s`|\n", r.Name, r.Ratio, ok, explanation)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/calls/calls.go b/internal/inspect/calls/calls.go
index faf289ee..02808cb0 100644
--- a/internal/inspect/calls/calls.go
+++ b/internal/inspect/calls/calls.go
@@ -9,23 +9,12 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT
-	query,
-	interval '1 millisecond' * total_exec_time AS total_exec_time,
-	to_char((total_exec_time/sum(total_exec_time) OVER()) * 100, 'FM90D0') || '%'  AS prop_exec_time,
-	to_char(calls, 'FM999G999G990') AS ncalls,
-	interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time
-FROM pg_stat_statements	
-ORDER BY calls DESC
-LIMIT 10
-`
-
 type Result struct {
 	Total_exec_time string
 	Prop_exec_time  string
@@ -39,7 +28,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.CALLS_QUERY)
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
diff --git a/internal/inspect/index_sizes/index_sizes.go b/internal/inspect/index_sizes/index_sizes.go
index 2d5848bc..37e6f273 100644
--- a/internal/inspect/index_sizes/index_sizes.go
+++ b/internal/inspect/index_sizes/index_sizes.go
@@ -8,22 +8,13 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT c.relname AS name,
-  pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size
-FROM pg_class c
-LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
-WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
-AND n.nspname !~ '^pg_toast'
-AND c.relkind='i'
-GROUP BY c.relname
-ORDER BY sum(c.relpages) DESC;`
-
 type Result struct {
 	Name string
 	Size string
@@ -34,7 +25,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.INDEX_SIZES_QUERY, reset.LikeEscapeSchema(utils.InternalSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
diff --git a/internal/inspect/index_usage/index_usage.go b/internal/inspect/index_usage/index_usage.go
index cd304609..c34d7deb 100644
--- a/internal/inspect/index_usage/index_usage.go
+++ b/internal/inspect/index_usage/index_usage.go
@@ -8,34 +8,17 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT relname,
-  CASE
-    WHEN idx_scan IS NULL THEN 'Insufficient data'
-    WHEN idx_scan = 0 THEN 'Insufficient data'
-    ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
-  END percent_of_times_index_used,
-  n_live_tup rows_in_table
-FROM
-  pg_stat_user_tables
-ORDER BY
-  CASE
-    WHEN idx_scan is null then 1
-    WHEN idx_scan = 0 then 1
-    ELSE 0
-  END,
-  n_live_tup DESC;
-`
-
 type Result struct {
-	Relname                     string
+	Name                        string
 	Percent_of_times_index_used string
-	Rows_in_table               string
+	Rows_in_table               int64
 }
 
 func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
@@ -43,7 +26,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.INDEX_USAGE_QUERY, reset.LikeEscapeSchema(utils.InternalSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -54,7 +37,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	// TODO: implement a markdown table marshaller
 	table := "|Table name|Percentage of times index used|Rows in table|\n|-|-|-|\n"
 	for _, r := range result {
-		table += fmt.Sprintf("|`%s`|`%v`|`%v`|\n", r.Relname, r.Percent_of_times_index_used, r.Rows_in_table)
+		table += fmt.Sprintf("|`%s`|`%s`|`%d`|\n", r.Name, r.Percent_of_times_index_used, r.Rows_in_table)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/locks/locks.go b/internal/inspect/locks/locks.go
index 0d3eb5cd..efeb82ae 100644
--- a/internal/inspect/locks/locks.go
+++ b/internal/inspect/locks/locks.go
@@ -9,31 +9,17 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT
-	pg_stat_activity.pid,
-	COALESCE(pg_class.relname, 'null') AS relname,
-	COALESCE(pg_locks.transactionid, 'null') AS transactionid,
-	pg_locks.granted,
-	pg_stat_activity.query,
-	age(now(),pg_stat_activity.query_start) AS age
-FROM pg_stat_activity, pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
-WHERE pg_stat_activity.query <> '<insufficient privilege>'
-AND pg_locks.pid=pg_stat_activity.pid
-AND pg_locks.mode = 'ExclusiveLock'
-ORDER BY query_start;
-`
-
 type Result struct {
-	Pid           string
+	Pid           int
 	Relname       string
 	Transactionid string
-	Granted       string
+	Granted       bool
 	Query         string
 	Age           string
 }
@@ -43,7 +29,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.LOCKS_QUERY)
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -61,7 +47,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 		// escape pipes in query
 		re = regexp.MustCompile(`\|`)
 		query = re.ReplaceAllString(query, `\|`)
-		table += fmt.Sprintf("|`%v`|`%v`|`%v`|`%v`|%s|`%v`|\n", r.Pid, r.Relname, r.Transactionid, r.Granted, query, r.Age)
+		table += fmt.Sprintf("|`%d`|`%s`|`%s`|`%t`|%s|`%s`|\n", r.Pid, r.Relname, r.Transactionid, r.Granted, query, r.Age)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/long_running_queries/long_running_queries.go b/internal/inspect/long_running_queries/long_running_queries.go
index 9b18f527..e091515d 100644
--- a/internal/inspect/long_running_queries/long_running_queries.go
+++ b/internal/inspect/long_running_queries/long_running_queries.go
@@ -8,27 +8,14 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT
-  pid,
-  now() - pg_stat_activity.query_start AS duration,
-  query AS query
-FROM
-  pg_stat_activity
-WHERE
-  pg_stat_activity.query <> ''::text
-  AND state <> 'idle'
-  AND now() - pg_stat_activity.query_start > interval '5 minutes'
-ORDER BY
-  now() - pg_stat_activity.query_start DESC;`
-
 type Result struct {
-	Pid      string
+	Pid      int
 	Duration string
 	Query    string
 }
@@ -38,7 +25,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.LONG_RUNNING_QUERY)
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -49,7 +36,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 
 	table := "|pid|Duration|Query|\n|-|-|-|\n"
 	for _, r := range result {
-		table += fmt.Sprintf("|`%s`|`%s`|`%s`|\n", r.Pid, r.Duration, r.Query)
+		table += fmt.Sprintf("|`%d`|`%s`|`%s`|\n", r.Pid, r.Duration, r.Query)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/outliers/outliers.go b/internal/inspect/outliers/outliers.go
index 33307045..d19a691e 100644
--- a/internal/inspect/outliers/outliers.go
+++ b/internal/inspect/outliers/outliers.go
@@ -9,23 +9,12 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT
-	interval '1 millisecond' * total_exec_time AS total_exec_time,
-	to_char((total_exec_time/sum(total_exec_time) OVER()) * 100, 'FM90D0') || '%'  AS prop_exec_time,
-	to_char(calls, 'FM999G999G999G990') AS ncalls,
-	interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time,
-	query
-FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)
-ORDER BY total_exec_time DESC
-LIMIT 10
-`
-
 type Result struct {
 	Total_exec_time string
 	Prop_exec_time  string
@@ -39,7 +28,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.OUTLIERS_QUERY)
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
diff --git a/internal/inspect/queries.go b/internal/inspect/queries.go
new file mode 100644
index 00000000..42139729
--- /dev/null
+++ b/internal/inspect/queries.go
@@ -0,0 +1,305 @@
+package inspect
+
+const BLOAT_QUERY = `WITH constants AS (
+  SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma
+), bloat_info AS (
+  SELECT
+    ma,bs,schemaname,tablename,
+    (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
+    (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
+  FROM (
+    SELECT
+      schemaname, tablename, hdr, ma, bs,
+      SUM((1-null_frac)*avg_width) AS datawidth,
+      MAX(null_frac) AS maxfracsum,
+      hdr+(
+        SELECT 1+count(*)/8
+        FROM pg_stats s2
+        WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
+      ) AS nullhdr
+    FROM pg_stats s, constants
+    GROUP BY 1,2,3,4,5
+  ) AS foo
+), table_bloat AS (
+  SELECT
+    schemaname, tablename, cc.relpages, bs,
+    CEIL((cc.reltuples*((datahdr+ma-
+      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
+  FROM bloat_info
+  JOIN pg_class cc ON cc.relname = bloat_info.tablename
+  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
+), index_bloat AS (
+  SELECT
+    schemaname, tablename, bs,
+    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
+    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
+  FROM bloat_info
+  JOIN pg_class cc ON cc.relname = bloat_info.tablename
+  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
+  JOIN pg_index i ON indrelid = cc.oid
+  JOIN pg_class c2 ON c2.oid = i.indexrelid
+)
+SELECT
+  type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste
+FROM
+(SELECT
+  'table' as type,
+  schemaname,
+  tablename as object_name,
+  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat,
+  CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste
+FROM
+  table_bloat
+    UNION
+SELECT
+  'index' as type,
+  schemaname,
+  tablename || '::' || iname as object_name,
+  ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat,
+  CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste
+FROM
+  index_bloat) bloat_summary
+WHERE NOT schemaname LIKE ANY($1)
+ORDER BY raw_waste DESC, bloat DESC`
+
+// Ref: https://github.com/heroku/heroku-pg-extras/blob/main/commands/blocking.js#L7
+const BLOCKING_QUERY = `SELECT
+  bl.pid AS blocked_pid,
+  ka.query AS blocking_statement,
+  age(now(), ka.query_start)::text AS blocking_duration,
+  kl.pid AS blocking_pid,
+  a.query AS blocked_statement,
+  age(now(), a.query_start)::text AS blocked_duration
+FROM pg_catalog.pg_locks bl
+JOIN pg_catalog.pg_stat_activity a
+  ON bl.pid = a.pid
+JOIN pg_catalog.pg_locks kl
+JOIN pg_catalog.pg_stat_activity ka
+  ON kl.pid = ka.pid
+  ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
+WHERE NOT bl.granted`
+
+// Ref: https://github.com/heroku/heroku-pg-extras/blob/main/commands/cache_hit.js#L7
+const CACHE_QUERY = `SELECT
+  'index hit rate' AS name,
+  (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) 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) AS ratio
+FROM pg_statio_user_tables;`
+
+const CALLS_QUERY = `SELECT
+  query,
+  (interval '1 millisecond' * total_exec_time)::text AS total_exec_time,
+  to_char((total_exec_time/sum(total_exec_time) OVER()) * 100, 'FM90D0') || '%'  AS prop_exec_time,
+  to_char(calls, 'FM999G999G990') AS ncalls,
+  (interval '1 millisecond' * (blk_read_time + blk_write_time))::text AS sync_io_time
+FROM pg_stat_statements
+ORDER BY calls DESC
+LIMIT 10`
+
+const INDEX_SIZES_QUERY = `SELECT
+  n.nspname || '.' || c.relname AS name,
+  pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size
+FROM pg_class c
+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
+WHERE NOT n.nspname LIKE ANY($1)
+AND c.relkind = 'i'
+GROUP BY n.nspname, c.relname
+ORDER BY sum(c.relpages) DESC`
+
+const INDEX_USAGE_QUERY = `SELECT
+  schemaname || '.' || relname AS name,
+  CASE
+    WHEN idx_scan IS NULL THEN 'Insufficient data'
+    WHEN idx_scan = 0 THEN 'Insufficient data'
+    ELSE ROUND(100.0 * idx_scan / (seq_scan + idx_scan), 1) || '%'
+  END percent_of_times_index_used,
+  n_live_tup rows_in_table
+FROM pg_stat_user_tables
+WHERE NOT schemaname LIKE ANY($1)
+ORDER BY
+  CASE
+    WHEN idx_scan is null then 1
+    WHEN idx_scan = 0 then 1
+    ELSE 0
+  END,
+  n_live_tup DESC`
+
+const LOCKS_QUERY = `SELECT
+  pg_stat_activity.pid,
+  COALESCE(pg_class.relname, 'null') AS relname,
+  COALESCE(pg_locks.transactionid, 'null') AS transactionid,
+  pg_locks.granted,
+  pg_stat_activity.query,
+  age(now(), pg_stat_activity.query_start)::text AS age
+FROM pg_stat_activity, pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
+WHERE pg_stat_activity.query <> '<insufficient privilege>'
+AND pg_locks.pid = pg_stat_activity.pid
+AND pg_locks.mode = 'ExclusiveLock'
+ORDER BY query_start`
+
+const LONG_RUNNING_QUERY = `SELECT
+  pid,
+  age(now(), pg_stat_activity.query_start)::text AS duration,
+  query AS query
+FROM
+  pg_stat_activity
+WHERE
+  pg_stat_activity.query <> ''::text
+  AND state <> 'idle'
+  AND age(now(), pg_stat_activity.query_start) > interval '5 minutes'
+ORDER BY
+  age(now(), pg_stat_activity.query_start) DESC`
+
+const OUTLIERS_QUERY = `SELECT
+  (interval '1 millisecond' * total_exec_time)::text AS total_exec_time,
+  to_char((total_exec_time/sum(total_exec_time) OVER()) * 100, 'FM90D0') || '%'  AS prop_exec_time,
+  to_char(calls, 'FM999G999G999G990') AS ncalls,
+  (interval '1 millisecond' * (blk_read_time + blk_write_time))::text AS sync_io_time,
+  query
+FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)
+ORDER BY total_exec_time DESC
+LIMIT 10`
+
+const REPLICATION_SLOTS_QUERY = `SELECT
+  s.slot_name,
+  s.active,
+  COALESCE(r.state, 'N/A') as state,
+  CASE WHEN r.client_addr IS NULL
+    THEN 'N/A'
+    ELSE r.client_addr::text
+  END replication_client_address,
+  GREATEST(0, ROUND((redo_lsn-restart_lsn)/1024/1024/1024, 2)) as replication_lag_gb
+FROM pg_control_checkpoint(), pg_replication_slots s
+LEFT JOIN pg_stat_replication r ON (r.pid = s.active_pid)`
+
+const ROLE_CONNECTIONS_QUERY = `SELECT
+  rolname,
+  (
+    SELECT
+      count(*)
+    FROM
+      pg_stat_activity
+    WHERE
+      pg_roles.rolname = pg_stat_activity.usename
+  ) AS active_connections,
+  CASE WHEN rolconnlimit = -1
+    THEN current_setting('max_connections')::int8
+    ELSE rolconnlimit
+  END AS connection_limit
+FROM pg_roles
+ORDER BY 2 DESC`
+
+const SEQ_SCANS_QUERY = `SELECT
+  schemaname || '.' || relname AS name,
+  seq_scan as count
+FROM pg_stat_user_tables
+WHERE NOT schemaname LIKE ANY($1)
+ORDER BY seq_scan DESC`
+
+const TABLE_INDEX_SIZES_QUERY = `SELECT
+  n.nspname || '.' || c.relname AS table,
+  pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
+FROM pg_class c
+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
+WHERE NOT n.nspname LIKE ANY($1)
+AND c.relkind = 'r'
+ORDER BY pg_indexes_size(c.oid) DESC`
+
+const TABLE_RECORD_COUNTS_QUERY = `SELECT
+  schemaname AS schema,
+  relname AS name,
+  n_live_tup AS estimated_count
+FROM pg_stat_user_tables
+WHERE NOT schemaname LIKE ANY($1)
+ORDER BY n_live_tup DESC`
+
+const TABLE_SIZES_QUERY = `SELECT
+  n.nspname AS schema,
+  c.relname AS name,
+  pg_size_pretty(pg_table_size(c.oid)) AS size
+FROM pg_class c
+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
+WHERE NOT n.nspname LIKE ANY($1)
+AND c.relkind = 'r'
+ORDER BY pg_table_size(c.oid) DESC`
+
+const TOTAL_INDEX_SIZE_QUERY = `SELECT
+  pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size
+FROM pg_class c
+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
+WHERE NOT n.nspname LIKE ANY($1)
+AND c.relkind = 'i'`
+
+const TOTAL_TABLE_SIZES_QUERY = `SELECT
+  n.nspname AS schema,
+  c.relname AS name,
+  pg_size_pretty(pg_total_relation_size(c.oid)) AS size
+FROM pg_class c
+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
+WHERE NOT n.nspname LIKE ANY($1)
+AND c.relkind = 'r'
+ORDER BY pg_total_relation_size(c.oid) DESC`
+
+const UNUSED_INDEXES_QUERY = `SELECT
+  schemaname || '.' || relname AS table,
+  indexrelname AS index,
+  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
+  idx_scan as index_scans
+FROM pg_stat_user_indexes ui
+JOIN pg_index i ON ui.indexrelid = i.indexrelid
+WHERE
+  NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
+  AND NOT schemaname LIKE ANY($1)
+ORDER BY
+  pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
+  pg_relation_size(i.indexrelid) DESC`
+
+const VACUUM_STATS_QUERY = `WITH table_opts AS (
+  SELECT
+    pg_class.oid, relname, nspname, array_to_string(reloptions, '') AS relopts
+  FROM
+    pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid
+), vacuum_settings AS (
+  SELECT
+    oid, relname, nspname,
+    CASE
+      WHEN relopts LIKE '%autovacuum_vacuum_threshold%'
+        THEN substring(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*')::integer
+        ELSE current_setting('autovacuum_vacuum_threshold')::integer
+      END AS autovacuum_vacuum_threshold,
+    CASE
+      WHEN relopts LIKE '%autovacuum_vacuum_scale_factor%'
+        THEN substring(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*')::real
+        ELSE current_setting('autovacuum_vacuum_scale_factor')::real
+      END AS autovacuum_vacuum_scale_factor
+  FROM
+    table_opts
+)
+SELECT
+  vacuum_settings.nspname AS schema,
+  vacuum_settings.relname AS table,
+  coalesce(to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI'), '') AS last_vacuum,
+  coalesce(to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI'), '') AS last_autovacuum,
+  to_char(pg_class.reltuples, '9G999G999G999') AS rowcount,
+  to_char(psut.n_dead_tup, '9G999G999G999') AS dead_rowcount,
+  to_char(autovacuum_vacuum_threshold
+       + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), '9G999G999G999') AS autovacuum_threshold,
+  CASE
+    WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup
+    THEN 'yes'
+    ELSE 'no'
+  END AS expect_autovacuum
+FROM
+  pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid
+INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid
+WHERE NOT vacuum_settings.nspname LIKE ANY($1)
+ORDER BY
+  case
+    when pg_class.reltuples = -1 then 1
+    else 0
+  end,
+  1`
diff --git a/internal/inspect/replication_slots/replication_slots.go b/internal/inspect/replication_slots/replication_slots.go
index 97408d6c..b66e7a0d 100644
--- a/internal/inspect/replication_slots/replication_slots.go
+++ b/internal/inspect/replication_slots/replication_slots.go
@@ -8,28 +8,15 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT
-  s.slot_name,
-  s.active,
-  COALESCE(r.state, 'N/A') as state,
-  CASE WHEN r.client_addr IS NULL
-     THEN 'N/A'
-     ELSE r.client_addr::text
-  END replication_client_address,
-  GREATEST(0, ROUND((redo_lsn-restart_lsn)/1024/1024/1024, 2)) as replication_lag_gb
-FROM pg_control_checkpoint(), pg_replication_slots s
-LEFT JOIN pg_stat_replication r ON (r.pid = s.active_pid);
-`
-
 type Result struct {
 	Slot_name                  string
-	Active                     string
+	Active                     bool
 	State                      string
 	Replication_client_address string
 	Replication_lag_gb         string
@@ -40,7 +27,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.REPLICATION_SLOTS_QUERY)
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -51,7 +38,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	// TODO: implement a markdown table marshaller
 	table := "|Name|Active|State|Replication Client Address|Replication Lag GB|\n|-|-|-|-|-|\n"
 	for _, r := range result {
-		table += fmt.Sprintf("|`%s`|`%v`|`%v`|`%v`|`%v`|\n", r.Slot_name, r.Active, r.State, r.Replication_client_address, r.Replication_lag_gb)
+		table += fmt.Sprintf("|`%s`|`%t`|`%s`|`%s`|`%s`|\n", r.Slot_name, r.Active, r.State, r.Replication_client_address, r.Replication_lag_gb)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/role_connections/role_connections.go b/internal/inspect/role_connections/role_connections.go
index 0f5c92e8..b0a2aa1f 100644
--- a/internal/inspect/role_connections/role_connections.go
+++ b/internal/inspect/role_connections/role_connections.go
@@ -8,29 +8,12 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-select
-  rolname,
-  (
-    select
-      count(*)
-    from
-      pg_stat_activity
-    where
-      pg_roles.rolname = pg_stat_activity.usename
-  ) as active_connections,
-  case when rolconnlimit = -1 then current_setting('max_connections') :: int8
-       else rolconnlimit
-  end as connection_limit
-from
-  pg_roles
-order by 2 desc`
-
 type Result struct {
 	Rolname            string
 	Active_connections int
@@ -42,7 +25,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.ROLE_CONNECTIONS_QUERY)
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
diff --git a/internal/inspect/seq_scans/seq_scans.go b/internal/inspect/seq_scans/seq_scans.go
index e0235118..3cb4cf55 100644
--- a/internal/inspect/seq_scans/seq_scans.go
+++ b/internal/inspect/seq_scans/seq_scans.go
@@ -8,21 +8,16 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT relname AS name,
-       seq_scan as count
-FROM
-  pg_stat_user_tables
-ORDER BY seq_scan DESC;`
-
 type Result struct {
 	Name  string
-	Count string
+	Count int64
 }
 
 func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
@@ -30,7 +25,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.SEQ_SCANS_QUERY, reset.LikeEscapeSchema(utils.InternalSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -41,7 +36,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 
 	table := "|Name|Count|\n|-|-|\n"
 	for _, r := range result {
-		table += fmt.Sprintf("|`%s`|`%s`|\n", r.Name, r.Count)
+		table += fmt.Sprintf("|`%s`|`%d`|\n", r.Name, r.Count)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/table_index_sizes/table_index_sizes.go b/internal/inspect/table_index_sizes/table_index_sizes.go
index 62905833..905ad3b5 100644
--- a/internal/inspect/table_index_sizes/table_index_sizes.go
+++ b/internal/inspect/table_index_sizes/table_index_sizes.go
@@ -8,21 +8,13 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT c.relname AS table,
-  pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
-FROM pg_class c
-LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
-WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
-AND n.nspname !~ '^pg_toast'
-AND c.relkind='r'
-ORDER BY pg_indexes_size(c.oid) DESC;`
-
 type Result struct {
 	Table      string
 	Index_size string
@@ -33,7 +25,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.TABLE_INDEX_SIZES_QUERY, reset.LikeEscapeSchema(utils.InternalSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
diff --git a/internal/inspect/table_record_counts/table_record_counts.go b/internal/inspect/table_record_counts/table_record_counts.go
index ba204c84..72ca70de 100644
--- a/internal/inspect/table_record_counts/table_record_counts.go
+++ b/internal/inspect/table_record_counts/table_record_counts.go
@@ -8,23 +8,17 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT
-  relname AS name,
-  n_live_tup AS estimated_count
-FROM
-  pg_stat_user_tables
-ORDER BY
-  n_live_tup DESC;`
-
 type Result struct {
+	Schema          string
 	Name            string
-	Estimated_count string
+	Estimated_count int64
 }
 
 func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
@@ -32,7 +26,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.TABLE_RECORD_COUNTS_QUERY, reset.LikeEscapeSchema(utils.PgSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -41,9 +35,9 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 		return err
 	}
 
-	table := "|Name|Estimated count|\n|-|-|\n"
+	table := "Schema|Table|Estimated count|\n|-|-|-|\n"
 	for _, r := range result {
-		table += fmt.Sprintf("|`%s`|`%s`|\n", r.Name, r.Estimated_count)
+		table += fmt.Sprintf("|`%s`|`%s`|`%d`|\n", r.Schema, r.Name, r.Estimated_count)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/table_sizes/table_sizes.go b/internal/inspect/table_sizes/table_sizes.go
index 579ba172..74c0396d 100644
--- a/internal/inspect/table_sizes/table_sizes.go
+++ b/internal/inspect/table_sizes/table_sizes.go
@@ -8,24 +8,17 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT c.relname AS name,
-  pg_size_pretty(pg_table_size(c.oid)) AS size
-FROM pg_class c
-LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
-WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
-AND n.nspname !~ '^pg_toast'
-AND c.relkind='r'
-ORDER BY pg_table_size(c.oid) DESC;`
-
 type Result struct {
-	Name string
-	Size string
+	Schema string
+	Name   string
+	Size   string
 }
 
 func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
@@ -33,7 +26,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.TABLE_SIZES_QUERY, reset.LikeEscapeSchema(utils.PgSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -42,9 +35,9 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 		return err
 	}
 
-	table := "|Name|size|\n|-|-|\n"
+	table := "Schema|Table|size|\n|-|-|-|\n"
 	for _, r := range result {
-		table += fmt.Sprintf("|`%s`|`%s`|\n", r.Name, r.Size)
+		table += fmt.Sprintf("|`%s`|`%s`|`%s`|\n", r.Schema, r.Name, r.Size)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/total_index_size/total_index_size.go b/internal/inspect/total_index_size/total_index_size.go
index 38900177..73ee9e67 100644
--- a/internal/inspect/total_index_size/total_index_size.go
+++ b/internal/inspect/total_index_size/total_index_size.go
@@ -8,20 +8,13 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size
-FROM pg_class c
-LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
-WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
-AND n.nspname !~ '^pg_toast'
-AND c.relkind='i';
-`
-
 type Result struct {
 	Size string
 }
@@ -31,7 +24,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.TOTAL_INDEX_SIZE_QUERY, reset.LikeEscapeSchema(utils.InternalSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
diff --git a/internal/inspect/total_table_sizes/total_table_sizes.go b/internal/inspect/total_table_sizes/total_table_sizes.go
index e47f90b6..8fd44195 100644
--- a/internal/inspect/total_table_sizes/total_table_sizes.go
+++ b/internal/inspect/total_table_sizes/total_table_sizes.go
@@ -8,25 +8,17 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT c.relname AS name,
-  pg_size_pretty(pg_total_relation_size(c.oid)) AS size
-FROM pg_class c
-LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
-WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
-AND n.nspname !~ '^pg_toast'
-AND c.relkind='r'
-ORDER BY pg_total_relation_size(c.oid) DESC;
-`
-
 type Result struct {
-	Name string
-	Size string
+	Schema string
+	Name   string
+	Size   string
 }
 
 func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
@@ -34,7 +26,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.TOTAL_TABLE_SIZES_QUERY, reset.LikeEscapeSchema(utils.PgSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -43,9 +35,9 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 		return err
 	}
 
-	table := "|Name|Size|\n|-|-|\n"
+	table := "Schema|Table|Size|\n|-|-|-|\n"
 	for _, r := range result {
-		table += fmt.Sprintf("|`%s`|`%s`|\n", r.Name, r.Size)
+		table += fmt.Sprintf("|`%s`|`%s`|`%s`|\n", r.Schema, r.Name, r.Size)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/unused_indexes/unused_indexes.go b/internal/inspect/unused_indexes/unused_indexes.go
index 18fc06dc..ca0c4fef 100644
--- a/internal/inspect/unused_indexes/unused_indexes.go
+++ b/internal/inspect/unused_indexes/unused_indexes.go
@@ -8,28 +8,18 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-SELECT
-  schemaname || '.' || relname AS table,
-  indexrelname AS index,
-  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
-  idx_scan as index_scans
-FROM pg_stat_user_indexes ui
-JOIN pg_index i ON ui.indexrelid = i.indexrelid
-WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
-ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
-pg_relation_size(i.indexrelid) DESC;`
-
 type Result struct {
 	Table       string
 	Index       string
 	Index_size  string
-	Index_scans string
+	Index_scans int64
 }
 
 func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
@@ -37,7 +27,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.UNUSED_INDEXES_QUERY, reset.LikeEscapeSchema(utils.InternalSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
@@ -48,7 +38,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 
 	table := "|Table|Index|Index Size|Index Scans\n|-|-|-|-|\n"
 	for _, r := range result {
-		table += fmt.Sprintf("|`%s`|`%s`|`%s`|`%s`|\n", r.Table, r.Index, r.Index_size, r.Index_scans)
+		table += fmt.Sprintf("|`%s`|`%s`|`%s`|`%d`|\n", r.Table, r.Index, r.Index_size, r.Index_scans)
 	}
 	return list.RenderTable(table)
 }
diff --git a/internal/inspect/vacuum_stats/vacuum_stats.go b/internal/inspect/vacuum_stats/vacuum_stats.go
index 220990ed..cc98b6b0 100644
--- a/internal/inspect/vacuum_stats/vacuum_stats.go
+++ b/internal/inspect/vacuum_stats/vacuum_stats.go
@@ -9,57 +9,13 @@ import (
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/inspect"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/pgxv5"
 )
 
-const QUERY = `
-WITH table_opts AS (
-  SELECT
-    pg_class.oid, relname, nspname, array_to_string(reloptions, '') AS relopts
-  FROM
-     pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid
-), vacuum_settings AS (
-  SELECT
-    oid, relname, nspname,
-    CASE
-      WHEN relopts LIKE '%autovacuum_vacuum_threshold%'
-        THEN substring(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*')::integer
-        ELSE current_setting('autovacuum_vacuum_threshold')::integer
-      END AS autovacuum_vacuum_threshold,
-    CASE
-      WHEN relopts LIKE '%autovacuum_vacuum_scale_factor%'
-        THEN substring(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*')::real
-        ELSE current_setting('autovacuum_vacuum_scale_factor')::real
-      END AS autovacuum_vacuum_scale_factor
-  FROM
-    table_opts
-)
-SELECT
-  vacuum_settings.nspname AS schema,
-  vacuum_settings.relname AS table,
-  coalesce(to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI'), '') AS last_vacuum,
-  coalesce(to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI'), '') AS last_autovacuum,
-  to_char(pg_class.reltuples, '9G999G999G999') AS rowcount,
-  to_char(psut.n_dead_tup, '9G999G999G999') AS dead_rowcount,
-  to_char(autovacuum_vacuum_threshold
-       + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), '9G999G999G999') AS autovacuum_threshold,
-  CASE
-    WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup
-    THEN 'yes'
-    ELSE 'no'
-  END AS expect_autovacuum
-FROM
-  pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid
-INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid
-ORDER BY
-  case
-    when pg_class.reltuples = -1 then 1
-    else 0
-  end,
-  1`
-
 type Result struct {
 	Schema               string
 	Table                string
@@ -76,7 +32,7 @@ func Run(ctx context.Context, config pgconn.Config, fsys afero.Fs, options ...fu
 	if err != nil {
 		return err
 	}
-	rows, err := conn.Query(ctx, QUERY)
+	rows, err := conn.Query(ctx, inspect.VACUUM_STATS_QUERY, reset.LikeEscapeSchema(utils.InternalSchemas))
 	if err != nil {
 		return errors.Errorf("failed to query rows: %w", err)
 	}
diff --git a/internal/utils/misc.go b/internal/utils/misc.go
index 469cd07e..3876a0bc 100644
--- a/internal/utils/misc.go
+++ b/internal/utils/misc.go
@@ -107,9 +107,11 @@ var (
 	ImageNamePattern   = regexp.MustCompile(`\/(.*):`)
 
 	// These schemas are ignored from db diff and db dump
-	SystemSchemas = []string{
+	PgSchemas = []string{
 		"information_schema",
 		"pg_*", // Wildcard pattern follows pg_dump
+	}
+	SystemSchemas = append([]string{
 		// Owned by extensions
 		"cron",
 		"graphql",
@@ -125,7 +127,7 @@ var (
 		"_timescaledb_*",
 		"topology",
 		"vault",
-	}
+	}, PgSchemas...)
 	InternalSchemas = append([]string{
 		"auth",
 		"extensions",


Test patch:
diff --git a/internal/db/diff/diff_test.go b/internal/db/diff/diff_test.go
index 00d019d4..5d714315 100644
--- a/internal/db/diff/diff_test.go
+++ b/internal/db/diff/diff_test.go
@@ -43,8 +43,6 @@ var escapedSchemas = []string{
 	`\_analytics`,
 	`supabase\_functions`,
 	`supabase\_migrations`,
-	`information\_schema`,
-	`pg\_%`,
 	"cron",
 	"graphql",
 	`graphql\_public`,
@@ -59,6 +57,8 @@ var escapedSchemas = []string{
 	`\_timescaledb\_%`,
 	"topology",
 	"vault",
+	`information\_schema`,
+	`pg\_%`,
 }
 
 func TestRun(t *testing.T) {
diff --git a/internal/db/pull/pull_test.go b/internal/db/pull/pull_test.go
index 62c49ff9..fba4fa1a 100644
--- a/internal/db/pull/pull_test.go
+++ b/internal/db/pull/pull_test.go
@@ -39,8 +39,6 @@ var escapedSchemas = []string{
 	`\_analytics`,
 	`supabase\_functions`,
 	`supabase\_migrations`,
-	`information\_schema`,
-	`pg\_%`,
 	"cron",
 	"graphql",
 	`graphql\_public`,
@@ -55,6 +53,8 @@ var escapedSchemas = []string{
 	`\_timescaledb\_%`,
 	"topology",
 	"vault",
+	`information\_schema`,
+	`pg\_%`,
 }
 
 func TestPullCommand(t *testing.T) {
diff --git a/internal/db/reset/reset_test.go b/internal/db/reset/reset_test.go
index 3b91a344..cbeecb93 100644
--- a/internal/db/reset/reset_test.go
+++ b/internal/db/reset/reset_test.go
@@ -302,8 +302,6 @@ var escapedSchemas = []string{
 	"storage",
 	`\_analytics`,
 	`supabase\_functions`,
-	`information\_schema`,
-	`pg\_%`,
 	"cron",
 	"graphql",
 	`graphql\_public`,
@@ -318,6 +316,8 @@ var escapedSchemas = []string{
 	`\_timescaledb\_%`,
 	"topology",
 	"vault",
+	`information\_schema`,
+	`pg\_%`,
 }
 
 func TestResetRemote(t *testing.T) {