PostgreSQL 11数据库管理员指南第二章：探密数据库

本书目录请见：PostgreSQL 11数据库管理员指南

要掌握PostgreSQL，你需要在使用中进行了解。一个空数据库就你是没有房子的鬼城。

下面我们会假定你已经安装了数据库。有几千本又关如何从零开始设计你的数据库的书。因此，这里我们旨在帮助那些有探秘数据库日程的人们学习如何使用PostgreSQL数据库管理系统。

最好的开启方式是询问一些简单问题来引导自己并开始进行理解。同时这些也是你在寻求其他人帮助时所要回答的问题。

本章中，我们将讲解如下小节：

• 这是什么类型的服务？
• 服务的版本是什么？
• 服务的启动时间是多少？
• 定位数据库服务端文件
• 定位数据库服务端的消息日志
• 定位数据库的系统标识符
• 列出数据库服务端的数据库
• 数据库中有多少张表？
• 每个数据库使用多大的磁盘空间？
• 每张表使用多大的磁盘空间？
• 哪些是最大的数据表？
• 数据表中有多少行？
• 快速评估数据表中的行数
• 列出数据库中的插件
• 掌握对象依赖

这是什么类型的服务？

PostgreSQL是一款按照非常开放许可分发的对象关系数据库管理系统（ORDBMS），由一个非常活跃的社区进行开发。

有大量PostgreSQL相关的服务和软件（https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases），有开源的，也有由其它软件公司所提供非开源的。这里我们讨论如何辨别所使用的属于哪一种。

通过名称来辨别PostgreSQL的变体并不容易，很多涉及PostgreSQL的产品和服务都包含Postgres或PostgreSQL这个词。

但是，如果你需要查看文档，或购买支持和咨询等服务，则需要知道它到底是哪种类型的服务，因为市场上的选择非常广泛。

如果你在支持证书服务或云服务订阅费，一定已经知道了所支持公司的名称，及所订阅PostgreSQL的具体变体。但是，拥有不同类型的多个服务并不少见，因此能够进行分别的话会非常有益。

如何实现…

不幸的是，并没有适用于所有PostgreSQL的每个变体的某个函数或参数，当前还无法回答这一问题。最接近的函数是下一节服务的版本是什么？中将使用到的version() ，它会返回所运行的版本的文本描述，包含（但不限于）版本号。

在一些情况下，这还不够，这时你需要通过其它线索来决定具体的版本，如以下内容：

• PostgreSQL社区稳定版的版本号要么是X.Y（X=10或以上）要么是X.Y.Z（最大X=9）。附加的版本号通常表示你在运行一个PostgreSQL的变体。
• 某些的对象仅对特定变体才会出现，如某个插件。更多有关插件的知识可参见本章的列出数据库中的插件一节。

扩展知识…

一些云端基于PostgreSQL的服务的version() 会返回社区版PostgreSQL中返回的相同值。但这是不正确的，它们确实运行着相同版本的PostgreSQL，但这并不表示你拥有相同级别的控制。例如，你可能没有超级用户账号，那么也就可能无法自由地安装插件。

服务的版本是什么？

针对数据文件格式、数据目录布局、崩溃还原格式PostgreSQL有一些内部版本号。每个都在服务运行时进行检查来确保数据没有损坏。PostgreSQL在单个发行版本中并不会修改这些内部格式，仅在跨发行版时才会做出调整。

从用户的角度看，每个发行版本在服务端行为会存在不同。如果你很了解自己的应用的话，就应该能够通过阅读每个版本的发行记录来评估这些不同。很多时候，重新测试应用是最安全的方法。

如果你经历了任何与数据库设置和配置相关的问题，需要反复检查你所拥有的服务的版本。这会帮助你报告错误或查询正确版本的手册。

如何实现…

我们将通过直接查询数据库服务端来查找版本：

1. 连接数据库并发送如下命令：
   
   postgres # SELECT version();

   1	postgres # SELECT version();

2. 你会获取类似下面这样的响应：
   
   PostgreSQL 11.2 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit

   1 2	PostgreSQL 11.2 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit

可能信息量有点大。

查看版本号的其它方式如下：

但是小心这显示的是客户端软件的版本号，可能会与服务端软件的版本号不同。应当使用如下命令直接查看服务端版本号：

此处，你必须设置PGDATA为真实的数据目录。参见定位数据库服务端文件一节获取更多信息。

注意前述命令并不会显示维护版本号。

运行原理…

当前的PostgreSQL服务版本格式由两个数字组成，第一个数字为大版本号，第二个为大版本后续的维护版本。在讨论所支持的功能时通常只需要提到大版本号，因为在维护版本中并不会进行改变。

11.0是PostgreSQL 11的第一个版本，后续的维护版本为11.1, 11.2, 11.3，以此类推。因前面的示例中，我们看到PostgreSQL服务端的版本号为11.2。

对于每个大版本，都一个单独版本的手册，因为其功能集并不相同。如果有些功能并没有像你所想的那样，请确保查看正确版本的手册。

扩展知识…

在发行版本10之前，PostgreSQL使用一个三部分数据组成的序列，第一和第二个数字分别表示功能集和兼容性，第三个数据表示维护版本。例如，9.4相对9.3版本添加一些功能及做了兼容性的调整；9.4.0 是9.4的初始版本，9.4.1是后续的维护版本。

PostgreSQL的版本支持政策可通过http://www.postgresql.org/support/versioning/获取。这篇文章讲到每个版本支持的周期为5年。因为每年都会发布一个大版本，也即5个大版本。

所有到9.3（含）版本的支持已于2018年9月结束。因此在读者读到本书的时候，只有PostgreSQL 9.4及以上版本还被支持。更早的版本仍然健壮，但在这些版本中没有很多性能和企业的特性。未来结束支持的日期如下：

服务的启动时间是多少？

你可能会想，服务究竟启动了多长时间呢？

例如，你可能会想要验证如果服务未被监控有没有崩溃，或者查询服务上次重启的时间，藉此来修改配置。通过向数据库服务端询问可以获取这一信息。

如何实现…

通过任意界面执行如下SQL：

你应该会获取到下面这样的输出：

运行原理…

Postgres保存服务启动时间，因此我们可以像下面这样直接访问：

然后，我们可以编写SQL查询来获取启动时间，如下：

最后，我们可以应用这些格式化：

其它内容

这非常简单。更多有关监控和统计的知识会在第八章：监控及诊断中进行讲解。

定位数据库服务端文件

数据库服务文件初始存储在一个称为数据目录的位置。其它的数据文件也可以在存在的表格空间中存储。

本节中，你将学到如何在给定的数据库服务器上找到这些目录的位置。

准备工作

读者需要有数据库系统所在操作系统的访问权限，我们称之为数据库所运行的平台。

如何实现…

以下是系统的默认数据目录的位置：

• Debian或Ubuntu系统： /var/lib/postgresql/MAJOR_RELEASE/main
• Red Hat RHEL, CentOS及Fedora：/var/lib/pgsql/data/
• Windows: C:\Program Files\PostgreSQL\MAJOR_RELEASE\data

MAJOR_RELEASE仅由一部分数字（版本10及以上）或两部分数字（截至9.6版本）组成。

在Debian或Ubuntu系统中，配置文件位于/etc/postgresql/MAJOR_RELEASE/main/，其中main是数据库服务的名称。也可以使用其它名称 。为进行简化，我们假定你能安装一个 PostgreSQL，但在目录路径中包含版本号及数据库服务名称是为了能在同一主机上能同时存在多个数据库服务端。

ℹ️pg_lsclusters仅针对Debian/Ubuntu，显示一个可用数据库服务端的列表，包含以下这些针对每个服务㾍的信息：

• 大版本号
• 端口
• 状态（例如在线或离线）
• 数据目录
• 日志文件

pg_lsclusters是postgresql-common Debian/Ubuntu软件包的一部分，后者提供可安装多版本PostgreSQL及同时可维护多个集群的架构。

在通过 Red Hat RHEL, CentOS和Fedora分发的包中，默认的配置文件（*.conf）中也包含默认数据目录位置。但注意由PostgreSQL社区所分发的包中使用了一个不同的默认位置/var/lib/pgsql/MAJOR_RELEASE/data/。

再强调一下，这只是默认位置。你可以使用initdb工具创建其它的数据目录。

initdb工具将初始内容添加到给定的数据目录中。为方便起见在不存在时会创建该目录，但出于安全考虑，在该数据目录不为空时会停止该工具。initdb工具会通过PGDATA环境变量读取数据目录名称，除非你通过-d命令行选项进行了指定。

运行原理…

虽然 Debian/Ubuntu和Red Hat file的文件布局不同，但它们都遵循Linux文件系统结构标准（FHS），因此两种布局都没有错。

Red Hat系的布局更简单也更易于理解。Debian/Ubuntu系的布局更为复杂，但它有不同且更具开拓性的目标。Debian/Ubuntu的布局类似于其它数据库系统中的最优灵活架构（OFA）。在前面也指出了，它的目标是提供一个文件布局，来允许你可在一个系统中拥有多个PostgreSQL数据库服务端及同时在文件系统中存在多个版本的软件。

另外，Windows和OS X安装器的布局也不相同。可以使用多个数据库集群，但它们比在Debian/Ubuntu上更为复杂。

我推荐你不管使用哪种平台都遵循Debian/Ubuntu的布局。它并没有具体的名称，因此我们将其称为PostgreSQL灵活架构（PFA）。显然，如果你使用的是Debian或Ubuntu，那么已经在使用Debian/Ubuntu布局了。如果使用的是其它平台，你将需要自己进行相关布局，但长期来看这是值得的。要实现PFA，你可以为文件布局的名称部分设置如下环境变量：

本例中，PGDATA为/var/lib/pgsql/10/mamba。

最后，你必须像前面提到那样运行initdb来实际初始化数据目录，并且应在系统经历相似流程时准备自动化脚本来自动化启用或停止数据库服务等动作。

注意initdb这样的服务端应用仅能对一个PostgreSQL大版本起作用。对于像Debian或Ubuntu这样可以允许多个大版本分发的，这些应用放在独立的目录中，而不是放在默认的命令行路径中。这意味着如果只输入initdb，系统会找不到可执行文件，并且会报出错误消息。

这看似一个bug，但事实上是应该有的行为。代替直接访问initdb，你可以通过postgresql-common中使用pg_createcluster工具，它会根据所指定的大版本来选择正确的initdb工具。

ℹ️如果计划在同一台主机上运行多个数据库服务端，必须为每个服务端设置不同的前置变量，因为它们决定着数据目录的名称。例如，可以将它们放在用于启动或停止数据库服务的脚本中，这样就够了，因为PGDATA大多仅由数据库服务端进程所使用。

扩展知识…

一旦定位到了数据目录，就可以找到构成PostgreSQL数据库服务端的文件了。结构如下：

上述的目录均不包含用户可修改文件，也不应为清理空间或其它原因手动删除其中的任何文件。别碰它，因为你可能会让其崩溃而又无法修改。不仔细按照第十一章：备份和还原中所描述的流程对这些目录中的文件进行拷贝也是不明智的。不越雷池一步！

本书稍后会讨论到数据表空间。我们还将在第十章：性能和并发中讨论等性能提升如将事务日志放到一组磁盘中。

你唯一允许碰的是配置文件，都是.conf文件，以及服务消息日志文件。服务消息日志可能在也可能不在数据目录中。更多详情，参见下一节，定位数据库服务端的消息日志。

定位数据库服务端的消息日志

数据库服务消息日志是所有由数据库服务所记录的消息记录。这是在有服务问题时首先要看的地方，经常查看也是有益无害的。

这个日志会包含类似下面这样的消息：

我们在定位到文件时会讲解更多有关这些日志的内容。

准备工作

需要获取对数据库系统的操作系统访问权限，我们称之为运行数据库的平台。

服务端可以放在好几个地方，首先我们把它们列出来这样可以定位到日志或决定想要将其放在何处：

• 服务端日志可能放在数据目录下的目录中。
• 可能会在文件系统的其它目录中。
• 可能会被重定向到系统日志（syslog）中。
• 可能根本就没有服务端日志。此时应尽快添加日志。

如未重定向到系统日志中，服务端日志由一个或多个文件组成。可以修改这些文件的名称，因此在每个系统上名称可能会不同。

如何实现…

以下是默认的服务端日志的位置：

• Debian或Ubuntu系统： /var/log/postgresql
• Red Hat, RHEL, CentOS和Fedora：/var/lib/pgsql/data/pg_log
• Windows系统：消息被发送到Windows的事件日志中。

当前服务端日志的文件名为postgresql-MAJOR_RELEASE-SERVER.log，其中的SERVER是服务的名称（默认为main），MAJOR_RELEASE表示服务的大版本，例如9.6或11（我们在前一节中提到过，从版本10开始大版本号仅由一位数字组成）。一个例子为postgresql-11-main.log，而老的日志文件编号为postgresql-11- main.log.1。最终的数字越大，则文件越老，因为它们使用了logrotate工具进行了轮询。

运行原理…

服务端日志只是一个从服务中记录消息的文件。每条消息有其严重级别，其中最典型的是LOG，但还有其它级别，如下表所示：

要注意FATAL和PANIC。正常服务端操作的大多数情况下这都不应当发生，除一些与复制有关的情况外，可以参见第十二章：复制和升级。

可以通过修改log_min_messages参数来调整日志中消息数量。也可以通过修改log_error_verbosity参数来变更显示的信息的数量。如果消息发送到了标准日志文件中，那么日志中的每行会带有由系统管理员通过log_line_prefix参数控制的有用信息前缀。

可以通过修改其它设置如log_statements、log_checkpoints、log_connections/log_disconnections、log_verbosity和log_lock_waits来修改进入日志中的内容和数量。

扩展知识…

log_destination参数控制日志消息所存储的位置。有效值为stderr、csvlog、syslog和eventlog（后者仅在Windows中）。

日志收集器是将PostgreSQL服务输出到stderr中的所有内容写入到日志文件中的后台进程。这可能是针对问题最可靠的记录日志消息的方式，因为它依赖于更少的服务。

如果使用日志收集器，可通过log_rotation_age和log_rotation_size等设置在控制日志轮询。此外，可通过配置logrotate来执行日志轮询，这在Debian和Ubuntu系统中是默认的设置。

其它内容

监控活动会在第八章：监控及诊断中进行讲解，检查日志只是其中的一个部分。参见第八章：监控及诊断中的生成日汇总日志文件错误一节获取更多详情。

定位数据库的系统标识符

每个数据库服务在数据库初始化（创建）时会分配一个系统标识符。服务端标识符在服务备份、克隆等时保持一致。

很多服务端动作以系统标识符作为键名，因此在报告错误时可能会要求你提供这一信息。

本节中，你将学习如何展示系统标识符。

准备工作

需要通过Postgres OS或其它拥有对服务端软件执行权限的用户进行连接。

如何实现…

要显示系统标识符，我们只需启动如下命令：

注意以上的语法在Debian或Ubuntu系统中无法生效，与定位数据库服务端文件一节中所讲解initdb的原因相同。但在这种情况下并没有postgresql-common替代命令，因此如必须运行pg_controldata，则需如下例中这样指定可执行命令的完整路径：

ℹ️不要在数据目录名前使用-D。这是唯一不需要这么做的PostgreSQL服务应用。

运行原理…

pg_controldata工具是显示服务控制文件的PostgreSQL服务端应用。控制文件位于服务的数据目录内，在数据库初始化时创建。其中的一些信息定时更新，而另一些是在某些大事件发生时进行更新。

pg_controldata的完整输出如下（一些值在服务运行的过程中会发生改变）：

小贴士：不要编辑PostgreSQL控制文件。如若编辑，服务可能无法正确启动，或者你可能会隐藏其它错误。如果做了修改，人们是可以知道的，所以尽快坦白！

列出数据库服务端的数据库

在连接PostgreSQL时，我们总量只连接某个数据服务中的一个指定数据库。如果在一台服务器上有多个数据库，会产生混淆，因此有时你可能只想知道数据库服务端有哪些数据库。

这也是含糊的，因为我们可以在两个不同但关联的上下文中使用数据库一词。首先我们把PostgreSQL看作存储数据的数据库，通过数据库一词来指代整个数据库服务器。PostgreSQL中，数据库服务器（也称为集群）可分为多个单独数据变回，因此，在你逐渐适应使用PostgreSQL时，会开始能够区分这两个概念。

如何实现…

如果你可以访问psql，可以输入如下命令：

在运行psql时也可以仅输入 \l 来获取相同的信息。

我们刚刚看到信息存在名为pg_database的PostgreSQL目录表中。可以直接在任意连接中对该表进行查询来获取更简单的结果，如下：

运行原理…

PostgreSQL启动时有3个数据库：template0, template1和postgres。主用户数据库是postgres。

你也可以创建自己的数据库，如下：

可以使用如下表达式在命令行中实现相同功能：

在创建数据库后，确保进行相应的安全措施，参见第六章：访问权限中的讨论。

在创建另一个数据库时，它实际是获取已有数据库的一个拷贝。一旦创建后，这两个数据库之间就不存在关联了。

template0和template1数据库称为模板数据库。template1可修改为允许你为任意新创建数据库创建一个本地化模板。template0数据库的存在是为了在修改template1后，还有一个原始拷贝可以回溯。换句话说，如果template1搞杂了，可以删除掉然后通过template0重新创建。

你可以删除掉名为postgres的数据库。但不要这么做，好吗？类似地，不要尝试去碰template0，因为除用作模板外不允许使用它来做任何事。另一方面，template1数据库的存在是为了可以修改的，因此请按需要进行修改。

扩展知识…

我们刚刚看到的信息是存储在名为pg_database的PostgreSQL目录表中。我们可以直接查看它来获取更多信息。在一些方式下，输出用途也会更小，因为我们需要在其它表中查找编号：

首先了解\x的用途。它会让psql中的输出每行显示一列，而不是在每行中显示一行。

我们已经讨论过模板了。另一个有趣的事是我们可以对数据库打开连接和关闭连接，并且还可以对它们设置连接限制。

同时，可以看到每个数据库有一个默认表格空间。因此，数据表在指定数据库中创建，数据表中的数据文件放在一个表格空间中。

你也可以看到每个数据库中有一个排序规则（collation），这是定义不同语言功能的方式。我们会在第五章；数据表及数据中的为数据库对象选择良好的名称一节中进行更多的讲解。

数据库中有多少张表？

关系型数据库中数据表的数量是对数据库复杂度很好的度量，因此这是了解任意数据库的简单方式。但是什么样的复杂度呢？复杂数据库可能是主观灵活设计来处理不同的业务场景或一个复杂业务流程可能会在一个有限部分的详情放在数据库中。因此，大量数据表可能会暴露一个复杂业务流程或者仅仅是软件中一个复杂部分。

本节中，我们将展示如何计算数据表的数量。

如何实现…

在任意界面中，输入如下SQL命令：

也可以直接查看数据表列表，并判断该列表是大还是小。

在psql中，可以通过使用如下命令来查看你自己的数据表：

在pgAdmin 4中，你可以在左侧的树状视图中查看数据表，如下图所示：

TODO

运行原理…

PostgreSQL在目录表中存储有关数据库的信息。它们描述数据库定义方式的各个方面。在schema中存储有一个目录表中的主集，名为pg_catalog。第二组目录对象称为信息模式（schema），是SQL访问关联数据库信息的标准方式。

我们应将模式从查询中排除掉。否则，会获取太多信息。我们在前面的查询中通过在WHERE从句中使用 NOT IN语句进行了排除。

ℹ️注意这条查询仅显示PostgreSQL服务器上一个数据库的数据表数量。只能看到当前所连接数据库中的数据表，因此需要轮流对每个数据库运行相同的查询。

扩展知识…

我见过数据库中的独立主表的最高的数量是20,000，不计算分表、视图和工作表。那显示评级为非常复杂的系统：

当然你不能一直轻易地看出哪些表是实体，因此我们只需计算数据表。一些数据库使用大量的分表或近似表，因此数量会急剧上升。我们见过200,000 张（包含各种类别）数据表的数据库。但不推荐这么做，这时数据库的目录表会变得异常大。

每个数据库使用多大的磁盘空间？

为数据库分配足够的磁盘空间非常重要。如果磁盘满了的话，它不会损坏数据，但可能会导致数据库服务异常，最终关闭。

对于规划或空间监控，我们经常需要知道数据库有多大。

如何实现…

我们可以通过如下方式来实现：

• 查看组成数据库服务的文件大小。
• 运行SQL请求来确认数据库大小。

如果查看实际文件的大小，你需要确保包含了数据目录及所有子目录，还有其它包含数据表空间的目录。这会有些费力，也很难包含所有的不同部分。

最简单的方式是对数据库做简单查询，如下：

但是，这仅限于当前数据库。如果想要知道所有数据库整体的大小，需要进行如下的查询：

运行原理…

数据库服务知道要加载哪张表。它也知道如休计算每张表的大小，因此pg_database_size()函数只需查看文件的大小。

每张表使用多大的磁盘空间？

支持的最大小数据表大小为32 TB，并且它不要求操作系统对大文件的支持。文件系统大小的限制不会影响到大表，因为它们会存储在多个最大为1 GB的文件中。

大表会带来性能的问题。索引会花费更多的时间来更新并且查询性能会下降。本节中，我们将学习如何计算表格的大小。

如何实现…

我们可以通过如下命令来了解数据表的大小：

这条命令的输出如下：

我们也可看到数据表的总大小，包含索引及其它相关空间，如下：

输出如下：

我们还可以像下面这样使用psql命令：

运行原理…

在PostgreSQL中，数据库由多个关联组成。主关联是数据表。此外，有很多不同的其它数据文件。数据表中创建的每个索引也是关联。长数据值放在名为TOAST的二级表中，因此，在大部分情况下，每张表也会有TOAST表和一个TOAST索引。

每个关联由多个数据文件组成。主数据文件分隔为1 GB的大小。第一个文件没有后缀，其它数据块使用数字作为后缀（如2）。还有一些文件标记为_vm和_fsm，分别表示可视化映射和空闲空间映射。它们用作运维的一部分。它们对于超大表也会非常小。

扩展知识…

前面计算关联大小的函数，输出字节数，通常会过大而不够直观。可以使用pg_size_pretty()函数来将数字格式化的更好看，如下例所示：

这会产生如下输出：

TOAST表示The Oversized-Attribute Storage Technique（超大属性存储技术）。如名称所表示的，这是一种用于存储长列值的机制。PostgreSQL允许很多数据类型存储多达1 GB的值。在适当时，值自动进行压缩并在分割和存储前进行解压缩，因此实际限制根据可压缩性存在不同。

你可能还会看到以_init结尾的文件，它们由unlogged表和它们的索引所使用，用于在崩溃后修复它们。unlogged对象这么叫是因为它们不生成预写日志（WAL）。因此它们支持更快速的写入，但在崩溃的时候必须进行截取，即还原来空状态。

哪些是最大的数据表？

我们学习了如何获取具体表的大小，是时候将问题扩展到相关领域了。不是获取具体表的绝对数值，我们来了解相对大小。

如何实现…

以下基本查询会告诉我们最大的10张表：

数据表以大小降序进行显示，最多显示10行。本例中，我们看到了除information_schema或pg_catalog数据表之外的所有模式中的所有表，类似数据库中有多少张表？一节中那样。

运行原理…

PostgreSQL提供独立的pg_relation_size函数来计算具体表或索引所使用的磁盘空间。我们只需提供表名即可。除主数据文件外，还有其它文件（称为派生-fork）可通过指定第二个可选参数来进行计算。这些包含可视化映射（VM）、空闲空间映射（FSM）和其它无预写日志对象。

数据表中有多少行？

关于表的行数没有设限，但它会受到可用磁盘空间和内存/swap空间。如果存储超过2 KB 累计数据大小的行，那么最大行数可能在40亿行或以内。

计算是SQL中最简单的一种语句，因此它也是很多人对于PostgreSQL查询的第一体验。

如何实现…

通过任意界面，使用如下SQL命令来计算行数：

这会返回一个整数值来作为结果。

在psql中，命令运行如下：

运行原理…

PostgreSQL可以选择两种技术来计算SQL的 count(*)函数。在当前支持的版本中均可使用：

• 第一种称为顺序扫描（sequential scan）。我们一个接一个地访问每个数据块，读取每个数据块中的行数。如果数据表在磁盘上，这会引发受益磁盘访问模式，语句会非常快。
• 另一种技术称为索引扫描（index-only scan）。它要求数据表中有索引，它涵盖比通过count(*)优化SQL查询更通用的情况，因此我们会在第十章：性能和并发中进行深入讲解。

有些人认为count SQL 语句是对数据库管理系统（DBMS）一个很好的测试。有些DBMS有对count SQL语句具体的优化功能，Postgres使用索引扫描来进行优化。PostgreSQL项目对此进行了多次讨论，但很少人觉得应对其进行优化。是的count函数在应用中经常使用，但没有WHERE语句它的用处就没那么大。因此实现了索引扫描功能，它更适用于实际情况，包括本节。

因Postgres中一个名为多版本并发控制（MVCC）的主要特性，我们扫描表的每个数据块。MVCC允许我们在对数据表插入、更新或删除数据的同时运行count SQL语句。这是一个很酷的功能，我们在Postgres上花了很大功夫来提供这一功能。

MVCC要求我们记录数据表中每行的信息，并说明何时进行了修改。如果修改在SQL语句开始执行之后发生，则忽略这些修改。这表示我们需要对数据表的每行执行可视化检查，来让我们处理count SQL语句的结果。索引扫描所提供的优化是跳过对当前会话已经可见的数据块的检查。这些数据块中的行数可直接通过索引计算，这通常要比数据表更小，因此也更快。

如果你再深入地思考一下，会发现count SQL语句的结果只是时间上某一刻的值。根据数据表中所发生的， count SQL语句执行时可能会发生很小或很大的改变。因此，一旦你执行了，所获知的是，在过去的某个时间点上，数据表中具体有 x 行。

快速评估数据表中的行数

我们并不总是需要精确的行数，尤其是对需要花很长时间执行的大表。管理员经常需要评估一张表有多大，来估算其它运算可能会花费的时间。

如何实现…

我们可以大概使用Postgres优化器相同的方式来估算数据表的行数：

这会给出如下输出：

这很快就行返回行数，而不论我们所查看的表有多大。你可能会希望为前面的计算创建一个SQL函数，这样就无需每次都重新输入SQL代码。

如下的函数使用称为外推法（extrapolation）的数学过程估算总行数。换句话说，我们从最近一次数据集合中接收每行的平均字节数，将其应用于当前的数所表大小：

运行原理…

在此前我们学到了pg_relation_size()函数，因此我们知道它带回了当前数据表大小的精确值。

在清空 Postgres数据表时，我们在数据表的pg_class目录登记中记录了两条信息。这两项为数据表中的数据块数（relpages）和数据表的行数（reltuples）。有些人认为他们可以使用pg_class中的reltuples值进行估算，但它严重的过时。使用另一张名为pg_stat_user_tables的表中的信息也会让你获取错误信息，这将在第十章：性能和并发中进行详细的讨论。

Postgres优化器使用relpages和reltuples值来计算每个数据块的平均行数，这也称为平均元组密度。

若我们假定平均元组密度保持不变，那么可以使用如下公式来计算行数：预估行数 = 数据块数量 * 每块的行数。

我们包含一些代码来处理reltuples或relpages字段为0的情况。此时Postgres优化器的实际工作要比我们所执行的复杂一些，因此估算并不太好。

WHERE oid = ‘mytable’::regclass;语法引入了对象标识符类型的概念。它们只是使用了简写的技巧来将对象名转化为针对该对象的对象标识符数。理解它最好的方式是把该语句看作与名为relname2relid()的函数相同。

扩展知识…

前面所提到的好的方面是不论表有多大，它会返回相同时间的值。不好的方面是pg_relation_size() 要求对数据表执行锁，因此在其它用户对表格添加AccessExclusiveLock锁的时候，数据表大小的估算会等待锁的释放再返回值。

那么…AccessExclusiveLock锁又是什么呢？在执行SQL维护动作时，比如修改字段的数据类型时，PostgreSQL会锁定对该表的其它所有动作，将锁置为AccessShareLock模式。就我而言，一个典型的用例是在我发布一些SQL维护动作时，如ALTER TABLE，语句会花费比我设想更长的时间。这时我们会想难道数据表比我所以为的要大得多？需要等待多久呢？是的，最好提前进行计算，但事后诸葛亮并不能让你走出当下的困境。那么，我们需要一种不用锁并计算数据表大小的方式。

我的方案是查看Postgres用于存储数据的操作系统文件，并推导到它们有多大。

此时，这会变得有些困难。如果数据表锁定的话，PostgreSQL可能对表执行某种操作，那么尝试查看文件大小可能同样无果或得到错误的答案。以下是我们所需执行的步骤：

1. 首先，获取pg_class中该数据表的详情：
   
   SELECT reltablespace, relfilenode FROM pg_class WHERE oid = 'mytable'::regclass;

   1 2	SELECT reltablespace, relfilenode FROM pg_class WHERE oid = 'mytable'::regclass;

2. 然后，确定数据表所在的databaseid：
   
   SELECT oid as databaseid FROM pg_database WHERE datname = current_database();

   1 2	SELECT oid as databaseid FROM pg_database WHERE datname = current_database();

reltablespace、databaseid和relfilenode是我们用于在数据目录中定位基础数据文件的三个共同的内容。

如果reltablespace为0，那么文件会在如下的位置：

数据表越大，你会看到越多的文件。如果reltablespace不为0，文件会在如下的位置：

随最后一个文件外，每个文件的大小应为1 GB。

前面的讨论还可总结如下小点：

• Postgres使用数据块和页面来指代相同的概念。Postgres也通过元组和行实现相同的功能。
• 一个数据块的大小默认为 8,192 字节。如果你自己重新编译服务并创建新数据库的话，可以修改该值。

以下是基本实现pg_relation_size功能的函数，并且不使用锁。正因此，它总是很快速，但如果数据表同时在进行大量的修改时可能会得到错误的结果：

这个函数做一些很小的修改就可以在Windows上使用，这个留作读者练习。

列出数据库中的插件

每个PostgreSQL数据库包含创建数据时自动带入的对象。每个用户会发现一个pg_database系统目录，可列出如列出数据库服务端的数据库一节中的数据库。查看这些对象是否存在意义不大，因为即使是超级用户也无法删除它们。

另一方面，PostgreSQL带有几十个可选对象的集合，称为模块，或对等的插件。数据库管理员可以根据要求安装或卸载这些对象。它闪不会自动包含一个新创建的数据库，因为可能会根据用例不需要它们。用户会在需要时仅安装他们实际所需的插件，插件可以在数据库启动并运行时进行安装。

本节中，我们将讲解如何列出当前数据库中所安装的插件。这对于更熟悉数据库很重要，同时也因为一些插件会影响到数据库的行为。

如何实现…

在PostgreSQL中， 一个目录表存储所安装插件的列表，因此这一节非常简单。执行如下命令：

结果见如下输出：

ℹ️注意格式进行了扩展，就好像执行了\x 元命令一样。

要获取包含更少技术细节的相同列表，可以在列出数据库时使用\dx元命令。

运行原理…

PostgreSQL由控制文件 .control所体现，位于SHAREDIR/extension 目录下，外加一个或多个包含实际插件对象的文件。控制文件指定插件名、版本和其它对插件基础架构的信息。每次插件安装、卸载或升级到新版本时，pg_extension目录表中对应的行就会分别进行插入、删除或更新。

扩展知识…

本节中，我们仅讨论了通过PostgreSQL分发的插件，并且目的仅是列出当前数据库中所使用插件。插件的基础架构会在第三章：数据库配置中进行更深入的讲解。我们将讨论插件的版本号，并展示如何安装、卸载和升级插件，包含那些独立于PostgreSQL所分发的插件。

其它内容

要获取到哪些插件可供使用，可以浏览与PostgreSQL所共同分发的其它模块的列表，几乎是https://www.postgresql.org/docs/current/static/contrib.html中的所有插件。

掌握对象依赖

在大部分数据库中，数据库中对象会存在依赖关系。有时，我们需要掌握这些依赖来了解如何执行一些动作，如修改或删除已有对象。下面进行详细的了解。

准备工作

我们将使用如下的简单数据库来进行理解和探讨：

1. 创建如下的两张表：
   
   CREATE TABLE orders ( orderid integer PRIMARY KEY ); CREATE TABLE orderlines ( orderid integer ,lineid smallint ,PRIMARY KEY (orderid, lineid) );

   1 2 3 4 5 6 7 8

   CREATE TABLE orders ( orderid integer PRIMARY KEY ); CREATE TABLE orderlines ( orderid integer ,lineid smallint ,PRIMARY KEY (orderid, lineid) );

2. 下面，对它们添加链接来强制所谓的引用完整性（referential integrity），如下：
   
   ALTER TABLE orderlines ADD FOREIGN KEY (orderid) REFERENCES orders (orderid);

   1 2	ALTER TABLE orderlines ADD FOREIGN KEY (orderid) REFERENCES orders (orderid);

3. 如果我们尝试删除所引用的表，会得到如下消息：
   
   DROP TABLE orders; ERROR: cannot drop table orders because other objects depend on it DETAIL: constraint orderlines_orderid_fkey on table orderlines depends on table orders HINT: Use DROP ... CASCADE to drop the dependent objects too.

   1 2 3 4 5

   DROP TABLE orders; ERROR: cannot drop table orders because other objects depend on it DETAIL: constraint orderlines_orderid_fkey on table orderlines depends on table orders HINT: Use DROP ... CASCADE to drop the dependent objects too.

要非常小心！如果按照提示，你可能会误删除对orders表拥有依赖的所有对象。你可能觉得这是很好的想法，但却不是正确的做法。它可能没问题，但我们需要确保它会正常运行。

因此，我们需要知道当前对orders表有哪些依赖，并进行审查。然后，可以决定执行CASCADE版本的命令是否ok，或者是否要手动的调节当前状况。

如何实现…

可以通过psql执行如下命令来展示有关数据表的所有信息、定义在数据表中的约束以及引用它的约束：

我们也可以通过使用如下查询获取约束的具体详情：

前述的查询仅包含数据表之间的约束。这并是全部的内容，所以请阅读扩展知识…一部分。

运行原理…

在创建外键时，我们对目录表添加了一个约束，称为pg_constraint。因此，该查询向我们展示依赖于orders表的所有约束。

扩展知识…

使用Postgres时，总是有一些表面以外的内容。此时，还有很多内容，也非常重要。

我们没有讨论与其它类型对象的依赖。两种可能对数据表存在依赖的对象是视图和函数。

考虑使用如下命令：

如果执行这条命令，对任意视图的依赖都会防止数据表的删除。因此，你需要删除那些视图然后再删除数据表。

函数依赖就没有那么有用了。函数和数据表之间的关联以及函数间依赖的信息都不在目录中记录。这部分是因为大部PostgreSQL过程语言允许动态查询的执行，因此在函数执行前无法告知它会访问哪张表或哪个函数。这只是部分原因，因为大部分函数都清晰地引用其它数据表和函数，因此它应该可以识别并存储这些依赖。但是目前我们不这么做。所以需要自行手动记录函数的依赖信息，这样就知道删除删除或修改函数所依赖的数据表或其它对象的操作或时机是否合适。

喜欢 (0)赏

[]

分享 (0)