You can use the duckdb.postgres_role configuration parameter to specify the name of the role that can call pgpro_duckdb functions:

      postgres=# show duckdb.postgres_role;
       duckdb.postgres_role
      ----------------------

      (1 row)

By default, the duckdb.postgres_role configuration parameter is set to NULL, meaning only a superuser can call pgpro_duckdb functions:

      postgres=> SELECT usename, usesuper FROM pg_user WHERE usename = current_user;
       usename | usesuper
      ---------+----------
       user1   | f
      (1 row)

      postgres=> SELECT * FROM duckdb.query('SELECT * FROM a;');
      ERROR:  DuckDB execution is not allowed because you have not been granted the duckdb.postgres_role

To allow members of a user role to call pgpro_duckdb functions, specify the name of the required role, then reload the server:

      postgres=# ALTER SYSTEM SET duckdb.postgres_role TO 'user1';
      ALTER SYSTEM
      -- after server reboot --
      postgres=# show duckdb.postgres_role ;
       duckdb.postgres_role
      ----------------------
       user1
      (1 row)

You can use the duckdb.disabled_filesystems configuration parameter to specify the storages that will be disabled for the pgpro_duckdb extension.

Possible values:

By default, the local storage is disabled:

      postgres=> show duckdb.disabled_filesystems;
       duckdb.disabled_filesystems
      -----------------------------
       LocalFileSystem
      (1 row)

To specify multiple storages, separate their names with commas without spaces:

      postgres=# ALTER SYSTEM SET duckdb.disabled_filesystems TO 'LocalFileSystem,HTTPFileSystem';
      ALTER SYSTEM
      -- after server reboot --
      postgres=# show duckdb.disabled_filesystems;
        duckdb.disabled_filesystems
      --------------------------------
       LocalFileSystem,HTTPFileSystem
      (1 row)

        postgres=> show duckdb.disabled_filesystems;
         duckdb.disabled_filesystems
        -----------------------------
         LocalFileSystem
        (1 row)

        postgres=> COPY test TO 'out.parquet';
        ERROR:  (PGDuckDB/DuckdbUtilityHook_Cpp) Permission Error: File system LocalFileSystem has been disabled by configuration
        postgres=> SELECT * FROM read_parquet('test.parquet');
        ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: 'Permission Error: File system LocalFileSystem has been disabled by configurationinput>

        postgres=> show duckdb.disabled_filesystems;
         duckdb.disabled_filesystems
        -----------------------------

        (1 row)

        postgres=> COPY test TO 'out.parquet';
        COPY 7
        postgres=> SELECT * FROM read_parquet('out.parquet');
         a | b
        ---+---
         1 | 2
        ...
        (7 rows)

        postgres=> show duckdb.disabled_filesystems ;
         duckdb.disabled_filesystems
        -----------------------------

        (1 row)

        postgres=> SELECT pg_read_file('postgresql.auto.conf');
        ERROR:  permission denied for function pg_read_file

        postgres=> SELECT * FROM duckdb.query('SELECT content FROM read_text(''postgresql.auto.conf'')');
                              content
        -------------------------------------------------------
        # Do not edit this file manually!                    +
        # It will be overwritten by the ALTER SYSTEM command.+

        postgres=> show duckdb.disabled_filesystems ;
         duckdb.disabled_filesystems
        -----------------------------
         LocalFileSystem
        (1 row)

        postgres=> SELECT * FROM duckdb.query('SELECT content FROM read_text(''postgresql.auto.conf'')');
        ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: 'Permission Error: File system LocalFileSystem has been disabled by configuration

        postgres=> SELECT * FROM read_parquet('s3://bucket1/results.parquet');
         a | b
        ---+---
         1 | 2
         2 |
        (2 rows)

        postgres=> COPY a TO 's3://bucket1/a.parquet';
        COPY 8

        postgres=# DROP USER MAPPING FOR user1 SERVER my_secret1;
        DROP USER MAPPING

        postgres=> show duckdb.disabled_filesystems ;
          duckdb.disabled_filesystems
        ------------------------------
          LocalFileSystem,S3FileSystem
        (1 row)

        postgres=> SELECT * FROM read_parquet('s3://bucket1/results.parquet');
        ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: 'Permission Error: File system S3FileSystem has been disabled by configuration
        postgres=> COPY a TO 's3://bucket1/a.parquet';
        ERROR:  (PGDuckDB/DuckdbUtilityHook_Cpp) Permission Error: File system S3FileSystem has been disabled by configuration

OLAP data is exported using the COPY command. You can export an entire Postgres Pro table:

      
        COPY tbl TO 'output.parquet' (FORMAT parquet);
      
    

Or only the required OLAP data:

      
        COPY (SELECT * FROM tbl) TO 'output.parquet' (FORMAT parquet);
      
    

You can follow guidelines for directory structure and export to Parquet files:

      
        COPY (SELECT * FROM tbl) TO '/rootpath/db_name/schema_name/ table_name'
        (FORMAT parquet, COMPRESSION zstd, FILE_SIZE_BYTES 1g);
      
    

To export OLAP data to an S3 storage, specify the required connection information. For example, if you have an instance of the MinIO S3 storage deployed, perform the following steps under the postgres user:

To organize OLAP data by partition keys within the Postgres Pro table directory using Hive partitioning, specify the PARTITION_BY parameter when exporting data:

      
        COPY (SELECT * FROM tbl) TO 's3://bucket/db_name/schema_name/table_name' (FORMAT parquet, PARTITION_BY (year, month), COMPRESSION zstd);
      
    

Since writing a large number of Parquet files is a resource-intensive OLAP operation, select partition keys in a way that the size of each resulting partition is at least 100 MB. You cannot configure the size of Parquet files along with the PARTITION_BY parameter. The number of Parquet files created in each directory (partition) corresponds to the number of pgpro_duckdb threads.

For more information about the COPY command parameters and Parquet format, see the official DuckDB documentation on statements and Parquet files.

The COPY command is executed in a transaction so the snapshot of the exported Postgres Pro table is consistent. You can use an explicit transaction to consistently export multiple tables by continually executing the COPY command.

Open transactions delay vacuum, which can negatively impact performance and increase the size of tables. To avoid this, perform the following steps:

Note that you can specify the number of postgres processes for reading tables and the number of pgpro_duckdb threads for exporting OLAP data to Parquet files. However, the conversion from columnar to row view is currently performed in a single thread.

If an error occurs when exporting OLAP data to Parquet files, for example due to insufficient disk space, partially exported files are not deleted. For such cases, configure automatic deletion and work continuation.

After exporting OLAP data to Parquet files, you can work with them as you would with standard Postgres Pro tables. To do this, create a view for Parquet files:

      
        CREATE VIEW parquet_table AS
        SELECT
                r['id']::BIGINT AS id,
                r['age']::SMALLINT AS age,
                r['name']::VARCHAR AS name
        FROM read_parquet('file.parquet') AS r;
      
     

In this example, data types used in Parquet files are specified explicitly. This is optional, but it allows you to cast columns to required DuckDB types, otherwise metadata from Parquet files is used. If data types are not specified explicitly, the view description will include a special duckdb.unresolved_type data type.

Once the view is created, standard analytical queries can be executed:

      
        SELECT id, name FROM parquet_table r WHERE age > 21;
      
     

The read_parquet function can take a list of Parquet file names. It also allows using a glob pattern syntax and specifying lists of glob patterns. The glob pattern syntax can be used for reading a directory with Parquet files or multiple directories if Hive partitioning is used.

For more information, see the official DuckDB documentation on Parquet files.

The pgpro_duckdb extension includes the following performance optimizations for Parquet files:

For more information, see the official DuckDB documentation on partial reading and filter pushdown.

The pgpro_duckdb extension allows limiting the number of threads and memory consumption for analytical sessions using the following configuration parameters:

The real memory consumption by OS can be higher, for example, allocating space for query results does not take configured limits into account. For more information, see the official DuckDB documentation on resource management.

OLAP data is processed by the pgpro_duckdb extension in streams, i.e., part by part, avoiding full materialization when possible. If the pgpro_duckdb extension lacks RAM for executing an analytical query, the associated data is placed in temporary files, which significantly increases the execution time.

Temporary files are located in the PGDATA/.tmp directory and can take up to 90% of disk space. Monitor the amount of temporary files created by the pgpro_duckdb extension every 24 hours and avoid peak values.

The CH-benCHmark methodology was used to evaluate performance. It includes measuring the average number of transactions per second (TPS) during a specific time period, as well as the number of analytical queries executed in parallel. In this case, the nature of analytical queries is not important, as they are executed in a random order, and the execution time of a single analytical query is much less than the duration of the test run.

OLTP workload was provided by the pgbench program, and analytical ClickBench queries were generated in the background by a script. In the CH-benCHmark file, analytical TCP-H queries were replaced with ClickBench queries. As the initial implementation, citus-benchmark was used.

Test details:

Recommendations for simultaneous OLAP and OLTP workload: