我为啥总是用PostgreSQL函数来做所有事情

照片 VB，临近马尔卡斯特，克罗地亚

通常来说，每次我在应用程序中使用命令或查询时，我总是把它们放进一个PostgreSQL函数中！

有时，当我需要细粒度的事务控制时，会用到PostgreSQL 存储过程，例如，但这种情况真的很少见。大多数情况下，我用的还是PostgreSQL的用户定义函数。

用面向对象编程领域的术语来说，它们就是我的数据契约。

PostgreSQL与应用程序实例之间的正式数据交换约定。

特别的是：

• 函数或过程的参数 — 是我的输入契约。
• 函数结果（或缺失） — 是我的输出契约。

函数封装了命令、查询以及表的访问。这些都是私有的内部细节。数据接口是公开的。除非应用程序发生变化，否则它们不会改变。

让我给你举一个实际的例子，以便你更好地理解。

比如说我们有一个装满很多数据的大表格

创建一个名为device_measurements的表 (
    id bigint not null 自增主键,
    device_id int not null 引用设备表中的设备ID,
    时间戳 timestamp not null,
    value numeric not null
);

现在，这张表是在一个遗留系统中由ORM工具创建的标准表格。因此，它有一个自增整数主键（尽管用处不大，不过就是这样了）。

有几项标准索引，比如在device_id和timestamp上的。

现在，这个旧应用程序在进行分析报告时，生成了大量的带有不同参数的完全相同的查询：

    选择 "timestamp", device_id, value  
    从 device_measurements   
    其中 device_id = $1  
    按 id 降序排列

首先，当应用程序运行大量相同查询而参数不同时，这是一个很大的代码异味，极有可能需要重构和重写。

那些参数值可能来自其他查询，我们现在对第一个查询的每个结果都做了一次新的查询。换句话说，我们很可能遇到的是N+1反模式，这使得整个应用程序变得又慢又吃资源。

然而，首先需要非常仔细和周到地分析那些N+1反模式，然后需要对整个部分进行彻底重构，这可能不是每个遗留系统都能负担的。

N+1 至少会让应用程序变慢（甚至会导致崩溃），但仍然可以勉强使用。

所以，第一步是理解我们希望通过那个查询得到什么。你想通过这个查询得到什么？弄清楚这一点，我们就可以提取输入和输出的数据约定。

• 目的是返回最新的设备测量记录，这里指的是最后插入的那一项记录。
• 输入数据合约仅包含设备ID。
• 输出数据合约返回一条记录，包括时间戳、设备ID和值。

这给了我们以下函数：

    创建或替换函数 get_latest_device_measurement(  
        _device_id integer  
    )   
    返回表结构 (  
        "时间戳" timestamp,  
        device_id integer,  
        值 double precision  
    )   
    安全定义者（SECURITY DEFINER）   
    使用 SQL 语言  
    $  
    /* SQL 块 */  
    select   
        "时间戳", device_id, 值   
    from   
        device_measurements  
    where  
        device_id = _device_id  
    /* 按 ID 降序排列 */  
    order by id desc  
    limit 1;  
    $;

• 这个名字清晰地描述了我们函数的意图。我们的函数会输出什么呢？

• 具体来说，输入参数和返回类型定义了我们的数据约定。

我们可以用这样的函数调用替换掉我们在老应用中的查询，现在。

    查询 "时间戳", device_id, value, 在 get_latest_device_measurement(参数1)

这使得我们的查询和数据访问变得既清晰又易于维护。基本上就是——干净代码风格的SQL。

鲍勃叔叔会为此感到骄傲。

现在，从性能方面来说，我们目前还没有做任何事情。但是我们确实让我们的应用变得更加容易维护了。继续往下看。

谁能想到，随着时间的流逝，数据会不断增加呢？

开个玩笑，出于很多不同的原因——这里我就不细说了，因此决定将系统迁移到Timescale DB，并按timestamp字段进行分区。

Timescale分区不允许在未用于分区的字段上创建唯一索引，而我们的情况是这个字段是时间戳。

这就意味着需要移除ID字段的主键——以便正确地构建分区。

其实，它用处不大；它只是为了ORM而存在。

当然，除了我们用来“获取最新测量”的功能之外。删除那个主键索引后，我们的报表分析就无法用了，因为这个功能依赖于主键索引。前面提到的这个功能依赖于主键索引。

幸運的是，這類功能可以通過原子交易進行更改（調整），不需要任何停機時間或重新部署任何應用，這樣就不需要改變應用程序。

    创建或替换函数 get_latest_device_measurement(  
        _device_id integer  
    )   
    返回表结构 (  
        "timestamp" timestamp,  
        device_id integer,  
        value double precision  
    )   
    安全定义  
    语言 sql  
    $  
    select   
        "timestamp", device_id, value   
    from   
        从设备测量表 device_measurements  
    where  
        device_id = _device_id  
    order by "timestamp" desc  
    limit 1 行;  
    $;

现在好了；用对了索引，有几个分区，报告又能用了。

然而，随着时间的推移，数据也在增长。谁能想到会是这样呢？

虽然这个版本使用了索引，它依然需要为每个分区单独进行索引扫描。随着分区数量的增多，报表分析再次变得不可用。显然，我们需要采取稍微不同的策略。

根据我们的问题性质，找到给定设备的最新时间戳记录——下面的索引就合适。

    在设备测量上创建索引 (设备ID, 时间戳 DESC);

现在，我们可以查询给定设备的最新时间戳，然后使用该时间戳来定位整个记录，通过我们现有的时间和设备ID索引。

    创建或替换函数 get_latest_device_measurement(  
        _device_id integer  
    )   
    返回表结构 (  
        "timestamp" timestamp,  
        device_id integer,  
        value 毫秒精度  
    )   
    安全定义  
    语言 sql as  
    $  
    with cte as (  
        select max(timestamp)   
        from device_measurements  
        where 物体_id = _device_id  
    )  
    select   
        m."时间戳", m.device_id, m.value  
    from   
        device_measurements m  
        join cte ON m."时间戳" = cte.max::timestamp  
    where   
        m.device_id = _device_id  
    limit 1;  
    $;

执行计划显示，使用这种做法时，只有一个分区进行索引扫描来定位最新的记录。

这简直是太好了；我们的报告分析工具又可以用了，一切都好了。

再说一次，我们甚至不需要触碰应用程序，更别提重新部署了，完全没有超时。

然而，随着时间的推移，数据却在增长。又来了！谁能料到呢！？厉害吧！

现在，我们有很多分区，而不仅仅是几个。执行计划显示查询只扫描了最后一个分区，但这几乎耗时一秒钟。考虑到它在N+1循环中运行，并且有很多类似的查询，整体效果就是分析变慢了。

现在，如果我们仔细查看执行计划，可以发现查询的计划时间过长，接近一秒钟，而执行时间非常快。

这基本上没告诉我们什么特别的，除了引擎需要更多时间来处理。这可能是因为我们有大量的分区，但也可能不是。不过我们可以试试看。

仅从物理分区（TimescaleDB所说的分块）中查询时，其中最后一个分区（具有最高时间戳的）应包含我们想要查找的最新记录。

现在，我们的函数需要运行一些动态SQL，可能看起来是这样的：

    创建或替换函数 get_latest_device_measurement(  
        _device_id integer  
    )   
    返回表 (  
        "timestamp" timestamp,  
        device_id integer,  
        value double precision  
    )   
    安全定义者（SECURITY DEFINER）  
    语言plpgsql作为  
    $  
    声明变量  
        _table text;  
        _record record;  
    开始执行  
        select chunk_schema || '.' || chunk_name   
        into _table  
        from timescaledb_information.chunks c  
        where   
            hypertable_schema = 'public'   
            and hypertable_name = 'device_measurements'  
        order by range_end desc   
        limit 1;  

        执行格式化(  
            $sql$  
            with cte as (  
                select max(timestamp) from %1$s  
                where device_id = $1  
            )  
            select   
                m.timestamp, m.device_id, m.value  
            from %1$s m  
            join cte ON m.timestamp = cte.max  
            where m.device_id = $1  
            limit 1;  
            $sql$, _table  
        ) 使用参数 _device_id 将结果集存储到 _record;  

        返回查询语句  
        select _record.timestamp, _record.device_id, _record.value;  
    结束;  
    $;

这真是一个巨大的成功；最新的测量结果再次迅速返回，报告分析也恢复了可用性。

然而，随着时间的推移，数据却不断增加。谁能想到呢！

嘿，我只是开个玩笑。当然，数据会增长，但我们有选择。比如说：

• 我可以在这个表上创建一个触发器，当表中的数据被更新时，该触发器会更新另一个表中的最新时间戳和每个设备的最新值。这个只包含最新值的冗余数据表应该很小且运行速度很快。然后，我们可以调整我们的函数，使其返回那个表中的值。
• 我可以创建一个包含每个设备最新值的物化视图。在Timescale DB中，有一个利用所谓的连续聚合的选项，它会自动从超表中刷新物化视图。然后，我们可以调整我们的函数，使其返回那个物化视图中的值。

关键是，我们可以做许多不同的事情，而且不需要一次改变或重新部署应用。

一刀不拉，小宝贝。

当各种软件架构师谈论封装和抽象时，当他们不提数据库里的函数和过程时，这其实是个大失误。

他们通常也不会这样。

这种方法在安全方面还有大好处。

安全很重要，这可是人人都挂在嘴边的话。说到数据库安全，数据库安全往往被忽视，因为开发者更愿意用ORM工具而忽视安全。

采用这种方法，我们可以轻松地实现一个叫做最小权限原则（Least Privilege）或简称最小权限安全的原则。

这意味着每个账户必须拥有执行核心功能所需的最少权限。在军队中，他们将这种方法称为需要知道的原则。基本上，你只能知道完成任务所需的信息。因为如果你被敌人俘虏，你无法泄露你不知道的信息。军队非常重视安全，我们也要这样做。

无论如何，使用 PostgreSQL，我们可以轻松地配置数据库用户，使他们只能执行这些函数。他们无法访问这些表，因为这些表是私密的。即使敌人窃取了你的应用程序数据库凭证，他们也无法删除你的数据。或者至少，他们只能做应用程序所能做的那些事情。

我们用PostgreSQL试试看会怎么样。

首先，让我们来做一件事，创建一个只能用来登录的应用用户。

    创建角色 app_user 并设置  
        允许登录  
        非超级用户  
        禁止创建数据库  
        禁止创建角色  
        不继承角色权限  
        禁止数据复制  
        连接数限制为 -1  
        密码为 '******';

请注意，你可以使用“ALTER ROLE”命令设置正确且保密的密码哦。

此用户没有任何权限，只能进行登录。目前来看一切还好。

现在，我喜欢这样来实现：将我的函数/过程层放在一个独立的架构或独立的模式层里。

    创建模式 measurements 如果不存在，则创建;  

    创建或替换函数 measurements.get_latest(  
        _device_id 整数  
    )   
    返回表 (  
        "timestamp" 时间戳,  
        device_id 整数,  
        value float  
    )   
    安全定义者 SECURITY DEFINER  
    语言 sql 作为  
    $  
    with cte as (  
        select max(timestamp)   
        from device_measurements  
        where thing_id = _device_id  
    )  
    select   
        m.timestamp, m.device_id, m.value  
    from   
        device_measurements m  
        join cte ON m.timestamp = cte.max::timestamp  
    where   
        m.device_id = _device_id  
    limit 1;  
    $;

现在，我们可以按照相似性将用户定义的功能和过程（功能和程序）分组。所有测量功能都在测量模式(schema)中。我们还可以调整函数名称以删除冗余的词汇。

获取最新的测量数据

这么多干净的代码 🥰

如果我不再需要某些测量功能，我可以使用一条命令把它们全部移除。

    create schema if exists measurements cascade;

这也将移除该模式下所有相关对象（比如我们的测量函数）。

同样，我同样可以为应用用户授予这个模式中的所有函数的执行权。

将测量模式下的所有函数的执行权限授予应用程序用户。

这仅限现有的功能。

我们也可以修改模式的默认权限，使所有现有函数和将来的新函数自动获得特定角色的执行权限。

在 measurements 模式下修改默认权限  
授予 app_user, 所有函数上的执行权限;

这全看我们对最小权限原则有多严格了。

用户没有任何访问表或其他内容的权限，因为权限是最小的。这就是为什么每个函数或过程都必须使用安全定义者安全标签来创建。这个标签指示函数执行应以创建该函数的角色上下文执行，该角色通常是超级用户或管理员的角色。

PostgreSQL 自定义函数和存储过程可以帮助我们优化：

• 可读性；
• 维护性；
• 可用性；
• 最后但同样重要的 — 安全。

这是你的PostgreSQL的干净代码，你应该开始用它们了。

虽然我对 postgres 函数挺有好感，而且这个例子也很不错，但是你可以在不改动应用程序代码的情况下更改它们这并不是一个很大的优势。

SQL 的管理需要与应用程序同步，遵循相同的发布流程和周期。

我的回话是：

一般来说，我们通常使用的是Flyway迁移机制或其他类似的工具。在这种情况下，所有功能将被放入可重复迁移文件中——每个功能一个文件。

当函数变更时，并不是通过直接在服务器上修改，而是修改那个可重复的迁移文件，并在本地测试。

那个文件随后被提交并推送到需要提交的地方。在这种情况下，我们只会部署迁移代码，这将只会更新（或修改）被更改的功能。

这样我们就解决了那个问题。