Postgres Pro AXE
1.0.0 Documentation

Postgres Professional

Copyright © 2016–2026 The Postgres Professional company

Legal Notice

This documentation is intended solely for the use with Postgres Pro AXE and for users of this product.

It is not allowed to use the documentation for third-party products or as part of documentation for other products.

Other terms of use of the documentation are given in the User Agreement of this product.

Postgres Pro is Copyright © 2016–2026 by Postgres Professional.

IN NO EVENT SHALL THE POSTGRES PROFESSIONAL COMPANY BE LIABLE TO ANY PARTY FOR ANY DAMAGES, INCLUDING LOSS PROFITS, ARISING OUT OF THE USE OF THIS PRODUCT AND ITS DOCUMENTATION, EVEN IF THE POSTGRES PROFESSIONAL COMPANY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE POSTGRES PROFESSIONAL COMPANY SPECIFICALLY DISCLAIMS ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE OR ARISING FROM A COURSE OF DEALING OR TRADE PRACTICES. THIS PRODUCT PROVIDED HEREUNDER IS ON AN “AS-IS” BASIS, AND THE POSTGRES PROFESSIONAL COMPANY HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Distribution of this documentation or its part requires an explicit written permission from the Postgres Professional company.

All versions of the Postgres Pro AXE DBMS documentation are based on:

The PostgreSQL documentation, which is distributed under the following license:

PostgreSQL Database Management System (also known as Postgres, formerly known as Postgres95)

Portions Copyright © 1996-2026, PostgreSQL Global Development Group.

Portions Copyright © 1994, The Regents of the University of California.

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN “AS-IS” BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

The DuckDB documentation, which is distributed under the following license:

Copyright 2024–2025 Stichting DuckDB Foundation

Any person obtaining a copy of this software and its documentation files (the “Software”) is hereby granted permission, free of charge, to work with the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is provided to do so, subject to the following conditions:

The above copyright notice and the permission notice below shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED ON AN “AS-IS” BASIS WITHOUT ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, AND NON-INFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES, OR OTHER LIABILITY, WHETHER IN AN ACTION OF A CONTRACT, TORT, OR OTHERWISE, ARISING FROM, OUT OF, OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Part I. Overview

Postgres Pro AXE is an analytical edition of the Postgres Pro DBMS that allows processing large amounts of OLTP (online transaction processing), OLAP (online analytical processing), and HTAP (hybrid transactional and analytical processing) workloads. It is designed to develop and simplify DWH (data warehouse) and LakeHouse architectures, as well as to create hybrid solutions for OLTP and OLAP processing without using complex ETL operations.

Chapter 1. Key Advantages

Postgres Pro AXE offers the following key advantages:

Speed and efficiency: Processes analytical queries up to 20 times faster than Greenplum and 30 times faster than PostgreSQL, according to ClickBench and TCP-H/TCP-DS benchmark tests on complex queries.

Quick start: Uses the PostgreSQL syntax, which does not require deploying additional systems and training employees to use new tools.

Saving resources:

does not require integration with additional storages and ETL servers (Extract, Transform, Load)

consumes up to 10 times less RAM and CPU than Greenplum, according to benchmark tests

Simplified the development stack: Allows building full-scale data warehouses (DWH) using only Postgres Pro components.

Storing data in open formats accessible to external tools, such as BI systems, dashboards, and other applications:

the OLAP data of analytical tables is stored as Parquet files

the metadata of analytical tables is stored using SQL tables according to the DuckLake specification

Chapter 2. Main Compontents

The main components of Postgres Pro AXE are:

Postgres Pro DBMS.

pgpro_axe: The columnar-vectorized analytics engine that provides fast queries to the columnar data sources, i.e., Parquet files and temporary tables. It is almost completely compatible with the PostgreSQL syntax and can work with the OLAP data from local, network, and S3 storages. pgpro_axe also allows joining the data from Postgres Pro heap tables and columnar sources within one analytical query.

This extension consists of the following components:

proprietary Postgres Pro AXE components

Postgres Pro components

DuckDB and its functionality

pgpro_metastore: The catalog of analytical tables that allows granularly configuring access privileges to Parquet files and managing the metadata of these files, as well as performing transactional updates to analytical tables.

The solution can be scaled vertically and horizontally at any time.

Chapter 3. Business Scenarios

Using Postgres Pro AXE as an analytical data store allows you to implement various business scenarios, including, but not limited to, the following scenarios:

Finances and budgeting:

comparing actual and target indicators of income and expenses (by month and quarter, between different departments)

analyzing the profitability of products, services, and departments

controlling the budget and identifying deviations

Smart pricing and marketing:

dynamic pricing (analyzing the demand, competitor prices, and unsold stock)

evaluating performance of sales channels, regions, and managers

analyzing consumer behavior (preferred goods, purchase time and location)

customer segmentation for target marketing campaigns

Service desk:

identifying key issues and their causes

monitoring performance (quality and speed)

analyzing customer satisfaction

Security:

analyzing logs (identifying anomalies and patterns)

investigating incidents and searching for dependent events

Inventory and stock:

analyzing inventory turnover

demand forecasting and purchasing optimization

For more information about OLAP data processing scenarios, refer to Chapter 4.

Part II. Deployment

The Postgres Pro AXE deployment process includes the following steps:

Identify the OLAP data processing scenario and the deployment scenario.

Ensure that your servers meet hardware and software requirements.

Allocate sufficient computational resources for executing analytical queries.

Identify user groups and usage scenarios.

Organize ETL operations.

Plan the scalability of compute resources.

Provide high availability.

Install Postgres Pro AXE.

Chapter 4. OLAP Data Processing Scenarios

Postgres Pro AXE includes, but is not limited to, the following OLAP data processing scenarios:

Reducing the OLAP workload on the OLTP server and generating operating reports faster.

Providing a history of analytical table updates and searching for historical data among infrastructure and security events.

Using excess capacity of standby servers for processing the OLAP data.

Shifting the OLAP workload from an overloaded OLAP cluster (e.g., Greenplum) to Postgres Pro AXE.

Building a full-fledged data warehouse.

To implement any of these scenarios, ensure that requirements specific to your organization are met. It is recommended to first meet the requirements related to the complexity and cost of deployment.

Chapter 5. Deployment Scenarios

Postgres Pro AXE includes, but is not limited to, the following main deployment scenarios:

A single server that processes OLAP and OLTP workloads simultaneously without high availability.

A cluster in which the primary server processes the OLTP workload and one of the standby servers processes the OLAP workload.

A cluster in which the primary server processes the OLTP workload and standby servers process the OLAP workload balanced by proxima.

You can store Parquet files with the OLAP data in a local, network, or S3 storage.

In all deployment scenarios the pgpro_axe and pgpro_metastore extensions are installed on each server processing the OLAP workload.

5.1. Impact on OLTP Workload from Analytical Queries

The risk of analytical queries impacting the OLTP workload depends on where they are executed. For example, consider a cluster in which the primary server processes the OLTP workload and synchronous and asynchronous standby servers process the OLAP workload:

If analytical queries are executed on the primary server, the risk is high because OLTP and OLAP workloads compete for the same computational resources within a single cluster. In this case, an incorrect analytical query may interfere with OLTP workload processing.

If analytical queries are executed on the synchronous standby server, the risk is decreased but not absent because the synchronous standby server may still fail due to an incorrect analytical query and, depending on the server configuration, may affect the primary server.

If analytical queries are executed on the asynchronous standby server, the risk is low. Even if the asynchronous standby server fails due to an incorrect analytical query, the primary server continues to process the OLTP workload.

Recommendations for processing OLAP workloads:

Use the primary server and synchronous standby servers to process stable and verified OLAP workloads, based on available computational resources. Avoid granting direct access rights to analyst groups, as well as executing ad hoc analytical queries, unverified analytical queries, and analytical queries with unpredictable resource usage.

Use asynchronous standby servers to process most of the OLAP workload and to test analytical queries before executing them on the primary server or synchronous standby servers. Cascading standby servers that do not impact the primary server can be used for interactive analysis and ad hoc analytical queries.

Use standalone Postgres Pro servers working with shared network or S3 storages to create, test, and execute ad hoc analytical queries with unpredictable execution times.

5.2. Verifying Available Network, Storage, and Computational Resources

When deploying Postgres Pro AXE, distribute the OLAP workload across servers so that the resulting topology meets performance requirements.

Recommendations for building the topology:

Identify the main OLAP processes and applications, and define time limits for performing ETL operations.

Create a diagram that illustrates data flows between storages.

Ensure that the network and storages provide sufficient throughput to perform operations, with a recommended 30% margin.

Select servers to use for processing most of the OLAP workload and ensure that they have sufficient computational resources to perform regular ETL operations within the required time limits, with a recommended 30% margin.

Ensure that there are no bottlenecks. If there are, consider adding computational resources or modifying the topology.

Chapter 6. Hardware and Software Requirements

Consider the following minimum hardware requirements when deploying Postgres Pro AXE:

For installing the Postgres Pro AXE DBMS:

1 GHz CPU

RAM: 1 GB

Disk space: 1 GB

Note

Postgres Pro AXE supports using Postgres Pro Enterprise 17.7.1 as the DBMS.

For processing the OLAP workload:

4 CPU cores

RAM: 16 GB

Disk space: 100 GB

Postgres Pro AXE cannot operate on virtual machines without vector CPU instructions, in particular SSE4.2 and AVX512F. Before installing the solution, ensure that the required instructions are installed on target VMs.

Example 6.1. 

To view available vector CPU instructions on operating systems similar to Debian, execute the following command:

grep -Eow "sse4_2|avx|avx2|avx512f" /proc/cpuinfo | sort -u

All four instructions must be in the command output.

Operating systems supported for the x86-64 architecture:

Debian 13

Ubuntu 24.04

Red OS 8

Astra Linux 1.8

ALT 11

When deploying Postgres Pro AXE with the NUMA (non-uniform memory access) architecture, it is recommended to allocate the following:

8 CPU cores per each NUMA node

at least 4 GB of RAM per one CPU core in a NUMA node

Chapter 7. Capacity Planning

When deploying Postgres Pro AXE, sufficient computational resources must be allocated for executing analytical queries. The amount of required resources is determined by the following storage characteristics:

Total amount of OLAP data in the storage.

Amount of data processed by a standard analytical query.

Required analytical query execution time.

Number of analytical queries executed per unit of time.

This section provides guidelines for estimating the computational resources required for different OLAP workloads.

After deploying Postgres Pro AXE, monitor the available computational resources and be prepared to allocate additional resources outside the scope of this estimation if required.

7.1. Total Amount of OLAP Data in the Storage

Sufficient space must be allocated in a local, network, or S3 storage for analytical tables. Parquet files are usually stored in a compressed format, such as Snappy or zstd, and constitute approximately 20% of the primary unstructured text data or Postgres Pro table data.

The storage throughput must be sufficient to sustain all read and write analytical queries at peak workloads.

7.2. Amount of Data Processed by a Standard Analytical Query

Certain analytical queries require a large amount of RAM to be executed effectively, particularly:

joining two large analytical tables

aggregations over a large number of unique keys

sorting large analytical tables

The amount of RAM required for a specific OLAP workload depends on the number and size of analytical queries in the Postgres Pro AXE instance. Monitor the amount of available RAM and allocate additional RAM if required.

For the initial deployment, allocate 4 CPU cores per one analyst and approximately 6 GB of RAM per 1 CPU core reserved for executing analytical queries.

Monitor the amount of temporary files created by pgpro_axe every 24 hours and avoid peak values. Temporary files are located in the directory specified by the duckdb.temporary_directory configuration parameter.

If pgpro_axe lacks RAM for executing an analytical query, the associated OLAP data is placed in temporary files, which significantly increases the execution time. To maximize the total Postgres Pro AXE cluster throughput and the number of analytical queries executed per unit of time, process most analytical queries in RAM.

However, temporary files can be used when processing resource-intensive analytical queries with long execution time (hours).

7.3. Required Analytical Query Execution Time

Certain analytical queries must complete within a specific time limit. In particular, this applies to interactive queries and queries with results that must be available by a certain date and time, for example, at the start of business hours. If sufficient RAM is allocated for executing analytical queries, and temporary files are not created for them, the execution time is determined by the number of CPU cores reserved for executing analytical queries.

Use internal benchmark tests to estimate the required number of CPU cores. During these tests, time-sensitive analytical queries are executed to the test data, the size and structure of which mimic the real data to be used.

7.4. Number of Analytical Queries Executed per Unit of Time

After allocating the computational resources required for executing analytical queries, calculate the total workload from analytical queries executed in parallel. This workload tends to peak once during business hours or when performing resource-intensive ETL operations.

Certain ETL operations can be scheduled to be performed at a specific time of day, whereas others — such as ad-hoc analytical queries — cannot.

Avoid OLAP workload peaks by evenly distributing the execution of analytical queries throughout the day and leaving a 30% margin for unexpected operations.

pgpro_axe processes analytical queries linearly as long as it has sufficient I/O resources, RAM, and CPUs. Once these resources are exhausted, the performance drops.

Recommendations for avoiding resource exhaustion:

eliminate bottlenecks in the Postgres Pro AXE cluster

reduce OLAP workload peaks

optimize the execution of analytical queries

redistribute ETL operations and ad-hoc analytical queries to a dedicated Postgres Pro AXE server, for example, to an asynchronous standby replica

7.5. Considerations and Limitations

Postgres Pro AXE has the following limitations that must be considered when executing OLAP operations:

pgpro_axe executes SQL queries within a single server and is limited by the server capacity. Typically, limit of data that the pgpro_axe can process in a single SQL query on an average server (256 CPU, 2 TB RAM) does not exceed 100 TB.

To process a larger data scope, split the initial data into smaller parts. Process each part in a separate analytical query and save the result in a temporary table. After processing all parts, unite the results in a final analytical query.

For the massively parallel mode with many servers involved, it is required either to balance SQL queries at the application level or to make use of Proxima.

The pgpro_axe SQL syntax is compatible with but does not fully match the PostgreSQL syntax. Certain queries operating within Postgres Pro must be modified to be executable in pgpro_axe.

Chapter 8. Identifying User Groups and Usage Scenarios

Identify user groups, such as administrators, analysts, applications, and end users, that will work with Postgres Pro AXE and metadata tables.

Identify usage scenarios for user groups.

Identify the required response time and configure access to the OLAP data for usage scenarios.

Postgres Pro AXE provides the following ways of configuring access to the OLAP data:

Each user group has a separate pgpro_metastore catalog and storage.

User groups share a single pgpro_metastore catalog. Each user group has a separate storage with most of the OLAP data. The remaining data is located in the shared storage, where it can be accessed by other user groups.

User groups share a single pgpro_metastore catalog and storage.

You can either configure access to the OLAP data in one of these ways or in a custom way using pga_storage, pga_table, and pga_folder metadata tables.

Chapter 9. Organizing ETL operations

Determine how the OLAP data is added to storages and organize ETL operations for deployment scenarios.

You can add the OLAP data to storages in one of the following ways:

From analytical tables or directly from Parquet files using ETL operations.

This is the easiest and most reliable way, since it allows for transactional updates of the OLAP data and metadata.

Using SQL scripts and custom ETL operations.

It is recommended to use this way only for the use cases that cannot be implemented using ETL operations.

Important

If this way is used, responsibility for correct metadata data registration and integrity is not guaranteed.

Postgres Pro operations, temporary tables, and custom data conversion scripts can be used for implementing complex initial OLAP data processing within ETL operations.

Chapter 10. Planning the Scalability of Compute Resources

Postgres Pro AXE requires fewer compute resources for implementing OLAP data processing scenarios as compared to other solutions. Processing and storage compute resources are isolated from each other, and you can provide additional resources when required.

The following Postgres Pro AXE server topologies are possible:

One server with 256 CPU cores, 2 TB of RAM, and an array of NVMe (Non-Volatile Memory Express) disks.

This is sufficient for analytical queries processing up to 100 TB of the OLAP data.

Multiple servers.

OLAP workload is distributed between servers using applications and ETL (Extract, Transform, Load) scripts.

A primary-standby cluster.

OLAP workload is distributed between standby servers using proxima.

It is recommended to start with a simple one-server topology and monitor the response time and workload. If this topology does not meet your organization's requirements, switch to another topology.

Chapter 11. Providing High Availability

You can create backups of the OLAP data in local storages using BiHA and pg_probackup. In network and S3 storages, use BiHA and pg_probackup for the Postgres Pro AXE DBMS data and backup tools of the storage provider for the OLAP data.

The implementation of high availability depends on the required OLAP data restore time (refer to the table below).

OLAP Data Restore Time | Implementation
Days | Full backups of the OLAP data
Hours, minutes | Incremental backups of the OLAP data
Seconds | Full backups of the OLAP data on standby servers or a fail-safe network/S3 storage

Note

Most OLAP data processing scenarios do not have strict requirements on the restore time as compared to OLTP data processing scenarios and real-time analytics.

Chapter 12. Installing Postgres Pro AXE

On servers prepared for your deployment scenario:

Install the Postgres Pro AXE DBMS from the distribution or any other supported DBMS.

For more information, refer to the Postgres Pro support team.

Install pgpro_axe and pgpro_metastore:

Install the pgpro_axe package, then the pgpro_metastore package received from Postgres Pro.

In the postgresql.conf configuration file, set shared_preload_libraries to pgpro_axe, pgpro_metastore.

Important

Specify the names of extensions in this exact order. Otherwise, they are not installed.

Restart the server.

Create pgpro_axe, then pgpro_metastore in the server database:

CREATE EXTENSION pgpro_axe;
CREATE EXTENSION pgpro_metastore;

Ensure that Postgres Pro AXE operates correctly by creating Parquet files in a storage:

Example 12.1. Creating Parquet files in a local or network storage

COPY (
SELECT test_id, test_name
FROM tbl_test
WHERE test_id between 22 and 30
) TO '/tmp/data_parquet/test_local.parquet' (FORMAT parquet, COMPRESSION zstd);

Example 12.2. Creating Parquet files in an S3 storage

user@db=# COPY (
SELECT test_id, test_name
FROM tbl_test
WHERE test_id between 1 and 10
) TO 's3://axedemobucket/test01.parquet' (FORMAT parquet, COMPRESSION zstd);
COPY 10
user@db=# COPY (
SELECT test_id, test_name
FROM tbl_test
WHERE test_id between 11 and 20
) TO 's3://axedemobucket/test02.parquet' (FORMAT parquet, COMPRESSION zstd);
COPY 10

If Parquet files were created successfully, you can select them:

Example 12.3. Selecting Parquet files from a local or network storage

SELECT r['column_name'] FROM read_parquet('/path/test.parquet') r;

Example 12.4. Selecting Parquet files from an S3 storage

SELECT r['column_name'] FROM read_parquet('s3://bucket_name/test.parquet') r

If you are able to create and select Parquet files, Postgres Pro AXE is installed successfully.

Chapter 13. Updating and Deleting Postgres Pro AXE

13.1. Updating Postgres Pro AXE

Update the Postgres Pro AXE DBMS.

For more information, refer to the Postgres Pro support team.

Delete pgpro_axe and pgpro_metastore.

For more information, refer to the official Postgres Pro documentation on deleting extensions.

Install pgpro_axe and pgpro_metastore from the latest distributive received from Postgres Pro.

For more information, refer to Chapter 12.

13.2. Deleting Postgres Pro AXE

Important

After deleting Postgres Pro AXE, you permanently lose all OLTP and OLAP data that was being processed by the solution.

To delete Postgres Pro AXE:

Delete pgpro_axe and pgpro_metastore.

For more information, refer to the official Postgres Pro documentation on deleting extensions.

Delete the Postgres Pro AXE DBMS.

For more information, refer to the Postgres Pro support team.

Part III. Administration

Chapter 14. Access Management

Since pgpro_metastore has its own objects, a system based on proxy tables is used to differentiate privileges and granularly manage access to these objects.

Proxy tables are empty tables without columns created in the pgpro_metastore schema. Each proxy table name is in the prefix_name format, where:

prefix: The prefix determining the object encapsulated by the proxy table.

Possible values:

s for storages

t for analytical tables

f for directories

name: The name of the object encapsulated by the proxy table.

For example, the name of a proxy table can be s_localstorage.

Proxy tables are created and deleted simultaneously with objects that they encapsulate.

For more information about configuring access to the Postgres Pro AXE DBMS, refer to the official Postgres Pro documentation.

14.1. Configuring the Administrator Role

In Postgres Pro AXE, the administrator role is called metastore_admin. It can interact with pgpro_axe, execute ETL operations, and grant other users privileges to manage objects.

To configure the administrator role:

Create the metastore_admin role:

ALTER SYSTEM SET duckdb.postgres_role TO 'metastore_admin';

Grant the metastore_admin role full access to the pgpro_metastore schema and reload the pgpro_metastore server.

14.2. Privilege Management Scenario

Consider a privilege management scenario where privileges are granted based on a hierarchy from the main administrator to local administrators of user groups and analysts (refer to the diagram below).

Where:

Superuser is the Postgres Pro superuser with full privileges.

metastore_admin is the main administrator with the metastore_admin role that owns all pgpro_metastore objects.

Administrators A and B are local administrators of user groups that can access pgpro_metastore objects associated with these groups.

Privileges of these administrators are granted by the main administrator, and they can grant privileges to analysts within each group.

Analysts A, B, C, and D are users that work with the OLAP data.

To configure this privilege management scenario:

Create users in the database under the superuser:

postgres=# SELECT current_user;
current_user
--------------
root
postgres=# CREATE USER metastore_admin;
postgres=# CREATE USER admin_a;
postgres=# CREATE USER admin_b;
postgres=# CREATE USER analyst_a;
postgres=# CREATE USER analyst_b;
postgres=# CREATE USER analyst_c;
postgres=# CREATE USER analyst_d;

Create analytical schemas for user groups:

postgres=# SELECT current_user;
current_user
--------------
root
postgres=# CREATE SCHEMA schema_a;
postgres=# CREATE SCHEMA schema_b;

Grant the admin_a user privileges to the schema_a analytical schema:

postgres=# SELECT current_user;
current_user
--------------
root
postgres=# GRANT ALL ON SCHEMA schema_a to metastore_admin WITH GRANT OPTION;
postgres=# GRANT ALL ON SCHEMA schema_a to admin_a WITH GRANT OPTION;

Grant the metastore_admin user privileges to the metastore analytical schema and make this user administrator:

postgres=# SELECT current_user;
current_user
--------------
root
postgres=# GRANT ALL ON SCHEMA metastore TO metastore_admin ;
postgres=# ALTER SYSTEM SET duckdb.postgres_role TO 'metastore_admin';

Restart the server.

The metastore_admin user can now configure the pgpro_metastore catalog:

postgres=> SELECT current_user;
  current_user
-----------------
metastore_admin
postgres=> SELECT metastore.define_catalog_connection('localhost','5433','postgres','','');
postgres=> SELECT metastore.init();

If the group A has a Postgres Pro table in its schema, and this table must be inserted in pgpro_metastore for analytical queries:

The metastore_admin user creates a storage and analytical table:

postgres=> SELECT current_user;
  current_user
-----------------
metastore_admin
postgres=> SELECT metastore.add_storage('mt_storage', 'file:///tmp/mt_storage/', 'file:///tmp/mt_storage/tmp_dir/');
postgres=> SELECT metastore.add_table('mt_table1', 'mt_storage', 'schema_a.pg_table');

The metastore_admin user finds the name of the proxy table encapsulating the created analytical table by executing the query in the database that contains the pgpro_metastore catalog, that is, the axe_catalog schema:

SELECT proxy_table_name FROM axe_catalog.pga_proxy_table p
JOIN axe_catalog.pga_table t ON p.proxy_table_id = t.proxy_table_id
WHERE t.table_name = 'mt_table1';
 proxy_table_name
-------------------
 t_mt_table1_00001
(1 row)

Further steps are performed on the server where pgpro_metastore is installed.

The metastore_admin user grants privileges to the admin_a user:

postgres=> SELECT current_user;
  current_user
-----------------
metastore_admin
postgres=> GRANT ALL ON metastore.t_mt_table1_00001 TO admin_a WITH GRANT OPTION;

The WITH GRANT OPTION parameter allows the admin_a user to grant privileges to other users if required.

The admin_a user copies the Postgres Pro table to the analytical table and creates a Postgres Pro view:

postgres=> SELECT current_user;
current_user
--------------
admin_a
postgres=> SELECT metastore.copy_table('mt_table1', 'select * from schema_a.pg_table');
postgres=> SELECT metastore.create_view('mt_table1', 'schema_a');

Note that any user calling copy_table must have enough permissions to execute the query provided as a parameter; in this case admin_a must have SELECT permission for schema_a.pg_table.

As object owner, the metastore_admin user grants the admin_a user the privilege to grant other users the privilege to read the object:

postgres=> SELECT current_user;
  current_user
-----------------
metastore_admin
postgres=> GRANT SELECT ON schema_a.mt_table1 TO admin_a WITH GRANT OPTION ;

The admin_a user grants analysts the privilege to read the Postgres Pro view:

postgres=> SELECT current_user;
current_user
--------------
admin_a
(1 row)

postgres=> GRANT USAGE ON SCHEMA schema_a to analyst_a;
postgres=> GRANT SELECT ON schema_a.mt_table1 TO analyst_a;

The analyst reads the analytical table:

postgres=> SELECT current_user;
current_user
--------------
analyst_a
(1 row)

postgres=> SELECT count(*) FROM schema_a.mt_table1 ;
count
-------
    50
(1 row)

14.3. ETL Operations and Privileges

This section explains what privileges are required for the existing ETL operations.

cleanup

Only the metastore_admin user.

define_catalog_connection()

Only the metastore_admin user.

This operation is SECURITY DEFINER and can only be executed with superuser privileges. You may have to update the Postgres Pro AXE DBMS system catalog, to which metastore_admin does not have access.

init()

Only the metastore_admin user.

add_storage/remove_storage

Only the metastore_admin user.

When one of these ETL operations is executed, a proxy table owned by the metastore_admin user and corresponding to the storage is created or deleted from the pgpro_metastore schema.

add_table/remove_table

Only the metastore_admin user.

When one of these ETL operations is executed, a proxy table owned by the metastore_admin user and corresponding to the table is created or deleted from the pgpro_metastore schema.

add_folder/remove_folder

Only the metastore_admin user.

When one of these ETL operations is executed, a proxy table owned by the metastore_admin user and corresponding to the shared directory is created or deleted from the pgpro_metastore schema.

add_files

Users that are granted privileges to:

insert in the pga_table metadata table

read the pga_folder metadata table if Parquet files are added from a shared directory specified in this table

copy_table

Users that are granted privileges to:

insert in the pga_table metadata table

execute the query specified by the second parameter

This ETL operation is SECURITY DEFINER.

create_view

Users that are granted privileges to:

read the pga_table metadata table

create views in the Postgres Pro schema

This ETL operation is SECURITY DEFINER.

14.4. Use Case Examples

Example 14.1. 

To create the mt_admin role, execute the following query:

CREATE USER mt_admin;
GRANT ALL ON SCHEMA metastore TO mt_admin;
ALTER SYSTEM SET duckdb.postgres_role TO 'mt_admin';

Then, reload the server.

Example 14.2. 

To grant the user1 user the privilege to insert the OLAP data in the mt_table1 analytical table from the mt_storage shared directory, execute the following query:

postgres=# GRANT INSERT ON metastore.t_mt_table1_00001 TO user1;
postgres=# GRANT SELECT ON metastore.f_mt_folder to user1;

14.5. Secrets

DuckDB secrets can be configured either using utility functions or with a foreign data wrapper for more advanced cases.

Secrets are stored in a combination of SERVER and USER MAPPING on the DuckDB foreign data wrapper. USER MAPPING hosts sensitive elements, such as token, session_token, and secret. Each time a DuckDB instance is created by pgpro_axe, and when a secret is modified, secrets are loaded as non-persistent into the DuckDB secrets manager.

Important

Do not grant USAGE permission on the duckdb foreign data wrapper to regular users.

The owner of a foreign server can create user mappings for this server for any user, so only grant this access privilege to administrators. Otherwise, a regular user can create secrets for certain scopes for unsuspecting users.

Postgres Pro AXE supports the following types of secrets:

simple secrets

secrets with credential_chain provider

secrets with secret_access_key

You can manage secrets using pgpro_axe functions.

14.5.1. Simple Secrets

The easiest way to configure credentials is using utility functions:

Example 14.3. 

  -- Basic S3 secret (most common)
  SELECT duckdb.create_simple_secret(
      type := 'S3',
      key_id := 'your_access_key_id',
      secret := 'your_secret_access_key',
      region := 'us-east-1'
  );

This function has more parameters:

Example 14.4. 

  SELECT duckdb.create_simple_secret(
      type          := 'S3',          -- Type: one of (S3, GCS, R2)
      key_id        := 'access_key_id',
      secret        := 'xxx',
      session_token := 'yyy',         -- (optional)
      region        := 'us-east-1',   -- (optional)
      url_style     := 'xxx',         -- (optional)
      provider      := 'xxx',         -- (optional)
      endpoint      := 'xxx',         -- (optional)
      scope         := 'xxx',         -- (optional)
      validation    := 'xxx',         -- (optional)
      use_ssl       := 'xxx'          -- (optional)
  )

14.5.2. Secrets with the credential_chain Provider

For more advanced use-cases, you can define secrets with SERVER and USER MAPPING on the DuckDB foreign data wrapper:

Example 14.5. 

    CREATE SERVER my_s3_secret
    TYPE 's3'
    FOREIGN DATA WRAPPER duckdb
    OPTIONS (PROVIDER 'credential_chain');

14.5.3. Secrets with secret_access_key

When your secret contains sensitive information, you need to create an additional USER MAPPING:

Example 14.6. 

    CREATE SERVER my_s3_secret TYPE 's3' FOREIGN DATA WRAPPER duckdb;

    CREATE USER MAPPING FOR CURRENT_USER SERVER my_s3_secret
    OPTIONS (KEY_ID 'my_secret_key', SECRET 'my_secret_value');

You can use any of the supported DuckDB secret types as long as the related extension is installed. For more information, refer to the official DuckDB documentation.

Chapter 15. Storages

Storages are physical locations of Parquet files and shared directories. Postgres Pro AXE supports the following types of storages:

Local storages: Arrays of NVMe (Non-Volatile Memory Express) disks on servers with Postgres Pro AXE installed.

Network storages: The Network File Systems (NFS).

S3 storages.

You can select the storage type using information provided in the table below.

| Local Storage | Network Storage | S3 Storage
Throughput | High Determined by the number of NVMe disks in the array | Medium Limited by the capacity of the network interface of the server, network load, and network storage speed | Medium Limited by the capacity of the network interface of the server, network load, and S3 storage speed
Data scalability | Medium Determined by the number and volume of NVMe disks | High | High
Data distribution between servers | Not supported | Within the network of the organization | Global
High availability provider | Postgres Pro AXE server administrator | NFS administrator | S3 provider
Cost per terabyte and per one storage request | Low | Medium | Depends on the S3 provider

The metadata of storages is stored in the pga_storage metadata table.

Any directory structure can be used for storing the OLAP data, for example:

In a local or network storage:

root_path/db_name/schema_name/table_name

In an S3 storage:

  s3://bucket/db_name/schema_name/table_name

pgpro_axe allows automatically exporting the OLAP data to multiple Parquet files and adding a unique number to each file name. It is easier to store the OLAP data as multiple Parquet files of the same size.

You can also use Hive partitioning to organize the OLAP data by partition keys in a directory hierarchy:

  table_name
  ├── year=2024
  │    ├── month=1
  │    │   ├── file1.parquet
  │    │   └── file2.parquet
  │    └── month=2
  │        └── file3.parquet
  └── year=2025
      ├── month=11
      │   ├── file4.parquet
      │   └── file5.parquet
      └── month=12
          └── file6.parquet

This type of hierarchy can be useful for large historical tables when analytical queries require the data associated with a specific subset of partition keys.

Performing filter pushdown on the path level is also supported. This allows skipping paths that do not contain the required OLAP data when reading.

Postgres Pro foreign servers are used to work with S3 storages. This allows storing connection parameters and user credentials securely within Postgres Pro without specifying them in functions. Currently, you can create only one S3 storage.

15.1. Creating a Storage

If you are planning to create an S3 storage, before performing this instruction, create a simple secret.

To create a storage, execute the following query:

  SELECT metastore.add_storage('storage_name', 'root_directory_URI', 'temporary_directory_URI');

Where:

storage_name: The unique storage name.

root_directory_URI: The URI of the root directory of the storage.

temporary_directory_URI: The URI of the storage directory for temporary files.

Local storages have simple URIs, for example, file:///home/j.doe/workspace/axe/.

S3 storages have URIs with more complicated structure and parsing, for example, s3://premdb/team01.csv?versionId=w_B5qT8s5MkiT09.IRHay0lW.PycsHTS. These URIs are generated using third-party libraries.

Example 15.1. Creating a Local Storage

  SELECT metastore.add_storage('local_storage_example', 'file:///tmp/local_storage_example/', 'file:///tmp/local_storage_example/tmp_dir/');

Example 15.2. Creating an S3 Storage

  SELECT metastore.add_storage('s3_storage_example', 's3://demo/s3_storage_example/','s3://demo/s3_storage_example/tmp/');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Ensures that there is enough free space for the storage, by default 10 GB is required.

Ensures that the specified directories exist.

Creates a new entry in the pga_storage metadata table.

For more information about connecting to an S3 storage, refer to Appendix D.

15.2. Deleting a Storage

Execute the following query:

  SELECT metastore.remove_storage('storage_name');

Where storage_name is the name of the storage that will be deleted.

Example 15.3. 

  SELECT metastore.remove_storage('storage_example');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Creates a new entry in the pga_snapshot metadata table and specifies for the storage and all its associated pgpro_metastore objects the end_snapshot value in the pga_storage metadata table and other metadata tables.

Chapter 16. Analytical Tables

Analytical tables are sets of columns and rows with the OLAP data featuring a full history of data and table type updates.

Rows of analytical tables are stored as Parquet files in a storage. The metadata of analytical tables is stored in the pga_table metadata table.

16.1. Creating an Analytical Table

16.1.1. Creating an Analytical Table from a Parquet File

Execute the following query:

  SELECT metastore.add_table('table_name', 'storage_name', 'Parquet_file_URI', '');

Where:

table_name: The unique analytical table name.

The fully qualified name of the analytical table consists of the analytical schema and table name. It is generated in analytical_schema_name.analytical_table_name format.

storage_name: The name of the storage where Parquet files are located.

Parquet_file_URI: The URI of the Parquet file from which the analytical table is created.

The URI must either start with file:// or s3://.

Important

Do not omit the last empty parameter — ''. It is reserved for future use, and without it the query does not work.

Example 16.1. 

  SELECT metastore.add_table('table_example', 'storage_example', 'file:///home/user/tmp/parquet_data/t.parquet', '');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Ensures that the source of metadata of the analytical table exists.

Uses the Apache Arrow library to export information about columns from the Parquet file.

Associates the data type of each column of the analytical table with a pgpro_metastore data type.

Creates new entries in pga_snapshot, pga_table, and pga_column metadata tables.

16.1.2. Creating an Analytical Table From a Heap Table

Execute the following query:

  SELECT metastore.add_table('table_name', 'storage_name', 'heap_table_name', '');

Where:

table_name: The unique analytical table name.

The fully qualified name of the analytical table consists of the analytical schema and table name. It is generated in analytical_schema_name.analytical_table_name format.

storage_name: The name of the storage where Parquet files are located.

heap_table_name: The fully qualified heap table name starting with the schema name.

Important

Do not omit the last empty parameter — ''. It is reserved for future use, and without it the query does not work.

Example 16.2. 

  SELECT metastore.add_table('table_example', 'storage_example', 'public.heap_table_example', '');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Ensures that the source of metadata of the analytical table exists.

Exports information about columns of the analytical table from the heap table by querying Postgres Pro service tables.

Associates the data type of each column with a pgpro_metastore data type.

Creates new entries in pga_snapshot, pga_table, and pga_column metadata tables.

16.2. Renaming an Analytical Table

Execute the following query:

  SELECT metastore.rename_table('schema_name.current_table_name' or 'current_table_name', 'new_table_name');

Where:

schema_name: The name of the analytical schema where the table is created.

Optional parameter. If you do not specify it, the current schema is used.

current_table_name: The current analytical table name.

new_table_name: The new unique analytical table name.

Example 16.3. 

  SELECT metastore.rename_table('testtable', 'testtable1');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Updates the table_name value for the analytical table in the pga_table metadata table.

Updates the view_sql value for the analytical table.

16.3. Creating a Postgres Pro View From an Analytical Table

Important

Postgres Pro views can be recreated only during periods of zero user activity to prevent potential data loss.

Execute the following query:

  SELECT metastore.create_view('table_name', 'Postgres_Pro_schema_name');

Where:

table_name: The name of the analytical table from which the Postgres Pro view is created.

Postgres_Pro_schema_name: The name of the Postgres Pro schema associated with the view.

Optional parameter. If you do not specify it, the schema is selected automatically.

Example 16.4. 

  SELECT metastore.create_view('table_example');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Receives metadata of the analytical table and the path to the shared directory.

Generates the CREATE VIEW query with the data source being the result of calling the read_parquet() function.

Executes the CREATE VIEW query.

Grants privileges to the Postgres Pro view.

Creates a new entry in the pga_snapshot metadata table.

16.4. Deleting an Analytical Table

Execute the following query:

  SELECT metastore.remove_table('table_name');

Where table_name is the name of the analytical table that will be deleted.

Example 16.5. 

  SELECT metastore.remove_table('table_example');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Creates a new entry in the pga_snapshot metadata table and specifies for the analytical table and all its associated pgpro_metastore objects the end_snapshot value in the pga_table metadata table and other metadata tables.

Chapter 17. Columns of Analytical Tables

For more information about analytical tables, refer to Chapter 16.

17.1. Creating a Column

Execute the following query:

  SELECT metastore.add_column('schema_name.table_name' or 'table_name', 'column_name', 'data_type');

Where:

schema_name: The name of the analytical schema where the table is created.

Optional parameter. If you do not specify it, the current schema is used.

table_name: The name of the analytical table where the column is created.

column_name: The unique column name.

data_type: The data type of the column.

For more information about supported data types, refer to Section G.3.

Example 17.1. 

  SELECT metastore.add_column('testtable', 'third_col', 'uint8');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Creates a new entry in the pga_column metadata table.

Updates the view_sql value for the analytical table.

17.2. Renaming a Column

Execute the following query:

  SELECT metastore.rename_column('schema_name.table_name' or 'table_name', 'current_column_name', 'new_column_name');

Where:

schema_name: The name of the analytical schema where the table is created.

Optional parameter. If you do not specify it, the current schema is used.

table_name: The name of the analytical table where the column is created.

current_column_name: The current column name.

new_column_name: The new unique column name.

Example 17.2. 

  SELECT metastore.rename_column('testtable', 'first_col', 'eleventh_col');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Updates the column_name value for the column in the pga_column metadata table.

Updates the view_sql value for the analytical table.

17.3. Changing the Data Type of a Column

Execute the following query:

  SELECT metastore.change_column_type('schema_name.table_name' or 'table_name', 'column_name', 'new_data_type');

Where:

schema_name: The name of the analytical schema where the table is created.

Optional parameter. If you do not specify it, the current schema is used.

table_name: The name of the analytical table where the column is created.

column_name: The name of the column whose data type is changed.

data_type: The new data type of the column.

For more information about supported data types, refer to Section G.3.

Example 17.3. 

  SELECT metastore.change_column_type('testtable', 'third_col', 'uint8');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Ensures that the new column data type does not match the previous one.

Ensures that the analytical table is empty or that the new data type is compatible with the previous one:

int8 can be changed to int16, int32, or int64

int16 can be changed to int32 or int64

int32 can be changed to int64

uint8 can be changed to uint16, uint32, or uint64

uint16 can be changed to uint32 or uint64

uint32 can be changed to uint64

float32 can be changed to float64

Updates the column_type value for the column in the pga_column metadata table.

Updates the view_sql value for the analytical table.

17.4. Deleting a Column

Execute the following query:

  SELECT metastore.delete_column('schema_name.table_name' or 'table_name', 'column_name');

Where:

schema_name: The name of the analytical schema where the table is created.

Optional parameter. If you do not specify it, the current schema is used.

table_name: The name of the analytical table where the column is created.

column_name: The name of the column that is deleted.

Example 17.4. 

  SELECT metastore.delete_column('my_schema.testtable', 'second_col');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Creates a new entry in the pga_snapshot metadata table and specifies for the column the end_snapshot value in the pga_column metadata table.

Updates the view_sql value for the analytical table.

Chapter 18. Analytical Schemas

Analytical schemas are separate namespaces for the metadata of analytical tables. These schemas are similar to Postgres Pro schemas.

The metadata of analytical schemas is stored in the pga_schema metadata table.

18.1. Creating an Analytical Schema

Execute the following query:

  SELECT metastore.add_schema('schema_name');

Where schema_name is the unique analytical schema name.

Example 18.1. 

  SELECT metastore.add_schema('schema_example');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Creates a new entry in the pga_schema metadata table.

18.2. Deleting an Analytical Schema

Important

Analytical schemas associated with analytical tables or Postgres Pro views cannot be deleted.

Execute the following query:

  SELECT metastore.remove_schema('schema_name');

Where schema_name is the name of the analytical schema that is deleted.

Example 18.2. 

  SELECT metastore.remove_schema('schema_example');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Creates a new entry in the pga_snapshot metadata table and specifies for the analytical schema and all its associated pgpro_metastore objects the end_snapshot value in the pga_schema metadata table and other metadata tables.

Chapter 19. Shared Directories

Shared directories are storage directories where Parquet files are located when adding and exporting the OLAP data from analytical tables.

The metadata of shared directories is stored in the pga_folder metadata table.

19.1. Creating a Shared Directory

Execute the following query:

  SELECT metastore.add_folder('shared_directory_name', 'storage_name', 'path_to_the_shared_directory');

Where:

shared_directory_name: The unique shared directory name.

storage_name: The name of the storage where the shared directory is created.

path_to_the_shared_directory: The path to the shared directory relative to the root directory of the storage.

Example 19.1. 

  SELECT metastore.add_folder('shared_directory_example', 'storage_example', '/relative_path');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Ensures that the specified path leads to an existing shared directory.

Creates a new entry in the pga_folder metadata table.

19.2. Deleting a Shared Directory

Execute the following query:

  SELECT metastore.remove_folder('shared_directory_name');

Where shared_directory_name is the name of the shared directory that is deleted.

Example 19.2. 

  SELECT metastore.remove_folder('shared_directory_example');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Creates a new entry in the pga_snapshot metadata table and specifies for the shared directory the end_snapshot value in the pga_folder metadata table.

Chapter 20. OLAP Data

20.1. Adding the OLAP Data to an Analytical Table From Parquet Files

Execute the following query:

  SELECT metastore.add_files('table_name', 'path_to_Parquet_files');

Where:

table_name: The name of the analytical table to which the OLAP data is added.

path_to_Parquet_files: The path to the Parquet file or to the shared directory with Parquet files from which the OLAP data is added.

Enter a path relative to the shared directory from the pga_folder metadata table, starting with the name this directory.

Metadata of Parquet files and the analytical table must be compatible.

Example 20.1. 

  SELECT add_files('table_example', 'folder/file.parquet');

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Ensures that metadata of Parquet files and the analytical table are compatible.

Copies Parquet files to the shared directory with Parquet files of the analytical table.

Creates new entries in pga_snapshot and pga_data_file metadata tables.

Updates analytical table and column statistics.

20.2. Copying the OLAP Data to an Analytical Table From Other Tables

Execute the following query:

  SELECT metastore.copy_table('table_name', SQL_query);

Where:

table_name: The name of the analytical table to which the OLAP data is copied.

SQL_query: The SQL query that determines which OLAP data is copied, for example, SELECT * FROM my_pgtable.

The SQL query must be a single valid SELECT statement. The UNION operation is not supported.

Example 20.2. 

  SELECT copy_table('table_example', $$select * from pg_table_example$$);

Once the query is executed, pgpro_metastore performs the following actions:

Verifies user privileges.

Creates a new entry in the pga_snapshot metadata table.

Creates a directory for results of the SQL query that are saved as Parquet files.

Executes the SQL query and writes its results to Parquet files.

Adds the OLAP data to the analytical table.

20.3. Deleting Parquet Files

In Postgres Pro AXE, the deletion of Parquet files is a two-stage process. First, you must mark snapshots referenced by entries from the pga_data_file metadata table as expired to exclude associated Parquet files from analytical queries. Then, you can delete Parquet files.

Before performing this instruction, ensure that you are assigned the metastore_admin role. For more information, refer to Chapter 14.

To delete Parquet files:

Mark snapshots as expired in one of the following ways:

To mark snapshots created before a certain date and time as expired, execute the following query:

  SELECT metastore.expire_snapshot(threshold_date_and_time);

Where threshold_date_and_time is the threshold value for the snapshot creation date and time.

The snapshot creation date and time are contained in the snapshot_time column of the pga_snapshot metadata table.

Supported date and time formats:

Date in the yyyy-mm-dd format.

Example 20.3. 

2025-11-28

Date in the yyyy-mm-dd format and time in the hh:mm:ss format.

Example 20.4. 

2025-11-28 12:22:46

Date in the yyyy-mm-dd format and time in the hh:mm:ss format with the time zone.

Example 20.5. 

2025-11-28 12:22:46+03

Date in the yyyy-mm-dd format and time in the hh:mm:ss.ssssss format with time zone, where the fractional part of seconds must have no more than 6 digits.

Example 20.6. 

2025-11-28 12:22:46.123456+03

Example 20.7. 

  SELECT metastore.expire_snapshot('2025-11-13 12:22:46.123456+03');

To mark snapshots with certain IDs as expired, execute the following query:

  SELECT metastore.expire_snapshot('[list_of_snapshot_IDs]');

Where list_of_snapshot_IDs is a comma-separated list of snapshot IDs.

Snapshot IDs are contained in the snapshot_id column of the pga_snapshot metadata table.

Example 20.8. 

  SELECT metastore.expire_snapshot('[1,2,3,4]');

Once the query is executed, pgpro_metastore performs the following actions:

Marks snapshots as expired if their entries from the pga_snapshot metadata table have snapshot_time values that are less than threshold_date_and_time or have snapshot_id values that are equal to one of the specified snapshot IDs.

Finds entries in the pga_data_file metadata table whose begin_snapshot values reference expired snapshots and creates associated entries in the pga_files_scheduled_for_deletion metadata table if these entities were not already created by previous expire_snapshot calls.

Note

Other metadata tables whose begin_snapshot values reference expired snapshots are not impacted.

Recreate Postgres Pro views from analytical tables associated with Parquet files so that these files are excluded from analytical queries.

Important

Postgres Pro views can be recreated only during periods of zero user activity to prevent potential data loss.

Delete Parquet files:

SELECT metastore.delete_expired_files();

Once the query is executed, pgpro_metastore performs the following actions:

Deletes Parquet files from the storage and associated entries from pga_files_scheduled_for_deletion, pga_data_file, and pga_file_column_statistics metadata tables.

Recalculates statistics for entries associated with deleted Parquet files in pga_table_stats and pga_table_column_stats metadata tables.

Recalculates column_order values for entries associated with deleted Parquet files in the pga_column metadata table.

Part IV. pgpro_axe

pgpro_axe is a Postgres Pro columnar-vectorized analytics engine that, among the rest, supports storing the OLAP data as Parquet files in network, local, and S3 storages. This extension consists of the following components:

Proprietary Postgres Pro AXE components.

Postgres Pro core components.

DuckDB and its core functionality.

Chapter 21. Functions

By default, pgpro_axe functions are installed into the public schema. Currently, ALTER EXTENSION cannot move pgpro_axe to a different schema.

21.1. Functions for Reading OLAP Data from a Storage

read_parquet(path TEXT or TEXT[], optional_parameters) returns SETOF duckdb.row

Reads a Parquet file from a storage.

Returns DuckDB rows. You can expand these rows using * or select specific columns using the r['column_name'] syntax. To select specific columns, give the function call an easy alias, such as r:

    SELECT * FROM read_parquet('file.parquet');
    SELECT r['id'], r['name'] FROM read_parquet('file.parquet') r WHERE r['age'] > 21;
    SELECT COUNT(*) FROM read_parquet('file.parquet');

Required parameters:

Name | Type | Description
path | text or text[] | The path to the storage with Parquet files to read. It can be a glob or array of files.

Optional parameters mirror the DuckDB read_parquet function. To specify optional parameters, use parameter := 'value'.

read_csv(path TEXT or TEXT[], optional_parameters) returns SETOF duckdb.row

Reads a CSV file from a storage.

Returns DuckDB rows. You can expand these rows using * or select specific columns using the r['column_name'] syntax. To select specific columns, give the function call an easy alias, such as r:

  SELECT * FROM read_csv('file.csv');
  SELECT r['id'], r['name'] FROM read_csv('file.csv') r WHERE r['age'] > 21;
  SELECT COUNT(*) FROM read_csv('file.csv');

Required parameters:

Name | Type | Description
path | text or text[] | The path to the storage with CSV files to read. It can be a glob or array of files.

Optional parameters mirror the DuckDB read_csv function. To specify optional parameters, use parameter := 'value'.

Note

columns is not currently supported

nullstr must be an array (TEXT[])

read_json(path TEXT or TEXT[], optional_parameters) returns SETOF duckdb.row

Reads a JSON file from a storage.

Returns DuckDB rows. You can expand these rows using * or select specific columns using the r['column_name'] syntax. To select specific columns, give the function call an easy alias, such as r:

Example 21.1. 

    SELECT * FROM read_parquet('file.parquet');
    SELECT r['id'], r['name'] FROM read_parquet('file.parquet') r WHERE r['age'] > 21;
    SELECT COUNT(*) FROM read_parquet('file.parquet');

Required parameters:

Name | Type | Description
path | text or text[] | The path to the storage with JSON files to read. It can be a glob or array of files.

Optional parameters mirror the DuckDB read_json function. To specify optional parameters, use parameter := 'value'.

Note

columns is not currently supported.

iceberg_scan(path TEXT, optional_parameters) returns SETOF duckdb.row

Reads an iceberg table from a storage.

Returns DuckDB rows. You can expand these rows using * or select specific columns using the r['column_name'] syntax. To select specific columns, give the function call an easy alias, such as r:

Example 21.2. 

  SELECT * FROM iceberg_scan('data/iceberg/table');
  SELECT r['id'], r['name'] FROM iceberg_scan('data/iceberg/table') r WHERE r['age'] > 21;
  SELECT COUNT(*) FROM iceberg_scan('data/iceberg/table');

To use iceberg_scan, enable the iceberg extension:

  SELECT duckdb.install_extension('iceberg');

Required parameters:

Name | Type | Description
path | text | The path to the storage with the iceberg table to read.

Optional parameters mirror the DuckDB iceberg_scan function. To specify optional parameters, use parameter := 'value'.

Optional parameters:

Name | Type | Default value | Description
allowed_moved_paths | boolean | false | Ensures that path resolution is performed, which allows scanning iceberg tables that are moved.
mode | text | '' | —
metadata_compression_codec | text | 'none' | —
skip_schema_inference | boolean | false | —
version | text | 'version-hint.text' | —
version_name_format | text | 'v%s%s.metadata.json,%s%s.metadata.json' | —

iceberg_metadata(path TEXT, optional_parameters) returns SETOF iceberg_metadata_record

Returns metadata of an iceberg table. Metadata is returned as a set of icerberg_metadata_record, which is defined as follows:

  CREATE TYPE duckdb.iceberg_metadata_record AS (
    manifest_path TEXT,
    manifest_sequence_number NUMERIC,
    manifest_content TEXT,
    status TEXT,
    content TEXT,
    file_path TEXT
  );

To use iceberg_metadata, enable the iceberg extension:

  SELECT duckdb.install_extension('iceberg');

Required parameters:

Name | Type | Description
path | text | The path to the storage with the iceberg table to read.

Optional parameters mirror the DuckDB iceberg_metadata function. To specify optional parameters, use parameter := 'value'.

Optional parameters:

Name | Type | Default value | Description
allowed_moved_paths | boolean | false | Ensures that path resolution is performed, which allows scanning iceberg tables that are moved.
metadata_compression_codec | text | 'none' | —
skip_schema_inference | boolean | false | —
version | text | 'version-hint.text' | —
version_name_format | text | 'v%s%s.metadata.json,%s%s.metadata.json' |

iceberg_snapshots (path TEXT, optional_parameters) returns SETOF iceberg_snapshot_record

Reads snapshot information from an iceberg table.

Returns snapshot metadata for an iceberg table, which can be useful for "time travel" queries and understanding table history.

Example 21.3. 

  SELECT * FROM iceberg_snapshots('data/iceberg/table');

To use iceberg_snapshots, enable the iceberg extension:

  SELECT duckdb.install_extension('iceberg');

Required parameters:

Name | Type | Description
path | text | The path to the storage with the iceberg table to read.

Optional parameters mirror the DuckDB iceberg_snapshots function. To specify optional parameters, use parameter := 'value'.

Optional parameters:

Name | Type | Default value
metadata_compression_codec | text | 'none'
skip_schema_inference | boolean | false
version | text | 'version-hint.text'
version_name_format | text | 'v%s%s.metadata.json,%s%s.metadata.json'

delta_scan(path TEXT) returns SETOF duckdb.row

Reads a Delta dataset from a storage.

Returns DuckDB rows. You can expand these rows using * or select specific columns using the r['column_name'] syntax. To select specific columns, give the function call an easy alias, such as r:

Example 21.4. 

  SELECT * FROM delta_scan('/path/to/delta/dataset');
  SELECT r['id'], r['name'] FROM delta_scan('/path/to/delta/dataset') r WHERE r['age'] > 21;
  SELECT COUNT(*) FROM delta_scan('/path/to/delta/dataset');

To use delta_scan, enable the delta extension:

  SELECT duckdb.install_extension('delta');

Required parameters:

Name | Type | Description
path | text | The path to the storage with the Delta dataset to read.

21.2. JSON Functions

pgpro_axe supports all DuckDB JSON functions and aggregates. Postgres Pro JSON/JSONB functions are not supported.

21.3. Union Type Functions

union_extract(union_col, tag) returns duckdb.unresolved_type

Extracts a value from a union type by specifying the tag name of the member to access.

Example 21.5. 

-- Extract the string value if the union contains a string
SELECT union_extract(my_union_column, 'string') FROM my_table;

-- Extract integer value from union
SELECT union_extract(data_field, 'integer') AS extracted_int FROM mixed_data;

Required parameters:

Name | Type | Description
union_col | duckdb.union or duckdb.unresolved_type | The union column from which to extract a value
tag | text | The tag name of the union member to extract

union_tag(union_col) returns duckdb.unresolved_type

Returns the tag name of the currently active member in a union type.

Example 21.6. 

-- Get the active tag for each row
SELECT union_tag(my_union_column) AS active_type FROM my_table;

-- Filter rows based on union tag
SELECT * FROM my_table WHERE union_tag(data_field) = 'string';

Required parameters:

Name | Type | Description
union_col | duckdb.union or duckdb.unresolved_type | The union column from which to get the tag

21.4. MAP Functions

cardinality(map_col duckdb.map) returns numeric

Returns the size of a map (the number of key-value pairs).

Example 21.7. 

-- Get the number of entries in a map
SELECT cardinality(r['map_col']) as size
FROM duckdb.query($$ SELECT MAP(['a', 'b', 'c'], [1, 2, 3]) as map_col $$) r;
-- Returns: 3

-- Empty map
SELECT cardinality(r['map_col']) as size
FROM duckdb.query($$ SELECT MAP([], []) as map_col $$) r;
-- Returns: 0

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map, the size of which to return

element_at(map_col duckdb.map, key duckdb.unresolved_type) returns duckdb.unresolved_type

Returns the value for a given key as an array.

Example 21.8. 

-- Get value for a specific key
SELECT element_at(r['map_col'], 'a') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {1}

-- Non-existent key
SELECT element_at(r['map_col'], 'c') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {}

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map from which to extract a value
key | duckdb.unresolved_type | The key for which to extract a value

map_concat(map_col duckdb.map, map_col2 duckdb.map) returns duckdb.map

Merges two maps. On key collision, the value is taken from the last map.

Example 21.9. 

-- Merge two maps
SELECT map_concat(r1['map1'], r2['map2']) as merged
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map1 $$) r1,
     duckdb.query($$ SELECT MAP(['b', 'c'], [3, 4]) as map2 $$) r2;
-- Returns: {a=1, b=3, c=4}

-- Note: 'b' value from map2 (3) overwrites map1's value (2)

Required parameters:

Name | Type | Description
map_col | duckdb.map | The first map to merge
map_col2 | duckdb.map | The second map to merge

map_contains(map_col duckdb.map, key duckdb.unresolved_type) returns boolean

Checks whether a map contains a given key.

Example 21.10. 

-- Check if key exists
SELECT map_contains(r['map_col'], 'a') as has_key
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: t (true)

-- Check for non-existent key
SELECT map_contains(r['map_col'], 'c') as has_key
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: f (false)

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map where to check a key
key | duckdb.unresolved_type | The key to check

map_contains_entry(map_col duckdb.map, key duckdb.unresolved_type, value duckdb.unresolved_type) boolean

Checks whether a map contains a given key-value pair.

Example 21.11. 

-- Check if key-value pair exists
SELECT map_contains_entry(r['map_col'], 'a', 1) as has_entry
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: t (true)

-- Check with wrong value for existing key
SELECT map_contains_entry(r['map_col'], 'a', 2) as has_entry
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: f (false)

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map where to check a key-value pair
key | duckdb.unresolved_type | The key to check
value | duckdb.unresolved_type | The value to check

map_contains_value(map_col duckdb.map, value duckdb.unresolved_type) returns boolean

Checks whether a map contains the specified value.

Example 21.12. 

-- Check if value exists
SELECT map_contains_value(r['map_col'], 1) as has_value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: t (true)

-- Check for non-existent value
SELECT map_contains_value(r['map_col'], 3) as has_value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: f (false)

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map where to check a value
value | duckdb.unresolved_type | The value to check

map_entries(map_col duckdb.map) returns duckdb.struct[]

Returns an array of structs (key, value) for each key-value pair in the map.

Example 21.13. 

-- Get all key-value pairs as structs
SELECT map_entries(r['map_col']) as entries
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {"(a,1)","(b,2)"}

-- Access individual struct fields
SELECT unnest(map_entries(r['map_col'])) as entry
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map from which to extract arrays of structs

map_extract(map_col duckdb.map, key duckdb.unresolved_type) returns duckdb.unresolved_type

Extracts a value from a map using the specified key. If the key does not exist, returns an empty array.

Example 21.14. 

-- Extract value from a map
SELECT map_extract(r['map_col'], 'a') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {1}

-- Extract non-existent key
SELECT map_extract(r['map_col'], 'c') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {}

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map from which to extract a value
key | duckdb.unresolved_type | The key to use for extracting a value

map_extract_value(map_col duckdb.map, key duckdb.unresolved_type) returns duckdb.unresolved_type

Returns a value for the specified key or NULL if the key is not contained in the map.

Example 21.15. 

-- Extract single value (not as array)
SELECT map_extract_value(r['map_col'], 'a') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: 1

-- Non-existent key returns NULL
SELECT map_extract_value(r['map_col'], 'c') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: NULL

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map from which to extract a value
key | duckdb.unresolved_type | The key for which to extract a value

map_from_entries(entries duckdb.struct[]) returns duckdb.map

Creates a map from an array of structs (k, v).

Example 21.16. 

-- Create map from array of structs
SELECT map_from_entries(r['entries']) as new_map
FROM duckdb.query($$
    SELECT [{'k': 'a', 'v': 1}, {'k': 'b', 'v': 2}] as entries
$$) r;
-- Returns: {a=1, b=2}

-- This is the inverse operation of map_entries
SELECT map_from_entries(map_entries(r['map_col'])) as reconstructed
FROM duckdb.query($$ SELECT MAP(['x', 'y'], [10, 20]) as map_col $$) r;
-- Returns: {x=10, y=20}

Required parameters:

Name | Type | Description
entries | duckdb.struct[] | Array of structs with 'k' (key) and 'v' (value) fields

map_keys(map_col duckdb.map) returns duckdb.unresolved_type

Returns all keys from a map as an array.

Example 21.17. 

-- Get all keys from a map
SELECT map_keys(r['map_col']) as keys
FROM duckdb.query($$ SELECT MAP(['a', 'b', 'c'], [1, 2, 3]) as map_col $$) r;
-- Returns: {a,b,c}

-- Empty map
SELECT map_keys(r['map_col']) as keys
FROM duckdb.query($$ SELECT MAP([], []) as map_col $$) r;
-- Returns: {}

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map from which to extract keys

map_values(map_col duckdb.map) returns duckdb.unresolved_type

Returns all values from a map as an array.

Example 21.18. 

-- Get all values from a map
SELECT map_values(r['map_col']) as values
FROM duckdb.query($$ SELECT MAP(['a', 'b', 'c'], [1, 2, 3]) as map_col $$) r;
-- Returns: {1,2,3}

-- Empty map
SELECT map_values(r['map_col']) as values
FROM duckdb.query($$ SELECT MAP([], []) as map_col $$) r;
-- Returns: {}

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map from which to extract values

21.5. Aggregates Functions

approx_count_distinct(expression) returns BIGINT

Approximates the count of distinct elements using the HyperLogLog algorithm. This is much faster than COUNT(DISTINCT ...) for large datasets, with a small error rate.

Example 21.19. 

-- Approximate distinct count of customer IDs
SELECT approx_count_distinct(customer_id) FROM orders;

-- Compare with exact count
SELECT
    approx_count_distinct(customer_id) AS approx_distinct,
    COUNT(DISTINCT customer_id) AS exact_distinct
FROM orders;

Required parameters:

Name | Type | Description
expression | any | The expression for which to count distinct values

21.6. Sampling Functions

TABLESAMPLE (sampling_method(percentage | rows))

Samples a subset of rows from an analytical table or query result. This is useful for analyzing large datasets by working with representative samples, improving query performance for exploratory data analysis.

Example 21.20. 

-- Sample 10% of rows from a table
SELECT * FROM large_table TABLESAMPLE SYSTEM(10);

-- Sample approximately 1000 rows
SELECT * FROM events TABLESAMPLE SYSTEM(1000 ROWS);

-- Sample from data lake files
SELECT * FROM read_parquet('s3://datalake/**/*.parquet') TABLESAMPLE SYSTEM(5);

-- Use sampling for quick data profiling
SELECT
    region,
    COUNT(*) as sample_count,
    AVG(revenue) as avg_revenue
FROM sales_data TABLESAMPLE SYSTEM(2)
GROUP BY region;

-- Sample from joins for performance
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
JOIN orders o TABLESAMPLE SYSTEM(10) ON c.id = o.customer_id
GROUP BY c.name;

Sampling methods:

SYSTEM: Random sampling at the storage level (faster, approximate percentage).

BERNOULLI: Row-by-row random sampling (slower, exact percentage).

Example 21.21. 

-- System sampling (recommended for large tables)
SELECT * FROM huge_table TABLESAMPLE SYSTEM(1);

-- Bernoulli sampling (exact percentage)
SELECT * FROM medium_table TABLESAMPLE BERNOULLI(5);

Use cases:

Data exploration: Quick analysis of large datasets.

Performance testing: Test queries on sample data.

Data profiling: Understand data distribution patterns.

ETL operations development: Develop pipelines on sample data.

Quality checks: Validate data quality on samples.

Required parameters:

Name | Type | Description
sampling_method | keyword | SYSTEM or BERNOULLI
percentage | numeric | Percentage of rows to sample (0-100)

Optional parameters:

Name | Type | Description
rows | integer | Approximate number of rows to sample (use with ROWS keyword)

21.7. Time Functions

time_bucket(bucket_width INTERVAL, timestamp_col TIMESTAMP, origin TIMESTAMP) returns TIMESTAMP

Buckets timestamps into time intervals for time-series analysis. This function is compatible with the TimescaleDB time_bucket function, allowing for easier migration and interoperability.

Example 21.22. 

-- Group events by hour
SELECT time_bucket(INTERVAL '1 hour', created_at) as hour_bucket, COUNT(*)
FROM events
GROUP BY hour_bucket
ORDER BY hour_bucket;

-- Group by 15-minute intervals
SELECT time_bucket(INTERVAL '15 minutes', timestamp_col), AVG(value)
FROM sensor_data
WHERE timestamp_col >= '2024-01-01'
GROUP BY 1
ORDER BY 1;

Required parameters:

Name | Type | Description
bucket_width | interval | The time interval for bucketing (e.g., '1 hour', '15 minutes')
timestamp_col | timestamp | The timestamp column to bucket

Optional parameters:

Name | Type | Description
origin | timestamp | The origin point for bucketing. Buckets are aligned to this timestamp.

strftime (timestamp_expr, format_string) returns TEXT

Formats timestamps as strings using standard format codes. This function provides flexible timestamp formatting for display and export purposes.

Example 21.23. 

-- Format current timestamp
SELECT strftime(NOW(), '%Y-%m-%d %H:%M:%S') AS formatted_time;

-- Format timestamps in different formats
SELECT
    order_id,
    strftime(created_at, '%Y-%m-%d') AS order_date,
    strftime(created_at, '%H:%M') AS order_time,
    strftime(created_at, '%A, %B %d, %Y') AS readable_date
FROM orders;

-- Use for partitioning file exports
COPY (SELECT * FROM events WHERE event_date = '2024-01-01')
TO 's3://bucket/events/' || strftime('2024-01-01'::timestamp, '%Y/%m/%d') || '/events.parquet';

Common format codes:

%Y: 4-digit year (2024).

%m: Month as number (01-12).

%d: Day of month (01-31).

%H: Hour (00-23).

%M: Minute (00-59).

%S: Second (00-59).

%A: Full weekday name (Monday).

%B: Full month name (January).

Optional parameters:

Name | Type | Description
timestamp_expr | timestamp | The timestamp value to format
format_string | text | The format string with format codes

strptime(string_expr, format_string) returns TIMESTAMP

Parses strings into timestamps using format codes.

Example 21.24. 

-- Parse date strings
SELECT strptime('2024-01-15 14:30:00', '%Y-%m-%d %H:%M:%S') AS parsed_timestamp;

-- Parse different formats
SELECT
    strptime('Jan 15, 2024', '%b %d, %Y') AS date1,
    strptime('15/01/2024', '%d/%m/%Y') AS date2,
    strptime('2024-01-15T14:30:00Z', '%Y-%m-%dT%H:%M:%SZ') AS iso_date;

-- Parse log timestamps
SELECT
    log_id,
    strptime(timestamp_string, '%Y-%m-%d %H:%M:%S') AS parsed_time,
    message
FROM raw_logs;

Required parameters:

Name | Type | Description
string_expr | text | The string to parse as a timestamp
format_string | text | The format string describing the input format

epoch(timestamp_expr) returns BIGINT

Converts timestamps to Unix epoch seconds (seconds since 1970-01-01 00:00:00 UTC).

Example 21.25. 

-- Get current epoch time
SELECT epoch(NOW()) AS current_epoch;

-- Convert timestamps for API usage
SELECT
    event_id,
    epoch(event_timestamp) AS epoch_seconds
FROM events;

-- Filter using epoch time
SELECT * FROM events
WHERE epoch(created_at) > 1640995200; -- After 2022-01-01

Required parameters:

Name | Type | Description
map_col | duckdb.map | The map from which to extract parameters
key | duckdb.unresolved_type | The key to find in the map

epoch_ms(timestamp_expr) returns BIGINT

Converts timestamps to Unix epoch milliseconds.

Example 21.26. 

-- High-precision timestamp for JavaScript
SELECT epoch_ms(NOW()) AS timestamp_ms;

-- For time-series data
SELECT
    sensor_id,
    epoch_ms(reading_time) AS timestamp_ms,
    value
FROM sensor_readings;

Required parameters:

Name | Type | Description
timestamp_expr | timestamp | The timestamp to convert to Unix epoch milliseconds

epoch_ms(milliseconds) returns TIMESTAMP

Converts Unix epoch milliseconds to a timestamp.

Example 21.27. 

-- Convert epoch milliseconds to timestamp
SELECT epoch_ms(1640995200000) AS timestamp_from_ms; -- 2022-01-01 00:00:00

-- Convert stored milliseconds back to timestamps
SELECT
    event_id,
    epoch_ms(timestamp_ms) AS event_time
FROM events;

Required parameters:

Name | Type | Description
milliseconds | bigint | Milliseconds since the Unix epoch

epoch_us(timestamp_expr) returns BIGINT

Converts timestamps to Unix epoch microseconds.

Example 21.28. 

-- Microsecond precision timestamps
SELECT epoch_us(NOW()) AS timestamp_us;

Required parameters:

Name | Type | Description
timestamp_expr | timestamp | The timestamp to convert to Unix epoch microseconds

epoch_ns(timestamp_expr) returns BIGINT

Converts timestamps to Unix epoch nanoseconds.

Example 21.29. 

-- Nanosecond precision timestamps
SELECT epoch_ns(NOW()) AS timestamp_ns;

Required parameters:

Name | Type | Description
timestamp_expr | timestamp | The timestamp to convert to Unix epoch nanoseconds

make_timestamp(microseconds) returns TIMESTAMP

Creates a timestamp from microseconds since the Unix epoch (1970-01-01 00:00:00 UTC).

Example 21.30. 

-- Create timestamp from current epoch microseconds
SELECT make_timestamp(epoch_us(NOW())) AS reconstructed_timestamp;

-- Create specific timestamps
SELECT make_timestamp(1640995200000000) AS new_years_2022; -- 2022-01-01 00:00:00

Required parameters:

Name | Type | Description
microseconds | BIGINT | Microseconds since the Unix epoch

make_timestamptz(microseconds) returns TIMESTAMPTZ

Creates a timestamp with timezone from microseconds since the Unix epoch.

Example 21.31. 

-- Create timestamptz from current epoch microseconds
SELECT make_timestamptz(epoch_us(NOW())) AS reconstructed_timestamptz;

-- Create specific timestamptz
SELECT make_timestamptz(1640995200000000) AS new_years_2022_tz;

Required parameters:

Name | Type | Description
microseconds | bigint | Microseconds since the Unix epoch

21.8. DuckDB Administration Functions

duckdb.install_extension(extension_name TEXT, repository TEXT DEFAULT 'core') returns bool

Installs a DuckDB extension and configures it to be loaded automatically in every session that uses pgpro_axe.

Example 21.32. 

  SELECT duckdb.install_extension('iceberg');
  SELECT duckdb.install_extension('avro', 'community');

Since this function can be used to install and download any extensions, it can only be executed by a superuser by default. To allow execution by other administrators, such as my_admin, you can grant them the following privileges:

Example 21.33. 

  GRANT ALL ON FUNCTION duckdb.install_extension(TEXT, TEXT) TO my_admin;

Required parameters:

Name | Type | Description
extension_name | text | The name of the extension to install

duckdb.load_extension(extension_name TEXT) returns void

Loads a DuckDB extension for the current session only. Unlike duckdb.install_extension, this does not configure the extension to be loaded automatically in future sessions.

Example 21.34. 

SELECT duckdb.load_extension('iceberg');

Required parameters:

Name | Type | Description
extension_name | text | The name of the extension to load

duckdb.autoload_extension(extension_name TEXT, autoload BOOLEAN) returns void

Specifies whether an installed extension must be automatically loaded in new sessions.

Example 21.35. 

-- Disable auto-loading for an extension
SELECT duckdb.autoload_extension('iceberg', false);

-- Enable auto-loading for an extension
SELECT duckdb.autoload_extension('iceberg', true);

Required parameters:

Name | Type | Description
extension_name | text | The name of the extension
autoload | boolean | Specifies whether the extension must be automatically loaded in new sessions

duckdb.query(query TEXT) returns SETOF duckdb.row

Executes the given SELECT query directly against DuckDB. This can be useful if the DuckDB syntax makes the query easier to write or to use a function that is not exposed by pgpro_axe yet.

Example 21.36. 

This query puts FROM before SELECT and uses a list comprehension. Both of these features are not supported in Postgres Pro.

  SELECT * FROM duckdb.query('FROM range(10) as a(a) SELECT [a for i in generate_series(0, a)] as arr');

duckdb.raw_query(extension_name TEXT) returns void

Runs an arbitrary query directly against DuckDB.

Compared to duckdb.query, this function can execute any query, not just SELECT. The main downside is that it does not return its result as rows but instead sends the query result to logs. It is recommended to use duckdb.query when possible.

duckdb.recycle_ddb() returns void

pgpro_axe keeps the DuckDB instance open inbetween transactions. This is done to save session level state, such as manually executed SET commands. To clear the session level state, you can close the currently open DuckDB instance by calling this function.

Example 21.37. 

  CALL duckdb.recycle_ddb();

21.9. Secrets Management Functions

For more information about secrets, refer to Section 14.5.

duckdb.create_simple_secret(type TEXT, key_id TEXT, secret TEXT, region TEXT, optional_parameters) returns void

Creates a simple secret for accessing cloud storage services, such as S3, GCS, or R2.

Example 21.38. 

-- Create an S3 secret
SELECT duckdb.create_simple_secret(
    type := 'S3',
    key_id := 'your_access_key',
    secret := 'your_secret_key',
    region := 'us-east-1'
);

-- Create an S3 secret with session token
SELECT duckdb.create_simple_secret(
    type := 'S3',
    key_id := 'your_access_key',
    secret := 'your_secret_key',
    region := 'us-east-1',
    session_token := 'your_session_token'
);

Required parameters:

Name | Type | Description
type | text | The type of secret (e.g., 'S3', 'GCS', 'R2')
key_id | text | The access key ID or equivalent
secret | text | The secret key or equivalent

Optional parameters:

Name | Type | Description
session_token | text | Session token for temporary credentials
endpoint | text | Custom endpoint URL

url_style

text

URL style.

Possible values:

'vhost'

'path'

use_ssl
text

Specifies whether to use SSL.

Possible values:

true

false

Default value: true.

scope | text | Scope for the secret. Default value: ''
region | text | The region for the service

Chapter 22. Supported Extensions

pgpro_axe supports the following extensions.

iceberg

iceberg support allows reading iceberg tables and metadata.

For more information about iceberg functions, refer to Chapter 21.

delta

delta support adds the ability to read delta Lake files using delta_scan.

Chapter 23. Configuration Parameters

You can configure pgpro_axe parameters in the postgresql.conf file.

duckdb.force_execution

Forces queries to use DuckDB execution. This is necessary when accessing only Postgres Pro tables in a query. As soon as you use a DuckDB-only feature, DuckDB execution is used automatically.

DuckDB-only features include reading from DuckDB tables, using DuckDB functions (e.g., read_parquet) or COPY to a remote storage (e.g., s3://).

Default value: false.

Access: All users.

duckdb.default_collation

Specifies the default collation to use for DuckDB string operations and sorting. This parameter allows configuring locale-specific string comparison behavior.

Example 23.1. 

"en_us"

"de_de"

"C"

Default value: "" — uses DuckDB default.

Access: Superuser-only.

23.1. Security

duckdb.postgres_role

Specifies the Postgres Pro role that is allowed to use DuckDB execution and manage secrets. If the specified role does not exist when running CREATE EXTENSION pgpro_axe, this role is created automatically.

Default value: "" — superusers only.

Access: Requires a restart.

duckdb.disabled_filesystems

Disables storages for pgpro_axe.

Possible values:

LocalFileSystem: Disable local storages.

S3FileSystem: Disable S3 storages.

HTTPFileSystem: Disable network storages.

By default, the local storage is disabled:

Example 23.2. 

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

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

Example 23.3. 

  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)

Access: Superuser-only.

duckdb.autoinstall_known_extensions

Specifies whether known extensions can be automatically installed when a DuckDB query depends on them.

Possible values:

true

false

By default, only superusers can execute duckdb.install_extension and duckdb.autoload_extension. This prevents other users from installing extensions that can have security implications or interfere with database operation.

Users can only use extensions that DuckDB marked as auto-installable. To restrict the use of these extensions to a specific list of allowed extensions, set duckdb.autoinstall_known_extensions to false.

Default value: true.

Access: Superuser-only.

duckdb.autoload_known_extensions

Specifies whether known extensions can be automatically loaded when a DuckDB query depends on them.

Default value: true.

Access: Superuser-only

duckdb.allow_community_extensions

Specifies whether community extensions can be installed.

Default value: false.

Access: Superuser-only.

duckdb.unsafe_allow_execution_inside_functions

Allows DuckDB execution inside Postgres Pro functions.

Important

This feature can cause Postgres Pro AXE to crash and is disabled by default.

Default value: false.

Access: Superuser-only.

duckdb.enable_external_access

Allows DuckDB to access external resources (e.g., HTTP, S3).

Important

This feature can cause Postgres Pro AXE to crash and is disabled by default.

Default value: true.

Access: Superuser-only.

23.2. Resource Management

Since any connection that uses DuckDB has its own DuckDB instance, these parameters are per-connection. When using pgpro_axe in many concurrent connections, it is recommended to specify lower values for certain parameters as compared to their defaults.

duckdb.max_memory / duckdb.memory_limit

The maximum memory that Postgres Pro can use to process a single query, in megabytes. You can configure this parameter for each session. When set to zero, 80% of RAM is used.

Example 23.4. 

4096 (4GB)

8192 (8GB)

1024 (1GB)

Default value: 4096 (4GB).

Access: Superuser-only.

duckdb.threads / duckdb.worker_threads

The maximum number of threads (CPU cores) that Postgres Pro can use to process a single query. You can configure this parameter for each session.

Default value: -1 — the value is equal to the number of CPU cores.

Access: Superuser-only.

duckdb.max_workers_per_postgres_scan

The maximum number of Postgres Pro workers used for a single Postgres Pro scan. This parameter is similar to max_parallel_workers_per_gather.

Default value: 2.

Access: All users.

duckdb.threads_for_postgres_scan

The maximum number of DuckDB threads used for a single Postgres Pro scan. This parameter controls parallelism within DuckDB when scanning Postgres Pro tables.

Default value: 2.

Access: All users.

23.3. Advanced

duckdb.convert_unsupported_numeric_to_double

Converts NUMERIC types with unsupported precision/scale to DOUBLE instead of throwing an error.

Possible values:

true

false

DuckDB supports NUMERIC/DECIMAL with precision 1-38 and scale 0-38 (where scale ≤ precision). For NUMERICs outside these limits:

If true is specified, unsupported NUMERICs are converted to DOUBLE with possible precision loss.

If false is specified, unsupported NUMERICs cause an error.

Default value: false.

Access: All users.

duckdb.unsafe_allow_mixed_transactions

Allows mixed transactions between DuckDB and Postgres Pro.

Important

This parameter enables transactions that modify data in both databases, but it may cause consistency issues and is not recommended for production use.

Default value: false.

Access: All users.

23.4. Storage

duckdb.temporary_directory

Specifies the directory where DuckDB writes temporary files. By default, DuckDB uses a directory under the Postgres Pro directory (DataDir/pg_duckdb/temp). This parameter can be useful for pointing to faster storage (e.g., an SSD) or managing disk space more effectively.

Default value: "DataDir/pg_duckdb/temp".

Access: Superuser-only.

duckdb.max_temp_directory_size / duckdb.max_temporary_directory_size

The maximum amount of data that can be stored in the DuckDB temporary directory. This parameter allows preventing runaway queries from consuming all available disk space.

Example 23.5. 

"10GB"

"500MB"

"2TB"

Default value: "" — no limit is enforced.

Access: Superuser-only.

duckdb.extension_directory

Specifies the directory where DuckDB stores its extensions. By default, extensions are stored under the Postgres Pro directory (DataDir/pg_duckdb/extensions). This is useful for managing the extension storage or sharing extensions across multiple Postgres Pro instances.

Default value: "DataDir/pg_duckdb/extensions".

Access: Superuser-only.

23.5. For Developers

duckdb.allow_unsigned_extensions

Allows DuckDB to load extensions with invalid or missing signatures. This is mostly useful for the development of DuckDB extensions.

Default value: false.

Access: Superuser-only.

duckdb.log_pg_explain

Logs the EXPLAIN plan of a Postgres Pro scan at the NOTICE log level. This parameter is useful for debugging query execution and understanding how DuckDB interacts with Postgres Pro tables.

Default value: false.

Access: All users.

Chapter 24. Transactions

pgpro_axe supports multi-statement transactions. It is currently required to ensure the expected ACID guarantees: you cannot write to both a Postgres Pro table and a DuckDB table in the same transaction.

Similarly, you can do DDL (such as CREATE/DROP TABLE) on DuckDB tables inside a transaction, but it is not allowed to combine such statements with DDL involving Postgres Pro objects.

This restriction can be completely disabled to allow writes to both DuckDB and Postgres Pro in the same transaction by setting duckdb.unsafe_allow_mixed_transactions to true. However, it is not recommended to do so, as it can result in the transaction being committed only in DuckDB, but not in Postgres Pro. This can lead to inconsistencies and data loss.

Example 24.1. 

The following query may result in deleting duckdb_table while not copying its contents to pg_table:

  BEGIN;
  SET LOCAL duckdb.unsafe_allow_mixed_transactions TO true;
  CREATE TABLE pg_table AS SELECT * FROM duckdb_table;
  DROP TABLE duckdb_table;
  COMMIT;

Chapter 25. Syntax

25.1. Creating a Postgres Pro Table

Example 25.1. 

-- This is a standard Postgres Pro table
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  product_name TEXT,
  amount NUMERIC,
  order_date DATE
);

INSERT INTO orders (product_name, amount, order_date)
VALUES ('Laptop', 1200.00, '2024-07-01'),
      ('Keyboard', 75.50, '2024-07-01'),
      ('Mouse', 25.00, '2024-07-02');

25.2. Querying Postgres Pro Tables

For analytical queries on your existing Postgres Pro tables, use standard SQL. No special syntax is needed. pgpro_axe automatically accelerates these queries when you set duckdb.force_execution to true.

Example 25.2. 

SET duckdb.force_execution = true;
-- Standard SELECT on a Postgres Pro table
SELECT
  category,
  AVG(price) as avg_price,
  COUNT(*) as item_count
FROM
  products -- This is a regular Postgres Pro table
GROUP BY
  category
ORDER BY
  avg_price DESC;

SET duckdb.force_execution = true;
-- Standard SELECT on a Postgres Pro table
SELECT
  category,
  AVG(price) as avg_price,
  COUNT(*) as item_count
FROM
  products -- This is a regular Postgres Pro table
GROUP BY
  category
ORDER BY
  avg_price DESC;

25.3. Querying External Files

To query files from a data lake (e.g., local or S3 storage), use read_* functions.

To access columns, use the r['column_name'] syntax.

Example 25.3. 

-- Query a single Parquet file
SELECT
  r['product_id'],
  r['review_text']
FROM
  read_parquet('s3://my-bucket/reviews.parquet') r -- 'r' is a required alias
LIMIT 100;

-- Query multiple CSV files using a glob pattern
SELECT
  r['timestamp'],
  r['event_type'],
  COUNT(*) as event_count
FROM
  read_csv('s3://my-datalake/logs/2024-*.csv') r
GROUP BY
  r['timestamp'],
  r['event_type'];

25.4. Joining Postgres Pro and External Data

You can join Postgres Pro tables with external data sources in a single query.

Example 25.4. 

-- Join a local Postgres Pro 'customers' table with a remote Parquet file of 'orders'
SELECT
  c.customer_name,
  c.signup_date,
  SUM(r['order_total']) AS total_spent
FROM
  customers c -- This is a Postgres Pro table
JOIN
  read_parquet('s3://my-bucket/orders/*.parquet') r ON c.customer_id = r['customer_id']
WHERE
  c.status = 'active'
GROUP BY
  c.customer_name,
  c.signup_date
ORDER BY
  total_spent DESC;

25.5. Using duckdb.query()

The duckdb.query() function is an advanced feature that is not required for most queries. You must only use it to run a query that uses the DuckDB-specific syntax that is not valid in Postgres Pro.

Example 25.5. 

-- This query uses DuckDB's PIVOT syntax, so it must be wrapped in duckdb.query()
SELECT * FROM duckdb.query($$
  PIVOT sales_summary
  ON product_category
  USING SUM(total_sales)
  GROUP BY region;
$$);

Part V. pgpro_metastore

pgpro_metastore is a Postgres Pro extension used for processing and storing the OLAP data.

This extension allows you to manage analytical tables with the OLAP data. Rows of analytical tables are stored as Parquet files in a local, network, or S3 storage. Metadata of analytical tables is stored in metadata tables (for compliance with DuckLake specification).

pgpro_metastore offers the following advantages:

Executing analytical queries 10–30 times faster and saving 5–10 times less OLAP data as compared to heap tables.

Using Parquet-compatible tools, such as Jupyter and Notebook, for processing the OLAP data.

Unlimited storage scalability for increasing the amount of saved OLAP data and accessing it faster.

Independent scalability of storage compute resources.

Transparent scheme that provides flexible OLAP data processing scenarios by means of required tools.

Granular restriction of storage access similar to Postgres Pro.

Transactional data updates and consistency between the OLAP data and metadata.

Transactions with persistent IDs, which allows querying the OLAP data and metadata based on specific IDs and restoring the data to specific timestamps.

Collecting statistics for all columns and transactions, which allows querying required Parquet files.

Chapter 26. Configuring the pgpro_metastore Catalog

Metadata tables of pgpro_metastore are stored in the axe_catalog schema, or the so-called pgpro_metastore catalog. This catalog can be created on the pgpro_metastore server or on a separate server.

To configure the pgpro_metastore catalog:

Configure the connection to the pgpro_metastore catalog server:

SELECT metastore.define_catalog_connection('host', 'port', 'DB_name', 'username', 'password')

Where:

host and port: The hostname and port number of the pgpro_metastore catalog server.

DB_name: The name of the database where the pgpro_metastore catalog is created.

username and password: The username and password for connecting to the pgpro_metastore catalog server.

If one of these parameters is missing, for example, when connecting to the pgpro_metastore catalog server without a user password, pass an empty string instead of a value:

SELECT metastore.define_catalog_connection('host','port','DB_name','','');

For the changes to take effect, reconnect to the pgpro_metastore server (not to the pgpro_metastore catalog server).

If the connection to the pgpro_metastore catalog server fails, you are automatically reconnected using the last connection parameters:

Example 26.1. 

postgres=# SELECT metastore.remove_storage('mt_storage');
INFO:  Connection failed! Trying to reconnect.
INFO:  Reconnecting...1
INFO:  Reconnect successful!
        remove_storage
-----------------------------------
Storage was removed successfully.
(1 row)

Create the pgpro_metastore catalog:

SELECT metastore.init(force_init bool DEFAULT false)

If the pgpro_metastore catalog is already created on the server, you receive a warning:

postgres=# SELECT metastore.init();
INFO:  Metastore catalog already initialized on remote server
INFO:  Run metastore.init(true) to force rewrite catalog on remote server
init
------

(1 row)

In this case, you can recreate the pgpro_metastore catalog by executing the same query with force_init set to true:

  postgres=# SELECT metastore.init(true);
  init
  ------

  (1 row)

Chapter 27. Metadata Tables

The metadata stored in metadata tables of pgpro_metastore is required for the following functionality:

Creating analytical views.

Querying analytical tables that use partitioning and indexes of Parquet files.

Supporting basic ETL operations:

performing ETL operations

scheduling ETL operations to be performed at a certain date and time, as well as sequentially (one after another)

limiting resources available for ETL operations to avoid impacting the OLTP workload

Working with the OLAP data:

creating Parquet files with the OLAP data from Postgres Pro tables and importing this data to analytical tables

exporting the OLAP data from analytical tables to Parquet, CSV, and JSON files

creating reports with filtered OLAP data using SQL queries

Making transaction updates to pgpro_metastore objects, as well as providing consistent updates of analytical tables when reading and writing to these tables.

Checking data integrity and restoring Parquet files from backups or by using S3 storage mirrors.

Monitoring pgpro_metastore and notifying administrators about events requiring their immediate attention.

Storing Parquet files in separate storages to avoid competition with the OLTP workload for I/O throughput.

The schema of metadata tables is shown in the diagram below.

27.1. pga_snapshot Metadata Table

This metadata table contains information about snapshots.

Column Name | Column Type | Constraint | Description
snapshot_id | BIGINT | Primary key | The snapshot ID. The value is increased by 1 for each created snapshot. This is a primary key referred to by most metadata tables.
snapshot_time | TIMESTAMP | NOT NULL | The snapshot creation date and time.
schema_version | BIGINT | NOT NULL | The pgpro_metastore catalog version. The value is increased by 1 at each pgpro_metastore object update, for example, when a new analytical table is created.
next_catalog_id | BIGINT | NOT NULL | The ID assigned to the next pgpro_metastore object, such as an analytical table. The value is increased each time an object is created.
next_file_id | BIGINT | NOT NULL | The ID assigned to the next created Parquet file. The value is increased each time a file is created.

is_visible

BOOLEAN

NOT NULL

Specifies whether pgpro_metastore objects referring to the snapshot are visible.

Possible values:

true

false

When an ETL operation, such as add_table, creates multiple objects referring to the same snapshot, the is_visible value is set to false for this snapshot. New objects are not visible until the operation is executed.

After this, the is_visible value is set to true for the snapshot, and new objects become visible, i.e., they are in the SELECT command output.

You can use the is_visible value to temporarily hide an object without deleting it and then make it visible again later.

27.2. pga_snapshot_changes Metadata Table

This metadata table contains information about snapshot changes. You can use it to resolve conflicts between different snapshots.

Column Name | Column Type | Constraint | Description
snapshot_id | BIGINT | Primary key, foreign key | The ID of the snapshot from the pga_snapshot metadata table.

changes_made

VARCHAR

-

A comma-separated list of snapshot changes in the following formats:

created_schema:schema_name: The snapshot is associated with creating an analytical schema.

created_table:table_name: The snapshot is associated with creating an analytical table.

created_view:view_name: The snapshot is associated with creating a Postgres Pro view.

inserted_into_table:table_ID: The snapshot is associated with adding the OLAP data to an analytical table.

deleted_from_table:table_ID: The snapshot is associated with deleting the OLAP data from an analytical table.

dropped_schema:schema_ID: The snapshot is associated with deleting an analytical schema.

dropped_table:table_ID: The snapshot is associated with deleting an analytical table.

dropped_view:view_ID: The snapshot is associated with deleting a Postgres Pro view.

altered_table:table_ID: The snapshot is associated with updating metadata of an analytical table.

altered_view:view_ID: The snapshot is associated with updating metadata of a Postgres Pro view.

27.3. pga_schema Metadata Table

This metadata table contains information about analytical schemas.

Column Name | Column Type | Constraint | Description
schema_id | BIGINT | Primary key | The analytical schema ID assigned by increasing the next_catalog_id value of the pga_snapshot metadata table.
schema_uuid | UUID | NOT NULL, UNIQUE | The persistent analytical schema ID stored for compatibility with Postgres Pro schemas.
begin_snapshot | BIGINT | Foreign key, NOT NULL | The snapshot from the pga_snapshot metadata table associated with the analytical schema creation date and time.
end_snapshot | BIGINT | Foreign key | The snapshot from the pga_snapshot metadata table associated with the analytical schema deletion date and time. If the value is NULL, the analytical schema is not deleted.
schema_name | VARCHAR | NOT NULL, UNIQUE | The unique analytical schema name, for example, my_schema.

27.4. pga_table Metadata Table

This metadata table contains information about analytical tables.

Column Name | Column Type | Constraint | Description
table_id | BIGINT | Primary key | The analytical table ID assigned by increasing the next_catalog_id value of the pga_snapshot metadata table.
table_uuid | UUID | NOT NULL, UNIQUE | The persistent analytical table ID stored for compatibility with Postgres Pro tables.
begin_snapshot | BIGINT | Foreign key, NOT NULL | The snapshot from the pga_snapshot metadata table associated with the analytical table creation date and time.
end_snapshot | BIGINT | Foreign key | The snapshot from the pga_snapshot metadata table associated with the analytical table deletion date and time. If the value is NULL, the analytical table is not deleted.
schema_id | BIGINT | NOT NULL, UNIQUE | The ID of the analytical schema from the pga_schema metadata table where the analytical table is created.

table_name

VARCHAR

NOT NULL

The analytical table name, for example, my_table.

table_name values are not UNIQUE because pgpro_metastore does not delete metadata of analytical tables. Deleted analytical tables are rather associated with end_snapshot values to indicate that these tables can no longer be used.

If table_name values were UNIQUE, you would not be able to create a new analytical table with the same name as a previously deleted table.

path | VARCHAR | NOT NULL, UNIQUE | The path to the shared directory relative to the uri value of the pga_storage metadata table. The absolute path is generated by concatenating the uri value of the pga_storage metadata table and the path value of the pga_table metadata table.
storage_id | BIGINT | Foreign key, NOT NULL | The ID of the storage from the pga_storage metadata table where the analytical table is created.

27.5. pga_storage Metadata Table

This metadata table contains information about storages.

Column Name | Column Type | Constraint | Description
storage_id | BIGINT | Primary key | The storage ID assigned by increasing the next_catalog_id value of the pga_snapshot metadata table.
begin_snapshot | BIGINT | Foreign key, NOT NULL | The snapshot from the pga_snapshot metadata table associated with the storage creation date and time.
end_snapshot | BIGINT | Foreign key | The snapshot from the pga_snapshot metadata table associated with the storage deletion date and time. If the value is NULL, the storage is not deleted.

storage_name

VARCHAR

NOT NULL

The storage name, for example, my_storage.

storage_name values are not UNIQUE because pgpro_metastore does not delete metadata of storages. Deleted storages are rather associated with end_snapshot values to indicate that these storages can no longer be used.

If storage_name values were UNIQUE, you would not be able to create a new storage with the same name as a previously deleted storage.

uri

VARCHAR

NOT NULL, UNIQUE

The URI of the root storage directory.

Local storages have simple URIs, for example, file:///home/j.doe/workspace/axe/.

S3 storages have URIs with more complicated structure and parsing, for example, s3://premdb/team01.csv?versionId=w_B5qT8s5MkiT09.IRHay0lW.PycsHTS. These URIs are generated using third-party libraries.

tmp_uri

VARCHAR

NOT NULL, UNIQUE

The URI of the storage directory for temporary files.

Local storages have simple URIs, for example, file:///home/j.doe/workspace/axe/.

S3 storages have URIs with more complicated structure and parsing, for example, s3://premdb/team01.csv?versionId=w_B5qT8s5MkiT09.IRHay0lW.PycsHTS. These URIs are generated using third-party libraries.

storage_type | BIGINT | NOT NULL | The storage type. Currently, local, network, and S3 storages are supported.
min_space | BIGINT | NOT NULL | The minimum free space for the storage, in bytes. It is verified when executing the add_storage ETL operation or any operation that requires removing temporary files from the directory specified by the tmp_uri value.
uri_id | BIGINT | Foreign key, NOT NULL, UNIQUE | The ID of the URI from the pga_uri metadata table associated with the root storage directory.
tmp_uri_id | BIGINT | Foreign key, NOT NULL, UNIQUE | The ID of the URI from the pga_uri metadata table associated with the storage directory for temporary files.

27.6. pga_uri Metadata Table

This metadata table contains system pgpro_metastore information, and it is not intended to be updated by users.

The uri column contains initial serialized URIs associated with uri or tmp_uri values of the pga_storage metadata table.

The path column contains paths to root directories of storages. This is the only relevant column for local storages, and its values usually match uri values, except for prefixes specifying the storage type:

file:// for local storages

s3:// for S3 storages

Remaining columns contain arbitrary URIs for S3 storages.

For more information, refer to RFS 3968 and to the official AWS documentation.

27.7. pga_folder Metadata Table

This metadata table contains information about shared directories used for storing Parquet files. For example, you can use these directories as a source of Parquet files for the add_files ETL operation.

Column Name | Column Type | Constraint | Description
folder_id | BIGINT | Primary key | The shared directory ID assigned by increasing the next_catalog_id value of the pga_snapshot metadata table.
begin_snapshot | BIGINT | Foreign key, NOT NULL | The snapshot from the pga_snapshot metadata table associated with the shared directory creation date and time.
end_snapshot | BIGINT | Foreign key | The snapshot from the pga_snapshot metadata table associated with the shared directory deletion date and time. If the value is NULL, the shared directory is not deleted.
path | VARCHAR | NOT NULL | The path to the shared directory relative to the root storage directory. The absolute path is generated by concatenating the uri value of the pga_storage metadata table and the path value of the pga_folder metadata table.

folder_name

VARCHAR

NOT NULL

The shared directory name, for example, my_directory.

folder_name values are not UNIQUE because pgpro_metastore does not delete metadata of shared directories. Deleted directories are rather associated with end_snapshot values to indicate that these directories can no longer be used.

If folder_name values were UNIQUE, you would not be able to create a new shared directory with the same name as a previously deleted directory.

storage_id | BIGINT | Foreign key, NOT NULL | The ID of the storage from the pga_storage metadata table where the shared directory is created.

27.8. pga_column Metadata Table

This metadata table contains information about columns of analytical tables.

Column Name | Column Type | Constraint | Description
column_id | BIGINT | Primary key | The column ID. If the initial Parquet file contains the field_id column ID, this ID is used as the column_id value. The ID is not updated until the column is deleted. column_id values must be unique.
begin_snapshot | BIGINT | Foreign key, NOT NULL | The snapshot from the pga_snapshot metadata table associated with the column creation date and time.
end_snapshot | BIGINT | Foreign key | The snapshot from the pga_snapshot metadata table associated with the column deletion date and time. If the value is NULL, the column is not deleted.
table_id | BIGINT | Foreign key, NOT NULL | The ID of the analytical table from the pga_table metadata table where the column is created.
column_order | BIGINT | NOT NULL | The sequence number of the column. column_order values must be unique. However, their sequence can be interrupted, i.e., empty values are possible.

column_name

VARCHAR

NOT NULL

The column name, for example, my_column.

column_name values are not UNIQUE because pgpro_metastore does not delete metadata of columns. Deleted columns are rather associated with end_snapshot values to indicate that these columns can no longer be used.

If column_name values were UNIQUE, you would not be able to create a new column with the same name as a previously deleted column.

column_type | VARCHAR | NOT NULL | The column type corresponding to one of the supported pgpro_metastore data types.
initial_default | VARCHAR | - | The initial column value after its creation, for example, after executing ALTER TABLE. It can be NULL.
default_value | VARCHAR | - | The default column value used in upsert operations. It can be NULL.

nulls_allowed

BOOLEAN

-

Specifies whether the column can contain NULL values.

Possible values:

true

false

is_uniq

BOOLEAN

-

Specifies whether the column can contain repeating values for different rows.

Possible values:

true

false

27.9. pga_data_file Metadata Table

This metadata table contains information about Parquet files.

Column Name | Column Type | Constraint | Description
data_file_id | BIGINT | Primary key | The Parquet file ID assigned by increasing the next_file_id value of the pga_snapshot metadata table.
begin_snapshot | BIGINT | Foreign key, NOT NULL | The snapshot from the pga_snapshot metadata table associated with the Parquet file creation date and time.
end_snapshot | BIGINT | Foreign key | The snapshot from the pga_snapshot metadata table associated with the Parquet file deletion date and time. If the value is NULL, the Parquet file is not deleted.
table_id | BIGINT | Foreign key, NOT NULL | The ID of the analytical table from the pga_table metadata table associated with the Parquet file.
file_order | BIGINT | NOT NULL | The vertical position of the Parquet file. file_order values must be unique. However, their sequence can be interrupted, i.e., empty values are possible.
path | VARCHAR | NOT NULL, UNIQUE | The path to the Parquet file, for example, my_file.parquet.

path_is_relative

BOOLEAN

NOT NULL

Specifies whether the path to the Parquet file is relative to the path to the analytical table or absolute.

Possible values:

true

false

Default value: false.

file_format | VARCHAR | NOT NULL | The file format. Currently, only the Parquet format is supported.
record_count | BIGINT | NOT NULL | The number of rows in the Parquet file.
file_size_bytes | BIGINT | NOT NULL | The Parquet file size, in bytes.
row_id_start | BIGINT | NOT NULL | The row_id value for the first row corresponding to the Parquet file. Indicates where rows corresponding to the Parquet file start.
checksum_crc32 | BIGINT | NOT NULL | The current checksum value of the Parquet file. It is used for data integrity checks.

27.10. pga_files_scheduled_for_deletion Metadata Table

This metadata table contains information about Parquet files associated with snapshots that are marked as expired. These files can now be deleted.

Column Name | Column Type | Constraint | Description
data_file_id | BIGINT | Primary key, foreign key | The ID of the Parquet file from the pga_data_file metadata table.
path | VARCHAR | NOT NULL, UNIQUE | The path to the Parquet file, for example, my_file.parquet.

path_is_relative

BOOLEAN

NOT NULL

Specifies whether the path to the Parquet file is relative to the path to the analytical table or absolute.

Possible values:

true

false

Default value: false.

schedule_start | TIMESTAMPTZ | NOT NULL | The scheduled Parquet file deletion date and time.

27.11. pga_table_stats Metadata Table

This metadata table contains statistics of analytical tables.

Column Name | Column Type | Constraint | Description
table_id | BIGINT | Primary key, foreign key | The ID of the analytical table from the pga_table metadata table.
record_count | BIGINT | NOT NULL | The number of rows in the analytical table.
next_row_id | BIGINT | NOT NULL | The row_id value for the next column inserted in the analytical table.
file_size_bytes | BIGINT | NOT NULL | The total size of all Parquet files associated with the analytical table, in bytes.

27.12. pga_table_column_stats Metadata Table

This metadata table contains statistics of analytical table columns.

Column Name | Column Type | Constraint | Description
column_id | BIGINT | Primary key, foreign key | The ID of the column from the pga_column metadata table.

contains_null

BOOLEAN

NOT NULL

Specifies whether the column can contain NULL values.

Possible values:

true

false

contains_nan

BOOLEAN

-

Specifies whether the column can contain NaN values.

Possible values:

true

false

This is relevant only for FLOAT, DOUBLE, and NUMERIC data types.

min_value | VARCHAR | - | The minimum column value. It is displayed as a string for consistency and must be converted to the current column data type when used.
max_value | VARCHAR | - | The maximum column value. It is displayed as a string for consistency and must be converted to the current column data type when used.

27.13. pga_file_column_statistics Metadata Table

This metadata table contains statistics of analytical table columns in the context of Parquet files.

Column Name | Column Type | Constraint | Description
data_file_id | BIGINT | Primary key, foreign key | The ID of the Parquet file from the pga_data_file metadata table.
column_id | BIGINT | Primary key, foreign key | The ID of the column from the pga_column metadata table.
column_size | BIGINT | NOT NULL | The column size, in bytes.
value_count | BIGINT | NOT NULL | The number of column elements in the Parquet file. This is not the same as the number of Parquet file entries since there can be nested entries, such as structures and lists.
null_count | BIGINT | NOT NULL | The number of NULL values in the column.
min_value | VARCHAR | - | The minimum column value in the Parquet file. You can use it for estimating the upper bound of the minimum column value.
max_value | VARCHAR | - | The maximum column value in the Parquet file. You can use it for estimating the lower bound of the maximum column value.

27.14. pga_transaction_log Metadata Table

This metadata table contains logging information about pgpro_metastore tasks analogous to standard DBMS transactions.

Column Name | Column Type | Constraint | Description
lsn | BIGSERIAL | Primary key | The transaction log sequence number (LSN).
txid | BIGINT | NOT NULL | The task ID.
op_id | INT | - | The ETL operation ID. One task can consist of multiple ETL operations.
kind | VARCHAR | NOT NULL | The ETL operation type, such as add_storage, add_table, add_files, and create_view.
attr | JSONB | - | The initial and runtime data associated with the task.

Chapter 28. Reading Metadata

28.1. Retrieving a Snapshot

Execute the following query:

SELECT *
FROM pga_snapshot
WHERE snapshot_id = snapshot_ID;

Where snapshot_ID is the ID of the snapshot from the pga_snapshot metadata table.

To retrieve the latest snapshot, execute the following query:

SELECT *
FROM pga_snapshot
WHERE snapshot_id =
  (SELECT max(snapshot_id) FROM pga_snapshot);

28.2. Retrieving Analytical Schemas

Execute the following query:

SELECT schema_id, schema_name
FROM pga_schema
WHERE
    snapshot_ID >= begin_snapshot AND
    (snapshot_ID < end_snapshot OR end_snapshot IS NULL);

Where snapshot_ID is the ID of the snapshot from the pga_snapshot metadata table.

28.3. Retrieving Analytical Tables

Execute the following query:

SELECT table_id, table_name
FROM pga_table
WHERE
    schema_id = schema_ID AND
    snapshot_ID >= begin_snapshot AND
    (snapshot_ID < end_snapshot OR end_snapshot IS NULL);

Where:

schema_ID: The ID of the analytical schema from the pga_schema metadata table where analytical tables are created.

snapshot_ID: The ID of the snapshot from the pga_snapshot metadata table.

28.4. Retrieving Columns of an Analytical Table

Execute the following query:

SELECT column_id, column_name, column_type
FROM pga_column
WHERE
    table_id = table_ID AND
    snapshot_ID >= begin_snapshot AND
    (snapshot_ID < end_snapshot OR end_snapshot IS NULL)
ORDER BY column_order;

Where:

table_ID: The ID of the analytical table from the pga_table metadata table where columns are created.

snapshot_ID: The ID of the snapshot from the pga_snapshot metadata table.

28.5. Retrieving Filtered Parquet Files

You can retrieve Parquet files after retrieving columns of an analytical table. Retrieved Parquet can be filtered using statistics from the pga_file_column_statistics metadata table.

To retrieve Parquet files and filter them by column values, execute the following query:

SELECT data_file_id
FROM ducklake_file_column_stats
WHERE
    table_id = table_ID AND
    column_id = column_ID AND
    (SCALAR >= min_value OR min_value IS NULL) AND
    (SCALAR <= max_value OR max_value IS NULL);

Where:

table_ID: The ID of the analytical table from the pga_table metadata table associated with Parquet files.

column_ID: The ID of the column from the pga_column metadata table whose values are used for filtering Parquet files.

In this example, only Parquet files that do not contain scalar values in the column_ID column are retrieved.

You can filter column values using different conditions, such as "greater than (>)", by updating the query accordingly.

The minimum and maximum values of each column are stored as arrays and must be converted to integers.

Chapter 29. Configuring Objects Visibility

You can execute transactional ETL operations on pgpro_metastore objects. Similarly to Postgres Pro, snapshots are used to isolate procedures. Snapshots are monotonically increasing sequences of numbers. Each object has a snapshot number associated with its creation and deletion.

ETL operations use snapshot numbers to determine the visibility of pgpro_metastore objects. By default, the maximum snapshot number is used to read the latest state of pgpro_metastore. For example, you can request a list of Parquet files of an analytical table by executing the following query:

Example 29.1. 

  SELECT data.path AS data_file_path
  FROM ducklake_data_file AS data
  WHERE
      data.table_id = TABLE_ID AND
      SNAPSHOT_ID >= data.begin_snapshot AND
      (SNAPSHOT_ID < data.end_snapshot OR data.end_snapshot IS NULL)
  ORDER BY file_order;

If an ETL operation adds or deletes a pgpro_metastore object, it creates a new snapshot and specifies the snapshot number for the begin_snapshot or end_snapshot value of the object depending on the operation type.

While metadata tables are stored on the pgpro_metastore catalog server, snapshots are not associated with Postgres Pro transaction snapshots. This allows executing ETL operations even if the pgpro_metastore catalog server is temporarily unavailable after reloading. The downside of the separate snapshots implementation is that it requires a separate conflict resolution mechanism when two snapshots modify the same objects.

pgpro_metastore also uses snapshots of the Postgres Pro server where ETL operations are executed. Currently, executing multiple operations in the same transaction block is not supported. Even if source Postgres Pro tables stay consistent during the transaction, they will be added to pgpro_metastore in separate snapshots.

29.1. Isolation Guarantees

In terms of isolation, there are two types of ETL operations:

Selecting from analytical tables.

For these operations, standard Postgres Pro views are used. Currently, reading a view associated with an analytical table returns the latest version of data, even for transactions with the repeatable read isolation level or higher. In this case, only committed OLAP data is visible.

Storage mechanisms are used to provide isolation.

Other ETL operations with pgpro_metastore.

During the execution of these procedures, read-only queries are sent to pgpro_metastore, and all queries updating metadata are executed at the end in a single serialized transaction. This allows resolving most of the conflicts. The additional triggers logic is used when required.

For more information, refer to Chapter 15, Chapter 16, Chapter 19, and Chapter 20.

29.2. Atomicity Guarantees

ETL operations also support the atomicity of object updates. If an operation is canceled, all intermediate data is deleted, and updates of metadata tables are not committed.

Even if procedures update pgpro_metastore objects in one transaction, additional service transactions are committed to log data updates. Logs allow canceling updates when the server fails or reloads. You can do this by executing the following query:

  SELECT metastore.cleanup();

Part VI. Appendixes

Appendix A. Release Notes

Release date:  2026-01-30

This is the first public release of Postgres Pro AXE.

The solution provides the following functionality:

OLTP (online transaction processing) and OLAP (online analytical processing) scenarios using Postgres Pro DBMS.

Vectorized database queries and using the columnar data storage model when working with Postgres Pro tables, as well as with external storages.

Building OLAP data warehouses.

Storing the OLAP data of analytical tables as Parquet files.

Fast historical data search and analysis.

Appendix B. Glossary

Analytical schema

A separate namespace for the metadata of analytical tables. Analytical schemas are similar to Postgres Pro schemas.

The metadata of analytical schemas is stored in the pga_schema metadata table.

For more information about working with analytical schemas, refer to Chapter 18.

Analytical table

A set of columns and rows with the OLAP data featuring a full history of data and table type updates.

Rows of analytical tables are stored as Parquet files in a storage. The metadata of analytical tables is stored in the pga_table metadata table.

For more information about working with analytical tables, refer to Chapter 16.

Analytical table type

An ordered set of analytical table columns consisting of their names, types, and constraints. The analytical table type is updated when working with columns.

Analytical table view

A Postgres Pro view that provides users with a set of analytical table columns and rows. Views use the metadata of analytical tables from the pgpro_metastore catalog to effectively execute analytical queries with partition pruning and predicate pushdown.

For more information about creating analytical table views, refer to Section 16.3.

Extract, Transform, Load (ETL) operation

An operation that extracts, transforms, or loads the OLAP data of analytical tables. Currently, pgpro_metastore supports the following ETL operations:

add_storage

remove_storage

add_table

rename_table

create_view

remove_table

add_column

rename_column

change_column_type

delete_column

add_schema

remove_schema

add_folder

remove_folder

add_files

copy_table

expire_snapshot

Heap table

A standard Postgres Pro table.

Parquet

An open-source binary file format designed for storing and processing large amounts of data. The data is organized in columns rather than rows. This columnar structure allows executing analytical queries faster because only the required columns are scanned, reducing the amount of processed data. The Parquet format also supports compression and encoding, such as column compression, dictionary encoding, and run-length encoding (RLE) to decrease the file size.

pgpro_metastore stores the OLAP data as Parquet files.

The information about Parquet files is stored in pga_data_file, pga_files_scheduled_for_deletion, and pga_file_partition_value metadata tables.

For more information about the Parquet format, refer to https://parquet.apache.org.

Partition

A group of Parquet files created according to partitioning criteria, such as specific column values or ranges of column values. Each analytical table has at least one partition. Additional partitions can be performed in one of the following ways:

automatically as the size limit for Parquet files in a partition is reached

according to partitioning criteria

Partitioning

The process of distributing the OLAP data of analytical tables between Parquet files based on column values. Partitioning is performed in a way that optimizes the execution time of queries to analytical tables and allows using predicate pushdown to exclude from scanning Parquet files that do not meet query conditions. Analytical tables can be repartitioned at any time.

Currently, only hive partitioning provided by DuckDB is supported.

pgpro_metastore catalog

The axe_catalog schema where metadata tables are stored. It can be created on the pgpro_metastore server or on a separate server.

pgpro_metastore objects

Main pgpro_metastore entities to which access privileges can be granted:

storages

analytical tables

shared directories

Shared directory

A storage directory where Parquet files are located when adding and exporting the OLAP data from analytical tables.

The metadata of shared directories is stored in the pga_folder metadata table.

For more information about shared directories, refer to Chapter 19.

Snapshot

An entity used for supporting the temporality of pgpro_metastore by keeping the history of OLAP data and metadata updates. All user actions result in snapshot creation, and each snapshot is associated with a single update.

A specific instance of snapshot usage is snapshots of an analytical table. They reflect the state of the analytical table, including its OLAP data, name, and type at a particular moment in time. Analytical tables are updated in transactions, and a separate snapshot is created for each update. You can restore analytical tables to any state provided that the OLAP data of the analytical table was not deleted using the expire_snapshot ETL operation.

The metadata of snapshots is stored in the pga_snapshot metadata table.

For snapshot usage examples, refer to Appendix E.

Storage

Physical location of Parquet files and shared directories. Postgres Pro AXE supports local, network, and S3 storages.

S3 (Simple Storage Service) storages are cloud storages for any type of data that can be accessed via API. The data is stored as objects inside buckets (containers) with unique IDs and the metadata, such as type, creation date and time, as well as access privileges. The main advantages of such storages are their scalability, flexibility, and accessibility from anywhere using the internet.

The metadata of storages is stored in the pga_storage metadata table.

For more information about working with storages, refer to Chapter 15.

Universal Resource Identifier (URI)

A connection string for the storage that contains information required to initialize the data storage layer. It consists of the connection prefix (i.e., 'file://', 's3://'), network address, port number, and path. In addition, the S3 bucket name, storage region, and protocol can be specified.

Appendix C. Benchmark Test Results

This section includes results of standard benchmark tests TPC-H, TPC-DS, and ClickBench for pgpro_metastore.

Test details:

Data size: 100 GB. It corresponds to the scale factor of 100 in the benchmark test configuration.

Server: 64 vCPU cores and 128 GB of RAM.

Limitations:

Postgres Pro configuration parameters did not impact benchmark test results.

The duckdb.max_memory configuration parameter was set to 32Gb.

The duckdb.threads configuration parameter was set to 8.

In addition to the query execution time, the system metrics described in the table below were measured.

Metric | Units of Measurement | Description
user time | Seconds | The number of seconds the CPU was used by a process running in user mode
sys time | Seconds | The number of seconds the CPU was used by a process running in core mode
max memory | MB | The maximum amount of memory occupied over process execution time
avg memory | MB | The average amount of memory occupied over process execution time
read IO | MB | The amount of data read from a storage over process execution time
write IO | MB | The amount of data written to a storage over process execution time

Preparation of the test data and tables:

The test data in the CSV format was generated.

For more information about used instruments and the overall procedure, refer to https://tpc.org.

CSV files were uploaded to heap tables.

Test data was exported to Parquet files, then analytical views were created for them.

When creating analytical views, column data types corresponding to column data types of Postgres Pro tables were specified. It is required for the correct functioning of analytical queries.

C.1. TPC-H Benchmark Test

C.1.1. Creating Analytical Tables For the TPC-H Benchmark Test

Execute the following query:

COPY nation
TO '/home/test/tpch_tables100/nation.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY region
TO '/home/test/tpch_tables100/region.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY part
TO '/home/test/tpch_tables100/part.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY supplier
TO '/home/test/tpch_tables100/supplier.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY partsupp
TO '/home/test/tpch_tables100/partsupp.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY customer
TO '/home/test/tpch_tables100/customer.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY orders
TO '/home/test/tpch_tables100/orders.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY lineitem
TO '/home/test/tpch_tables100/lineitem.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

CREATE VIEW nation_parquet AS SELECT
r['n_nationkey']::integer AS n_nationkey,
r['n_name']::text AS n_name,0
r['n_regionkey']::integer AS n_regionkey,
r['n_comment']::text AS n_comment FROM
read_parquet('/home/test/tpch_tables100/nation.parquet', binary_as_string => true) r;

CREATE VIEW region_parquet AS SELECT
r['r_regionkey']::integer AS r_regionkey,
r['r_name']::text AS r_name,
r['r_comment']::text AS r_comment FROM
read_parquet('/home/test/tpch_tables100/region.parquet', binary_as_string => true) r;

CREATE VIEW part_parquet AS SELECT
r['p_partkey']::integer AS p_partkey,
r['p_name']::text AS p_name,
r['p_mfgr']::text AS p_mfgr,
r['p_brand']::text AS p_brand,
r['p_type']::text AS p_type,
r['p_size']::integer AS p_size,
r['p_container']::text AS p_container,
r['p_retailprice']::decimal(15,2) AS p_retailprice,
r['p_comment']::text AS p_comment FROM
read_parquet('/home/test/tpch_tables100/part.parquet', binary_as_string => true) r;

CREATE VIEW supplier_parquet AS SELECT
r['s_suppkey']::integer AS s_suppkey,
r['s_name']::text AS s_name,
r['s_address']::text AS s_address,
r['s_nationkey']::integer AS s_nationkey,
r['s_phone']::text AS s_phone,
r['s_acctbal']::decimal(15,2) AS s_acctbal,
r['s_comment']::text AS s_comment FROM
read_parquet('/home/test/tpch_tables100/supplier.parquet', binary_as_string => true) r;

CREATE VIEW nation_parquet AS SELECT
r['n_nationkey']::integer AS n_nationkey,
r['n_name']::text AS n_name,
r['n_regionkey']::integer AS n_regionkey,
r['n_comment']::text AS n_comment FROM
read_parquet('/home/test/tpch_tables100/nation.parquet', binary_as_string => true) r;

CREATE VIEW region_parquet AS SELECT
r['r_regionkey']::integer AS r_regionkey,
r['r_name']::text AS r_name,
r['r_comment']::text AS r_comment FROM
read_parquet('/home/test/tpch_tables100/region.parquet', binary_as_string => true) r;

CREATE VIEW part_parquet AS SELECT
r['p_partkey']::integer AS p_partkey,
r['p_name']::text AS p_name,
r['p_mfgr']::text AS p_mfgr,
r['p_brand']::text AS p_brand,
r['p_type']::text AS p_type,
r['p_size']::integer AS p_size,
r['p_container']::text AS p_container,
r['p_retailprice']::decimal(15,2) AS p_retailprice,
r['p_comment']::text AS p_comment FROM
read_parquet('/home/test/tpch_tables100/part.parquet', binary_as_string => true) r;

CREATE VIEW supplier_parquet as select
r['s_suppkey']::integer as s_suppkey,
r['s_name']::text as s_name,
r['s_address']::text as s_address,
r['s_nationkey']::integer as s_nationkey,
r['s_phone']::text as s_phone,
r['s_acctbal']::decimal(15,2) as s_acctbal,
r['s_comment']::text as s_comment from
read_parquet('/home/test/tpch_tables100/supplier.parquet', binary_as_string => true) r;

CREATE VIEW lineitem_parquet AS SELECT
r['l_orderkey']::integer AS l_orderkey,
r['l_partkey']::integer AS l_partkey,
r['l_suppkey']::integer AS l_suppkey,
r['l_linenumber']::integer AS l_linenumber,
r['l_quantity']::decimal(15,2) AS l_quantity,
r['l_extendedprice']::decimal(15,2) AS l_extendedprice,
r['l_discount']::decimal(15,2) AS l_discount,
r['l_tax']::decimal(15,2) AS l_tax,
r['l_returnflag']::text AS l_returnflag,
r['l_linestatus']::text AS l_linestatus,
r['l_shipdate']::date AS l_shipdate,
r['l_commitdate']::date AS l_commitdate,
r['l_receiptdate']::date AS l_receiptdate,
r['l_shipinstruct']::text AS l_shipinstruct,
r['l_shipmode']::text AS l_shipmode,
r['l_comment']::text AS l_comment FRO
read_parquet('/home/test/tpch_tables100/lineitem.parquet', binary_as_string => true) r;

C.1.2. TPC-H Benchmark Test: Scale Factor 10

Table C.1. Test Data

Data | Total size
Analytical tables in the CSV format | 12.3 GB
Analytical tables in the Parquet format | 2.3 GB

Table C.2. Table Information

Analytical Table | Number of Rows
customer | 1 500 000
lineitem | 60 000 000
nation | 25
orders | 15 000 000
part | 2 000 000
partsupp | 8 000 000
region | 5
supplier | 100 000

Table C.3. Test Results

Query | Duration (s) | user time (s) | sys time (s) | max RSS (MB) | avg RSS (MB) | read IO (MB) | write IO (MB)
Q1 | 1.24 | 4.41 | 0.49 | 175.78 | 119.21 | 547.41 | 0.00
Q1 — second execution | 0.64 | 3.97 | 0.13 | 28.99 | 19.21 | 0.00 | 0.00
Q2 | 0.41 | 0.35 | 0.14 | 137.95 | 55.49 | 125.65 | 0.00
Q2 — second execution | 0.16 | 0.65 | 0.08 | 87.58 | 87.58 | 0.12 | 0.00
Q3 | 1.16 | 2.83 | 0.73 | 207.54 | 149.16 | 859.54 | 0.00
Q3 — second execution | 0.43 | 2.55 | 0.24 | 79.77 | 75.65 | 0.00 | 0.00
Q4 | 0.75 | 1.54 | 0.38 | 160.36 | 96.14 | 372.92 | 0.00
Q4 — second execution | 0.34 | 1.46 | 0.14 | 94.09 | 94.03 | 0.00 | 0.00
Q5 | 1.10 | 2.71 | 0.65 | 150.69 | 86.55 | 816.84 | 0.00
Q5 — second execution | 0.46 | 2.58 | 0.20 | 61.12 | 52.95 | 0.00 | 0.00
Q6 | 0.70 | 1.06 | 0.41 | 138.09 | 83.34 | 429.32 | 0.00
Q6 — second execution | 0.23 | 0.73 | 0.09 | 4.45 | 4.45 | 0.00 | 0.00
Q7 | 1.15 | 2.35 | 0.73 | 107.64 | 56.96 | 778.34 | 0.00
Q7 — second execution | 0.44 | 2.64 | 0.25 | 138.61 | 92.78 | 0.00 | 0.00
Q8 | 1.26 | 3.29 | 0.78 | 111.01 | 70.97 | 891.59 | 0.00
Q8 — second execution | 0.54 | 2.48 | 0.18 | 132.82 | 113.96 | 0.00 | 0.00
Q9 | 1.56 | 5.75 | 1.33 | 1187.36 | 844.31 | 998.59 | 0.00
Q9 — second execution | 0.96 | 5.41 | 0.84 | 1016.37 | 856.13 | 0.00 | 0.00
Q10 | 1.29 | 2.69 | 0.92 | 279.62 | 91.94 | 897.04 | 0.00
Q10 — second execution | 0.53 | 2.41 | 0.34 | 294.97 | 144.50 | 0.00 | 0.00
Q11 | 0.74 | 0.88 | 0.14 | 60.24 | 27.92 | 113.06 | 0.00
Q11 — second execution | 0.58 | 0.83 | 0.08 | 75.74 | 69.54 | 0.00 | 0.00
Q12 | 0.78 | 1.64 | 0.48 | 115.89 | 66.10 | 554.29 | 0.00
Q12 — second execution | 0.30 | 1.61 | 0.14 | 58.46 | 53.79 | 0.00 | 0.00
Q13 | 1.05 | 5.64 | 0.57 | 566.08 | 323.92 | 384.95 | 0.00
Q13 — second execution | 0.81 | 5.08 | 0.37 | 424.62 | 304.15 | 0.00 | 0.00
Q14 | 1.21 | 1.77 | 0.85 | 144.30 | 91.96 | 982.34 | 0.00
Q14 — second execution | 0.30 | 1.65 | 0.22 | 169.76 | 169.08 | 0.00 | 0.00
Q15 | 1.14 | 2.21 | 0.68 | 116.00 | 75.72 | 681.02 | 0.00
Q15 — second execution | 0.49 | 2.43 | 0.27 | 130.55 | 108.68 | 0.06 | 0.00
Q16 | 0.35 | 0.76 | 0.20 | 226.43 | 99.28 | 105.09 | 0.00
Q16 — second execution | 0.22 | 0.68 | 0.16 | 220.67 | 220.67 | 0.00 | 0.00
Q17 | 1.19 | 2.06 | 0.58 | 45.12 | 3.68 | 928.13 | 0.00
Q17 — second execution | 0.38 | 1.46 | 0.14 | 25.31 | 12.52 | 0.00 | 0.00
Q18 | 0.99 | 3.22 | 0.75 | 854.52 | 449.22 | 332.49 | 0.00
Q18 — second execution | 0.65 | 3.20 | 0.54 | 817.68 | 559.04 | 0.00 | 0.00
Q19 | 1.35 | 5.36 | 0.78 | 248.18 | 147.08 | 1047.49 | 0.00
Q19 — second execution | 0.77 | 4.96 | 0.25 | 113.11 | 111.55 | 0.00 | 0.00
Q20 | 1.08 | 1.53 | 0.73 | 94.06 | 33.74 | 830.65 | 0.00
Q20 — second execution | 0.29 | 1.48 | 0.18 | 82.06 | 78.86 | 0.00 | 0.00
Q21 | 1.72 | 6.05 | 0.82 | 412.80 | 283.63 | 742.20 | 0.00
Q21 — second execution | 1.14 | 6.59 | 0.45 | 425.94 | 322.94 | 0.00 | 0.00
Q22 | 0.36 | 0.68 | 0.17 | 144.75 | 63.07 | 140.62 | 0.00
Q22 — second execution | 0.15 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00

C.1.3. TPC-H Benchmark Test: Scale Factor 100

Table C.4. Test Data

Data | Total size
Analytical tables in the CSV format | 112.7 GB
Analytical tables in the Parquet format | 24.3 GB
Database | 128 GB

Table C.5. Table Information

Analytical Table | Number of Rows
customer | 15 000 000
lineitem | 600 000 000
nation | 25
orders | 150 000 000
part | 20 000 000
partsupp | 80 000 000
region | 5
supplier | 1 000 000

Table C.6. Test Results

Query | Duration (s) | user time (s) | sys time (s) | max RSS (MB) | avg RSS (MB) | read IO (MB) | write IO (MB)
Q1 | 12.92 | 72.80 | 4.05 | 492.73 | 311.95 | 6358.24 | 0.00
Q1 — second execution | 9.46 | 70.33 | 1.16 | 409.30 | 273.64 | 0.00 | 0.00
Q2 | 2.12 | 6.27 | 0.96 | 491.55 | 361.52 | 1321.13 | 0.00
Q2 — second execution | 1.03 | 6.48 | 0.40 | 354.27 | 302.37 | 0.00 | 0.00
Q3 | 11.38 | 33.43 | 5.69 | 934.96 | 765.60 | 9683.71 | 0.00
Q3 — second execution | 4.58 | 31.15 | 1.31 | 875.82 | 686.64 | 0.00 | 0.00
Q4 | 8.10 | 36.50 | 5.93 | 4775.98 | 3691.67 | 4396.98 | 0.00
Q4 — second execution | 2.77 | 17.54 | 0.86 | 839.92 | 641.36 | 0.00 | 0.00
Q5 | 10.74 | 40.22 | 5.07 | 656.58 | 521.63 | 9477.89 | 0.00
Q5 — second execution | 5.38 | 37.76 | 1.17 | 645.07 | 531.99 | 0.00 | 0.00
Q6 | 7.02 | 12.20 | 3.64 | 265.81 | 207.99 | 6284.00 | 0.00
Q6 — second execution | 1.89 | 10.53 | 0.76 | 263.79 | 182.32 | 0.00 | 0.00
Q7 | 11.26 | 31.34 | 5.93 | 1247.71 | 606.35 | 9093.37 | 0.00
Q7 — second execution | 4.28 | 27.75 | 1.63 | 1439.95 | 794.07 | 0.00 | 0.00
Q8 | 12.48 | 45.23 | 5.87 | 909.35 | 707.38 | 9528.32 | 0.00
Q8 — second execution | 6.08 | 42.93 | 1.57 | 784.45 | 639.46 | 0.00 | 0.00
Q9 | 16.47 | 81.05 | 12.55 | 12589.41 | 10355.32 | 11261.07 | 0.00
Q9 — second execution | 11.66 | 77.63 | 8.09 | 12392.88 | 10138.04 | 0.00 | 0.00
Q10 | 11.97 | 30.71 | 7.86 | 2207.39 | 680.45 | 10548.94 | 0.00
Q10 — second execution | 4.34 | 27.91 | 2.14 | 1841.71 | 635.12 | 0.00 | 0.00
Q11 | 1.78 | 6.22 | 0.84 | 209.13 | 110.15 | 978.71 | 0.00
Q11 — second execution | 1.04 | 5.98 | 0.37 | 201.26 | 128.80 | 0.00 | 0.00
Q12 | 6.79 | 17.32 | 3.98 | 326.39 | 179.29 | 5353.07 | 0.00
Q12 — second execution | 2.57 | 15.04 | 0.92 | 406.32 | 248.50 | 0.00 | 0.00
Q13 | 8.29 | 54.70 | 4.03 | 4087.91 | 2248.88 | 3647.88 | 0.00
Q13 — second execution | 7.36 | 55.07 | 2.86 | 3989.38 | 2200.67 | 0.00 | 0.00
Q14 | 12.22 | 16.23 | 8.04 | 1672.34 | 1471.48 | 11552.39 | 0.00
Q14 — second execution | 2.51 | 14.48 | 1.80 | 1805.81 | 1419.27 | 0.00 | 0.00
Q15 | 9.60 | 25.58 | 5.90 | 878.58 | 453.89 | 7232.63 | 0.02
Q15 — second execution | 4.11 | 23.70 | 2.20 | 1280.88 | 789.34 | 0.06 | 0.00
Q16 | 1.47 | 6.80 | 1.19 | 1471.02 | 803.93 | 798.05 | 0.00
Q16 — second execution | 1.05 | 6.62 | 0.83 | 1352.21 | 890.62 | 0.00 | 0.00
Q17 | 11.55 | 36.66 | 4.95 | 323.85 | 103.95 | 9784.29 | 0.00
Q17 — second execution | 5.38 | 35.37 | 1.19 | 347.43 | 274.43 | 0.00 | 0.00
Q18 | 10.20 | 50.57 | 7.40 | 7231.00 | 3504.42 | 3183.46 | 0.00
Q18 — second execution | 8.03 | 48.98 | 5.96 | 6903.64 | 3178.65 | 0.00 | 0.00
Q19 | 12.86 | 55.66 | 6.89 | 1300.25 | 845.86 | 11058.48 | 0.00
Q19 — second execution | 7.77 | 55.12 | 2.43 | 1625.37 | 1393.91 | 0.00 | 0.00
Q20 | 9.83 | 16.10 | 5.21 | 390.94 | 302.91 | 8856.13 | 0.00
Q20 — second execution | 2.41 | 14.04 | 1.03 | 374.55 | 260.89 | 0.00 | 0.00
Q21 | 14.97 | 66.87 | 8.54 | 4101.62 | 2792.17 | 7644.21 | 0.00
Q21 — second execution | 10.46 | 64.93 | 4.72 | 3951.81 | 2257.96 | 0.00 | 0.00
Q22 | 1.97 | 9.47 | 0.95 | 237.94 | 134.41 | 1340.10 | 0.00
Q22 — second execution | 1.33 | 9.05 | 0.34 | 243.36 | 181.38 | 0.00 | 0.00

C.2. TPC-DS Benchmark Test

C.2.1. Creating Analytical Tables For the TPC-DS Benchmark Test

Execute the following query:

COPY dbgen_version
TO '/home/test/tpcds_tables100/dbgen_version.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY customer_address
TO '/home/test/tpcds_tables100/customer_address.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY customer_demographics
TO '/home/test/tpcds_tables100/customer_demographics.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY date_dim
TO '/home/test/tpcds_tables100/date_dim.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY warehouse
TO '/home/test/tpcds_tables100/warehouse.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY ship_mode
TO '/home/test/tpcds_tables100/ship_mode.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY time_dim
TO '/home/test/tpcds_tables100/time_dim.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY reason
TO '/home/test/tpcds_tables100/reason.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY income_band
TO '/home/test/tpcds_tables100/income_band.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY item
TO '/home/test/tpcds_tables100/item.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY store
TO '/home/test/tpcds_tables100/store.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY call_center
TO '/home/test/tpcds_tables100/call_center.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY customer
TO '/home/test/tpcds_tables100/customer.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY web_site
TO '/home/test/tpcds_tables100/web_site.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY store_returns
TO '/home/test/tpcds_tables100/store_returns.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY household_demographics
TO '/home/test/tpcds_tables100/household_demographics.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY web_page
TO '/home/test/tpcds_tables100/web_page.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY promotion
TO '/home/test/tpcds_tables100/promotion.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY catalog_page
TO '/home/test/tpcds_tables100/catalog_page.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY inventory
TO '/home/test/tpcds_tables100/inventory.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY catalog_returns
TO '/home/test/tpcds_tables100/catalog_returns.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY web_returns
TO '/home/test/tpcds_tables100/web_returns.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY web_sales
TO '/home/test/tpcds_tables100/web_sales.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY catalog_sales
TO '/home/test/tpcds_tables100/catalog_sales.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY store_sales
TO '/home/test/tpcds_tables100/store_sales.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

CREATE VIEW dbgen_version_parquet AS SELECT
  r['dv_version']::text AS dv_version,
  r['dv_create_date']::date AS dv_create_date,
  r['dv_create_time']::time AS dv_create_time,
  r['dv_cmdline_args']::text AS dv_cmdline_args FROM
read_parquet('/home/test/tpcds_tables100/dbgen_version.parquet', binary_as_string => true) r;

CREATE VIEW customer_address_parquet AS SElECT
  r['ca_address_sk']::integer AS ca_address_sk,
  r['ca_address_id']::text AS ca_address_id,
  r['ca_street_number']::text AS ca_street_number,
  r['ca_street_name']::text AS ca_street_name,
  r['ca_street_type']::text AS ca_street_type,
  r['ca_suite_number']::text AS ca_suite_number,
  r['ca_city']::text AS ca_city,
  r['ca_county']::text AS ca_county,
  r['ca_state']::text AS ca_state,
  r['ca_zip']::text AS ca_zip,
  r['ca_country']::text AS ca_country,
  r['ca_gmt_offset']::decimal(5,2) AS ca_gmt_offset,
  r['ca_location_type']::text AS ca_location_type FROM
read_parquet('/home/test/tpcds_tables100/customer_address.parquet', binary_as_string => true) r;

CREATE VIEW customer_demographics_parquet AS SELECT
  r['cd_demo_sk']::integer AS cd_demo_skz
  r['cd_gender']::text AS cd_gender,
  r['cd_marital_status']::text AS cd_marital_status,
  r['cd_education_status']::text AS cd_education_status,
  r['cd_purchase_estimate']::integer AS cd_purchase_estimate,
  r['cd_credit_rating']::text AS cd_credit_rating,
  r['cd_dep_count']::integer AS cd_dep_count,
  r['cd_dep_employed_count']::integer AS cd_dep_employed_count,
  r['cd_dep_college_count']::integer AS cd_dep_college_count FROM
read_parquet('/home/test/tpcds_tables100/customer_demographics.parquet', binary_as_string => true) r;

CREATE VIEW date_dim_parquet AS SELECT
  r['d_date_sk']::integer AS d_date_sk,
  r['d_date_id']::text AS d_date_id,
  r['d_date']::date AS d_date,
  r['d_month_seq']::integer AS d_month_seq,
  r['d_week_seq']::integer AS d_week_seq,
  r['d_quarter_seq']::integer AS d_quarter_seq,
  r['d_year']::integer AS d_year,
  r['d_dow']::integer AS d_dow,
  r['d_moy']::integer AS d_moy,
  r['d_dom']::integer AS d_dom,
  r['d_qoy']::integer AS d_qoy,
  r['d_fy_year']::integer AS d_fy_year,
  r['d_fy_quarter_seq']::integer AS d_fy_quarter_seq,
  r['d_fy_week_seq']::integer AS d_fy_week_seq,
  r['d_day_name']::text AS d_day_name,
  r['d_quarter_name']::text AS d_quarter_name,
  r['d_holiday']::text AS d_holiday,
  r['d_weekend']::text AS d_weekend,
  r['d_following_holiday']::text AS d_following_holiday,
  r['d_first_dom']::integer AS d_first_dom,
  r['d_last_dom']::integer AS d_last_dom,
  r['d_same_day_ly']::integer AS d_same_day_ly,
  r['d_same_day_lq']::integer AS d_same_day_lq,
  r['d_current_day']::text AS d_current_day,
  r['d_current_week']::text AS d_current_week,
  r['d_current_month']::text AS d_current_month,
  r['d_current_quarter']::text AS d_current_quarter,
  r['d_current_year']::text AS d_current_year FROM
read_parquet('/home/test/tpcds_tables100/date_dim.parquet', binary_as_string => true) r;

CREATE VIEW warehouse_parquet AS SELECT
  r['w_warehouse_sk']::integer AS w_warehouse_sk,
  r['w_warehouse_id']::text AS w_warehouse_id,
  r['w_warehouse_name']::text AS w_warehouse_name,
  r['w_warehouse_sq_ft']::integer AS w_warehouse_sq_ft,
  r['w_street_number']::text AS w_street_number,
  r['w_street_name']::text AS w_street_name,
  r['w_street_type']::text AS w_street_type,
  r['w_suite_number']::text AS w_suite_number,
  r['w_city']::text AS w_city,
  r['w_county']::text AS w_county,
  r['w_state']::text AS w_state,
  r['w_zip']::text AS w_zip,
  r['w_country']::text AS w_country,
  r['w_gmt_offset']::decimal(5,2) AS w_gmt_offset FROM
read_parquet('/home/test/tpcds_tables100/warehouse.parquet', binary_as_string => true) r;

CREATE VIEW ship_mode_parquet AS SELECT
  r['sm_ship_mode_sk']::integer AS sm_ship_mode_sk,
  r['sm_ship_mode_id']::text AS sm_ship_mode_id,
  r['sm_type']::text AS sm_type,
  r['sm_code']::text AS sm_code,
  r['sm_carrier']::text AS sm_carrier,
  r['sm_contract']::text AS sm_contract FROM
read_parquet('/home/test/tpcds_tables100/ship_mode.parquet', binary_as_string => true) r;

CREATE VIEW time_dim_parquet AS SELECT
  r['t_time_sk']::integer AS t_time_sk,
  r['t_time_id']::text AS t_time_id,
  r['t_time']::integer AS t_time,
  r['t_hour']::integer AS t_hour,
  r['t_minute']::integer AS t_minute,
  r['t_second']::integer AS t_second,
  r['t_am_pm']::text AS t_am_pm,
  r['t_shift']::text AS t_shift,
  r['t_sub_shift']::text AS t_sub_shift,
  r['t_meal_time']::text AS t_meal_time FROM
read_parquet('/home/test/tpcds_tables100/time_dim.parquet', binary_as_string => true) r;

CREATE VIEW reason_parquet AS SELECT
  r['r_reason_sk']::integer AS r_reason_sk,
  r['r_reason_id']::text AS r_reason_id,
  r['r_reason_desc']::text AS r_reason_desc FROM
read_parquet('/home/test/tpcds_tables100/reason.parquet', binary_as_string => true) r;

CREATE VIEW income_band_parquet AS SELECT
  r['ib_income_band_sk']::integer AS ib_income_band_sk,
  r['ib_lower_bound']::integer AS ib_lower_bound,
  r['ib_upper_bound']::integer AS ib_upper_bound FROM
read_parquet('/home/test/tpcds_tables100/income_band.parquet', binary_as_string => true) r;

CREATE VIEW item_parquet AS SELECT
  r['i_item_sk']::integer AS i_item_sk,
  r['i_item_id']::text AS i_item_id,
  r['i_rec_start_date']::date AS i_rec_start_date,
  r['i_rec_end_date']::date AS i_rec_end_date,
  r['i_item_desc']::text AS i_item_desc,
  r['i_current_price']::decimal(7,2) AS i_current_price,
  r['i_wholesale_cost']::decimal(7,2) AS i_wholesale_cost,
  r['i_brand_id']::integer AS i_brand_id,
  r['i_brand']::text AS i_brand,
  r['i_class_id']::integer AS i_class_id,
  r['i_class']::text AS i_class,
  r['i_category_id']::integer AS i_category_id,
  r['i_category']::text AS i_category,
  r['i_manufact_id']::integer AS i_manufact_id,
  r['i_manufact']::text AS i_manufact,
  r['i_size']::text AS i_size,
  r['i_formulation']::text AS i_formulation,
  r['i_color']::text AS i_color,
  r['i_units']::text AS i_units,
  r['i_container']::text AS i_container,
  r['i_manager_id']::integer AS i_manager_id,
  r['i_product_name']::text AS i_product_name FROM
read_parquet('/home/test/tpcds_tables100/item.parquet', binary_as_string => true) r;

create view store_parquet AS SELECT
  r['s_store_sk']::integer AS s_store_sk,
  r['s_store_id']::text AS s_store_id,
  r['s_rec_start_date']::date AS s_rec_start_date,
  r['s_rec_end_date']::date AS s_rec_end_date,
  r['s_closed_date_sk']::integer AS s_closed_date_sk,
  r['s_store_name']::text AS s_store_name,
  r['s_number_employees']::integer AS s_number_employees,
  r['s_floor_space']::integer AS s_floor_space,
  r['s_hours']::text AS s_hours,
  r['s_manager']::text AS s_manager,
  r['s_market_id']::integer AS s_market_id,
  r['s_geography_class']::text AS s_geography_class,
  r['s_market_desc']::text AS s_market_desc,
  r['s_market_manager']::text AS s_market_manager,
  r['s_division_id']::integer AS s_division_id,
  r['s_division_name']::text AS s_division_name,
  r['s_company_id']::integer AS s_company_id,
  r['s_company_name']::text AS s_company_name,
  r['s_street_number']::text AS s_street_number,
  r['s_street_name']::text AS s_street_name,
  r['s_street_type']::text AS s_street_type,
  r['s_suite_number']::text AS s_suite_number,
  r['s_city']::text AS s_city,
  r['s_county']::text AS s_county,
  r['s_state']::text AS s_state,
  r['s_zip']::text AS s_zip,
  r['s_country']::text AS s_country,
  r['s_gmt_offset']::decimal(5,2) AS s_gmt_offset,
  r['s_tax_precentage']::decimal(5,2) AS s_tax_precentage FROM
read_parquet('/home/test/tpcds_tables100/store.parquet', binary_as_string => true) r;

CREATE VIEW call_center_parquet AS SELECT
  r['cc_call_center_sk']::integer AS cc_call_center_sk,
  r['cc_call_center_id']::text AS cc_call_center_id,
  r['cc_rec_start_date']::date AS cc_rec_start_date,
  r['cc_rec_end_date']::date AS cc_rec_end_date,
  r['cc_closed_date_sk']::integer AS cc_closed_date_sk,
  r['cc_open_date_sk']::integer AS cc_open_date_sk,
  r['cc_name']::text AS cc_name,
  r['cc_class']::text AS cc_class,
  r['cc_employees']::integer AS cc_employees,
  r['cc_sq_ft']::integer AS cc_sq_ft,
  r['cc_hours']::text AS cc_hours,
  r['cc_manager']::text AS cc_manager,
  r['cc_mkt_id']::integer AS cc_mkt_id,
  r['cc_mkt_class']::text AS cc_mkt_class,
  r['cc_mkt_desc']::text AS cc_mkt_desc,
  r['cc_market_manager']::text AS cc_market_manager,
  r['cc_division']::integer AS cc_division,
  r['cc_division_name']::text AS cc_division_name,
  r['cc_company']::integer AS cc_company,
  r['cc_company_name']::text AS cc_company_name,
  r['cc_street_number']::text AS cc_street_number,
  r['cc_street_name']::text AS cc_street_name,
  r['cc_street_type']::text AS cc_street_type,
  r['cc_suite_number']::text AS cc_suite_number,
  r['cc_city']::text AS cc_city,
  r['cc_county']::text AS cc_county,
  r['cc_state']::text AS cc_state,
  r['cc_zip']::text AS cc_zip,
  r['cc_country']::text AS cc_country,
  r['cc_gmt_offset']::decimal(5,2) AS cc_gmt_offset,
  r['cc_tax_percentage']::decimal(5,2) AS cc_tax_percentage FROM
read_parquet('/home/test/tpcds_tables100/call_center.parquet', binary_as_string => true) r;

CREATE VIEW customer_parquet AS SELECT
  r['c_customer_sk']::integer AS c_customer_sk,
  r['c_customer_id']::text AS c_customer_id,
  r['c_current_cdemo_sk']::integer AS c_current_cdemo_sk,
  r['c_current_hdemo_sk']::integer AS c_current_hdemo_sk,
  r['c_current_addr_sk']::integer AS c_current_addr_sk,
  r['c_first_shipto_date_sk']::integer AS c_first_shipto_date_sk,
  r['c_first_sales_date_sk']::integer AS c_first_sales_date_sk,
  r['c_salutation']::text AS c_salutation,
  r['c_first_name']::text AS c_first_name,
  r['c_last_name']::text AS c_last_name,
  r['c_preferred_cust_flag']::text AS c_preferred_cust_flag,
  r['c_birth_day']::integer AS c_birth_day,
  r['c_birth_month']::integer AS c_birth_month,
  r['c_birth_year']::integer AS c_birth_year,
  r['c_birth_country']::text AS c_birth_country,
  r['c_login']::text AS c_login,
  r['c_email_address']::text AS c_email_address,
  r['c_last_review_date_sk']::integer AS c_last_review_date_sk FROM
read_parquet('/home/test/tpcds_tables100/customer.parquet', binary_as_string => true) r;

CREATE VIEW web_site_parquet AS SELECT
  r['web_site_sk']::integer AS web_site_sk,
  r['web_site_id']::text AS web_site_id,
  r['web_rec_start_date']::date AS web_rec_start_date,
  r['web_rec_end_date']::date AS web_rec_end_date,
  r['web_name']::text AS web_name,
  r['web_open_date_sk']::integer AS web_open_date_sk,
  r['web_close_date_sk']::integer AS web_close_date_sk,
  r['web_class']::text AS web_class,
  r['web_manager']::text AS web_manager,
  r['web_mkt_id']::integer AS web_mkt_id,
  r['web_mkt_class']::text AS web_mkt_class,
  r['web_mkt_desc']::text AS web_mkt_desc,
  r['web_market_manager']::text AS web_market_manager,
  r['web_company_id']::integer AS web_company_id,
  r['web_company_name']::text AS web_company_name,
  r['web_street_number']::text AS web_street_number,
  r['web_street_name']::text AS web_street_name,
  r['web_street_type']::text AS web_street_type,
  r['web_suite_number']::text AS web_suite_number,
  r['web_city']::text AS web_city,
  r['web_county']::text AS web_county,
  r['web_state']::text AS web_state,
  r['web_zip']::text AS web_zip,
  r['web_country']::text AS web_country,
  r['web_gmt_offset']::decimal(5,2) AS web_gmt_offset,
  r['web_tax_percentage']::decimal(5,2) AS web_tax_percentage FROM
read_parquet('/home/test/tpcds_tables100/web_site.parquet', binary_as_string => true) r;

CREATE VIEW store_returns_parquet AS SELECT
  r['sr_returned_date_sk']::integer AS sr_returned_date_sk,
  r['sr_return_time_sk']::integer AS sr_return_time_sk,
  r['sr_item_sk']::integer AS sr_item_sk,
  r['sr_customer_sk']::integer AS sr_customer_sk,
  r['sr_cdemo_sk']::integer AS sr_cdemo_sk,
  r['sr_hdemo_sk']::integer AS sr_hdemo_sk,
  r['sr_addr_sk']::integer AS sr_addr_sk,
  r['sr_store_sk']::integer AS sr_store_sk,
  r['sr_reason_sk']::integer AS sr_reason_sk,
  r['sr_ticket_number']::integer AS sr_ticket_number,
  r['sr_return_quantity']::integer AS sr_return_quantity,
  r['sr_return_amt']::decimal(7,2) AS sr_return_amt,
  r['sr_return_tax']::decimal(7,2) AS sr_return_tax,
  r['sr_return_amt_inc_tax']::decimal(7,2) AS sr_return_amt_inc_tax,
  r['sr_fee']::decimal(7,2) AS sr_fee,
  r['sr_return_ship_cost']::decimal(7,2) AS sr_return_ship_cost,
  r['sr_refunded_cash']::decimal(7,2) AS sr_refunded_cash,
  r['sr_reversed_charge']::decimal(7,2) AS sr_reversed_charge,
  r['sr_store_credit']::decimal(7,2) AS sr_store_credit,
  r['sr_net_loss']::decimal(7,2) AS sr_net_loss FROM
read_parquet('/home/test/tpcds_tables100/store_returns.parquet', binary_as_string => true) r;

CREATE VIEW household_demographics_parquet AS SELECT
  r['hd_demo_sk']::integer AS hd_demo_sk,
  r['hd_income_band_sk']::integer AS hd_income_band_sk,
  r['hd_buy_potential']::text AS hd_buy_potential,
  r['hd_dep_count']::integer AS hd_dep_count,
  r['hd_vehicle_count']::integer AS hd_vehicle_count FROM
read_parquet('/home/test/tpcds_tables100/household_demographics.parquet', binary_as_string => true) r;

CREATE VIEW web_page_parquet AS SELECT
  r['wp_web_page_sk']::integer AS wp_web_page_sk,
  r['wp_web_page_id']::text AS wp_web_page_id,
  r['wp_rec_start_date']::date AS wp_rec_start_date,
  r['wp_rec_end_date']::date AS wp_rec_end_date,
  r['wp_creation_date_sk']::integer AS wp_creation_date_sk,
  r['wp_access_date_sk']::integer AS wp_access_date_sk,
  r['wp_autogen_flag']::text AS wp_autogen_flag,
  r['wp_customer_sk']::integer AS wp_customer_sk,
  r['wp_url']::text AS wp_url,
  r['wp_type']::text AS wp_type,
  r['wp_char_count']::integer AS wp_char_count,
  r['wp_link_count']::integer AS wp_link_count,
  r['wp_image_count']::integer AS wp_image_count,
  r['wp_max_ad_count']::integer AS wp_max_ad_count FROM
read_parquet('/home/test/tpcds_tables100/web_page.parquet', binary_as_string => true) r;

CREATE VIEW promotion_parquet AS SELECT
  r['p_promo_sk']::integer AS p_promo_sk,
  r['p_promo_id']::text AS p_promo_id,
  r['p_start_date_sk']::integer AS p_start_date_sk,
  r['p_end_date_sk']::integer AS p_end_date_sk,
  r['p_item_sk']::integer AS p_item_sk,
  r['p_cost']::decimal(15,2) AS p_cost,
  r['p_response_target']::integer as p_response_target,
  r['p_promo_name']::text AS p_promo_name,
  r['p_channel_dmail']::text AS p_channel_dmail,
  r['p_channel_email']::text AS p_channel_email,
  r['p_channel_catalog']::text AS p_channel_catalog,
  r['p_channel_tv']::text AS p_channel_tv,
  r['p_channel_radio']::text AS p_channel_radio,
  r['p_channel_press']::text AS p_channel_press,
  r['p_channel_event']::text AS p_channel_event,
  r['p_channel_demo']::text AS p_channel_demo,
  r['p_channel_details']::text AS p_channel_details,
  r['p_purpose']::text AS p_purpose,
  r['p_discount_active']::text AS p_discount_active FROM
read_parquet('/home/test/tpcds_tables100/promotion.parquet', binary_as_string => true) r;

CREATE VIEW catalog_page_parquet AS SELECT
  r['cp_catalog_page_sk']::integer AS cp_catalog_page_sk,
  r['cp_catalog_page_id']::text AS cp_catalog_page_id,
  r['cp_start_date_sk']::integer AS cp_start_date_sk,
  r['cp_end_date_sk']::integer AS cp_end_date_sk,
  r['cp_department']::text AS cp_department,
  r['cp_catalog_number']::integer AS cp_catalog_number,
  r['cp_catalog_page_number']::integer AS cp_catalog_page_number,
  r['cp_description']::text AS cp_description,
  r['cp_type']::text AS cp_type FROM
read_parquet('/home/test/tpcds_tables100/catalog_page.parquet', binary_as_string => true) r;

CREATE VIEW inventory_parquet AS SELECT
  r['inv_date_sk']::integer AS inv_date_sk,
  r['inv_item_sk']::integer AS inv_item_sk,
  r['inv_warehouse_sk']::integer AS inv_warehouse_sk,
  r['inv_quantity_on_hand']::integer AS inv_quantity_on_hand FROM
read_parquet('/home/test/tpcds_tables100/inventory.parquet', binary_as_string => true) r;

CREATE VIEW catalog_returns_parquet AS SELECT
  r['cr_returned_date_sk']::integer AS cr_returned_date_sk,
  r['cr_returned_time_sk']::integer AS cr_returned_time_sk,
  r['cr_item_sk']::integer AS cr_item_sk,
  r['cr_refunded_customer_sk']::integer AS cr_refunded_customer_sk,
  r['cr_refunded_cdemo_sk']::integer AS cr_refunded_cdemo_sk,
  r['cr_refunded_hdemo_sk']::integer AS cr_refunded_hdemo_sk,
  r['cr_refunded_addr_sk']::integer AS cr_refunded_addr_sk,
  r['cr_returning_customer_sk']::integer AS cr_returning_customer_sk,
  r['cr_returning_cdemo_sk']::integer AS cr_returning_cdemo_sk,
  r['cr_returning_hdemo_sk']::integer AS cr_returning_hdemo_sk,
  r['cr_returning_addr_sk']::integer AS cr_returning_addr_sk,
  r['cr_call_center_sk']::integer AS cr_call_center_sk,
  r['cr_catalog_page_sk']::integer AS cr_catalog_page_sk,
  r['cr_ship_mode_sk']::integer AS cr_ship_mode_sk,
  r['cr_warehouse_sk']::integer AS cr_warehouse_sk,
  r['cr_reason_sk']::integer AS cr_reason_sk,
  r['cr_order_number']::integer AS cr_order_number,
  r['cr_return_quantity']::integer AS cr_return_quantity,
  r['cr_return_amount']::decimal(7,2) AS cr_return_amount,
  r['cr_return_tax']::decimal(7,2) AS cr_return_tax,
  r['cr_return_amt_inc_tax']::decimal(7,2) AS cr_return_amt_inc_tax,
  r['cr_fee']::decimal(7,2) AS cr_fee,
  r['cr_return_ship_cost']::decimal(7,2) AS cr_return_ship_cost,
  r['cr_refunded_cash']::decimal(7,2) AS cr_refunded_cash,
  r['cr_reversed_charge']::decimal(7,2) AS cr_reversed_charge,
  r['cr_store_credit']::decimal(7,2) AS cr_store_credit,
  r['cr_net_loss']::decimal(7,2) AS cr_net_loss FROM
read_parquet('/home/test/tpcds_tables100/catalog_returns.parquet', binary_as_string => true) r;

CREATE VIEW web_returns_parquet AS SELECT
  r['wr_returned_date_sk']::integer AS wr_returned_date_sk,
  r['wr_returned_time_sk']::integer AS wr_returned_time_sk,
  r['wr_item_sk']::integer AS wr_item_sk,
  r['wr_refunded_customer_sk']::integer AS wr_refunded_customer_sk,
  r['wr_refunded_cdemo_sk']::integer AS wr_refunded_cdemo_sk,
  r['wr_refunded_hdemo_sk']::integer AS wr_refunded_hdemo_sk,
  r['wr_refunded_addr_sk']::integer AS wr_refunded_addr_sk,
  r['wr_returning_customer_sk']::integer AS wr_returning_customer_sk,
  r['wr_returning_cdemo_sk']::integer AS wr_returning_cdemo_sk,
  r['wr_returning_hdemo_sk']::integer AS wr_returning_hdemo_sk,
  r['wr_returning_addr_sk']::integer AS wr_returning_addr_sk,
  r['wr_web_page_sk']::integer AS wr_web_page_sk,
  r['wr_reason_sk']::integer AS wr_reason_sk,
  r['wr_order_number']::integer AS wr_order_number,
  r['wr_return_quantity']::integer AS wr_return_quantity,
  r['wr_return_amt']::decimal(7,2) AS wr_return_amt,
  r['wr_return_tax']::decimal(7,2) AS wr_return_tax,
  r['wr_return_amt_inc_tax']::decimal(7,2) AS wr_return_amt_inc_tax,
  r['wr_fee']::decimal(7,2) AS wr_fee,
  r['wr_return_ship_cost']::decimal(7,2) AS wr_return_ship_cost,
  r['wr_refunded_cash']::decimal(7,2) AS wr_refunded_cash,
  r['wr_reversed_charge']::decimal(7,2) AS wr_reversed_charge,
  r['wr_account_credit']::decimal(7,2) AS wr_account_credit,
  r['wr_net_loss']::decimal(7,2) AS wr_net_loss FROM
read_parquet('/home/test/tpcds_tables100/web_returns.parquet', binary_as_string => true) r;

CREATE VIEW web_sales_parquet AS SELECT
  r['ws_sold_date_sk']::integer AS ws_sold_date_sk,
  r['ws_sold_time_sk']::integer AS ws_sold_time_sk,
  r['ws_ship_date_sk']::integer AS ws_ship_date_sk,
  r['ws_item_sk']::integer AS ws_item_sk,
  r['ws_bill_customer_sk']::integer AS ws_bill_customer_sk,
  r['ws_bill_cdemo_sk']::integer AS ws_bill_cdemo_sk,
  r['ws_bill_hdemo_sk']::integer AS ws_bill_hdemo_sk,
  r['ws_bill_addr_sk']::integer AS ws_bill_addr_sk,
  r['ws_ship_customer_sk']::integer AS ws_ship_customer_sk,
  r['ws_ship_cdemo_sk']::integer AS ws_ship_cdemo_sk,
  r['ws_ship_hdemo_sk']::integer AS ws_ship_hdemo_sk,
  r['ws_ship_addr_sk']::integer AS ws_ship_addr_sk,
  r['ws_web_page_sk']::integer AS ws_web_page_sk,
  r['ws_web_site_sk']::integer AS ws_web_site_sk,
  r['ws_ship_mode_sk']::integer AS ws_ship_mode_sk,
  r['ws_warehouse_sk']::integer AS ws_warehouse_sk,
  r['ws_promo_sk']::integer AS ws_promo_sk,
  r['ws_order_number']::integer AS ws_order_number,
  r['ws_quantity']::integer AS ws_quantity,
  r['ws_wholesale_cost']::decimal(7,2) AS ws_wholesale_cost,
  r['ws_list_price']::decimal(7,2) AS ws_list_price,
  r['ws_sales_price']::decimal(7,2) AS ws_sales_price,
  r['ws_ext_discount_amt']::decimal(7,2) AS ws_ext_discount_amt,
  r['ws_ext_sales_price']::decimal(7,2) AS ws_ext_sales_price,
  r['ws_ext_wholesale_cost']::decimal(7,2) AS ws_ext_wholesale_cost,
  r['ws_ext_list_price']::decimal(7,2) AS ws_ext_list_price,
  r['ws_ext_tax']::decimal(7,2) AS ws_ext_tax,
  r['ws_coupon_amt']::decimal(7,2) AS ws_coupon_amt,
  r['ws_ext_ship_cost']::decimal(7,2) AS ws_ext_ship_cost,
  r['ws_net_paid']::decimal(7,2) AS ws_net_paid,
  r['ws_net_paid_inc_tax']::decimal(7,2) AS ws_net_paid_inc_tax,
  r['ws_net_paid_inc_ship']::decimal(7,2) AS ws_net_paid_inc_ship,
  r['ws_net_paid_inc_ship_tax']::decimal(7,2) AS ws_net_paid_inc_ship_tax,
  r['ws_net_profit']::decimal(7,2) AS ws_net_profit FROM
read_parquet('/home/test/tpcds_tables100/web_sales.parquet', binary_as_string => true) r;

CREATE VIEW catalog_sales_parquet AS SELECT
  r['cs_sold_date_sk']::integer AS cs_sold_date_sk,
  r['cs_sold_time_sk']::integer AS cs_sold_time_sk,
  r['cs_ship_date_sk']::integer AS cs_ship_date_sk,
  r['cs_bill_customer_sk']::integer AS cs_bill_customer_sk,
  r['cs_bill_cdemo_sk']::integer AS cs_bill_cdemo_sk,
  r['cs_bill_hdemo_sk']::integer AS cs_bill_hdemo_sk,
  r['cs_bill_addr_sk']::integer AS cs_bill_addr_sk,
  r['cs_ship_customer_sk']::integer AS cs_ship_customer_sk,
  r['cs_ship_cdemo_sk']::integer AS cs_ship_cdemo_sk,
  r['cs_ship_hdemo_sk']::integer AS cs_ship_hdemo_sk,
  r['cs_ship_addr_sk']::integer AS cs_ship_addr_sk,
  r['cs_call_center_sk']::integer AS cs_call_center_sk,
  r['cs_catalog_page_sk']::integer AS cs_catalog_page_sk,
  r['cs_ship_mode_sk']::integer AS cs_ship_mode_sk,
  r['cs_warehouse_sk']::integer AS cs_warehouse_sk,
  r['cs_item_sk']::integer AS cs_item_sk,
  r['cs_promo_sk']::integer AS cs_promo_sk,
  r['cs_order_number']::integer AS cs_order_number,
  r['cs_quantity']::integer AS cs_quantity,
  r['cs_wholesale_cost']::decimal(7,2) AS cs_wholesale_cost,
  r['cs_list_price']::decimal(7,2) AS cs_list_price,
  r['cs_sales_price']::decimal(7,2) AS cs_sales_price,
  r['cs_ext_discount_amt']::decimal(7,2) AS cs_ext_discount_amt,
  r['cs_ext_sales_price']::decimal(7,2) AS cs_ext_sales_price,
  r['cs_ext_wholesale_cost']::decimal(7,2) AS cs_ext_wholesale_cost,
  r['cs_ext_list_price']::decimal(7,2) AS cs_ext_list_price,
  r['cs_ext_tax']::decimal(7,2) AS cs_ext_tax,
  r['cs_coupon_amt']::decimal(7,2) AS cs_coupon_amt,
  r['cs_ext_ship_cost']::decimal(7,2) AS cs_ext_ship_cost,
  r['cs_net_paid']::decimal(7,2) AS cs_net_paid,
  r['cs_net_paid_inc_tax']::decimal(7,2) AS cs_net_paid_inc_tax,
  r['cs_net_paid_inc_ship']::decimal(7,2) AS cs_net_paid_inc_ship,
  r['cs_net_paid_inc_ship_tax']::decimal(7,2) AS cs_net_paid_inc_ship_tax,
  r['cs_net_profit']::decimal(7,2) AS cs_net_profit FROM
read_parquet('/home/test/tpcds_tables100/catalog_sales.parquet', binary_as_string => true) r;

CREATE VIEW store_sales_parquet AS SELECT
  r['ss_sold_date_sk']::integer AS ss_sold_date_sk,
  r['ss_sold_time_sk']::integer AS ss_sold_time_sk,
  r['ss_item_sk']::integer AS ss_item_sk,
  r['ss_customer_sk']::integer as ss_customer_sk,
  r['ss_cdemo_sk']::integer AS ss_cdemo_sk,
  r['ss_hdemo_sk']::integer AS ss_hdemo_sk,
  r['ss_addr_sk']::integer AS ss_addr_sk,
  r['ss_store_sk']::integer AS ss_store_sk,
  r['ss_promo_sk']::integer AS ss_promo_sk,
  r['ss_ticket_number']::integer AS ss_ticket_number,
  r['ss_quantity']::integer AS ss_quantity,
  r['ss_wholesale_cost']::decimal(7,2) AS ss_wholesale_cost,
  r['ss_list_price']::decimal(7,2) AS ss_list_price,
  r['ss_sales_price']::decimal(7,2) AS ss_sales_price,
  r['ss_ext_discount_amt']::decimal(7,2) AS ss_ext_discount_amt,
  r['ss_ext_sales_price']::decimal(7,2) AS ss_ext_sales_price,
  r['ss_ext_wholesale_cost']::decimal(7,2) AS ss_ext_wholesale_cost,
  r['ss_ext_list_price']::decimal(7,2) AS ss_ext_list_price,
  r['ss_ext_tax']::decimal(7,2) AS ss_ext_tax,
  r['ss_coupon_amt']::decimal(7,2) AS ss_coupon_amt,
  r['ss_net_paid']::decimal(7,2) AS ss_net_paid,
  r['ss_net_paid_inc_tax']::decimal(7,2) AS ss_net_paid_inc_tax,
  r['ss_net_profit']::decimal(7,2) AS ss_net_profit FROM
read_parquet('/home/test/tpcds_tables100/store_sales.parquet', binary_as_string => true) r;

C.2.2. TPC-DS Benchmark Test: Scale Factor 100

Table C.7. Test Data

Data | Total size
Analytical tables in the CSV format | 100.3 GB
Analytical tables in the Parquet format | 24.4 GB

Table C.8. Table Information

Analytical Table | Number of Rows
call_center | 30
catalog_page | 20 400
catalog_returns | 14 404 374
catalog_sales | 143 997 065
customer_address | 1 000 000
customer_demographics | 1 920 800
customer | 2 000 000
date_dim | 73 049
household_demographics | 7200
income_band | 20
inventory | 399 330 000
item | 204 000
promotion | 1000
reason | 55
ship_mode | 20
store | 402
store_returns | 28 795 080
store_sales | 287 997 024
time_dim | 86 400
warehouse | 15
web_page | 2040
web_returns | 7 197 670
web_sales | 72 001 237
web_site | 24

Table C.9. Test Results

Query | Duration (s) | user time (s) | sys time (s) | max RSS (MB) | avg RSS (MB) | read IO (MB) | write IO (MB)
Q1 | 1.17 | 3.22 | 0.93 | 1192.97 | 491.60 | 477.13 | 0.00
Q1 — second execution | 0.63 | 3.03 | 0.69 | 988.18 | 594.60 | 0.00 | 0.00
Q2 | 2.50 | 8.59 | 0.86 | 235.27 | 174.93 | 1288.71 | 0.00
Q2 — second execution | 1.43 | 8.75 | 0.20 | 36.79 | 18.57 | 0.00 | 0.00
Q3 | 3.37 | 6.17 | 1.58 | 217.61 | 156.81 | 2520.28 | 0.00
Q3 — second execution | 1.06 | 5.57 | 0.30 | 115.94 | 98.99 | 0.00 | 0.00
Q4 | 16.10 | 77.26 | 7.36 | 3724.82 | 2715.21 | 6804.64 | 0.00
Q4 — second execution | 12.44 | 75.30 | 4.32 | 3484.33 | 2438.37 | 0.00 | 0.00
Q5 | 6.53 | 14.17 | 3.97 | 262.91 | 86.14 | 4917.16 | 0.00
Q5 — second execution | 2.49 | 12.63 | 0.83 | 655.33 | 334.02 | 0.00 | 0.00
Q6 | 2.88 | 5.46 | 1.67 | 803.32 | 248.04 | 2049.75 | 0.00
Q6 — second execution | 1.12 | 5.22 | 0.54 | 670.04 | 256.43 | 0.00 | 0.00
Q7 | 5.08 | 14.50 | 3.06 | 273.12 | 219.21 | 4417.77 | 0.00
Q7 — second execution | 2.07 | 12.82 | 0.60 | 304.13 | 203.10 | 0.00 | 0.00
Q8 | 2.57 | 6.02 | 1.53 | 126.75 | 75.49 | 1832.75 | 0.00
Q8 — second execution | 1.07 | 5.23 | 0.33 | 172.51 | 125.57 | 0.00 | 0.00
Q9 | 11.98 | 45.17 | 4.10 | 1231.43 | 987.05 | 3097.12 | 0.00
Q9 — second execution | 9.59 | 44.64 | 1.95 | 1168.15 | 943.60 | 0.00 | 0.00
Q10 | 2.00 | 4.45 | 0.78 | 291.94 | 197.11 | 735.45 | 0.00
Q10 — second execution | 1.14 | 4.33 | 0.25 | 260.18 | 184.88 | 0.00 | 0.00
Q11 | 9.82 | 48.49 | 4.96 | 2943.27 | 2084.55 | 3595.08 | 0.00
Q11 — second execution | 7.52 | 46.24 | 2.91 | 2814.27 | 1953.61 | 0.00 | 0.00
Q12 | 1.12 | 1.24 | 0.71 | 114.03 | 46.17 | 782.98 | 0.00
Q12 — second execution | 0.33 | 1.06 | 0.15 | 81.71 | 65.32 | 0.00 | 0.00
Q13 | 7.19 | 25.40 | 4.26 | 337.76 | 170.22 | 6412.03 | 0.00
Q13 — second execution | 3.43 | 23.90 | 0.87 | 277.51 | 247.07 | 0.00 | 0.00
Q14 | 55.35 | 351.97 | 27.29 | 12440.79 | 6139.15 | 3974.71 | 0.00
Q14 — second execution | 52.91 | 350.84 | 25.29 | 12300.06 | 6103.95 | 0.00 | 0.00
Q15 | 0.60 | 0.44 | 0.22 | 273.75 | 98.49 | 59.92 | 0.00
Q15 — second execution | 0.47 | 0.99 | 0.21 | 278.39 | 155.70 | 0.00 | 0.00
Q16 | 0.82 | 0.68 | 0.21 | 138.67 | 37.96 | 133.39 | 0.00
Q16 — second execution | 0.55 | 0.36 | 0.07 | 130.62 | 88.34 | 0.00 | 0.00
Q17 | 3.77 | 10.79 | 2.32 | 1011.01 | 435.42 | 2540.09 | 0.00
Q17 — second execution | 1.97 | 9.47 | 0.94 | 938.59 | 603.75 | 0.00 | 0.00
Q18 | 2.72 | 7.33 | 2.07 | 849.32 | 520.70 | 1880.99 | 0.00
Q18 — second execution | 1.44 | 6.90 | 0.79 | 485.93 | 271.32 | 0.00 | 0.00
Q19 | 4.37 | 8.49 | 2.51 | 366.98 | 262.37 | 3507.33 | 0.00
Q19 — second execution | 1.40 | 7.46 | 0.45 | 305.40 | 241.06 | 0.00 | 0.00
Q20 | 0.51 | 0.48 | 0.14 | 168.94 | 67.48 | 57.51 | 0.00
Q20 — second execution | 0.34 | 0.41 | 0.09 | 103.89 | 63.66 | 0.00 | 0.00
Q21 | 0.38 | 0.49 | 0.15 | 94.69 | 28.35 | 71.18 | 0.00
Q21 — second execution | 0.22 | 0.31 | 0.09 | 48.75 | 48.75 | 0.00 | 0.00
Q22 | 5.12 | 35.25 | 2.51 | 3978.26 | 3339.97 | 288.99 | 0.00
Q22 — second execution | 4.84 | 34.30 | 2.40 | 3754.82 | 3294.72 | 0.00 | 0.00
Q23 | 34.78 | 211.18 | 29.75 | 14410.55 | 5756.93 | 3619.45 | 0.00
Q23 — second execution | 33.42 | 214.81 | 28.10 | 14177.65 | 7060.66 | 0.00 | 0.00
Q24 | 8.05 | 38.52 | 6.51 | 3716.41 | 2728.57 | 3818.02 | 0.00
Q24 — second execution | 5.98 | 37.81 | 4.04 | 3100.28 | 2448.52 | 0.00 | 0.00
Q25 | 1.91 | 9.35 | 0.90 | 593.10 | 330.63 | 0.13 | 0.00
Q25 — second execution | 1.89 | 9.57 | 0.98 | 752.11 | 451.65 | 0.00 | 0.00
Q26 | 0.95 | 1.95 | 0.46 | 50.44 | 17.93 | 464.31 | 0.00
Q26 — second execution | 0.52 | 1.48 | 0.17 | 88.94 | 60.80 | 0.00 | 0.00
Q27 | 5.23 | 36.54 | 1.91 | 257.18 | 208.34 | 0.00 | 0.00
Q27 — second execution | 5.22 | 36.46 | 2.00 | 255.22 | 223.97 | 0.00 | 0.00
Q28 | 9.51 | 49.79 | 3.08 | 569.21 | 488.75 | 2391.03 | 0.00
Q28 — second execution | 8.00 | 49.99 | 1.72 | 522.01 | 464.69 | 0.00 | 0.00
Q29 | 4.30 | 11.96 | 2.13 | 477.48 | 334.80 | 2970.54 | 0.00
Q29 — second execution | 2.04 | 10.66 | 0.60 | 397.14 | 304.42 | 0.00 | 0.00
Q30 | 0.60 | 1.38 | 0.53 | 650.11 | 298.65 | 260.07 | 0.00
Q30 — second execution | 0.34 | 1.06 | 0.36 | 523.42 | 360.41 | 0.00 | 0.00
Q31 | 3.60 | 10.84 | 2.15 | 344.71 | 245.75 | 2707.19 | 0.00
Q31 — second execution | 1.68 | 9.07 | 0.39 | 315.16 | 254.40 | 0.00 | 0.00
Q32 | 0.64 | 0.57 | 0.15 | 160.23 | 72.60 | 73.26 | 0.00
Q32 — second execution | 0.50 | 0.38 | 0.08 | 105.56 | 76.21 | 0.00 | 0.00
Q33 | 4.53 | 8.28 | 3.01 | 341.25 | 182.34 | 3831.89 | 0.00
Q33 — second execution | 1.59 | 7.33 | 0.51 | 301.92 | 226.27 | 0.13 | 0.00
Q34 | 2.57 | 9.14 | 1.66 | 267.28 | 189.25 | 1306.16 | 0.00
Q34 — second execution | 1.56 | 8.59 | 0.67 | 189.05 | 139.17 | 0.00 | 0.00
Q35 | 2.84 | 10.58 | 1.75 | 1329.99 | 524.17 | 798.14 | 0.00
Q35 — second execution | 2.03 | 9.89 | 1.02 | 1279.56 | 508.52 | 0.00 | 0.00
Q36 | 9.65 | 39.32 | 6.48 | 230.54 | 98.88 | 9484.43 | 0.00
Q36 — second execution | 5.25 | 36.71 | 1.64 | 287.10 | 243.82 | 0.00 | 0.00
Q37 | 1.09 | 1.16 | 0.50 | 230.54 | 140.18 | 597.99 | 0.00
Q37 — second execution | 0.48 | 0.98 | 0.15 | 72.25 | 23.13 | 0.00 | 0.00
Q38 | 3.25 | 14.29 | 1.49 | 944.50 | 622.75 | 796.22 | 0.00
Q38 — second execution | 2.45 | 13.89 | 0.89 | 931.42 | 562.25 | 0.00 | 0.00
Q39 | 1.25 | 4.34 | 0.70 | 834.70 | 528.92 | 69.11 | 0.00
Q39 — second execution | 1.09 | 4.19 | 0.68 | 630.05 | 481.47 | 0.00 | 0.00
Q40 | 0.79 | 1.61 | 0.56 | 753.05 | 322.24 | 252.40 | 0.00
Q40 — second execution | 0.53 | 1.29 | 0.43 | 642.39 | 306.34 | 0.00 | 0.00
Q41 | 0.05 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
Q41 — second execution | 0.05 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
Q42 | 3.38 | 5.36 | 1.83 | 173.89 | 59.68 | 2911.17 | 0.00
Q42 — second execution | 0.93 | 4.48 | 0.31 | 146.17 | 112.09 | 0.00 | 0.00
Q43 | 2.58 | 9.63 | 1.30 | 173.25 | 136.36 | 1634.61 | 0.00
Q43 — second execution | 1.47 | 9.06 | 0.24 | 118.13 | 94.52 | 0.00 | 0.00
Q44 | 5.46 | 16.37 | 3.45 | 376.72 | 266.74 | 2359.85 | 0.00
Q44 — second execution | 3.21 | 15.29 | 1.26 | 413.08 | 320.93 | 0.00 | 0.00
Q45 | 1.24 | 2.39 | 0.78 | 347.48 | 245.26 | 863.27 | 0.00
Q45 — second execution | 0.48 | 1.93 | 0.22 | 286.66 | 225.99 | 0.00 | 0.00
Q46 | 4.24 | 14.46 | 2.41 | 149.67 | 40.76 | 2965.36 | 0.00
Q46 — second execution | 2.11 | 13.71 | 0.56 | 368.88 | 190.46 | 0.00 | 0.00
Q47 | 4.85 | 25.55 | 2.31 | 947.38 | 691.41 | 2619.97 | 0.00
Q47 — second execution | 3.54 | 23.57 | 1.10 | 915.69 | 641.71 | 0.00 | 0.00
Q48 | 5.03 | 17.65 | 2.88 | 459.10 | 392.36 | 3708.38 | 0.00
Q48 — second execution | 2.52 | 17.06 | 0.60 | 362.76 | 323.18 | 0.00 | 0.00
Q49 | 6.56 | 12.66 | 3.76 | 407.96 | 309.14 | 5597.77 | 0.00
Q49 — second execution | 2.18 | 11.22 | 0.69 | 332.85 | 238.94 | 0.00 | 0.00
Q50 | 3.51 | 12.97 | 1.57 | 260.37 | 153.21 | 2347.37 | 0.00
Q50 — second execution | 1.88 | 11.82 | 0.39 | 193.53 | 156.92 | 0.00 | 0.00
Q51 | 9.38 | 51.42 | 5.17 | 3372.08 | 2109.36 | 3313.81 | 0.00
Q51 — second execution | 7.44 | 50.63 | 3.97 | 3197.07 | 2224.74 | 0.00 | 0.00
Q52 | 0.96 | 4.29 | 0.35 | 118.52 | 86.21 | 0.00 | 0.00
Q52 — second execution | 0.97 | 4.36 | 0.33 | 98.36 | 75.20 | 0.00 | 0.00
Q53 | 3.83 | 27.57 | 0.45 | 31.68 | 21.46 | 0.00 | 0.00
Q53 — second execution | 3.75 | 26.24 | 0.46 | 127.29 | 113.64 | 0.00 | 0.00
Q54 | 10.95 | 37.76 | 7.35 | 514.70 | 409.99 | 10135.38 | 0.00
Q54 — second execution | 5.15 | 34.27 | 1.74 | 400.75 | 319.33 | 0.00 | 0.00
Q55 | 3.49 | 5.16 | 1.95 | 216.37 | 173.05 | 2949.66 | 0.00
Q55 — second execution | 0.92 | 4.53 | 0.30 | 145.31 | 109.57 | 0.00 | 0.00
Q56 | 4.89 | 8.23 | 3.04 | 295.09 | 221.59 | 3765.99 | 0.00
Q56 — second execution | 1.62 | 7.17 | 0.54 | 323.50 | 234.02 | 0.13 | 0.00
Q57 | 1.35 | 5.90 | 0.55 | 450.29 | 242.16 | 338.17 | 0.00
Q57 — second execution | 1.10 | 5.50 | 0.32 | 318.67 | 203.66 | 0.00 | 0.00
Q58 | 4.68 | 6.37 | 2.57 | 290.60 | 193.40 | 3816.84 | 0.00
Q58 — second execution | 1.35 | 5.40 | 0.45 | 322.89 | 209.36 | 0.00 | 0.00
Q59 | 4.26 | 22.92 | 2.79 | 3261.81 | 1652.81 | 1638.08 | 0.00
Q59 — second execution | 3.24 | 21.50 | 1.92 | 3080.73 | 1556.97 | 0.00 | 0.00
Q60 | 4.76 | 8.76 | 2.73 | 409.08 | 322.64 | 3774.54 | 0.00
Q60 — second execution | 1.65 | 7.47 | 0.50 | 377.33 | 265.09 | 0.00 | 0.00
Q61 | 0.82 | 0.57 | 0.15 | 238.39 | 116.60 | 57.09 | 0.00
Q61 — second execution | 0.66 | 0.49 | 0.07 | 165.11 | 116.57 | 0.00 | 0.00
Q62 | 1.05 | 3.43 | 0.49 | 125.36 | 75.53 | 500.09 | 0.00
Q62 — second execution | 0.55 | 2.46 | 0.11 | 66.26 | 55.99 | 0.00 | 0.00
Q63 | 4.66 | 26.13 | 1.69 | 262.25 | 214.19 | 2620.00 | 0.00
Q63 — second execution | 3.67 | 26.30 | 0.41 | 111.27 | 88.72 | 0.00 | 0.00
Q64 | 8.48 | 33.17 | 4.94 | 1535.12 | 872.82 | 6340.82 | 0.00
Q64 — second execution | 4.82 | 30.85 | 1.84 | 1511.87 | 929.80 | 0.00 | 0.00
Q65 | 5.47 | 24.22 | 3.77 | 2605.05 | 1520.66 | 2627.54 | 0.00
Q65 — second execution | 3.85 | 23.73 | 2.22 | 2227.14 | 1299.75 | 0.00 | 0.00
Q66 | 0.54 | 0.31 | 0.09 | 80.12 | 34.42 | 32.08 | 0.00
Q66 — second execution | 0.41 | 0.25 | 0.04 | 64.41 | 56.79 | 0.00 | 0.00
Q67 | 32.80 | 184.39 | 52.09 | 27685.02 | 15027.58 | 9539.32 | 0.00
Q67 — second execution | 31.85 | 187.17 | 53.17 | 25786.45 | 14302.98 | 0.00 | 0.00
Q68 | 2.26 | 14.54 | 0.80 | 243.20 | 172.12 | 0.00 | 0.00
Q68 — second execution | 2.26 | 14.50 | 0.84 | 171.16 | 146.57 | 0.00 | 0.00
Q69 | 2.06 | 4.91 | 0.95 | 331.04 | 221.16 | 781.57 | 0.00
Q69 — second execution | 1.23 | 4.18 | 0.23 | 250.59 | 169.31 | 0.00 | 0.00
Q70 | 10.69 | 48.99 | 7.30 | 251.75 | 155.97 | 9527.83 | 0.00
Q70 — second execution | 6.62 | 45.26 | 2.33 | 274.30 | 223.94 | 0.00 | 0.00
Q71 | 5.41 | 8.94 | 3.24 | 293.62 | 155.80 | 4213.18 | 0.00
Q71 — second execution | 1.83 | 8.05 | 0.54 | 244.07 | 181.46 | 0.00 | 0.00
Q72 | 3.35 | 14.40 | 2.39 | 1738.82 | 899.55 | 1833.04 | 0.00
Q72 — second execution | 2.31 | 13.71 | 1.40 | 1675.54 | 946.35 | 0.00 | 0.00
Q73 | 2.56 | 9.00 | 1.64 | 165.74 | 73.43 | 1305.82 | 0.00
Q73 — second execution | 1.52 | 8.59 | 0.80 | 200.02 | 148.51 | 0.00 | 0.00
Q74 | 7.45 | 32.32 | 3.67 | 1555.93 | 992.62 | 2716.92 | 0.00
Q74 — second execution | 5.62 | 31.92 | 2.05 | 1637.58 | 1022.67 | 0.00 | 0.00
Q75 | 7.99 | 23.62 | 5.38 | 2366.00 | 895.90 | 5997.67 | 0.00
Q75 — second execution | 3.82 | 22.79 | 2.27 | 2518.67 | 1103.22 | 0.00 | 0.00
Q76 | 6.63 | 12.67 | 4.58 | 243.60 | 2.41 | 6114.49 | 0.00
Q76 — second execution | 2.05 | 10.82 | 0.56 | 260.00 | 209.38 | 0.00 | 0.00
Q77 | 5.64 | 10.18 | 3.86 | 305.27 | 229.59 | 4930.04 | 0.00
Q77 — second execution | 1.87 | 9.14 | 0.56 | 278.83 | 206.50 | 0.00 | 0.00
Q78 | 10.97 | 48.95 | 10.27 | 8747.98 | 4708.29 | 6069.71 | 0.00
Q78 — second execution | 7.94 | 47.75 | 7.26 | 8704.53 | 4716.97 | 0.00 | 0.00
Q79 | 4.65 | 18.15 | 3.10 | 160.60 | 26.10 | 2955.89 | 0.00
Q79 — second execution | 2.63 | 17.10 | 1.23 | 535.23 | 307.60 | 0.00 | 0.00
Q80 | 8.35 | 18.92 | 6.13 | 2152.33 | 1599.29 | 6825.18 | 0.00
Q80 — second execution | 3.17 | 16.97 | 2.00 | 1986.95 | 1368.29 | 0.00 | 0.00
Q81 | 0.54 | 1.21 | 0.56 | 704.92 | 336.28 | 219.25 | 0.00
Q81 — second execution | 0.34 | 1.30 | 0.48 | 603.72 | 463.50 | 0.00 | 0.00
Q82 | 1.91 | 2.89 | 1.05 | 222.29 | 136.18 | 1365.56 | 0.00
Q82 — second execution | 0.74 | 2.59 | 0.26 | 127.56 | 92.90 | 0.00 | 0.00
Q83 | 0.64 | 0.86 | 0.40 | 126.32 | 76.58 | 387.46 | 0.00
Q83 — second execution | 0.21 | 0.47 | 0.09 | 70.56 | 70.56 | 0.00 | 0.00
Q84 | 0.41 | 0.46 | 0.24 | 147.61 | 65.52 | 174.04 | 0.00
Q84 — second execution | 0.15 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
Q85 | 2.51 | 6.82 | 2.13 | 1582.14 | 878.51 | 1627.35 | 0.00
Q85 — second execution | 1.19 | 6.44 | 1.06 | 1448.66 | 987.85 | 0.00 | 0.00
Q86 | 3.80 | 11.81 | 2.62 | 212.07 | 71.60 | 3520.44 | 0.00
Q86 — second execution | 1.66 | 10.28 | 0.79 | 193.19 | 163.83 | 0.00 | 0.00
Q87 | 3.37 | 15.27 | 1.45 | 917.36 | 558.52 | 794.04 | 0.00
Q87 — second execution | 2.63 | 14.85 | 0.89 | 922.12 | 605.15 | 0.00 | 0.00
Q88 | 7.75 | 37.90 | 1.71 | 747.62 | 607.90 | 1138.95 | 0.00
Q88 — second execution | 6.83 | 37.41 | 0.94 | 635.72 | 525.87 | 0.00 | 0.00
Q89 | 4.41 | 23.04 | 1.78 | 228.59 | 173.88 | 2618.04 | 0.00
Q89 — second execution | 3.35 | 23.85 | 0.46 | 178.50 | 152.58 | 0.00 | 0.00
Q90 | 0.85 | 1.24 | 0.34 | 212.45 | 123.93 | 373.87 | 0.00
Q90 — second execution | 0.44 | 1.75 | 0.14 | 99.01 | 71.43 | 0.00 | 0.00
Q91 | 0.28 | 0.07 | 0.05 | 41.54 | 23.18 | 45.54 | 0.00
Q91 — second execution | 0.10 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00
Q92 | 1.31 | 2.22 | 0.81 | 200.43 | 140.33 | 830.58 | 0.00
Q92 — second execution | 0.50 | 1.65 | 0.17 | 75.73 | 46.71 | 0.00 | 0.00
Q93 | 1.04 | 2.55 | 1.06 | 1415.03 | 541.22 | 331.42 | 0.00
Q93 — second execution | 0.77 | 2.40 | 0.87 | 1334.89 | 672.78 | 0.00 | 0.00
Q94 | 0.55 | 1.63 | 0.21 | 106.06 | 75.45 | 0.00 | 0.00
Q94 — second execution | 0.57 | 2.47 | 0.21 | 140.13 | 100.85 | 0.00 | 0.00
Q95 | 7.49 | 40.62 | 6.17 | 3596.52 | 2449.48 | 1140.46 | 0.00
Q95 — second execution | 6.41 | 40.35 | 4.71 | 3644.67 | 2890.47 | 0.00 | 0.00
Q96 | 1.68 | 4.97 | 0.84 | 215.09 | 163.58 | 1090.45 | 0.00
Q96 — second execution | 0.88 | 4.66 | 0.17 | 112.72 | 95.50 | 0.00 | 0.00
Q97 | 3.97 | 16.53 | 2.97 | 2424.42 | 1272.05 | 2167.17 | 0.00
Q97 — second execution | 2.77 | 15.64 | 1.97 | 2599.21 | 1429.85 | 0.00 | 0.00
Q98 | 3.56 | 5.89 | 2.13 | 172.88 | 123.42 | 3016.77 | 0.00
Q98 — second execution | 1.28 | 5.35 | 0.43 | 309.03 | 205.25 | 0.00 | 0.00
Q99 | 0.97 | 3.81 | 0.19 | 115.52 | 77.30 | 169.06 | 0.00
Q99 — second execution | 0.75 | 3.98 | 0.09 | 70.03 | 58.50 | 0.00 | 0.00

C.3. ClickBench Benchmark Test

C.3.1. Creating Analytical Tables for the ClickBench Benchmark Test

Execute the following query:

COPY hits
TO '/home/test/clickbench/hits.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

CREATE VIEW hits_parquet AS SELECT
  r['watchid']::bigint AS watchid,
  r['javaenable']::smallint AS javaenable,
  r['title']::text AS title,
  r['goodevent']::smallint AS goodevent,
  r['eventtime']::timestamp AS eventtime,
  r['eventdate']::date AS eventdate,
  r['counterid']::integer AS counterid,
  r['clientip']::integer AS clientip,
  r['regionid']::integer AS regionid,
  r['userid']::bigint AS userid,
  r['counterclass']::smallint AS counterclass,
  r['os']::smallint AS os,
  r['useragent']::smallint AS useragent,
  r['url']::text AS url,
  r['referer']::text AS referer,
  r['isrefresh']::smallint AS isrefresh,
  r['referercategoryid']::smallint AS referercategoryid,
  r['refererregionid']::integer AS refererregionid,
  r['urlcategoryid']::smallint AS urlcategoryid,
  r['urlregionid']::integer AS urlregionid,
  r['resolutionwidth']::smallint AS resolutionwidth,
  r['resolutionheight']::smallint AS resolutionheight,
  r['resolutiondepth']::smallint AS resolutiondepth,
  r['flashmajor']::smallint AS flashmajor,
  r['flashminor']::smallint AS flashminor,
  r['flashminor2']::text AS flashminor2,
  r['netmajor']::smallint AS netmajor,
  r['netminor']::smallint AS netminor,
  r['useragentmajor']::smallint AS useragentmajor,
  r['useragentminor']::text AS useragentminor,
  r['cookieenable']::smallint AS cookieenable,
  r['javascriptenable']::smallint AS javascriptenable,
  r['ismobile']::smallint AS ismobile,
  r['mobilephone']::smallint AS mobilephone,
  r['mobilephonemodel']::text AS mobilephonemodel,
  r['params']::text AS params,
  r['ipnetworkid']::integer AS ipnetworkid,
  r['traficsourceid']::smallint AS traficsourceid,
  r['searchengineid']::smallint AS searchengineid,
  r['searchphrase']::text AS searchphrase,
  r['advengineid']::smallint AS advengineid,
  r['isartifical']::smallint AS isartifical,
  r['windowclientwidth']::smallint AS windowclientwidth,
  r['windowclientheight']::smallint AS windowclientheight,
  r['clienttimezone']::smallint AS clienttimezone,
  r['clienteventtime']::timestamp AS clienteventtime,
  r['silverlightversion1']::smallint AS silverlightversion1,
  r['silverlightversion2']::smallint AS silverlightversion2,
  r['silverlightversion3']::integer AS silverlightversion3,
  r['silverlightversion4']::smallint AS silverlightversion4,
  r['pagecharset']::text AS pagecharset,
  r['codeversion']::integer AS codeversion,
  r['islink']::smallint AS islink,
  r['isdownload']::smallint AS isdownload,
  r['isnotbounce']::smallint AS isnotbounce,
  r['funiqid']::bigint AS funiqid,
  r['originalurl']::text AS originalurl,
  r['hid']::integer AS hid,
  r['isoldcounter']::smallint AS isoldcounter,
  r['isevent']::smallint AS isevent,
  r['isparameter']::smallint AS isparameter,
  r['dontcounthits']::smallint AS dontcounthits,
  r['withhash']::smallint AS withhash,
  r['hitcolor']::text AS hitcolor,
  r['localeventtime']::timestamp AS localeventtime,
  r['age']::smallint AS age,
  r['sex']::smallint AS sex,
  r['income']::smallint AS income,
  r['interests']::smallint AS interests,
  r['robotness']::smallint AS robotness,
  r['remoteip']::integer AS remoteip,
  r['windowname']::integer AS windowname,
  r['openername']::integer AS openername,
  r['historylength']::smallint AS historylength,
  r['browserlanguage']::text AS browserlanguage,
  r['browsercountry']::text AS browsercountry,
  r['socialnetwork']::text AS socialnetwork,
  r['socialaction']::text AS socialaction,
  r['httperror']::smallint AS httperror,
  r['sendtiming']::integer AS sendtiming,
  r['dnstiming']::integer AS dnstiming,
  r['connecttiming']::integer AS connecttiming,
  r['responsestarttiming']::integer AS responsestarttiming,
  r['responseendtiming']::integer AS responseendtiming,
  r['fetchtiming']::integer AS fetchtiming,
  r['socialsourcenetworkid']::smallint AS socialsourcenetworkid,
  r['socialsourcepage']::text AS socialsourcepage,
  r['paramprice']::bigint AS paramprice,
  r['paramorderid']::text AS paramorderid,
  r['paramcurrency']::text AS paramcurrency,
  r['paramcurrencyid']::smallint AS paramcurrencyid,
  r['openstatservicename']::text AS openstatservicename,
  r['openstatcampaignid']::text AS openstatcampaignid,
  r['openstatadid']::text AS openstatadid,
  r['openstatsourceid']::text AS openstatsourceid,
  r['utmsource']::text AS utmsource,
  r['utmmedium']::text AS utmmedium,
  r['utmcampaign']::text AS utmcampaign,
  r['utmcontent']::text AS utmcontent,
  r['utmterm']::text AS utmterm,
  r['fromtag']::text AS fromtag,
  r['hasgclid']::smallint AS hasgclid,
  r['refererhash']::bigint AS refererhash,
  r['urlhash']::bigint AS urlhash,
  r['clid']::integer AS clid from
read_parquet('/home/test/clickbench/hits.parquet', binary_as_string => true) r;

C.3.2. ClickBench Benchmark Test: Scale Factor 100

Table C.10. Test Data

Data | Total size
Analytical tables in the CSV format | 71.3 GB
Analytical tables in the Parquet format | 9.3 GB
Database | 66 GB

Table C.11. Table Information

Analytical Table | Number of Rows
hits | 99 997 497

Table C.12. Test Results

Query | Duration (s) | user time (s) | sys time (s) | max RSS (MB) | avg RSS (MB) | read IO (MB) | write IO (MB)
Q1 | 0.63 | 0.45 | 0.07 | 125.90 | 77.85 | 31.49 | 0.00
Q1 — second execution | 0.47 | 0.39 | 0.04 | 73.11 | 71.41 | 0.00 | 0.00
Q2 | 0.79 | 0.56 | 0.11 | 158.62 | 89.55 | 36.81 | 0.00
Q2 — second execution | 0.53 | 0.36 | 0.07 | 135.46 | 92.99 | 0.00 | 0.00
Q3 | 0.91 | 1.23 | 0.20 | 167.48 | 85.73 | 131.00 | 0.00
Q3 — second execution | 0.60 | 0.98 | 0.10 | 148.55 | 110.61 | 0.00 | 0.00
Q4 | 1.00 | 0.81 | 0.27 | 220.86 | 112.01 | 284.62 | 0.00
Q4 — second execution | 0.59 | 0.92 | 0.13 | 136.31 | 101.65 | 0.00 | 0.00
Q5 | 1.20 | 3.14 | 0.70 | 708.39 | 216.61 | 382.22 | 0.00
Q5 — second execution | 0.92 | 2.86 | 0.48 | 620.24 | 209.80 | 0.00 | 0.00
Q6 | 1.50 | 4.54 | 0.93 | 1163.14 | 486.90 | 479.83 | 0.00
Q6 — second execution | 1.16 | 4.55 | 0.75 | 954.81 | 361.70 | 0.00 | 0.00
Q7 | 0.77 | 0.77 | 0.14 | 144.44 | 88.64 | 48.86 | 0.00
Q7 — second execution | 0.57 | 0.35 | 0.08 | 137.39 | 90.48 | 0.00 | 0.00
Q8 | 0.70 | 0.43 | 0.08 | 155.98 | 84.44 | 33.83 | 0.00
Q8 — second execution | 0.57 | 0.66 | 0.08 | 143.61 | 102.05 | 0.00 | 0.00
Q9 | 1.43 | 3.44 | 0.74 | 747.97 | 321.12 | 489.98 | 0.00
Q9 — second execution | 1.02 | 3.87 | 0.49 | 605.55 | 290.13 | 0.00 | 0.00
Q10 | 1.68 | 4.81 | 0.91 | 707.90 | 302.82 | 587.95 | 0.00
Q10 — second execution | 1.20 | 4.67 | 0.54 | 712.61 | 317.29 | 0.00 | 0.00
Q11 | 1.11 | 1.32 | 0.43 | 124.87 | 33.22 | 342.63 | 0.00
Q11 — second execution | 0.68 | 0.82 | 0.13 | 163.49 | 98.62 | 0.00 | 0.00
Q12 | 1.14 | 1.55 | 0.46 | 317.14 | 144.63 | 348.34 | 0.00
Q12 — second execution | 0.69 | 0.88 | 0.15 | 92.59 | 22.58 | 0.00 | 0.00
Q13 | 1.46 | 4.85 | 1.03 | 1260.84 | 523.72 | 482.57 | 0.00
Q13 — second execution | 1.18 | 4.69 | 0.78 | 1129.12 | 486.61 | 0.00 | 0.00
Q14 | 2.04 | 7.44 | 1.51 | 1312.48 | 615.04 | 828.38 | 0.00
Q14 — second execution | 1.52 | 6.58 | 1.04 | 1338.21 | 582.66 | 0.00 | 0.00
Q15 | 1.62 | 5.31 | 1.05 | 1262.77 | 530.42 | 494.15 | 0.00
Q15 — second execution | 1.21 | 4.76 | 0.79 | 1167.94 | 527.60 | 0.00 | 0.00
Q16 | 1.26 | 2.82 | 0.78 | 745.76 | 198.17 | 384.04 | 0.00
Q16 — second execution | 0.95 | 2.69 | 0.54 | 770.43 | 240.03 | 0.00 | 0.00
Q17 | 2.14 | 7.74 | 1.90 | 2271.80 | 988.01 | 829.14 | 0.00
Q17 — second execution | 1.70 | 8.43 | 1.36 | 2292.72 | 1122.14 | 0.00 | 0.00
Q18 | 2.13 | 7.77 | 1.79 | 2191.19 | 940.75 | 831.77 | 0.00
Q18 — second execution | 1.71 | 8.45 | 1.44 | 2184.30 | 1042.97 | 0.00 | 0.00
Q19 | 3.17 | 14.12 | 3.17 | 3353.14 | 1553.31 | 1349.53 | 0.00
Q19 — second execution | 2.54 | 13.22 | 2.43 | 3812.80 | 1997.06 | 0.00 | 0.00
Q20 | 0.88 | 0.92 | 0.34 | 132.50 | 71.91 | 350.26 | 0.00
Q20 — second execution | 0.58 | 0.86 | 0.11 | 78.63 | 41.52 | 0.00 | 0.00
Q21 | 5.71 | 35.14 | 1.80 | 528.65 | 295.37 | 2665.79 | 0.00
Q21 — second execution | 4.96 | 34.58 | 0.84 | 765.54 | 507.69 | 0.00 | 0.00
Q22 | 4.12 | 17.66 | 2.49 | 1280.60 | 787.99 | 3064.32 | 0.00
Q22 — second execution | 2.77 | 16.86 | 1.14 | 1043.66 | 667.71 | 0.00 | 0.00
Q23 | 5.65 | 25.08 | 3.60 | 1052.83 | 760.07 | 4476.53 | 0.00
Q23 — second execution | 3.73 | 23.60 | 1.80 | 806.15 | 525.07 | 61.22 | 0.00
Q24 | 2.27 | 10.19 | 0.70 | 353.58 | 224.77 | 1040.53 | 0.00
Q24 — second execution | 1.90 | 10.03 | 0.32 | 253.96 | 177.31 | 0.00 | 0.00
Q25 | 0.79 | 0.88 | 0.23 | 200.04 | 83.95 | 151.16 | 0.00
Q25 — second execution | 0.65 | 0.63 | 0.12 | 176.91 | 100.47 | 0.43 | 0.00
Q26 | 1.17 | 2.49 | 0.38 | 220.63 | 108.22 | 388.89 | 0.00
Q26 — second execution | 0.86 | 2.96 | 0.17 | 220.31 | 128.92 | 0.00 | 0.00
Q27 | 0.89 | 0.89 | 0.22 | 174.16 | 53.80 | 164.66 | 0.00
Q27 — second execution | 0.65 | 0.93 | 0.11 | 140.21 | 91.47 | 0.00 | 0.00
Q28 | 3.66 | 13.22 | 2.22 | 1306.91 | 794.35 | 2635.39 | 0.00
Q28 — second execution | 2.24 | 12.38 | 1.06 | 1003.94 | 573.61 | 0.00 | 0.00
Q29 | 12.23 | 82.03 | 3.60 | 1164.64 | 638.60 | 2695.30 | 0.00
Q29 — second execution | 11.47 | 81.52 | 2.62 | 1625.54 | 1002.23 | 0.00 | 0.00
Q30 | 0.78 | 0.83 | 0.20 | 149.80 | 88.79 | 116.46 | 0.00
Q30 — second execution | 0.58 | 0.37 | 0.06 | 51.00 | 18.07 | 0.00 | 0.00
Q31 | 2.03 | 5.78 | 1.22 | 701.85 | 369.72 | 1021.30 | 0.00
Q31 — second execution | 1.26 | 4.69 | 0.53 | 603.29 | 274.75 | 0.00 | 0.00
Q32 | 3.33 | 5.87 | 2.15 | 757.51 | 366.02 | 2516.53 | 0.00
Q32 — second execution | 1.25 | 4.67 | 0.79 | 895.50 | 376.31 | 0.00 | 0.00
Q33 | 4.08 | 12.12 | 4.79 | 5902.10 | 3058.48 | 2069.20 | 0.00
Q33 — second execution | 2.58 | 12.02 | 4.27 | 5291.00 | 2890.68 | 0.00 | 0.00
Q34 | 4.17 | 17.30 | 5.64 | 5521.45 | 2674.25 | 2648.54 | 0.00
Q34 — second execution | 3.19 | 16.57 | 4.18 | 5923.27 | 2989.65 | 0.00 | 0.00
Q35 | 4.36 | 17.58 | 6.10 | 5529.96 | 2811.14 | 2648.79 | 0.00
Q35 — second execution | 3.30 | 16.83 | 4.73 | 5503.18 | 2698.39 | 0.00 | 0.00
Q36 | 1.15 | 3.17 | 0.79 | 352.95 | 99.13 | 221.50 | 0.00
Q36 — second execution | 1.07 | 3.51 | 0.67 | 838.83 | 285.11 | 0.00 | 0.00
Q37 | 0.90 | 0.98 | 0.52 | 353.64 | 116.84 | 208.02 | 0.00
Q37 — second execution | 0.64 | 0.65 | 0.26 | 186.30 | 63.08 | 0.00 | 0.00
Q38 | 0.89 | 0.91 | 0.41 | 118.58 | 6.67 | 154.77 | 0.00
Q38 — second execution | 0.61 | 0.71 | 0.23 | 119.66 | 2.78 | 0.00 | 0.00
Q39 | 0.87 | 0.65 | 0.25 | 265.94 | 42.13 | 103.84 | 0.00
Q39 — second execution | 0.62 | 0.66 | 0.23 | 245.41 | 88.15 | 0.02 | 0.00
Q40 | 1.01 | 1.04 | 0.56 | 394.09 | 110.50 | 210.13 | 0.00
Q40 — second execution | 0.74 | 1.33 | 0.61 | 793.19 | 283.12 | 0.00 | 0.00
Q41 | 0.76 | 0.59 | 0.21 | 203.92 | 89.32 | 107.98 | 0.00
Q41 — second execution | 0.57 | 0.36 | 0.07 | 117.24 | 81.53 | 0.00 | 0.00
Q42 | 0.82 | 0.58 | 0.18 | 136.13 | 61.56 | 72.99 | 0.00
Q42 — second execution | 0.58 | 0.60 | 0.12 | 99.95 | 55.43 | 0.00 | 0.00
Q43 | 0.88 | 0.64 | 0.18 | 243.94 | 110.67 | 80.21 | 0.00
Q43 — second execution | 0.63 | 0.47 | 0.08 | 135.68 | 97.21 | 0.00 | 0.00

Appendix D. S3 Storage Connection Scenario

For more information about storages, refer to Chapter 15.

Before performing this scenario:

Install Postgres Pro AXE.

Configure the pgpro_metastore catalog.

To connect to an S3 storage:

Set duckdb.unsafe_allow_execution_inside_functions and duckdb.convert_unsupported_numeric_to_double to true:

ALTER SYSTEM SET duckdb.unsafe_allow_execution_inside_functions TO true;
SET duckdb.convert_unsupported_numeric_to_double = true;

Reload the Postgres Pro AXE server:

SELECT pg_reload_conf();

Create a simple secret using duckdb.create_simple_secret.

Example D.1. 

SELECT duckdb.create_simple_secret(
      type          := 'S3',
      key_id        := 'access_key_ID_example',
      secret        := 'simple_secret_example',
      region        := '',
      url_style     := 'path',
      endpoint      := 'hostname:port',
      use_ssl       := 'false');

Create an S3 storage.

Example D.2. 

  SELECT metastore.add_storage('s3_storage_example', 's3://demo/s3_storage_example/','s3://demo/s3_storage_example/tmp/');

Ensure that the S3 storage is configured correctly:

Create a heap table using CREATE TABLE.

Example D.3. 

CREATE TABLE heap_table_example (id int4 NULL, "name" text NULL, price numeric(10,2) NULL, created_at timestamp NULL);

Insert rows into the heap table using INSERT.

Example D.4. 

INSERT INTO heap_table_example VALUES (1, 'Item 1', 150.99, '2025-08-09 00:01:01.75');
INSERT INTO heap_table_example VALUES (2, 'Item 2', 300.99, '2025-08-10 00:01:01.75');

Create an analytical table from the heap table.

Example D.5. 

SELECT metastore.add_table('analytical_table_example', 's3_storage_example', 'public.heap_table_example', '');

Copy the OLAP data to the analytical table from the heap table.

Example D.6. 

SELECT metastore.copy_table('analytical_table_example', $$select * from heap_table_example$$);

Create a Postgres Pro view from the analytical table.

Example D.7. 

SELECT metastore.create_view('analytical_table_example');

Ensure that the values that you inserted into the heap table can be retrieved from the analytical table.

Example D.8. 

SELECT * from analytical_table_example;

Appendix E. Examples of Snapshot Usage

Example E.1. 

Executing the add_storage ETL operation creates a new storage in the pga_storage metadata table. For this update, a new snapshot is created in the pga_snapshot metadata table. The created entries are associated using the begin_snapshot value of the pga_snapshot metadata table.

The current schema_version and next_catalog_id values of the pga_snapshot metadata table are increased by one since the metadata was updated, and new entries were created in metadata tables.

Example E.2. 

Executing the remove_storage ETL operation deactivates a storage from the pga_storage metadata table but does not delete it physically. For this update, a new snapshot is created in the pga_snapshot metadata table. The created entries are associated using the end_snapshot value of the pga_snapshot metadata table.

The current schema_version value of the pga_snapshot metadata table is increased by one, and the next_catalog_id value remains the same, since the metadata was updated, but no new entries were created in metadata tables.

Example E.3. 

Executing the add_table ETL operation creates a new analytical table in the pga_table metadata table and N new columns in the pga_column metadata table. For these updates, N+1 new snapshots are created in the pga_snapshot metadata table. The created entries are associated using the begin_snapshot value of the pga_snapshot metadata table.

The current schema_version value of the pga_snapshot metadata table is increased by one, and the next_catalog_id value is increased by N+1 since the metadata was updated, and new entries were created in metadata tables. The schema_version value is only increased by one because all updates were made in a single ETL operation.

The next_catalog_id+1 value is used as the table_id value for the new analytical table in the pga_table metadata table, whereas next_catalog_id+1+N values are used as column_id values for new columns in the pga_column metadata table.

Example E.4. 

Executing the add_files ETL operation creates one or multiple Parquet files in the pga_data_file metadata table. For this update, N new snapshots are created in the pga_snapshot metadata table. The created entries are associated using the begin_snapshot value of the pga_snapshot metadata table.

The current schema_version and next_catalog_id values of the pga_snapshot metadata table remain the same since the metadata was not updated, and no new entries were created in metadata tables.

The next_file_id value of the pga_snapshot metadata table is increased by one, and next_file_id+N values are used as data_file_id values for new Parquet files in the pga_data_file metadata table.

Appendix F. About OLTP and OLAP Workloads

Postgres Pro is traditionally used for transactional (OLTP) workloads. It determines architectural decisions on data layout and processing algorithms. OLTP workloads have the following characteristics:

operate on small volumes of data

perform read/write operations on most columns

require strict ACID guarantees

require short response time

With these characteristics, an optimal approach is to store and process the data in a row-based format.

Analytical (OLAP) workloads are significantly different:

process large volumes of data

perform read-only operations on a small subset of columns

do not require strict ACID guarantees

prioritize throughput over latency

Considering the difference, Postgres Pro AXE holds a significant advantage due to its architecture:

local, network, or S3 storage for better scalability and price-to-performance ratio

columnar layout on storage and in memory to work efficiently with individual columns and for higher level of compression

vectorized execution to process the data in batches

Postgres Pro AXE enhances the traditional Postgres Pro OLTP functionality by offering possibilities to operate with OLAP and HTAP workloads.

Appendix G. Data Types

G.1. Data Types in pgpro_axe

pgpro_axe can read many data types that exist in both Postgres Pro and DuckDB. The following data types are currently supported for use in queries:

integer data types (e.g., integer, bigint)

floating-point data types (e.g., real, double precision)

numeric

It can be internally converted to double precision. For more information, refer to the section called “Known Limitations”.

text/varchar/bpchar

bit-related data types, including both fixed- and varied-sized bit arrays

bytea/blob

timestamp/timestamptz/date/interval/timestamp_ns/timestamp_ms/timestamp_s

boolean

uuid

json/jsonb

domain

arrays for all of the data types above

For more information about limitations regarding multi-dimensional arrays, refer to the section called “Known Limitations”.

Known Limitations

Currently, pgpro_axe does not support all data types. You might run into the following issues:

enum data types are not supported.

The DuckDB decimal data type does not support the wide range of values that the Postgres Pro numeric data type does.

To avoid errors when converting between the two, numeric is converted to double precision internally if DuckDB does not support the required precision. This may cause precision loss of the values.

The DuckDB timestamp_ns data type gets truncated to microseconds when it is converted to the Postgres Pro timestamp data type, which loses precision in the output. Operations on a timestamp_ns value, such as sorting/grouping/comparing, use the full precision.

jsonb columns are converted to json columns when reading from DuckDB. This is because DuckDB does not have the jsonb data type.

Many Postgres Pro json and jsonb functions and operators are not implemented in DuckDB. Instead, you can use DuckDB json functions and operators.

For more information about these functions, refer to the official DuckDB documentation.

The DuckDB tinyint data type is converted to the char data type in Postgres Pro, because Postgres Pro does not have the tinyint data type and displays it as a hex code instead of a regular number.

Conversion between Postgres Pro multi-dimensional arrays and DuckDB nested LISTs in DuckDB can run into various problems due to lack of compatibility between databases.

Specifically, in Postgres Pro, different arrays in a column can have different numbers of dimensions, e.g., [1] and [[1], [2]] can both occur in the same column. In DuckDB, that is not allowed, and the amount of nesting must always be the same. On the other hand, in DuckDB different lists at the same nest level can contain different numbers of elements, e.g., [[1], [1, 2]], which is not allowed in Postgres Pro. So conversion between these data types is only possible when the arrays follow the subset.

Another possible problem that you can run into is that pgpro_axe uses the Postgres Pro column metadata to determine the number of dimensions that an array has. Since Postgres Pro does not warn you when you add arrays of different dimensions, it is possible that the number of dimensions in the column metadata does not match the actual number of dimensions. To avoid this, alter the column data type:

    -- this configures the column to be a 3-dimensional array of text --
    ALTER TABLE s ALTER COLUMN a SET DATA TYPE text[][][];

For domain, when INSERT is executed, the check regarding domain is conducted by Postgres Pro, rather than DuckDB. When SELECT is executed, and the data type of the queried field is domain, this field is converted to the corresponding base and handled by DuckDB.

G.2. Special Data Types in pgpro_axe

pgpro_axe introduces a number of special Postgres Pro data types. You must not create these data types explicitly, but they can be displayed in error messages from Postgres Pro.

duckdb.row

The duckdb.row data type is returned by certain functions, such as read_parquet, read_csv, and scan_iceberg. Depending on the arguments, these functions can return rows with different columns and data types. Currently, Postgres Pro does not fully support such functions, so a custom type is returned. To get columns out of these rows, use the “square bracket indexing” syntax, similarly to how you get the field:

Example G.1. 

      SELECT r['id'], r['name'] FROM read_parquet('file.parquet') r WHERE r['age'] > 21;

Using SELECT * results in the columns of this row being expanded, so your query result never has a column that has duckdb.row as its data type:

Example G.2. 

      SELECT * FROM read_parquet('file.parquet');

There are certain limitations associated with using a function that returns duckdb.row in a CTE or subquery. The main problem is that pgpro_axe cannot automatically assign aliases to the selected columns from the row.

While this query without a CTE or subquery returns the r[company] column as company:

Example G.3. 

      SELECT r['company']
      FROM duckdb.query($$ SELECT 'DuckDB Labs' company $$) r;
      --    company
      -- ─────────────
      --  DuckDB Labs

The same query in a CTE or subquery returns the column as r:

Example G.4. 

      WITH mycte AS (
      SELECT r['company']
      FROM duckdb.query($$ SELECT 'DuckDB Labs' company $$) r
      )
      SELECT * FROM mycte;
      --       r
      -- ─────────────
      --  DuckDB Labs

This is easy to work around by adding an explicit alias to the column in the CTE or subquery:

Example G.5. 

      WITH mycte AS (
      SELECT r['company'] AS company
      FROM duckdb.query($$ SELECT 'DuckDB Labs' company $$) r
      )
      SELECT * FROM mycte;
      --    company
      -- ─────────────
      --  DuckDB Labs

Another limitation is that if when using SELECT * inside the CTE or subquery, you want to reference a specific column outside the CTE or subquery, you still need to use the r['column_name'] syntax instead of column_name.

While this query works as expected:

Example G.6. 

      WITH mycte AS (
      SELECT *
      FROM duckdb.query($$ SELECT 'DuckDB Labs' company $$) r
      )
      SELECT * FROM mycte;
      --    company
      -- ─────────────
      --  DuckDB Labs

The following query outputs an error:

Example G.7. 

      WITH mycte AS (
      SELECT *
      FROM duckdb.query($$ SELECT 'DuckDB Labs' company $$) r
      )
      SELECT * FROM mycte WHERE company = 'DuckDB Labs';
      -- ERROR:  42703: column "company" does not exist
      -- LINE 5: SELECT * FROM mycte WHERE company = 'DuckDB Labs';
      --                                   ^
      -- HINT:  If you use DuckDB functions like read_parquet, you need to use the r['colname'] syntax to use columns. If you are already doing that, maybe you forgot to to give the function the r alias.

This is easy to work around by using the following r['column_name'] syntax:

Example G.8. 

      > WITH mycte AS (
      SELECT *
      FROM duckdb.query($$ SELECT 'DuckDB Labs' company $$) r
      )
      SELECT * FROM mycte WHERE r['company'] = 'DuckDB Labs';
      --    company
      -- ─────────────
      --  DuckDB Labs

duckdb.unresolved_type

The duckdb.unresolved_type data type is used to make Postgres Pro process an expression for which the data type is not known at query parse time. This is the data type of any of the columns extracted from a duckdb.row using the r['column_name'] syntax. Many operators and aggregates return duckdb.unresolved_type when one of the sides of the operator has the duckdb.unresolved_type data type, for example, r['age'] + 10.

The actual data type is specified once DuckDB executes a query. The query result never contains a column that has duckdb.unresolved_type as its data type.

You can receive errors indicating that functions or operators do not exist for the duckdb.unresolved_type data type:

Example G.9. 

      ERROR:  function some_func(duckdb.unresolved_type) does not exist
      LINE 6:  some_func(r['somecol']) as somecol

In such cases, a simple workaround is often to add an explicit cast to the data type that the function accepts, such as some_func(r['somecol']::text) as somecol.

duckdb.json

The duckdb.json data type is used for arguments to DuckDB JSON functions. This type exists so that these functions can take json, jsonb, and duckdb.unresolved_type values.

G.3. Data Types in pgpro_metastore

The table belows describes the data types supported by pgpro_metastore.

Data Type | Value
boolean | true or false.
int8 | Integer, from -128 to 127.
int16 | Integer, from -32768 to 32767.
int32 | Integer, from -2 147 483 648 to 2 147 483 647.
int64 | Integer, from -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807.
unit8 | Integer, from 0 to 255.
unit16 | Integer, from 0 to 65535.
unit32 | Integer, from 0 to 4 294 967 295.
unit64 | Integer, from 0 to 18 446 744 073 709 551 615.
float32 | Fraction, approximately from 1.17549435E-38 to 3.40282347E+38. Precision: 6–7 numbers after the comma.
float64 | Float, approximately from 4.9E-324 to 1.8E+308. Precision: 14–15 numbers after the comma.
decimal | Decimal, for example, decimal(5,2) is 123.45 or 999.99.
time | Time, for example, 13:00:00.
timez | Time with timezone, for example, 10:30:00+03.
date | Date, for example, 2023-05-13.
timestamp | Timestamp, for example, 2023-05-15 14:30:00.
timestamptz | Timestamp with timezone, for example, 2023-05-15 14:30:00+03.
timestamp_s | Second-precision timestamp, for example, 2023-05-15 14:30:00+03.
timestamp_ms | Millisecond-precison timestamp, for example, 2023-05-15 14:30:00.123.
timestamp_ns | Nanosecond-precision timestamp, for example, 2023-05-15 14:30:00.123456789.
interval | Time interval, for example, 1 day, 1 hour, 1 minute.
varchar | Varchar, for example, abcde, 12&^gh.
blob | Blob, for example, PDF, image, video, audio.
json | JSON, for example, {"brand": "Apple","model": "MacBook Pro","screen_size": 13.3,"ram": "16GB"}.
json | UUID, for example, a81bc81b-dead-4e5d-abff-90865d1e13b1.

G.4. Data Type Correspondence in Postgres Pro, DuckDB, and Parquet

Postgres Pro AXE operates with the data from different systems and objects. The table below explains the correspondence of data types for these systems and objects.

Postgres Pro DBMS | DuckDB | Parquet Physical | Parquet Logical | Parquet Converted
bigint | BIGINT | INT64 | - | INT_64
bigserial | BIGINT | INT64 | - | INT_64
boolean | BOOLEAN | BOOLEAN | - | -
bytea | BLOB | BYTE_ARRAY | - | -
character | VARCHAR | BYTE_ARRAY | - | UTF8
character varying | VARCHAR | BYTE_ARRAY | - | UTF8
date | DATE | INT32 | - | DATE
double precision | DOUBLE | DOUBLE | - | -
integer | INTEGER | INT32 | - | INT_32
interval | INTERVAL | FIXED_LEN_BYTE_ARRAY(12) | - | INTERVAL
json | JSON | BYTE_ARRAY | JsonType | JSON
jsonb | JSON | BYTE_ARRAY | JsonType | JSON
money | VARCHAR | BYTE_ARRAY | - | UTF8
numeric | DOUBLE | DOUBLE | - | -
numeric (1–9) | DECIMAL (1–9) | INT32 | Decimal (1–9) | DECIMAL
numeric (10–18) | DECIMAL (10–18) | INT64 | Decimal (10–18) | DECIMAL
numeric (19–38) | DECIMAL (19–38) | FIXED_LEN_BYTE_ARRAY(16) | Decimal (19–38) | DECIMAL
real | FLOAT | FLOAT | - | -
smallint | SMALLINT | INT32 | - | INT_16
smallserial | SMALLINT | INT32 | - | INT_16
serial | INTEGER | INT32 | - | INT_32
text | VARCHAR | BYTE_ARRAY | - | UTF8
time | TIME | INT64 | TimeType(MICROS) | TIME_MICROS
timetz | TIMETZ | INT64 | TimeType(MICROS) | TIME_MICROS
timestamp | TIMESTAMP | INT64 | TimestampType(MICROS) | TIMESTAMP_MICROS
timestamptz | TIMESTAMPTZ | INT64 | TimestampType(MICROS) | TIMESTAMP_MICROS
uuid | UUID | FIXED_LEN_BYTE_ARRAY(16) | UUIDType() | -

For more information about data type limitations, refer to the official DuckDB documentation.