Вы можете использовать параметр конфигурации duckdb.postgres_role, чтобы указать имя роли, которая может вызывать функции pgpro_duckdb:

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

      (1 row)

По умолчанию для параметра конфигурации duckdb.postgres_role указано значение NULL, то есть вызывать функции pgpro_duckdb может только суперпользователь:

      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

Чтобы разрешить членам пользовательской роли вызывать функции pgpro_duckdb, укажите имя требуемой роли, после чего перезапустите сервер:

      postgres=# ALTER SYSTEM SET duckdb.postgres_role TO 'user1';
      ALTER SYSTEM
      -- после перезапуска сервера --
      postgres=# show duckdb.postgres_role ;
       duckdb.postgres_role
      ----------------------
       user1
      (1 row)

Вы можете использовать параметр конфигурации duckdb.disabled_filesystems, чтобы указать хранилища, которые будут отключены для расширения pgpro_duckdb.

Возможные значения:

По умолчанию локальное хранилище отключено:

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

Чтобы указать несколько хранилищ, разделите их имена запятыми без пробелов:

      postgres=# ALTER SYSTEM SET duckdb.disabled_filesystems TO 'LocalFileSystem,HTTPFileSystem';
      ALTER SYSTEM
      -- после перезапуска сервера --
      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
        -------------------------------------------------------
        # Не редактируйте этот файл вручную!                    +
        # Он будет перезаписан командой ALTER SYSTEM.+

        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-данные экспортируются с помощью команды COPY. Вы можете экспортировать таблицу Postgres Pro целиком:

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

Или только необходимые OLAP-данные:

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

Вы можете использовать следующие рекомендации по структуре каталогов и экспорту в Parquet-файлы:

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

Чтобы экспортировать OLAP-данные в S3-хранилище, укажите информацию о подключении. Например, если у вас развёрнут экземпляр S3-хранилища MinIO, выполните следующие действия от имени пользователя postgres:

Чтобы организовать OLAP-данные по ключам секционирования в каталоге таблиц Postgres Pro с помощью Hive-секционирования, укажите параметр PARTITION_BY при экспорте данных:

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

Так как запись большого количества Parquet-файлов является ресурсоёмкой OLAP-операцией, выбирайте ключи секционирования таким образом, чтобы размер каждой итоговой секции составлял как минимум 100 МБ. Размер Parquet-файлов невозможно настроить вместе с параметром PARTITION_BY. Количество создаваемых в каждом каталоге (секции) Parquet-файлов соответствует количеству потоков pgpro_duckdb.

За более подробной информацией о команде COPY и формате Parquet обратитесь к официальной документации DuckDB по операторам и Parquet-файлам.

Команда COPY выполняется в транзакции, потому снимок экспортированной таблицы Postgres Pro получается согласованным. Вы можете использовать явную транзакцию, чтобы экспортировать несколько согласованных таблиц, несколько раз подряд выполнив команду COPY.

Открытые транзакции откладывают очистку, что может негативно повлиять на производительность и увеличить размер таблиц. Чтобы этого избежать, выполните следующие действия:

Обратите внимание, что вы можете указать количество процессов postgres для чтения таблиц, а также количество потоков pgpro_duckdb для экспорта OLAP-данных в Parquet-файлы. Однако конвертация из столбцового в строчное представление в настоящее время выполняется в одном потоке.

Если при экспорте OLAP-данных в Parquet-файлы возникает ошибка, например из-за недостаточного дискового пространства, частично экспортированные файлы не удаляются. Для таких случаев необходимо настроить автоматическое удаление и продолжение работы.

После экспорта OLAP-данных в Parquet-файлы вы можете работать с ними как со стандартными таблицами Postgres Pro. Для этого создайте представление для Parquet-файлов:

              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;
      
     

В этом примере явно указаны используемые в Parquet-файлах типы данных. Это необязательно, но так вы можете назначить столбцам необходимые типы DuckDB. В противном случае будут использованы метаданные Parquet-файлов. Если типы данных не указаны явно, описание представления будет содержать уникальный тип данных duckdb.unresolved_type.

После создания представления можно выполнять стандартные аналитические запросы:

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

Функция read_parquet может принимать списки имён Parquet-файлов. Она также позволяет использовать синтаксис шаблона поиска и указывать списки шаблонов поиска. Синтаксис шаблона поиска можно использовать для чтения каталога Parquet-файлов или нескольких каталогов, если используется Hive-секционирование.

За более подробной информацией обратитесь к официальной документации DuckDB по Parquet-файлам.

Расширение pgpro_duckdb содержит следующие оптимизации производительности для Parquet-файлов:

За более подробной информацией обратитесь к официальной документации DuckDB по частичному чтению и переносу фильтров на файловый уровень.

Расширение pgpro_duckdb позволяет ограничивать количество потоков и потребление памяти для аналитических сеансов с помощью следующих параметров конфигурации:

Реальное потребление памяти операционной системой может быть выше. Например, настроенные ограничения не учитываются процессом выделения пространства для результатов запросов. За более подробной информацией обратитесь к официальной документации DuckDB по управлению ресурсами.

Расширение pgpro_duckdb обрабатывает OLAP-данные в потоках, то есть часть за частью, по возможности избегая полной материализации. Если расширению pgpro_duckdb не хватает оперативной памяти для выполнения аналитического запроса, связанные данные помещаются во временные файлы, что существенно повышает время выполнения.

Временные файлы размещаются в каталоге PGDATA/.tmp и могут занимать до 90% дискового пространства. Каждые 24 часа выполняйте мониторинг количества временных файлов, создаваемых расширением pgpro_duckdb, и избегайте пиковых значений.

Для оценки производительности был использован CH-benCHmark. Он включает в себя измерение среднего количества транзакций в секунду (TPS) за определённый период времени, а также количества параллельно выполненных аналитических запросов. В этом случае характер аналитических запросов не имеет значения, так как они выполняются в случайном порядке, и время выполнения одного аналитического запроса гораздо меньше продолжительности запуска теста.

OLTP-нагрузка была обеспечена программой pgbench, а аналитические запросы ClickBench были сгенерированы на фоне скриптом. В файле CH-benCHmark аналитические запросы TCP-H были заменены запросами ClickBench. В качестве изначальной реализации был использован citus-benchmark.

Описание теста:

Рекомендации для одновременной нагрузки OLAP и OLTP: