Installing the Performance Advisor agent for PostgreSQL

The Performance Advisor agent collects PostgreSQL database metadata to provide index optimization recommendations. This guide covers the prerequisites and installation steps.

Prerequisites

Before installing the Performance Advisor agent, ensure you have:

• PostgreSQL with pg_stat_statements extension - Required for query statistics collection.
• A database user with appropriate permissions - The agent needs read-only access to system catalogs and statistics.

Step 1: Enable the pg_stat_statements extension

The pg_stat_statements extension must be installed and enabled in your PostgreSQL database. This extension tracks query execution statistics that the Performance Advisor uses for analysis.

Check if already enabled

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

Enable the extension

If not already enabled, run the following as a superuser:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

📘

Note

Enabling pg_stat_statements may require adding it to shared_preload_libraries in your PostgreSQL configuration and restarting the database. Consult your database administrator or cloud provider documentation for specific instructions.

Step 2: Create a database user for the agent

The Performance Advisor agent requires a dedicated database user with read-only access to PostgreSQL system catalogs. Create this user with the minimum required permissions.

Create the user

-- Create a dedicated user for the Performance Advisor agent
CREATE USER index_advisor_agent WITH PASSWORD 'your_secure_password_here';

Grant required permissions

The agent needs the pg_read_all_stats role for access to query statistics views.

GRANT pg_read_all_stats TO index_advisor_agent;

Step 3: Enable Performance Advisor in the console

Performance Advisor is enabled per cache, not per database. Your database must have a DBO cache group defined in the console before you can proceed — the cache does not need to be deployed to a cluster. If you haven't created one yet, see Getting started with DBO.

Once your cache is in place:

1. In the Cast AI console, go to Database → Performance Advisor.
2. Select the cache associated with the database you want to analyze.
3. Click Enable.

The console will display Helm installation instructions specific to your cache, including your API key and database identifier.

Step 4: Install the agent via Helm

Follow the Helm installation instructions provided in the console.

Using Kubernetes secrets for credentials (recommended)

For production deployments, store credentials in Kubernetes secrets instead of passing them directly to the Helm chart.

API key secret

Create a secret containing your Cast AI API key:

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

Database credentials secret

Create a secret containing your database credentials:

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

Then reference them in your values.yaml for the Helm chart:

apiKeySecretRef: castai-db-agent-api-key

database:
  host: "your_database_host"
  port: 5432
  protocol: "postgres"
  credentialsSecretRef: castai-db-agent-db-credentials

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 install castai-db-agent castai-helm/castai-db-agent \
  --namespace castai-db-optimizer \
  --create-namespace \
  --values values.yaml

To verify the agent is running:

kubectl get pods -n castai-db-optimizer

Once the agent is healthy, it will begin collecting query statistics and index metadata. Recommendations will appear in the Cast AI console within a few minutes.

Cloud SQL Proxy (optional)

Use this option to connect via the Cloud SQL instance's Connection Name instead of a direct IP address.

Prerequisites

Before you begin, ensure you have the following:

• A GKE cluster with network access to your Cloud SQL instances
• kubectl configured to access your cluster
• helm CLI installed
• Appropriate IAM permissions for your Cloud SQL instances
• Service account with the following Cloud SQL permissions:

  roles/cloudsql.client
  roles/cloudsql.instanceUser

Configuration

The following changes are required in your values.yaml file:

• database.host value needs to be set to the instance connection name
• cloudSqlProxy section needs to be added
• cloudsqladmin database needs to be excluded from collection

Example values.yaml

database:
  host: <Connection name>
  
cloudSqlProxy:
  enabled: true
  port: 10000
  privateIp: true
  autoIamAuthn: true
collectors:
  excludedDatabases: "cloudsqladmin"

AWS RDS IAM authentication (optional)

Instead of a static username/password, you can configure passwordless authentication using AWS IAM roles. This creates an IAM role bound to the db-agent Kubernetes service account via IRSA, and enables IAM auth on the RDS instance.

Prerequisites

• EKS cluster with an OIDC provider associated
• RDS PostgreSQL instance with IAM database authentication enabled
• IAM role with rds-db:connect permission

Step 1: Enable IAM authentication on the RDS instance

aws rds modify-db-instance \
  --db-instance-identifier <your-db-instance> \
  --enable-iam-database-authentication \
  --apply-immediately

Step 2: Create a PostgreSQL user with the rds_iam role

CREATE USER index_advisor_agent;
GRANT rds_iam TO index_advisor_agent;

Then grant the catalog permissions from Step 2 above.

Step 3: Create an IAM role for the db-agent

Create an IAM policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "rds-db:connect",
      "Resource": "arn:aws:rds-db:<region>:<account-id>:dbuser:<db-resource-id>/index_advisor_agent"
    }
  ]
}

Attach a trust policy scoped to the db-agent service account in the castai-db-optimizer namespace:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Federated": "arn:aws:iam::<account-id>:oidc-provider/<oidc-provider-url>"
      },
      "Action": "sts:AssumeRoleWithWebIdentity",
      "Condition": {
        "StringEquals": {
          "<oidc-provider-url>:sub": "system:serviceaccount:castai-db-optimizer:castai-db-agent"
        }
      }
    }
  ]
}

📘

Note

The service account name defaults to castai-db-agent (the chart name) when serviceAccount.create: true.

Configuration

Add the following to your values.yaml:

serviceAccount:
  create: true
  annotations:
    eks.amazonaws.com/role-arn: arn:aws:iam::<account-id>:role/<role-name>

database:
  host: "<your-rds-endpoint>"
  port: 5432
  username: "index_advisor_agent"
  useRDSIAMAuth: true

📘

Note

By default, the db-agent pod uses the default service account in the castai-db-optimizer namespace. Setting serviceAccount.create: true creates a dedicated service account (named castai-db-agent) with the IRSA annotation attached. You can also use an existing annotated service account by setting serviceAccount.create: false and serviceAccount.name: <your-sa>.

📘

Note

Do not set database.password when useRDSIAMAuth is enabled — the chart will reject it.