Step 2: Create Monitoring User

We recommend creating a separate monitoring user, rather than using the built-in postgres user.

Connect to your database as the postgres user and run the following to create a new user. This is a one-off setup step, so it doesn’t matter which connection you use to run it — the SQL Editor is the easiest option, but psql against either the direct connection or the Supavisor pooler (session or transaction mode) works just as well:

CREATE USER pganalyze WITH PASSWORD 'mypassword' CONNECTION LIMIT 5;
GRANT pg_monitor TO pganalyze;

CREATE SCHEMA pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
GRANT USAGE ON SCHEMA public TO pganalyze;

If you enable the optional reset mode (usually not required), you will also need this helper method:

CREATE OR REPLACE FUNCTION pganalyze.reset_stat_statements() RETURNS SETOF void AS
$$
  /* pganalyze-collector */ SELECT * FROM public.pg_stat_statements_reset();
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Then, connect to the postgres database as postgres and run the following to enable the collection of additional column statistics and extended statistics:

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;


DROP FUNCTION IF EXISTS pganalyze.get_column_stats;
CREATE FUNCTION pganalyze.get_column_stats() RETURNS TABLE(
  schemaname name, tablename name, attname name, inherited bool, null_frac real, avg_width int, n_distinct real, correlation real
) AS $$
  /* pganalyze-collector */
  SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, n_distinct, correlation
  FROM pg_catalog.pg_stats
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename <> 'pg_subscription';
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

DROP FUNCTION IF EXISTS pganalyze.get_relation_stats_ext;
CREATE FUNCTION pganalyze.get_relation_stats_ext() RETURNS TABLE(
  statistics_schemaname text, statistics_name text,
  inherited boolean, n_distinct pg_ndistinct, dependencies pg_dependencies,
  most_common_val_nulls boolean[], most_common_freqs float8[], most_common_base_freqs float8[]
) AS
$$
  /* pganalyze-collector */ SELECT statistics_schemaname::text, statistics_name::text,
  (row_to_json(se.*)::jsonb ->> 'inherited')::boolean AS inherited, n_distinct, dependencies,
  most_common_val_nulls, most_common_freqs, most_common_base_freqs
  FROM pg_catalog.pg_stats_ext se
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename <> 'pg_subscription';
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Note: We never collect actual table data through this method (see the NULL values in the get_column_stats function and omitted fields like most_common_vals in the get_relation_stats_ext function), but we do collect statistics about the distribution of values in your tables. You can skip creating the get_column_stats and get_relation_stats_ext helper functions if the database contains highly sensitive information and statistics about it should not be collected. This will impact the accuracy of Index Advisor recommendations.

Supabase installs extensions like pg_stat_statements into a dedicated extensions schema, rather than public. Since the pganalyze user doesn’t have access to that schema by default, grant it explicitly (still connected as postgres):

GRANT USAGE ON SCHEMA extensions TO pganalyze;
GRANT SELECT ON extensions.pg_stat_statements TO pganalyze;

Write down the username and password of the monitoring user, we will need it in later steps of this guide.

Next we continue by installing the pganalyze collector:

Proceed to Step 3: Install the Collector

Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →