在不同环境中，每个Postgres数据库服务器的优化读写性能都不同。即使我们决定增加启动A Postgres服务器的 shared_buffer 参数， 这样做对于启动B也是不明智的 。
另外，我们需要对Postgres规划器如何决定如何执行特定查询的理解，以便编写高效的SQL来提高读写性能。
在本文中，我们正在研究在不同情况下提高Postgres服务器读写性能的基本方法。这篇文章中列出的规则是通用的建议，您可以根据自己的需要将它们应用于Postgres服务器。
在查看如何提高Postgres服务器的读写性能之前，我们将讨论有关PostgreSQL的数据库 和 查询执行组件 的 一些 事实。
大纲
数据库事实和查询执行组件
为了优化我们的PostgreSQL服务器，我们应该知道一些有关PostgreSQL查询计划器的事实和细节。
事实1：少写多读
“均衡的内存缓存使您的客户满意”
让我们看一个假想的例子：Ticket Air，票务平台使用PostgreSQL保存客户的数据。这家虚构的公司依靠 Elasticsearch 提供的span_query ，和` 为他们的客户构建更快的搜索解决方案，以帮助他们寻找飞往特拉维夫，布达佩斯等目的地的最便宜航班。term_query
最近在ticketing.il上注册的一位顾客正在寻找飞往特拉维夫的最便宜航班。在这种情况下，我们不应期望应用程序代表客户执行WRITE操作。
该Web应用程序接受客户的查询，并通过价格列在排期表中搜索价格合理的价格。几分钟后，以下结果将显示给查询该结果的客户。
另一组客户请求类似的查询。如果PostgreSQL缓存有足够的内存来存储以前的结果，则直接从缓存中获取它，而无需访问磁盘。
事实2：数据库配置不固定
航空业中PostgreSQL服务器的配置设置与社交媒体站点的配置设置不同。使用相同的参数（例如 wal_buffer 和和） wal_writer_delay 来配置数据库服务器。但是，根据Web服务或Web应用程序的核心功能，分配给这些参数以优化WRITE / READ性能的值会有所不同。
查询执行组件
- 解析器： 它检查或检查SQL字符串的语法。
- 重写器：在某些情况下，我们更喜欢针对视图进行查询，而不是直接针对表进行查询。Rewriter组件的功能是对其进行修改以优化查询。
- 计划者： 这是最重要的组成部分之一。它生成执行特定查询的计划。
- 执行程序： 它与计划程序组件一起执行计划程序生成的计划。
计划者分析执行计划
计划者负责提出执行查询的计划。计划者根据查询的类型决定执行计划。知道计划者如何为特定查询决定计划，有助于编写有效的查询。
假设国家雇员数据库有两个表。该 employee 表存储了在以色列私营和上市公司工作的所有雇员的姓名，而该 department 表存储了雇员表中每个雇员所在的部门列表。
员工表：
CREATE TABLE emp (
emp_id serial PRIMARY KEY,
first_name varchar(50),
last_name varchar(50)
);
first_name | last_name | emp_id
------------+-----------+--------
Ben | Cohen | 1
Eitan | Lahav | 2
Dror | Hevroni | 3
Shira | Eilat | 4
部门表：
CREATE TABLE department (
id serial PRIMARY KEY,
dpt_name varchar(50),
dpt_location varchar(50),
CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES emp (emp_id)
);
id | dpt_name | dpt_location
----+-------------+--------------
1 | accounting | chicago
2 | engineering | tel-aviv
3 | HR | eilat
4 | research | eilat
现在，让我们找出计划者将使用EXPLAIN 命令或 EXPLAIN ANALYZE 命令执行以下查询的决定 。
注意：除了EXPLAIN 显示执行计划外，该 命令EXPLAIN ANALYZE 还会显示由Postgres计划程序生成的执行计划，而 实际会执行查询。
访问单个表
第一个例子
EXPLAIN SELECT first_name from emp WHERE last_name = "Eilat";
EXPLAIN ANALYSE SELECT first_name FROM emp WHERE last_name = 'Eilat';
该查询选择 first name 的每一位员工，其的 last_name 是 “Eilat” 。
EXPLAIN 命令结果 ：
Seq Scan on emp (cost=0.00..13.75 rows=2 width=118)
Filter: ((last_name)::text = 'Eilat'::text)
EXPLAIN ANALYSE 命令结果 ：
Seq Scan on emp (cost=0.00..13.75 rows=2 width=118) (actual time=0.019..0.020 rows=1 loops=1)
Filter: ((last_name)::text = 'Eilat'::text)
Rows Removed by Filter: 3
Planning Time: 33.502 ms
Execution Time: 0.053 ms
EXPLAIN 和 EXPLAIN ANALYSE 命令的结果都 表明，在第一个示例中，Postgres规划器emp 使用顺序扫描方法扫描 表。
第二个例子
现在，让我们再来看一下Postgres规划器如何为下面的第二条语句生成执行计划：
EXPLAIN ANALYSE SELECT * FROM emp WHERE emp_id = 4;
EXPLAIN SELECT * FROM emp WHERE emp_id = 4;
EXPLAIN ANALYZE 命令结果 ：
Index Scan using emp_pkey on emp (cost=0.15..8.17 rows=1 width=240) (actual time=0.867..0.870 rows=1 loops=1)
Index Cond: (emp_id = 4)
Planning Time: 89.925 ms
Execution Time: 12.548 ms
EXPlAIN 命令结果 ：
Index Scan using emp_pkey on emp (cost=0.15..8.17 rows=1 width=240)
Index Cond: (emp_id = 4)
在这种情况下，Postgres计划者使用索引扫描为该语句生成一个执行计划。
访问两个表
现在，让我们找出从两个表访问数据时Postgres计划程序将使用哪种方法：
假设我们发出了与下面的查询类似的查询，以使用EXPLAIN and EXPLAIN ANALYZE 命令查找其部门位于埃拉特的员工 ：
EXPLAIN SELECT first_name FROM emp, department WHERE emp.emp_id = department.id AND department.dpt_location = 'eilat';
EXPLAIN ANALYZE 命令结果 ：
Hash Join (cost=13.78..27.57 rows=2 width=118) (actual time=0.051..0.054 rows=2 loops=1)
Hash Cond: (emp.emp_id = department.id)
-> Seq Scan on emp (cost=0.00..13.00 rows=300 width=122) (actual time=0.016..0.017 rows=4 loops=1)
-> Hash (cost=13.75..13.75 rows=2 width=4) (actual time=0.021..0.021 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 5kB
-> Seq Scan on department (cost=0.00..13.75 rows=2 width=4) (actual time=0.011..0.014 rows=2 loops=1)
Filter: ((dpt_location)::text = 'eilat'::text)
Rows Removed by Filter: 2
Planning Time: 0.273 ms
Execution Time: 0.106 ms
EXPLAIN 命令结果 ：
Hash Join (cost=13.78..27.57 rows=2 width=118)
Hash Cond: (emp.emp_id = department.id)
-> Seq Scan on emp (cost=0.00..13.00 rows=300 width=122)
-> Hash (cost=13.75..13.75 rows=2 width=4)
-> Seq Scan on department (cost=0.00..13.75 rows=2 width=4)
Filter: ((dpt_location)::text = 'eilat'::text)
从这两个结果中，Postgres计划者使用 哈希联接策略 从emp 表和 department 表中检索信息 。另外，计划者使用顺序扫描方法扫描了两个表。
除了哈希联接策略之外，还有其他联接方法，例如 计划者在访问两个表时使用的合并联接 和 嵌套循环。
基于Postgres规划器如何为查询生成执行计划，我们可以依靠这一知识来编写有效的查询以及优化读取性能。
速度优化“规则”
这些规则将指导您进行PostgreSQL服务器优化。
规则1：如果可能，请始终选择仅索引扫描而不是索引扫描
我们如何知道Postgres规划器是否要执行 索引扫描？从前面描述计划者如何为每个查询生成执行计划的示例中，我们可以轻松判断出计划者是否仅使用Index scan扫描表 。
每当我们尝试在索引列上进行搜索时 ，计划者都会使用索引扫描而不是顺序扫描或索引扫描。
为什么索引仅扫描比索引扫描有效？
仅索引扫描的目的 是从索引中获取所有数据，而根本不访问堆。但是， 索引扫描从堆 和 Index两者中获取数据 。
从索引中获取数据仅意味着读取较少的数据。显然，读取较少的数据比读取更多的数据要快。
规则2：如果有多余的列，则减少表中的列数
由Postgres计划程序生成的扫描类型中，有一种称为顺序扫描。顺序扫描通过扫描磁盘上存储的整个表/关系来工作。
为了提供实际证据，让我们假设有两个表/关系： emp 和 users 位于数据库中 demo 。
emp表有t列，即 name 和 location ，而用户的表有四列，即 first_name ， middle_name ， last_name ，和 location 。
如果我们在 SELECT * FROM 两个表中都运行查询语句 ，则返回以下结果。
表emp的查询计划：
Planning Time: 0.284 ms
Execution Time: 0.042 ms
(3 rows)
表用户查询计划：
Planning Time: 0.331 ms
Execution Time: 0.046 ms
(3 rows)
因此，似乎emp表的执行时间比用户表的执行时间短。在这种情况下，执行时间是指服务器使用Postgres计划程序使用生成的查询计划运行查询所花费的时间。
在现实世界中，在一个关系中我们有五个以上的列或多余的列，并且用户向特定表发出并发请求。您认为会发生什么？即时读取性能。
我们如何避免呢？
解决方案：通过减少表中的列数。
规则3：通过选择合适的分片策略明智地分片
分片是DB Reliability工程师用来防止数据库服务器由于客户端的大量请求而崩溃的最优选解决方案。为用户使用而构建产品或服务的初创企业通常会利用一些分片策略来扩展其服务。
但是，如果我们为数据库基础结构创建了许多分片而未使用适当的分片策略，则读写性能可能会受到影响。
规则4：使用SSD磁盘存储关键数据文件
SSD磁盘的访问速度为35到100微秒，这是100倍的速度。这意味着程序或服务可以以更快的速度非常快速地访问大数据。因此，建议将关键任务数据文件放置在SSD磁盘上，以提高读取和写入性能。
规则5：避免在表上使用许多索引
假设我们有一个包含六列的表。在这六列中，已经索引了五列。如果用户更新记录，则所有受影响的索引都需要反映更新。因此，它影响 / 操作的性能。 INSERTUPDATE
规则6：足够的内存用于缓存
如果有足够的内存用于Postgres，则从缓存而不是从磁盘读取经常访问的数据。即使将这些数据文件放置在SSD磁盘上，也建议将 shared_buffer 参数的值增加到比默认值大的值，以通过缓存更多数据来提高READ性能。 更多的内存缓存，更多的数据被缓存。
注意：在密集读写环境中，建议增加shared_buffer的值，但不要大于40％，以便为操作系统留出足够的空间。因此，如果您的数据库服务器具有3GB的RAM，则可以将该值设置为30％或更小。此外，如果增加了shared_buffer 参数的值，则 需要增加max_wal_size 参数的值， 以允许在将数据写入数据文件之前有更长的保留更改后的数据的时间。
但是，增加shared_buffer参数和max_wal_size 参数的值以提高读写性能并不总是正确的 。
假设在一个环境中，其中postgresql数据库用于支持工作负载，例如为订阅要在不久的将来发布的产品的用户提供订阅服务。
在这种情况下，我们希望应用程序连续写入数据库而不会发出许多读取请求。与前面的示例将值增加到小于40％的示例不同，我们可以决定将的值减小 shared_buffer 到至少25％，而是将的值 max_wal_size 增加到更高的值。
为什么？因为我们不希望大多数订阅我们服务的用户向数据库服务器发出读取请求。有些人可能以后决定更改其电子邮件地址。因此，我们增加了max_wal_size的值以防止频繁写入数据。
尽管这些步骤是基本步骤，但是当您打算使用数据库基础结构来支持读写密集型工作负载时，这是非常必要的。如果您无法正确使用基础知识，则很难使用检查点，复制和其他高级技术来增强读写性能。
为什么？因为检查点，复制和其他辅助方法依赖于这些基本步骤来优化读取和写入性能。例如，没有SSD或内存缓存不足，无论您如何完美地调整检查点，以及是否使用异步同步复制方法来复制数据，Postgres服务器都需要一些时间才能返回到客户端。
作者：Michael Aboagye
文章来源：https://vettabase.com/blog/optimize-read-and-write-performance-in-postgresql/?utm_source=rss&utm_medium=rss&utm_campaign=optimize-read-and-write-performance-in-postgresql
