Installing the agent for PostgreSQL

Performance Advisor collects its data through a lightweight agent that you deploy as a Kubernetes workload next to your PostgreSQL database. The agent observes your query workload and metadata through read-only access and feeds the recommendations shown in the console. For MySQL, see Installing the agent for MySQL.

Prerequisites

  • PostgreSQL with the pg_stat_statements extension enabled.
  • A database user with read-only access to system catalogs and statistics.
  • Helm and kubectl configured against the cluster where the agent will run.

Step 1: Enable the pg_stat_statements extension

Check whether the extension is already installed and which schema it lives in:

SELECT e.extname, n.nspname AS schema
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
WHERE e.extname = 'pg_stat_statements';

If the extension is not installed yet, create it as a superuser, explicitly in the public schema:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;

If the extension is already installed in a schema other than public, move it to public as a superuser:

ALTER EXTENSION pg_stat_statements SET SCHEMA public;
⚠️

Warning

The agent sets the search path of its connections to public, pg_catalog automatically, so you don't need to configure the search path on your side. Because of this, the extension must be installed in the public schema.

Enabling it may require adding pg_stat_statements to shared_preload_libraries in your PostgreSQL configuration and restarting the database. On AWS RDS, set shared_preload_libraries via a parameter group and reboot the instance.

Step 2: Create a database user for the agent

CREATE USER performance_advisor_agent WITH PASSWORD 'your_secure_password_here';
GRANT pg_read_all_stats TO performance_advisor_agent;

The pg_read_all_stats role gives the agent access to the query statistics views without any write privileges.

Step 3: Enable Performance Advisor in the console

  1. On the organization Overview, click Enable database optimizer on the instance's row (Not onboarded tab).

  2. In the Enable Database optimization dialog, confirm Performance advisor is ticked under Optimization features. It is pre-selected and marked Recommended.

  3. Under Run script below, choose how to install:

    • Helm: download the provided values.yaml, add your database connection details and credentials, and run the generated helm upgrade --install command.
    • Script: copy the curl command and run it as-is. It asks for a read-only database user's Username and Password. Credentials are shown once and not stored.
  4. Finish with I ran the script.

Your database must be onboarded to DBO first (see Getting started).

The Helm values.yaml

The downloadable values.yaml already contains your API key and organization ID in a db-agent: block:

db-agent:
  enabled: true
  apiKey: <your-api-key>
  apiURL: https://api.cast.ai
  nameOverride: ""
  organizationID: <your-organization-id>
Enable Database optimization dialog, Helm tab, with the Configure database permissions, Access repository, Customize values, and Install steps

The Enable Database optimization dialog's Helm tab, with the Configure database permissions, Access repository, Customize values, and Install steps.

The Script tab is the copy-paste alternative, asking only for the read-only agent user's Username and Password:

Enable Database optimization dialog, Script tab, with the Configure user permission and Install steps

The Enable Database optimization dialog's Script tab, with the Configure user permission and Install steps.

Once the agent is installed and healthy, the status row on every instance page shows ADVISOR AGENT Active, and the instance Settings page shows a Performance advisor card with an Active badge:

Instance Settings page with the Performance advisor card showing an Active badge

The instance Settings page's Performance advisor card, showing an Active badge once the agent is healthy.

Step 4: Install the agent via Helm

Store credentials in Kubernetes secrets rather than passing them directly to the Helm chart:

kubectl create secret generic castai-db-agent-api-key \
  --namespace castai-db-optimizer \
  --from-literal=API_KEY='your-api-key-here'

kubectl create secret generic castai-db-agent-db-credentials \
  --namespace castai-db-optimizer \
  --from-literal=DATABASE_USERNAME='performance_advisor_agent' \
  --from-literal=DATABASE_PASSWORD='your_secure_password_here'

Then add the Cast AI Helm repository and install the agent:

helm repo add castai-helm https://castai.github.io/helm-charts
helm repo update
helm upgrade --install castai-db-agent castai-helm/castai-dbo \
  --namespace castai-db-optimizer --create-namespace \
  --values values.yaml

Verify that the agent is running:

kubectl get pods -n castai-db-optimizer

Once the pods are healthy, recommendations appear in the console within a few minutes.

Excluding databases from analysis

If a database on the instance should not be scanned — for example, a template, scratch, or staging database — add it to the agent's excludedDatabases list. Performance Advisor skips excluded databases during collection and does not generate recommendations for them.

Add the database names as a comma-separated list under collectors in the db-agent: block of your values.yaml:

db-agent:
  collectors:
    excludedDatabases: "template0,scratch_db"

Then redeploy the agent with the same command you used to install it:

helm upgrade --install castai-db-agent castai-helm/castai-dbo \
  --namespace castai-db-optimizer --create-namespace \
  --values values.yaml

Alternatively, set the list directly on the existing release without editing values.yaml:

helm upgrade castai-db-agent castai-helm/castai-dbo \
  --namespace castai-db-optimizer \
  --reset-then-reuse-values \
  --set db-agent.collectors.excludedDatabases="template0,scratch_db"

To analyze an excluded database again, remove it from the list and redeploy. For the full list of agent configuration options, see the castai-db-agent chart reference.

Managing the agent

To remove the agent, open the instance Settings page and click Uninstall on the Performance advisor card.

Optional connection methods

  • Cloud SQL Proxy (GCP): connect through the Cloud SQL instance's Connection Name instead of a direct IP.
  • AWS RDS IAM authentication: passwordless authentication via an IAM role bound to the db-agent service account (IRSA), instead of a static username and password. Create the database user with the rds_iam role (CREATE USER performance_advisor_agent; GRANT rds_iam TO performance_advisor_agent;), grant the same pg_read_all_stats permissions as in step 2, enable IAM database authentication on the RDS instance, and give the IAM role the rds-db:connect permission.

Next steps


Did this page help you?