掌握Postgres：10个高级技巧

PostgreSQL不仅仅是另一个数据库，它是一个包含可以改变您处理数据方式的功能的系统。

1、元组是行的物理版本

PostgreSQL的基础之一是元组（tuple）的概念，这让许多新手感到惊讶。

简单地说，PostgreSQL 中的元组是一行数据的物理版本。

这意味着，当一行数据发生变化时，Postgres 不是修改现有数据，而是为该行添加一个新版本，即一个元组。

这种版本系统被称为 MVCC（多版本并发控制），了解它对于设计性能良好的系统非常重要。

下面是各种写入操作过程中发生的情况：

当你执行 DELETE 命令时，它不会立即回收磁盘空间。相反，旧元组会被标记为死元组，但会一直存在，直到 VACUUM 将其删除。如果这些死图元不断累积，并被大量真空删除，就会导致表和索引臃肿。

同样，当更新一条记录时，Postgres 不会修改现有的元组。相反，它会为该行创建一个新版本（新元组），并将旧元组标记为死元组。

即使取消 INSERT 也会创建一个死元组，这可能会让很多人感到惊讶。这意味着，如果你试图插入一条记录，然后回滚该操作，那么之前要插入的元组就会被标记为死元组。（区块链 或EventSourcing 概念）

为了帮助理解这些概念，Postgres 中的每个表都有隐藏列：ctid、xmin 和 xmax：

ctid 代表元组的位置（页码 + 其内部的偏移量），而 xmin 和 xmax 可视为元组的 "出生日期 "和 "死亡日期"。

如果能尽早了解这种行为，就能更好地应对与磁盘空间、臃肿和旨在清理这些死元组的自动真空进程有关的挑战。

下面是我最喜欢的基本示例，一个简单但非常重要的示例：

nik=# create table t1 as select 1 as id;SELECT 1nik=# select ctid, xmin, xmax, * from t1; ctid  | xmin  | xmax | id-------+-------+------+---- (0,1) | 47496 |    0 |  1(1 row)nik=# update t1 set id = id where id = 1;UPDATE 1nik=# select ctid, xmin, xmax, * from t1; ctid  | xmin  | xmax | id-------+-------+------+---- (0,2) | 47497 |    0 |  1(1 row)

看到了吗？我们创建了一个包含单行的表，检查了该行的实时元组（ctid）的位置，然后发布了一个 UPDATE 命令，从逻辑上讲，这个命令什么也没做，没有改变值。

但位置发生了变化，从（0,1）（第 0 页，偏移量 1）变为（0,2）。

因为在物理上，Postgres 创建了一个新的元组--一个新的行版本。

了解 Postgres 的这一行为将有助于你更高效地设计系统。

2.EXPLAIN分析始终使用 BUFFERS！

了解查询是如何运行的，对优化查询性能至关重要。

在 PostgreSQL 中，EXPLAIN 命令是实现这一目标的主要工具。

不过，要想获得更详细的信息，您应该使用 EXPLAIN (ANALYZE,BUFFERS)。

为什么呢？让我们来分析一下：

EXPLAIN 本身提供查询计划，让你深入了解 Postgres 打算用来获取或修改数据的操作。这包括顺序扫描、索引扫描、连接、排序等。该命令应在不执行的情况下单独用于检查查询计划。

添加 ANALYZE 命令后，不仅能显示计划的操作，还能执行查询并提供实际的运行统计信息。这样就可以比较估计行数和实际行数，帮助找出 Postgres 可能做出不准确假设的地方。它还提供了每个执行茎的运行时序信息。

BUFFERS 选项则更进一步。

它提供了有关缓冲区使用情况的信息--具体来说，有多少块在缓冲区池中被命中，或从底层缓存或磁盘读入缓冲区池。这为了解查询的 IO 密集程度提供了宝贵的信息。

3.最佳用户界面工具选择：超越 pgAdmin

当你进入 Postgres 的世界时，首先要面对的选择之一就是使用哪个客户端或界面。虽然很多初学者都会从 pgAdmin 开始，因为它既受欢迎又易于使用，但随着你的 Postgres 之旅逐渐成熟，你可能会发现还有更强大、功能更全面的工具可供使用。

PostgreSQL 最强大的客户端之一是其内置的命令行工具 psql。

对某些人来说，命令行界面可能令人生畏或不方便，但 psql 功能强大，可以实现高效的数据库交互。

此外，psql 无处不在，几乎所有安装了 PostgreSQL 的系统上都能找到它。

将它与 tmux 搭配使用，你就拥有了一个强大的组合，可以毫不费力地管理多个会话和脚本。

如果你更倾向于图形界面，有几款软件可以在用户友好性和高级功能之间取得平衡：

DBeaver、JetBrains 的 DataGrip 和 Postico 等工具提供了支持查询执行、数据可视化等功能的复杂界面。

不过，无论您选择哪种图形工具，投入一些时间学习 psql 的来龙去脉都会让您受益匪浅。花点时间学习它，你会得到回报的。

4.日志：尽快调整设置

与许多系统一样，在 Postgres 中，日志是一个信息宝库，能让你详细了解系统的运行情况和潜在问题。通过启用全面的日志记录，您可以提前发现问题、优化性能并确保数据库的整体健康。

选择记录内容：有效记录日志的关键在于知道记录哪些内容，同时又不会让系统不堪重负。通过设置 log_checkpoints = 0、
log_autovacuum_min_duration = 0、log_temp_files = 0 和 log_lock_waits = on 等参数，可以查看检查点、自动真空操作、临时文件创建和锁等待。

这些都是可能出现问题的最常见领域，因此对监控至关重要。

洞察力与开销之间的平衡：

需要注意的是，虽然大量日志记录可以提供有价值的洞察力，但也会带来开销。如果将
log_min_duration_statement 设置为很低的值，情况就尤其如此。

例如，如果将其设置为 200 毫秒，就会记录每条超过 200 毫秒的语句，这既可能提供信息，也可能降低性能。

一定要谨慎并意识到 "观察者效应"，即监控过程对被观察系统的影响。

但是，如果不能从日志中获得更详细的信息，诊断问题就会变得更加困难。

总之，虽然日志是 Postgres 库中一个非常强大的工具，但它需要精心配置和定期检查，以确保它始终是帮助而不是阻碍。

5.利用扩展增强可观察性：pg_stat_statements 等

当你想维护 Postgres 数据库的性能和健康时，扩展可以成为你的秘密武器。其中，pg_stat_statements 是不可或缺的必备工具。

为什么要使用 pg_stat_statements？

该模块提供了一种跟踪服务器成功执行的所有 SQL 语句的执行统计数据的方法。通俗地说，它可以帮助你监控哪些查询被频繁运行，哪些查询消耗了更多时间，哪些查询可能需要优化。有了这个扩展，你就可以了解数据库的运行情况，从而发现并纠正效率低下的问题。

其他值得考虑的扩展

虽然 pg_stat_statements 是自上而下查询分析的核心，但还有其他值得注意的扩展可以提供更深入的分析：

• pg_stat_kcache：有助于了解实际的磁盘 IO 和 CPU 使用情况，这正是识别造成高 CPU 使用率或高磁盘 IO 的查询的方法。
• pg_wait_sampling 或 pgsentinel：这两个扩展可以更清楚地了解查询在哪些地方花费了等待时间--提供所谓的等待事件分析，也就是活动会话历史分析（类似于 RDS 性能洞察）。
• auto_explain：该扩展会自动记录慢语句的执行计划，使理解和优化它们变得更简单

请记住，这些扩展需要进行一些初始设置和调整，以获得最佳效果并降低开销。遗憾的是，大多数托管 Postgres 提供商都不提供 pg_stat_kcache 或 pg_wait_sampling / pgsentilel。

6.采用数据库分支进行开发（使用 DBLab)

在数据库中进行开发和测试的过程通常需要复制数据，这可能会耗费大量资源、速度缓慢且繁琐。不过，有了瘦克隆和分支，就有了更聪明的方法。

1、什么是瘦克隆？

精简克隆工具提供轻量级、可写的数据库克隆。这些克隆与源数据库共享相同的底层数据块，但在用户看来是独立的数据库。当对克隆进行更改时，只有这些更改会消耗额外的存储空间--这是通过写入时复制（CoW）来实现的，类似于容器或 Git 的功能，但只是在块级而不是文件级。这使得为开发、测试或分析创建多个副本变得异常快速和高效。

2、数据库分支的优势

数据库分支是瘦克隆的扩展，它能够保存进度，并允许根据新状态进一步创建克隆。与代码版本控制一样，数据库分支允许开发人员在主数据集之外创建分支。这意味着您可以在隔离的环境中测试新功能或变更，而不会影响主数据。

3、数据库实验室和 ChatGPT

数据库实验室（DBLab）等工具提供了强大的精简克隆和分支功能。此外，当与 ChatGPT 等人工智能解决方案结合使用时，开发人员甚至可以通过 SQL 查询从实验中获得即时结果，而不会影响生产或同事的工作。ChatGPT 经常会出现幻觉问题，因此使用克隆来验证人工智能生成的建议总是很重要。分支是最具成本效益和时间效益的方法。

从本质上讲，利用瘦克隆和 DB 分支意味着更快的开发周期、更低的存储成本以及无风险的实验能力。这是我们在开发环境中处理数据的一种变革性方法。请收听我们详细讨论这一问题的播客节目：

4、PostgresFM e019：数据库分支

开始使用瘦克隆和数据库分支的最快方法是使用 Postgres.ai 控制台（支持：AWS、GCP、DigitalOne）点击几下安装 DBLab SE：支持：AWS、GCP、DigitalOcean 和 Hetzner Cloud，以及任何其他位置，包括使用 "BYOM，自带机器 "选项的内部部署）。

7.确保启用数据校验

数据完整性是任何数据库的基石。如果不相信数据的准确性和一致性，即使是最先进的数据库结构或算法也会变得毫无用处。这正是 Postgres 中数据校验和发挥关键作用的地方。

1、什么是数据校验和？

在数据库中，校验和是由数据块中所有字节的总和得出的值。如果启用了数据校验和，Postgres 就会使用它来验证存储在磁盘上的数据的完整性。数据写入磁盘时，Postgres 会计算并存储一个校验和值。之后，当数据被读回内存时，Postgres 会重新计算校验和，并将其与存储值进行比较，以确保数据未被破坏。

2、为什么它们至关重要？

磁盘级损坏可能由各种因素造成，从硬件故障到软件错误。启用数据校验和后，Postgres 可以在损坏数据影响应用程序或导致更大问题之前将其识别出来。

3、激活和开销

需要注意的是，数据校验和需要在创建数据库集群（initdb）时激活。如果不转储和还原数据，或不使用特殊工具 pg_checksums（这需要经验），则无法为现有数据库集群打开数据校验和。与数据校验相关的开销相对较小，尤其是与确保数据完整性的好处相比。

8.调整 autovacuum 以提高运行频率和执行速度

Postgres 中的 autovacuum 进程就像是数据库的清洁工。它在幕后工作，清理旧数据，为新数据腾出空间，确保数据库保持高效。

1、了解autovacuum

Postgres 中的每个 INSERT、UPDATE 或 DELETE 操作都会创建一条记录的一个版本（元组）。随着时间的推移，这些旧版本会不断累积，需要进行清理。Autovacuum 通过回收存储空间、删除死行来完成清理工作。此外，它还负责保持表统计信息的最新状态，并防止出现事务 ID 包络事件。

2、为何至关重要

如果不定期进行自动真空清理，数据库就会出现臃肿问题--数据库保留的未使用空间会导致查询速度变慢并浪费磁盘空间。另一个问题是统计数据过期，这会导致计划选择不理想和性能下降。

3、如何调整

调整 autovacuum 意味着调整其配置，使其更频繁地运行并更快地完成任务。在高层次上，必须从两个方向进行调整：

• 为 autovacuum 提供更多动力（更多 Worker、更大配额--因为默认情况下只允许使用 3 个 Worker，而且节流相当保守）
• 让它更频繁地触发（因为同样的，默认情况下，它只在大量图元发生变化（10%-20%）时触发；在 OLTP 中，你希望将触发频率降至 1%，甚至更低）。

9.查询优化优于配置调整

说到 Postgres 的性能，在大多数情况下，优化 Postgres 配置 "足够好 "是个不错的选择，不需要经常（只有在 Postgres 发生重大变化（如重大升级）时）重新审视这些决定，然后完全专注于查询优化。尤其是在经常更改应用程序的情况下。

1、为什么查询调整更重要？

最初，调整 Postgres 配置可以提高性能。但随着应用程序的增长和发展，性能的主要争夺战往往从配置转向查询优化。结构合理的查询可以使应用程序顺利扩展，也可以使应用程序在负载情况下停滞不前。

2、调整与优化

初学者有一个常见的误解："只要我把配置调整得足够好，就不会有问题"。配置调整至关重要，但这仅仅是个开始。最终，您必须将重点转向持续优化查询。

3、工作工具

前面已经讨论过的 pg_stat_statements 是识别问题查询的宝贵工具。它提供了一个按各种指标排序的 SQL 语句列表。如果与上文讨论过的 EXPLAIN (ANALYZE,BUFFERS)搭配使用，就能了解查询的执行计划并找出效率低下的问题。

作为 Postgres 用户，我们必须牢记这一真理：配置奠定了基础，但持续的查询优化能让我们的系统保持最高性能。

10.索引维护：必要的实践

索引对任何关系数据库的性能都至关重要，Postgres 也不例外。

1、为何重要

随着时间的推移，数据会发生变化，索引会变得支离破碎，效率也会降低。即使使用现代 Postgres 版本（特别是 Postgres 13 和 14 的 btree 优化）和经过良好调整的自动真空，索引的健康状况仍会随着时间的推移而下降，而大量的写入正在发生。

2、索引健康状况下降

插入、更新或删除数据时，反映这些数据的索引会发生变化。这些变化会导致索引结构变得不平衡或出现死条目，从而降低搜索性能。

3、重建索引

与某些误解不同，索引不会无限期地保持其最佳结构。它们需要定期重建。这个过程包括创建一个新版本的索引，通常会产生一个更紧凑、更高效的结构。最好以自动化方式为这些重建做好准备，以确保数据库性能保持一致。

4、清理

除了重建，删除未使用或多余的索引也同样重要。它们不仅会浪费存储空间，还会降低写入操作的速度。定期检查和清理不必要的索引应该成为日常维护工作的一部分。

重申一个关键点：索引至关重要，但与所有工具一样，它们也需要维护。要保持 Postgres 数据库的快速性能，就必须使其处于良好的健康状态。

原文：10个Postgres使用高级技巧