Skip to main content
The queries section defines the SQL queries that will be executed against databases to populate metrics. Each query is configured with a unique name as the key.

​
Query Structure

​
queries
object
required
Dictionary of query configurations, keyed by query name.
queries:
  user_count:
    databases: [main_db]
    metrics: [active_users]
    sql: SELECT COUNT(*) as active_users FROM users WHERE active = true

​
Required Fields

​
databases
array
required
List of database names to run this query against.Database names must reference databases defined in the databases section.Constraints:
  • Must contain at least 1 database name
  • Must not contain duplicate database names
queries:
  database_size:
    databases: [db1, db2, db3]
    metrics: [size_bytes]
    sql: SELECT pg_database_size(current_database()) as size_bytes
​
metrics
array
required
List of metric names to populate with query results.Metric names must reference metrics defined in the metrics section. Query results must include columns matching the metric names and their labels.Constraints:
  • Must contain at least 1 metric name
  • Must not contain duplicate metric names
queries:
  table_stats:
    databases: [postgres]
    metrics: [table_rows, table_size_bytes]
    sql: |
      SELECT 
        schemaname,
        tablename,
        n_live_tup as table_rows,
        pg_total_relation_size(schemaname||'.'||tablename) as table_size_bytes
      FROM pg_stat_user_tables
​
sql
string
required
The SQL query to execute.The query results should include:
  • Columns matching the metric names
  • Columns for each label defined in the metrics
Can reference external SQL files using !file tag.
queries:
  # Inline SQL
  simple_query:
    databases: [db]
    metrics: [count]
    sql: SELECT COUNT(*) as count FROM users
  
  # External SQL file
  complex_query:
    databases: [db]
    metrics: [metric1]
    sql: !file queries/complex.sql

​
Scheduling Fields

​
interval
string | integer
How often to run the query.Cannot be used with: schedule (must use one or the other)Format:
  • Integer: seconds
  • String: number followed by optional suffix (s=seconds, m=minutes, h=hours, d=days)
Constraints:
  • Must be a positive number
  • Must match pattern ^[0-9]+[smhd]?$ for string format
Validation: Setting both interval and schedule will raise: ValueError: can't set both interval and schedule
queries:
  frequent_check:
    databases: [db]
    metrics: [status]
    sql: SELECT status FROM health_check
    interval: 30s  # Run every 30 seconds
  
  daily_report:
    databases: [db]
    metrics: [daily_total]
    sql: SELECT SUM(amount) as daily_total FROM transactions
    interval: 1d  # Run once per day
​
schedule
string
Cron expression for when to run the query.Cannot be used with: interval (must use one or the other)Use this for more complex scheduling needs (e.g., specific times, days of week).Format: Standard cron expression (validated using croniter)Validation:
  • Must be a valid cron expression
  • Setting both interval and schedule will raise: ValueError: can't set both interval and schedule
queries:
  # Run every day at 2 AM
  nightly_cleanup:
    databases: [db]
    metrics: [cleaned_rows]
    sql: DELETE FROM temp_data WHERE created < NOW() - INTERVAL '7 days' RETURNING COUNT(*) as cleaned_rows
    schedule: "0 2 * * *"
  
  # Run every Monday at 9 AM
  weekly_report:
    databases: [db]
    metrics: [weekly_total]
    sql: SELECT SUM(amount) as weekly_total FROM orders
    schedule: "0 9 * * 1"

​
Optional Fields

​
timeout
number
Query timeout in seconds.If the query takes longer than this timeout, it will be cancelled.Constraints:
  • Must be greater than 0
  • Must be a multiple of 0.1 (one decimal place precision)
queries:
  long_running_query:
    databases: [db]
    metrics: [result]
    sql: SELECT expensive_calculation() as result
    timeout: 30.0  # 30 seconds timeout
​
parameters
array | object
Query parameters for running the same query with different parameter values.This allows running a single query multiple times with different parameters, creating separate metric series for each parameter set.Two formats supported:
  1. List format: Simple list of parameter sets
  2. Matrix format: Dictionary with parameter groups that are combined
Constraints:
  • If specified, must contain at least 1 parameter set
See examples below for detailed usage.

​
Query Parameters

​
List Format

Provide a simple list of parameter dictionaries. The query runs once for each set. 
queries:
  table_size:
    databases: [db]
    metrics: [size_bytes]
    sql: |
      SELECT 
        :table_name as table_name,
        pg_total_relation_size(:table_name) as size_bytes
    parameters:
      - {table_name: users}
      - {table_name: orders}
      - {table_name: products}
This runs the query 3 times, once for each table.

​
Matrix Format

Provide a dictionary where each key is a parameter group, and values are lists of parameter sets. Query Exporter will run the query for every combination (Cartesian product) of parameters. 
queries:
  region_status:
    databases: [db]
    metrics: [service_count]
    sql: |
      SELECT COUNT(*) as service_count 
      FROM services 
      WHERE region = :region__name 
        AND status = :status__value
    parameters:
      region:
        - {name: us-east}
        - {name: us-west}
        - {name: eu-west}
      status:
        - {value: active}
        - {value: inactive}
This creates 6 combinations (3 regions × 2 statuses) and runs the query 6 times:
  • region__name=us-east, status__value=active
  • region__name=us-east, status__value=inactive
  • region__name=us-west, status__value=active
  • region__name=us-west, status__value=inactive
  • region__name=eu-west, status__value=active
  • region__name=eu-west, status__value=inactive
Note: When using matrix format, parameter names are prefixed with the group key and __ (e.g., region__name, status__value).

​
Parameters with Labels

Parameters are often used to create metric labels: 
metrics:
  table_rows:
    type: gauge
    labels: [table_name]

queries:
  count_rows:
    databases: [db]
    metrics: [table_rows]
    sql: |
      SELECT 
        :table_name as table_name,
        COUNT(*) as table_rows 
      FROM :table_name
    parameters:
      - {table_name: users}
      - {table_name: orders}

​
Complete Examples

​
Simple Query with Interval

queries:
  active_connections:
    databases: [postgres]
    metrics: [connection_count]
    sql: SELECT COUNT(*) as connection_count FROM pg_stat_activity WHERE state = 'active'
    interval: 30s

​
Query with Schedule

queries:
  daily_summary:
    databases: [analytics]
    metrics: [daily_revenue, daily_orders]
    sql: |
      SELECT 
        SUM(amount) as daily_revenue,
        COUNT(*) as daily_orders
      FROM orders
      WHERE date = CURRENT_DATE
    schedule: "0 1 * * *"  # Run at 1 AM every day

​
Query with Timeout

queries:
  expensive_aggregation:
    databases: [warehouse]
    metrics: [total_sales]
    sql: SELECT SUM(amount) as total_sales FROM large_fact_table
    interval: 5m
    timeout: 60.0

​
Query with List Parameters

queries:
  per_table_stats:
    databases: [postgres]
    metrics: [table_size, table_rows]
    sql: |
      SELECT
        :table as table,
        pg_total_relation_size(:schema || '.' || :table) as table_size,
        (SELECT COUNT(*) FROM :schema.:table) as table_rows
    parameters:
      - {schema: public, table: users}
      - {schema: public, table: orders}
      - {schema: analytics, table: events}
    interval: 5m

​
Query with Matrix Parameters

queries:
  service_health:
    databases: [monitoring]
    metrics: [health_status]
    sql: |
      SELECT 
        :service__name as service,
        :env__name as environment,
        health_check(:service__name, :env__name) as health_status
    parameters:
      service:
        - {name: api}
        - {name: web}
        - {name: worker}
      env:
        - {name: production}
        - {name: staging}
    interval: 1m

​
Multi-Database Query

queries:
  database_size:
    databases: [prod_db, staging_db, dev_db]
    metrics: [db_size_bytes]
    sql: SELECT pg_database_size(current_database()) as db_size_bytes
    interval: 10m

​
Query with Multiple Metrics and Labels

metrics:
  table_stats_rows:
    type: gauge
    labels: [schema, table]
  table_stats_size:
    type: gauge
    labels: [schema, table]

queries:
  table_statistics:
    databases: [postgres]
    metrics: [table_stats_rows, table_stats_size]
    sql: |
      SELECT
        schemaname as schema,
        tablename as table,
        n_live_tup as table_stats_rows,
        pg_total_relation_size(schemaname || '.' || tablename) as table_stats_size
      FROM pg_stat_user_tables
    interval: 5m
    timeout: 10.0

​
Validation Rules

The following validation rules are enforced on query configurations:
  1. Database validation:
    • databases must contain at least 1 database name
    • Database names must not contain duplicates
  2. Metric validation:
    • metrics must contain at least 1 metric name
    • Metric names must not contain duplicates
  3. SQL validation:
    • sql is required and must be a non-empty string
  4. Scheduling validation:
    • Cannot set both interval and schedule
    • interval must be a positive number
    • interval string format must match ^[0-9]+[smhd]?$
    • schedule must be a valid cron expression
  5. Timeout validation:
    • Must be greater than 0
    • Must be a multiple of 0.1
  6. Parameter validation:
    • If specified, must contain at least 1 parameter set
    • In matrix format, each group must contain at least 1 parameter set

​
Error Messages

Common validation errors:
  • ValueError: can't set both interval and schedule - Both interval and schedule were specified
  • AssertionError: must not contain duplicate items - Duplicate database or metric names
  • AssertionError: invalid interval definition - Invalid interval format
  • croniter.CroniterBadCronError - Invalid cron expression in schedule
  • AssertionError: must be a positive number - Invalid timeout or interval value

​
Time Interval Format Reference

Time intervals can be specified as:
  • Integers: Interpreted as seconds
    • 30 = 30 seconds
    • 300 = 300 seconds (5 minutes)
  • Strings with suffixes:
    • s = seconds: 30s = 30 seconds
    • m = minutes: 5m = 5 minutes (300 seconds)
    • h = hours: 2h = 2 hours (7200 seconds)
    • d = days: 1d = 1 day (86400 seconds)

​
Cron Expression Format Reference

Cron expressions follow the standard format: 
┌───────────── minute (0 - 59)
│ ┌───────────── hour (0 - 23)
│ │ ┌───────────── day of month (1 - 31)
│ │ │ ┌───────────── month (1 - 12)
│ │ │ │ ┌───────────── day of week (0 - 6) (Sunday to Saturday)
│ │ │ │ │
│ │ │ │ │
* * * * *
Examples:
  • 0 * * * * - Every hour
  • 0 0 * * * - Every day at midnight
  • 30 2 * * * - Every day at 2:30 AM
  • 0 9 * * 1 - Every Monday at 9 AM
  • 0 0 1 * * - First day of every month at midnight
  • */15 * * * * - Every 15 minutes

Build docs developers (and LLMs) love

Get started for freeTalk to us