主页 / dba / 问题 / 8119
Accepted
Derek Downey
Asked: 2011-11-19 10:47:52 +0800 CST

功能性能

  • 772

来自 MySQL 背景，存储过程的性能（较早的文章）和可用性是有问题的，我正在评估 PostgreSQL 是否适合我公司的新产品。

我想做的一件事是将一些应用程序逻辑移动到存储过程中，所以我在这里询问有关在 PostgreSQL (9.0) 中使用函数的 DO 和 DON'T （最佳实践），特别是关于性能缺陷的问题。

postgresql best-practices

4 个回答

  1. Best Answer

    严格来说，术语“存储过程”是指 Postgres 中的SQL 过程，由 Postgres 11 引入。相关：

    还有一些功能，几乎但不完全相同，而且从一开始就存在。

    函数基本上只是批处理文件，在函数包装器中带有LANGUAGE sql普通的 SQL 命令（因此是原子的，总是在单个事务中运行）接受参数。SQL 函数中的所有语句都是一次计划的，这与执行一个语句一个又一个语句有细微的不同，并且可能会影响锁定的顺序。

    除此之外，最成熟的语言是PL/pgSQL ( LANGUAGE plpgsql)。它运行良好，并且在过去十年的每个版本中都得到了改进，但它最适合作为 SQL 命令的粘合剂。它不适用于繁重的计算（SQL 命令除外）。

    PL/pgSQL 函数像预处理语句一样执行查询。重用缓存的查询计划会减少一些计划开销，并使它们比等效的 SQL 语句快一点，这可能会根据情况产生明显的影响。它也可能有像这个相关问题一样的副作用：

    这带来了准备好的语句的优点和缺点——正如手册中所讨论的那样。对于具有不规则数据分布和变化参数的表的查询，当针对给定参数的优化执行计划的收益超过重新计划的成本时，动态 SQL 可能会执行得更好。EXECUTE

    由于 Postgres 9.2 通用执行计划仍为会话缓存，但引用手册：

    对于没有参数的准备好的语句，这会立即发生；否则，只有在五次或更多执行产生的计划的估计平均成本（包括计划开销）比通用计划成本估计更昂贵时才会发生。

    大多数时候，我们在没有 (ab)using的情况下获得了两全其美（减少了一些额外的开销） EXECUTE。PostgreSQL Wiki 的PostgreSQL 9.2 中的新增功能中的详细信息。

    Postgres 12 引入了额外的服务器变量plan_cache_mode来强制通用或自定义计划。对于特殊情况，请谨慎使用。

    您可以通过服务器端功能赢得大奖，这些功能可以防止从您的应用程序到数据库服务器的额外往返。让服务器一次尽可能多地执行，并且只返回一个明确定义的结果。

    避免嵌套复杂函数，尤其是表函数（RETURNING SETOF record或TABLE (...)）。函数是对查询计划器构成优化障碍的黑盒子。它们是单独优化的，而不是在外部查询的上下文中，这使得计划更简单，但可能导致计划不够完美。此外，无法可靠地预测函数的成本和结果大小。

    此规则的例外是简单的 SQL 函数 ( )，如果满足某些先决条件LANGUAGE sql，它可以“内联”。在Neil Conway的这个演示文稿中阅读有关查询计划器如何工作的更多信息（高级内容）。

    在 PostgreSQL 中，函数总是在单个事务中自动运行。全部成功或一无所获。如果发生异常，一切都会回滚。但是有错误处理...

    这也是为什么函数不完全是“存储过程”的原因（即使该术语有时会被误导）。某些命令，如VACUUM，CREATE INDEX CONCURRENTLY或CREATE DATABASE不能在事务块内运行，因此它们不允许在函数中使用。（从 Postgres 11 开始，SQL 过程中都没有。以后可能会添加。）

    多年来，我编写了数千个 plpgsql 函数。

    • 80

  2. 一些DO：

    • 尽可能使用 SQL 作为函数语言，因为 PG 可以内联语句
    • 正确使用 IMMUTABLE / STABLE / VOLATILE，因为 PG 可以缓存不可变或稳定的结果
    • 正确使用 STRICT，因为如果任何输入为空，PG 可以只返回空，而不是运行函数
    • 当您不能使用 SQL 作为函数语言时，请考虑使用 PL/V8。在我运行的一些不科学的测试中，它比 PL/pgSQL 更快
    • 使用 LISTEN / NOTIFY 处理可能在事务外发生的运行时间较长的进程
    • 考虑使用函数来实现分页，因为基于键的分页可以比基于 LIMIT 的分页更快
    • 确保对函数进行单元测试
    • 18

  3. 一般来说，将应用程序逻辑移动到数据库中意味着它会更快——毕竟它会更靠近数据运行。

    我相信（但不是 100% 肯定）SQL 语言函数比使用任何其他语言的函数更快，因为它们不需要上下文切换。缺点是不允许任何程序逻辑。

    PL/pgSQL是内置语言中最成熟和功能最完整的——但为了性能，可以使用C（尽管它只会使计算密集型函数受益）

    • 11

  4. 您可以使用 postgresql 中的用户定义函数 (UDF) 来做一些非常有趣的事情。例如，您可以使用数十种可能的语言。内置的 pl/sql 和 pl/pgsql 既强大又可靠，并使用沙盒方法来防止用户做任何太危险的事情。用 C 编写的 UDF 为您提供了终极的功能和性能，因为它们与数据库本身在相同的上下文中运行。然而，这就像在玩火，因为即使是小错误也可能导致大问题，导致后端崩溃或数据损坏。自定义 pl 语言，如 pl/R、pl/ruby、pl/perl 等，为您提供了使用相同语言编写数据库和应用程序层的能力。这很方便，因为这意味着您不必教 perl 程序员 java 或 pl/pgsql 等来编写 UDF。

    最后，还有pl/proxy语言。这种 UDF 语言允许您在数十个或更多后端 postgresql 服务器上运行您的应用程序，以实现扩展目的。它是由 Skype 的好人开发的，基本上允许穷人的水平缩放解决方案。写起来也出奇的容易。

    现在，关于性能问题。这是一个灰色地带。您是否正在为一个人编写应用程序？还是1000？还是 10,000,000？您构建应用程序和使用 UDF 的方式在很大程度上取决于您尝试扩展的方式。如果您正在为成千上万的用户编写代码，那么您要做的主要事情就是尽可能减少数据库的负载。减少移出和移回数据库的数据量的 UDF 将有助于减少 IO 负载。但是，如果它们开始增加 CPU 负载，那么它们可能会成为问题。一般来说，减少 IO 负载是第一要务，其次是确保 UDF 高效以免 CPU 过载。

    • 10

相关问题