PostgreSQL: 适合所有人的数据字典（PgDD扩展）

飞象数据 2022-12-10

968

对于任何存储和使用数据的人来说，数据字典都是一个重要的工具。PgDD扩展 (https://github.com/rustprooflabs/pgdd)使在 Postgres 中检查和探索数据结构变得容易。这篇文章展示了 PgDD 如何为各种用户提供对您的数据库的当前和准确信息的访问：

分析师
DBA 和开发人员
企业

来自 PgDD 的数据字典信息可以使用标准 SQL 通过查询一小组视图来获得。

背景

包括 Postgres 在内的关系数据库跟踪数据字典所需的大部分信息。这是在底层系统目录(https://www.postgresql.org/docs/current/catalogs-overview.html)中完成的；Postgres 的系统目录在pg_catalog
模式中。使用系统目录的挑战在于它们对于查询通常需要的详细信息类型不是很友好。PgDD 没有做任何神奇的事情，它只是 Postgres 系统目录的一个包装器！

psql
使用带有斜杠命令的客户端可以找到类似的信息。这些斜杠命令（例如\dt
，\dn
等）在使用时很方便，psql
但仅限于使用psql
。

PgDD 的接口只是对dd
模式中的视图使用标准 SQL 查询。PgDD 中可用内容的基本思想可以通过在视图中查询dd模式中的 dd.views
视图来找到。这显示了当前可用的五 (5) 个 PgDD 视图:columns
、functions
、schemas
、tables
和views
。

ELECT s_name, v_name, description
    FROM dd.views
    WHERE s_name = 'dd'
    ORDER BY v_name
;


┌────────┬───────────┬────────────────────────────────────────────────────────────────────┐
│ s_name │  v_name   │                            description                             │
╞════════╪═══════════╪════════════════════════════════════════════════════════════════════╡
│ dd     │ columns   │ Data dictionary view: Lists columns, excluding system columns.     │
│ dd     │ functions │ Data dictionary view: Lists functions, excluding system functions. │
│ dd     │ schemas   │ Data dictionary view: Lists schemas, excluding system schemas.     │
│ dd     │ tables    │ Data dictionary view: Lists tables, excluding system tables.       │
│ dd     │ views     │ Data dictionary view: Lists views, excluding system views.         │
└────────┴───────────┴────────────────────────────────────────────────────────────────────┘

当前版本是 PgDD 0.4.0，有关 PgDD 的历史和进展的更多信息，请阅读此处(https://blog.rustprooflabs.com/2021/10/pgdd-extension-using-pgx-rust)和此处(https://blog.rustprooflabs.com/2019/11/pgdd-now-postgresql-extension)。

对于分析师

分析师需要了解他们正在查询和使用的数据，最好是在他们最熟悉的工具中。他们对数据的理解使他们能够准确地分析和传播数据。我与分析师一起使用的一些工具是 psql、DBeaver、Python（Jupyter Notebooks）和 QGIS。可以从这些工具中的任何一个轻松查询 PgDD。

描述一列

为了说明分析师的常见场景，我从我的帖子中使用 PostGIS 在 OpenStreetMap 中查找丢失的交叉点中(https://blog.rustprooflabs.com/2021/11/postgis-find-openstreetmap-missing-crossing)提取了一个查询，下面的查询来自步骤 4a。想象一下，您正在查看此代码并发现自己想知道 t.osm_type 是什么意思？

-- Crossing aggregates
WITH d AS (
SELECT p.osm_id, COUNT(*) AS crossings
    FROM foot_points p
    INNER JOIN osm.traffic_point t
        ON t.geom = p.geom AND t.osm_type = 'crossing'
    GROUP BY p.osm_id
)
UPDATE foot_roads r
    SET crossings = d.crossings
    FROM d 
    WHERE r.osm_id = d.osm_id
;

我是这个查询的作者，我创建了 PgOSM Flex(https://github.com/rustprooflabs/pgosm-flex)使用的数据结构。即便如此，我对那个特定列的含义也只有一个基本的记忆。对于确切的、细致入微的细节，我仍然需要查看文档。

使用dd.columns
视图的快速查询可以告诉我们数据类型 ( text
) 并提供列的描述。此描述告知列中的特定值osm_type
及其与osm_subtype
列的交互。最好的部分是无论我在哪里编写提示问题的 SQL 查询，我都可以简单地添加一个针对dd
视图的查询来回答问题。然后回到我的查询，不用离开我选择的工具。

SELECT c_name, data_type, description
    FROM dd.columns
    WHERE s_name = 'osm'
        AND t_name = 'traffic_point'
        AND c_name = 'osm_type'
;


┌──────────┬───────────┬──────────────────────────────────────────────────────────────────────────────┐
│  c_name  │ data_type │                                 description                                  │
╞══════════╪═══════════╪══════════════════════════════════════════════════════════════════════════════╡
│ osm_type │ text      │ Value of the main key associated with traffic details.  If osm_subtype IS NU…│
│          │           │…LL then key = "highway" or key = "noexit".  Otherwise the main key is the va…│
│          │           │…lue stored in osm_type while osm_subtype has the value for the main key.     │
└──────────┴───────────┴──────────────────────────────────────────────────────────────────────────────┘

使用 Postgres的COMMENT ON
语法添加数据库对象描述。理想情况下，数据库对象在创建时就记录在案。但是，如果您的对象今天没有得到很好的记录，那么很容易开始改进！

查找数据

我发现自己问的另一个问题是“我还能在哪里找到这些数据？” 有时我知道一个列存在，但不知道它在哪些表或视图中。例如，我知道 PgOSM Flex 加载的一些表有该address
列，我想知道哪些表。使用 PgDD 进行简单查询，我有一个表、视图和物化视图的列表，每个都有一address
列。

SELECT s_name, t_name, source_type, data_type
    FROM dd.columns
    WHERE s_name = 'osm'
        AND c_name = 'address'
;


┌────────┬──────────────────┬───────────────────┬───────────┐
│ s_name │      t_name      │    source_type    │ data_type │
╞════════╪══════════════════╪═══════════════════╪═══════════╡
│ osm    │ poi_line         │ table             │ text      │
│ osm    │ poi_point        │ table             │ text      │
│ osm    │ poi_polygon      │ table             │ text      │
│ osm    │ amenity_line     │ table             │ text      │
│ osm    │ amenity_point    │ table             │ text      │
│ osm    │ amenity_polygon  │ table             │ text      │
│ osm    │ building_point   │ table             │ text      │
│ osm    │ building_polygon │ table             │ text      │
│ osm    │ shop_point       │ table             │ text      │
│ osm    │ shop_polygon     │ table             │ text      │
│ osm    │ vbuilding_all    │ view              │ text      │
│ osm    │ vshop_all        │ view              │ text      │
│ osm    │ vpoi_all         │ materialized view │ text      │
└────────┴──────────────────┴───────────────────┴───────────┘

对于 DBA 和开发人员

上面的分析师用例示例也可以轻松应用于 DBA 和开发人员角色。这些更具技术性的角色负责创建和维护数据结构，还关心有关所包含数据的大小和形状的细节。

dd.schemas
视图很好地概述了按模式名称 ( s_name
) 分组的数据库中的内容。此视图包括磁盘大小，并提供表、视图和函数的统计。此视图对于获取数据库的全局视图很有用，如果数据库被很好地组织成逻辑模式，则特别有用。以下查询过滤了三 (3) 个特定模式，结果显示该osm
模式具有很多表 (41)，大小接近 2 GB。

SELECT s_name, size_plus_indexes,
        table_count, view_count, function_count,
        description
    FROM dd.schemas
    WHERE s_name IN ('osm', 'osm_routing', 'public')
;


┌────────────┬───────────────────┬─────────────┬────────────┬────────────────┬────────────────────────────────────────┐
│   s_name   │ size_plus_indexes │ table_count │ view_count │ function_count │              description               │
╞════════════╪═══════════════════╪═════════════╪════════════╪════════════════╪════════════════════════════════════════╡
│ osm        │ 1763 MB           │          41 │          6 │              3 │ Schema populated by PgOSM-Flex.  SELEC…│
│            │                   │             │            │                │…T * FROM osm.pgosm_flex; for details.  │
│ osm_routin…│ 239 MB            │           3 │          0 │              0 │ ¤                                      │
│…g          │                   │             │            │                │                                        │
│ public     │ 7544 kB           │           3 │          2 │           1051 │ standard public schema                 │
└────────────┴───────────────────┴─────────────┴────────────┴────────────────┴────────────────────────────────────────┘

表

另一个常见任务是根据表的大小跟踪表。表大小通常使用行数来讨论，但磁盘大小也很重要。下一个查询按磁盘大小返回五 (5) 个最大的表以及它们的行数和bytes_per_row
估计值。随着时间的推移保存这些数据的快照有助于跟踪和管理数据增长。

SELECT s_name, t_name, rows, size_pretty, size_plus_indexes, bytes_per_row
    FROM dd.tables 
    ORDER BY size_bytes DESC
    LIMIT 5
;


┌─────────────┬──────────────────┬─────────┬─────────────┬───────────────────┬───────────────┐
│   s_name    │      t_name      │  rows   │ size_pretty │ size_plus_indexes │ bytes_per_row │
╞═════════════╪══════════════════╪═════════╪═════════════╪═══════════════════╪═══════════════╡
│ osm         │ tags             │ 3076011 │ 538 MB      │ 631 MB            │           184 │
│ osm         │ road_line        │  770203 │ 220 MB      │ 284 MB            │           300 │
│ osm         │ building_polygon │  771073 │ 203 MB      │ 261 MB            │           276 │
│ osm         │ water_line       │  434721 │ 180 MB      │ 216 MB            │           435 │
│ osm_routing │ roads_noded      │  193652 │ 107 MB      │ 163 MB            │           578 │
└─────────────┴──────────────────┴─────────┴─────────────┴───────────────────┴───────────────┘

按类型查找列

当我在新数据库中进行探索时，我检查的一件事是用作主键/外键的INTEGER
( int4) 列。对于这些情况，我建议使用BIGINT
( int8
) 而不是INT。
以下查询帮助我开始构建BIGINT类型更改为的潜在候选者列表。

SELECT c_name, data_type
    FROM dd.columns
    WHERE data_type = 'int4'
        AND s_name = 'osm'
    GROUP BY c_name, data_type
;


┌─────────────┬───────────┐
│   c_name    │ data_type │
╞═════════════╪═══════════╡
│ admin_level │ int4      │
│ ele         │ int4      │
│ layer       │ int4      │
│ levels      │ int4      │
│ maxspeed    │ int4      │
└─────────────┴───────────┘

如果上述查询id
中的任何列的名称中有 ( c_name
)，我一定会仔细查看并评估是否INT
建议BIGINT
迁移。在此数据库的情况下，int4
上述结果中显示的列存储来自 OpenStreetMap 的属性值。这些列中的每一个都被限制在 21 亿阈值以下，int4
并且可以保持原样。

列中的值maxspeed
以 km/hr 为单位，使 2.1 B km/hr 成为允许的最大值（准确地说是 2,147,483,647 km/hr）。那是光速的 1.99 倍！考虑到这些数据是关于来自 OpenStreetMap 的地球上的道路......我认为我们在这个限制下是安全的。

功能

该数据库的public
架构报告有 1,051 个函数。在我的数据库中，public模式中有很多功能是安装 PostGIS 和 pgRouting 的结果。说到 PostGIS，如果您想知道哪些 PostGIS 函数支持该GEOGRAPHY
数据类型，怎么办？使用 PgDD 很容易！

下面的查询显示了如何过滤argument_data_types列以包含地理信息。它还过滤以st开头的函数名（f_name），以将结果限制为主要的PostGIS函数。

SELECT DISTINCT f_name
    FROM dd.functions
    WHERE s_name = 'public'
        AND argument_data_types LIKE '%geography%'
        AND LEFT(f_name, 2) = 'st'
;

GEOGRAPHY
此查询返回 25 个主要的 PostGIS 函数，它们作为输入提供支持。结果示例如下所示。

┌─────────────────┐
│     f_name      │
╞═════════════════╡
│ st_area         │
│ st_asgeojson    │
│ st_buffer       │
│ st_distance     │
│ st_intersects   │
│ st_length       │
│ st_setsrid      │
│ st_srid         │
└─────────────────┘

对于企业

企业是由人组成的。企业人员需要了解他们使用的数据。从创建和维护数据结构的 DBA 和开发人员，到使用、汇总和报告数据的分析师，再到制定决策的高管和实施决策的人员……理解数据是关键！一个好的数据字典可以提高整个数据团队的数据管理能力，消除对数据的混淆，并使企业能够更好地长期支持其数据库。

除了核心数据团队，PgDD的结果可以从数据库中取出，并以适合您特定需求的格式与组织共享。这里的目标是消除黑箱数据的概念，即整个企业的用户都知道他们有数据，但却不觉得他们了解数据。PgDDUI项目是一个离线、浏览器内数据字典概念的粗略证明。这种类型的文档可以在内部共享，为非数据库用户提供有关其组织可用数据的更详细信息。

概括

这篇文章介绍了 Postgres 的 PgDD 扩展，以及它如何帮助多个级别的用户从数据库中的数据字典中受益。通过有限数量的视图提供有用的详细信息，PgDD 易于学习、易于使用和易于共享。为多种类型的用户提供相同的信息可以提高跨团队对数据的理解。

数据字典数据库视图 postgresql

文章转载自飞象数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。