PostgreSQL

Configure alert rules for PostgreSQL data sources with standard SQL syntax support

Monitors supports using standard SQL syntax to query PostgreSQL and trigger alerts based on query results.

Core Concepts

Config Item	Description
Query Language	Uses PostgreSQL SQL syntax
Field Processing	All field names are automatically converted to lowercase; please use lowercase letters when configuring
Time Processing	Recommended to use NOW(), CURRENT_TIMESTAMP and other functions for time filtering

1. Threshold Evaluation Mode

This mode is suitable for scenarios requiring threshold comparison on aggregated values.

Configuration

1. Query Statement: Write SQL aggregate query, returning value columns and (optional) label columns.

• Example: Count error log quantity by service in the last 5 minutes.
  SELECT 
      service_name, 
      count(*) AS error_cnt 
  FROM app_log 
  WHERE log_time > NOW() - INTERVAL '5 minutes' AND level = 'error'
  GROUP BY service_name
  

2. Field Mapping:

• Label Fields: Fields used to distinguish different alert objects. In the above example, it’s service_name. This field can be left empty; Monitors will automatically treat all fields except value fields as label fields.
• Value Fields: Numeric fields used for threshold evaluation. In the above example, it’s error_cnt.

3. Threshold Conditions:

• Use $A.field_name to reference values.
• Example: Critical: $A.error_cnt > 50, Warning: $A.error_cnt > 10.

How It Works

The engine executes SQL query and gets the result set. It groups data by “label fields”, then extracts “value fields” values to compare against threshold expressions.

Recovery Logic

Strategy	Description
Auto Recovery	When values no longer satisfy any alert threshold, automatically generates recovery event
Specific Recovery Condition	Configure recovery expression (e.g., $A.error_cnt < 5)
Recovery Query	Independent SQL for recovery evaluation, supports ${label_name} variables

2. Data Exists Mode

This mode is suitable for scenarios where filter logic is written directly in SQL.

Configuration

1. Query Statement: Use HAVING clause in SQL to directly filter out anomalous data.

• Example: Directly query services with error count exceeding 50.
  SELECT 
      service_name, 
      count(*) AS error_cnt 
  FROM app_log 
  WHERE log_time > NOW() - INTERVAL '5 minutes' AND level = 'error'
  GROUP BY service_name
  HAVING count(*) > 50
  

2. Evaluation Rules: As long as SQL query returns data, triggers alert.

Pros and Cons Analysis

Type	Description
Pros	Leverages PostgreSQL database’s computing power for filtering, reducing network transmission
Cons	Cannot differentiate multi-level alerts

Recovery Logic

• Recovery When Data Disappears: When SQL query result is empty, determines recovery
• Recovery Query: Supports configuring additional query statements to assist in determining recovery status

3. No Data Mode

This mode is used to monitor scenarios where “data is expected but actually missing”.

Configuration

1. Query Statement: Write a SQL query that is expected to continuously return data.

• Example: Query heartbeat reports from all probes.
  SELECT probe_id, max(check_time) as last_seen
  FROM probe_heartbeat
  WHERE check_time > NOW() - INTERVAL '5 minutes'
  GROUP BY probe_id
  

2. Evaluation Rules: If a probe_id appeared in previous cycles but cannot be found in current and N consecutive cycles, triggers “No Data” alert.

4. Best Practices

Index Optimization

Always include time range filtering in WHERE clause and ensure the time field has an index, otherwise it may cause full table scan.

Recommended syntax: log_time > NOW() - INTERVAL '5 minutes'

Field Case

Monitors engine converts column names returned by PostgreSQL to lowercase. When filling in “label fields” and “value fields”, always use lowercase letters.