优化PostgreSQL进行快速测试

首先，总是使用最新版本的PostgreSQL。性能改进总是会到来的，所以如果你调整旧版本，你可能浪费你的时间。例如， PostgreSQL 9.2 significantly improves the speed of TRUNCATE，当然添加仅索引扫描。即使是小的释放应该总是遵循;见 version policy。

不要

Do NOT put a tablespace on a RAMdisk or other non-durable storage。

如果你失去一个表空间，整个数据库可能会被损坏，很难使用没有重要的工作。与只使用UNLOGGED表和具有大量缓存的RAM相比，这有很少的优势。

如果你真的想要一个基于ramdisk的系统，initdb通过在ramdisk上引入一个新的PostgreSQL实例在ramdisk上创建一个新的集群，所以你有一个完全一次性的PostgreSQL实例。

PostgreSQL服务器配置

测试时，您可以配置您的服务器non-durable but faster operation。

这是PostgreSQL中fsync=off设置的唯一可接受的用法之一。这个设置几乎告诉PostgreSQL不要打扰有序的写入或任何其他讨厌的数据完整性保护和崩溃安全的东西，如果你失去电源或操作系统崩溃，它允许完全删除您的数据。

不用说，你不应该在生产中启用fsync = off，除非你使用Pg作为临时数据库来存储可以从别处重新生成的数据。如果并且只有如果你打算关闭fsync也可以关闭full_page_writes，因为它不再有任何好处。请注意，fsync = off和full_page_writes在集群级别应用，因此它们会影响PostgreSQL实例中的所有数据库。

对于生产使用，您可以使用synchronous_commit = off并设置commit_delay，因为您将获得许多与fsync = off相同的优点，而不会有巨大的数据损坏风险。如果你启用异步提交，你确实有一个小窗口的最近的数据丢失 – 但就是这样。

如果您可以选择稍微更改DDL，您还可以使用Pg 9.1中的UNLOGGED表完全避免WAL日志记录，并以服务器崩溃时表被擦除为代价获得真正的速度提升。没有配置选项使所有表不记录，必须在CREATE TABLE期间设置。除了有利于测试这是很方便，如果你有一个数据库中包含生成或不重要的数据表，否则包含你需要安全的东西。

检查您的日志，看看是否收到太多检查点的警告。如果你是，你应该增加你的checkpoint_segments.你可能还想调整你的checkpoint_completion_target平滑写出。

调整shared_buffers以适应您的工作负载。这取决于操作系统，取决于你的机器上发生了什么，需要一些试验和错误。默认值非常保守。如果您在PostgreSQL 9.2及以下版本中增加shared_buffers，您可能需要增加操作系统的最大共享内存限制; 9.3及以上版本改变了他们如何使用共享内存来避免这种情况。

如果你使用只是几个连接，做很多工作，增加work_mem给他们更多的RAM，以排序等。小心太高的work_mem设置可能导致内存不足的问题，因为它是per-不是每个连接排序，因此一个查询可以有许多嵌套排序。你只需要增加work_mem，如果你可以看到排序溢出到磁盘在EXPLAIN或用log_temp_files setting(推荐)记录，但更高的值也可能让Pg选择更聪明的计划。

正如另一张海报所说，如果可能的话，将xlog和主表/索引放在单独的HDD上是明智的。单独的分区是毫无意义的，你真的想要单独的驱动器。如果你使用fsync = off运行，这种分离的好处要少得多，如果你使用UNLOGGED表，几乎没有。

最后，调整您的查询。确保您的random_page_cost和seq_page_cost反映您的系统性能，确保您的effective_cache_size是正确的等。使用EXPLAIN(BUFFERS，ANALYZE)来检查单个查询计划，并打开auto_explain模块以报告所有慢查询。您通常可以通过创建适当的索引或调整成本参数来大幅提高查询性能。

AFAIK没有办法将整个数据库或集群设置为UNLOGGED。这是有趣的是能够这样做。考虑询问PostgreSQL邮件列表。

主机操作系统调整

还有一些调整，你可以在操作系统级别做。你可能想做的主要事情是说服操作系统不要积极地刷新写入磁盘，因为你真的不在乎/如果他们使它到磁盘。

在Linux中，您可以使用virtual memory subsystem的dirty_ *设置来控制此设置，例如dirty_writeback_centisecs。

调整回写设置太松散的唯一问题是，一些其他程序的刷新可能会导致所有PostgreSQL的累积缓冲区也被刷新，导致大的停顿，而一切阻塞写。您可以通过在不同的文件系统上运行PostgreSQL来缓解这种情况，但是一些刷新可能是设备级或整个主机级，而不是文件系统级，因此您不能依赖它。

这个调整真的需要玩弄设置，看看什么是最适合你的工作负载。

在较新的内核上，您可能希望确保vm.zone_reclaim_mode设置为零，因为它可能会导致严重的性能问题与NUMA系统(大多数系统这些天)，由于与如何管理shared_buffers的PostgreSQL交互。

查询和工作负载调优

这些是需要代码更改的事情;他们可能不适合你。有些是你可以申请的东西。

如果您不是将工作分批为更大的事务，请开始。许多小交易是昂贵的，所以你应该批量的东西，只要有可能和实用的做到这一点。如果你使用异步提交，这不那么重要，但仍然强烈推荐。

尽可能使用临时表。它们不会生成WAL流量，因此插入和更新的速度更快。有时候，值得把一堆数据写入一个临时表，然后操作它，然后执行INSERT INTO … SELECT …将其复制到最终表。注意临时表是每个会话;如果您的会话结束或您失去了连接，那么临时表将消失，没有其他连接可以看到会话的临时表的内容。

如果你使用PostgreSQL 9.1或更高版本，你可以使用UNLOGGED表来获取你可以承受的数据，比如会话状态。这些在不同会话之间可见，并在连接之间保留。如果服务器不干净地关闭，它们将被截断，因此它们不能用于无法重新创建的任何内容，但它们对于缓存，物化视图，状态表等非常有用。

一般来说，不要从blah。使用TRUNCATE TABLE blah;代替;当你转储表中的所有行时，它会快得多。如果可以，在一个TRUNCATE调用中截断多个表。有一个警告，如果你做了很多小表的TRUNCATES一遍又一遍;参见：Postgresql Truncation speed

如果你没有外键索引，那么涉及这些外键引用的主键的DELETE将会非常慢。如果您希望从引用的表中删除DELETE，请确保创建此类索引。 TRUNCATE不需要索引。

不要创建不需要的索引。每个索引都有维护成本。尝试使用最小的索引集，并让位图索引扫描结合它们，而不是维护太多庞大，昂贵的多列索引。在需要索引的情况下，请尝试先填充表，然后在末尾创建索引。

硬件

有足够的RAM来保存整个数据库是一个巨大的胜利，如果你可以管理它。

如果你没有足够的RAM，更快的存储，你可以得到更好。即使是便宜的SSD，在旋转生锈上也有巨大的区别。不要相信便宜的SSD生产虽然，他们往往不是崩溃安全，可能吃你的数据。

学习

格雷格·史密斯的书，PostgreSQL 9.0 High Performance仍然相关，尽管提到一个较旧的版本。它应该是一个有用的参考。

加入PostgreSQL一般邮件列表，并按照它。

读：

> Tuning your PostgreSQL server – PostgreSQL wiki
> Number of database connections – PostgreSQL wiki