我想做的一件事情就是搬家存储过程中的一些应用程序逻辑，所以我在这里要求在PostgreSQL（9.0）中使用功能时要做DO和不做（最佳实践），特别是关于性能陷阱。
#1 楼
严格来讲，术语“存储过程”是指Postgres 11中引入的Postgres中的SQL过程。相关：何时使用存储过程/用户定义函数？
也有一些函数，它们的功能几乎相同，但从一开始就存在。
带有
LANGUAGE sql的函数基本上只是在函数包装中带有普通SQL命令的批处理文件。 （因此是原子的，总是在单个事务中运行）接受参数。 SQL函数中的所有语句都是立即计划的，这与在另一个语句之后执行一个语句有细微的差别，并且可能会影响获取锁的顺序。最重要的是，最成熟的语言是PL / pgSQL（
LANGUAGE plpgsql）。它运行良好，并且在过去十年中的每个发行版中都得到了改进，但它最适合用作SQL命令的粘合剂。它不用于繁重的计算（除了使用SQL命令）。PL/ pgSQL函数执行查询，如预准备语句。重用缓存的查询计划可以减少一些计划开销，并使它们比等效的SQL语句快一些，这可能会因环境而异。它也可能有类似此相关问题的副作用：函数两次使用时会出现PL / pgSQL问题（缓存问题？）准备好的报表的缺点-如手册中所述。对于具有不规则数据分布和可变参数的表的查询，当针对给定参数的优化执行计划获得的收益超过重新计划的成本时，使用
EXECUTE的动态SQL可能会表现更好。Postgres 9.2通用执行计划仍为该会话缓存，但请引用手册：更多处决产生计划
其估算的平均成本（包括计划间接费用）比一般的计划成本估算要昂贵。
我们在大多数情况下都兼顾了两者（减少了一些额外的间接费用） ），而无需（ab）使用
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函数。
评论
@nhahtdh：“自动交易”不是技术术语。在我澄清之后，这只是一种几乎不优雅的表达方式。根本不是自主交易。 “自治”恰好是一个类似的词。
–欧文·布兰德斯特（Erwin Brandstetter）
2015年3月10日在3:01
从这里开始的答案可能是史诗般的PostGreSQL最佳实践手册。
–达沃斯
17年3月3日于13:07
#2 楼
一些操作：尽可能使用SQL作为函数语言，因为PG可以内联语句
正确使用IMMUTABLE / STABLE / VOLATILE，因为PG可以在结果不变时缓存结果或稳定
正确使用STRICT，因为当您不能使用SQL作为函数语言时，如果任何输入为空，PG只会返回null而不是运行函数
考虑PL / V8。在我运行的一些不科学的测试中，它比PL / pgSQL快可以比基于LIMIT的分页更快
请确保您对功能进行单元测试
评论
这是我第一次看到PL / V8比PL / pgSQL更快的说法。您是否有（已发布的）数据来支持这一点？
– a_horse_with_no_name
2013年12月19日在22:21
@a_horse_with_no_name不，我不知道。就像我说的，我做了一些不科学的测试。它们主要是逻辑，而不是数据访问。我会在圣诞节期间尝试进行一些可重复的测试，然后在此处重新发布。
–尼尔·麦圭根（Neil McGuigan）
2013年12月19日22:25
@a_horse_with_no_name这是FizzBuzz plv8 vs plpgsql的一个快速处理的示例：blog.databasepatterns.com/2014/08/plv8-vs-plpgsql.html
–尼尔·麦圭根（Neil McGuigan）
2014年8月20日在18:36
#3 楼
一般来说，将应用程序逻辑移入数据库将意味着它更快-毕竟它将运行在更靠近数据的位置。我相信（但不是100％肯定）SQL语言函数比使用其他语言的用户，因为它们不需要上下文切换。缺点是不允许使用程序逻辑。强化功能）
#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？还是一千万？构建应用和使用UDF的方式将取决于您尝试扩展的方式。如果您要为成千上万的用户编写程序，那么您要做的主要事情就是尽可能减少db的负载。 UDF可以减少移出和移回数据库的数据量，这将有助于减少IO负载。但是，如果它们开始增加CPU负载，则可能是一个问题。一般而言，降低IO负载是第一要务，接下来要确保UDF高效以免使CPU过载。
评论
您是不是要答案不提任何与性能无关的内容？克里斯·特拉弗斯（Chris Travers）关于使用存储过程的优点的博客很多，例如这里：ledgersmbdev.blogspot.de/2012/07/…和这里：ledgersmbdev.blogspot.de/2012/07/…只是浏览了他的博客，关于这个话题有很多有趣的文章。