PostgreSQL架构
QQ群
视频号
微信
微信公众号
知识星球
- 270 次浏览
【PostgreSQL 】PostgreSQL 12的8大改进
PostgreSQL 12专注于性能和优化。此版本的发布并未考虑到全新的闪亮功能；相反，它是对现有PostgreSQL功能的微调和精心设计的实现。因为PostgreSQL每年都会发布新版本，所以并不是每一个新功能都完全具备。在发布了几个版本之后，当该功能有机会从其最初的实现中发展出来时，其性能将得到改善，边缘情况将得到支持，缺失的功能将得到实现。
这是PostgreSQL 12中发现的八个最重要的改进。
1.分区性能
分区并不是一项新功能，它已经存在了好几年了，但是分区开销却降低了性能。 PostgreSQL 11引入了一些分区性能改进，而PostgreSQL 12提供了完善的实现。对于从具有数千个分区的其他数据库迁移来的用户，PostgreSQL 12现在通过提供可同时有效处理数千个分区的功能而带来性能优势。分区性能增强可以提高查询性能，尤其是INSERT和COPY语句的性能。此外，用户现在可以更改分区表而不会阻止查询，并可以使用外键引用分区表。
2. B树增强
B-Tree功能是近年来对PostgreSQL添加的最复杂的功能之一。使用B树的好处是减少了访问的磁盘块的数量。考虑到B-Tree技术可以追溯到1970年代，很难对已经存在数十年的可靠功能进行改进。但是PostgreSQL 12团队致力于提供可自动启用的重大性能改进，旨在避免某些极端情况和B树代码中曾经存在的“病理行为”。现在，通过更有效地利用空间，多列索引大小最多可减少40％，从而节省了磁盘空间。具有重复项（非唯一B树索引）的索引的性能得以提高，并且从索引中删除元组（行）的真空运行效率更高。此外，索引更新期间的锁定要求有所降低。
3.多列最有价值（MCV）统计信息
此更新已经进行了几年的开发，旨在解决多年来引起投诉的问题：查询中相关列的边缘情况。以俄亥俄州辛辛那提为例-您有一个标记为“城市”的字段，另一字段称为“州”，其中“辛辛那提”位于一列，而俄亥俄州则在另一列。俄亥俄州的辛辛那提市将相当普遍，但亚利桑那州的辛辛那提市却很少见。在此功能之前，PostgreSQL仅记录了多个列的单个相关值。从本质上讲，它将俄亥俄州的辛辛那提和亚利桑那州的辛辛那提视为同一件事。现在，您可以比较多个列并关联组合以优化查询索引。
4.公用表表达式（CTE）
正确实现的另一个过期功能是通用表表达式（带有查询内联）。公用表表达式充当优化障碍，公用表表达式中的查询首先执行，然后PostgreSQL将在查询中执行之后的任何操作。一些用户采用通用表表达式来提高SQL的可读性和调试，而不是优化SQL的执行。这些用户不可避免地会遇到优化行为。 PostgreSQL 12使用关键字“ MATERIALIZE”实现了一项新功能，该功能允许用户打开优化围栏。如果您不使用MATERIALIZE，则不会获得优化范围，并且可能会看到更快的查询。
5.准备好的计划控制
一项重要的新功能使用户可以控制PostgreSQL优化器的行为，并有可能提高性能。早期版本的PostgreSQL将使用自定义计划五次，第六次创建一个通用计划，并在与自定义计划一样好的情况下使用它。现在，可以通过名为“ plan_cache_mode”的新变量手动控制此行为，该变量允许用户立即强制执行通用计划。这为那些知道其参数恒定并且知道通用计划将起作用的用户带来了显着的性能优势。
6.即时编译
PostgreSQL 11最初引入的一项功能是现在在PostgreSQL 12中默认启用即时复杂功能。即时编译允许处理大量数据的数据仓库查询来更有效地运行执行程序。由于许多用户启用了此功能，因此该功能现已成为PostgreSQL 12的默认功能。
7.校验和控制
追溯到2013年，PostgreSQL引入了一种校验和功能，用于识别数据损坏。首次初始化数据库时必须打开此功能，否则用户必须转储，打开该功能并重新加载数据。这使得某些用户几乎无法使用该功能。在PostgreSQL 12中，通过一个称为“ pg checksums”的命令（以前称为pg verify checksum），用户可以在不转储和重新加载数据的情况下将群集从无校验和更改为校验和。当前，在此更改期间，群集必须处于脱机状态，但是正在开发联机校验和支持。
8.并发重新编制索引
索引并发功能已经存在多年，允许用户创建索引而又不阻止写入索引。重新索引不允许您在写入数据库时创建索引。同时使用reindex，通过在同一位置创建新索引来替换现有索引。同时使用Reindex可以写入索引并保留原始索引名称。显然，当替换索引时，最小的锁定将发生，直到实现替换为止。长期以来要求的功能很难开发，但最终在PostgreSQL 12中交付。
升级到PostgreSQL 12
这八个功能只是PostgreSQL 12中许多改进中的几个。从分区改进到公用表表达式的实现，PostgreSQL 12提供了显着的可用性增强，将使许多新用户和长期用户满意。
Postgres已成为数据库领域的巨头。 根据2019年Stack Overflow对近90,000名开发人员的调查，Postgres的部署现在比SQL Server部署得更为普遍。
原文：https://www.enterprisedb.com/ko/blog/8-major-improvements-postgresql-12
本文：http://jiagoushi.pro/8-major-improvements-postgresql-12
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 40 次浏览
【PostgreSQL 】PostgreSQL 16扩展分析功能
QQ群
视频号
微信
微信公众号
知识星球
最近的版本还包括对支持新体系结构的逻辑表示和超级用户权限的改进。
最近发布的PostgreSQL 16之所以意义重大，原因有很多。它实现了更灵活的访问控制机制，这对涉及托管服务提供商（MSP）的部署具有直接影响。
EnterpriseDB（EDB）高级产品经理Adam Wright肯定道，版本16还支持热备用功能，当它作为逻辑复制的源时，它能够“允许新的体系结构”。EDB是PostgreSQL 16（一个开源关系数据库管理系统）最重要的代码贡献者之一。
然而，最重要的是，最新版本的PostgreSQL包含了分析功能，用于促进复杂的聚合和窗口查询。当与数据库分别用于管理地理空间数据和矢量数据的扩展相结合时，这种增强可能是事务数据库与分析相关性日益增强的最新指标。
“我认为你开始看到的是，对专业数据仓库的需求开始降低，”Wright反思道。“在数据仓库市场的极端，拥有专业系统是必要的。但这真的开始成为极端，在很多用例中，你可以只使用Postgres，而不需要专业系统。”
分析
尽管PostgreSQL在事务系统中被广泛使用，但它在版本16中实现了Wright所说的“任意值函数”，具有明确的分析含义。此函数是SQL:2023标准的一部分。“这个函数实际上主要用于分析数据库，”Wright透露。“复杂的聚合/窗口查询是一种可以用来做什么的副标题。”
这个特殊的函数允许管理员和开发人员跨表中的一组行进行计算。Wright解释道：“你可以比较一行与另一行的计算方式，并对这两行进行汇总。”。“因此，可以通过几行SQL轻松地获得一个运行总数。”这一功能对于比较零售中库存订购的产品类型等用例很有价值，尤其是在大表中表示的不同位置。
向量和地理空间数据
根据Wright的说法，在数据库级别管理此任务通常比在应用程序级别更高效。使用后一种方法，管理员或开发人员将不得不编写比其他方法更多的代码，以及编写多个函数。然后，他们必须将数据带回并进行比较，而不是从数据库服务器中过滤所有内容。然而，“如果我在数据库服务器上编写这个聚合查询，我会比较表中的不同行，”Wright提到。“一旦全部完成，我将只流式传输应用程序所需的记录。”
PostgreSQL扩展PostGIS使用户能够存储和查询地理空间数据。Wright引用了另一个用于管理矢量工作负载的扩展，包括存储矢量数据和支持矢量运算符。Wright评论道：“这只是Postgres中的另一个用例，你不需要去找专业数据库供应商，获得另一份合同和另一份支持，也不需要加入任何你可能需要的东西来实际支持这些工作负载。”
逻辑复制
尽管Wright提到的扩展不是PostgreSQL 16中发布的新功能的一部分，但它们证明了数据库日益增长的分析实用性，这与最近发布的聚合和窗口功能不谋而合。Wright表示，新版还包括对其逻辑复制功能的增强，其中最重要的是“您可以从备用服务器中进行逻辑表示”。有了这种范式，用户可以从Wright所说的热备用中进行逻辑表示，他将其描述为将PostgreSQL数据中的每一个更改物理复制到目标系统——通常是为了实现高可用性。
Wright观察到：“有了逻辑表示，你可能只想复制几个表、一个销售表、一份订单表，并将它们提供给这些不同的系统，而不是从数据库中获取所有更改。”。逻辑表示使用户能够指定要复制的表，当与从备用系统进行复制的能力相结合时，可以广泛扩展复制和体系结构方法的可能性。Wright说：“你可以级联多个复制和类似的事情。”。“你可以在一个系统中做任何事情，但也可以更容易地从Postgres中获取数据子集，进入只读系统……由于这些原生功能，将支持更多的体系结构。”
超级用户改进
PostgreSQL 16还增强了与超级用户相关的控制程度和范围，超级用户拥有相当多的数据和系统访问权限。Wright承认，在以前的版本中，超级用户几乎可以做任何事情，包括篡改底层的“作为Postgres运行的服务的操作系统”。“这对托管数据服务来说是个大问题。”因此，托管服务提供商（包括一些超规模提供商）会将数据库、用户和设置从原始集群“分叉”或复制到另一个集群。
根据Wright的说法，这个过程可能会导致“错误和安全问题”。因此，最新版本的PostgreSQL中更精细的超级用户控件使“你能够对权限进行细粒度管理，并委托DBA管理数据库所需的任务，但不会给他们一些东西，让他们脱离数据库，”Wright说。或者，您可以管理一个角色，但不一定要管理该角色的数据
数据库空间
PostgreSQL 16的新增内容在很大程度上表明了正在影响整个数据库空间的发展。传统上用于事务目的的系统正在承担更多的分析责任。PostgreSQL对用于编写聚合和窗口查询的聚合函数的原生支持，以及对与地理空间和矢量数据相关的工作负载的扩展，可能预示着未来事务数据库和分析数据库之间的传统区别不再那么明显。
- 11 次浏览
【PostgreSQL 】PostgreSQL 中的并行性
PostgreSQL 是最好的对象关系数据库之一，它的架构是基于进程而不是基于线程的。虽然几乎所有当前的数据库系统都使用线程进行并行处理，但 PostgreSQL 基于进程的架构是在 POSIX 线程之前实现的。 PostgreSQL 在启动时启动一个进程“postmaster”，之后每当有新客户端连接到 PostgreSQL 时都会跨越新进程。
在版本 10 之前，单个连接中没有并行性。诚然，来自不同客户端的多个查询由于进程架构而具有并行性，但它们无法从彼此之间获得任何性能优势。换句话说，单个查询串行运行并且没有并行性。这是一个巨大的限制，因为单个查询不能利用多核。 PostgreSQL 中的并行性是从 9.6 版开始引入的。从某种意义上说，并行性是单个进程可以有多个线程来查询系统并利用系统中的多核。这为 PostgreSQL 提供了查询内并行性。
PostgreSQL 中的并行性是作为涵盖顺序扫描、聚合和连接的多个功能的一部分实现的。
PostgreSQL 中的并行组件
PostgreSQL 中有三个重要的并行性组件。这些是进程本身、聚集器和工人。如果没有并行性，进程本身会处理所有数据，但是，当规划器决定查询或其一部分可以并行化时，它会在计划的可并行化部分中添加一个 Gather 节点，并生成该子树的一个 Gather 根节点。查询执行从进程（领导者）级别开始，计划的所有串行部分都由领导者运行。但是，如果对查询的任何部分（或全部）启用并允许并行性，则为它分配带有一组工作人员的收集节点。工作者是与需要并行化的部分树（部分计划）并行运行的线程。关系的块在线程之间划分，使得关系保持顺序。线程数由 PostgreSQL 配置文件中的设置控制。工作人员使用共享内存进行协调/通信，工作人员完成工作后，将结果传递给领导者进行积累。
并行顺序扫描
在 PostgreSQL 9.6 中，添加了对并行顺序扫描的支持。 顺序扫描是对表的扫描，其中依次评估一系列块。 就其本质而言，这允许并行性。 因此，这是第一个实现并行性的自然候选者。 在这种情况下，整个表在多个工作线程中被顺序扫描。 这是一个简单的查询，我们查询 pgbench_accounts 表行 (63165)，它有 1,500,000,000 个元组。 总执行时间为 4,343,080ms。 由于没有定义索引，因此使用顺序扫描。 整个表在没有线程的单个进程中扫描。 因此，无论有多少内核可用，都使用 CPU 的单核。
db=# EXPLAIN ANALYZE SELECT *
FROM pgbench_accounts
WHERE abalance > 0;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..73708261.04 rows=1 width=97)
(actual time=6868.238..4343052.233 rows=63165 loops=1)
Filter: (abalance > 0)
Rows Removed by Filter: 1499936835
Planning Time: 1.155 ms
Execution Time: 4343080.557 ms
(5 rows)
如果这 1,500,000,000 行在一个进程中使用“10”个工作人员并行扫描会怎样？ 它将大大减少执行时间。
db=# EXPLAIN ANALYZE select * from pgbench_accounts where abalance > 0;
QUERY PLAN
----------------------------------------------------------------------
Gather (cost=1000.00..45010087.20 rows=1 width=97)
(actual time=14356.160..1628287.828 rows=63165 loops=1)
Workers Planned: 10
Workers Launched: 10
-> Parallel Seq Scan on pgbench_accounts
(cost=0.00..45009087.10 rows=1 width=97)
(actual time=43694.076..1628068.096 rows=5742 loops=11)
Filter: (abalance > 0)
Rows Removed by Filter: 136357894
Planning Time: 37.714 ms
Execution Time: 1628295.442 ms
(8 rows)
现在总执行时间是1,628,295ms； 使用 10 个工作线程进行扫描时，这提高了 266%。
用于基准的查询：SELECT * FROM pgbench_accounts WHERE abalance > 0;
表大小：426GB
表中的总行数：1,500,000,000
用于基准测试的系统：
CPU：2 Intel(R) Xeon(R) CPU E5-2643 v2 @ 3.50GHz
内存：256GB DDR3 1600
磁盘：ST3000NM0033
上图清楚地显示了并行性如何提高顺序扫描的性能。添加单个工作人员时，性能会降低，这是可以理解的，因为没有获得并行性，但是创建额外的收集节点和单个工作会增加开销。但是，使用多个工作线程时，性能会显着提高。此外，重要的是要注意性能不会以线性或指数方式增加。它会逐渐改善，直到添加更多工人不会带来任何性能提升；有点像接近水平渐近线。该基准测试是在 64 核机器上执行的，很明显，拥有 10 名以上的工作人员不会带来任何显着的性能提升。
并行聚合
在数据库中，计算聚合是非常昂贵的操作。在单个过程中进行评估时，这些需要相当长的时间。在 PostgreSQL 9.6 中，通过简单地将它们分成块（分而治之的策略）添加了并行计算这些的能力。这允许多个工作人员在基于这些计算的最终值由领导者计算之前计算聚合的部分。从技术上讲，PartialAggregate 节点被添加到计划树中，每个 PartialAggregate 节点从一个工作人员那里获取输出。然后将这些输出发送到 FinalizeAggregate 节点，该节点组合来自多个（所有）PartialAggregate 节点的聚合。如此有效地，并行部分计划包括根处的 FinalizeAggregate 节点和将 PartialAggregate 节点作为子节点的 Gather 节点。
db=# EXPLAIN ANALYZE SELECT count(*) from pgbench_accounts;
QUERY PLAN
----------------------------------------------------------------------
Aggregate (cost=73708261.04..73708261.05 rows=1 width=8)
(actual time=2025408.357..2025408.358 rows=1 loops=1)
-> Seq Scan on pgbench_accounts (cost=0.00..67330666.83 rows=2551037683 width=0)
(actual time=8.162..1963979.618 rows=1500000000 loops=1)
Planning Time: 54.295 ms
Execution Time: 2025419.744 ms
(4 rows)
以下是要并行评估聚合时的计划示例。 您可以在这里清楚地看到性能改进。
db=# EXPLAIN ANALYZE SELECT count(*) from pgbench_accounts;
QUERY PLAN
----------------------------------------------------------------------
Finalize Aggregate (cost=45010088.14..45010088.15 rows=1 width=8)
(actual time=1737802.625..1737802.625 rows=1 loops=1)
-> Gather (cost=45010087.10..45010088.11 rows=10 width=8)
(actual time=1737791.426..1737808.572 rows=11 loops=1)
Workers Planned: 10
Workers Launched: 10
-> Partial Aggregate
(cost=45009087.10..45009087.11 rows=1 width=8)
(actual time=1737752.333..1737752.334 rows=1 loops=11)
-> Parallel Seq Scan on pgbench_accounts
(cost=0.00..44371327.68 rows=255103768 width=0)
(actual time=7.037..1731083.005 rows=136363636 loops=11)
Planning Time: 46.031 ms
Execution Time: 1737817.346 ms
(8 rows)
使用并行聚合，在这种特殊情况下，当涉及 10 个并行工作人员时，由于 2,025,419.744 的执行时间减少到 1,737,817.346，我们的性能提升了 16% 以上。
用于基准的查询：SELECT count(*) FROM pgbench_accounts WHERE abalance > 0;
表大小：426GB
表中的总行数：1500000000
用于基准测试的系统：
CPU：2 Intel(R) Xeon(R) CPU E5-2643 v2 @ 3.50GHz
内存：256GB DDR3 1600
磁盘：ST3000NM0033
并行索引（B-Tree）扫描
对 B-Tree 索引的并行支持意味着索引页面是并行扫描的。 B-Tree 索引是 PostgreSQL 中最常用的索引之一。 在 B-Tree 的并行版本中，worker 扫描 B-Tree，当它到达其叶节点时，它会扫描块并触发阻塞的等待 worker 扫描下一个块。
使困惑？ 让我们看一个例子。 假设我们有一个包含 id 和 name 列的表 foo，有 18 行数据。 我们在表 foo 的 id 列上创建一个索引。 系统列 CTID 附加到表的每一行，用于标识该行的物理位置。 CTID 列中有两个值：块号和偏移量。
postgres=# <strong>SELECT</strong> ctid, id <strong>FROM</strong> foo; ctid | id --------+----- (0,55) | 200 (0,56) | 300 (0,57) | 210 (0,58) | 220 (0,59) | 230 (0,60) | 203 (0,61) | 204 (0,62) | 300 (0,63) | 301 (0,64) | 302 (0,65) | 301 (0,66) | 302 (1,31) | 100 (1,32) | 101 (1,33) | 102 (1,34) | 103 (1,35) | 104 (1,36) | 105 (18 rows)
让我们在该表的 id 列上创建 B-Tree 索引。
CREATE INDEX foo_idx ON foo(id)
假设我们要选择 id <= 200 且有 2 个工作人员的值。 Worker-0 将从根节点开始扫描，直到叶子节点 200。它将节点 105 下的下一个块移交给处于阻塞和等待状态的 Worker-1。如果还有其他工作人员，则将块划分为工作人员。重复类似的模式，直到扫描完成。
并行位图扫描
要并行化位图堆扫描，我们需要能够以非常类似于并行顺序扫描的方式在工作人员之间划分块。为此，对一个或多个索引进行扫描，并创建指示要访问哪些块的位图。这是由领导进程完成的，即这部分扫描是按顺序运行的。但是，当将识别出的块传递给工作人员时，并行性就会启动，这与并行顺序扫描中的方式相同。
平行连接
合并连接支持中的并行性也是此版本中添加的最热门功能之一。在这种情况下，一个表与其他表的内部循环哈希或合并连接。无论如何，内部循环不支持并行性。将整个循环作为一个整体进行扫描，当每个worker作为一个整体执行内部循环时，就会发生并行。每个连接发送到聚集的结果累积并产生最终结果。
概括
从我们已经在本博客中讨论过的内容可以明显看出，并行性可以显着提升某些情况的性能，而对另一些情况则略有提升，并且在某些情况下可能会导致性能下降。 确保正确设置了 parallel_setup_cost 或 parallel_tuple_cost，以使查询计划程序能够选择并行计划。 即使为这些 GUI 设置了较低的值，如果没有生成并行计划，请参阅 PostgreSQL 并行性文档以获取详细信息。
对于并行计划，您可以获得每个计划节点的每个工作人员的统计信息，以了解负载在工作人员之间的分布情况。 您可以通过 EXPLAIN (ANALYZE, VERBOSE) 做到这一点。 与任何其他性能特性一样，没有适用于所有工作负载的规则。 应根据需要仔细配置并行性，并且您必须确保获得性能的概率明显高于性能下降的概率。
原文：https://www.percona.com/blog/2019/07/30/parallelism-in-postgresql/
- 34 次浏览
【PostgreSQL 】PostgreSQL 简介
PostgreSQL，也称为Postgres，是一个免费的开源关系数据库管理系统（RDBMS），强调可扩展性和技术标准合规性。 它旨在处理各种工作负载，从单机到数据仓库或具有许多并发用户的Web服务。 它是macOS Server的默认数据库，[11] [12] [13]，也适用于Linux，FreeBSD，OpenBSD和Windows。
PostgreSQL具有原子性，一致性，隔离性，耐久性（ACID）属性，自动可更新视图，物化视图，触发器，外键和存储过程的事务。[14] PostgreSQL由PostgreSQL全球开发小组开发，该小组是众多公司和个人贡献者的多元化小组。[15]
名称
PostgreSQL的开发人员将PostgreSQL称为/poʊstɡrɛsˌkjuːːl/。[16]它简称为Postgres，因为关系数据库中对SQL标准的支持无处不在。最初命名为POSTGRES，名称（Post Ingres）指的是该项目起源于加州大学伯克利分校的RDBMS。[17] [18]经过审查，PostgreSQL核心团队在2007年宣布该产品将继续使用PostgreSQL这个名称。[19]
历史
PostgreSQL是从加州大学伯克利分校的Ingres项目发展而来的。 1982年，Ingres团队的负责人Michael Stonebraker离开伯克利，制作了Ingres的专有版本。[17]他于1985年回到伯克利，并开始了一个后Ingres项目，以解决当代数据库系统的问题，这些系统在20世纪80年代早期变得越来越清晰。他在2014年为这些项目和其他项目赢得了图灵奖，[20]以及其中首创的技术。
新项目POSTGRES旨在增加完全支持数据类型所需的最少功能。[21]这些功能包括定义类型和完全描述关系的能力 - 广泛使用的东西，但完全由用户维护。在POSTGRES中，数据库理解关系，并且可以使用规则以自然的方式检索相关表中的信息。 POSTGRES使用了Ingres的许多想法，但没有使用它的代码。[22]
从1986年开始，已发表的论文描述了该系统的基础，并在1988年的ACM SIGMOD会议上展示了原型版本。该团队于1989年6月向少数用户发布了第1版，随后是1990年6月重新编写规则系统的第2版。1991年发布的第3版再次重写了规则系统，并增加了对多个用户的支持。存储管理器[引证需要]和改进的查询引擎。到1993年，随着支持和功能的要求，用户数量开始超过项目。 1994年6月30日发布版本4.2 [23]后 - 主要是清理 - 项目结束。 Berkeley在MIT许可证变体下发布了POSTGRES，这使得其他开发人员可以将代码用于任何用途。当时，POSTGRES使用了受Ingres影响的POSTQUEL查询语言解释器，该解释器可以与名为monitor的控制台应用程序交互使用。
1994年，伯克利大学的研究生Andrew Yu和Jolly Chen用一种SQL查询语言替换了POSTQUEL查询语言解释器，创建了Postgres95。监视器也被psql取代。 Yu和Chen于1995年5月5日向beta测试者宣布了第一个版本（0.01）.Postgres95的1.0版本于1995年9月5日宣布，更加自由的许可证使软件可以自由修改。
1996年7月8日，Hub.org Networking Services的Marc Fournier为开源开发工作提供了第一个非大学开发服务器。[1]在Bruce Momjian和Vadim B. Mikheev的参与下，工作开始稳定从伯克利继承的代码。
1996年，该项目更名为PostgreSQL以反映其对SQL的支持。 PostgreSQL.org网站的在线展示始于1996年10月22日。[24]第一个PostgreSQL版本于1997年1月29日形成6.0版。从那时起，全世界的开发人员和志愿者都将该软件作为PostgreSQL全球开发组维护。[15]
该项目继续根据其免费和开源软件PostgreSQL许可证发布。代码来自专有供应商，支持公司和开源程序员的贡献。
多版本并发控制（MVCC）
PostgreSQL通过多版本并发控制（MVCC）管理并发，它为每个事务提供数据库的“快照”，允许在不影响其他事务的情况下进行更改。这在很大程度上消除了对读锁的需要，并确保数据库保持ACID原则。 PostgreSQL提供三个级别的事务隔离：Read Committed，Repeatable Read和Serializable。因为PostgreSQL不受脏读操作的影响，所以请求Read Uncommitted事务隔离级别提供read committed。 PostgreSQL通过可序列化快照隔离（SSI）方法支持完全可序列化。[25]
存储和复制
复制
PostgreSQL包括内置的二进制复制，它基于将更改（预写日志（WAL））异步传递到副本节点，并能够对这些复制节点运行只读查询。这允许有效地在多个节点之间分割读取流量。早期允许类似读取扩展的复制软件通常依赖于向主服务器添加复制触发器，从而增加负载。
PostgreSQL包含内置的同步复制[26]，可确保对于每个写入事务，主服务器等待至少一个副本节点已将数据写入其事务日志。与其他数据库系统不同，可以按数据库，每用户，每会话甚至每个事务指定事务的持久性（无论是异步还是同步）。这对于不需要此类保证的工作负载非常有用，并且可能不需要所有数据，因为由于需要确认事务到达同步备用数据库而导致性能降低。
备用服务器可以是同步或异步的。可以在配置中指定同步备用服务器，以确定哪些服务器可用于同步复制。列表中第一个主动流式传输将用作当前的同步服务器。当此操作失败时，系统将故障转移到下一行。
PostgreSQL核心中不包含同步多主复制。 Postgres-XC基于PostgreSQL，提供可扩展的同步多主复制。[27]它的许可与PostgreSQL相同。一个相关项目叫做Postgres-XL。 Postgres-R是另一个分支。[28]双向复制（BDR）是PostgreSQL的异步多主复制系统。[29]
repmgr等工具可以更轻松地管理复制集群。
有几个基于异步触发器的复制包可用。即使在引入扩展核心功能之后，对于完整数据库集群的二进制复制不合适的情况，这些仍然有用：
Slony聚-I
Londiste，SkyTools的一部分（由Skype开发）
Bucardo多主复制（由Backcountry.com开发）[30]
SymmetricDS多主，多层复制
索引
PostgreSQL包括对常规B树和散列表索引的内置支持，以及四种索引访问方法：广义搜索树（GiST），广义反向索引（GIN），空间分区GiST（SP-GiST）[31]和Block范围索引（BRIN）。此外，可以创建用户定义的索引方法，尽管这是一个非常复杂的过程。 PostgreSQL中的索引还支持以下功能：
可以使用表达式或函数的结果索引创建表达式索引，而不仅仅是列的值。
可以通过在CREATE INDEX语句的末尾添加WHERE子句来创建仅索引表的一部分的部分索引。这允许创建较小的索引。
规划器能够使用多个索引来使用临时内存位图索引操作来满足复杂查询（对于将大型事实表连接到较小维度表（例如以星型模式排列的那些），数据仓库应用程序非常有用）。
k-最近邻（k-NN）索引（也称为KNN-GiST [32]）提供了对指定的“最接近的值”的有效搜索，对于查找相似的单词或用地理空间数据关闭对象或位置很有用。这是在没有穷尽的值匹配的情况下实现的。
仅索引扫描通常允许系统从索引获取数据，而无需访问主表。
PostgreSQL 9.5引入了块范围索引（BRIN）。
Schema
在PostgreSQL中，Schema包含除角色和表空间之外的所有对象。模式有效地像名称空间一样，允许同名对象在同一个数据库中共存。默认情况下，新创建的数据库具有名为public的模式，但可以添加任何其他模式，并且公共模式不是必需的。
search_path设置确定PostgreSQL检查非限定对象（没有前缀模式的对象）的模式的顺序。默认情况下，它设置为$ user，public（$ user指当前连接的数据库用户）。此缺省值可以在数据库或角色级别设置，但由于它是会话参数，因此可以在客户端会话期间自由更改（甚至多次），仅影响该会话。
在对象查找期间，将以无提示方式跳过search_path中列出的不存在的模式。
在search_path中首先出现新的对象，无论哪个有效的模式（当前存在的模式）都出现.Schema是数据库的概要。
数据类型
支持各种本机数据类型，包括：
- 布尔
- 任意精度数值
- 字符（text，varchar，char）
- 二进制
- 日期/时间（时间戳/时间有/无时区，日期，间隔）
- 钱
- 枚举
- 位串
- 文字搜索类型
- 综合
- HStore，PostgreSQL中支持扩展的键值存储[33]
- 数组（可变长度，可以是任何数据类型，包括文本和复合类型），总存储大小最多为1 GB
- 几何图元
- IPv4和IPv6地址
- 无类别域间路由（CIDR）块和MAC地址
- 支持XPath查询的XML
- 通用唯一标识符（UUID）
- JavaScript Object Notation（JSON）和更快的二进制JSONB（自版本9.4起;与BSON [34]不同）
此外，用户可以创建自己的数据类型，通常可以通过PostgreSQL的索引基础结构 - GiST，GIN，SP-GiST完全索引。这些示例包括来自PostgreSQL的PostGIS项目的地理信息系统（GIS）数据类型。
还有一种称为域的数据类型，它与任何其他数据类型相同，但具有由该域的创建者定义的可选约束。这意味着使用域输入到列中的任何数据都必须符合作为域的一部分定义的任何约束。
可以使用表示一系列数据的数据类型，称为范围类型。这些可以是离散范围（例如，所有整数值1到10）或连续范围（例如，在上午10:00到11:00之间的任何时间）。可用的内置范围类型包括整数范围，大整数，十进制数，时间戳（有和没有时区）和日期。
可以创建自定义范围类型以使新类型的范围可用，例如使用inet类型作为基础的IP地址范围，或使用float数据类型作为基础的浮点范围。范围类型分别使用[/]和（/）字符支持包含和排除范围边界。 （例如，[4,9]表示从4开始包括但不包括9的所有整数。）范围类型也与用于检查重叠，包含，权限等的现有运算符兼容。
用户定义的对象
可以创建数据库中几乎所有对象的新类型，包括：
- 类型转换
- 转换
- 数据类型
- 数据域
- 功能，包括聚合函数和窗口函数
- 索引包括自定义类型的自定义索引
- 运营商（现有运营商可能超载）
- 程序语言
继承
可以将表设置为从父表继承其特征。子表中的数据似乎存在于父表中，除非使用ONLY关键字从父表中选择数据，即SELECT * FROM ONLY parent_table;。在父表中添加列将导致该列显示在子表中。
继承可用于实现表分区，使用触发器或规则将父表的插入指向正确的子表。
截至2010年，尚未完全支持此功能 - 特别是，表约束目前不可继承。父表上的所有检查约束和非空约束都由其子表自动继承。其他类型的约束（唯一，主键和外键约束）不会被继承。
继承提供了一种将实体关系图（ERD）中描述的泛化层次结构的特征直接映射到PostgreSQL数据库的方法。
其他存储功能
- 参照完整性约束，包括外键约束，列约束和行检查
- 二进制和文本大对象存储
- 表空间
- 每列整理
- 在线备份
- 使用预写日志记录实现的时间点恢复
- 使用pg_upgrade进行就地升级以减少停机时间（支持从8.3.x及更高版本升级）
控制和连接
外国数据包装器
PostgreSQL可以链接到其他系统，通过外部数据包装器（FDW）检索数据。[35]这些可以采用任何数据源的形式，例如文件系统，另一个关系数据库管理系统（RDBMS）或Web服务。这意味着常规数据库查询可以使用常规表等这些数据源，甚至可以将多个数据源连接在一起。
接口
PostgreSQL有几个可用的接口，并且在编程语言库中也得到广泛支持。内置接口包括libpq（PostgreSQL的官方C应用程序接口）和ECPG（嵌入式C系统）。外部接口包括：
- libpqxx：C ++接口
- Pgfe：C ++接口
- PostgresDAC：PostgresDAC（适用于Embarcadero RadStudio，Delphi，CBuilder XE-XE3）
- DBD :: Pg：Perl DBI驱动程序
- JDBC：Java数据库连接（JDBC）接口
- Lua：Lua接口
- Npgsql：.NET数据提供程序
- ST-Links SpatialKit：链接工具到ArcGIS
- PostgreSQL.jl：Julia界面
- node-postgres：Node.js接口
- pgoledb：OLE DB接口
- psqlODBC：打开数据库连接（ODBC）接口
- psycopg2：[36] Python接口（也被HTSQL使用）
- pgtclng：Tcl接口
- pyODBC：Python库
- php5-pgsql：基于libpq的PHP驱动程序
- 后现代：一个Common Lisp接口
- pq：Go数据库/ sql包的纯Go PostgreSQL驱动程序。驱动程序通过了兼容性测试套件。[37]
- RPostgreSQL：R接口[38]
- dpq：libpq的D接口
- epgsql：Erlang接口
- Rust-Postgres：Rust界面
程序语言
过程语言允许开发人员使用自定义子例程（函数）扩展数据库，通常称为存储过程。这些函数可用于构建数据库触发器（在修改某些数据时调用的函数）以及自定义数据类型和聚合函数。[39]在SQL级别使用DO命令，也可以在不定义函数的情况下调用过程语言。[40]
语言分为两组：用安全语言编写的程序是沙箱，可以由任何用户安全地创建和使用。以不安全语言编写的过程只能由超级用户创建，因为它们允许绕过数据库的安全限制，但也可以访问数据库外部的源。像Perl这样的语言提供了安全和不安全的版本。
PostgreSQL内置了对三种过程语言的支持：
普通的SQL（安全）。更简单的SQL函数可以内联扩展到调用（SQL）查询，这样可以节省函数调用开销，并允许查询优化器“查看”函数内部。
过程语言/ PostgreSQL（PL / pgSQL）（安全），类似于Oracle的SQL（PL / SQL）过程语言和SQL /持久存储模块（SQL / PSM）的过程语言。
C（不安全），允许将一个或多个自定义共享库加载到数据库中。用C语言编写的函数提供了最佳性能，但代码中的错误可能会崩溃并可能破坏数据库。大多数内置函数都是用C语言编写的。
此外，PostgreSQL允许通过扩展将过程语言加载到数据库中。 PostgreSQL包含三种语言扩展，以支持Perl，Python [41]和Tcl。有外部项目可以添加对许多其他语言的支持，[42]包括Java，JavaScript（PL / V8），R，Ruby等。
触发器
触发器是由SQL数据操作语言（DML）语句的操作触发的事件。例如，INSERT语句可能会激活一个触发器，该触发器检查语句的值是否有效。大多数触发器仅由INSERT或UPDATE语句激活。
触发器完全受支持，可以附加到表。触发器可以是每列和条件的，因为UPDATE触发器可以定位表的特定列，并且可以告诉触发器在触发器的WHERE子句中指定的一组条件下执行。可以使用INSTEAD OF条件将触发器附加到视图。按字母顺序触发多个触发器。除了调用本机PL / pgSQL中编写的函数之外，触发器还可以调用用其他语言编写的函数，如PL / Python或PL / Perl。
异步通知
PostgreSQL提供了一个异步消息传递系统，可通过NOTIFY，LISTEN和UNLISTEN命令进行访问。会话可以发出NOTIFY命令以及用户指定的通道和可选的有效负载，以标记发生的特定事件。其他会话能够通过发出LISTEN命令来检测这些事件，该命令可以侦听特定通道。此功能可用于多种用途，例如让其他会话知道表何时更新，或者让单独的应用程序检测何时执行特定操作。这样的系统可以防止应用程序连续轮询以查看是否有任何更改，并减少不必要的开销。通知是完全事务性的，因为在提交它们的事务之前不会发送消息。这消除了为正在执行的操作发送消息的问题，然后回滚。
PostgreSQL的许多连接器都支持这个通知系统（包括libpq，JDBC，Npgsql，psycopg和node.js），因此外部应用程序可以使用它。
规则
规则允许重写传入查询的“查询树”。 “查询重写规则”附加到表/类，并将传入的DML（选择，插入，更新和/或删除）“重写”到一个或多个查询中，这些查询替换原始DML语句或执行除此之外。查询重写发生在DML语句解析之后，但在查询规划之前。
其他查询功能
- 交易
- 全文检索
- Views
- 物化Views43]
- 可更新的Views44]
- 递归Views[45]
- 内，外（全，左，右）和交叉连接
- 子选择
- 相关的子查询[46]
- 正则表达式[47]
- 公用表表达式和可写公用表表达式
- 通过传输层安全性（TLS）加密连接;当前版本不使用易受攻击的SSL，即使使用该配置选项[48]
- 域
- 保存点
- 两阶段提交
- 超大属性存储技术（TOAST）用于通过自动压缩在单独的区域中透明地存储大型表属性（例如大型MIME附件或XML消息）。
- 嵌入式SQL使用预处理器实现。 SQL代码首先被嵌入到C代码中。然后代码通过ECPG预处理器运行，它将SQL替换为对代码库的调用。然后可以使用C编译器编译代码。嵌入也适用于C ++，但它不能识别所有C ++构造。
并发模型
PostgreSQL服务器是基于进程的（非线程化的），每个数据库会话使用一个操作系统进程。多个会话由操作系统自动分布在所有可用的CPU上。从PostgreSQL 9.6开始，许多类型的查询也可以跨多个后台工作进程并行化，利用多个CPU或核心。[49]客户端应用程序可以使用线程并从每个线程创建多个数据库连接。[50]
安全
PostgreSQL基于每个角色管理其内部安全性。角色通常被视为用户（可以登录的角色）或组（其他角色是其成员的角色）。可以在列级别的任何对象上授予或撤消权限，还可以允许/阻止在数据库，模式或表级别创建新对象。
PostgreSQL的SECURITY LABEL功能（SQL标准的扩展）允许额外的安全性;带有捆绑的可加载模块，支持基于安全增强型Linux（SELinux）安全策略的基于标签的强制访问控制（MAC）。[51] [52]
PostgreSQL本身支持大量的外部认证机制，包括：
- 密码：MD5或纯文本
- 通用安全服务应用程序接口（GSSAPI）
- 安全支持提供程序接口（SSPI）
- Kerberos的
- ident（将标识服务器提供的O / S用户名映射到数据库用户名）
- 对等（将本地用户名映射到数据库用户名）
- 轻量级目录访问协议（LDAP）
- 活动目录（AD）
- 半径
- 证书
- 可插拔认证模块（PAM）
- GSSAPI，SSPI，Kerberos，对等，身份和证书方法也可以使用指定的“映射”文件，该文件列出允许该身份验证系统匹配的用户作为特定数据库用户进行连接。
这些方法在集群的基于主机的身份验证配置文件（pg_hba.conf）中指定，该文件确定允许的连接。这允许控制哪个用户可以连接到哪个数据库，他们可以从哪里连接（IP地址，IP地址范围，域套接字），将强制执行哪个身份验证系统，以及连接是否必须使用传输层安全性（TLS）。
符合标准
PostgreSQL声称与SQL标准的一致性很高，但并不完整。一个例外是处理不带引号的标识符，如表名或列名。在PostgreSQL中，它们被折叠 - 内部 - 小写字符[53]，而标准则表示不带引号的标识符应折叠成大写字母。因此，根据标准，Foo应该等同于FOO而不是foo。
基准和表现
PostgreSQL的许多非正式表演研究已经完成。[54]旨在提高可伸缩性的性能改进始于8.1版。 8.0版和8.4版之间的简单基准测试表明，后者在只读工作负载上的速度提高了10倍以上，在读写工作负载上的速度提高了至少7.5倍。[55]
第一个行业标准和同行验证的基准测试于2007年6月完成，使用Sun Java System Application Server（GlassFish的专有版本）9.0 Platform Edition，基于UltraSPARC T1的Sun Fire服务器和PostgreSQL 8.2。[56] 778.14 SPECjAppServer2004 JOPS @ Standard的结果与基于Itanium的HP-UX系统上的874 JOPS @ Standard与Oracle 10相比毫不逊色。[54]
2007年8月，Sun提交了一份改进的基准分数813.73 SPECjAppServer2004 JOPS @ Standard。随着被测系统的降价，价格/性能从84.98美元/ JOPS提高到70.57美元/ JOPS。[57]
PostgreSQL的默认配置仅使用少量专用内存用于性能关键目的，例如缓存数据库块和排序。这种限制主要是因为较旧的操作系统需要更改内核以允许分配大块共享内存。[58] PostgreSQL.org在维基中提供有关基本推荐性能实践的建议。[59]
2012年4月，EnterpriseDB的Robert Haas使用64核的服务器演示了PostgreSQL 9.2的线性CPU可扩展性。[60]
Matloob Khushi在Postgresql 9.0和MySQL 5.6.15之间进行基准测试，以处理基因组数据。在他的表现分析中，他发现PostgreSQL提取的重叠基因组区域比MySQL快8倍，使用两个80,000个数据集，每个数据集形成随机的人类DNA区域。 PostgreSQL中的插入和数据上传也更好，尽管两个数据库的一般搜索能力几乎相同。[61]
平台
PostgreSQL可用于以下操作系统：Linux（所有最新发行版），Windows（Windows 2000 SP4及更高版本;可编译，例如Visual Studio，现在最近的2017版本），FreeBSD，OpenBSD，[62] NetBSD，macOS （OS X），[13] AIX，HP-UX，Solaris和UnixWare;尚未正式测试：DragonFly BSD，BSD / OS，IRIX，OpenIndiana，[63] OpenSolaris，OpenServer和Tru64 UNIX。大多数其他类Unix系统也可以工作;最现代化的支持。
PostgreSQL适用于以下任何指令集体系结构：Windows和其他操作系统上的x86和x86-64;除了Windows之外，还支持这些：IA-64 Itanium（HP-UX的外部支持），PowerPC，PowerPC 64，S / 390，S / 390x，SPARC，SPARC 64，ARMv8-A（64位）[64]和较旧的ARM（32位，包括较旧的，如Raspberry Pi [65]中的ARMv6），MIPS，MIPSel和PA-RISC。它也被称为Alpha（在9.5中删除），M68k，M32R，NS32k和VAX。除此之外，还可以通过禁用自旋锁来为不受支持的CPU构建PostgreSQL。[66]
数据库管理
另请参见：数据库工具的比较
用于管理PostgreSQL的开源前端和工具包括：
- PSQL
-
- PostgreSQL的主要前端是psql命令行程序，可用于直接输入SQL查询或从文件执行它们。此外，psql提供了许多元命令和各种类似shell的功能，以便于编写脚本和自动执行各种任务;例如，对象名称和SQL语法的选项卡完成。
- pgAdmin的
- pgAdmin包是PostgreSQL的免费开源图形用户界面（GUI）管理工具，许多计算机平台都支持该工具。[67]该计划有十几种语言版本。第一个原型名为pgManager，是从1998年开始为PostgreSQL 6.3.2编写的，并在后几个月以GNU通用公共许可证（GPL）的形式重写并发布为pgAdmin。第二个版本（名为pgAdmin II）是完全重写的，首次发布于2002年1月16日。第三个版本pgAdmin III最初是在Artistic License下发布的，然后在与PostgreSQL相同的许可下发布。与以Visual Basic编写的先前版本不同，pgAdmin III是用C ++编写的，使用wxWidgets [68]框架允许它在大多数常见操作系统上运行。查询工具包括一个名为pgScript的脚本语言，用于支持管理和开发任务。 2014年12月，pgAdmin项目创始人兼主要开发人员Dave Page [69]宣布，随着向基于网络的模式的转变，pgAdmin 4的工作已经开始，旨在促进云部署。[70] 2016年，pgAdmin 4发布。 pgAdmin 4后端是用Python编写的，使用Flask和Qt框架。[71]
- phpPgAdmin的
- phpPgAdmin是一个基于Web的PostgreSQL管理工具，用PHP编写，基于最初为MySQL管理编写的流行的phpMyAdmin接口。[72]
- PostgreSQL Studio
- PostgreSQL Studio允许用户从基于Web的控制台执行必要的PostgreSQL数据库开发任务。 PostgreSQL Studio允许用户使用云数据库而无需打开防火墙。[73]
- TeamPostgreSQL
- 用于PostgreSQL的AJAX / JavaScript驱动的Web界面。允许通过Web浏览器浏览，维护和创建数据和数据库对象。该界面提供带选项卡的SQL编辑器，包括自动完成，行编辑小部件，行和表之间的点击式外键导航，常用脚本的收藏夹管理以及其他功能。支持Web界面和数据库连接的SSH。安装程序可用于Windows，Macintosh和Linux，以及一个从脚本运行的简单跨平台存档。[74]
- LibreOffice，OpenOffice.org
- LibreOffice和OpenOffice.org Base可以用作PostgreSQL的前端。[75] [76]
- pgBadger
- pgBadger PostgreSQL日志分析器从PostgreSQL日志文件生成详细报告。[77]
- pgDevOps
- pgDevOps是一套Web工具，用于安装和管理多个PostgreSQL版本，扩展和社区组件，开发SQL查询，监视正在运行的数据库并查找性能问题。[78]
- 许多公司为PostgreSQL提供专有工具。它们通常由适用于各种特定数据库产品的通用核心组成。这些工具主要与开源工具共享管理功能，但在数据建模，导入，导出或报告方面提供了改进。
知名的用户
使用PostgreSQL作为主数据库的着名组织和产品包括：
- 2009年，社交网站Myspace使用Aster Data Systems的nCluster数据库进行数据仓库，这是基于未经修改的PostgreSQL构建的。[79] [80]
- Geni.com使用PostgreSQL作为他们的主要家谱数据库。[81]
- OpenStreetMap，一个创建免费可编辑世界地图的合作项目。[82]
- Afili，.org，.info等域名注册机构。[83] [84]
- 索尼在线多人在线游戏。[85]
- 巴斯夫，他们的农业综合企业门户网站的购物平台。[86]
- Reddit社交新闻网站。[87]
- Skype VoIP应用程序，中央商业数据库。[88]
- Sun xVM，Sun的虚拟化和数据中心自动化套件。[89]
- MusicBrainz，开放在线音乐百科全书。[90]
- 国际空间站 - 收集轨道上的遥测数据并将其复制到地面。[91]
- MyYearbook社交网站。[92]
- Instagram，一种移动照片共享服务。[93]
- Disqus，在线讨论和评论服务。[94]
- TripAdvisor，旅游信息网站，主要是用户生成的内容。[95]
- 俄罗斯互联网公司Yandex将其Yandex.Mail服务从Oracle改为Postgres。[96]
- Amazon Redshift是AWS的一部分，是基于ParAccel Postgres修改的柱状在线分析处理（OLAP）系统。
- 国家海洋和大气管理局（NOAA）国家气象局（NWS），交互式预报准备系统（IFPS），该系统集成了NEXRAD天气雷达，地面和水文系统的数据，以建立详细的本地化预报模型。[84] [ 97]
- 英国的国家气象服务部门Met Office已经开始将Oracle for PostgreSQL转换为部署更多开源技术的战略。[97] [98]
- WhitePages.com一直在使用Oracle [99] [循环引用]和MySQL，但是当它在内部移动其核心目录时，它转向了PostgreSQL。由于WhitePages.com需要结合来自多个来源的大量数据，因此PostgreSQL以高速率加载和索引数据的能力是决定使用PostgreSQL的关键。[84]
- FlightAware，一个航班跟踪网站。[100]
- Grofers，一种在线杂货店送货服务。[101]
- 卫报在2018年从MongoDB迁移到PostgreSQL。[102]
服务实施
一些着名的供应商提供PostgreSQL作为软件即服务：
- 作为服务提供商的平台Heroku从2007年开始就支持PostgreSQL。[103]它们提供增值功能，如完全数据库回滚（从任何指定时间恢复数据库的能力），[104]基于WAL-E，由Heroku开发的开源软件。[105]
- 2012年1月，EnterpriseDB发布了PostgreSQL和他们自己专有的Postgres Plus Advanced Server的云版本，并自动配置了故障转移，复制，负载平衡和扩展。它在Amazon Web Services上运行。[106]
- 自2012年5月起，VMware为VMware vSphere上的私有云提供了vFabric Postgres（也称为vPostgres [107]）。[108]
- 2013年11月，亚马逊网络服务公司宣布将PostgreSQL添加到他们的关系数据库服务产品中。[109] [110]
- 2016年11月，亚马逊网络服务公司宣布将PostgreSQL兼容性添加到其原生的Amazon Aurora托管数据库产品中。[111]
- 2017年5月，Microsoft Azure宣布为PostgreSQL提供Azure数据库[112]
原文：https://en.wikipedia.org/wiki/PostgreSQL
讨论：请加入知识星球或者小红圈【首席架构师圈】
- 200 次浏览
【PostgreSQL 】如何在CentOS 7 / CentOS 8上安装PostgreSQL 12
本指南将引导您完成在CentOS 7 / CentOS 8 Linux服务器上安装PostgreSQL 12的步骤。 PostgreSQL是一个基于POSTGRES 4.2的对象关系数据库管理系统。开发人员和数据库管理员可以使用PostgreSQL 12。
PostgreSQL项目为最常见的发行版提供了所有受支持版本的软件包的存储库。支持的发行版包括所有Red Hat系列，其中包括CentOS，Fedora，Scientific Linux，Oracle Linux和Red Hat Enterprise Linux。
使用以下步骤在CentOS 8 / CentOS 7上安装PostgreSQL 12。
步骤1：将PostgreSQL Yum存储库添加到CentOS 7 / CentOS 8
PostgreSQL Yum存储库将与您的常规系统和补丁程序管理集成，并在PostgreSQL的整个支持期限内为所有受支持的PostgreSQL版本提供自动更新。
可以通过运行以下命令将其添加到CentOS系统中：
CentOS 8：
sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
CentOS 7:
sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
您可以通过运行以下命令获取有关已安装软件包的更多信息：
$ rpm -qi pgdg-redhat-repo Name : pgdg-redhat-repo Version : 42.0 Release : 4 Architecture: noarch Install Date: Thu 19 Sep 2019 06:34:53 PM UTC Group : System Environment/Base Size : 6915 License : PostgreSQL Signature : DSA/SHA1, Wed 17 Apr 2019 04:12:42 AM UTC, Key ID 1f16d2e1442df0f8 Source RPM : pgdg-redhat-repo-42.0-4.src.rpm Build Date : Wed 17 Apr 2019 04:12:41 AM UTC Build Host : koji-centos7-x86-64-pgbuild Relocations : (not relocatable) Vendor : PostgreSQL Global Development Group URL : https://yum.postgresql.org Summary : PostgreSQL PGDG RPMs- Yum Repository Configuration for Red Hat / CentOS / Scientific Linux Description : This package contains yum configuration for Red Hat Enterprise Linux, CentOS and Scientific Linux. and also the GPG key for PGDG RPMs.
步骤2：在CentOS 8 / CentOS 7上安装PostgreSQL 12
添加YUM存储库后，我们可以使用以下命令在CentOS 7/8上安装PostgreSQL 12。
CentOS 8上的PostgreSQL 12
禁用内置的PostgreSQL模块：
sudo dnf -qy module disable postgresql
然后安装客户端和服务器软件包：
sudo dnf -y install postgresql12 postgresql12-server
CentOS 7上的PostgreSQL 12
安装PostgreSQL客户端和服务器软件包：
sudo yum -y install epel-release yum-utils sudo yum-config-manager --enable pgdg12 sudo yum install postgresql12-server postgresql12
样本安装输出：
Dependencies Resolved =================================================================================================================================================== Package Arch Version Repository Size =================================================================================================================================================== Installing: postgresql12 x86_64 12beta4-1PGDG.rhel7 pgdg12-testing 1.8 M postgresql12-server x86_64 12beta4-1PGDG.rhel7 pgdg12-testing 5.4 M Installing for dependencies: libicu x86_64 50.2-3.el7 base 6.9 M postgresql12-libs x86_64 12beta4-1PGDG.rhel7 pgdg12-testing 383 k python3 x86_64 3.6.8-10.el7 base 69 k python3-libs x86_64 3.6.8-10.el7 base 7.0 M python3-pip noarch 9.0.3-5.el7 base 1.8 M python3-setuptools noarch 39.2.0-10.el7 base 629 k Transaction Summary =================================================================================================================================================== Install 2 Packages (+6 Dependent packages) Total download size: 24 M Installed size: 104 M Downloading packages: (1/8): libicu-50.2-3.el7.x86_64.rpm | 6.9 MB 00:00:00 warning: /var/cache/yum/x86_64/7/pgdg12-testing/packages/postgresql12-libs-12beta4-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Public key for postgresql12-libs-12beta4-1PGDG.rhel7.x86_64.rpm is not installed (2/8): postgresql12-libs-12beta4-1PGDG.rhel7.x86_64.rpm | 383 kB 00:00:00 (3/8): python3-3.6.8-10.el7.x86_64.rpm | 69 kB 00:00:00 (4/8): python3-setuptools-39.2.0-10.el7.noarch.rpm | 629 kB 00:00:00 (5/8): postgresql12-12beta4-1PGDG.rhel7.x86_64.rpm | 1.8 MB 00:00:00 (6/8): python3-libs-3.6.8-10.el7.x86_64.rpm | 7.0 MB 00:00:00 (7/8): postgresql12-server-12beta4-1PGDG.rhel7.x86_64.rpm | 5.4 MB 00:00:00 (8/8): python3-pip-9.0.3-5.el7.noarch.rpm | 1.8 MB 00:00:00 --------------------------------------------------------------------------------------------------------------------------------------------------- Total 15 MB/s | 24 MB 00:00:01 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-4.noarch (installed) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : libicu-50.2-3.el7.x86_64 1/8 Installing : postgresql12-libs-12beta4-1PGDG.rhel7.x86_64 2/8 Installing : python3-libs-3.6.8-10.el7.x86_64 3/8 Installing : python3-setuptools-39.2.0-10.el7.noarch 4/8 Installing : python3-3.6.8-10.el7.x86_64 5/8 Installing : python3-pip-9.0.3-5.el7.noarch 6/8 Installing : postgresql12-12beta4-1PGDG.rhel7.x86_64 7/8 Installing : postgresql12-server-12beta4-1PGDG.rhel7.x86_64 8/8 Verifying : postgresql12-libs-12beta4-1PGDG.rhel7.x86_64 1/8 Verifying : python3-pip-9.0.3-5.el7.noarch 2/8 Verifying : libicu-50.2-3.el7.x86_64 3/8 Verifying : python3-libs-3.6.8-10.el7.x86_64 4/8 Verifying : postgresql12-12beta4-1PGDG.rhel7.x86_64 5/8 Verifying : postgresql12-server-12beta4-1PGDG.rhel7.x86_64 6/8 Verifying : python3-setuptools-39.2.0-10.el7.noarch 7/8 Verifying : python3-3.6.8-10.el7.x86_64 8/8 Installed: postgresql12.x86_64 0:12beta4-1PGDG.rhel7 postgresql12-server.x86_64 0:12beta4-1PGDG.rhel7 Dependency Installed: libicu.x86_64 0:50.2-3.el7 postgresql12-libs.x86_64 0:12beta4-1PGDG.rhel7 python3.x86_64 0:3.6.8-10.el7 python3-libs.x86_64 0:3.6.8-10.el7 python3-pip.noarch 0:9.0.3-5.el7 python3-setuptools.noarch 0:39.2.0-10.el7
步骤3：初始化并启动数据库服务
安装后，需要先进行数据库初始化，然后才能启动服务。
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
数据库主要配置ifile写入：/var/lib/pgsql/12/data/postgresql.conf
启动并启用数据库服务器服务。
sudo systemctl enable --now postgresql-12
确认服务已启动，没有任何错误。
$ systemctl status postgresql-12 ● postgresql-12.service - PostgreSQL 12 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2019-09-19 18:50:10 UTC; 39s ago Docs: https://www.postgresql.org/docs/12/static/ Process: 10647 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 10652 (postmaster) CGroup: /system.slice/postgresql-12.service ├─10652 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/ ├─10654 postgres: logger ├─10656 postgres: checkpointer ├─10657 postgres: background writer ├─10658 postgres: walwriter ├─10659 postgres: autovacuum launcher ├─10660 postgres: stats collector └─10661 postgres: logical replication launcher Sep 19 18:50:10 cent7.novalocal systemd[1]: Starting PostgreSQL 12 database server... Sep 19 18:50:10 cent7.novalocal postmaster[10652]: 2019-09-19 18:50:10.207 UTC [10652] LOG: starting PostgreSQL 12beta4 on x86_64-pc-lin... 64-bit Sep 19 18:50:10 cent7.novalocal postmaster[10652]: 2019-09-19 18:50:10.209 UTC [10652] LOG: listening on IPv6 address "::1", port 5432 Sep 19 18:50:10 cent7.novalocal postmaster[10652]: 2019-09-19 18:50:10.209 UTC [10652] LOG: listening on IPv4 address "127.0.0.1", port 5432 Sep 19 18:50:10 cent7.novalocal postmaster[10652]: 2019-09-19 18:50:10.214 UTC [10652] LOG: listening on Unix socket "/var/run/postgresq...L.5432" Sep 19 18:50:10 cent7.novalocal postmaster[10652]: 2019-09-19 18:50:10.229 UTC [10652] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" Sep 19 18:50:10 cent7.novalocal postmaster[10652]: 2019-09-19 18:50:10.254 UTC [10652] LOG: redirecting log output to logging collector process Sep 19 18:50:10 cent7.novalocal postmaster[10652]: 2019-09-19 18:50:10.254 UTC [10652] HINT: Future log output will appear in directory "log". Sep 19 18:50:10 cent7.novalocal systemd[1]: Started PostgreSQL 12 database server. Hint: Some lines were ellipsized, use -l to show in full.
如果您有正在运行的防火墙服务，并且远程客户端应连接到数据库服务器，则允许PostgreSQL服务。
sudo firewall-cmd --add-service=postgresql --permanent sudo firewall-cmd --reload
第4步：设置PostgreSQL管理员用户的密码
设置PostgreSQL管理员用户
$ sudo su - postgres ~]$ psql -c "alter user postgres with password 'StrongPassword'" ALTER ROLE
步骤5：启用远程访问（可选）
编辑文件/var/lib/pgsql/12/data/postgresql.conf并将所有服务器的“监听地址”设置为服务器IP地址或“ *”。
listen_addresses ='192.168.10.10'
还设置PostgreSQL接受远程连接
$ sudo vim /var/lib/pgsql/12/data/pg_hba.conf # Accept from anywhere host all all 0.0.0.0/0 md5 # Accept from trusted subnet host all all 192.168.18.0/24 md5
提交更改后，重新启动数据库服务。
sudo systemctl restart postgresql-12
步骤6：安装pgAdmin 4 Web界面
pgAdmin是领先的开源功能丰富的PostgreSQL管理和开发平台，可在Linux，Unix，Mac OS X和Windows上运行。 这是在CentOS上安装pgAdmin4的链接。
How To Install pgAdmin 4 on CentOS 8 Linux
原文：https://computingforgeeks.com/how-to-install-postgresql-12-on-centos-7/
本文：http://jiagoushi.pro/node/920
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 52 次浏览
【PostgreSQL 技术】扩展Postgresql到TB甚至更大的想法
在最近与一位DBA交谈后，他迈出了将大型数据库从Oracle迁移到Postgres的第一步。
我认为没有足够的文章介绍PostgreSQL的特性和黑客，从硬件中挤出最后一部分，安全地容纳好几个tb大小的数据库以实现可伸缩性。特别是在有相当多的选择的情况下，我很惊讶有这么多的人担心PostgreSQL在伸缩性方面是非常有限的。也许确实曾经是这样(我在2011年开始使用Postgres)，但在2018年，事情已经相当稳固了——所以请继续读下去，看看如何轻松地处理tb。
标准Postgres设施
如果你不喜欢出汗太多,然后做一些开拓性的尺度。应该是最安全的方法坚持证明Postgres的开箱即用的特性,首先我建议看看以下关键词和一些简短的解释,也许是你需要的一切。
轻量级/特殊用途索引
对于支持数百个奇怪查询的复杂OLTP系统，索引实际上占用的磁盘空间比保存数据的表文件要多得多，这是很常见的。为了改善这一点(特别是对于不经常使用的索引)，可以通过适当使用部分、BRIN、GIN甚至少量实验性BLOOM索引来大幅减小索引的大小。总共支持7种不同的索引类型……但是大多数人只知道和使用默认的b -树——这在多tb设置中是个大错误!
部分索引只允许数据的一个子集——例如，在销售系统中，我们可能对快速访问状态为“FINISHED”的订单不感兴趣(一些夜间报告通常会处理这个问题，它们可能会花很多时间)，那么我们为什么要索引这样的行呢?
GIN可能是最知名的非默认索引类型，它已经存在很长时间了(全文搜索)，简而言之，它非常适合索引有很多重复值的列——考虑各种状态或老的Mr/Mrs/Miss。GIN只将每个惟一的列值存储一次，对于默认的b树，您将拥有1,000,000个叶节点，其中包含整数“1”。
BRIN(block-range又名min-max指数)另一方面,是新的和非常不同——它是一种有损压缩指数与一个很小的磁盘占用,并不是所有的列值建立索引,但只有最大和最小值的行范围(1 MB的默认表)——这仍然很有效有序的值,例如适合时间序列数据或其他“日志”类型的表。
BLOOM可能有点奇怪，但如果你能找到一个很好的用例(“位图/矩阵搜索”)，它的效率会比传统索引高出20倍——这里有一个看起来过于抽象的用例。
但是为什么我要把索引这个有点不太原创的话题放在列表的最前面呢?因为这个解决方案的最大优点是您不需要更改任何应用程序—DBA可以让它轻松地在幕后工作，就像一次性工作一样!完美的。
表分区
Postgres分区已经15年我相信……但“弄脏你的手”的方式——一个不得不做一些附加的低级管理分区,添加检查约束和直接插入行纠正子表,或路由通过父表插入触发器。所有这些都是从Postgres version 10开始的历史，有声明式分区，并且在version 11中变得更好，在版本11中，功能可以被称为功能完整的功能，支持主键和外键。
但何苦呢?分区的优点是:它可以干净地分离“冷数据”和“热数据”,这给了我们一些不错的选项如使用VACUUM FULL最大限度压缩旧数据或把它放在另一个媒体(参见下面的“表空间”)作为一个副作用较小的索引,以少得多的“shared_buffers”空间所以我们有更多的空间数据。对于统一访问的数据场景(通过名称/电子邮件/散列)，这种影响最大，大型索引的所有部分仍然需要遍历/读取/缓存，但实际使用的只是其中很小的一部分。同样类似于索引，在良好的应用程序条件下，dba无需在后台更改任何代码就可以实现分区。
表空间
如上所述——借助表空间可以有选择地将表/索引移动到不同的磁盘介质。这里可以实现不同的目标之一——为了节省资金，使用较慢/负担得起的磁盘分区来处理“冷”数据,只在快速/昂贵的媒体上保存最新/重要的数据,对重复次数很多的数据使用一些特殊的压缩文件系统，对大量非持久性数据使用一些网络共享甚至远程节点上的内存文件系统——有一些选项。而且表空间的管理实际上也非常简单，由于完全锁定，只有在实时操作期间传输现有的表/索引才会有问题。
最大限度地使用多进程特性
从Postgres 9.6开始，可以对数据的一些常见操作进行并行化。在Postgres 10/11中，默认情况下也启用了相关参数“max_parallel_workers_per_gather”，其值为2，因此是max。使用了2个后台进程。对于“大数据”用例，增加更多(以及一些相关参数)是有意义的。此外，对并行化操作的支持也会随着每一个新的主要发行版的增加而增加。例如，即将发布的version 11现在可以执行并行散列连接、索引扫描和UNION-s。
使用副本进行查询负载平衡
现在我们已经走出了“单节点”或“扩展”的领域……但是考虑到非常合理的硬件价格和可用的Postgres集群管理软件(Patroni是我们的最爱)，它不仅适用于更大的“商店”，而且应该适用于每个人。当然，这种扩展只能在主要是读取数据的情况下使用……按照目前(以及未来几年)的官方规定，一个接受写操作的集群中只能有一个“主/主”节点。此外，根据您选择的技术栈，您可能需要处理一些技术细节(特别是连接路由)，但实际上，Postgres 10确实在驱动程序级别增加了对多主机用例的支持——因此电池也包括在内!更多信息请看这里。同样，从Postgres 9.6开始，副本可以在“镜像”模式下运行，这样您选择哪个节点运行就无关紧要了!不过，作为一个友好的警告——这只能在读取查询是纯OLTP的情况下工作，也就是说非常快。
有一些妥协的方法
所以现在我们已经完成了传统的事情…但是如果你准备走一条非常规的路
也许对您的应用程序做一些细微的调整，或者尝试一些命名有趣的扩展，您可能会从单节点硬件中挤出最后一滴性能。我的意思是:
混合/外部表
我称之为混合表,实际上是基于Postgres的优秀的MED SQL标准的实现也被称为外部数据包装（FDW）,基本上,他们看起来像正常Postgres表读取查询,但数据可能存在或管道从字面上任何地方——它可能是来自Twitter、LDAP或Amazon S3,看到疯狂的数据源支持的完整列表。实际上，最常用的外部数据包装程序可能是使正常(正确格式化的)文件看起来像表，例如将服务器日志公开为表，以便更容易监视。
你可能会问扩展部分在哪里?FDW方法工作得非常好，因为它能够通过使用一些聪明的文件格式或仅仅压缩来减少数据量，这通常会减少数据大小10-20x，以便数据适合节点!这对于“冷”数据非常有效，为“热”数据的真实表留下更多的磁盘空间/缓存可用。由于Postgres 10，它也很容易实现——这里是示例代码。
另一个非常有前途的用例是使用柱状数据存储格式(ORC)—查看“c_store”扩展项目以获得更多信息。它特别适合帮助扩展大型数据仓库，其中的表可以小到10倍，查询速度可以快到100%。
为什么我没有把这个特性添加到上面的“标准Postgres设施”部分，尽管外部数据包装基础设施已经牢固地构建到Postgres中?好的，缺点是您通常不能通过SQL更改数据和添加索引或约束，因此它的使用有一定的限制。
外表继承，即分片!（Foreign table inheritance a.k.a. sharding!）
与上一点基本相同——但是引入了表分区并将子表驻留在远程节点上!数据可以被植入到附近的Postgres服务器，并根据需要通过网络自动提取。实际上，它们不必是Postgres表!它很可能是MySQL、Oracle或MS SQL任何其他流行的服务器，对某些查询子集工作得很好。这有多酷?虽然只有“postgres_fdw”支持所有的写操作、事务和聪明的过滤器下推，所以通过网络传递的数据量最小化，但是可以从postgres_fdw到postgres的交互中得到最好的结果。
terabyte-hunting快乐!
原文：https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/
本文：http://jiagoushi.pro/node/1180
讨论：请加入知识星球【首席架构师圈】或者小号【jiagoushi_pro】
- 28 次浏览
【PostgreSQL 架构】PostgreSQL 10和逻辑复制-概述
- 24 次浏览
【PostgreSQL 架构】PostgreSQL 11和即时编译查询
PostgreSQL 11正在酝酿之中，即将发布。同时，使用您自己的应用程序对其进行测试是确保社区在零点发行之前捕获所有剩余错误的好方法。
下一个PostgreSQL版本的重大变化之一是Andres Freund在查询执行器引擎上的工作成果。 Andres已经在系统的这一部分上工作了一段时间，在下一发行版中，我们将看到执行引擎中的一个新组件：一个JIT表达式编译器！
基准和TPC-H
我喜欢在Citus Data进行工程工作以通过Citus扩展扩展PostgreSQL的一件事就是，我可以运行基准测试！基准测试是一个很好的工具，可以显示性能改进可带来哪些好处。当前，JIT表达式编译器在以下情况下效果最佳：
- 该查询包含多个复杂的表达式，例如聚合。
- 该查询读取了大量数据，但没有IO资源短缺。
- 该查询非常复杂，以至于需要花费大量的JIT精力。
通过主键代理ID获取某些信息的查询不太适合查看PostgreSQL中新的JIT基础结构所提供的改进。
TPC-H基准测试第1季度查询可以很好地评估新执行程序堆栈的影响，因此我们在这里使用它。
基准测试的规范可在137页的名为TPC Benchmark™H的PDF文档中找到。该规范中的每个查询都附带一个业务问题，因此请参阅第一季度
定价摘要报告查询（Q1）
此查询报告已开票，发货和退回的业务量。
定价摘要报告查询提供了给定日期发货的所有订单项的摘要定价报告。该日期位于数据库中包含的最晚发货日期的60-120天之内。该查询列出了扩展价格，折扣扩展价格，折扣扩展价格加税，平均数量，平均扩展价格和平均折扣的总计。这些聚合按RETURNFLAG和LINESTATUS分组，并按RETURNFLAG和LINESTATUS的升序排列。包括每个组中的行项目数的计数。
这就是SQL中的样子：
select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval ':1' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus :n -1 ;
此外，该规范还提供有关查询的注释：
注释：1998-12-01是数据库填充中定义的最高可能的发货日期。 （这是ENDDATE-30）。 该查询将包括该日期之前减去DELTA天之前发货的所有订单项。 目的是选择DELTA，以便扫描表中95％至97％的行。
为了使查询有资格显示新的PostgreSQL表达式以执行JIT编译器，我们将选择适合内存的比例因子。
结果
选择10的比例因子时，我们得到的数据库大小为22GB，包括创建的索引。 此处使用的完整架构在tpch-schema.sql上可用，而索引在tpch-pkeys.sql和tpch-index.sql上。
在我的测试中，执行TPCH Q1查询时，PostgreSQL 11比PostgreSQL 10快29.31％。 在循环中运行查询10分钟时，当PostgreSQL 10仅执行同一查询时，它允许PostgreSQL 11执行30次。 21次。
如我们所见，PostgreSQL 10中的Andres工作已经对该查询产生了巨大影响。在此版本中，对执行程序的表达式评估进行了全面修订，以考虑到CPU缓存行和指令管道。在此基准测试中，我们选择在PostgreSQL中禁用并行查询，以便评估主要由新执行程序导致的改进。 PostgreSQL 10 then 11中的并行支持能够大大增强我们在此看到的查询时间！
在PostgreSQL 11中，由于在查询计划时使用LLVM编译器基础结构，SQL表达式已转换为机器代码，这对查询性能产生了另一个非常好的影响！
工具
基准测试规范有两个文件可用：
llvm-q1-infra.ini定义了用于运行此测试的AWS EC2实例。
- 在这里您可以看到我们选择了c5.4xlarge实例来托管我们的PostgreSQL数据库。它们每个都有30GB的RAM，因此我们的22GB数据集和索引非常适合RAM。
- 另外，我们使用http://apt.postgresql.org中的软件包选择了debian操作系统，该软件包提供了我们在此处一直使用的PostgreSQL 11开发快照。
llvm-q1-schedule.ini定义了我们的基准计划，这在这里非常简单：
[schedule]
full = initdb, single-user-stream, multi-user-stream
- 在initdb阶段，在8个并发进程中加载比例因子10的数据，每个进程一次执行一个步骤，考虑到我们将工作负载分为10个子进程。
- 我们在这里使用TPC-H s语。另外，在我研究的PostgreSQL的TPC-H实现中，我增加了对直接加载机制的支持，这意味着dbgen工具连接到数据库服务器并使用COPY协议。
- 然后执行一个单用户流，该流包括在客户端的单个CPU上运行尽可能多的查询，并持续10分钟。
- 然后执行一个多用户流，该流包含从所有8个CPU并行运行尽可能多的查询，并持续10分钟。
此处使用的基准测试工具是“开源”，可从https://github.com/dimitri/tpch-citus免费获得。这是一个简单的应用程序，可以自动在动态的AWS EC2基础架构中运行TPCH。
这个想法是，在创建几个配置文件后，可以在多个系统上并行驱动一个完整的基准测试，并在合并的数据库中检索结果以供以后分析。
此外，该项目还包括适用于PostgreSQL的TPCH C代码版本，并使用COPY协议实现直接加载。然后，该项目使用dbgen工具生成数据，并使用qgen工具为每个客户端根据规范生成新的查询流。
期待未来的Postgres
PostgreSQL 11引入了一个新的PostgreSQL执行引擎，借助LLVM框架，该引擎将您的SQL代码编译为机器代码。对于足够昂贵的查询（遍历许多行并一次又一次地计算表达式的查询），其好处可能是巨大的！
为了帮助PostgreSQL实现版本11的最佳发行，请考虑在测试和CI环境中使用beta版本，并报告您可能会发现的所有错误或性能下降，并通过一种简便的方法来再现它们。有关声明和如何报告相关发现的详细信息，请参见PostgreSQL 10.5和11 Beta 3 Released。
在我们的基准测试中，PostgreSQL 11 JIT是一项很棒的技术，它提供了高达29.31％的速度改进，在使用PostgreSQL 10时以20.5s的比例因子10执行TPC-H Q1而不是29s。
在Citus，我们几个月来一直在忙于针对PostgreSQL测试Citus扩展。因为Citus是Postgres的纯粹扩展，而不是fork，这意味着当时候到来时，您应该能够升级以获得Postgres 11的所有新优势，以帮助您保持扩展。
原文：https://www.citusdata.com/blog/2018/09/11/postgresql-11-just-in-time/
本文：http://jiagoushi.pro/node/924
讨论:请加入知识星球或者微信圈子【首席架构师圈】
- 24 次浏览
【PostgreSQL 架构】PostgreSQL的大量处理开发
- 8 次浏览
【PostgreSQL】PostgreSQL CDC：如何设置实时同步
QQ群
视频号
微信
微信公众号
知识星球
PostgreSQL是使用最广泛的开源关系数据库之一。其全面的查询层和符合技术标准的特性使其成为OLTP工作负载，数据仓库和分析用例等各种用例的最爱。在典型的体系结构中，组织将有一个关系数据库来处理事务负载，然后有一个单独的数据仓库来执行所有分析用例和汇总报告。这里的挑战是确保数据仓库具有事务数据库中存在的最新数据版本。通常有时间紧迫的报告要求，无法在这些数据库之间进行每小时或每天的批处理同步。使用基于变更数据捕获范例的实例之间的连续同步来处理此类需求。这篇文章是关于PostgreSQL CDC及其实现方法的。
在本文中，您将学习：
- 实现PostgreSQL CDC的方法
- 方法1 –使用自定义代码的Postgres CDC
- 方法1 –使用审核触发器
- 方法2：使用Postgres逻辑解码
- 方法3：使用时间戳列
- 方法2 –实施PostgreSQL CDC的简便方法
实施PostgreSQL变化数据捕获（CDC）的方法
有很多实现Postgres CDC的方法。 该博客讨论了以下两种方法：方法1：构建自定义代码以实现PostgreSQL CDC
我们将在此博客中讨论以下方法：
- 使用Postgres审核触发器
- 使用Postgres逻辑解码
- 使用时间戳列
方法2：使用完全自动化的数据管道解决方案，如Hevo Data
尽管构建自定义代码可能看起来是非常有利可图的选择，但仍有许多障碍，挑战和限制使其变得很费力。 实施类似Hevo的解决方案有助于实现PostgreSQL CDC到您选择的任何目标，而无需编写任何代码。
Postgres使用自定义代码更改数据捕获
方法1：使用Postgres审核触发器(Audit Triggers)
基于触发器的方法涉及在数据库上创建审核触发器，以捕获与INSERT，UPDATE和DELETE方法有关的所有事件。这种方法的优点是，一切都发生在SQL级别，并且开发人员只需要读取一个包含所有审核日志的单独表。
要在Postgres表中创建触发器，请执行以下命令。
SELECT audit.audit_table('target_table');
该语句将以具有访问审核模式权限的用户角色执行。写入表的应用程序一定不能使用超级用户角色连接到数据库。
如果要求不需要跟踪不经常更改的字段，则可以在特定字段的情况下关闭这些审核日志。
然后，我们有了一个捕获审计日志的表。开发人员仍然需要编写程序来读取和解析这些日志并将其推送到目标数据库中。此逻辑的复杂性还将取决于将数据推送到的目标数据库或数据仓库。
在PostgreSQL CDC中使用Postgres审计触发器的局限性
这种方法的缺点是触发器会影响数据库的性能。避免这种性能下降的常见做法是拥有一个单独的表来跟踪主表，并在第二个表上具有触发器。可以使用Postgres逻辑复制功能完成主表和辅助表之间的同步。
方法2：使用Postgres逻辑解码( Logical Decoding)
逻辑解码使用预写日志的内容来创建数据库中发生的活动的日志。预写日志是内部日志，描述了存储级别上的数据库更改。这种方法的优点是它不会以任何方式影响数据库的性能。
该方法基于输出插件的安装而工作。为了启用逻辑解码，需要在Postgres配置中设置以下参数。
wal_level = logical max_replication_slots = 10
设置完这些参数后，执行以下命令以创建逻辑解码槽。
SELECT * FROM pg_create_logical_replication_slot('slot_repl', 'decode_test');这将确保对表的每次更新都将创建可在视图选择语句中访问的审计事件。 逻辑解码支持两种选择语句来访问这些事件。 以下命令可用于窥视事件，
SELECT * FROM pg_logical_slot_peek_changes('slot_repl', NULL, NULL);要使用或获取结果，请使用以下命令。
SELECT * FROM pg_logical_slot_get_changes('slot_repl', NULL, NULL);“消费”和“窥视”之间的区别在于，第二条命令在消费事件后便将其删除。 让我们尝试通过一个例子来理解这一点。
假设有一个学生表，我们在该表中插入了一个条目。
Insert into students (id, name, age) values (1, ‘Peter’, '30');
一旦执行了peek命令，结果将如下所示。
SELECT * FROM pg_logical_slot_peek_changes('slot_repl', NULL, NULL);0/16CEE88 | 102 | BEGIN 102 0/16CEE88 | 102 | table public.students: INSERT: id[integer]:1 name[character varying]:' Peter' age[integer]:30 0/16CEFA0 | 102 | COMMIT 102
同样，如果我们执行相同的命令，您仍然会看到相同的审核事件。
SELECT * FROM pg_logical_slot_peek_changes('slot_repl', NULL, NULL);0/16CEE88 | 102 | BEGIN 102 0/16CEE88 | 102 | table public.students: INSERT: id[integer]:1 name[character varying]:' Peter' age[integer]:30 0/16CEFA0 | 102 | COMMIT 102
现在执行get命令。
SELECT * FROM pg_logical_slot_get_changes('slot_repl', NULL, NULL);0/16CEE88 | 102 | BEGIN 102 0/16CEE88 | 102 | table public.students: INSERT: id[integer]:1 name[character varying]:' Peter' age[integer]:30 0/16CEFA0 | 102 | COMMIT 102
结果是一样的。 但是，如果再次执行相同的命令或peek命令，则会发现结果为零。
SELECT * FROM pg_logical_slot_get_changes('slot_repl', NULL, NULL);
(0 rows)这意味着当执行get命令时，将提供结果并删除结果，从而大大增强了我们编写使用这些事件创建表副本的逻辑的能力。
Postgres还支持使用输出插件-基本上是c程序，可以基于INSERT，UPDATE和DELETE事件执行，并将事件转换为更加用户友好的格式。 Wal2json是一个这样的插件，可以以JSON格式输出这些事件。大多数Degrecium之类的Postgres CDC附件都广泛使用了此插件。
这里要注意的一点是，这些事件不会捕获表的创建和修改步骤。
对Postgres CDC使用Postgres逻辑解码的缺点
同样，在这种情况下，开发人员需要编写复杂的逻辑来处理这些事件，然后将其转换为目标数据库的语句。根据您要解决的用例，这可能会增加项目时间表。
方法3：使用时间戳列
Postgres引擎提供了以上两种方法来实现CDC。如果您在表中具有timestamp列的灵活性，那么还有一个稍微复杂的自定义方法。这意味着开发人员将需要定期查询表并监视对timestamp列的更改。当检测到更改时，脚本可以创建适当的数据库语句以将这些更改写入目标数据库。但是，这种方法需要大量的精力，并且需要开发人员花费大量的时间和精力。
简而言之，即使Postgres提供了通过触发器，逻辑解码或通过自定义逻辑的连续同步支持，开发人员仍然需要捕获这些事件并将其转换为目标数据库。根据用例，将需要为不同的目标数据库专门编写此逻辑。一种替代方法是使用像Hevo这样的基于云的服务，该服务可以使用Postgres CDC将数据连续同步到大多数目的地。
设置POSTGRESQL更改数据捕获的简便方法
Hevo是一个完全托管的数据集成平台，可以帮助您将数据从PostgreSQL实时加载到您选择的任何目标，而无需编写任何代码。
Hevo开箱即用地支持Postgres CDC，并提供了一个点击式视觉界面来启用更改数据捕获。 您只需要将Hevo指向Postgres数据库中的auto-crementing列或timestamp列即可。
Hevo将完成所有艰苦的工作，并确保您的Postgres数据始终在目标数据库/数据仓库中是最新的。
本文：http://jiagoushi.pro/node/1041
讨论：请加入知识星球【首席架构师圈】或者小号【cea_csa_cto】
- 1162 次浏览
【PostgreSQL】PostgreSQL变更数据捕获（CDC）：完整指南
QQ群
视频号
微信
微信公众号
知识星球
本指南帮助您开始将CDC与PostgreSQL数据库系统一起使用。
目录
- 介绍
- PostgreSQL中使用触发器的变更数据捕获
- PostgreSQL中使用查询进行变更数据捕获
- PostgreSQL中带有逻辑复制的变更数据捕获
- 总结
介绍
PostgreSQL是一个著名的开源数据库管理系统，目前正在众多企业生产。在典型的设置中，PostgreSQL管理应用程序的事务数据，例如电子商务商店中的产品，并集成第三方数据系统用于其他目的，例如用于分析的数据仓库、用于报告的BI工具等。
连接PostgreSQL与其他数据存储的传统方法是基于批处理的。数据管道时不时地从PostgreSQL中提取所有数据并发送到下游的数据存储中，这不仅效率低下，而且容易出错。
变更数据捕获（CDC）是一种现代的替代方案，可以从PostgreSQL中实时提取记录级变更事件（插入、更新和删除）。变更数据捕获的主要好处是：
- CDC实时捕获更改事件，保持下游系统（如数据仓库）始终与PostgreSQL同步，并启用完全事件驱动的数据体系结构。
- 使用CDC可以减少PostgreSQL的负载，因为只处理相关信息，例如更改。
- CDC支持高效地实现需要访问PostgreSQL更改事件（如审核或更改日志）的用例，而无需修改应用程序代码。
在本文中，我们将提供一个完整的介绍，介绍如何将变更数据捕获与PostgreSQL结合使用。我们介绍了实现更改数据捕获的三种常用方法：触发器、查询和逻辑复制。虽然每种方法都有各自的优缺点，但在DataCate，我们最喜欢的是使用逻辑复制的基于日志的CDC。
在PostgreSQL中使用触发器更改数据捕获
使用PostgreSQL的触发器特性，我们可以侦听感兴趣的表中发生的所有插入、更新和删除事件，并为每个事件将一行插入第二个表中，从而构建变更日志。
PostgreSQL社区提供了一个通用触发器函数（代码），该函数支持PostgreSQL 9.1版及更高版本，并将所有更改事件存储在表audit.logged\u actions中。如果希望为表public.users启用基于触发器的更改数据捕获，则可以运行以下SQL语句：
SELECT audit.audit_table('public.users');为表public.users启用基于触发器的CDC。
CDC的这种方法只在PostgreSQL中存储捕获的事件。如果要将更改事件同步到其他数据系统（如数据仓库），则必须重复查询包含更改事件的PostgreSQL表（此处为audit.logged\u操作），这会增加实现的复杂性。
让我们比较一下在PostgreSQL中使用触发器实现更改数据捕获的优缺点：
- 可以立即捕获更改，从而实现更改事件的实时处理。
- 触发器可以捕获所有事件类型：插入、更新和删除。
- 默认情况下，此处使用的PostgreSQL触发器函数将有用的元数据添加到事件中，例如导致更改的语句、事务ID或会话用户名。
- 触发器会增加原始语句的执行时间，从而影响PostgreSQL的性能。
- 触发器需要更改PostgreSQL数据库。
- 如果更改事件应同步到同一PostgreSQL数据库以外的数据存储，则需要设置单独的数据管道，该管道轮询触发器函数（此处为audit.logged\u actions）填充的表。
- 创建和管理触发器会导致额外的操作复杂性。
在PostgreSQL中使用查询更改数据捕获
使用PostgreSQL实现更改数据捕获的第二种方法是基于查询的。
如果受监视数据库表的模式具有一个timestamp列，该列指示上一次更改行的时间，那么我们可以使用该列重复查询PostgreSQL，并请求自上次查询PostgreSQL以来已修改的所有记录。假设有一个名为public.users的表和一个名为updated\u at的时间戳列，这样的查询可以按如下方式实现：
SELECT * FROM public.users WHERE updated_at > 'TIMESTAMP_LAST_QUERY';示例：提取自上次访问时间以来已修改的所有用户的SQL查询（请替换TIMESTAMP\u last\u查询）。
请注意，基于查询的CDC无法捕获删除（除非使用软删除），但仅限于插入和更新事件。
- 如果模式具有指示行修改时间的timestamp列，则可以实现基于查询的CDC，而无需对PostgreSQL进行任何更改。
- 基于查询的CDC实现使用查询层提取数据，这给PostgreSQL带来了额外的负载。
- 基于查询的CDC需要对受监视表（此处为public.users）进行周期性轮询，如果数据很少更改，这将浪费资源。
- 基于查询的CDC需要一个列（此处为updated\u at）来跟踪记录上次修改的时间。
- 基于查询的CDC无法捕获删除事件（除非应用程序使用软删除）。
在PostgreSQL中使用逻辑复制更改数据捕获
自9.4版以来，PostgreSQL提供了逻辑复制，以便在可能不同的物理机器上的不同PostgreSQL实例之间高效、安全地复制数据。从技术上讲，它是一个磁盘上的预写日志，它保存更改PostgreSQL数据库数据的所有事件，例如插入、更新和删除。
PostgreSQL使用具有发布服务器和订阅服务器的订阅模型来实现逻辑复制。为了实现变更数据捕获，我们可以使用感兴趣的数据库作为发布者并订阅其日志。
虽然许多数据库系统可能已经使用复制，但默认情况下不启用复制。您可以通过对配置文件postgresql.conf引入以下更改来启用逻辑复制。
wal_level = logical
在postgresql.conf中启用逻辑复制。
在下一步中，您需要修改配置文件pga\u hba.conf以允许复制（请参阅PostgreSQL文档了解各个配置）：
host all repuser 0.0.0.0/0 md5
允许在pga\u hba.conf中进行逻辑复制。
假设您要从public.users表中捕获更改。您可以通过创建新发布为该表启用CDC，如下所示：
CREATE PUBLICATION newpub FOR TABLE public.users;为表public.users创建发布。
在下一步中，您可以开始订阅此出版物。订阅从初始快照开始，然后复制所有增量更改。如果要使用另一个PostgreSQL实例中的事件，可以按如下方式创建订阅：
CREATE SUBSCRIPTION newsub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION newpub;为使用更改创建订阅。
从技术上讲，逻辑复制是通过逻辑解码插件实现的。如果您使用的PostgreSQL版本早于10，则需要在PostgreSQL数据库中手动安装插件，例如wal2json或decoderbufs。从版本10开始，PostgreSQL默认提供插件pgoutput。
对于基于日志的变更数据捕获的技术实现，我们强烈建议使用现有的开源项目之一，例如Debezium。DataCate的PostgreSQL源连接器基于Debezium。
据我们所知，大多数托管PostgreSQL服务都提供对逻辑复制的支持，例如AWS RDS、Google Cloud SQL或Azure数据库。
下表显示了使用PostgreSQL的逻辑复制实现CDC的优缺点：
- 基于日志的CDC支持实时捕获事件驱动的数据更改。下游应用程序始终可以访问来自PostgreSQL的最新数据。
- 基于日志的CDC可以检测PostgreSQL中的所有更改事件类型：插入、更新和删除。
- 通过逻辑复制使用事件归结为直接访问文件系统，这不会影响PostgreSQL数据库的性能。
- 非常旧版本的PostgreSQL（早于9.4）不支持逻辑复制。
总结
在比较使用PostgreSQL实现变更数据捕获的三种方法时，使用逻辑复制显然是胜利者。它不仅高效、实时捕获所有事件类型而不损害PostgreSQL数据库的性能，而且广泛可用（无论您使用的是自管理还是托管PostgreSQL安装），并且在不更改数据库模式的情况下适用。
CDC连接器通常比传统的SELECT*FROM表更复杂；查询。在DataCate中，我们提供用于更改数据捕获的即插即用连接器，它使您能够在几分钟内使用PostgreSQL设置基于日志的CDC，并降低操作复杂性。试试看！
- 129 次浏览
【PostgreSQL】PostgreSQL在性能和可伸缩性方面是否与SQL SERVER匹配？
数据库系统的性能和可伸缩性可以对任何项目产生重大影响。在许多情况下，开发人员必须从一个数据库系统迁移到另一个数据库系统，以提高数据库密集型应用程序的性能和操作速度。不仅如此，每个应用程序都会进行修改，以获得更好的用户体验，并引入新功能，对数据库存储的需求也会大大增加。如果您的应用程序的数据库系统没有提供健壮的可伸缩性功能，并且如果随着负载的增加性能受到影响，那么应用程序的受欢迎程度将受到影响。今天，让我们来比较两个最流行的数据库系统MS SQL Server和PostgreSQL的性能和可伸缩性因素。
性能-
并发性-
并发性是决定数据库系统性能的一个重要因素。并发性是指多个进程可以同时访问和修改共享数据的特性。在每一个应用程序中，某些数据被运行的各种并发进程共享，数据的这种并发性可以是健壮的，数据库和应用程序的性能将是更快、更完美的。SQL Server的并发性不足，您肯定会在日志中得到各种锁定、阻止和死锁的报告。这会导致数据管理不当，应用程序的进程变得非常缓慢。相比之下，PostgreSQL具有更好的并发管理系统，并且由于其优化MVCC的特性，死锁的可能性更小。
分区-
与并发性一样，分区也是数据库系统的一大特性。分区是将大表分成小部分的过程。随着数据库大小，特别是表大小的增长，分区是很重要的。这有助于在访问分数数据而不是整个大表时提高性能。从可伸缩性的角度来看，分区也很重要。随着应用程序规模的增大，数据库会变得很大，如果不进行拆分，数据库会变得更大，访问数据将需要很多时间。至于SQL Server，有一个合适的分区特性，但是你必须购买这个特性作为附加组件，而在PostgreSQL中，你可以以更低的价格和更高的效率获得它。
索引-
技术更新的速度比以往任何时候都快。在这种情况下，SQL Server在几年后发布新版本的方法已经过时。PostgreSQL定期发布更新版本，并紧跟潮流，提供更快的性能。接下来，PostgreSQL的可索引函数特性将把数据库的性能提升到另一个级别。不仅如此，PostgreSQL还支持模块或扩展，您可以做很多SQL server无法做到的事情。由于缺乏正确的索引实现是SQL server，它们省略了一个最常用的变量系统array。
可扩展性-
数据库系统的可伸缩性直接取决于数据的压缩能力。理想情况下，数据库系统必须具有先进的现成的压缩技术。在某些数据库系统中，开发人员必须手动压缩，不仅耗时而且效率低下。MS SQL Server提供了开箱即用的压缩，但您必须手动实现它。另一方面，PostgreSQL免费提供，整个过程是自动的。
平台-
在当今世界，应用程序必须是通用的。这意味着每个操作系统的人，无论是Windows、Linux、Mac还是其他什么，都应该可以访问应用程序。数据库系统也应该如此，这样开发人员可以根据自己的选择在任何操作系统上工作。由于SQL Server是微软的产品，它只能在Windows上运行，而拥有Mac或Linux的开发人员不能在它上工作。这是开发可伸缩性和灵活性方面的一个主要缺点。值得庆幸的是，PostgreSQL在每一个平台上都能工作，为开发人员开辟了一条新的途径。这也是将数据库从SQL Server迁移到PostgreSQL的主要原因之一，因为各种商业公司的开发人员都在基于Mac的计算机上开发应用程序。JSON和JavaScript统治着web世界，PostgreSQL支持JSON。您可以正确地同步客户机、服务器和数据库，但SQL server仍然停留在XML上。甚至PostgreSQL的数据类型也优于SQL server，克服了所有缺点，使PostgreSQL具有更好的性能和可扩展性。
最终裁决-
PostgreSQL不仅与SQL Server的性能或可伸缩性相匹配，而且在多个参数上都明显优于sqlserver。就企业级而言，它的定价比SQL Server好，而且在PostgreSQL中有一些特性是免费的，而sqlserver对它们收费很高。
本文：http://jiagoushi.pro/does-postgresql-match-sql-server-terms-performance-and-scalability
讨论：请加入知识星球【首席架构师圈】或者微信小号【jiagoushi_pro】
- 31 次浏览
【PostgreSQL】SQL还是NoSQL？为什么不同时使用（与PostgreSQL一起使用）？
QQ群
视频号
微信
微信公众号
知识星球
对于任何一个开始新项目的开发人员来说，这都是一个艰难的决定。您应该将数据存储在标准的、经过时间测试的SQL数据库中，还是使用较新的基于NoSQL文档的数据库？这个看似简单的决定实际上可以决定你的项目的成败。正确选择并构建好数据结构，你就可以顺利进入生产阶段，看着你的应用程序起飞。如果选择错误，你可能会在应用程序问世之前做噩梦（甚至可能会进行一些重大重写）。
简洁与强大
SQL和NoSQL解决方案都存在折衷。通常，开始使用NoSQL数据结构更容易，尤其是在数据复杂或层次结构时。您只需从前端代码中获取一个JSON数据对象，并将其放入数据库中即可完成操作。但以后，当您需要访问该数据来回答一些基本的业务问题时，这将更加困难。SQL解决方案使收集数据和得出结论变得更加容易。让我们看一个例子：
每天我都会跟踪我吃的食物，以及每种食物中的卡路里数：
| Day | Food Item | Calories | Meal |
|---|---|---|---|
| 01 Jan | Apple | 72 | Breakfast |
| 01 Jan | Oatmeal | 146 | Breakfast |
| 01 Jan | Sandwich | 445 | Lunch |
| 01 Jan | Chips | 280 | Lunch |
| 01 Jan | Cookie | 108 | Lunch |
| 01 Jan | Mixed Nuts | 175 | Snack |
| 01 Jan | Pasta/Sauce | 380 | Dinner |
| 01 Jan | Garlic Bread | 200 | Dinner |
| 01 Jan | Broccoli | 32 | Dinner |
我还跟踪我喝的水的杯数以及何时喝：
| Day | Time | Cups |
|---|---|---|
| Jan 01 | 08:15 | 1 |
| Jan 01 | 09:31 | 1 |
| Jan 01 | 10:42 | 2 |
| Jan 01 | 12:07 | 2 |
| Jan 01 | 14:58 | 1 |
| Jan 01 | 17:15 | 1 |
| Jan 01 | 18:40 | 1 |
| Jan 01 | 19:05 | 1 |
最后，我跟踪我的锻炼：
| Day | Time | Duration | Exercise |
|---|---|---|---|
| Jan 01 | 11:02 | 0.5 | Walking |
| Jan 02 | 09:44 | 0.75 | Bicycling |
| Jan 02 | 17:00 | 0.25 | Walking |
对于每一天，我还跟踪我当前的体重以及当天的任何笔记：
| Day | Weight | Notes |
|---|---|---|
| Jan 01 | 172.6 | This new diet is awesome! |
| Jan 14 | 170.2 | Not sure all this is worth it. |
| Jan 22 | 169.8 | Jogged past a McDonald's today. It was hard. |
| Feb 01 | 168.0 | I feel better, but sure miss all that greasy food. |
收集所有数据
需要收集、存储、检索和稍后分析大量不同的数据。它的组织简单而容易，但记录的数量每天都有所不同。在任何一天，我可能没有或更多关于食物、水和锻炼的条目，我可能有零或一个关于体重和笔记的条目。
在我的应用程序中，我在一个页面上收集一天的所有数据，让我的用户更轻松。因此，我每天都会得到一个JSON对象，如下所示：
{
"date": "2022-01-01",
"weight": 172.6,
"notes": "This new diet is awesome!",
"food": [
{ "title": "Apple", "calories": 72, "meal": "Breakfast" },
{ "title": "Oatmeal", "calories": 146, "meal": "Breakfast" },
{ "title": "Sandwich", "calories": 445, "meal": "Lunch" },
{ "title": "Chips", "calories": 280, "meal": "Lunch" },
{ "title": "Cookie", "calories": 108, "meal": "Lunch" },
{ "title": "Mixed Nuts", "calories": 175, "meal": "Snack" },
{ "title": "Pasta/Sauce", "calories": 380, "meal": "Dinner" },
{ "title": "Garlic Bread", "calories": 200, "meal": "Dinner" },
{ "title": "Broccoli", "calories": 32, "meal": "Dinner" }
],
"water": [
{ "time": "08:15", "qty": 1 },
{ "time": "09:31", "qty": 1 },
{ "time": "10:42", "qty": 2 },
{ "time": "10:42", "qty": 2 },
{ "time": "12:07", "qty": 1 },
{ "time": "14:58", "qty": 1 },
{ "time": "17:15", "qty": 1 },
{ "time": "18:40", "qty": 1 },
{ "time": "19:05", "qty": 1 }
],
"exercise": [{ "time": "11:02", "duration": 0.5, "type": "Walking" }]
}
保存数据
一旦我们收集了一天的所有数据，我们就需要将其存储在数据库中。在NoSQL数据库中，这可能是一个非常简单的过程，因为我们只需为特定日期的特定用户创建一个记录（文档），然后将文档放入集合中，就完成了。使用SQL，我们有一些必须在其中工作的结构，在这种情况下，它看起来像4个独立的表：食物、水、锻炼和笔记。我们想在这里做4个单独的插入，每个表一个。如果我们没有特定表格的数据（比如今天没有记录锻炼），那么我们可以跳过该表格。
如果您使用SQL来存储这些数据，您可能希望在数据输入表单中输入每个表的数据时保存该表的数据（而不是等到输入完所有数据后再保存。）或者您可能希望创建一个数据库函数，该函数获取所有JSON数据，对其进行解析，并在单个事务中将其写入所有相关表。有很多方法可以处理这一问题，但只要这样说就足够了：这比将数据保存在NoSQL数据库中要复杂一些。
检索数据
如果我们想显示一天的所有数据，那基本上是一样的。使用NoSQL，您可以获取用户当天的数据，然后在应用程序中使用它。美好的使用SQL，我们需要查询4个表来获取所有数据（或者我们可以使用一个函数在一次调用中获取所有数据。
分析数据
现在我们已经保存了数据，并且可以检索和显示它，让我们使用它进行一些分析。让我们展示一张我在过去一个月里总共摄入了多少卡路里的图表。使用SQL，这是一项简单的任务：
select date, sum(calories) as total_calories from food_log group by date where user_id = 'xyz' and day between '2022-01-01' and '2022-01-31' order by date;
砰！完成！现在我们可以把这些结果发送到我们的绘图库，并把我的饮食习惯画得很漂亮。
但是，如果我们将这些数据存储在NoSQL中，它会变得更加复杂。我们需要：
- 为用户获取当月的所有数据
- 解析每天的数据以获得食物日志信息
- 每天循环并计算热量
- 将聚合数据发送到我们的绘图模块
如果这是我们要定期做的事情，那么计算每天的总热量并将其存储在当天的文档中是有意义的，这样我们就可以更快地获取数据。但这需要更多的前期工作，我们仍然需要提取每天的数据，并首先解析出卡路里总量。如果我们更新数据，我们仍然需要重新计算并更新总数。最终，我们会想在水和运动总量方面做到这一点。代码最终会变得越来越长、越来越复杂。
SQL和NoSQL在一起-FTW
让我们看看如何在同一个数据库中使用SQL的强大功能和NoSQL的易用性，使这一切变得更容易。我们将为每天的数据（为每个用户）创建一个表，并首先存储权重和注释等基本字段。然后，我们将在JSONB字段中抛出food_log、water_log和exercise_log字段。
CREATE TABLE calendar (
id uuid DEFAULT gen_random_uuid() NOT NULL,
date date,
user_id uuid NOT NULL,
weight numeric,
notes text,
food_log jsonb,
water_log jsonb,
exercise_log jsonb
);
-- (Optional) - create a foreign key relationship for the user_id field
ALTER TABLE ONLY calendar
ADD CONSTRAINT calendar_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);
现在让我们在表中插入一些数据。PostgreSQL同时提供JSON和JSONB字段，由于后者更容易被数据库优化，查询处理速度更快，所以我们几乎总是希望使用JSONB。我们将使用food_log、water_log和exercise_log的JSONB字段，并将从应用程序中获得的数据作为字符串直接转储到这些字段中：
insert into calendar
(date, user_id, weight, notes, food_log, water_log, exercise_log)
values
(
'2022-01-01',
'xyz',
172.6,
'This new diet is awesome!',
'[
{ "title": "Apple", "calories": 72, "meal": "Breakfast"},
{ "title": "Oatmeal", "calories": 146, "meal": "Breakfast"},
{ "title": "Sandwich", "calories": 445, "meal": "Lunch"},
{ "title": "Chips", "calories": 280, "meal": "Lunch"},
{ "title": "Cookie", "calories": 108, "meal": "Lunch"},
{ "title": "Mixed Nuts", "calories": 175, "meal": "Snack"},
{ "title": "Pasta/Sauce", "calories": 380, "meal": "Dinner"},
{ "title": "Garlic Bread", "calories": 200, "meal": "Dinner"},
{ "title": "Broccoli", "calories": 32, "meal": "Dinner"}
]',
'[
{"time": "08:15", "qty": 1},
{"time": "09:31", "qty": 1},
{"time": "10:42", "qty": 2},
{"time": "10:42", "qty": 2},
{"time": "12:07", "qty": 1},
{"time": "14:58", "qty": 1},
{"time": "17:15", "qty": 1},
{"time": "18:40", "qty": 1},
{"time": "19:05", "qty": 1}
]',
'[
{"time": "11:02", "duration": 0.5, "type": "Walking"}
]'
);
虽然这是一个很大的insert语句，但它肯定比在4个单独的表上进行insert要好。对于所有这些食物条目和水日志条目，我们必须在主表中创建1个条目，然后是9个食物日志条目、9个水日志条目和一个锻炼日志条目，总共20个数据库记录。我们把它包装成一张唱片。
但是我们如何查询这些数据？
很好，我们现在正在收集数据，而且很容易将数据插入数据库。编辑数据也不算太糟，因为我们只是将数据下载到客户端，根据需要更新JSON字段，然后将它们扔回数据库。不太难。但我如何查询这些数据？以前的任务怎么样？让我们展示一张我在过去一个月里总共摄入了多少卡路里的图表。
在这种情况下，该数据存储在日历表的food_log字段中。如果PostgreSQL能够将JSONB数组转换为单独的数据库记录（记录集）就好了。的确如此！jsonb_array_elements函数将为我们做这件事，允许创建一个简单的表，我们可以使用它来计算我们的热量摄入。
以下是一些SQL，用于将food_log数组转换为单独的输出记录：
select user_id, date, jsonb_array_elements(food_log)->>'title' as title, jsonb_array_elements(food_log)->'calories' as calories, jsonb_array_elements(food_log)->'meal' as meal from calendar where user_id = 'xyz' and date between '2022-01-01' and '2022-01-31';
这将返回一个如下所示的表：
| date | title | calories | meal |
|---|---|---|---|
| 2022-01-01 | Apple | 72 | Breakfast |
| 2022-01-01 | Oatmeal | 146 | Breakfast |
| 2022-01-01 | Sandwich | 445 | Lunch |
| 2022-01-01 | Chips | 280 | Lunch |
| 2022-01-01 | Cookie | 108 | Lunch |
| 2022-01-01 | Mixed Nuts | 175 | Snack |
| 2022-01-01 | Pasta/Sauce | 380 | Dinner |
| 2022-01-01 | Garlic Bread | 200 | Dinner |
| 2022-01-01 | Broccoli | 32 | Dinner |
需要注意的几点：
- jsonb_array_elements(food_log)->>'title' as title 作为title这将返回一个文本字段，因为->>运算符返回text
- jsonb_array_elements(food_log)->'calories' as calories ，这返回一个JSON对象，因为->运算符返回JSON
如果我们想对卡路里求和以获得一些总数，我们就不能有JSON对象，所以我们需要将其转换为更有用的对象，比如INTEGER：
- (jsonb_array_elements(food_log)->'calories')::INTEGER as calories，返回一个整数
现在我们不能只在这个问题上加上求和运算来得到一天的总热量。如果我们尝试这样做：
select date, sum((jsonb_array_elements(food_log)->'calories')::integer) as total_calories from calendar where user_id = 'xyz' and date between '2022-01-01' and '2022-01-31' group by date;
我们从PostgreSQL得到一个错误：运行sql查询失败：聚合函数调用不能包含返回集合的函数调用。
相反，我们需要将其视为一组构建块，其中我们的第一条SQL语句返回一个表：
select date, (jsonb_array_elements(food_log)->'calories')::integer as calories from calendar where user_id = 'xyz' and date between '2022-01-01' and '2022-01-31';
现在，我们可以使用“table”语句，在它周围插入一些（括号），然后查询它：
with data as
(
select
date,
(jsonb_array_elements(food_log)->'calories')::integer as calories
from calendar
where user_id = 'xyz'
and date between '2022-01-01' and '2022-01-31'
)
select date, sum(calories)
from data
group by date;
这正是我们想要的：
| date | sum |
|---|---|
| 2022-01-01 | 1838 |
如果我们在这个月剩下的日子里添加更多的数据，我们将拥有一个漂亮的图表所需的所有数据。
搜索数据
如果我们想回答这个问题：我上个月吃的大蒜面包里有多少卡路里？这些数据存储在日历表的food_log字段中。我们可以使用以前使用的相同类型的查询来“压平”food_log数据，以便进行搜索。
要获得我在一月份吃的每一件东西，我们可以使用：
select date, jsonb_array_elements(food_log)->>'title' as title, (jsonb_array_elements(food_log)->'calories')::integer as calories from calendar where user_id = 'xyz' and date between '2022-01-01' and '2022-01-31'
现在，为了搜索大蒜面包，我们可以在它周围放（括号）来制作一张“桌子”，然后搜索我们想要的物品：
with my_food as
(
select
date,
jsonb_array_elements(food_log)->>'title' as title,
(jsonb_array_elements(food_log)->'calories')::integer as calories
from calendar
where user_id = 'xyz'
and date between '2022-01-01' and '2022-01-31'
)
select
title,
calories
from my_food
where title = 'Garlic Bread';
这给了我们：
| title | calories |
|---|---|
| Garlic Bread | 200 |
结论
如果我们花一点时间研究PostgreSQL提供的JSON函数和运算符，我们可以将Postgres变成一个易于使用的NoSQL数据库，它仍然保留了SQL的所有功能。这为我们在数据库中存储来自应用程序代码的复杂JSON数据提供了一种非常简单的方法。然后，我们可以使用强大的SQL功能来分析数据，并在应用程序中显示这些数据。这是两全其美！
- 17 次浏览
【PostgreSQL】使用PostgreSQL和ApacheAGE实现多模型：图形数据库的实验
QQ群
视频号
微信
微信公众号
知识星球
让我们探索多种可用的数据库，并使用PostgreSQL和ApacheAGE进行图形模型实验
数据库环境
当谈到数据库时，对大多数人来说，第一个想到的是经典的关系数据库，或者他们经常提到的SQL数据库。在这些数据库中，数据存储在表中的行和列中，这些行和列可以通过外键相关联，外键是一个或多个链接回另一个表中行的主键（也称为ID）的列。
近五十年来，关系数据库一直是常态，但近年来，出现了其他类型的数据库来处理关系模型可能显示其局限性（主要是性能和可扩展性）的特定用例。
在过去的十五年里，已经创建了许多不同类型的数据库，如列数据库（例如AWS Dynamo DB或Apache Cassandra）、文档数据库（MongoDB或CouchDB）和图形数据库（Neo4J）。
创建这些新形式的数据库中的每一种都是为了更好地满足特定应用程序领域的需求，并提高传统关系系统的性能和可扩展性。
为什么要使用图形数据库
如果我们谈论存储数据并能够查询数据，那么关系数据库几乎可以很好地对每个应用程序领域进行建模。但在某些情况下，关系模型在性能或复杂性方面并不是最佳选择。在这些上下文中，关系与实体本身具有相同甚至更大的含义。
让我们借用《图形数据库》第二版一书中关于数据中心域上下文的一个例子（您可以从Neo4J网站上获取该书的副本）。
A Datacenter ER example model
这仍然是一个非常简单的域上下文，但它有足够数量的实体和关系，可以开始使传统数据库上的查询变得又大又慢。
以下是先前ER模型上的密钥和关系的可能建模
现在，让我们试着想象一个查询，它查找受基础结构的某个组件（服务器、负载均衡器、数据库等）故障影响的用户。您可能会得到一个带有许多JOIN的怪物查询，或者有很多小查询，每个查询都指向模型中的特定资产。当然，我们将能够得到问题的答案，但性能可能会成为一个问题，尤其是当资产数量开始增长时。
现在，让我们尝试将上一个域建模为具有资产和它们之间关系的Graph。
Example graph model for the Datacenter context
首先你可以注意到，现在关系已经成为模型中的“头等公民”，这意味着现在每个箭头都有一个特定的含义，比如HOSTED_BY、RUNS_ON、USES、USER_OF等等。在关系模型中，您只知道一个表链接到另一个表，但这种关系的含义可能很难理解。
例如，如果现在前一张图片的用户3报告了一个问题，我们可以通过发出以下查询来查询该图，以查找任何可能涉及的资产：
MATCH (user:User)-[*1..5]-(asset:Asset)
WHERE user.name = 'User 3' AND asset.status = 'down'
RETURN DISTINCT asset
等待这是什么样的疑问？！？这是一种名为Cypher的查询语言，由Neo4J引入，旨在以一种简单的方式查询图。它被定义为“在图表中导航的某种ASCIIart”。
前面的查询用通俗英语翻译如下：查找任何名为“用户3”的用户，该用户与状态为“向下”的资产有一到五个关系，然后返回找到的不同资产列表。
如果你在SQL中也这么想。。。这可能吗？可能是的，但不是以如此简短和富有表现力的方式。现在，您应该开始了解图形模型的要点了。
NEO4J
我们之前讨论过Neo4J作为一个图形数据库，有充分的理由，它是市场上最广为人知的图形数据库，它已经存在了十多年，所以它非常坚固，经过了战斗测试。
但是，如果我必须在Neo4J中找到我不喜欢的东西，那就是Graph Only，所以传统的关系数据库没有“平稳过渡”，如果你已经编写了一些软件，你必须重写所有内容才能使用新的数据模型。除此之外，Neo4J是用Java编写的，即使我每天都使用Java进行应用程序开发，我也有偏见地认为用Java编写数据库可能有点内存不足。
不要误解我的意思，如果你需要使用纯图数据模型，Neo4J仍然是最好的选择，它有很多功能，如果你想要支持，开发它的公司会提供企业支持。
一种混合方法
我想在我的应用程序中介绍图数据库的强大功能，但正如一切一样，没有一刀切的方法，图数据库也不例外，它们有利于图遍历，但不是所有可能场景的最佳选择。
应用程序的需求有时可能涉及广泛，在谈论数据库时，最好是将数据模型的一部分作为图，另一部分作为一组表，也许另一部分在文档集合中。问题是，我不喜欢在同一个应用程序中使用许多不同的数据库引擎。
有解决办法吗？
PostgreSQL与Apache AGE
PostgreSQL在数据库领域是一个众所周知的名字，原因有很多。举几个例子，它是可用的最强大的开源数据库之一，同时它坚如磐石，性能卓越。有几十兆字节的PostgreSQL数据库的例子。
PostgreSQL的一个好处是，它允许使用扩展来增强数据库本身的功能，并且有许多强大的扩展允许PostgreSQL跨越许多不同的上下文。扩展示例如下：
- TimescaleDB:处理时间序列和窗口的扩展
- PostGIS：用于地理信息系统（GIS）的扩展，允许使用坐标和地理空间计算
- ZomboDB：在与Elasticsearch集成的同时增强PostgreSQL全文搜索功能的扩展
Apache AGE（其中AGE代表“图形扩展”）是其中的另一个扩展，它是关系数据库、文档数据库和图形数据库世界之间的桥梁，利用PostgreSQL数据库的稳定性和稳定性，同时利用图形数据库的强大功能增强其功能。
AGE仍在Apache孵化器中，这意味着它仍处于早期开发阶段（当前版本i 0.7），但它是基于BitNine的AgentsGraph所做的工作，即使采用了不同的方法。AgentsGraph是一个基于PostgreSQL 10分支构建的商业图形数据库，而Apache AGE是作为标准PostgreSQL的扩展构建的（在撰写本文时，它支持版本11，但预计2022年会支持新版本），因此它可以用作标准功能的“升级”，而不会失去其余功能。
这种方法的好处在于，您可以将关系模型的特性与图数据库的特性混合在一起，这意味着您可以将表上的SQL查询与图上的OpenCyhpher查询混合在一起。
如果您认为PostgreSQL还支持JSON和JSONB数据类型，这样您就可以拥有文档数据库的部分功能，那么您就可以理解为什么这种混合解决方案是一个不错的选择。
让我们看看如何使用Docker开始使用ApacheAGE，然后在下一篇文章中，我们将看到OpenCyhper的一些功能以及与标准关系世界的集成。
使用Docker开始使用Apache AGE
开始使用Apache AGE的最快方法是在预构建的Docker容器中运行它，您可以使用以下命令来完成：
docker run -it -e POSTGRES_PASSWORD={MyPassword} -p {HostPort}:5432 sorrell/apache-age
这是Docker的“官方”镜像，但在撰写本文时，它已经有三个月的历史了，所以它没有最近添加的所有功能和错误修复。为了测试所有功能，让我们使用GitHub repo的最新来源构建一个新的映像。
使用Docker从源代码构建最新版本
- GitHub - apache/incubator-age: Graph database optimized for fast analysis and real-time data processing. It is provided as an extension to PostgreSQL.
- Graph database optimized for fast analysis and real-time data processing. It is provided as an extension to PostgreSQL. - GitHub - apache/incubator-age: Graph database optimized for fast analysis a...
- GitHubapache
您可以认为从源代码构建Docker映像可能是一个复杂的过程，但实际上这很容易，只需使用以下命令在您的机器上克隆存储库即可：
git clone https://github.com/apache/incubator-age.git
cd incubator-age
并告诉Docker构建镜像：
docker build -t apache/age .
几分钟后（取决于网络连接的速度和计算机的电源），该过程应该完成。
使用Docker启动AGE实例
要使用PostgreSQL 11和AGE扩展启动新建映像的实例，可以运行以下命令：
docker run -it -e POSTGRES_PASSWORD=mypassword -p 5432:5432 apache/age
一旦容器启动，您就可以连接到数据库并开始使用SQL和OpenCypher。
如果您更喜欢查询输出的图形表示，还可以试用AGE Viewer，这是一个node.js应用程序（处于早期开发阶段），允许您直接在浏览器内以图形或表格的方式查询和导航结果。
- GitHub - apache/incubator-age-viewer: Graph database optimized for fast analysis and real-time data processing. It is provided as an extension to PostgreSQL.
- Graph database optimized for fast analysis and real-time data processing. It is provided as an extension to PostgreSQL. - GitHub - apache/incubator-age-viewer: Graph database optimized for fast ana...
- GitHubapache
正在连接到数据库
使用ApacheAGE的另一个好处是，您正在使用标准的PostgreSQL数据库进行数据处理，因此要连接到它，您可以使用标准的psql命令行客户端
psql -h 0.0.0.0 -p 5432 -U postgres
or your preferred SQL client, like DBeaver.
如果在启动时未自动加载AGE扩展，则可以通过发出以下SQL命令来启用AGE扩展：
CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
现在您已经准备好开始使用新的图形数据库了。
创建图形
开始玩grap模型需要做的第一件事是…创建一个图形。您可以使用create_graph（'graph_NAME'）函数执行此操作：
SELECT create_graph('my_graph_name');
创建图形后，您可以使用Cypher（'graph_NAME'，QUERY）函数执行Cypher命令，如下所示：
SELECT * from cypher('my_graph_name', $$
CypherQuery
$$) as (a agtype);
例如，要创建节点（或图形语言中的“顶点”），可以运行：
SELECT * from cypher('my_graph_name', $$
CREATE (a:User { firstName: 'Fabio', lastName: 'Marini'})
RETURN a
$$) as (a agtype);
这将创建一个带有标签User以及firstName和lastName属性的顶点。图形数据库的另一个好方面是，“顶点”和“边”都可以包含可以动态添加和查询的属性。
我们刚刚触及了图形数据库的表面，您可以查看AGE的文档来了解OpenCypher查询语言的功能和语法
- 80 次浏览
【PostgreSQL架构】PostgreSQL 10和逻辑复制-设置
- 5 次浏览
【PostgreSQL架构】PostgreSQL 10：性能改进
- 11 次浏览
【PostgreSQL架构】PostgreSQL-JSONB和统计
- 30 次浏览
【PostgreSQL架构】PostgreSQL中的并行性
PostgreSQL是最优秀的对象关系数据库之一，其体系结构是基于进程的，而不是基于线程的。虽然目前几乎所有的数据库系统都使用线程来实现并行性，但是PostgreSQL的基于进程的体系结构是在POSIX线程之前实现的。PostgreSQL在启动时启动一个进程“postmaster”，之后每当一个新的客户端连接到PostgreSQL时，它就会跨越新的进程。
在版本10之前，单个连接中没有并行性。诚然，由于流程架构的原因，来自不同客户机的多个查询可以具有并行性，但它们无法从彼此获得任何性能好处。换句话说，单个查询是串行运行的，没有并行性。这是一个巨大的限制，因为单个查询不能利用多核。PostgreSQL中的并行性是从9.6版引入的。在某种意义上，并行是指一个进程可以有多个线程来查询系统并利用系统中的多核。这就提供了PostgreSQL内部查询并行性。
PostgreSQL中的并行性是作为多个功能的一部分实现的，这些功能包括顺序扫描、聚合和连接。
PostgreSQL中的并行组件
在PostgreSQL中，并行性有三个重要组成部分。这些是过程本身，聚集，和工人。如果没有并行，进程本身将处理所有数据，但是，当planner决定某个查询或其一部分可以并行时，它会在计划的可并行部分中添加一个Gather节点，并生成该子树的Gather根节点。查询执行从流程（leader）级别开始，计划的所有序列部分都由leader运行。但是，如果对查询的任何部分（或全部）启用并允许并行，则为其分配具有一组工作线程的gather节点。工作线程是与需要并行化的部分树（部分计划）并行运行的线程。关系的块在线程之间被划分，这样关系就保持顺序。线程数由PostgreSQL配置文件中设置的设置控制。工人使用共享内存进行协调/交流，一旦工人完成工作，结果将传递给领导进行积累。
并行顺序扫描
在PostgreSQL 9.6中，增加了对并行顺序扫描的支持。顺序扫描是对一个表的扫描，在这个表中，一个块序列一个接一个地被求值。这就其本质而言，允许并行性。所以这是第一个并行实现的自然候选。在这种情况下，整个表在多个工作线程中被顺序扫描。这里是一个简单的查询，我们在这里查询pgbench_accounts表行（63165），它有150000000个元组。总执行时间为4343080ms。由于没有定义索引，因此使用顺序扫描。整个表在一个没有线程的进程中被扫描。因此，无论有多少可用内核，都要使用CPU的单核。
db=# EXPLAIN ANALYZE SELECT *
FROM pgbench_accounts
WHERE abalance > 0;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..73708261.04 rows=1 width=97)
(actual time=6868.238..4343052.233 rows=63165 loops=1)
Filter: (abalance > 0)
Rows Removed by Filter: 1499936835
Planning Time: 1.155 ms
Execution Time: 4343080.557 ms
(5 rows)
如果这些150000000行在一个进程中使用“10”个工作线程并行扫描呢？它将大大缩短执行时间。
db=# EXPLAIN ANALYZE select * from pgbench_accounts where abalance > 0;
QUERY PLAN
----------------------------------------------------------------------
Gather (cost=1000.00..45010087.20 rows=1 width=97)
(actual time=14356.160..1628287.828 rows=63165 loops=1)
Workers Planned: 10
Workers Launched: 10
-> Parallel Seq Scan on pgbench_accounts
(cost=0.00..45009087.10 rows=1 width=97)
(actual time=43694.076..1628068.096 rows=5742 loops=11)
Filter: (abalance > 0)
Rows Removed by Filter: 136357894
Planning Time: 37.714 ms
Execution Time: 1628295.442 ms
(8 rows)
现在，总的执行时间是1628295ms；这是一个266%的改进，而使用10个工人线程用于扫描。
用于基准的查询：从abalance>0的pgbench_帐户中选择*；
表大小：426GB
表中总行：150000000
用于基准测试的系统：
CPU:2个Intel（R）Xeon（R）CPU E5-2643 v2@3.50GHz
内存：256GB DDR3 1600
磁盘：ST3000NM0033
上图清楚地显示了并行性如何提高顺序扫描的性能。添加单个工作进程时，由于没有获得并行性，性能下降是可以理解的，但是创建额外的聚集节点和单个工作会增加开销。但是，使用多个工作线程时，性能会显著提高。另外，需要注意的是，性能不会以线性或指数方式增加。它会逐渐改善，直到增加更多的工人不会给性能带来任何提升；有点像接近水平渐近线。这个基准测试是在一个64核的机器上执行的，很明显，拥有10个以上的工人不会显著提高性能。
平行聚合
在数据库中，计算聚合是非常昂贵的操作。当在单个过程中进行评估时，这些过程需要相当长的时间。在PostgreSQL 9.6中，通过简单地将它们分成块（一种分而治之的策略）来增加并行计算这些数据的能力。这允许多个工人在领导计算基于这些计算的最终值之前计算聚合部分。更严格地说，PartialAggregate节点被添加到一个计划树中，每个PartialAggregate节点从一个worker获取输出。这些输出随后发送到FinalizeAggregate节点，该节点组合来自多个（所有）PartialAggregate节点的聚合。因此，有效的并行部分计划包括一个FinalizeAggregate节点和一个Gather节点，后者将PartialAggregate节点作为子节点。
db=# EXPLAIN ANALYZE SELECT count(*) from pgbench_accounts;
QUERY PLAN
----------------------------------------------------------------------
Aggregate (cost=73708261.04..73708261.05 rows=1 width=8)
(actual time=2025408.357..2025408.358 rows=1 loops=1)
-> Seq Scan on pgbench_accounts (cost=0.00..67330666.83 rows=2551037683 width=0)
(actual time=8.162..1963979.618 rows=1500000000 loops=1)
Planning Time: 54.295 ms
Execution Time: 2025419.744 ms
(4 rows)
下面是并行计算聚合时计划的示例。在这里你可以清楚地看到性能的提高。
db=# EXPLAIN ANALYZE SELECT count(*) from pgbench_accounts;
QUERY PLAN
----------------------------------------------------------------------
Finalize Aggregate (cost=45010088.14..45010088.15 rows=1 width=8)
(actual time=1737802.625..1737802.625 rows=1 loops=1)
-> Gather (cost=45010087.10..45010088.11 rows=10 width=8)
(actual time=1737791.426..1737808.572 rows=11 loops=1)
Workers Planned: 10
Workers Launched: 10
-> Partial Aggregate
(cost=45009087.10..45009087.11 rows=1 width=8)
(actual time=1737752.333..1737752.334 rows=1 loops=11)
-> Parallel Seq Scan on pgbench_accounts
(cost=0.00..44371327.68 rows=255103768 width=0)
(actual time=7.037..1731083.005 rows=136363636 loops=11)
Planning Time: 46.031 ms
Execution Time: 1737817.346 ms
(8 rows)
对于并行聚合，在这种特殊情况下，当涉及10个并行工作线程时，执行时间2025419.744减少到1737817.346，我们的性能提升略高于16%。
用于基准的查询：从abalance>0的pgbench_帐户中选择count（*）；
表大小：426GB
表中总行：150000000
用于基准测试的系统：
CPU:2个Intel（R）Xeon（R）CPU E5-2643 v2@3.50GHz
内存：256GB DDR3 1600
磁盘：ST3000NM0033
并行索引（B树）扫描
对B树索引的并行支持意味着索引页被并行扫描。B树索引是PostgreSQL中最常用的索引之一。在并行版本的B-Tree中，一个worker扫描B-Tree，当它到达它的叶节点时，它会扫描块并触发阻塞的等待worker扫描下一个块。
困惑的？我们来看一个例子。假设我们有一个具有id和name列的表foo，其中有18行数据。我们在表foo的id列上创建一个索引。系统列CTID附加在表的每一行，用于标识行的物理位置。CTID列中有两个值：块号和偏移量。
postgres=# <strong>SELECT</strong> ctid, id <strong>FROM</strong> foo;
ctid | id
--------+-----
(0,55) | 200
(0,56) | 300
(0,57) | 210
(0,58) | 220
(0,59) | 230
(0,60) | 203
(0,61) | 204
(0,62) | 300
(0,63) | 301
(0,64) | 302
(0,65) | 301
(0,66) | 302
(1,31) | 100
(1,32) | 101
(1,33) | 102
(1,34) | 103
(1,35) | 104
(1,36) | 105
(18 rows)
让我们在该表的id列上创建B树索引。
CREATE INDEX foo_idx ON foo(id)
假设我们要选择id<=200的值，其中有两个工人。Worker-0将从根节点开始扫描，直到叶节点200。它将把节点105下的下一个块移交给Worker-1，Worker-1处于阻塞等待状态。如果还有其他工人，就把街区分成工人区。重复类似的模式，直到扫描完成。
并行位图扫描
要并行化位图堆扫描，我们需要能够以非常类似于并行顺序扫描的方式在工作线程之间划分块。为此，将对一个或多个索引进行扫描，并创建指示要访问哪些块的位图。这是由一个引导进程完成的，即扫描的这一部分是按顺序运行的。然而，当识别出的块被传递给workers时，并行性就会启动，就像并行顺序扫描一样。
平行连接
合并联接支持中的并行性也是此版本中添加的最热门功能之一。在这种情况下，表与其他表的内部循环哈希或合并相连接。在任何情况下，内部循环中都不支持并行性。整个循环作为一个整体进行扫描，并行性在每个工作进程作为一个整体执行内部循环时发生。发送到gather的每个连接的结果都会累积并产生最终结果。
摘要
从我们在本博客中已经讨论过的内容中可以明显看出，并行性在某些情况下会显著提高性能，而在某些情况下会导致性能下降。确保已正确设置并行设置成本或并行元组成本，以使查询计划器能够选择并行计划。即使为这些gui设置了较低的值，如果没有生成并行计划，请参阅PostgreSQL并行性文档以了解详细信息。
对于并行计划，您可以获取每个计划节点的每个工作进程统计信息，以了解如何在工作进程之间分配负载。你可以通过解释（分析，详细）来做到这一点。与任何其他性能特性一样，没有一条规则适用于所有工作负载。无论需要什么，都应该仔细配置并行性，并且必须确保获得性能的概率明显高于性能下降的概率。
原文：https://www.percona.com/blog/2019/07/30/parallelism-in-postgresql/
本文：http://jiagoushi.pro/node/1049
讨论：请加入知识星球【首席架构师圈】或者小号【jiagoushi_pro】
- 74 次浏览
【PostgreSQL架构】PostgreSQL和仅堆元组更新-第2部分
- 15 次浏览
【PostgreSQL架构】PostgreSQL的最佳PG群集高可用性（HA）解决方案
如果您的系统依赖PostgreSQL数据库并且您正在寻找HA的集群解决方案，我们希望提前告知您这是一项复杂的任务，但并非不可能实现。
我们将讨论一些解决方案，您可以从中选择对您的容错要求。
PostgreSQL本身不支持任何多主群集解决方案，例如MySQL或Oracle。尽管如此，仍有许多商业和社区产品提供此实现，以及其他产品，例如PostgreSQL的复制或负载平衡。
首先，让我们回顾一些基本概念：
什么是高可用性？
它是服务可用的时间量，通常由企业定义。
冗余是高可用性的基础；万一发生事故，我们可以继续毫无问题地运转。
持续恢复
如果发生事件，则必须还原备份，然后应用wal日志；恢复时间将非常长，我们不会谈论高可用性。
但是，如果我们将备份和日志存档在应急服务器中，则可以在日志到达时应用它们。
如果日志每隔1分钟发送和应用一次，则应急基础将处于连续恢复状态，并且到生产的时间最多为1分钟。
备用数据库
备用数据库的想法是保留生产数据库的副本，该副本始终具有相同的数据，并且可以在发生事件时使用。
有几种方法可以对备用数据库进行分类：
根据复制的性质：
- 物理备用数据库：复制磁盘块。
- 逻辑备用数据库：流式传输数据更改。
通过事务的同步性：
- 异步：可能会丢失数据。
- 同步：不会丢失数据；主服务器中的提交等待备用服务器的响应。
通过用法：
- 热备用：它们不支持连接。
- 热备用：支持只读连接。
集群
群集是一组一起工作的主机，被视为一个主机。
这提供了一种实现水平可伸缩性的方法，并提供了通过添加服务器来处理更多工作的能力。
它可以抵抗节点的故障并继续透明地工作。
根据共享的内容，有两种模型：
- 共享存储：所有节点都使用相同的信息访问相同的存储。
- 不共享：每个节点都有自己的存储，取决于我们系统的结构，该存储可能与其他节点具有相同的信息。
现在让我们回顾一下PostgreSQL中的一些集群选项。
分布式复制块设备 （Distributed Replicated Block Device）
DRBD是一个Linux内核模块，可使用网络实现同步块复制。 它实际上不实现群集，也不处理故障转移或监视。 为此，您需要补充软件，例如Corosync + Pacemaker + DRBD。
例：
- Corosync：处理主机之间的消息。
- Pacemaker：启动和停止服务，确保它们仅在一台主机上运行。
- DRBD：在块设备级别同步数据。
集群控制（ClusterControl）
ClusterControl是用于数据库集群的无代理管理和自动化软件。 它可直接从其用户界面帮助部署，监视，管理和扩展数据库服务器/集群。
ClusterControl能够处理维护数据库服务器或群集所需的大多数管理任务。
使用ClusterControl，您可以：
- 在您选择的技术堆栈上部署独立的，复制的或群集的数据库。
- 跨多语言数据库和动态基础架构统一自动化故障转移，恢复和日常任务。
- 您可以创建完整或增量备份并计划它们。
- 对整个数据库和服务器基础结构进行统一和全面的实时监控。
- 只需一个操作即可轻松添加或删除节点。
在PostgreSQL上，如果发生事件，可以自动将您的从属提升为主状态。
它是一个非常完整的工具，带有免费的社区版本（还包括免费的企业试用版）。
红宝石 （Rubyrep）
异步，多主机，多平台复制（在Ruby或JRuby中实现）和多DBMS（MySQL或PostgreSQL）的解决方案。
基于触发器，它不支持DDL，用户或授权。
使用和管理的简单性是其主要目标。
一些功能：
- 配置简单
- 安装简单
- 平台独立，表格设计独立。
Pgpool II
它是一种在PostgreSQL服务器和PostgreSQL数据库客户端之间工作的中间件。
一些功能：
- 连接池
- 复写
- 负载均衡
- 自动故障转移
- 并行查询
Bucardo
基于行的异步级联主从复制，使用触发器在数据库中排队；基于行的异步主-主复制，基于行，使用触发器和自定义冲突解决方案。
Bucardo需要专用的数据库并作为Perl守护程序运行，该守护程序与此数据库以及复制中涉及的所有其他数据库进行通信。它可以作为多主机或多从机运行。
主从复制涉及到一个或多个目标的一个或多个源。源必须是PostgreSQL，但是目标可以是PostgreSQL，MySQL，Redis，Oracle，MariaDB，SQLite或MongoDB。
一些功能：
- 负载均衡
- 从站不受限制，可以写
- 部分复制
- 按需复制（更改可以自动或在需要时推送）
- 从站可以“预热”以快速设置
缺点：
- 无法处理DDL
- 无法处理大物件
- 没有唯一键无法增量复制表
- 不适用于Postgres 8之前的版本
Postgres-XC
Postgres-XC是一个开源项目，旨在提供可写扩展，同步，对称和透明的PostgreSQL集群解决方案。它是紧密耦合的数据库组件的集合，可以将其安装在多个硬件或虚拟机中。
写可伸缩性意味着Postgres-XC可以配置任意数量的数据库服务器，并且与单个数据库服务器相比，可以处理更多的写操作（更新SQL语句）。
您可以有多个客户端连接到的数据库服务器，该服务器提供数据库的单个一致的群集范围视图。
来自任何数据库服务器的任何数据库更新对于在不同主服务器上运行的任何其他事务都是立即可见的。
透明意味着您不必担心内部如何将数据存储在多个数据库服务器中。
您可以配置Postgres-XC在多个服务器上运行。您为每个表选择的数据以分布式方式存储，即分区或复制。发出查询时，Postgres-XC会确定目标数据的存储位置，并向包含目标数据的服务器发出相应的查询。
Citus
Citus用内置的高可用性功能（例如自动分片和复制）替代了PostgreSQL。 Citus分片将您的数据库分片，并在整个商品节点集群中复制每个分片的多个副本。如果群集中的任何节点不可用，Citus会将所有写入或查询透明地重定向到其他一个包含受影响的分片副本的节点。
一些功能：
- 自动逻辑分片
- 内置复制
- 用于灾难恢复的数据中心感知复制
- 具有高级负载平衡功能的中查询容错
您可以增加由PostgreSQL支持的实时应用程序的正常运行时间，并最大程度地减少硬件故障对性能的影响。您可以使用内置的高可用性工具来实现此目标，从而最大程度地减少成本高昂且易于出错的手动干预。
PostgresXL
它是一种无共享的多主群集解决方案，可以透明地在一组节点上分配表，并并行执行这些节点的查询。它具有一个称为全局事务管理器（GTM）的附加组件，用于提供群集的全局一致视图。该项目基于PostgreSQL 9.5版本。一些公司，例如2ndQuadrant，为该产品提供商业支持。
PostgresXL是可水平扩展的开源SQL数据库集群，具有足够的灵活性来处理各种数据库工作负载：
- OLTP写密集型工作负载
- 需要MPP并行性的商业智能
- 运营数据存储
- 键值存储
- GIS地理空间
- 混合工作负载环境
- 多租户提供商托管环境
组件：
- 全局事务监视器（GTM）：全局事务监视器确保群集范围内的事务一致性。
- 协调器：协调器管理用户会话并与GTM和数据节点进行交互。
- 数据节点：数据节点是存储实际数据的位置。
结论
还有许多其他产品可以为PostgreSQL创建我们的高可用性环境，但是您必须注意以下几点：
- 新产品，未经充分测试
- 停产项目
- 局限性
- 许可费用
- 非常复杂的实现
- 不安全的解决方案
您还必须考虑您的基础架构。如果只有一台应用程序服务器，那么无论您配置了多少数据库的高可用性，如果应用程序服务器发生故障，则将无法访问。您必须很好地分析基础架构中的单点故障，并尝试解决它们。
考虑到这些要点，您可以找到一种适合您的需求和要求的解决方案，而不会产生麻烦，并且能够实施您的高可用性群集解决方案。来吧，祝你好运！
原文：https://severalnines.com/database-blog/top-pg-clustering-high-availability-ha-solutions-postgresql
本文：http://jiagoushi.pro/top-pg-clustering-high-availability-ha-solutions-postgresql
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 1569 次浏览
【PostgreSQL架构】设置和部署具有高可用性的企业级PostgreSQL集群
业务应用程序要求其后端数据库集群具有高可用性。 2ndQuadrant为Single Master体系结构提供了企业级解决方案，该解决方案允许部署具有高可用性，滚动升级，自动故障转移等功能的PostgreSQL。 该架构依赖于行业最佳实践，并根据多年满足企业需求的经验为生产使用提供了高水平的可靠性。
要了解有关如何部署PostgreSQL以获得高可用性的最佳实践的更多信息，请下载HA Postgres白皮书。
备份策略和灾难恢复
该解决方案具有内置的能力，可以制定备份策略并从灾难中恢复。 我们依靠Barman（一种用于备份和恢复管理的领先工具）直接从主节点传输数据流。 该解决方案还提供了时间点恢复的功能。
高可用性
无论对于计划的切换还是计划外的故障，可用性对于任何业务应用程序都是至关重要的。 该解决方案具有处理任何一种情况的能力。 内置控件将使您轻松计划切换。 如果发生节点故障（主节点或备用节点），该解决方案将进行故障转移以确保数据库可用于您的应用程序。 它甚至在数据中心完全故障的情况下提供冗余配置！
原文：https://www.2ndquadrant.com/en/resources/highly-available-postgresql-clusters/
本文：http://jiagoushi.pro/node/931/
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 112 次浏览
【Postgres 架构】pg_auto_failover简介：高可用性和自动故障转移Postgres扩展
作为Citus团队的一员（Citus横向扩展Postgres，但这不是我们要做的全部），我从事pg_auto_failover已有相当一段时间了，我很高兴我们现在已经将pgautofailover作为开源引入了，为您提供自动故障转移和高可用性！
在设计pg_auto_failover时，我们的目标是：为Postgres提供易于设置的业务连续性解决方案，该解决方案实现系统中任何一个节点的容错能力。关于pg_auto_failover架构的文档章节包括以下内容：
重要的是要了解pgautofailover已针对业务连续性进行了优化。万一丢失单个节点，由于PostgreSQL同步复制，pgautofailover能够继续PostgreSQL服务，并在这样做时防止任何数据丢失。
pg_auto_failover简介
用于Postgres的pg_auto_failover解决方案旨在提供一种易于设置且可靠的自动化故障转移解决方案。该解决方案包括由软件驱动的决策，以决定何时在生产中实施故障转移。
任何自动故障转移系统中最重要的部分是决策策略，我们在线上有完整的文档章节，内容涉及pgautofailover故障容忍机制。
使用pgautofailover时，将部署多个活动代理来跟踪您的生产Postgres安装属性：
- 监视器是一个本身具有pg_auto_failover扩展名的Postgres数据库，它注册并检查活动Postgres节点的运行状况。
- 在pg_auto_failover监视器中注册的每个Postgres节点也必须运行本地代理pg_autoctl运行服务。
- 每个受管理的Postgres服务在同一个组中有两个设置在一起的Postgres节点。一个监视器设置可以根据需要管理多个Postgres组。
通过这样的部署，监控器会定期连接到每个已注册的节点（默认为20秒），并在其pgautofailover.node表中注册成功或失败。
除此之外，每个Postgres节点上的pg_autoctl运行服务还会检查Postgres是否正在运行，并监视其他节点的pgstatreplication统计信息。此Postgres系统视图使我们的本地代理能够发现主节点和备用节点之间的网络连接。本地代理定期每隔5s向监视器报告每个节点的状态，除非需要进行转换，然后立即进行。
pg_auto_failover监视器根据集群中两个节点的已知状态做出决策，并且仅遵循我们精心设计以确保节点收敛的有限状态机。特别是，只有在pg_autoctl代理报告成功实现了确定的过渡到新状态后，FSM才取得进展。关于故障转移逻辑的体系结构文档部分包含FSM的映像，我们使用这些映像来确保pgautofailover中的自动故障转移决策。
pg_auto_failover快速入门
再一次，请参阅pg_auto_failover的“快速入门”文档部分以获取更多详细信息。 首次尝试该项目时，最简单的方法是创建一个监视器，然后注册一个主要的Postgres实例，然后注册一个辅助的Postgres实例。
下面列出了一些Shell命令，这些命令在localhost上都实现了简单的部署，以用于项目发现。
监控器
在第一个终端，终端选项卡，屏幕或tmux窗口中，运行以下命令来创建监视器，包括使用initdb初始化Postgres集群，安装我们的pg_auto_failover扩展以及在HBA文件中打开连接特权。
首先，我们在终端中准备环境：
$ mkdir /tmp/pg_auto_failover/test
$ export PGDATA=/tmp/pg_auto_failover/test/monitor
然后，我们可以使用刚刚准备的PGDATA环境设置在本地端口6000上的本地主机上创建Monitor Postgres实例：
$ pg_autoctl create monitor --nodename localhost --pgport 6000
12:12:53 INFO Initialising a PostgreSQL cluster at "/tmp/pg_auto_failover/test/monitor"
12:12:53 INFO Now using absolute pgdata value "/private/tmp/pg_auto_failover/test/monitor" in the configuration
12:12:53 INFO /Applications/Postgres.app/Contents/Versions/10/bin/pg_ctl --pgdata /tmp/pg_auto_failover/test/monitor --options "-p 6000" --options "-h *" --wait start
12:12:53 INFO Granting connection privileges on 192.168.1.0/24
12:12:53 INFO Your pg_auto_failover monitor instance is now ready on port 6000.
12:12:53 INFO pg_auto_failover monitor is ready at postgres://autoctl_node@localhost:6000/pg_auto_failover
12:12:53 INFO Monitor has been succesfully initialized.
现在我们可以将连接字符串重新显示到监视器：
$ pg_autoctl show uri
postgres://autoctl_node@localhost:6000/pg_auto_failover
Postgres主节点
在另一个终端（选项卡，窗口，以通常的方式进行操作）中，现在创建一个主要的PostgreSQL实例：
$ export PGDATA=/tmp/pg_auto_failover/test/node_a
$ pg_autoctl create postgres --nodename localhost --pgport 6001 --dbname test --monitor postgres://autoctl_node@localhost:6000/pg_auto_failover
12:15:27 INFO Registered node localhost:6001 with id 1 in formation "default", group 0.
12:15:27 INFO Writing keeper init state file at "/Users/dim/.local/share/pg_autoctl/tmp/pg_auto_failover/test/node_a/pg_autoctl.init"
12:15:27 INFO Successfully registered as "single" to the monitor.
12:15:28 INFO Initialising a PostgreSQL cluster at "/tmp/pg_auto_failover/test/node_a"
12:15:28 INFO Now using absolute pgdata value "/private/tmp/pg_auto_failover/test/node_a" in the configuration
12:15:28 INFO Postgres is not running, starting postgres
12:15:28 INFO /Applications/Postgres.app/Contents/Versions/10/bin/pg_ctl --pgdata /private/tmp/pg_auto_failover/test/node_a --options "-p 6001" --options "-h *" --wait start
12:15:28 INFO CREATE DATABASE test;
12:15:29 INFO FSM transition from "init" to "single": Start as a single node
12:15:29 INFO Initialising postgres as a primary
12:15:29 INFO Transition complete: current state is now "single"
12:15:29 INFO Keeper has been succesfully initialized.
此命令将PostgreSQL实例注册到监视器，使用pg_ctl initdb创建实例，为监视器运行状况检查准备一些连接权限，并为您创建一个名为test的数据库。 然后，执行由监视器排序的第一个转换，从状态INIT到达状态SINGLE。
现在，我们仍在测试中，因此在终端中以交互方式启动pg_autoctl运行服务。 对于生产设置，这将进入需要引导时间的系统服务，例如systemd。
$ pg_autoctl run
12:17:07 INFO Managing PostgreSQL installation at "/tmp/pg_auto_failover/test/node_a"
12:17:07 INFO pg_autoctl service is starting
12:17:07 INFO Calling node_active for node default/1/0 with current state: single, PostgreSQL is running, sync_state is "", WAL delta is -1.
最后一行将每5s重复一次，这表明主节点运行状况良好，并且可以正常连接到监视器。 而且，它现在处于SINGLE状态，一旦新的Postgres节点加入该组，它就会改变。
Postgres辅助节点
现在是时候在另一个终端上创建辅助Postgres实例了：
$ export PGDATA=/tmp/pg_auto_failover/test/node_b
$ pg_autoctl create postgres --nodename localhost --pgport 6002 --dbname test --monitor postgres://autoctl_node@localhost:6000/pg_auto_failover
12:21:08 INFO Registered node localhost:6002 with id 5 in formation "default", group 0.
12:21:09 INFO Writing keeper init state file at "/Users/dim/.local/share/pg_autoctl/tmp/pg_auto_failover/test/node_b/pg_autoctl.init"
12:21:09 INFO Successfully registered as "wait_standby" to the monitor.
12:21:09 INFO FSM transition from "init" to "wait_standby": Start following a primary
12:21:09 INFO Transition complete: current state is now "wait_standby"
12:21:14 INFO FSM transition from "wait_standby" to "catchingup": The primary is now ready to accept a standby
12:21:14 INFO The primary node returned by the monitor is localhost:6001
12:21:14 INFO Initialising PostgreSQL as a hot standby
12:21:14 INFO Running /Applications/Postgres.app/Contents/Versions/10/bin/pg_basebackup -w -h localhost -p 6001 --pgdata /tmp/pg_auto_failover/test/backup -U pgautofailover_replicator --write-recovery-conf --max-rate 100M --wal-method=stream --slot pgautofailover_standby ...
12:21:14 INFO pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
32041/32041 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/20000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
12:21:14 INFO Postgres is not running, starting postgres
12:21:14 INFO /Applications/Postgres.app/Contents/Versions/10/bin/pg_ctl --pgdata /tmp/pg_auto_failover/test/node_b --options "-p 6002" --options "-h *" --wait start
12:21:15 INFO PostgreSQL started on port 6002
12:21:15 WARN Contents of "/tmp/pg_auto_failover/test/node_b/postgresql-auto-failover.conf" have changed, overwriting
12:21:15 INFO Transition complete: current state is now "catchingup"
12:21:15 INFO Now using absolute pgdata value "/private/tmp/pg_auto_failover/test/node_b" in the configuration
12:21:15 INFO Keeper has been succesfully initialized.
这次向监视器的注册返回了状态WAITSTANDBY，该状态驱动pgautoctl创建辅助节点。 这是因为服务器已存在于组中，并且当前为SINGLE。 与此并行，监视器将目标状态WAIT_PRIMARY分配给主节点，localpgautoctlagent将在其中从监视器数据库和openpghba.conf中检索新节点的节点名称和端口以进行复制。 完成后，辅助节点继续pg_basebackup，安装arecovery.conf`文件，启动本地Postgres服务，并通知监视器有关达到目标状态的信息。
不过，我们仍在CATCHING_UP。 这意味着尚无法进行自动故障转移。 为了能够安排故障转移，我们需要在新节点上运行本地服务，监视Postgres的运行状况和复制状态，并每5秒向监视器报告一次：
$ pg_autoctl run
12:26:26 INFO Calling node_active for node default/5/0 with current state: catchingup, PostgreSQL is running, sync_state is "", WAL delta is -1.
12:26:26 INFO FSM transition from "catchingup" to "secondary": Convinced the monitor that I'm up and running, and eligible for promotion again
12:26:26 INFO Transition complete: current state is now "secondary"
12:26:26 INFO Calling node_active for node default/5/0 with current state: secondary, PostgreSQL is running, sync_state is "", WAL delta is 0.
现在，新节点处于SECONDARY状态，并继续向监视器报告，准备在监视器做出决定时提升本地Postgres实例。
使用pg_auto_failover进行自动和手动故障转移
每个节点使用pg_auto_failover来配置具有自动故障转移功能的PostgreSQL集群所需要做的就是：每个节点使用两个命令：首先使用pg_autoctl create ...创建节点，然后运行pg_autoctl来运行本地服务，以实现由监视器决定的转换。
要见证故障转移，最简单的方法是停止pg_autoctl运行服务（在运行它的终端中使用^ C或在其他任何地方使用pg_autoctl stop --pgdata ...；然后也使用pg_ctl停止Postgres实例- D ...停下来。
当仅停止Postgres时，pg_autoctl运行服务将检测到该情况为异常，然后首先尝试重新启动Postgres。仅当使用默认pg_auto_failover参数连续3次未能启动Postgres时，才认为故障转移是适当的。
注入故障转移条件的另一种方法是礼貌地要求监视器为您安排一个：
$ psql postgres://autoctl_node@localhost:6000/pg_auto_failover
> select pgautofailover.perform_failover();
应用程序和客户端的连接字符串
整个设置以pg_auto_failover条款的形式运行。 默认格式名为default，并且包含两个Postgres实例的单个组。 想法是只有一个入口，可以将应用程序连接到任何给定的形式。 要获取到我们的pg_auto_failover托管的Postgres服务的连接字符串，请发出以下命令，例如 在监视器终端上：
$ pg_autoctl show uri --formation default
postgres://localhost:6002,localhost:6001/test?target_session_attrs=read-write
我们在这里使用libpq的多主机功能。 当它基于libpq（大多数都是这样）时，可以与任何现代Postgres驱动程序一起使用，并且已知其他本地驱动程序也可以实现相同的功能，例如JDBC Postgres驱动程序。
当然，如果适用于psql：
$ psql postgres://localhost:6002,localhost:6001/test?target_session_attrs=read-write
psql (12devel, server 10.7)
Type "help" for help.
test# select pg_is_in_recovery();
pg_is_in_recovery
═══════════════════
f
(1 row)
当使用这样的连接字符串时，连接驱动程序将连接到第一台主机并检查是否接受写操作，如果不是，则连接到第二台主机并再次检查。那是因为我们说过我们希望targetsessionattrs是可读写的。
使用核心Postgres的此功能，我们实现了客户端的高可用性：在发生故障转移的情况下，我们的node_b将成为主要对象，并且我们需要应用程序现在将node_b定位为写入对象，并且该操作将在连接驱动程序中自动完成水平。
高可用性，容错和业务连续性
因此pgautofailover就是关于业务连续性的，并且为每个主要的Postgres服务器使用一个备用服务器。
在用于Postgres的经典HA设置中，我们依靠每个主服务器都有两个备用服务器的同步复制。当您想要实现零或接近零的RTO和RPO目标时，这就是预期的架构。
同样，每个主节点使用两个备用节点的想法是，您会丢失任何备用服务器，并且仍然知道在两个不同的地方仍可以使用数据，因此仍然乐于接受写入。这在许多生产设置中都是非常好的属性，并且是其他现有Postgres HA工具的目标。
在某些情况下，最佳的生产设置折衷方法与当前Postgres HA工具支持的方法有所不同。有时可以在需要执行灾难恢复过程时面对服务中断，因为对这种情况下必要风险的评估符合生产预算，预期的SLA或其组合。
并非所有项目都需要超过99.95％的可用性，即使没有走到最后一英里，有时也需要达到99.999％的目标。此外，尽管物联网和其他一些用例（例如庞大的用户群）需要HA解决方案，这些解决方案需要将TB级数据扩展到PB级数据，但许多项目却是针对较小的受众和数据集的。当您拥有千兆字节的数据，甚至数十千兆字节的数据时，灾难恢复的时机也将不再可能被吞噬，具体取决于您的SLA条款。
数据可用性
pg_auto_failover使用PostgreSQL同步复制来确保在故障转移操作时没有数据丢失。 sync rep Postgre功能可确保当客户端应用程序收到来自Postgres的COMMIT消息时，数据便将其发送到我们的辅助节点。
面对系统中任何一个ONE节点丢失的情况，pg_auto_failover可以正常工作。如果丢失了主服务器，然后又丢失了辅助服务器，那么除了备份之外，什么都没有。使用pg_auto_failover时，对于一次丢失多台服务器的情况，您仍然必须设置适当的灾难恢复解决方案。是的，这发生了。
还请注意臭名昭著的_file系统是否已满_，由于我们习惯于部署类似规格的服务器，因此它喜欢同时攻击主服务器和辅助服务器……
结论
微软在这里的整个Citus团队都对pg_auto_failover扩展的开源版本感到兴奋。我们根据Postgres开放源代码许可发布了pg_auto_failover，因此您可以以与部署Postgres完全相同的能力享受我们的贡献。该项目是完全开放的，欢迎每个人参与并在我们的GitHub存储库上为https://github.com/citusdata/pg_auto_failover做出贡献。我们正在遵循Microsoft开放源代码行为准则，并确保所有人都受到欢迎和聆听。
我的希望是，由于有了pg_auto_failover，你们中的许多人现在将能够使用自动故障转移解决方案在生产中部署Postgres。
原文：https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover/
本文：http://jiagoushi.pro/node/922
讨论:请加入知识星球或者微信圈子【首席架构师圈】
- 234 次浏览
【Postgresql】PostgreSQL和MySQL：每秒数百万的查询
这篇博客比较了PostgreSQL和MySQL每秒处理数百万次查询的方式。
Anastasia：开源数据库可以应对每秒数百万次查询吗？许多开源倡导者会回答“是”。然而，断言并不足以提供充分的证据。这就是为什么在这篇博文中，我们分享了Alexander Korotkov（开发，Postgres Professional首席执行官）和Sveta Smirnova（首席技术服务工程师，Percona）的基准测试结果。 PostgreSQL 9.6和MySQL 5.7性能的比较研究对于具有多个数据库的环境尤其有用。
这项研究背后的想法是提供两个流行的RDBMS的诚实比较。 Sveta和Alexander希望使用相同的工具在相同的挑战性工作负载下使用相同的配置参数（如果可能）测试PostgreSQL和MySQL的最新版本。但是，由于PostgreSQL和MySQL生态系统都是独立发展的，每个数据库都使用标准的测试工具（pgbench和SysBench），这不是一件容易的事。
这项任务落到了具有多年实践经验的数据库专家手中。 Sveta曾在Oracle MySQL支持小组的Bugs Verification Group担任高级首席技术支持工程师超过八年，自2015年起担任Percona的首席技术服务工程师。 Alexander Korotkov是PostgreSQL的主要贡献者，也是PostgreSQL众多功能的开发者 - 包括CREATE ACCESS METHOD命令，通用WAL接口，无锁Pin / UnpinBuffer，基于索引的正则表达式搜索等等。所以我们有一个相当不错的演员阵容！
Sveta：Dimitri Kravtchuk定期发布MySQL的详细基准测试，因此我的主要任务不是确认MySQL每秒可以进行数百万次查询。正如我们的图表所示，我们已经通过了那个标记。作为支持工程师，我经常与在他们的商店中拥有异构数据库环境的客户合作，并希望了解将作业从一个数据库迁移到另一个数据库的影响。相反，我发现有机会与Postgres专业公司合作，并确定两个数据库的优点和缺点是一个绝佳的机会。
我们希望使用相同的工具和测试在相同的硬件上测试PostgreSQL和MySQL。我们期望测试基本功能，然后进行更详细的比较。这样我们就可以比较不同的真实用例场景和流行的选项。
剧透：我们距离最终结果还很远。这是博客系列的开始。
大机器上的OpenSource数据库，系列1：“那是关闭......”
Postgres Professional与 Freematiq一起提供了两台现代化，功能强大的测试机器。
硬件配置：
Processors: physical = 4, cores = 72, virtual = 144, hyperthreading = yes
Memory: 3.0T
Disk speed: about 3K IOPS
OS: CentOS 7.1.1503
File system: XFS
我还使用了较小的Percona机器。
硬件配置：
Processors: physical = 2, cores = 12, virtual = 24, hyperthreading = yes
Memory: 251.9G
Disk speed: about 33K IOPS
OS: Ubuntu 14.04.5 LTS
File system: EXT4
请注意，具有较少CPU核心和较快磁盘的计算机比具有较大核心数的计算机更常见于MySQL安装。
我们需要达成共识的第一件事是使用哪种工具。如果工作负载尽可能接近，那么公平的比较才有意义。
用于性能测试的标准PostgreSQL工具是pgbench，而对于MySQL，它是SysBench。 SysBench支持Lua编程语言中的多个数据库驱动程序和脚本化测试，因此我们决定将这个工具用于这两个数据库。
最初的计划是将pgbench测试转换为SysBench Lua语法，然后在两个数据库上运行标准测试。在初步结果之后，我们修改了测试以更好地检查特定的MySQL和PostgreSQL功能。
我将pgbench测试转换为SysBench语法，并将测试放入开放式数据库工作台GitHub存储库中。
然后我们都遇到了困难。
正如我已经写过的那样，我也在Percona机器上运行测试。对于此转换测试，结果几乎相同：
Percona machine:
OLTP test statistics:
transactions: 1000000 (28727.81 per sec.)
read/write requests: 5000000 (143639.05 per sec.)
other operations: 2000000 (57455.62 per sec.)）
Freematiq机器：
OLTP test statistics:
transactions: 1000000 (29784.74 per sec.)
read/write requests: 5000000 (148923.71 per sec.)
other operations: 2000000 (59569.49 per sec.)
我开始调查了。 Percona机器比Freematiq更好的唯一地方是磁盘速度。所以我开始运行pgbench只读测试，这与SysBench的点选择测试相同，内存中有完整的数据集。但这次SysBench使用了50％的可用CPU资源：
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4585 smirnova 20 0 0,157t 0,041t 9596 S 7226 1,4 12:27.16 mysqld
8745 smirnova 20 0 1266212 629148 1824 S 7126 0,0 9:22.78 sysbench
反过来，Alexander遇到了SysBench的问题，当使用预处理语句时，它无法在PostgreSQL上创建高负载：
93087 korotkov 20 0 9289440 3,718g 2964 S 242,6 0,1 0:32.82 sysbench
93161 korotkov 20 0 32,904g 81612 80208 S 4,0 0,0 0:00.47 postgres
93116 korotkov 20 0 32,904g 80828 79424 S 3,6 0,0 0:00.46 postgres
93118 korotkov 20 0 32,904g 80424 79020 S 3,6 0,0 0:00.47 postgres
93121 korotkov 20 0 32,904g 80720 79312 S 3,6 0,0 0:00.47 postgres
93128 korotkov 20 0 32,904g 77936 76536 S 3,6 0,0 0:00.46 postgres
93130 korotkov 20 0 32,904g 81604 80204 S 3,6 0,0 0:00.47 postgres
93146 korotkov 20 0 32,904g 81112 79704 S 3,6 0,0 0:00.46 postgres
我们联系了SysBench的作者Alexey Kopytov，他修复了MySQL问题。解决方案是：
- Use SysBench with the options --percentile=0 --max-requests=0 (reasonable CPU usage)
- Use the concurrency_kit branch (better concurrency and Lua processing)
- Rewrite Lua scripts to support prepared statements (pull request: https://github.com/akopytov/sysbench/pull/94)
- Start both SysBench and mysqld with the jemalloc or tmalloc library pre-loaded
PostgreSQL的修复程序正在进行中。目前，Alexander将标准的SysBench测试转换为pgbench格式，我们坚持使用它。对于MySQL来说并不是什么新鲜事，但至少我们有一个比较基线。
我面临的下一个难题是默认的操作系统参数。长话短说，我将它们改为推荐的（如下所述）：
vm.swappiness=1
cpupower frequency-set --governor performance
kernel.sched_autogroup_enabled=0
kernel.sched_migration_cost_ns= 5000000
vm.dirty_background_bytes=67108864
vm.dirty_bytes=536870912
IO scheduler [deadline]
对于PostgreSQL性能，相同的参数也更好。亚历山大同样设置他的机器
解决了这些问题后，我们学习并实施了以下内容：
- 我们不能使用单一工具（暂时）
- 亚历山大为pgbench编写了一个测试，模仿标准的SysBench测试
- 我们仍然无法编写自定义测试，因为我们使用不同的工具
但我们可以将这些测试用作基线。在亚历山大完成工作后，我们坚持使用标准的SysBench测试。我将它们转换为使用准备好的语句，亚历山大将它们转换为pgbench格式。
我应该提一下，对于Read Only和Point Select测试，我无法获得与Dimitri相同的结果。它们很近但略慢。我们需要调查这是不同硬件的结果还是我缺乏性能测试能力。读写测试的结果是类似的。
另一个区别是PostgreSQL和MySQL测试。 MySQL用户通常有很多连接。如今，设置变量max_conenctions的值，并将并行连接的总数限制为数千并不罕见。虽然不推荐，但即使没有线程池插件，人们也会使用此选项。在现实生活中，大多数这些联系都在睡觉。但是，在网站活动增加的情况下，他们总是有机会被使用。
对于MySQL，我测试了多达1024个连接。我使用了2的幂和核心数的倍数：1,2,4,8,16,32,36,64,72,128,144,256,512和1024个线程。
对亚历山大来说，以较小的步骤进行测试更为重要。他从一个线程开始，增加了10个线程，直到达到250个并行线程。因此，您将看到PostgreSQL的更详细的图表，但在250个线程之后没有结果。
这是我们的比较结果。
Point SELECTs
- pgsql-9.6 is standard PostgreSQL
- pgsql-9.6 + pgxact-align is PostgreSQL with this patch (more details can be found in this blog post)
- MySQL-5.7 Dimitri is Oracle’s MySQL Server
- MySQL-5.7 Sveta is Percona Server 5.7.15
OLTP RO
OLTP RW
PostgreSQL中的同步提交是一个功能，类似于InnoDB中的innodb_flush_log_at_trx_commit = 1，异步提交类似于innodb_flush_log_at_trx_commit = 2。
您会看到结果非常相似：两个数据库都在快速发展，并且可以很好地使用现代硬件。
MySQL results which show 1024 threads for reference.
Point SELECT and OLTP RO
OLTP RW with innodb_flush_log_at_trx_commit set to 1 and 2
收到这些结果后，我们做了一些功能特定的测试，这些测试将在单独的博客文章中介绍。
更多信息
用于OLTP RO和Point SELECT测试的MySQL选项：
# general
table_open_cache = 8000
table_open_cache_instances=16
back_log=1500
query_cache_type=0
max_connections=4000# files
innodb_file_per_table
innodb_log_file_size=1024M
innodb_log_files_in_group=3
innodb_open_files=4000# Monitoring
innodb_monitor_enable = '%'
performance_schema=OFF #cpu-bound, matters for performance#Percona Server specific
userstat=0
thread-statistics=0# buffers
innodb_buffer_pool_size=128000M
innodb_buffer_pool_instances=128 #to avoid wait on InnoDB Buffer Pool mutex
innodb_log_buffer_size=64M# InnoDB-specific
innodb_checksums=1 #Default is CRC32 in 5.7, very fast
innodb_use_native_aio=1
innodb_doublewrite= 1 #https://www.percona.com/blog/2016/05/09/percona-server-5-7-parallel-dou…
innodb_stats_persistent = 1
innodb_support_xa=0 #(We are read-only, but this option is deprecated)
innodb_spin_wait_delay=6 #(Processor and OS-dependent)
innodb_thread_concurrency=0
join_buffer_size=32K
innodb_flush_log_at_trx_commit=2
sort_buffer_size=32K
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_lru_scan_depth=4000
innodb_page_cleaners=4# perf special
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_purge_threads=4
innodb_max_purge_lag_delay=30000000
innodb_max_purge_lag=0
innodb_adaptive_hash_index=0 (depends on workload, always check)
MySQL Options for OLTP RW:
#Open files
table_open_cache = 8000
table_open_cache_instances = 16
query_cache_type = 0
join_buffer_size=32k
sort_buffer_size=32k
max_connections=16000
back_log=5000
innodb_open_files=4000#Monitoring
performance-schema=0#Percona Server specific
userstat=0
thread-statistics=0#InnoDB General
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1
innodb_numa_interleave=1
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_doublewrite=1
innodb_support_xa=1
innodb_checksums=1#Concurrency
innodb_thread_concurrency=144
innodb_page_cleaners=8
innodb_purge_threads=4
innodb_spin_wait_delay=12 Good value for RO is 6, for RW and RC is 192
innodb_log_file_size=8G
innodb_log_files_in_group=16
innodb_buffer_pool_size=128G
innodb_buffer_pool_instances=128 #to avoid wait on InnoDB Buffer Pool mutex
innodb_io_capacity=18000
innodb_io_capacity_max=36000
innodb_flush_log_at_timeout=0
innodb_flush_log_at_trx_commit=2
innodb_flush_sync=1
innodb_adaptive_flushing=1
innodb_flush_neighbors = 0
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_lru_scan_depth=4000
innodb_adaptive_hash_index=0
innodb_change_buffering=none #can be inserts, workload-specific
optimizer_switch="index_condition_pushdown=off" #workload-specific
MySQL SysBench parameters:
LD_PRELOAD=/data/sveta/5.7.14/lib/mysql/libjemalloc.so
/data/sveta/sbkk/bin/sysbench
[ --test=/data/sveta/sysbench/sysbench/tests/db/oltp_prepared.lua | --test=/data/sveta/sysbench/sysbench/tests/db/oltp_simple_prepared.lua ]
--db-driver=mysql --oltp-tables-count=8 --oltp-table-size=10000000
--mysql-table-engine=innodb --mysql-user=msandbox --mysql-password=msandbox
--mysql-socket=/tmp/mysql_sandbox5715.sock
--num-threads=$i --max-requests=0 --max-time=300
--percentile=0 [--oltp-read-only=on --oltp-skip-trx=on]
PostgreSQL pgbench parameters:
$ git clone https://github.com/postgrespro/pg_oltp_bench.git
$ cd pg_oltp_bench
$ make USE_PGXS=1
$ sudo make USE_PGXS=1 install
$ psql DB -f oltp_init.sql
$ psql DB -c "CREATE EXTENSION pg_oltp_bench;"
$ pgbench -c 100 -j 100 -M prepared -f oltp_ro.sql -T 300 -P 1 DB
$ pgbench -c 100 -j 100 -M prepared -f oltp_rw.sql -T 300 -P 1 DB
MySQL 5.7中的功能显着改善了性能：
- InnoDB: transaction list optimization
- InnoDB: Reduce lock_sys_t::mutex contention
- InnoDB: fix index->lock contention
- InnoDB: faster and parallel flushing
- MDL (Meta-Data Lock) scalability
- Remove THR_LOCK::mutex for InnoDB: Wl #6671
- Partitioned LOCK_grant
- Number of partitions is constant
- Thread ID used to assign partition
- Lock-free MDL lock acquisition for DML
Anastasia：这项研究的最初发现在Percona Live Amsterdam 2016上公布。更多的调查结果被添加到 Moscow HighLoad++ 2016的同一个演讲的第二版中。希望本次演讲的第三次演示将在Percona Live Open Source Database Conference 2017 in Santa Clara.上发布。年圣克拉拉数据库大会。
本文：http://pub.intelligentx.net/node/553
讨论：请加入知识星球或者小红圈【首席架构师圈】
- 175 次浏览
【Postgresql架构】Citus实时执行程序如何并行化Postgres查询
Citus有多个不同的执行程序，每个执行程序的行为都不同，以支持各种用例。对于许多概念而言，分布式SQL似乎必须是一个复杂的概念，但是其原理并不是火箭科学。在这里，我们将看几个有关Citus如何采用标准SQL并将其转换为以分布式形式运行以便可以并行化的示例。结果是您可以看到单节点数据库的查询性能提高了100倍或更多。
我们如何知道某物是分布式的还是单片？
在了解实时执行器的工作方式之前，值得对Citus执行器进行全面的复习。
当Citus收到查询时，我们首先查看它是否具有where子句的分片键（也称为分发列）。如果您要分拆诸如CRM应用程序之类的多租户应用程序，则可能会有一个org_id，您总是会限制查询。在这种情况下，只要org_id是where子句的一部分，我们就知道它的目标是单个分片，因此可以使用路由器执行程序。如果未使用该查询，我们会将查询拆分并跨节点并行发送给所有分片。
作为快速更新，Citus中的一个表是另一个表。如果您有一个表事件并想要分发它，则可以创建32个分片，这意味着我们可以轻松扩展到32个节点。如果您从2个节点开始，则每个节点包含32个分片。这意味着每个节点将一次接收16个查询，并且如果它有16个可用的内核，那么所有工作将并行完成，从而导致2个节点x 16个内核，或者说，与在单个内核上执行相比，速度提高了32倍。
对于后面的示例，我们将仅创建4个分片以简化它们，但是随着添加的分片和对应的内核的增加，事情几乎线性地扩展。
用SQL编写，用MapReduce思考
Citus对实时分析的支持是自从我们早期以来，人们就一直使用Citus的工作负载，这要归功于我们先进的查询并行化。结果就是您能够用标准SQL表示事物，并让Citus的分布式查询计划器完成重写查询的艰苦工作，从而为您提供出色的性能，而无需创建复杂的工程胶带。
深入研究一些示例，从count（*）开始
我们可以开始处理的最简单的查询是count（*）。对于count（*），我们需要从每个分片中获取一个count（*）。首先，针对事件表运行一个解释计划，以了解其运作方式：
QUERY PLAN ------------------------------------------------------------------------------------------------------- Aggregate (cost=0.00..0.00 rows=0 width=0) -> Custom Scan (Citus Real-Time) (cost=0.00..0.00 rows=0 width=0) Task Count: 4 Tasks Shown: One of 4 -> Task Node: host=ec2-18-233-232-9.compute-1.amazonaws.com port=5432 dbname=citus -> Aggregate (cost=11.62..11.63 rows=1 width=8) -> Seq Scan on events_102329 events (cost=0.00..11.30 rows=130 width=0) (8 rows) Time: 160.596 ms
查询中有一些注意事项。 首先是它使用的是Citus Real-Time执行程序，这意味着查询正在击中所有碎片。 第二个是任务是4个之一。该任务在所有节点上通常是相同的，但是由于它是纯粹的Postgres计划，可以根据数据分布和估算值进行更改。 如果要查看所有查询计划，则可以扩展输出以获取所有4个分片的任务。 最后，您具有针对该特定分片的查询计划本身。
让我们以集群示例为例：
如果我们要对该集群执行count（*），Citus将重新编写查询并将四个count（*）查询发送到每个分片。 然后将所得的计数返回给协调器以执行最终聚合：
性能远远超过count（*）
虽然count（*）很容易看出它是如何工作的，但是您可以执行更多操作。 如果要获得四个平均值并将它们平均在一起，则实际上并不会获得结果平均值。 相反，对于普通的Citus将执行sum（foo）和count（foo），然后在协调器上将sum（foo）/ count（foo）相除，以得出正确的结果。 最好的部分仍然可以编写AVG，Citus负责底层的复杂性。
除了汇总之外，Citus还可以告诉您何时加入并在本地执行这些加入。 让我们向事件表中添加另一个表：会话。 现在，对于每个事件，我们都将会话ID记录为其中的一部分，以便我们加入。 有了这两个表，我们现在想要一个查询，该查询将告诉我们会话的平均事件数，以及上周创建的会话：
SELECT count(events.*),
count(distinct session_id)
FROM events,
sessions
WHERE sessions.created_at >= now() - '1 week'::interval
AND sessions.id = events.session_id
在这两个表上都分配有会话ID的情况下，Citus会知道这些表在同一位置。 使用共置的表，Citus将重新编写查询以将连接向下推送到本地，从而不会通过网络发送太多数据。 结果是，我们将从每个分片（而不是所有原始数据）中将2条记录发送回协调器，从而大大缩短了分析查询时间。 内部重写的内容可能类似于：
SELECT count(events_01.*),
count(distinct session_id)
FROM events_01,
sessions_01
WHERE sessions_01.created_at >= now() - '1 week'::interval
AND sessions_01.id = events_01.session_id
SELECT count(events_02.*),
count(distinct session_id)
FROM events_02,
sessions_02
WHERE sessions_02.created_at >= now() - '1 week'::interval
AND sessions_02.id = events_02.session_id
...
分布式SQL不一定很困难，但是可以肯定很快
下推连接和并行化的好处是：
您不必通过网络发送太多数据，这比在内存中扫描要慢您可以一次利用系统中的所有内核，而不是在单个内核上运行查询您可以超出可以在一台计算机中装载多少内存/内核的限制
希望这次对Citus实时执行器的浏览简化了幕后工作的方式。 如果您想更深入地学习，请阅读我们的文档。
原文：https://www.citusdata.com/blog/2018/08/17/breaking-down-citus-real-time-executor/
本文：http://jiagoushi.pro/node/927
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 23 次浏览
【Postgresql架构】PGDay：全文搜索如何工作？
- 24 次浏览
【Postgresql架构】PostgreSQL和仅堆元组更新-第3部分
- 9 次浏览
【Postgresql架构】Postgresql BRIN 索引– 概览
- 23 次浏览
【Postgresql架构】Postgresql 数据库的近似算法
在较早的博客文章中，我写了关于如何将问题分解为MapReduce样式的方法可以如何为您提供更好的性能。当我们能够在集群中所有核心之间并行化工作负载时，我们发现Citus比单节点数据库快几个数量级。虽然计数（*）和平均数很容易分解成较小的部分，但我立即想到了一个问题，即计数不重复数，列表中的最高值或中位数是什么？
公认的是，在大型分布式设置中，确切的非重复计数更难解决，因为它需要在节点之间进行大量数据转换。 Citus确实支持不重复计数，但是在处理特别大的数据集时有时会很慢。任何中型到大型数据集的中位数都可能对最终用户完全禁止。幸运的是，几乎所有这些算法都有近似算法，可以提供足够接近的答案，并且具有令人印象深刻的性能特征。
HyperLogLog的近似唯一性
在某些类别的应用程序中，例如网络分析，物联网（物联网）和广告，计算某事物发生的不同次数是一个共同的目标。 HyperLogLog是PostgreSQL数据类型扩展，它允许您获取原始数据并将其压缩为一段时间内存在的唯一身份值。
将数据保存到HLL数据类型的结果是，星期一的值将为25，而星期二的值将为20。与原始数据相比，此数据压缩后的压缩量要大得多。但是真正令人赞叹的是，您可以然后合并这些存储桶，通过合并两个HyperLogLog数据类型，您可以返回星期一和星期二有25个唯一身份，因为星期二您有10个重复访客：
SELECT hll_union_agg(users) as unique_visitors FROM daily_uniques; unique_visitors ----------------- 35 (1 row)
于HyperLogLog可以通过这种方式拆分和组合，因此还可以跨Citus群集中的所有节点很好地并行化
使用TopN查找重要事项
我们通常在Web分析，广告应用程序和安全性/日志事件应用程序中发现的另一种计数形式是希望知道已发生的最主要的操作或事件集。 这可能是您在Google Analytics（分析）中看到的首页视图，也可能是事件日志中发生的主要错误。
TopN利用基础JSONB数据类型存储其所有数据。 但随后会维护一个列表，其中是最重要的项目以及有关这些项目的各种数据。 随着订单的改组，它会清除旧数据，从而使其现在必须维护所有原始数据的完整列表。
为了使用它，您将以与HyperLogLog类似的方式插入它：
# create table aggregated_topns (day date, topn jsonb); CREATE TABLE Time: 9.593 ms # insert into aggregated_topns select date_trunc('day', created_at), topn_add_agg((repo::json)->> 'name') as topn from github_events group by 1; INSERT 0 7 Time: 34904.259 ms (00:34.904)
在查询时，您可以轻松获取数据的前十名列表：
SELECT (topn(topn_union_agg(topn), 10)).* FROM aggregated_topns WHERE day IN ('2018-01-02', '2018-01-03'); ------------------------------------------------+----------- dipper-github-fra-sin-syd-nrt/test-ruby-sample | 12489 wangshub/wechat_jump_game | 6402 ...
不只是计数和列表
前面我们提到过，像中位数这样的运算可能会困难得多。 尽管扩展可能尚不存在，但未来可以支持这些操作。 对于中位数，存在多种不同的算法和方法。 可以应用于Postgres的两个有趣的方法：
- T-digest
-提供大约百分位数 - HDR (high dynamic range)
-提供更好的压缩效果，但只专注于前99％和更高的百分位数
如果答案能在数TB的数据范围内达到亚秒级响应，那么答案是否完全接近但又不能完全满足您的需求？ 以我的经验，答案通常是肯定的。
因此，下次您认为分布式设置中不可能实现某些功能时，请研究一下存在哪些近似算法。
原文：https://www.citusdata.com/blog/2019/02/28/approximation-algorithms-for-your-database/
本文：http://jiagoushi.pro/node/925
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 23 次浏览
【Postgresql架构】Postgres中关注者（只读副本）的用例
Citus将Postgres扩展为可水平扩展的数据库。水平扩展是指数据分散在多台计算机上，您不仅可以扩展存储，还可以扩展内存和计算，从而提供更好的性能。无需使用Citus之类的工具即可将PostgreSQL转换为分布式数据库，请确保您可以添加只读副本以进行扩展，但是您仍将维护数据的单个副本。当您遇到Postgres数据库的扩展问题时，添加只读副本并将某些流量分流到只读副本是降低速度的一种常见的创可贴，但这只是时间问题，直到它失效为止再进一步。而使用Citus，向外扩展数据库就像拖动滑块并重新平衡数据一样简单。
只读副本对水平可伸缩数据库是否仍然有用？
但这留下了一个问题，只读副本仍然有用吗？好吧，可以肯定。
在Citus Cloud（我们的完全托管数据库即服务）中，我们支持只读副本，在我们的情况下称为追随者。追随者群集利用了我们与叉式利用相同的许多基础灾难恢复基础架构，但是它们支持非常不同的用例集。
之前，我们讨论过如何在Citus Cloud中使用forks来将生产数据集转移到暂存阶段，以帮助测试迁移，重新平衡或SQL查询优化。叉子通常在短时间内用作一次性物品。相比之下，关注者通常是长期运行的Citus数据库集群，可以成为运行业务的关键机制，可帮助您在需要时获取见解。
追随者群集通常仅比主数据库群集落后几秒钟（如果有）
跟随者群集以异步方式从主Citus群集接收所有更新。追随者通常仅比主数据库集群落后几秒钟（如果有的话），尽管有时可能会滞后几分钟。关注者拥有您数据的完整副本，但位于单独的群集中。这意味着您将拥有数据的单独副本，并拥有自己的计算能力，内存和磁盘。
关于跟随者形成的有趣事实，您也可以跨区域创建它们。是否要在另一个区域中拥有数据库的完整副本，以便在发生灾难时可以进行故障转移？是否希望为不同地理位置的特定读取提供较低的延迟？跨区域的Citus追随者可以提供帮助。
追随者=有助于将分析工作负载与核心生产活动分开
那么追随者在Citus中什么时候有用？关注者对于可能需要较长时间运行的分析工作负载最有帮助。是否要针对您的整个数据集计算一些复杂的报告？从主数据库到其他数据仓库执行ETL，在该仓库中数据在进入之前已经清理和混淆了？
这些用例中的每一个对您的业务而言都可能很重要，但是长时间运行的SQL查询与数据库的主要工作负载争用相同的资源。这种性能竞争有时会导致数据库中主要工作负载的性能问题。通过将分析工作量拆分出来以在Citus追随者群集上运行，您可以为内部数据分析人员提供所需的访问权限，而不必让他们接受相同的生产代码审查流程，同时又可以确保生产数据库的安全。
只读副本和关注者仍然有位置
虽然使用只读副本来扩展您的主应用程序可能会带来各种不必要的复杂性，并且要比解决的问题产生更多的工作，但是只读副本仍然有自己的位置。当使用对Postgres的Citus扩展来解决扩展性能问题时，您可以创建跟随者形式（也称为只读副本）以在内部提供对数据的访问，而不必冒生产可用性的风险。这是一个强大的组合。
原文：https://www.citusdata.com/blog/2018/09/19/use-cases-for-read-replicas/
本文：http://jiagoushi.pro/node/923
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 12 次浏览
【Postgresql架构】使用PostgreSQL中的JSONB数据类型加快操作
从版本9.4开始，PostgreSQL在使用JSON数据的二进制表示jsonb时提供了显着的加速，这可以为您提供增加性能所需的额外优势。
什么是jsonb
由PostgreSQL文档定义的数据类型json和jsonb几乎相同;关键的区别在于json数据存储为JSON输入文本的精确副本，而jsonb以分解的二进制形式存储数据;也就是说，不是ASCII / UTF-8字符串，而是二进制代码。
这有一些直接的好处：
- 效率更高，
- 加工速度明显加快
- 支持索引（这可能是一个重要的优势，我们稍后会看到），
- 更简单的模式设计（用jsonb列替换实体 - 属性 - 值（EAV）表，可以查询，索引和连接，从而使性能提高到1000倍！）
还有一些缺点：
- 输入稍慢（由于增加的转换开销），
- 它可能需要比普通json更多的磁盘空间，因为更大的表占用空间，尽管并非总是如此，
- 由于缺乏统计信息，某些查询（尤其是聚合查询）可能会变慢。
最后一个问题背后的原因是，对于任何给定的列，PostgreSQL保存描述性统计信息，例如不同和最常见值的数量，NULL条目的分数，以及 - 对于有序类型 - 数据分布的直方图。当信息作为JSON字段输入时，所有这些都将不可用，并且您将遭受严重的性能损失，尤其是在大量JSON字段之间聚合数据（COUNT，AVG，SUM等）时。
为避免这种情况，您可以考虑存储稍后可能在常规字段上汇总的数据。
有关此问题的进一步评论，您可以阅读Heap的博客文章何时在PostgreSQL架构中避免使用JSONB。
用例：书籍条目
让我们使用带有书籍条目的玩具模型来说明在PostgreSQL中使用JSON数据时的一些基本操作。
如果您使用json或jsonb，本节中的操作将基本相同，但让我们回顾它们以刷新我们可以用JSON做什么，并在我们看到jsonb好吃之后立即设置我们的用例。
在表中定义列
很简单，我们使用jsonb数据类型指定数据列：
CREATE TABLE books ( book_id serial NOT NULL, data jsonb );
插入JSON数据
要将数据输入books表，我们只需将整个JSON字符串作为字段值传递：
INSERT INTO books VALUES (1, '{"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"], "published": false}'); INSERT INTO books VALUES (2, '{"title": "Influence", "genres": ["Marketing & Sales", "Self-Help ", "Psychology"], "published": true}'); INSERT INTO books VALUES (3, '{"title": "The Dictator''s Handbook", "genres": ["Law", "Politics"], "authors": ["Bruce Bueno de Mesquita", "Alastair Smith"], "published": true}'); INSERT INTO books VALUES (4, '{"title": "Deep Work", "genres": ["Productivity", "Reference"], "published": true}'); INSERT INTO books VALUES (5, '{"title": "Siddhartha", "genres": ["Fiction", "Spirituality"], "published": true}');
查询数据
我们现在可以查询JSON数据中的特定键：
SELECT data->'title' AS title FROM books;
这将从JSONB数据中提取的标题作为列返回：
title
---------------------------
"Sleeping Beauties"
"Influence"
"The Dictator's Handbook"
"Deep Work"
"Siddhartha"
(5 rows)
过滤结果
您也可以使用WHERE子句但通过JSON键以正常方式过滤结果集：
SELECT * FROM books WHERE data->'published' = 'false';
在这种情况下，返回原始JSON数据：
book_id | data
---------+-------------------------------------------------------------------------------------------------
1 | {"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"], "published": false}
(1 row)
展开数据
这是一个重要的问题，因为它将使我们能够在处理关系数据库时使用我们熟悉的聚合函数，但是在JSON数据的反直觉环境中也是如此。
SELECT jsonb_array_elements_text(data->'genres') AS genre FROM books WHERE book_id = 1;
这会将JSON数组扩展为一列：
genre
----------
Fiction
Thriller
Horror
(3 rows)
特殊的jsonb功能
除了效率之外，还有其他方法可以让您以二进制形式存储JSON。
其中一个增强功能是GIN（广义倒置索引）索引以及随附的新品牌运营商。
检查遏制（Checking Containment）
Containment测试一个文档（一个集合或一个数组）是否包含在另一个文档中。这可以使用@>运算符在jsonb数据中完成。
例如，数组[“Fiction”，“Horror”]包含在数组[“Fiction”，“Thriller”，“Horror”]中（其中t代表true）：
SELECT '["Fiction", "Thriller", "Horror"]'::jsonb @> '["Fiction", "Horror"]'::jsonb;
t
然而，相反的是["Fiction", "Thriller", "Horror"]包含在["Fiction", "Horror"]中，是错误的：
SELECT '["Fiction", "Horror"]'::jsonb @> '["Fiction", "Thriller", "Horror"]'::jsonb;f
使用这个原则，我们可以轻松检查单一书籍类型：
SELECT data->'title' FROM books WHERE data->'genres' @> '["Fiction"]'::jsonb;"Sleeping Beauties" "Siddhartha"
通过传递一个数组（注意它们的关键顺序根本不重要），或者同时使用多个类型：
SELECT data->'title' FROM books WHERE data->'genres' @> '["Fiction", "Horror"]'::jsonb;"Sleeping Beauties"
此外，从9.5版开始，PostgreSQL引入了检查顶级键和空对象包含的功能：
SELECT '{"book": {"title": "War and Peace"}}'::jsonb @> '{"book": {}}'::jsonb;t
检查存在
作为包含的变体，jsonb还有一个存在运算符（？），可用于查找是否存在对象键或数组元素。
在这里，让我们计算出输入作者字段的书籍：
SELECT COUNT(*) FROM books WHERE data ? 'authors';
在这种情况下只有一个（“独裁者的手册”）：
count
-------
1
(1 row)
创建指数/索引
让我们花点时间提醒自己索引是关系数据库的关键组成部分。没有它们，每当我们需要检索一条信息时，数据库就会扫描整个表格，这当然效率很低。
jsonb相对于json数据类型的显着改进是能够索引JSON数据。
我们的玩具示例只有5个条目，但如果它们是数千或数百万个条目，我们可以通过构建索引来减少一半以上的搜索时间。
例如，我们可以索引出版的书籍：
CREATE INDEX idx_published ON books (data->'published');
由于idx_published索引，这个简单的索引将自动加速我们在已发布的书籍上运行的所有聚合函数（WHERE data - >'published'='true'）。
事实上，我们可以 - 并且可能应该在DB大小增加时 - 索引在过滤结果时要在WHERE子句上使用的任何内容。
注意事项
切换到jsonb数据类型时，您需要考虑一些技术细节。
jsonb更严格，因此，除非数据库编码为UTF8，否则它不允许非ASCII字符（U + 007F以上的字符）的Unicode转义。它还拒绝NULL字符（\ u0000），它不能用PostgreSQL的文本类型表示。
它不会保留空白区域，它会剥离JSON字符串中的前导/滞后空白区域以及JSON字符串中的空白区域，所有这些都只会使代码不整齐（毕竟这对你来说可能不是件坏事） 。）
它不保留对象键的顺序，处理键的方式与Python字典中的处理方式非常相似 - 未排序。如果您依赖JSON密钥的顺序，则需要找到解决此问题的方法。
最后，jsonb不会保留重复的对象键（这可能不是一件坏事，特别是如果你想避免数据中的歧义），只存储最后一个条目。
结论
PostgreSQL文档建议大多数应用程序应该更喜欢将JSON数据存储为jsonb，因为我们已经看到有显着的性能增强和仅有的小警告。
jsonb带来的功能非常强大，您可以很好地处理关系数据，就像在常规RDBMS中一样，但是所有这些都在JSON中，并且在性能上有非常显着的提升，结合了NoSQL解决方案的实用性。 RDBMS的强大功能。
切换到jsonb时的主要缺点是遗留代码，例如，可能依赖于对象密钥的排序;这是需要更新以按预期工作的代码。并且说明显而易见的是，作为9.4版中引入的一个特性，jsonb不是向后兼容的，你需要使用的jsonb关键字设置JSON表将破坏传统平台上的SQL代码。
最后，请注意我已经涵盖了指数及其运算符的一些典型用法;有关更多详细信息和示例，请查看官方PostgreSQL文档中的jsonb索引以及JSON函数和运算符。
原文：https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/
本文：
讨论：请加入知识星球或者小红圈【首席架构师圈】
- 34 次浏览
【Postgresql架构】用MapReduce的方式思考，但使用SQL
对于那些考虑使用Citus的人来说，如果您的用例看起来很合适，我们通常愿意花一些时间与您一起帮助您了解Citus数据库及其可以提供的性能类型。我们通常与我们的一位工程师进行大约两个小时的配对，以完成此操作。我们将讨论架构，加载一些数据并运行一些查询。如果最后有时间，将相同的数据和查询加载到单节点Postgres中并查看我们如何进行比较总是很有趣。在看了多年之后，我仍然很高兴看到单节点数据库的性能提高了10到20倍，在高达100倍的情况下也是如此。
最好的部分是，它不需要对数据管道进行大量的重新架构。它所要做的只是一些数据建模以及与Citus的并行化。
第一步是分片
我们之前已经讨论过这一点，但是获得这些性能提升的首要关键是Citus将您的数据隐藏在更小的，更易于管理的部分。这些碎片（是标准Postgres表）分布在多个物理节点上。这意味着您可以从系统中获得更多的集体能力。当您定位单个分片时，它非常简单：查询被重新路由到基础数据，一旦获得结果，它就会返回它们。
用MapReduce的方式思考
MapReduce已经存在了很多年，并由Hadoop普及。关于大规模数据的问题是为了从中获得及时的答案，您需要对问题进行分解并并行进行操作。或者，您会找到一个非常快的系统。使用更大，更快的设备的问题在于，数据增长超过了硬件改进的速度。
MapReduce本身是一个框架，用于拆分数据，根据需要将数据改组到节点，然后在重新组合结果之前对数据的子集执行工作。让我们举一个例子，例如累计总浏览量。如果我们想在此基础上利用MapReduce，我们会将浏览量分成4个单独的存储桶。我们可以这样做：
for i = 1 to 4:
for page in pageview:
bucket[i].append(page)
现在，我们将有4个存储桶，每个存储桶都具有一组网页浏览量。从这里我们可以执行许多操作，例如搜索以找到每个存储桶中最近的10个，或计算每个存储桶中的综合浏览量：
for i = 1 to 4:
for page in bucket:
bucket_count[i]++
现在，通过合并结果，我们可以获得页面浏览总数。如果将工作分配到四个不同的节点，则与使用一个节点的所有计算来执行计数相比，可以看到性能大约提高了4倍。
MapReduce作为一个概念
MapReduce在Hadoop生态系统中广为人知，但您不必跳入Java来利用。 Citus本身有多个不同的执行器来处理各种工作负载，我们的实时执行器实质上与成为MapReduce执行器是同义的。
如果您在Citus中有32个分片并运行SELECT count（*），我们将其拆分并运行多个计数，然后将最终结果汇总到协调器上。但是，除了计数（*）以外，您还可以做更多的事情，而平均值呢。对于平均值，我们从所有节点和计数中获得总和。然后，我们将总和与计数加在一起，并在协调器上进行最终数学运算，或者您可以将每个节点的平均值求和。实际上，它是：
SELECT avg(page), day FROM pageviews_shard_1 GROUP BY day; average | date ---------+---------- 2 | 1/1/2019 4 | 1/2/2019 (2 rows) SELECT avg(page), day FROM pageviews_shard_2 GROUP BY day; average | date ---------+---------- 8 | 1/1/2019 2 | 1/2/2019 (2 rows)
当我们将以上结果输入表中，然后取它们的平均值时，我们得到：
average | date ---------+---------- 5 | 1/1/2019 3 | 1/2/2019 (2 rows)
请注意，在Citus中，您实际上不必运行多个查询。 在后台，我们的实时执行器可以处理它，实际上就像运行一样简单：
SELECT avg(page), day FROM pageviews GROUP BY day; average | date ---------+---------- 5 | 1/1/2019 3 | 1/2/2019 (2 rows)
对于大型数据集，MapReduce中的思路为您提供了无需费力即可获得出色性能的途径。 最好的部分可能是您不必编写数百行来完成它，您可以使用与编写相同的SQL来完成。 在幕后，我们负责繁重的工作，但是很高兴知道它在幕后如何工作。
原文：https://www.citusdata.com/blog/2019/02/21/thinking-in-mapreduce-but-with-sql/
本文：http://jiagoushi.pro/node/926
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 23 次浏览
【Postgresql架构】解释CREATE INDEX CONCURRENTLY
- 31 次浏览
【Postgresql架构】逻辑复制内幕
- 30 次浏览
【Postgresql架构】部分索引（ Partial Indexes）如何影响Postgres中的UPDATE性能
部分索引是节省磁盘空间和提高记录查找性能的好方法。共同的规则是“用它当你能负担得起”——换句话说,如果你所有的查询涉及到一些过滤器,这是通常被认为是一个好主意,以包括过滤索引定义,以减少其规模和改善其性能(指数越小,越快就会执行相应的索引扫描或IndexOnlyScan)。
但是，部分索引是否总是提供更好的性能?好,让我们看看。
举个简单的例子:
create table asset(
id bigserial primary key,
owner_id int8 not null,
name text not null,
price int8
);
假设在这个表格中我们存储了一些资产的信息，让我们用一些样本数据来填充它:
insert into asset(
owner_id,
name,
price
)
select
i,
round(random()*100000)::text,
round(random() * 10000)
from generate_series(1, 600000) _(i);
再加入更多的样本数据，这次的价格“未知”:
insert into asset( owner_id, name, price ) select i, round(random()*100000)::text, null -- price is unknown (yet) from generate_series(600001, 1000000) _(i); vacuum analyze asset;
现在我们有100万条记录，其中40%的价格未知(无效)。
价格会不时变化，因此任何记录都可能被更新为新的、经过修正的价格值。
接下来，考虑这样一种情况:我们需要快速找到属于某个特定用户的所有资产，但我们从不对价格未知的记录感兴趣:
select *
from asset
where
owner_id = :owner_id
and price is not null;
我们需要使用什么索引?我们很自然地认为，这部分指数将完美地满足我们的需要:
create index i_asset_price_partial
on asset
using btree(owner_id)
where price is not null;
但是拥有最好的表现真的有帮助吗?
这得视情况而定。
首先，这样的索引会小得多，即常规的全表索引:
create index i_asset_price_full
on asset
using btree(owner_id);
让我们来比较一下它们的大小:
test=# \x Expanded display is on.test=# \di+ i_asset_price_partial -[ RECORD 1 ]---------------------- Schema | public Name | i_asset_price_partial Type | index Owner | nikolay Table | asset Size | 13 MB Description |test=# \di+ i_asset_price_full -[ RECORD 1 ]-------------- Schema | public Name | i_asset_price_full Type | index Owner | nikolay Table | asset Size | 21 MB Description |
正如预期的那样，全表索引的大小更高——21 MB vs 13 MB，因此比部分索引大60%左右。这是您可以决定的时候—好吧，我最好使用部分索引(就像我在优化的一个Postgres实例中所做的那样)。但是不要着急，等一会儿。
那么SELECT性能呢?让我们用pgbench快速检查一下，这是Postgres的本地基准测试工具:
echo "\set owner_id random(1, 1 * 1000000)" > selects.bench echo "select from asset where owner_id = :owner_id and price is not null;" >> selects.bench pgbench -n -T 30 -j 4 -c 12 -M prepared -f selects.bench -r test
在没有索引的情况下，我的笔记本只有13.12 TPS(每秒事务数):
$ pgbench -n -T 30 -j 4 -c 12 -M prepared -r test -f selects.bench
transaction type: selects.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 397
latency average = 915.767 ms
tps = 13.103765 (including connections establishing)
tps = 13.113909 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.003 \set owner_id random(1, 1 * 1000000)
908.755 select from asset where owner_id = :owner_id and price is not null;
连续扫描不适合在干草堆里找针。这里没有惊喜。
全表索引(单独定义，不包含部分索引):
$ pgbench -n -T 30 -j 4 -c 12 -M prepared -r test -f selects.bench
transaction type: selects.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 779801
latency average = 0.462 ms
tps = 25963.230818 (including connections establishing)
tps = 25972.470987 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set owner_id random(1, 1 * 1000000)
0.460 select from asset where owner_id = :owner_id and price is not null;
和部分索引(再次定义，没有全文):
$ pgbench -n -T 30 -j 4 -c 12 -M prepared -r test -f selects.bench
transaction type: selects.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 817490
latency average = 0.440 ms
tps = 27242.705122 (including connections establishing)
tps = 27253.100588 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set owner_id random(1, 1 * 1000000)
0.439 select from asset where owner_id = :owner_id and price is not null;
可以看到，使用部分索引的select要比使用全表索引的select快一点(~5%)。这是我们所期望的。
现在让我们看看更新性能的不同。我们将只接触那些已经定义了价格的记录，以保持null(40%)的数量不变，以防我们想要做更多的实验:
echo "\set id random(1, 1 * 600000)" > updates.bench echo "update asset set price = price + 10 where id = :id;" >> updates.benchpsql test -c 'vacuum full analyze asset;' && \ psql test -c 'select pg_stat_reset();' >> /dev/null && \ pgbench -n -T 30 -j 4 -c 12 -M prepared -r test -f updates.bench
当除主键外没有索引时:14553 tps。
全文索引:14371 tps。
部分索引:12198 tps。
这看起来可能令人惊讶——部分索引显著地(约14%)减慢了更新速度!
为什么如此?要回答这个问题，让我们看看Postgres内部统计信息pg_stat_user_tables。注意到我们在运行pgbench进行更新之前调用了pg_stat_reset()函数吗?这是为了重置整个Postgres集群的统计数据并收集新的数据。现在，如果我们用这个查询每次实验后检查这个查询的结果:
select
n_tup_upd,
n_tup_hot_upd,
round(100 * n_tup_hot_upd::numeric / n_tup_upd, 2) as hot_ratio
from pg_stat_user_tables
where relname = 'asset';
我们会看到这样的情况:
-- no indexes except PK
n_tup_upd | n_tup_hot_upd | hot_ratio
-----------+---------------+-----------
436808 | 409220 | 93.68-- full-table index
n_tup_upd | n_tup_hot_upd | hot_ratio
-----------+---------------+-----------
431473 | 430807 | 99.85-- partial index
n_tup_upd | n_tup_hot_upd | hot_ratio
-----------+---------------+-----------
366089 | 0 | 0.00
这些数字很清楚——在部分索引的情况下，我们将完全失去热更新。HOT表示仅堆元组，这是PostgreSQL执行元组更新的内部技术。虽然在PostgreSQL的主文档中没有详细的说明，但是你可以在README中找到详细的解释。来自Postgres源文件的热(或阅读本文:“使用热更新提高更新查询的速度”)。
简而言之,如果在更新期间,新老元组(行你更新的版本)都位于同一页面的内存,这更新不修改任何索引列,特殊类型的优化会发生,这将允许Postgres节省时间,首先,因为它不会改变索引条目。
但是这里的索引列究竟是什么呢?自述文件。热解释:
HOT解决了一个受限但有用的特殊情况:一个tuple以不改变其索引列的方式重复更新。(这里的“索引列”是指在索引定义中引用的任何列，包括例如在部分索引谓词中测试但不存储在索引中的列。)
在我们的例子中，索引依赖于price列，它是我们的部分索引谓词，一旦我们更改它，Postgres就不能使用HOT，因此我们的更新(和删除)通常会变慢。
我相信这可以在Postgres内部进行优化:如果tuple中的旧值和新值都有price不为空，这意味着没有理由触及索引。但是Postgres内部没有这样的优化(到2018年3月，最新版本是Postgres 10)，所以开发人员在优化数据库模式和查询时必须牢记这一点。
我希望你不会认为部分索引是不利于更新性能——这并不坏,但是你需要注意你使用的组列索引定义和记住这个增益之间的权衡选择性能和更新性能可能的损失。
这只是众多例子中的一个，在非专业人士的眼中，数据库优化过程是多么棘手——我经常听说Postgres DBA的工作是一种“魔法”。如果您需要处理数百或数千个表、索引和其他DB对象，并试图玩打地鼠游戏，那么猜测哪些更改对您最有帮助。在优化一个SQL查询时，通常会损害其他查询的性能
我相信在不久的将来会出现新的自动化数据库管理工具，帮助dba和开发人员进行性能优化。
同时，我建议使用我的新项目postgres_dba——它是一个SQL报告集合，绑定到一个方便的工具集，可以在psql中以交互模式运行。这是一个名为“3 - Load Profile”的特殊报告，它允许您查看每个表中选择、插入、更新和删除了多少元组，以及所有更新中热更新的比例:
来自postgres_dba工具集的报告“3 - Load Profile”显示了数据库中每个表的热更新比率
这是控制表的更新性能的一种非常基本的方法。如果您看到一个表的热更新率很低，那么为了理解为什么需要检查这个表的所有索引定义—它们是否允许热更新，或者更新查询是否涉及参与某些索引定义的列?
原文：https://medium.com/@postgresql/how-partial-indexes-affect-update-performance-in-postgres-d05e0052abc
本文：http://jiagoushi.pro/how-partial-indexes-affect-update-performance-postgres
讨论：请加入知识星球【首席架构师圈】或者微信圈子【首席架构师圈】
- 69 次浏览
【Postgres】最有用的Postgres扩展：pg_stat_statements
扩展能够扩展，更改和推进Postgres的行为。 怎么样？ 通过挂钩到低级的Postgres API挂钩。 可以水平扩展Postgres的开源Citus数据库本身是作为PostgreSQL扩展实现的，这使Citus可以与Postgres版本保持最新，而不会像其他Postgres fork那样落后。 尽管我想更深入地研究最有用的Postgres扩展：pg_stat_statements，但我之前已经写过各种扩展类型。
你看，我刚从FOSDEM回来。 FOSDEM是在布鲁塞尔举行的年度免费开源软件会议，在活动中，我在PostgreSQL开发室中发表了有关Postgres扩展的演讲。 到今天结束时，Postgres开发室中进行的一半以上的讨论都提到了pg_stat_statements：
如果您使用Postgres，但尚未使用pg_stat_statements，则必须将其添加到工具箱中。而且，即使您很熟悉，也可能值得重温。
pg_stat_statements入门
Pg_stat_statements是所谓的contrib扩展名，可以在PostgreSQL发行版的contrib目录中找到。这意味着它已经随Postgres一起提供了，您无需从源代码构建它或安装软件包。如果尚未启用数据库，则可能必须启用它。这很简单：
CREATE EXTENSION pg_stat_statements;
如果您在主要的云提供商上运行，则很有可能他们已经为您安装并启用了它。
一旦安装了pg_stat_statements，它就会开始悄悄地在后台运行。 Pg_stat_statements记录针对您的数据库运行的查询，从中删除一些变量，然后保存有关该查询的数据，例如花费了多长时间以及基础读/写发生了什么。
注意：它不会保存每个查询，而是对其进行参数化，然后保存汇总结果
让我们来看几个示例。假设我们执行以下查询：
SELECT order_details.qty, order_details.item_id, order_details.item_price FROM order_details, customers WHERE customers.id = order_details.customer_id AND customers.email = 'craig@citusdata.com'
它将查询转换为：
SELECT order_details.qty, order_details.item_id, order_details.item_price FROM order_details, customers WHERE customers.id = order_details.customer_id AND customers.email = '?'
如果这是我在应用程序中经常执行的查询，以获取诸如零售订单历史记录之类的订单详细信息，那么它不会节省我为每个用户运行该订单的频率，而是节省了汇总视图。
看数据
从这里我们可以查询pg_stat_statements的原始数据，我们将看到类似以下内容：
SELECT * FROM pg_stat_statements; userid | 16384 dbid | 16388 query | select * from users where email = ?; calls | 2 total_time | 0.000268 rows | 2 shared_blks_hit | 16 shared_blks_read | 0 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 ...
使用pg_stat_statements提取见解
现在，这里有大量有价值的信息，作为高级用户，有时它们都可以证明是有价值的。但是，即使没有开始理解数据库的内部结构，您仍然可以通过以某些方式查询pg_stat_statements来获得一些真正强大的见解。通过查看total_time和每个查询被调用一次的次数，我们可以非常快速地查看哪些查询经常运行以及它们平均消耗了多少：
SELECT (total_time / 1000 / 60) as total, (total_time/calls) as avg, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;
您可以通过多种不同的方式对此进行过滤和排序，您可能只想关注运行1000多次的查询。或平均超过100毫秒的查询。上面的查询向我们显示了数据库消耗的总时间（以分钟为单位）以及平均时间（以毫秒为单位）。通过上面的查询，我会得到类似以下内容的信息：
total | avg | query --------+--------+------------------------- 295.76 | 10.13 | SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows)
根据经验，我知道快速获取记录时，PostgreSQL应该能够在1ms内返回。鉴于此，我可以开始优化工作。在上面的内容中，我看到将第一个查询降低到1ms会有所改善，但是优化第二个查询将对整个系统的性能产生更大的提升。
特别说明：如果要构建多租户应用，则可能不希望pg_stat_statements参数化tenant_id。为了解决这个问题，我们构建了citus_stat_statements来为每个租户提供见解。
原文：https://www.citusdata.com/blog/2019/02/08/the-most-useful-postgres-extension-pg-stat-statements/
本文：http://jiagoushi.pro/node/930
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 55 次浏览
【Postgres架构】Postgres中的物化视图与汇总表
多年来，物化视图一直是Postgres期待已久的功能。他们最终到达了Postgres 9.3，尽管当时很有限。在Postgres 9.3中，当刷新实例化视图时，它将在刷新时在表上保持锁定。如果您的工作量是非常繁忙的工作时间，则可以工作，但是如果您要为最终用户提供动力，那么这将是一个大问题。在Postgres 9.4中，我们看到了Postgres实现了同时刷新实例化视图的功能。现在，我们已经完全烘焙了物化视图的支持，但即使如此，我们仍然看到它们可能并不总是正确的方法。
什么是视图view？
对于那些不是数据库专家的人，我们将做一点备份。要了解什么是实体化视图，我们首先来看一个标准视图。视图是已定义的查询，您可以像表一样对其进行查询。当您具有通常用于某些标准报表/构建块的复杂数据模型时，视图特别有用。稍后我们将介绍一个实例化视图。
视图非常适合简化复杂SQL的复制/粘贴。缺点是每次执行视图时都会重新计算结果。对于大型数据集，这可能会导致扫描大量数据，使缓存无效，并且通常速度较慢。输入实例化视图
物化你的视图
让我们从一个可能包含大量原始数据的示例架构开始。在这种情况下，一个非常基本的网络分析工具会记录综合浏览量，发生时间和用户的会话ID。
CREATE TABLE pageviews ( id bigserial, page text, occurred_at timestamptz, session_id bigint );
基于这些原始数据，有很多不同的视图可能非常普遍。而且，如果我们有一个实时仪表板，我们将为它提供动力，因为它可能花费很长时间来查询原始数据，因此很快变得不可行。相反，我们可以对物化视图进行一些汇总：
CREATE MATERIALIZED VIEW rollups AS SELECT date_trunc('day') as day, page, count(*) as views FROM pageviews GROUP BY date_trunc('day'), page;
对于每天至少浏览一次的页面，这将为我们每天提供1条记录。
对于每天晚上批处理的事情，可以处理前一天的事情。但是对于面对客户的事情，您可能不希望等到一天结束后再提供有关网页浏览量如何进行分析的信息。当然，您可以定期刷新一次：
refresh materialized view rollups;
这种刷新方式的缺点是每次刷新时都会重新计算当天的总数，这实际上是在进行不必要的处理。
为了可扩展性增量汇总
另一种方法是使用upsert，它使我们能够增量汇总数据而不必重新处理所有基础数据。 Upsert本质上是创建或更新。为此，我们将创建一个表而不是物化视图，然后在其上施加唯一约束：
CREATE TABLE ( day as timestamptz, page text, count as bigint, constraint unq_page_per_day unique (day, page) );
现在开始汇总，我们将执行以下操作：
INSERT INTO rollups SELECT date_trunc('day') as day, page, count(*) as views FROM pageviews GROUP BY date_trunc('day'), page;
这基本上与我们的物化视图相同。但是由于我们的独特限制，当遇到已经插入的记录时，插入会出错。为了完成这项工作，我们将调整查询以完成两件事。一项我们将只处理新记录，另一项我们将使用upsert语法。
为了处理新记录，我们将保留上次停止记录的记录，仅处理新记录。我们在本文中概述了一组方便使用的函数/表。使用适当的函数和表格来跟踪我们上次中断的位置，现在我们将查询更新为仅汇总自上次处理后的数据。然后，我们将其与upsert结合在一起。 upsert将尝试插入当天/页面的任何新记录，如果已经看到这些值，则将增加它们：
INSERT INTO rollups SELECT day, page, count(*) as views FROM pageviews WHERE event_id > e GROUP BY day, page ON CONFLICT (day, page) DO UPDATE SET views = views + EXCLUDED.views;
物化视图与汇总表哪个正确？
物化视图是一种非常简单直接的方法。它们的易用性使它们成为快速简便的事情的理想选择。但是，对于具有较大活动负载的较大数据集和数据库，仅处理上一次汇总的净新数据可以更有效地利用资源。哪种方法最合适取决于时间和系统资源。尽管如您所见，汇总方法仅需要一点点努力，并且可以进一步扩展。
原文：https://www.citusdata.com/blog/2018/10/31/materialized-views-vs-rollup-tables/
本文：http://jiagoushi.pro/node/928
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 24 次浏览
【Postgres架构】Postgres和Netdata：适用于autovacuum和pg_stat_bgwriter
- 11 次浏览
【Postgres架构】为什么RDBMS是分布式数据库的未来，请参见Postgres和Citus
大约10年前，我加入了Amazon Web Services，在那里我第一次看到了在分布式系统中进行权衡的重要性。在大学里，我已经了解了一致性和可用性之间的权衡（CAP定理），但实际上，频谱要比这深得多。任何设计决策都可能涉及延迟，并发性，可伸缩性，耐用性，可维护性，功能性，操作简便性以及系统其他方面之间的权衡，而这些权衡会对应用程序的功能和用户体验产生有意义的影响，并且即使是业务本身的有效性。
也许在权衡需求最明显的分布式系统中最具挑战性的问题是构建分布式数据库。当应用程序开始需要可以在许多服务器上扩展的数据库时，数据库开发人员开始做出极端的权衡。为了在许多节点上实现可伸缩性，分布式键值存储（NoSQL）抛弃了传统关系数据库管理系统（RDBMS）提供的丰富功能集，包括SQL，联接，外键和ACID保证。由于每个人都想要可伸缩性，因此RDBMS消失只是时间问题，对吗？实际上，关系数据库继续主导着数据库领域。这就是为什么：
在分布式系统（或任何系统）中进行权衡时，要考虑的最重要方面是开发成本。
数据库软件所做出的权衡将对应用程序的开发成本产生重大影响。在高级应用程序中处理需要可用性，可靠性和性能的数据是一个固有地需要解决的问题。成功解决每个小问题所需的工时数量可能很大。幸运的是，数据库可以解决许多这些子问题，但是数据库开发人员也面临成本问题。实际上，要使数据库足以满足大多数应用程序的功能，保证和性能，就需要数十年的时间。那就是建立关系数据库如PostgreSQL和MySQL的地方。
在Citus Data，我们从不同角度解决了数据库可伸缩性的需求。我和我的团队在过去的几年中花费了很多时间将已建立的RDBMS转换为分布式数据库，而又不会失去其强大功能或从基础项目中分叉。通过这样做，我们发现RDBMS是构建分布式数据库的理想基础。
通过在RDBMS之上构建来降低应用程序开发成本
使RDBMS对开发应用程序（尤其是开源RDBMS，尤其是云RDBMS）如此吸引人的原因在于，您可以有效地利用数十年来对RDBMS进行的工程投资，并利用这些RDBMS功能。您的应用，降低了开发成本。
RDBMS为您提供：
- 围绕数据完整性和持久性的有意义的保证
- 极大的灵活性来操纵和查询数据
- 最先进的算法和数据结构，可在各种工作负载下获得高性能。
这些功能几乎对任何非平凡的应用都很重要，但是要花很长时间才能开发。另一方面，某些应用程序的工作量对于单台计算机来说太过苛刻，因此需要水平可伸缩性。
许多新的分布式数据库正在开发中，并且正在分布式键值存储（“ NewSQL”）之上实现RDBMS功能，例如SQL。尽管这些较新的数据库可以使用多台计算机的资源，但是在SQL支持，查询性能，并发性，索引，外键，事务，存储过程等方面，它们仍远未建立在关系数据库系统上。您遇到许多要在应用程序中解决的复杂问题。
许多大型互联网公司采用的替代方法是RDBMS的手动，应用程序层分片（通常是PostgreSQL或MySQL）。手动分片意味着有许多RDBMS节点，并且应用程序会根据某种条件（例如，用户ID）决定连接到哪个节点。应用程序本身负责如何处理数据放置，架构更改，查询多个节点，复制表等，因此，如果执行手动分片，最终将在应用程序中实现自己的分布式数据库，这可能甚至更多。昂贵。
幸运的是，有一种方法可以解决开发成本难题。
PostgreSQL已有数十年的发展历史，其令人难以置信的重点是代码质量，模块化和可扩展性。这种可扩展性提供了一个独特的机会：无需分叉就可以将PostgreSQL转换为分布式数据库。这就是我们构建Citus的方式。
Citus：成为世界上最先进的分布式数据库
大约5年前，当我加入一家名为Citus Data的初创公司时，我为在竞争激烈的市场中建立高级分布式数据库而无任何现有基础架构，品牌知名度，进入市场，资本或大量工程师的挑战感到沮丧 。 仅开发成本就似乎是无法克服的。 但是，就像应用程序开发人员利用PostgreSQL来构建复杂的应用程序一样，我们利用PostgreSQL来构建……分布式PostgreSQL。
我们创建了Citus，这是开源的PostgreSQL扩展，而不是从头开始创建分布式数据库，它以提供水平扩展的方式透明地分发表和查询，但是应用程序开发人员需要具备所有PostgreSQL功能才能成功。
通过使用在计划查询时Postgres调用的内部挂钩，我们能够将分布式表的概念添加到Postgres。
分布式表的分片存储在具有所有现有功能的常规PostgreSQL节点中，Citus发送常规SQL命令以查询分片，然后合并结果。 我们还添加了参考表的概念，该参考表可在所有节点上复制，因此可以通过任何列与分布式表连接。 通过进一步增加对分布式事务，查询路由，分布式子查询和CTE，序列，更新等的支持，我们达到了最先进的PostgreSQL功能可以使用的规模，但现在已经可以大规模使用。
Citus相对来说还很年轻，但是已经建立在PostgreSQL之上，已经成为世界上最先进的分布式数据库之一。与PostgreSQL的完整功能集相比，这令人毛骨悚然，还有许多工作要做，Citus现在提供的功能及其扩展方式使其在分布式数据库环境中具有很大的独特性。许多当前的Citus用户最初使用Postgres中的许多高级功能在单节点PostgreSQL服务器上建立业务，然后仅用几周的开发工作就迁移到Citus，以将其数据库模式转换为分布式表和引用表。对于任何其他数据库，从单节点数据库到分布式数据库的这种迁移可能要花费数月甚至数年的时间。
使用Citus将Postgres功能转变为超级强大
像PostgreSQL这样的RDBMS具有几乎无限的功能和成熟的SQL引擎，可让您以多种方式查询数据。当然，这些功能只有在速度很快时才对应用程序有用。幸运的是，PostgreSQL很快，并且通过诸如实时查询编译之类的新功能不断提高，但是当您拥有大量数据或流量以至于一台机器速度太慢时，那些强大的功能就不再那么有用了……除非您可以结合许多计算机的计算能力。这就是功能成为超级大国的地方。
通过采用PostgreSQL功能并进行扩展，Citus具有许多超级功能，这些功能使用户可以将数据库扩展到任意大小，同时保持高性能及其所有功能。
- 查询路由意味着获取查询（作为查询的一部分），并让存储相关分片的RDBMS节点处理查询，而不是收集或重新整理中间结果，当查询通过分发列进行过滤和合并时，这是可能的。查询路由使Citus能够为多租户（SaaS）应用程序大规模支持底层PostgreSQL服务器的所有SQL功能，这些应用程序通常按租户ID进行过滤。就分布式查询计划时间和网络流量而言，此方法具有最小的开销，可实现高并发性和低延迟。
- 与顺序执行相比，跨分布式表中所有分片的并行，分布式SELECT允许您在短时间内查询大量数据，这意味着您可以构建具有一致响应时间的应用程序，即使您的数据和客户数量通过扩展数据库来增长。 Citus查询计划程序将从多个分片中读取数据的SELECT查询转换为一个或多个类似于map-reduce的步骤，其中并行查询每个分片（map），然后合并或重新组合结果（reduce）。对于线性比例尺，大多数工作应在映射步骤中完成，对于联接或按分布列分组的查询通常是这种情况。
- 联接是SQL的重要组成部分，其原因有两个：1）它们提供了极大的灵活性，可以以不同的方式查询数据，从而避免了应用程序中复杂的数据处理逻辑； 2）它们使您的数据表示更加紧凑。 。如果没有联接，则需要在每一行中存储大量冗余信息，这将大大增加存储，扫描表或将其保留在内存中所需的硬件数量。通过联接，您可以存储紧凑的不透明ID并进行高级过滤，而不必读取所有数据。
- 参考表看起来像其他任何表一样，但是它们在群集中的所有节点之间透明地复制。在典型的星型模式中，所有维表都将是参考表，而事实表则是分布式表。然后，事实表可以与任何列上的任何维表结合（并行！），而无需通过网络移动任何数据。在多租户应用程序中，参考表可用于保存在租户之间共享的数据。
- 子查询下推是并行，分布式SELECT，查询路由和联接之间的结合。可以通过子查询下推在单个回合中并行化包含高级子查询树的所有分片中的查询（例如子查询之间的联接），只要它们可以联接分布列上的所有分布式表（而引用表可以在任何列上联接）。这将启用非常高级的分析查询，该查询仍具有线性可伸缩性。 Citus可以利用PostgreSQL计划程序已经对所有查询进行的转换来识别可下推的子查询，并为所有剩余的子查询生成单独的计划。这允许有效地分布所有子查询和CTE。
- 索引就像桌子的腿。没有它们，要从桌子上拿东西会很费力，而且实际上不是桌子。 PostgreSQL特别提供了非常强大的索引功能，例如部分索引，表达式索引，GIN，GiST，BRIN和覆盖索引。这使查询（包括联接！）即使在大规模时也能保持快速。值得记住的是，索引查找通常比扫描数据的一千个内核快。 Citus通过索引各个分片来支持所有PostgreSQL索引类型。像Heap和Microsoft这样的高级用户尤其喜欢使用部分索引来处理针对许多不同事件类型的各种查询。
- 分布式事务允许您一次或根本不进行一组更改，这大大提高了应用程序的可靠性。 Citus可以使用类似于查询下推的方法将事务委派给PostgreSQL节点，并继承其ACID属性。对于跨碎片的交易，Citus使用PostgreSQL的内置2PC机制，并添加了一个分布式死锁检测器，该检测器使用PostgreSQL内部函数从所有节点获取锁表。
- 并行，分布式DML允许以相对较少的时间和事务方式转换和维护大量数据。分布式DML的常见应用是INSERT…SELECT命令，该命令将原始数据表中的行聚合到汇总表中。通过在所有可用内核上并行执行INSERT…SELECT，数据库将始终足够快以聚集传入的事件，而应用程序（例如仪表板）将查询紧凑的，高索引的汇总表。另一个例子是Citus用户，他吸收了260亿行不良数据，并使用分布式更新对其进行了修复，平均每秒修改了70万行。
- 批量加载是分析大量数据的应用程序的一项基本功能。即使在单个节点上，PostgreSQL的COPY命令也可以每秒向表追加数十万行，这已经超过了大多数分布式数据库基准测试。 Citus可以散出COPY流，以在许多PostgreSQL服务器上并行添加和索引许多行，这可以扩展到每秒数百万行。
- 存储过程和函数（包括触发器）在数据库内部提供了一个编程环境，用于实现单个SQL查询无法捕获的业务逻辑。当您需要一组操作来进行事务处理而无需在应用程序服务器和数据库之间来回移动时，对数据库进行编程的功能特别有用。使用存储过程可以简化您的应用程序，并使数据库更高效，因为您可以避免在进行网络往返时保持事务打开。尽管它可能会给数据库带来更多的负载，但是在数据库扩展时，这将不再是一个大问题。
尽管大多数这些功能对于开发需要扩展的复杂应用程序来说似乎都是必不可少的，但并不是所有分布式数据库都支持它们。下面我们根据公开提供的文档对一些流行的分布式数据库进行比较。
让我们的力量结合起来……
与在分布式数据库中拥有超级功能相比，更重要的是能够组合数据库超级功能来解决复杂的用例。
星球队长
图片由特纳广播公司提供。 ©特纳广播。由芭芭拉·皮尔（Barbara Pyle）和特德·特纳（Ted Turner）创建的Planet队长。
由于支持查询路由，参考表，索引，分布式事务和存储过程，因此即使最先进的多租户OLTP应用程序（例如Copper）也可以使用Citus扩展到单个PostgreSQL节点之外，而不会在应用程序中做出任何牺牲。
如果将子查询下推与并行的分布式DML结合使用，则可以在数据库内部转换大量数据。一个常见的示例是使用INSERT…SELECT构建汇总表，该表可以并行化以适应任何类型的数据量。结合通过COPY，索引，联接和分区进行的批量加载，您将拥有一个非常适合时间序列数据和实时分析应用程序（如Algolia仪表板）的数据库。
正如Microsoft的Min Wei在谈到Microsoft如何使用Citus和PostgreSQL分析Windows数据时指出的那样：Citus使您能够使用分布式OLTP解决大规模OLAP问题。
GoodOldSQL
Citus与其他分布式数据库有些不同，后者通常是从头开始开发的。 Citus没有引入PostgreSQL中尚未提供的任何功能。 Citus数据库以满足需要扩展的用例的方式扩展了现有功能。重要的是，大多数PostgreSQL功能已经针对各种用例进行了数十年的开发和测试，而当今用例的功能要求最终并没有太大不同；主要是数据的规模和大小不同。因此，在构建现代应用程序时，基于世界上最先进的开源RDBMS（PostgreSQL！）构建的分布式数据库（如Citus）可以成为您的武器库中最强大的工具。
原文：https://www.citusdata.com/blog/2018/11/30/why-rdbms-is-the-future-of-distributed-databases/
本文：http://jiagoushi.pro/node/929
讨论：请加入知识星球或者微信圈子【首席架构师圈】
- 43 次浏览
【关系型数据库】数据库深度探索:PostgreSQL
在最新一期的数据库深度挖掘中，我们采访了Brad Nicholson和Dave Cramer，了解了他们在PostgreSQL世界中的经历。
来自Crunchy Data的Dave (@dave_cramer)是PostgreSQL JDBC驱动程序的维护者。来自IBM的Brad是IBM云数据库组合的架构师和工程师。
阅读下面的采访，了解PostgreSQL社区是如何成为其最大的优势——但有时也是弱点——以及新的可插拔存储引擎如何帮助Postgres从数据库变成应用程序开发平台。
跟我们谈谈你自己和你今天在做什么?
戴夫·克莱默(华盛顿特区):我目前住在加拿大安大略省的一个小镇上，我很幸运，在我的职业生涯中一直在家工作。为了乐趣和刺激，我喜欢在赛道上开车探索我目前对物理学的理解。
至于我今天在做什么，复杂的数据让我可以全职工作在Postgres上，这真的，真的很酷。我目前正在JDBC驱动程序上工作，修复bug;实际上，正如我们所说的，在PostgreSQL的后端添加一些东西来帮助JDBC驱动程序和其他驱动程序处理像人们改变它们的搜索路径这样的事情。
我还帮助使用了Joe Conway编写的PL/R过程语言(他也处理复杂的数据)。还有一堆其他有趣的技术我正在帮助或参与。我最喜欢的一个是逻辑解码，它以一种只有Postgres才能做到的独特方式支持更改数据捕获。最近在Java PostgreSQL中，有许多我非常感兴趣的反应/异步驱动程序。
布拉德·尼科尔森(BN):我住在多伦多。今天，我在IBM Cloud数据库工作。我是一名架构师和工程师，在Postgres空间和其他数据库工作。真正处理将数据库即服务从内部自主开发的解决方案引入成熟的kubernet本地平台和产品的挑战。
你是如何加入PostgreSQL的?
完全是意外。我的职业生涯是从web开发人员开始的。我所从事的工作是MySQL(在事务出现之前)，当时发生了各种奇怪的事情，比如列被悄悄地截断，人们会在那一天停止浏览器，让一个表写入，而不是另一个表。我在想，肯定有比这更好的方法，而那正是在Postgres成为一个可行的开源产品的时候。
我不记得具体的版本了，但是像Tom Lane这样的人接管了它，并开始在它上面做很多工作。所以，我打开它，它很快就解决了我的问题，因为它像你期望的那样工作。从那以后，我开始了一份博士后DBA的工作。
DC:这几乎是一种意外，有点……也许吧。这里有一个故事——大约在1998年，我辞去了工作，成为了一名顾问。该合同支持一个Java应用程序，在那时，这意味着获得微软网络订阅。
我遇到了一个问题，我打电话给支持部门，他们说需要三周时间。我说，“啊，这怎么可能呢?”我是按小时计酬的，所以三周的时间看起来有点长。我想一定会有更好的方法，于是我开始关注开源。
(一开始)我对它一无所知，所以我问一个朋友如何开始。他说:“打开邮件列表，开始回答你能回答的所有问题。”我对Java很感兴趣，所以我找到了JDBC列表，开始回答每个问题。第一次花了几天时间才弄明白，第二次花得少一些，一两个月后，我就可以不用做研究就能回答问题了。
在某个时候，维护JDBC驱动程序的家伙决定辞职，于是Bruce Momjian问我是否有兴趣支持它。我说，“是的。”现在我已经做了20年了。这就是我如何融入社区的。
在你看来，PostgreSQL的优点和缺点是什么?
DC:哦，哇，这是个有趣的挑战。我认为优势和劣势在于社区，而不是技术。引用路线图——我要总结一下，因为它有点长——它说这是一个“非商业的，全是志愿者的，自由的软件项目，因此，开发不需要正式的功能需求列表。”我们喜欢让开发者探索他们所选择的主题。”还有更多，但这就是要点。
我在某些方面看到了它的优势，因为项目往往不会被锁定在一个特定的解决方案上，因为有公司在营销特定的解决方案。因为它是完全开源的，当我们意识到我们所做的是错误的或者有更好的方法去做的时候，我们对维持现状没有既得利益。所以，我们放弃它，改变我们的路径。
举一个具体的例子，几年前，有一个关于复制的事实标准，这是一个基于触发器的解决方案。它有很多挑战，从那时起，社区开发了一种叫做逻辑复制的东西，它取代了基于触发的复制。我们现在有一个更好的解决方案。
在我看来，优势实际上也是劣势——认为我们有这个大项目和所有这些不同的解决方案。通常，在公司开发的产品中，只有一种方法来做一件特定的事情，这也是唯一的方法。
在PostgreSQL中，做一件事有10到15种方法。现在，Postgres正在被大量的人采用，这意味着有很多新用户试图找到做事情的正确方法。可能没有正确的方法，但可能有一大堆错误的方法。
对于习惯于使用商业软件的人(其中有一种受支持的解决方案)来说，弄清楚如何使用Postgres来架构您的解决方案是相当具有挑战性的。的好事,我和松脆的数据,这为公司提供了一个机会脆数据为已知的工作提供支持解决方案(至少知道工作的解决方案,我们理解,可以支持)。这就是为什么我认为这是一个优点和一个缺点。
BN:当然，社区是优势之一。与之相吻合的是许可证。最近有很多关于Mongo, Redis和Elasticsearch的开源许可的新闻。
在Postgres中，许可证是开放的——人们不能改变它或从它那里拿走东西。没人能买到Postgres;只要它还存在，它就会是一个开源项目。这是一个非常非常大的好处。
在更技术的方面，以及一个明显而乏味的事情，是稳定。总的来说，这是一个非常可靠的数据库，只要你远离一些可能会有一些边缘情况的前沿事物。
(它也)非常安全。我真正喜欢它的一点是——尤其是与大多数数据库相比——它的可预测性。总的来说，它的运作方式和失败方式都是可以预测的。它如何失败的可预测性是非常非常好的，特别是当你要为它进行自动化解决方案时。在我所使用的其他一些数据库中，缺乏可预测性是最大的挑战之一(有太多奇怪的边界情况)。
另一件事是可扩展性。当你研究扩展系统时，你可以在Postgres上构建的东西，而不需要派生主代码基，这实际上是很了不起的。你看一下这个东西，它在30年前以关系数据库的形式出现，现在正逐渐成为应用程序开发平台，甚至是数据库开发平台。这是很酷的。
在缺点方面，我们开始越来越多地看到的一件事是缺乏本地扩展故事，比如本地分片，它允许在Postgres上本机运行更大的工作负载。
我认为连接模型也确实有局限性。整个进程连接以及使用外部连接池;我们有一些第三方工具可以解决这个问题，但它们在多租户中并不能很好地工作，因为需要将连接总数保持在相对较低的水平，并且不能在数据库用户之间共享连接，因此您必须在某些时候放松您的安全模型。
逻辑解码和本地Postgres逻辑复制也非常酷。我认为现在有一个大漏洞——它实际上并不能很好地与流复制一起工作。您不能切换或故障转移一个成员并让它同步您的位置。构建下游系统来使用它们的更改确实很好，但是一旦您必须切换或故障转移，您就会失去位置。你必须考虑重新同步你的系统，这很麻烦。
我要提到的最后一个问题是过度依赖外部工具来正确地完成许多基本工作。Postgres为您提供了这个坚如摧的数据库，它带有用于部署、备份、复制等的钩子，但将解决方案组合在一起取决于您。我想Dave之前提到过，这样做会很复杂。您必须具备丰富的知识，才能组织可靠的Postgres部署，使用DbaaS解决方案，或者花钱请人来为您完成这些工作。
我认为，如果你看更现代的数据库，设置复制和故障转移等等并不是那么困难。这是一种配置，它内置在系统中，可以正常工作。
说到现代数据库，PG 12很快就会面世。你在那里期待什么?
DC:这和我之前说的很酷的新功能以及人们的各种各样的痒感是一致的。发生的一件事基本上是Postgres能力的下一个飞跃的基础——可插拔表存储接口。这是我感兴趣的“开发酷东西”。
从一开始，就像Postgres做事情的方式一样——在一个版本中，我们将开始一个特性的框架，它不会特别有用，但下一个版本会更有用一些，[下一个版本会更有用。Pluggable table storage接口将支持柱状存储、加密列和其他特定于领域的存储系统。此外，我们开始看到分区方面的改进。我们在每个主要版本中都为分区添加了功能。这将使PostgreSQL能够处理更大的工作负载。
BN:对我来说也是可插拔的表存储。这对Postgres来说是非常令人兴奋的事情。我之前提到了可扩展性这与之相吻合，对吧?一旦您开始考虑将扩展和可插拔表存储组合在一起，您就有了这个开发平台，您可以在上面开始构建各种很酷的东西。
另一件更以开发人员为中心的事情是JSON路径。Postgres具有非常棒的JSON功能。但如果你不是一个Postgres用户，你从使用一个文档存储来使用它，它使用起来会很奇怪。JSON路径更接近于能够在Postgres中以一种开发人员会觉得更友好的方式使用JSON。
你会给那些想要在Kubernetes上部署PostgreSQL的人什么建议?
BN:不要。说真的，现在每个人都想这么做，因为这是buzz技术。它确实解决了某些问题，但它引入了另一组问题。这一切都是关于权衡和理解你需要做出的选择。
你必须问问自己为什么要运行它。当你进入Kubernetes的世界时，你需要真正理解你在购买什么。从一个更加标准的部署配置文件来看，你完全改变了你的部署平台——事情如何被部署，事情如何移动。
很可能您还没有在容器中运行数据库。所以，当你来到Kubernetes，你就进入了集装箱化。你将不得不把很多你所知道的(关于内存管理和资源利用的)知识扔到一边，然后重新学习。这是一项伟大的事业。
您还可能需要熟练地破解Kubernetes的源代码，并弄清楚为什么它在做您认为它不应该做的事情(因为它有时会做)。
从那以后，如果你想继续前进，就不要白费力气了。基本上，有几家运营商(比如Dave, Crunchy有一家Postgres运营商)，而经营Patroni(我们很喜欢)的Zalando也有一家Postgres运营商。所以，如果你真的想这么做，看看这些运算符，然后从那里开始。不要从零开始。
DC:与“不做”相反，我想说的是，调用复杂的数据。您知道，Kubernetes最初是为无状态的工作负载设计的。当你使用PostgreSQL时，这当然是一个挑战;两者有一些阻抗不匹配。
Crunchy数据对Kubernetes世界和Postgres世界有深刻的见解。正如Brad指出的，尝试自己做这是相当艰巨的。在这里你必须是两个世界的主人!一个人同时做到这两点是一个相当大的挑战。如果你想在Kubernetes上运行它，你必须雇人或者打电话给我们。
BN:还有一件事——博客圈有很多关于在Kubernetes上运行数据库的东西。他们非常专注于第一天:建立一个数据库。现在那些东西很容易。这之后发生的所有事情——数据库的生命周期——都变得非常困难。
DC:同样的，我们有很多问题的解决方案。如果你想让数据库运行，那很好。但如果你想让它继续运行，这是一个更大的问题。例如，Kubernetes可能会决定关闭您的pods。重新安排一个运行后的时间——我们有这类事情的解决方案。我告诉你，有人揪头发来找那些东西。我要重申布拉德说过的话:“不要白费力气。”
自己在Kubernetes上运行可能会犯一个错误，那么人们在使用PostgreSQL时还会犯哪些错误呢?
BN:我想说的是，最上面的一个没有使用连接池，不理解Postgres连接模型及其限制。人们启动了20份应用程序。每一个都试图打开几百个连接，接下来您就会发现连接不足或出现性能问题。您必须了解Postgres中的连接模型是如何工作的，并使用像PgBouncer这样的池程序将事情简化到更易于管理的数量。
DC:差不多是一样的。我认为这取决于项目的规模。有一些大型项目可以从一些初步的建筑咨询中受益。他们不是数据库专家(尤其是Postgres专家)。
实际上，有一天有人给我打电话说关于物联网项目，那个人有一个特别的解决方案，我知道这个方案行不通。他有一个天真、简单的解决方案，但这个方案注定会失败。阅读、研究，或者给有这方面知识的人打电话(并且说，“这是我想做的，有什么工具可以做吗?”)在项目的开始会有很长的路要走。
当然，在较小的规模上，如果你不试图做一些具有建筑挑战性的东西，学习如何调整Postgres。至少，阅读有关设置和安全性的文档对简化您的工作大有帮助。我们有一个很棒的文档网站，只是看起来没有多少人会读它。我猜这有点像试图吃掉一头大象，你从哪里开始呢?但我认为一些阅读是很有帮助的。
您有什么秘密的性能调优技巧吗?
BN:我不认为有任何秘密的性能调优技巧。我想对人们说的是
- 学习如何读取查询计划和优化查询。
- 调整数据模型。
- 修复错误的访问模式。
老实说，根据我的经验，你可以调整旋钮来改变计划，增加内存，诸如此类的事情。但是，总的来说，最大的好处来自于正确地修复错误的查询和错误的访问模式。为此，安装pg_stat_statements扩展并学习如何使用它。分析访问模式，验证访问模式。用一种迭代的方式去做——不要只做一次就忘记它。在你的产品发展的过程中就这样做。
在Postgres和大多数数据库中，更为微妙的一点是索引开销很大。Postgres深受文字放大的困扰。人们会抛出一堆索引来处理任何类型的查询，但他们没有意识到作为其中一部分的所有写操作的总体影响。因此，要仔细索引，查找您没有使用的索引并删除它们。你可以很容易地在网上找到你想要的东西。
DC:和布拉德说的差不多。在一天结束时，在数据库中，读和写磁盘花费的时间最多。这是非常简单的数学——I/O通道有一定的带宽，并且您试图在磁盘上读/写一定数量的字节，并且您有一定数量的时间。这个问题无法解决，但可以确保查询正常运行。
我所见过的每一个擅长于此的人都对监控他们的数据库保持警惕和无情。他们确保他们添加到代码中的每个查询不会爆炸。当他们发布新代码时，他们检查回归，他们测量每条SQL语句或测量应用程序。只是要确保你明白自己在做什么。
另一件重要的事情是他们的开发系统和开发数据比他们的生产数据要小几个数量级。然后他们在制作中得到了这些巨大的惊喜!有这样一个典型的时刻:“为什么不使用索引?”因为索引实际上更慢。这是因为他们没有足够的开发数据或测试数据来实际“测试”他们在生产中所做的事情。说到底，这是常识。测试一切，再次检查它，验证它，测量它，并修复损坏的部分。
PostgreSQL vs. MySQL:应用程序开发人员在选择数据库时应该考虑哪些事情?
DC:我对MySQL不是很熟悉，不能很好地回答这个问题。但是我可以谈谈他们在Postgres中应该注意什么。他们应该理解这个问题，“什么是事务?”他们应该理解问题的答案，“什么是事务隔离?”
我们今天在大型应用中看到的是，应用框架本身非常大。它们是大型的、复杂的框架，需要花费时间和精力来掌握它们。他们倾向于把数据库看作是一种“愚蠢”的存储。他们不会真的去注意它，直到它做了一些他们没有预料到的事情。所以，我认为他们至少应该知道什么是事务，什么是事务隔离。他们应该叫人……复杂的数据会很乐意提供帮助。
BN:我在这里没有太多的贡献，因为我不太了解MySQL从应用程序开发的角度。我已经很长时间没有在这种情况下使用它了，它已经改变了很多。
最后，你认为Postgres在未来5-10年里会走向何方?
DC:这是一个非常好的问题，这意味着我真的没有答案。我确实认为有些事情我们可以看看。
我可以给你们一些猜测;我们已经克服了在大企业中使用开源的污名。我敢打赌，没有一家《财富》500强公司是没有Postgres的。这意味着它的受欢迎程度将呈几何级数增长。使用它的人越多，就会有越多的人告诉他们的朋友。他们告诉朋友的越多，使用它的人就越多。对于这个项目来说，这意味着我们将有更多的人致力于代码库。
我发现在开源社区工作的人非常聪明。这是一种羞辱。有一些聪明的人将会解决我们今天遇到的一些难题。最大的挑战是，社区将不得不弄清楚如何与更多的人打交道。我不知道未来会发生什么，但我知道我们会有更多的人。
BN:我想补充一下Dave的观点，这是一个很好的观点。我认为Postgres做事情的方式将会有摩擦——他们有自己的方式来做事情，这与当今大多数人所习惯的github风格的工作流程不同。这并没有什么错，但是对于那些来自GitHub工作流之类的平台的人来说，这是一个更高的门槛。
在这方面会有一些挑战。我认为作为一个数据库，没有人能预测未来。但是如果你看看它是从哪里来的，它是从一个学术性的关系数据库到一个可靠的、开放源码的关系数据库到一个混合模型系统的关系和非关系的东西。
当您开始研究可插拔表存储和扩展框架时，我认为您会开始看到Postgres更像是一个瑞士军刀数据库。您将开始看到它处理更多的工作负载。
这是它。非常感谢Brad和David加入我们并分享他们对PostgreSQL的看法。请留意我们未来几周的下一次采访。特别感谢Emily Hu对这次采访的帮助。如果你还没有，看看我们的其他部分。
原文：https://www.ibm.com/cloud/blog/database-deep-dives-postgresql
本文：http://jiagoushi.pro/node/1142
讨论：请加入知识星球【首席架构师圈】或者小号【jiagoushi_pro】
- 26 次浏览
【数据库架构】PostgreSQL常用的开源扩展
如果您使用的是PostgreSQL，您可以利用优秀的开源扩展来根据您的业务需求增强或添加功能。这些扩展是由其社区积极开发的，这些社区与PostgreSQL社区本身是分开的。PostgreSQL有数百个OSS扩展，其中许多是在生产环境中实现的。
尽管它们功能强大，但要检查规范并验证其行为以做出符合您需求的最佳选择并不容易。本文介绍富士通客户和普通用户经常使用的OSS扩展。这可以作为选择OSS扩展的指南。
PostgreSQL社区简介
PostgreSQL相关社区有两种类型：
- 开发社区
- 除了PostgreSQL本身的开发社区之外，每个OSS扩展都有一个开发社区。
- 促进PostgreSQL广泛使用的社区
- 世界各地都有PostgreSQL用户组。
富士通热衷于与PostgreSQL社区合作。我们的一些员工是PostgreSQL开发社区的积极贡献者，我们的一名员工作为PostgreSQL企业联盟的董事会成员。富士通还赞助各种活动，如PGDay Down Under、PGDay Asia和PGCon。
常用OSS扩展
下面的列表显示了PostgreSQL经常使用的OSS扩展。该分类基于非功能需求的概念，包括性能、可扩展性、可操作性、监控、可用性、兼容性和安全性。
可操作性
- pg_bulkload-提供高速加载大量数据的能力。
- pg_rman—支持备份操作，如备份/恢复简化和备份生成管理。
- pgBackRest—备份/恢复管理工具，提供多线程格式的数据库备份和高速备份所需的功能。
- Barman—备份/恢复管理工具，简化了PostgreSQL的时间点恢复过程，集中管理多个数据库集群的时间点备份。
- pg_repack-通过重新组织臃肿的表和索引，删除不必要的区域并重新排列行。由于它持有锁的时间很短，因此可以在正常业务操作期间使用。
- pgAdmin-简化数据库对象的创建、维护和使用的图形用户界面。
可扩展性
- pg_bigm-通过名为2-gram（bigram）的方法为全文搜索创建索引，允许对字符串进行高速搜索。
- PostGIS-允许用户使用SQL管理、编辑、搜索和计算地理空间信息。
- oracle_fdw-提供从PostgreSQL访问oracle表和视图的外部数据包装器。在此查看如何使用Oracle_fdw链接到Oracle数据库。
- PostgreSQL JDBC驱动程序-提供用于从Java连接到PostgreSQL的API。
- psqlODBC-PostgreSQL的ODBC驱动程序，提供用于从Microsoft Access、Microsoft Excel等连接到PostgreQL的API。
- Npgsql-一个.NET数据提供程序，提供用于从Microsoft.NET连接到PostgreSQL的API。
性能
- pg_hint_plan-通过在查询中指定提示子句，在不更改SQL语句或GUC参数的情况下控制执行计划。
- pg_dbms_stats-管理PostgreSQL统计信息并间接控制执行计划。
- PgBouncer-在PostgreSQL服务器和客户端之间运行的软件。提供连接池功能。
监测
- check_postgres-监视数据库运行状况并报告异常情况。
- pgBadger-分析PostgreSQL日志文件并生成统计报告，如SQL执行状态。
- pg_statsinfo-定期收集和累积PostgreSQL操作统计信息，以监控数据库操作。生成累积信息的文本报告。
- pg_stats_reporter-根据pg_statsinfo获取和累积的信息生成HTML格式的图形报告。
可用性
- pgpool II-在PostgreSQL服务器和客户端之间运行的软件。提供连接池、负载平衡、复制和自动故障切换等功能。
兼容性
- orafce—提供与Oracle数据库兼容的函数和数据类型等兼容性。
- ora2pg-支持从Oracle迁移到PostgreSQL的工具。从Oracle数据库读取对象定义和数据，并将其转换为PostgreSQL可执行的格式。
安全
- pgaudit-使用PostgreSQL的日志功能获取审计日志。
有关每个OSS扩展规范的详细信息，请参阅其网站。
按类别映射
PostgreSQL特性和OSS扩展的分类和映射如下所示。
从上图中，您可以看到用于可伸缩性、可操作性和监控的OSS扩展的数量非常多。这是因为这些扩展是作为一个组件或工具提供的，可以嵌入到PostgreSQL主体中，以执行重要任务，例如使PostgreSQL与各种应用程序的API兼容，将PostgreSQL链接到各种外部数据，或者根据各种需求和操作模式调整PostgreSQL。
为了根据您的业务需求选择最佳的OSS扩展，了解每个OSS扩展的特性和好处非常重要。
- 36 次浏览
【数据库架构】使用pgpool II的PostgreSQL高可用性
企业数据库系统要求高可用性——系统应能容忍故障并始终稳定运行。同样重要的是可扩展性，以便随着系统的发展可以添加服务器。即使数据量或访问量增加，也需要保持响应速度和处理能力。
在本文中，我们讨论了使用pgpool II（PostgreSQL开源扩展之一）的高可用性系统设置。它是我们的解决方案之一，可以满足这两个要求：高可用性和可扩展性。
什么是pgpool II？
pgpool II是一个中间件，可以在Linux和Solaris上运行，在应用程序和数据库之间运行。其主要特点包括：
| Feature | Classification | 解释 |
| Load balancing | 高效地将只读查询分发到多个数据库服务器，以便可以处理更多查询。 | 性能改进 |
| Connection pooling | 保留/重用与数据库的连接，以减少连接开销，并在重新连接时提高性能。 | |
| Replication | 在任何给定时间点将数据复制到多个数据库服务器，以实现数据库冗余。† | 数据库的高可用性 |
| Automatic failover | 如果主数据库服务器发生故障，将自动切换到备用服务器以继续操作。 | |
| Online recovery | 在不停止操作的情况下还原或添加数据库服务器。 | |
| Watchdog | 链接pgpool II的多个实例，执行心跳监测，并共享服务器信息。当发生故障时，开关自动导通。 | pgpool II的高可用性 |
pgpool II可以使用自己的复制功能或其他软件提供的复制功能，但通常建议使用PostgreSQL的流式复制。流复制是一种通过将PostgreSQL（主）的事务日志（WAL）传送到PostgreSQL的多个实例（备用）来复制数据库的功能。这一功能在文章“什么是流式复制，以及如何设置它？”中进行了解释。
Diagram illustrating streaming replication
负载平衡和连接池
横向扩展是通过添加服务器来提高整个数据库系统处理能力的一种方法。PostgreSQL允许您使用流复制进行扩展。在此场景中，将查询从应用程序高效地分发到数据库服务器至关重要。PostgreSQL本身没有分发功能，但您可以利用pgpoolII的负载平衡，它可以高效地分发只读查询，以平衡工作负载。
另一个很棒的pgpool II功能是连接池。通过使用pgpool II的连接池，可以保留和重用连接，从而减少连接到数据库服务器时发生的开销。
要使用这些功能，需要在pgpool.conf中设置相关参数。
自动故障切换和在线恢复
本节介绍使用pgpool II的自动故障切换和在线恢复。解释假设使用PostgreSQL的流复制。
下图说明了pgpool II在PostgreSQL（主）中检测到错误时如何执行自动故障切换。
- 1.分离出现故障的主服务器并停止pgpool II的查询分发
- 2.将备用服务器升级为新的主服务器
- 3.更改每个PostgreSQL的复制同步位置
注意，您需要事先为2和3创建脚本，并在pgpool II中设置它们。
在线恢复功能执行来自pgpool II的在线恢复命令，并将分离的旧PostgreSQL（主）恢复为PostgreQL（备用）。命令操作如下所述。
- 4.根据新PostgreSQL（主）的数据重建旧的PostgreSQL，并将其恢复为PostgreSQL
- 5.更改每个PostgreSQL的复制同步位置
您需要预先为这些进程创建脚本，并将它们存储在数据库服务器端。
笔记
pgpool II的在线恢复功能必须作为PostgreSQL的扩展功能安装在数据库服务器端。
监控狗（Watchdog）
为了实现整个系统的高可用性，pgpool II本身也需要冗余。此冗余的此功能称为看门狗。
下面是它的工作原理。看门狗在活动/备用设置中链接pgpool II的多个实例。然后，链接的pgpool II实例执行相互监听并共享服务器信息（主机名、端口号、pgpoolⅡ状态、虚拟IP信息、启动时间）。如果提供服务的pgpool II（活动）发生故障，pgpoolⅡ（备用）会自动检测并执行故障切换。执行此操作时，新的pgpool II（活动）启动一个虚拟IP接口，而旧的pgpool-II（活动的）停止其虚拟IP接口。这允许应用程序端使用具有相同IP地址的pgpool II，即使在服务器切换之后也是如此。通过使用Watchdog，pgpool II的所有实例协同工作，以执行数据库服务器监视和故障切换操作-pgpoolⅡ（活动）充当协调器。
在冗余配置中，我们还需要考虑pgpool II和数据库（PostgreSQL）大脑分裂的可能后果。分裂大脑是存在多个活动服务器的情况。在此状态下更新数据将导致数据不一致，恢复将变得繁重。为了避免大脑分裂，建议将pgpool II配置为3个或更多服务器，并且服务器数量为奇数。以下是大脑分裂可能发生的例子：
- pgpool II的裂脑
- 当设置了2个pgpool II实例并且仅在连接这些pgpoolⅡ实例的网络中发生错误时，将不会发生故障切换，但pgpool II的协调将停止。
- 数据库的分割大脑（PostgreSQL）
- 当设置了2个pgpool II实例并且连接pgpoolⅡ（活动）和PostgreSQL（主要）的网络中发生错误时。在两站式设置中，将不进行投票以判断故障转移（稍后描述）。
现在，让我们看一个例子，当有3个pgpool II实例时，看门狗可以避免数据库中出现分裂的大脑：
对pgpool II使用看门狗-网络故障
- 1.pgpool II（活动）检测到由于自身和PostgreSQL（主）之间的网络断开而导致的故障，但无法确定PostgreQL（主）是否正在运行。
- 2.pgpool II（备用）的其他实例投票判断故障切换
之后，pgpool II的实例决定采取行动：
将看门狗与pgpool II一起使用
- 3.没有故障转移，因为它不是多数票。在这里，避免了大脑分裂。pgpool II（活动）阻止PostgreSQL（主要）停止查询分发
- 4.如果在pgpool II（活动）上检测到故障，则将连接切换到pgpoolⅡ（备用）
因此，应用程序端的更新查询可以继续运行。
使用pgpool II进行系统设置
在这里，我们介绍了一个基于真实业务的系统设置，并执行了一个简单的操作检查。
系统设置
我们将设计一个满足以下要求的示例系统设置，假设使用pgpool II的实际业务场景：
- 考虑到大脑分裂的高可用性设置
- 未来扩展
- 负载平衡和连接池以提高性能
通常，为了选择硬件和软件，我们会分析性价比，并以简单高效的设置为目标。这样做的第一个因素是pgpool II的分配。下表总结了可能的位置及其对系统的影响：
| 位置 | Server load | Network | Server cost (quantity) |
| 专用服务器 | 不受其他软件影响。 |
受网络影响：
|
需要额外的服务器来运行pgpool II。 我们建议准备奇数个服务器，从3开始。 |
| 数据库服务器（共存） |
受共存数据库的性能影响。 需要考虑服务器的冗余和可扩展性。 |
网络影响小。 pgpool II和数据库之间的部分连接可以是本地的。 |
我们建议使用3个或更多共存的pgpool II服务器。 根据可用数据库服务器的数量，还需要更多的服务器。 |
| 应用服务器或web服务器（共存） |
受共存软件性能的影响。 需要考虑服务器的冗余和可扩展性。 |
无网络影响。 应用程序（web服务器）和pgpool II可以本地连接。 |
我们建议使用3个或更多共存的pgpool II服务器。 此外，还需要更多的服务器，具体取决于可用应用程序服务器或web服务器的数量。 |
pgpool II在数据库服务器上共存
首先，让我们来看看pgpool II在数据库服务器上共存的配置设置。
我们假设一个实际的系统设置可以在实际的业务案例中使用。如下图所示，来自客户端的请求由负载平衡器分发，应用程序在3个应用程序服务器上并行运行。
pgpool II的多个实例将与看门狗一起工作。将PostgreSQL（备用）添加到数据库服务器3以进行扩展，然后根据需要添加更多服务器。
在此设置中，请注意，工作负载集中在运行pgpool II（活动）的数据库服务器上。在计算负载平衡时要考虑到这一点。
pgpool II配置示例
pgpool II在应用程序服务器上共存
接下来，让我们看一个pgpool II在应用程序服务器上共存的示例设置。pgpool II现在以多主机配置运行，因此分配了固定IP。好处如下。
- pgpool II的负载平衡
- 在pgpool II（活动）和pgpoolⅡ（备用）上接受更新和只读查询
- 减少应用程序和pgpool II之间的网络开销
注意，即使在多主机配置中，pgpool II（活动）仍然是控制数据库的协调器。添加第三个数据库服务器以及更多的扩展。
pgpool II配置示例
操作检查
让我们检查应用程序服务器上同时存在pgpool II的设置中的自动故障切换和在线恢复。假设PostgreSQL和pgpool II已经安装、配置和启动。该示例使用以下内容：
- host0:数据库服务器1的主机
- host1：数据库服务器2的主机
- host5：运行pgpool II（活动）的应用程序服务器1的主机
1连接到应用程序服务器1上的pgpool II（活动），并检查数据库服务器的状态。
$ psql -h host5 -p 9999 -U postgres postgres=# SHOW pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0 | host0 | 5432 | up | 0.500000 | primary | 2 | false | 0 | 2020-08-27 14:43:54 1 | host1 | 5432 | up | 0.500000 | standby | 2 | true | 0 | 2020-08-27 14:43:54 (2 rows)
2强制停止数据库服务器1（主机0）上的PostgreSQL（主）。
$ pg_ctl -m immediate stop
3从应用程序服务器1再次检查数据库服务器状态。
第一个连接将失败，但第二个连接将成功，因为重置后将恢复到数据库的连接。
请注意，host0的状态已更改为“关闭”，角色已交换。我们刚刚确认，到主机1的故障切换是自动完成的，并且已升级到主服务器。
postgres=# SHOW pool_nodes;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# SHOW pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
0 | host0 | 5432 | down | 0.500000 | standby | 2 | false | 0 | 2020-08-27 14:47:20
1 | host1 | 5432 | up | 0.500000 | primary | 2 | true | 0 | 2020-08-27 14:47:20
(2 rows)
4从应用程序服务器1执行数据库服务器1（主机0）的联机恢复命令。
确认已正常完成。
$ pcp_recovery_node -h host5 -p 9898 -U postgres -n 0 Password: （yourPassword） pcp_recovery_node -- Command Successful
5从应用程序服务器1再次检查数据库服务器状态。
在线恢复后，将在第一次连接到数据库时重置连接，第二次连接将成功。由于host0的状态已更改为“up”，您可以看到它已恢复并处于待机状态。
postgres=# SHOW pool_nodes;
ERROR: connection terminated due to online recovery
DETAIL: child connection forced to terminate due to client_idle_limitis:-1
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# SHOW pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
0 | host0 | 5432 | up | 0.500000 | standby | 2 | true | 0 | 2020-08-27 14:50:13
1 | host1 | 5432 | up | 0.500000 | primary | 2 | false | 0 | 2020-08-27 14:47:20
(2 rows)
笔记
我们已经看到，pgpool II是PostgreSQL的一个有效的开源扩展，用于企业使用。它适用于具有大量同时连接并需要扩展的中大型系统。
在采用pgpool II时，请务必注意以下事项：
- 构建环境需要许多配置设置，用户必须创建所需的脚本。
- 彻底检查和验证设计和环境设置。
- 负载平衡中只分发只读查询。
- 在处理许多更新查询的系统中看不到性能改进。类似地，只读性能随着数据库服务器数量的增加而提高，但更新性能可能会降低。
- 220 次浏览
【数据库架构】设置 PostgreSQL 多主复制：变得简单
多主复制是一种允许多个节点接受写入请求的方法。在本文中，您将了解如何使用 BDR 设置 PostgreSQL 多主复制。这是本文的大纲。
目录
- 什么是复制？
- 单主复制
- 什么是多主复制？
- PostgreSQL 多主复制
- 使用 BDR 设置 PostgreSQL 多主复制的步骤
- 第 1 部分：初始配置
- 第 2 部分：创建单个 BDR 节点
- 第 3 部分：创建另一个 BDR 节点
- 第 4 部分：配置 HAProxy
- 第 5 部分：将 PgBouncer 与 HAProxy 结合使用
- 第 6 部分：节点切换
- 结论
什么是复制？
复制是将数据从一个数据库服务器复制到另一个数据库服务器。源服务器一般称为主服务器或主服务器。接收数据库服务器也称为副本或从属服务器。
复制通常用于提高性能、创建备份、可扩展性并减少主服务器上的负载。对主服务器所做的更改会传递到从服务器。这些更改可以同时复制，也可以分批复制。如果它在两个服务器中同时发生，则称为同步复制。如果它分批发生，则称为异步。
复制允许您在不影响主节点操作的情况下执行分析。您可以使用复制进行横向扩展，尤其是在写入次数较少而读取次数较多的情况下。
单主复制
在单主多从场景中，只允许一台服务器（主服务器）对数据进行更改。其他服务器不接受来自除主服务器之外的任何地方的写查询。
什么是多主复制？
考虑有几个可以更新数据的连接数据库服务器的情况。在这种情况下，组中的一个成员所做的更改会波及所有其他成员。这可以通过多主复制系统来完成。因此，多主复制为该过程添加了一个双向元素。
多主复制允许多个节点接受写入查询，并且所有涉及的节点都包含相同的数据。多主复制的主要目的当然是高可用性。当主服务器发生故障时，无需等待物理备用服务器被提升。
建议 PostgreSQL 用户不要通过单主复制就足够的多主复制来使设计复杂化。这是因为多主复制使系统变得复杂和混乱。例如，如果您的应用程序具有增量字段并且两个节点没有集成此因素，则可能会导致冲突。
PostgreSQL 多主复制
虽然 PostgreSQL 中内置了单主复制，但多主复制没有。 有一些 PostgreSQL 分叉由小公司和社区管理。
目前有一种流行的产品在 PostgreSQL 中支持多主复制。 它被称为由 2ndQuadrant 创建的双向复制。 BDR 的早期版本是开源的，但不是最新版本。
请注意，在事务速度和防止数据冲突之间进行权衡时，BDR 更喜欢低延迟，允许出现一些冲突（如果不可避免）并在以后解决它们。
使用 BDR 设置 PostgreSQL 多主复制的步骤
第 1 部分：初始配置
在安装 BDR 和 pglogical 插件（充当逻辑复制解决方案）后的此步骤中，您修改 postgresql.conf 和 pg_hba.conf 文件，然后重新启动服务。
Step1：安装 BDR 和 pglogical 插件。
第 2 步：将 postgresql.conf 配置为这些值。
wal_level = logical shared_preload_libraries= ‘pg_logical, bdr’ track_commit_timestamp= ‘on’ # This is necessary for conflict resolution.
第 3 步：创建具有超级用户权限的用户来管理 BDR 连接。
CREATE USER my_user WITH SUPERUSER REPLICATION PASSWORD ‘my_password’;
第 4 步：通过添加这些行来更改 pg_hba.conf 文件。
host all bdr 10.20.30.40/24 md5 host replication bdr 10.20.30.40/24 md5
第 5 步：将用户添加到 .pgpass 文件。
hostname:port:database:my_user: my_password
第 6 步：重新启动 Postgresql。
第 2 部分：创建单个 BDR 节点
将主机 my_host1 上的 my_db 上的 BDR 激活为 my_user。
第 1 步：创建扩展。
CREATE EXTENSION bdr CASCADE; # CASCADE also creates the pglogical extension.
第 2 步：使用 bdr.create_node 函数初始化当前节点。
SELECT bdr.create_node ( node_name:= ‘initail_node’, local_dsn:= ‘dbname=my_db host= my_host1 user=my_user’);
第 3 步：使用 bdr.create_node_group 函数创建 BDR 集群定义。
SELECT bdr.create_node_group(node_group_name:='the_node_group');
第 4 步：使用 bdr.wait_for_join_completion 函数等待全部完成。
SELECT bdr.wait_for_join_completion();
第 3 部分：创建另一个 BDR 节点
第 1 步：创建 BDR 扩展。
CREATE EXTENSION bdr CASCADE;
第 2 步：使用 bdr.create_node 函数初始化当前节点。
SELECT bdr.create_node ( node_name:= ‘next_node’, local_dsn:= ‘dbname=my_db host= my_host2 user=my_user’);
第 3 步：使用 bdr.join_node_group 函数创建 BDR 集群定义。
SELECT bdr.join_node_group (join_target_dsn:= ‘dbname=my_db host= my_host1 user=my_user’, wait_for_completion:=True);
第 4 部分：配置 HAProxy
HAProxy 是一个提供高可用性的开源代理软件。它会自动将流量引导到任何在线节点。为此，您需要先安装 HAProxy。
第 1 步：修改 haproxy.cfg 文件的全局部分。
global state socket /var/run/haproxy/sock level admin # This allows us to get information and send commands to HAProxy.
第 2 步：修改 haproxy.cfg 文件的 bk_db 部分。
stick-table type ip size 1 stick on dst server bdr_initial_node my_host1: 5432 check server bdr_next_node my_host2: 5432 backup check
步骤 3：通过触发 HAProxy 重新加载配置文件。
sudo systemctl reload haproxy
第 5 部分：将 PgBouncer 与 HAProxy 结合使用
PgBouncer 是 PostgreSQL 的连接池。在 PgBouncer 的帮助下，PostgreSQL 可以与更多的客户端进行交互。这对于事务管理来说是必不可少的。当 HAProxy 重定向流量时，PgBouncer 允许交易完成。以下步骤需要安装 PgBouncer。
第 1 步：修改 ft_postgresql 部分的 haproxy.cfg 文件。
frontend ft_postgresql bind *: 5433 default_backend bk_db
第 2 步：更改 pgbouncer.ini 文件中的数据库部分以包含以下行。
* = host= proxy_server port= 5433
修改 pgbouncer 部分。
listen_port = 5432
第 3 步：重新启动 HAProxy，然后重新启动 PgBouncer。
第 6 部分：节点切换
我们正在将连接从初始节点移开。首先，我们使用 HAProxy 禁用 bdr_initial_node，以便不会向它发送新连接。我们将 RECONNECT 命令传递给 pgbouncer 以确保在当前事务之后重新连接。现在 HAProxy 将导致重新连接到第二个服务器。然后在进行更改后，再次启用初始服务器。
第 1 步：在 HAProxy 中禁用 initial_node。
echo “disable server bk_db/bdr_initial_node” | socat /var/run/haproxy/sock –
步骤 2：交易完成后，PgBouncer 应该重新连接并等待连接重新建立。
psql –h proxy_server –U pgbouncer pgbouncer –c “RECONNECT” psql –h proxy_server –U pgbouncer pgbouncer –c “WAIT_CLOSE”
第 3 步：在重新启用之前处理初始节点。
第 4 步：再次重新启用初始节点。
echo “enable server bk_db/bdr_initial_node” | socat /var/run/haproxy/sock –
结论
上述步骤绝不是完整的。在处理序列、安装、在线版本之间的升级以及测试故障转移过程时，您会遇到一些复杂情况。您可以在 PostgreSQL 12 High Availability Cookbook 中找到完整而全面的指南。这本书不仅详细介绍了 PostgreSQL 多主机复制，还详细介绍了设计高可用性服务器的其他几种方法。
无论如何，PostgreSQL多主复制需要大量的技术理解和学习。但 Hevo 可以帮助您应对这些挑战。 Hevo Data 是一种无代码数据管道，可以帮助您实时复制数据，而无需编写任何代码。 Hevo 作为一个完全托管的系统，提供了一个高度安全的自动化解决方案，使用其交互式 UI 只需单击几下即可帮助执行复制。
原文：https://hevodata.com/learn/postgresql-multi-master-replication/
- 346 次浏览