Navicat 干货 | 探索 PostgreSQL 的外部数据包装器和统计函数

news/2024/5/10 0:18:01

PostgreSQL 因其稳定性和可扩展性而广受青睐，为开发人员和数据管理员提供了许多有用的函数。在这些函数中，file_fdw_handler、file_fdw_validator、pg_stat_statements、pg_stat_statements_info 以及 pg_stat_statements_reset 是其中的重要函数，成为增强数据库管理和性能优化的宝贵工具。今天的博客，我们一起学习如何使用这些函数以及 Navicat 是如何帮助我们使用这些函数的。

文件函数

PostgreSQL 外部数据包装器（FDW）功能可以将外部数据源无缝地集成到数据库中。Xfile_fdw_handler 和 file_fdw_validator 函数是专为处理由文件支持的外部表而设计的。

file_fdw_handler 函数作为 PostgreSQL 和外部数据源之间的接口，使得可以对位于数据库外部的文件执行 SQL 查询。假设我们想要创建一个名为 external_data 的外部表，该表引用名为 data.csv 的 CSV 文件，我们可以这样做：

CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;CREATE FOREIGN TABLE external_data (id INT,name TEXT,age INT) SERVER file_server OPTIONS (filename '/path/to/data.csv');

与此同时，file_fdw_validator 函数确保在创建外部表时所提供的选项的完整性，它会验证指定的文件是否存在且可访问。例如：

SELECT file_fdw_validator('filename', '/path/to/data.csv');

统计函数

PostgreSQL 的 pg_stat_statements 组件为监控和分析查询性能提供一组内置函数。其中，pg_stat_statements，pg_stat_statements_info 和 pg_stat_statements_reset 是识别瓶颈和优化数据库性能必不可少的函数。

pg_stat_statements 组件是记录服务器中执行的 SQL 语句的统计信息。它可跟踪每个单独查询的详细信息如执行次数，整体运行时间和资源占用情况。启用 pg_stat_statements,你需要将其添加到 postgresql.conf 的配置参数 shared_preload_libraries 中：

shared_preload_libraries = 'pg_stat_statements'

重新启动 PostgreSQL 服务器后，你可以使用以下语句查询统计信息：

SELECT * FROM pg_stat_statements;

pg_stat_statements_info 提供关于 pg_stat_statements 组件的额外信息，比如版本号以及上次重置的时间。你可以通过一下语句进行查询：

SELECT * FROM pg_stat_statements_info;

最后，pg_stat_statements_reset 用于重置由 pg_stat_statements 收集的统计信息，你就可以重新开始性能检测。执行如下：

SELECT pg_stat_statements_reset();

在 Navicat 中使用 PostgreSQL 的内置函数

我们可以在 Navicat for PostgreSQL 或 Navicat Premium 16 的导航面板中展开“函数”部分，来查看上述所有函数:

PostgreSQL_functions_in_Navicat (113K)

执行一个函数，则在对象列表中单击此函数并点击“运行函数”按钮：

execute_function_button (62K)

它会弹出对话框，你可以在其中输入参数值：

input_parameter_dialog (33K)

点击“确定”按钮执行函数并查看结果（或者点击“取消”中止执行）：

pg_stat_statements_results (330K)

PostgreSQL 的内置函数 file_fdw_handler, file_fdw_validator, pg_stat_statements, pg_stat_statements_info, 和 pg_stat_statements_reset，在增强数据库管理和优化查询性能方面发挥着关键作用。通过有效利用这些函数，开发人员和管理员可以简化操作并确保最大化利用 PostgreSQL 的功能。