Step 1: Create Monitoring User
We recommend creating a separate monitoring user on your PostgreSQL database for pganalyze.
Connect to the database you will be monitoring as the avnadmin user. Then run the following to create a new user (we've generated a random password for you, but you can replace it with one of your choosing):
CREATE USER pganalyze WITH PASSWORD 'mypassword' CONNECTION LIMIT 5;
CREATE SCHEMA pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
GRANT USAGE ON SCHEMA public TO pganalyze;
CREATE OR REPLACE FUNCTION pganalyze.get_stat_replication() RETURNS SETOF pg_stat_replication AS
$$
/* pganalyze-collector */ SELECT * FROM pg_catalog.pg_stat_replication;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
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 each database that you plan to monitor on this server as avnadmin 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;
CREATE OR REPLACE FUNCTION pganalyze.get_column_stats() RETURNS SETOF pg_stats AS
$$
/* pganalyze-collector */ SELECT schemaname, tablename, attname, inherited, null_frac, avg_width,
n_distinct, NULL::anyarray, most_common_freqs, NULL::anyarray, correlation, NULL::anyarray,
most_common_elem_freqs, elem_count_histogram
FROM pg_catalog.pg_stats
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename <> 'pg_subscription';
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
CREATE OR REPLACE 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.
Note that it is important you run these as the avnadmin user in order to pass down the full access to statistics tables.
Write down the username and password of the monitoring user, we will need it in later steps of this guide.
Next we continue by enabling pg_stat_statements:
Proceed to Step 2: Enabling pg_stat_statementsCouldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →