PostgreSQL 应用程序性能提示
Tradução do texto original de Josh Berkus publicado em 28/11/2006 e 29/11/2006.
以下是我给 Sun 团队的一套建议的编辑版，当时他们重新设计了一个采用 MySQL 构建的 C++ 应用程序，并移植到 PostgreSQL，但从未针对性能进行过优化。我认为这些建议可能对社区有帮助，因此在此分享给大家。
针对 PostgreSQL 性能设计的应用程序
编写查询规则
对于所有数据库管理系统 (DBMS)，每轮处理时间都很长。这是查询通过语言语法分析器、驱动程序、网络接口、数据库语法分析器、计划程序、执行器、语法分析器（再次）、返回网络接口、通过驱动程序的数据处理程序并返回应用程序客户端所需的时间。DBMS 处理此循环所花费的时间和 CPU 各不相同，由于多种原因，PostgreSQL 每轮耗费的时间和系统资源较多。
然而，PostgreSQL 的每个事务都具有明显的开销，包括输出日志和必须在每次事务中调整的访问规则。虽然你可能认为自己没有对简单的 SELECT 读命令使用事务，但实际上，PostgreSQL 中的每一条简单命令都是一个事务。在没有显式事务的情况下，该命令本身隐式地就是一个事务。
通过此方式，PostgreSQL 在处理复杂且较长的查询、包含多条命令的事务（可轻松解决并发冲突）时显然仅次于 Oracle。它还支持可滚动和不可滚动的游标。
小贴士 1：禁止使用大量的小查询来代替一个大型查询
在 MySQL 应用程序中，经常会出现一种情况，即在应用程序代码中处理联接，也就是说，查询某个关联记录的 ID，然后手动遍历该 ID 下的子记录。这可能导致在用户界面屏幕中运行数以百计的查询。每次查询需要花费 2 到 6 毫秒，也就是说，如果您执行了大约 1000 个查询，这时将耗费 3 到 5 秒的时间。相比之下，在一个查询中请求获得这些记录只需几百毫秒，这将节省大约 80% 的时间。
小贴士 2：将多个小的 UPDATE、INSERT 或 DELETE 合并为单个命令，或者在无法合并的情况下，将它们放在一个较长的事务中
由于早期的 MySQL 版本中缺少子查询，因此应用程序开发人员以前会采用与中间件中的联接相同的方式来设计其数据修改语言 (DML) 命令。对于 PostgreSQL 而言，这并不是一个好主意。相反，应充分利用子查询和联接，在 UPDATE、INSERT 和 DELETE 命令中尝试使用单个命令进行批量修改。这将缩短周转时间并且减少事务开销。
但是，在某些情况下，无法使用一个查询来修改您想要的所有行，您将使用一系列串行命令。在这种情况下，您需要确保将一系列 DML 命令包含在一个显式事务中（例如，BEGIN；UPDATE；UPDATE；UPDATE；COMMIT；）。这将减少事务开销并将执行时间缩短 50%。
小贴士 3：考虑批量加载数据，而不是串行 INSERT
PostgreSQL 提供了一种称为 COPY 的批量加载机制，它可以从制表符或 CSV 分隔文件或管道中获取输入。如果使用 COPY 来代替数以百计的 INSERT，可以将执行时间缩短 75%。
小贴士 4：DELETE 成本较高
应用程序开发人员通常认为 DELETE 命令几乎没有开销。您只是关闭了一些节点，对吗？错。SGDB 不是文件系统；当您删除一行时，需要更新索引，需要清除释放的空间，这使得删除实际上比插入更昂贵。因此，每次进行任何更改时都习惯删除所有明细行并用新行替换它们的应用程序都在应用程序方面节省了精力，并将其推向了数据库中。在可能的情况下，应该用更细致的按行替换来替换它，例如，仅更新修改的行。
此外，当要清除整个表时，请务必使用 TRUNCATE TABLE 命令，而不是 DELETE FROM TABLE。第一个方式比后一个方式快 100 倍，因为它将整个表作为整体处理，而不是逐行处理。
提示 5：使用 PREPARE/EXECUTE 对查询进行迭代
有时，即便尝试将类似查询的迭代合并到一个较长的命令中，但在应用程序中它也不总是能够进行结构。为此，PREPARE ... EXECUTE 有用；它允许数据库引擎为每次查询迭代跳过语法分析器和计划程序。例如
准备
query_handle = query('SELECT * FROM TABLE WHERE id = ?')(parameter_type = INTEGER)
然后启动迭代
for 1..100 query_handle.execute(i); end
C++ 中用于准备命令的类在 libpqxx 文档中进行了解释。
这将根据迭代次数的大小直接减少执行时间。
提示 6：有效使用连接池
对于 Web 应用程序，您会发现其性能潜力最多可通过使用及适当配置连接池控制 50%。这是因为在数据库中创建和销毁连接需要大量系统时间，并且过多的非活动连接将继续需要 RAM 和系统资源。
可以使用多种工具在 PostgreSQL 中创建连接池。一个开源的第三方工具是 pgPool。但是，对于具有高可用性要求的 C++ 应用程序，最好使用 libpqxx 本机的称为“惰性连接”的伪池技术。我建议联系电子邮件列表以获取有关如何使用它的更多信息。
对于 PostgreSQL，您将希望尽可能多地定义持续连接（或连接对象）在并发连接正常使用峰值中。因此，如果正常的最大使用量（例如，清晨）为 200 个来自代理、用户和组件的并发连接，那么您将希望将此量置于定义值，以便应用程序在低效创建高峰期间不需要等待新连接。
版权声明
Fábio Telles Rodriguez <[email protected]> 原本翻译的文本发表于 http://savepoint.blog.br/dicas-de-performance-em-aplicacoes-com-postgresql/
文本的自由翻译：“PostgreSQL 应用程序性能提示，第 1 部分”，Josh Berkus 于 2006 年 11 月 28 日发布在 http://blogs.ittoolbox.com/database/soup/archives/postgresql-application-performance-tips-part-1-13172 ，“PostgreSQL 应用程序性能提示，第 2 部分”，Josh Berkus 于 2006 年 12 月 1 日发布在 http://blogs.ittoolbox.com/database/soup/archives/postgresql-application-performance-tips-part-2-13194
