Exploring PostgreSQL’s Foreign Data Wrapper and Statistical Functions

Mar 15, 2024 by Robert Gravelle

PostgreSQL, renowned for its robustness and extensibility, offers several helpful functions for both developers and database administrators alike. Among these functions, file_fdw_handler, file_fdw_validator, pg_stat_statements, pg_stat_statements_info, and pg_stat_statements_reset stand out as invaluable tools for enhancing database management and performance optimization. In today’s blog we’ll learn how to use all of these functions as well as how Navicat can help!

PostgreSQL’s Foreign Data Wrapper (FDW) functionality allows seamless integration of external data sources into the database. The file_fdw_handler and file_fdw_validator functions are specifically designed to handle foreign tables backed by files.

The file_fdw_handler function serves as an interface between PostgreSQL and the foreign data source, enabling the execution of SQL queries against files residing outside the database. Let’s consider an example where we want to create a foreign table named external_data referencing a CSV file named data.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');
    

Meanwhile, the file_fdw_validator function ensures the integrity of the options provided when creating a foreign table. It validates if the specified file exists and is accessible. For instance:

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

PostgreSQL’s pg_stat_statements module provides a set of built-in functions for monitoring and analyzing query performance. Among these, pg_stat_statements, pg_stat_statements_info, and pg_stat_statements_reset are indispensable for identifying bottlenecks and optimizing database performance.

pg_stat_statements is a module that records statistics about SQL statements executed by a server. It tracks details such as execution counts, total runtime, and resource usage for each unique query. To enable pg_stat_statements, you need to add it to the shared_preload_libraries configuration parameter in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

After restarting the PostgreSQL server, you can query the statistics using:

        SELECT * FROM pg_stat_statements;
    

pg_stat_statements_info provides additional information about the pg_stat_statements module, such as the version number and the last reset time. It can be queried as follows:

        SELECT * FROM pg_stat_statements_info;
    

Finally, pg_stat_statements_reset resets the statistics collected by pg_stat_statements, allowing you to start afresh with performance monitoring. Simply execute:

        SELECT pg_stat_statements_reset();
    

We can access all of the above functions in Navicat for PostgreSQL or Navicat Premium 16 by expanding the “Functions” section in the Objects Pane:

To execute a function, simply select it from the Objects list and click the Execute Function button:

That will bring up a dialog where you can supply input parameter values:

Click the OK button to execute the function and view the results (or Cancel to abort):

PostgreSQL’s built-in functions, including file_fdw_handler, file_fdw_validator, pg_stat_statements, pg_stat_statements_info, and pg_stat_statements_reset, play a pivotal role in enhancing database management and optimizing query performance. By leveraging these functions effectively, developers and administrators can streamline operations and ensure optimal utilization of PostgreSQL’s capabilities.