PostgreSQL作为一个优雅而且稳健的数据库越来越被大家所采纳，除了Mysql以外很多人都开始接触并学习PostgreSQL。但是PostgreSQL和传统的一些数据库系统还有有点点小小的鸿沟，有其一些架构和思维上变化，为此我们给介绍一些PostgreSQL的基本技巧和原生思维方式给初学者帮助大家尽快入门并提高。

开始使用 PostgreSQL 既令人兴奋又充满挑战。

tuples元组是行的物理版本

很多初学者进入PostgreSQL世界遇到的第一个困惑或许要数元组了。元组是什么？简单来说，Postgres中的元组是一行数据的物理版本。这意味着当一行中的数据发生更改时，Postgres不会更改现有数据，而是为该行增加一个新的版本（元组）。这个版本控制系统称为MVCC（多版本并发控制），了解它对于设计性能良好的系统非常重要。

以下是各种写入操作期间发生的情况：

当你执行一个DELETE命令，它不会立即回收磁盘空间。相反，旧元组被标记为死亡，但会一直保留到VACUUM删除它。 如果这些死元组可以累积并通过大量清理而被删除，则会导致表和索引膨胀。

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

取消INSERT创建一个死元组，就是在插入一条记录然后回滚该操作，则要插入的元组将被标记为死亡。

为了帮助掌握这些概念，Postgres中的每个表都有可以选择的隐藏列：ctid, xmin，和xmax。ctid表示元组的位置（页码+其中的偏移量），而xmin和xmax可以被视为元组的“出生日期”和“死亡日期”。

通过尽早了解这种行为，将能够更好地应对与磁盘空间、膨胀和自动清理进程等机制。

上面，创建了一个只有一行的表，然后检查该行的活动元组的位置（ctid），进行一个UPDATE操作，从逻辑上讲，它不会做任何事情，它不会实际改变值。但地点变了，从(0,1)（第0页，偏移量1），至(0,2)。因为在物理上，Postgres 创建了一个新的元组——一个新的行版本。了解 Postgres 的这种行为将帮助设计更高效地工作的系统。

EXPLAIN

了解查询的运行方式对于优化其性能至关重要。在PostgreSQL中可以使用EXPLAIN命令可以用来了解查询运行的过程。为了获得更精细的视角，需要使用 特殊参数化的

EXPLAIN(ANALYZE, BUFFERS)

EXPLAIN本身提供查询计划，让用户深入了解Postgres打算用来获取或修改数据的操作。这包括顺序扫描、索引扫描、连接、排序等等。该命令应单独用于检查查询计划而不执行。添加ANALYZE混合不仅显示计划的操作，还执行查询并提供实际的运行时统计信息。

例如，可以将估计行数与实际行数进行比较，从而帮助了解Postgres可能出现问题的地方。它还提供每个执行干的操作计时信息。

BUFFERS选项，则提供有关缓冲区使用情况的信息。具体来说，缓冲池中命中了多少块或从底层缓存或磁盘读取了多少块。这提供了有关查询的IO密集程度的底层操作信息。

最佳UI工具选择

要深入了解Postgres的世界时，初学者面临的第一个选择是使用哪个客户端或界面。虽然许多初学者因为pgAdmin的受欢迎程度和可访问性而开始使用它，但随着对Postgres了解一点点加深，就会发现一些更强大和通用的工具可用。

当然PostgreSQL最强大的客户端之一是其内置的命令行工具psql。虽然命令行界面对某些人来说可能看起来令人生畏或不方便，但 psql包含了高效数据库交互的功能。而且，它无需额外部署，始终和数据库共存。Psql在配合上tmux可以让DBA和运维能够轻松管理多个会话和脚本。

对于更加喜欢图形界面的用户，有一些界面可以在用户友好性和高级功能之间提供平衡，而且使用图形界面可以帮助初学者突破学习的屏障，让学习曲线更加平滑。

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

Heidisql

DBeaver

DataGrip

Postico

最后要说的是，无论选择哪种图形工具，都需要投入一些时间来了解其细节psql可能会非常有益。

日志记录设置

与许多系统一样，在Postgres中，日志是信息宝库，可让您详细了解系统的操作和潜在问题。通过启用全面的日志记录，可以领先于问题、优化性能并确保数据库的整体健康状况。

选择要记录的内容：有效记录的关键是知道要记录的内容而不会使系统不堪重负。 通过设置参数：

log_checkpoints = 0,log_autovacuum_min_duration = 0,log_temp_files = 0，

和

log_lock_waits = on，

可以了解检查点、自动清理操作、临时文件创建和锁定等待。这些是一些最容易出现问题的领域，因此对于监控至关重要。

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

需要注意的是，虽然大量日志记录可以提供有价值的洞察力，但它也会带来开销。 如果设置log_min_duration_statement到一个非常低的值。例如，将其设置为 200ms会记录每一条花费比这更长的时间的语句，这既可以提供信息，也可能会降低性能。始终保持谨慎并意识到 “观察者效应” ——监控过程对被观察系统的影响。但如果没有日志中的详细见解，诊断问题就会更具挑战性。

从本质上讲，虽然日志记录是Postgres工具库中一个非常强大的工具，但它需要仔细配置和定期审查，以确保它仍然是一种帮助，而不是一种障碍。

性能扩展

为了维护Postgres数据库的性能和健康状况时，一些扩展可能是是最佳工具套件。比如pg_stat_statements。

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

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

pg_stat_kcache：有助于了解实际的磁盘 IO 和 CPU 使用情况，这正是您识别导致高 CPU 利用率或磁盘 IO 的查询的方法

pg_wait_sampling或者pgsentinel：这两个可以更清晰地显示您的查询在哪里花费时间等待 – 提供所谓的等待事件分析，又称活动会话历史记录分析（类似于 RDS Performance Insights）

auto_explain：此扩展自动记录慢语句的执行计划，使理解和优化它们变得更简单

请记住，这些扩展需要一些初始设置和调整才能获得最佳结果和较低的开销。另外，大多数托管Postgres提供商并不提供pg_stat_kcache等这些插件。

DB分支

数据库的开发和测试过程通常需要复制数据，这可能会占用大量资源、速度缓慢且繁琐。然而，通过精简克隆和分支，有一种更聪明的方法。

精简克隆

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

数据库分支的好处

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

DBLab和ChatGPT

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

从本质上讲，利用精简克隆和数据库分支意味着更快的开发周期、降低的存储成本以及无风险实验的能力。

数据校验

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

数据校验和

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

重要性

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

激活

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

自动清理

Postgres 中的自动清理过程就像数据库的清洁人员。自动清理进程在后台工作，清理旧数据并为新数据腾出空间，以确保数据库保持高效。

Postgres中的INSERT, UPDATE，或者DELETE的操作都会创建行（元组）的一个版本。随着时间的推移，这些旧版本会累积并需要清理。自动清理通过回收存储空间、删除死行来进行清理。它还负责保持表统计信息最新并防止事务 ID 环绕事件。

如果不定期进行自动清理，数据库可能会出现膨胀——数据库保留未使用的空间，这会减慢查询速度并浪费磁盘空间。另一个问题是过时的统计数据，可能导致计划选择次优和性能下降。

配置自动清理使其运行更频繁并更快地完成任务。在高层次上，调整必须在两个方向上进行：

给予autovacuum 更多权限

更多的工作人员，更大的配额，因为默认情况下，它只允许3个工作进程，并且受到相当保守的限制。

让它更频繁地触发

因为默认情况下，只有当元组的10-20%发生重大更改时，它才会触发；

在OLTP中，可能需要将其减少到1%甚至更低。

查询优化

当谈到Postgres的性能时，在大多数情况下，最好“足够好”地优化Postgres 配置，不经常重新审视决策（仅当发生Postgres主要升级等重大变化时），然后完全专注于查询调优。

通过初期调整Postgres配置可以提高性能。但随着应用程序的增长和发展，性能的主要争夺通常从配置转移到查询优化。正确结构化的查询可能是平滑扩展的应用程序和在负载下逐渐停止的应用程序之间的区别。

调整与优化

初学者中有一个常见的误解：“如果我将配置调整得足够好，就不会有问题。” 配置调整至关重要，但这只是开始。 最终，重点将不得不转向不断优化查询。

工具调优化工具

前面提到老的pg_stat_statements是识别有问题查询的宝贵工具。它提供了 SQL语句的排名列表，按各种指标排序。当与 EXPLAIN (ANALYZE, BUFFERS)，我们在上面也讨论过，可以了解查询的执行计划并查明效率低下的地方。

索引维护

在任何关系数据库系统，影响性能的最关键因素是索引，Postgres中更是如此。

随着时间的推移，随着数据的变化，索引变得碎片化并且效率降低。即使使用最新Postgres版本（特别是使用btree优化的Postgres13和14）和经过跳优的autovacuum，索引健康状况仍然会随着时间的推移而下降，同时发生大量写入。

健康指数

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

索引重建

索引不会无限期地保持其最佳结构。它们需要定期重建。此过程涉及创建新版本的索引，这通常会产生更紧凑、更高效的结构。为这些重建做好准备（最好以自动化方式进行）可确保数据库性能保持一致。

清理

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

重申一个关键点：索引至关重要，但像所有工具一样，它们需要维护。保持它们的健康对于维持 Postgres 数据库的快速性能至关重要。

总结

无论刚刚开始使用Postgres的初学者还是有一定基础的Postgres用户我们给出这些技巧都会有会有所获益。

除了这些，Postgres官方的文档和一些教程都是学习的最权威和最佳材料，当然如果你对其代码感兴趣的话，可以直接从代码层次获得更加底层和深入的见解。