我想做的一件事情就是搬家存储过程中的一些应用程序逻辑,所以我在这里要求在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/…只是浏览了他的博客,关于这个话题有很多有趣的文章。