Spring Boot+PostgreSQL：持续插入相同数据1小时的邮件告警方案咨询

一、两种方案的优劣势分析 #

1. Spring Boot定时任务方案 #

优势

• 逻辑实现灵活：邮件发送、额外数据校验、和其他服务联动这类需求，用Java代码就能轻松搞定，不用写复杂的SQL或存储过程，修改调整都很方便。
• 调试维护省心：后端开发大多熟悉Java，排查问题比啃PL/pgSQL存储过程简单得多，团队协作成本更低。
• 扩展性强：比如后续要把告警条件改成连续45分钟、或者新增数据范围判断，直接修改代码即可，无需改动数据库层面的逻辑。

劣势

• 存在性能开销：每秒插入一条数据的场景下，定时任务如果执行频率过高（比如每分钟一次），会增加数据库查询压力；频率太低又可能延迟通知或错过触发时机。
• 依赖应用可用性：一旦Spring Boot服务宕机，整个监控告警逻辑就会中断，无法触发通知。

2. PostgreSQL触发器+存储过程方案 #

优势

• 实时性拉满：触发器在数据插入时立刻执行检查，不会有定时任务的延迟问题，能第一时间捕捉到触发条件。
• 不依赖应用服务：就算Spring Boot服务挂了，数据库自身的监控逻辑依然在运行，不会遗漏告警触发。
• 数据库层面闭环：所有数据相关逻辑都在数据库内处理，减少应用与数据库的交互次数，性能表现更优。

劣势

• 逻辑编写难度高：用PL/pgSQL实现连续相同值的时长判断，需要处理窗口函数、分组统计等复杂逻辑，调试和维护成本高，对开发人员的SQL能力要求苛刻。
• 扩展性差：后续修改告警规则需要改动存储过程和触发器，还要考虑数据库版本兼容性，远不如应用层修改灵活。
• 邮件发送受限：数据库本身不擅长发送邮件，要么依赖第三方扩展（如pgmail），要么通过触发器调用外部API，配置和维护都很麻烦。

二、推荐方案 #

推荐Spring Boot定时任务+数据库辅助查询的结合方案，兼顾灵活性与性能：

• 借助PostgreSQL的窗口函数快速计算连续相同值的时长，避免应用层处理大量数据；
• 以合理频率（比如每5分钟一次）执行定时任务，查询数据库判断是否满足连续1小时的告警条件；
• 满足条件时触发邮件通知，同时记录告警状态避免重复发送。

三、代码示例与实现指导 #

1. 数据库表结构与查询逻辑 #

假设传感器数据表结构如下：

CREATE TABLE sensor_data (
    id SERIAL PRIMARY KEY,
    sensor_value INT NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

用窗口函数计算连续相同值的时长，筛选出满足条件的记录：

WITH consecutive_groups AS (
    SELECT
        sensor_value,
        create_time,
        -- 用行号差值分组，把连续相同值的数据归为一组
        ROW_NUMBER() OVER (ORDER BY create_time) - 
        ROW_NUMBER() OVER (PARTITION BY sensor_value ORDER BY create_time) AS group_id
    FROM sensor_data
    WHERE create_time >= NOW() - INTERVAL '1 hour' -- 仅查询最近1小时数据
),
group_stats AS (
    SELECT
        sensor_value,
        MIN(create_time) AS start_time,
        MAX(create_time) AS end_time,
        EXTRACT(EPOCH FROM (MAX(create_time) - MIN(create_time))) AS duration_seconds
    FROM consecutive_groups
    GROUP BY sensor_value, group_id
)
SELECT sensor_value, start_time, end_time
FROM group_stats
WHERE duration_seconds >= 3600 -- 时长超过1小时
AND end_time >= NOW() - INTERVAL '5 minutes' -- 仅检查最近5分钟内结束的连续段，避免重复告警

2. Spring Boot定时任务与邮件实现 #

首先添加邮件依赖（pom.xml）：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-mail</artifactId>
</dependency>

配置邮件参数（application.yml）：

spring:
  mail:
    host: smtp.example.com
    port: 587
    username: your-alert-email@example.com
    password: your-email-auth-code
    properties:
      mail:
        smtp:
          auth: true
          starttls:
            enable: true

编写定时任务与邮件发送逻辑：

import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@Component
public class SensorAlertTask {

    @Resource
    private DataSource dataSource;

    @Resource
    private JavaMailSender mailSender;

    // 每5分钟执行一次定时检查
    @Scheduled(cron = "0 */5 * * * ?")
    public void checkContinuousData() {
        String checkSql = """
            WITH consecutive_groups AS (
                SELECT
                    sensor_value,
                    create_time,
                    ROW_NUMBER() OVER (ORDER BY create_time) - 
                    ROW_NUMBER() OVER (PARTITION BY sensor_value ORDER BY create_time) AS group_id
                FROM sensor_data
                WHERE create_time >= NOW() - INTERVAL '1 hour'
            ),
            group_stats AS (
                SELECT
                    sensor_value,
                    MIN(create_time) AS start_time,
                    MAX(create_time) AS end_time,
                    EXTRACT(EPOCH FROM (MAX(create_time) - MIN(create_time))) AS duration_seconds
                FROM consecutive_groups
                GROUP BY sensor_value, group_id
            )
            SELECT sensor_value, start_time, end_time
            FROM group_stats
            WHERE duration_seconds >= 3600
            AND end_time >= NOW() - INTERVAL '5 minutes'
        """;

        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(checkSql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                int value = rs.getInt("sensor_value");
                String startTime = rs.getTimestamp("start_time").toString();
                String endTime = rs.getTimestamp("end_time").toString();

                // 发送告警邮件
                sendAlertEmail(value, startTime, endTime);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void sendAlertEmail(int sensorValue, String startTime, String endTime) {
        SimpleMailMessage message = new SimpleMailMessage();
        message.setFrom("your-alert-email@example.com");
        message.setTo("alert-receiver@example.com");
        message.setSubject("传感器连续值告警");
        message.setText(String.format(
            "传感器连续1小时插入相同值：%d\n开始时间：%s\n结束时间：%s",
            sensorValue, startTime, endTime
        ));
        mailSender.send(message);
    }
}

3. 优化建议 #

• 新增告警日志表：创建sensor_alert_log表记录已发送的告警信息，查询时先判断该值是否在最近1小时内已告警，避免重复发送。
• 调整定时频率：如果对实时性要求高，可将定时任务改为每1分钟执行一次，但需注意控制数据库查询压力。
• 异步发送邮件：给sendAlertEmail方法添加@Async注解，避免定时任务因邮件发送阻塞。

内容的提问来源于stack exchange，提问作者Emine