Snowflake Postgres Insights¶

Insights is a Postgres extension that continuously collects performance and health metrics from your Snowflake Postgres instance and stores them inside the database. It samples standard Postgres statistics views, such as pg_stat_activity, pg_stat_database, and pg_stat_statements, on a schedule. It then keeps a rolling history and automatically rolls that history up into hourly and daily summaries, expected-range baselines, and capacity forecasts.

Because the data lives in ordinary tables in the insights schema, you can query it with plain SQL. No external agent, collector, or time-series database is required. This makes pgInsights a convenient backend for dashboards (for example Grafana), ad-hoc troubleshooting, and capacity planning.

What pgInsights collects¶

Enabling Insights¶

Create the extension¶

Connect to the database you want to monitor, then create the extension. Creating insights also creates pg_stat_statements automatically through the CASCADE option:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE EXTENSION insights CASCADE;

All objects are created in the insights schema. The background workers begin collecting on their schedule immediately; no further action is required.

Verify the installation¶

-- Confirm the extension and its version
SELECT extname, extversion FROM pg_extension WHERE extname = 'insights';

-- List the registered collections
SELECT col_name, col_type, enabled, collect_interval
FROM   insights.pgi_collection
ORDER  BY col_type, col_name;

-- Confirm the background workers are running
SELECT pid, application_name
FROM   pg_stat_activity
WHERE  application_name LIKE 'insights%';

You can also confirm that data is landing:

SELECT col_name, metric_name, metric_value, snapshot_dt
FROM   insights.pgi_metric
ORDER  BY snapshot_dt DESC
LIMIT  20;

How it works¶

pgInsights runs a simple, automatic pipeline. You normally don’t call these steps by hand; the background workers do that for you. Understanding the flow makes the tables easier to interpret.

pgi_snapshot()                     every ~30s (per collection schedule)
   collects from pg_stat_*, pg_stat_statements, /proc
        │
        ▼
pgi_metric            current value of every metric (one row per metric)
pgi_metric_history    every snapshot, ~30s granularity        (30-day retention)
        │
        ├─▶ hourly rollup ─▶ pgi_metric_summary_hour          (180-day retention)
        │                          │
        │                          ├─▶ pgi_metric_trend      expected range per hour-of-week
        │                          
        └─▶ daily rollup  ─▶ pgi_metric_summary_day           (360-day retention)
                                   │
                                   └─▶ pgi_metric_forecast    linear projection up to 180 days

• Collections are the named units of work (for example pg_session). Each one is due on its own interval.
• Metrics are the individual values a collection emits (for example active, idle). Multi-valued metrics carry a JSON key (for example {"datname": "mydb"}).
• Properties are slowly-changing settings (for example server configuration) tracked separately, with a history of changes.

Reference: database tables¶

All pgInsights objects are in the insights schema. The tables you’ll query most often are listed first.

Common columns in the metric tables:

Reference: collections and metrics¶

pgInsights ships with about 34 collections. Most run every 30 seconds; larger or more expensive collections run less often. Two collections are disabled by default.

Metrics: every 30 seconds¶

Metrics: other intervals¶

Metrics: disabled by default¶

Properties: every day¶

Example: pg_session metrics¶

Each collection emits a defined set of metrics. For example, pg_session is keyed by {"datname": …} and emits these gauges:

SELECT metric_name, metric_key, metric_value
FROM   insights.pgi_metric
WHERE  col_name = 'pg_session';

Setting server parameters¶

A few pgInsights settings, such as insights.enabled and the insights.retention_* windows, are PostgreSQL server parameters. On Snowflake Postgres, you can’t change server parameters with ALTER SYSTEM, by editing postgresql.conf, or by calling pg_reload_conf(). Instead, set them on the instance through Snowsight. You can use either the instance settings UI or the ALTER POSTGRES INSTANCE SQL command with the POSTGRES_SETTINGS clause, keyed as component:parameter_name:

ALTER POSTGRES INSTANCE my_instance
  SET POSTGRES_SETTINGS = '{"postgres:insights.retention_raw": "14d"}'
  APPLY IMMEDIATELY;

None of the parameters below require an instance restart, so you don’t need to wait for a maintenance window. APPLY IMMEDIATELY applies the change as soon as it’s ready. For the full server-settings mechanism and the Snowsight UI steps, see Snowflake Postgres server settings, ALTER POSTGRES INSTANCE, and Managing instances.

GUC parameter reference¶

Every parameter pgInsights registers is prefixed with insights.. Time-based values are stored in seconds. When setting one, use a plain number of seconds or a Postgres duration abbreviation (s, min, h, or d; for example 14d, not 14 days).

Managing collections¶

Collection behavior is controlled by the pgi_collection table. Changes take effect on the background worker’s next scheduling cycle; no restart is needed.

Enable or disable a collection¶

-- Turn on the exact database-size collector
UPDATE insights.pgi_collection SET enabled = true  WHERE col_name = 'pg_db_size';

-- Turn off a high-volume collection
UPDATE insights.pgi_collection SET enabled = false WHERE col_name = 'pg_stat_io';

Change how often a collection runs¶

-- Sample connections every 15 seconds instead of 30
UPDATE insights.pgi_collection
   SET collect_interval = '15 seconds'::interval
 WHERE col_name = 'pg_session';

-- Estimate database size every 6 hours instead of hourly
UPDATE insights.pgi_collection
   SET collect_interval = '6 hours'::interval
 WHERE col_name = 'pg_db_size_est';

Force a collection to run now¶

pgi_snapshot() runs only the collections whose next_collect time has passed. Reset the watermark to collect on the next cycle:

UPDATE insights.pgi_collection
   SET next_collect = current_timestamp
 WHERE col_name = 'pg_db_size_est';

Pause all background collection¶

To stop the background workers without removing the extension, set the insights.enabled server parameter to off. On Snowflake Postgres, server parameters are changed through Snowsight, either with SQL or the UI, not with ALTER SYSTEM, postgresql.conf, or pg_reload_conf(). Using ALTER POSTGRES INSTANCE:

ALTER POSTGRES INSTANCE my_instance
  SET POSTGRES_SETTINGS = '{"postgres:insights.enabled": "off"}'
  APPLY IMMEDIATELY;

Set it back to "on" the same way to resume. See Setting server parameters below for details.

GRANT UPDATE (enabled, collect_interval, next_collect)
    ON insights.pgi_collection
    TO my_dba_role;

Data retention¶

Old rows are removed automatically by a scheduled purge task. Retention windows are server parameters, so you can change the defaults without touching the schema.

To change a default, set the corresponding server parameter through Snowsight (see Setting server parameters). For example, to keep raw history for two weeks:

ALTER POSTGRES INSTANCE my_instance
  SET POSTGRES_SETTINGS = '{"postgres:insights.retention_raw": "14d"}'
  APPLY IMMEDIATELY;

To apply a retention window immediately without changing a server parameter, call the purge procedure directly with explicit intervals. This is a plain SQL call that any user with access to the schema can run:

-- metric_history, metric_hour, metric_day, property_history
CALL insights.pgi_snapshot_purge(
    interval '14 days',
    interval '180 days',
    interval '360 days',
    interval '60 days'
);

Sample queries¶

Current connection state¶

SELECT metric_name, metric_value::numeric AS value
FROM   insights.pgi_metric
WHERE  col_name = 'pg_session'
ORDER  BY metric_name;

Connection count over the last hour¶

SELECT snapshot_dt, metric_value::numeric AS active_connections
FROM   insights.pgi_metric_history
WHERE  col_name = 'pg_session'
  AND  metric_name = 'active'
  AND  snapshot_dt > now() - interval '1 hour'
ORDER  BY snapshot_dt;

Peak and average connections by hour (last 7 days)¶

SELECT rollup_dt AS hour,
       max_val   AS peak_connections,
       avg_val   AS avg_connections
FROM   insights.pgi_metric_summary_hour
WHERE  col_name = 'pg_session'
  AND  metric_name = 'active'
  AND  rollup_dt > now() - interval '7 days'
ORDER  BY hour;

Active session wait events (last 5 minutes)¶

SELECT wait_event_type, wait_event, count(*) AS sessions
FROM   insights.pgi_wait_event
WHERE  snapshot_dt > now() - interval '5 minutes'
  AND  wait_event_type IS NOT NULL
GROUP  BY wait_event_type, wait_event
ORDER  BY sessions DESC;

Top 10 queries by total execution time¶

SELECT s.query,
       m.calls,
       round(m.total_exec_time::numeric, 2) AS total_ms,
       round(m.mean_exec_time::numeric, 2)  AS mean_ms,
       m.datname,
       m.rolname
FROM   insights.pgi_sql_metric_history m
JOIN   insights.pgi_sql s USING (queryid, datname, rolname)
WHERE  m.snapshot_dt = (SELECT max(snapshot_dt) FROM insights.pgi_sql_metric_history)
ORDER  BY m.total_exec_time DESC
LIMIT  10;

PostgreSQL configuration changes over time¶

SELECT col_name, property_name,
       property_value_old, property_value_new,
       change_type, snapshot_dt
FROM   insights.pgi_property_history
ORDER  BY snapshot_dt DESC
LIMIT  20;

Metrics currently outside their expected range (anomalies)¶

pgi_metric_trend records the normal range for each metric at each hour of the week. Join current values against it to surface anything unusual for the current day-of-week and hour:

SELECT m.col_name, m.metric_name,
       m.metric_value::numeric AS current_val,
       t.floor_val, t.ceiling_val,
       CASE WHEN m.metric_value::numeric > t.ceiling_val THEN 'HIGH' ELSE 'LOW' END AS direction
FROM   insights.pgi_metric m
JOIN   insights.pgi_metric_trend t
  ON   m.col_name    = t.col_name
 AND   m.metric_name = t.metric_name
 AND   COALESCE(m.metric_key::text, '') = COALESCE(t.metric_key::text, '')
 AND   t.dow = EXTRACT(dow  FROM now())::int
 AND   t.hr  = EXTRACT(hour FROM now())::int
WHERE  m.data_type = 'number'
  AND  (m.metric_value::numeric < t.floor_val OR m.metric_value::numeric > t.ceiling_val)
ORDER  BY m.col_name, m.metric_name;

Capacity forecast¶

SELECT forecast_dt, avg_val AS projected_connections
FROM   insights.pgi_metric_forecast
WHERE  col_name = 'pg_session'
  AND  metric_name = 'active'
ORDER  BY forecast_dt;

Query privacy¶

pg_stat_statements normalizes literal values out of most queries, so parameter values don’t appear in pgi_sql. In addition, pgInsights deliberately doesn’t store administrative and session-management statements that carry no performance value and could contain secrets, including CREATE/ALTER/DROP USER/ROLE/GROUP, CREATE SUBSCRIPTION, CREATE SERVER, CREATE USER MAPPING, SET, RESET, SHOW, DISCARD, DEALLOCATE, and COMMENT.

Granting read access¶

Objects are owned by the snowflake_insights role. To let another user read pgInsights data:

GRANT USAGE ON SCHEMA insights TO your_user;
GRANT SELECT ON ALL TABLES IN SCHEMA insights TO your_user;

Uninstalling¶

DROP EXTENSION insights CASCADE;

To also stop the background workers, set the insights.enabled server parameter to off through Snowsight (see Setting server parameters). Removing insights from the instance’s preloaded libraries requires a restart and is also a server-parameter change made through Snowsight.

Troubleshooting¶

No data is being collected¶

Confirm collections are enabled and due:

SELECT col_name, enabled, next_collect,
       (next_collect <= current_timestamp) AS due_now
FROM   insights.pgi_collection
ORDER  BY col_type, col_name;

Confirm the background workers are running:

SELECT pid, application_name FROM pg_stat_activity
WHERE  application_name LIKE 'insights%';

If no insights% workers appear, confirm that insights is preloaded and enabled. These are instance server parameters, changed through Snowsight (see Setting server parameters). You can read their current values with:

SHOW shared_preload_libraries;
SHOW insights.enabled;

SQL statistics are empty¶

Verify pg_stat_statements is installed, preloaded, and tracking:

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
SHOW shared_preload_libraries;
SHOW pg_stat_statements.track;

Permission denied reading insights tables¶

Grant read access as shown in Granting read access.