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

Prerequisites

Before installing the Index 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 Index 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.

Enable via a parameter group:

Set shared_preload_libraries to include pg_stat_statements
Reboot the instance
Run CREATE EXTENSION pg_stat_statements;

Step 2: Create a Database User for the Agent

The Index 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 Index Advisor agent
CREATE USER index_advisor_agent WITH PASSWORD 'your_secure_password_here';

Grant Required Permissions

The agent needs access to system catalogs, statistics views, and SELECT permission on all user tables to generate execution plans via EXPLAIN.

-- ============================================================
-- CRITICAL: Grant SELECT on all tables for EXPLAIN to work
-- ============================================================
-- The agent runs EXPLAIN on queries to collect execution plans.
-- Without SELECT permission on the tables, all EXPLAIN operations will fail.

-- Grant SELECT on all existing tables in the public schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO index_advisor_agent;

-- Grant SELECT on future tables created in the public schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO index_advisor_agent;

-- If you have additional schemas, repeat for each:
-- GRANT USAGE ON SCHEMA your_schema TO index_advisor_agent;
-- GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO index_advisor_agent;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema GRANT SELECT ON TABLES TO index_advisor_agent;

-- ============================================================
-- Statistics access via predefined role
-- ============================================================
-- pg_read_all_stats grants access to all pg_stat_* views including
-- pg_stat_statements, pg_stat_user_indexes, pg_stat_user_tables, etc.

GRANT pg_read_all_stats TO index_advisor_agent;

-- ============================================================
-- System catalog permissions (not covered by pg_read_all_stats)
-- ============================================================

GRANT SELECT ON pg_catalog.pg_index TO index_advisor_agent;
GRANT SELECT ON pg_catalog.pg_class TO index_advisor_agent;
GRANT SELECT ON pg_catalog.pg_namespace TO index_advisor_agent;
GRANT SELECT ON pg_catalog.pg_am TO index_advisor_agent;
GRANT SELECT ON pg_catalog.pg_attribute TO index_advisor_agent;
GRANT SELECT ON pg_catalog.pg_type TO index_advisor_agent;
GRANT SELECT ON pg_catalog.pg_database TO index_advisor_agent;
GRANT SELECT ON pg_catalog.pg_stats TO index_advisor_agent;
GRANT SELECT ON pg_catalog.pg_extension TO index_advisor_agent;
GRANT SELECT ON pg_catalog.pg_prepared_statements TO index_advisor_agent;

-- ============================================================
-- Function permissions
-- ============================================================

GRANT EXECUTE ON FUNCTION pg_catalog.pg_get_indexdef(oid) TO index_advisor_agent;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_get_indexdef(oid, integer, boolean) TO index_advisor_agent;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_relation_size(regclass) TO index_advisor_agent;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_total_relation_size(regclass) TO index_advisor_agent;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_get_expr(pg_node_tree, oid) TO index_advisor_agent;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_get_expr(pg_node_tree, oid, boolean) TO index_advisor_agent;

-- ============================================================
-- pg_stat_statements_reset permission
-- Required to reset statistics after collection
-- ============================================================

-- For PostgreSQL 17+ (4 parameters - with minmax_only boolean):
GRANT EXECUTE ON FUNCTION pg_stat_statements_reset(oid, oid, bigint, boolean) TO index_advisor_agent;

-- For PostgreSQL 15 and earlier (3 parameters):
-- Run this instead if on PostgreSQL 15 or earlier:
-- GRANT EXECUTE ON FUNCTION pg_stat_statements_reset(oid, oid, bigint) TO index_advisor_agent;

Step 3: Enable Index Advisor in the Console

Navigate to the Cast AI console.
Locate the cache you want to enable the Index Advisor for.
Click Enable on the cache.

The console will display Helm installation instructions specific to your cache.

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: your_database_port
  credentialsSecretRef: castai-db-agent-db-credentials