pg_parquet

Copy from/to Parquet files in PostgreSQL!

pg_parquet is a PostgreSQL extension that allows you to read and write Parquet files, which are located in S3, Azure Blob Storage, Google Cloud Storage, http(s) endpoints or file system, from PostgreSQL via COPY TO/FROM commands. It depends on Apache Arrow project to read and write Parquet files and pgrx project to extend PostgreSQL's COPY command.

-- Copy a query result into Parquet in S3
COPY (SELECT * FROM table) TO 's3://mybucket/data.parquet' WITH (format 'parquet');

-- Load data from Parquet in S3
COPY table FROM 's3://mybucket/data.parquet' WITH (format 'parquet');

Quick Reference

• Installation From Source
• Usage
  • Copy FROM/TO Parquet files TO/FROM Postgres tables
  • Create Postgres tables from Parquet files
  • COPY FROM/TO Parquet stdin/stdout TO/FROM Postgres tables)
  • COPY FROM/TO Parquet program stream TO/FROM Postgres tables)
  • Inspect Parquet schema
  • Inspect Parquet metadata
  • Inspect Parquet column statistics
  • List and read Parquet files from uri pattern
  • Monitor COPY progress
• Object Store Support
• Copy Options
• Configuration
• Supported Types
  • Nested Types
• Postgres Support Matrix

Installation From Source

After installing Postgres, you need to set up rustup, cargo-pgrx to build the extension.

# clone repo
> git clone https://github.com/CrunchyData/pg_parquet.git
> cd pg_parquet

# install rustup
> curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

# set cargo-pgrx (should be the same as pgrx dep in Cargo.toml) and pg versions
> export CARGO_PGRX_VERSION=0.19.3
> export PG_MAJOR=19

# install cargo-pgrx
> cargo install --force --locked cargo-pgrx@"${CARGO_PGRX_VERSION}"

# configure pgrx
> cargo pgrx init --pg"${PG_MAJOR}" $(which pg_config)

# append the extension to shared_preload_libraries 
> echo "shared_preload_libraries = 'pg_parquet'" >> ~/.pgrx/data-"${PG_MAJOR}"/postgresql.conf

# initialize a data directory, build and install the extension (to the targets specified by configured pg_config), then connects to a session
> cargo pgrx run --features pg"${PG_MAJOR}"
# alternatively you can only build and install the extension (pass --release flag for production binary)
> cargo pgrx install --release --features pg"${PG_MAJOR}"

# create the extension in the database
psql> "CREATE EXTENSION pg_parquet;"

[!NOTE] pgrx installs the extension into the directories of the pg_config that it is configured with, which are owned by root for a packaged Postgres. cargo pgrx install can be told to use sudo for that, but cargo pgrx run has no such flag and instead needs those directories to be writable, as shown below. Alternatively, cargo pgrx init --pg"${PG_MAJOR}" download has pgrx build its own Postgres under ~/.pgrx, which belongs to you and needs no root permissions at all.

# either install the extension with sudo
> cargo pgrx install --release --sudo --features pg"${PG_MAJOR}"

# or make the installation directories writable once
> sudo chmod a+rwx $(pg_config --pkglibdir)         \
                   $(pg_config --pkglibdir)/bitcode \
                   $(pg_config --sharedir)/extension

# the files of a packaged pg_parquet are still owned by root after that, and they are
# overwritten in place rather than recreated, so remove them to be able to install over
> sudo rm -f $(pg_config --pkglibdir)/pg_parquet* \
             $(pg_config --sharedir)/extension/pg_parquet*

Usage

There are mainly 3 things that you can do with pg_parquet:

1. You can export Postgres tables/queries to Parquet files, stdin/stdout or a program's stream,
2. You can ingest data from Parquet files to Postgres tables with type coercion and schema inference,
3. You can inspect the schema and metadata of Parquet files.

COPY from/to Parquet files to/from Postgres tables

You can use PostgreSQL's COPY command to read and write from/to Parquet files. Below is an example of how to write a PostgreSQL table, with complex types, into a Parquet file and then to read the Parquet file content back into the same table.

-- create composite types
CREATE TYPE product_item AS (id INT, name TEXT, price float4);
CREATE TYPE product AS (id INT, name TEXT, items product_item[]);

-- create a table with complex types
CREATE TABLE product_example (
    id int,
    product product,
    products product[],
    created_at TIMESTAMP,
    updated_at TIMESTAMPTZ
);

-- insert some rows into the table
insert into product_example values (
    1,
    ROW(1, 'product 1', ARRAY[ROW(1, 'item 1', 1.0), ROW(2, 'item 2', 2.0), NULL]::product_item[])::product,
    ARRAY[ROW(1, NULL, NULL)::product, NULL],
    now(),
    '2022-05-01 12:00:00-04'
);

-- copy the table to a parquet file
COPY product_example TO '/tmp/product_example.parquet' (format 'parquet', compression 'gzip');

-- show table
SELECT * FROM product_example;

-- copy the parquet file to the table
COPY product_example FROM '/tmp/product_example.parquet';

-- show table
SELECT * FROM product_example;

Create Postgres tables from Parquet files

You can use CREATE TABLE () WITH (definition_from = <uri>) command to infer the columns of Postgres tables. You can even infer + populate the table via CREATE TABLE () WITH (load_from = <uri>).

-- create table with inferred columns and populated rows
CREATE TABLE product_inferred_example () WITH (load_from = '/tmp/product_example.parquet');

-- show table
SELECT * FROM product_inferred_example;

[!NOTE] If the inferred column is of composite type, a new type will be created named as parquet_structs.struct_<hash>. Hash is determined by field types and names of the composite type. The type is created by the user that runs the command, so that user needs CREATE privilege on the parquet_structs schema, which the extension grants to public. The generated types are not dropped together with the table. The parquet_structs schema belongs to the extension, so DROP EXTENSION pg_parquet reports the dependency while any table still has such a column, and DROP EXTENSION pg_parquet CASCADE drops those columns along with the types. Drop or alter the columns first.

[!NOTE] Postgres has no unsigned integers, so an unsigned column is inferred as the next wider type e.g. uint32 as bigint and uint64 as numeric(20,0). A 16 byte fixed size binary column is inferred as uuid, which is the type that COPY FROM reads it into. A map column requires the crunchy_map extension. The command errors out for a parquet type that has no Postgres counterpart e.g. Duration or Decimal256, in which case you can create the table with explicit column definitions and then COPY FROM the parquet file.

COPY from/to Parquet stdin/stdout to/from Postgres tables

You can use COPY command to read and write Parquet stream from/to standard input and output. Below is an example usage (you have to specify format = parquet):

psql -d pg_parquet -p 28817 -h localhost -c "create table product_example_reconstructed (like product_example);"
 CREATE TABLE

psql -d pg_parquet -p 28817 -h localhost -c "copy product_example to stdout (format parquet);" | psql -d pg_parquet -p 28817 -h localhost -c "copy product_example_reconstructed from stdin (format parquet);"
 COPY 2

COPY from/to Parquet program stream to/from Postgres tables

You can use COPY command to read and write Parquet stream from/to a program's input and output. Below is an example usage (you have to specify format = parquet):

psql -d pg_parquet -p 28817 -h localhost -c "copy product_example_reconstructed to program 'cat > /tmp/test.parquet' (format parquet);"
 COPY 2

psql -d pg_parquet -p 28817 -h localhost -c "copy product_example_reconstructed from program 'cat /tmp/test.parquet' (format parquet);"
 COPY 2

Inspect Parquet schema

You can call SELECT * FROM parquet.schema(<uri>) to discover the schema of the Parquet file at given uri.

SELECT * FROM parquet.schema('/tmp/product_example.parquet') LIMIT 10;
             uri              |     name     | type_name  | type_length | repetition_type | num_children | converted_type | scale | precision | field_id | logical_type 
------------------------------+--------------+------------+-------------+-----------------+--------------+----------------+-------+-----------+----------+--------------
 /tmp/product_example.parquet | arrow_schema |            |             |                 |            5 |                |       |           |          | 
 /tmp/product_example.parquet | id           | INT32      |             | OPTIONAL        |              |                |       |           |        0 | 
 /tmp/product_example.parquet | product      |            |             | OPTIONAL        |            3 |                |       |           |        1 | 
 /tmp/product_example.parquet | id           | INT32      |             | OPTIONAL        |              |                |       |           |        2 | 
 /tmp/product_example.parquet | name         | BYTE_ARRAY |             | OPTIONAL        |              | UTF8           |       |           |        3 | STRING
 /tmp/product_example.parquet | items        |            |             | OPTIONAL        |            1 | LIST           |       |           |        4 | LIST
 /tmp/product_example.parquet | list         |            |             | REPEATED        |            1 |                |       |           |          | 
 /tmp/product_example.parquet | element        |            |             | OPTIONAL        |            3 |                |       |           |        5 | 
 /tmp/product_example.parquet | id           | INT32      |             | OPTIONAL        |              |                |       |           |        6 | 
 /tmp/product_example.parquet | name         | BYTE_ARRAY |             | OPTIONAL        |              | UTF8           |       |           |        7 | STRING
(10 rows)

Inspect Parquet metadata

You can call SELECT * FROM parquet.metadata(<uri>) to discover the detailed metadata of the Parquet file, such as column statistics, at given uri.

SELECT uri, row_group_id, row_group_num_rows, row_group_num_columns, row_group_bytes, column_id, file_offset, num_values, path_in_schema, type_name FROM parquet.metadata('/tmp/product_example.parquet') LIMIT 1;
             uri              | row_group_id | row_group_num_rows | row_group_num_columns | row_group_bytes | column_id | file_offset | num_values | path_in_schema | type_name 
------------------------------+--------------+--------------------+-----------------------+-----------------+-----------+-------------+------------+----------------+-----------
 /tmp/product_example.parquet |            0 |                  1 |                    13 |             842 |         0 |           0 |          1 | id             | INT32
(1 row)

SELECT stats_null_count, stats_distinct_count, stats_min, stats_max, compression, encodings, index_page_offset, dictionary_page_offset, data_page_offset, total_compressed_size, total_uncompressed_size FROM parquet.metadata('/tmp/product_example.parquet') LIMIT 1;
 stats_null_count | stats_distinct_count | stats_min | stats_max |    compression     |        encodings         | index_page_offset | dictionary_page_offset | data_page_offset | total_compressed_size | total_uncompressed_size 
------------------+----------------------+-----------+-----------+--------------------+--------------------------+-------------------+------------------------+------------------+-----------------------+-------------------------
                0 |                      | 1         | 1         | GZIP(GzipLevel(6)) | PLAIN,RLE,RLE_DICTIONARY |                   |                      4 |               42 |                   101 |                      61
(1 row)

The stats_geospatial column reports the bounding box and the geometry types that Parquet computed for a geospatial column, as jsonb. It is NULL for the columns that have no geospatial statistics, which are all of the columns except the geometry and geography ones. The geometry types are the ISO WKB codes that the Parquet geospatial specification lists, e.g. 1 is a point and 2 is a linestring. The zmin, zmax, mmin and mmax bounds are only reported for the geometries that have those dimensions. A geometry column is also reported as NULL when it contains a PostGIS type that the Parquet geospatial specification does not cover, e.g. a curved, triangulated or polyhedral one, since those cannot be bounded. A geography column always reports its geometry types, but its bounding box is only reported when all of its geometries are points: the edges of a geography are geodesics, which can reach beyond the box that the coordinates of their endpoints give.

SELECT path_in_schema, stats_geospatial FROM parquet.metadata('/tmp/geo_example.parquet') WHERE stats_geospatial IS NOT NULL;
 path_in_schema |                                     stats_geospatial
----------------+--------------------------------------------------------------------------------------------
 location       | {"bbox": {"xmax": 5.0, "xmin": 1.0, "ymax": 6.0, "ymin": 2.0}, "geospatial_types": [1, 2]}
(1 row)

You can call SELECT * FROM parquet.file_metadata(<uri>) to discover file level metadata of the Parquet file, such as format version, at given uri.

SELECT * FROM parquet.file_metadata('/tmp/product_example.parquet')
             uri              | created_by | num_rows | num_row_groups | format_version 
------------------------------+------------+----------+----------------+----------------
 /tmp/product_example.parquet | pg_parquet |        1 |              1 | 1
(1 row)

You can call SELECT * FROM parquet.kv_metadata(<uri>) to query custom key-value metadata of the Parquet file at given uri.

SELECT uri, encode(key, 'escape') as key, encode(value, 'escape') as value FROM parquet.kv_metadata('/tmp/product_example.parquet');
             uri              |     key      |    value
------------------------------+--------------+---------------------
 /tmp/product_example.parquet | ARROW:schema | /////5gIAAAQAAAA ...
(1 row)

Inspect Parquet column statistics

You can call SELECT * FROM parquet.column_stats(<uri>) to discover the column statistics of the Parquet file, such as min and max value for the column, at given uri.

SELECT * FROM parquet.column_stats('/tmp/product_example.parquet')
 column_id | field_id |         stats_min          |         stats_max          | stats_null_count | stats_distinct_count 
-----------+----------+----------------------------+----------------------------+------------------+----------------------
         4 |        7 | item 1                     | item 2                     |                1 |                     
         6 |       11 | 1                          | 1                          |                1 |                     
         7 |       12 |                            |                            |                2 |                     
        10 |       17 |                            |                            |                2 |                     
         0 |        0 | 1                          | 1                          |                0 |                     
        11 |       18 | 2025-03-11 14:01:22.045739 | 2025-03-11 14:01:22.045739 |                0 |                     
         3 |        6 | 1                          | 2                          |                1 |                     
        12 |       19 | 2022-05-01 19:00:00+03     | 2022-05-01 19:00:00+03     |                0 |                     
         8 |       15 |                            |                            |                2 |                     
         5 |        8 | 1                          | 2                          |                1 |                     
         9 |       16 |                            |                            |                2 |                     
         1 |        2 | 1                          | 1                          |                0 |                     
         2 |        3 | product 1                  | product 1                  |                0 |                     
(13 rows)

List and read Parquet files from uri pattern

You can call SELECT * FROM parquet.list(<uri_pattern>) to see all uris that matches with the uri pattern. Uri pattern can resolve ** for directories and * for words in the uri. A file that holds no byte is not among the uris a pattern matches, since a Parquet file is its footer at the very least: the marker an object store browser writes for a folder it creates and the _SUCCESS of a job that wrote one are reported rather than read, so that a pattern covering one of them reads the files next to it instead of failing over it. No other character is pattern syntax, so a ? or a [...] in a uri is part of the name of the object it is written as rather than a pattern that matches a name next to it.

COPY (SELECT i FROM generate_series(1, 1000000) i) TO '/tmp/some/test.parquet' with (file_size_bytes '1MB');
COPY 1000000

SELECT * FROM parquet.list('/tmp/some/**/*.parquet');
                  uri                  |  size
---------------------------------------+---------
 /tmp/some/test.parquet/data_4.parquet |  100162
 /tmp/some/test.parquet/data_3.parquet | 1486916
 /tmp/some/test.parquet/data_2.parquet | 1486916
 /tmp/some/test.parquet/data_0.parquet | 1486920
 /tmp/some/test.parquet/data_1.parquet | 1486916
(5 rows)

Uri pattern is also supported by COPY FROM for all supported object stores except http(s) endpoints. A folder that a COPY TO with file_size_bytes wrote is read this way too: the folder holds no object of its own name, so COPY FROM '<folder>/*.parquet' is what reads the files in it.

A COPY FROM reads an object of a store over several requests, and every one of them names the version of the object that the read was opened on, so an object that is replaced while it is being read fails the copy rather than giving the rows of two objects at once.

COPY (SELECT i FROM generate_series(1, 1000000) i) TO 's3://testbucket/some/test.parquet' with (file_size_bytes '1MB');
COPY 1000000

CREATE TABLE test(a int);
CREATE TABLE

COPY test FROM 's3://testbucket/some/**/*.parquet';
COPY 1000000

Monitor COPY progress

The progress of an ongoing COPY TO/FROM a Parquet file is reported to the pg_stat_progress_copy view.

SELECT relid::regclass, command, type, bytes_processed, bytes_total, tuples_processed
FROM pg_stat_progress_copy;
 relid | command   | type     | bytes_processed | bytes_total | tuples_processed
-------+-----------+----------+-----------------+-------------+------------------
 test  | COPY FROM | CALLBACK |       318767104 |   390000021 |         31876709
(1 row)

The byte counters mean different things for the two directions:

• For COPY TO, bytes_processed is the number of the bytes that are already written to the Parquet file(s). bytes_total is not reported since the size of a Parquet file is not known before it is written.
• For COPY FROM, bytes_processed and bytes_total count the bytes of the uncompressed binary stream that pg_parquet feeds to Postgres, which is unrelated to the size of the Parquet file. bytes_total is extrapolated from the rows that are read so far, so it is an estimation until the last row of the file is read. The estimation is corrected after every record batch, hence it can move in both directions when the rows of the file differ in size.

Object Store Support

pg_parquet supports reading and writing Parquet files from/to S3, Azure Blob Storage, http(s) and Google Cloud Storage object stores. The host of an s3://, az:// or gs:// uri is the bucket or the container that holds the object rather than an endpoint, so such a uri is written without a port and the endpoint is configured per store below. Only an http(s):// uri names its endpoint, and keeps the port of it.

The uri of an object is parsed as the url standard asks it to be parsed, which removes a tab, a newline and a carriage return in it and trims the whitespace around it, so a uri holding one of those characters is refused rather than copied into an object other than the one it names. A space inside the path is kept, so an object whose name holds one is written and read as it is.

The object of a uri is named by the whole path of that uri, and a # or a ? ends that path rather than belonging to it: s3://bucket/report#2026.parquet names the object report. A uri holding one of those characters is rejected, since it names an object other than the one it was written for, and an object whose name holds one is named by writing the character as %23 or %3F. A query that is meant for the store rather than for the object, as the version id or the signature of a presigned uri is, is sent to no store. A local path is a path and not a uri, so both characters are part of the name of the file there.

[!NOTE] To be able to write into a object store location, you need to grant parquet_object_store_write role to your current postgres user. Similarly, to read from an object store location, you need to grant parquet_object_store_read role to your current postgres user.

The credentials of an object store are read from the environment of the postgres process, as described below for each store, and never from the uri: a uri that carries credentials before its host, e.g. s3://key:secret@bucket/file.parquet, is rejected rather than signed with the credentials of the environment while its own are only written into the logs.

S3 Storage

The simplest way to configure object storage is by creating the standard ~/.aws/credentials and ~/.aws/config files:

$ cat ~/.aws/credentials
[default]
aws_access_key_id = AKIAIOSFODNN7EXAMPLE
aws_secret_access_key = wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

$ cat ~/.aws/config 
[default]
region = eu-central-1

Alternatively, you can use the following environment variables when starting postgres to configure the S3 client:

• AWS_ACCESS_KEY_ID: the access key ID of the AWS account
• AWS_SECRET_ACCESS_KEY: the secret access key of the AWS account
• AWS_SESSION_TOKEN: the session token for the AWS account
• AWS_REGION: the default region of the AWS account
• AWS_ENDPOINT_URL: the endpoint
• AWS_SHARED_CREDENTIALS_FILE: an alternative location for the credentials file (only via environment variables)
• AWS_CONFIG_FILE: an alternative location for the config file (only via environment variables)
• AWS_PROFILE: the name of the profile from the credentials and config file (default profile name is default) (only via environment variables)
• AWS_ALLOW_HTTP: allows http endpoints (only via environment variables)
• AWS_<HTTP CLIENT OPTION>: an http client option, e.g. AWS_TIMEOUT (only via environment variables)

Config source priority order is shown below:

1. Environment variables,
2. Config file.

Supported S3 uri formats are shown below:

• s3:// <bucket> / <path>
• https:// <bucket>.s3.amazonaws.com / <path>
• https:// s3.amazonaws.com / <bucket> / <path>

Supported authorization methods' priority order is shown below:

1. Temporary session tokens by assuming roles,
2. Long term credentials.

Azure Blob Storage

The simplest way to configure object storage is by creating the standard ~/.azure/config file:

$ cat ~/.azure/config
[storage]
account = devstoreaccount1
key = Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==

Alternatively, you can use the following environment variables when starting postgres to configure the Azure Blob Storage client:

• AZURE_STORAGE_ACCOUNT: the storage account name of the Azure Blob
• AZURE_STORAGE_KEY: the storage key of the Azure Blob
• AZURE_STORAGE_CONNECTION_STRING: the connection string for the Azure Blob (overrides any other config)
• AZURE_STORAGE_SAS_TOKEN: the storage SAS token for the Azure Blob
• AZURE_TENANT_ID: the tenant id for client secret or workload identity auth (only via environment variables)
• AZURE_CLIENT_ID: the client id for client secret or workload identity auth (only via environment variables)
• AZURE_FEDERATED_TOKEN_FILE: the file system path to the federated token file to use for workload identity auth (only via environment variables)
• AZURE_CLIENT_SECRET: the client secret for client secret auth (only via environment variables)
• AZURE_STORAGE_ENDPOINT: the endpoint (only via environment variables)
• AZURE_CONFIG_FILE: an alternative location for the config file (only via environment variables)
• AZURE_ALLOW_HTTP: allows http endpoints (only via environment variables)
• AZURE_<HTTP CLIENT OPTION>: an http client option, e.g. AZURE_TIMEOUT (only via environment variables)

Config source priority order is shown below:

1. Connection string (read from environment variable or config file),
2. Environment variables,
3. Config file.

Supported Azure Blob Storage uri formats are shown below:

• az:// <container> / <path>
• azure:// <container> / <path>
• https:// <account>.blob.core.windows.net / <container> / <path>

Supported authorization methods' priority order is shown below:

1. Bearer token via client secret,
2. Sas token,
3. Storage key.

Http(s) Storage

Only https uris are supported by default. You can set ALLOW_HTTP environment variable to allow http uris.

An http(s) endpoint takes an object in a single request rather than in an upload of several parts, so a COPY TO such an endpoint buffers the whole file in memory and writes at most 10 MiB. A larger table is written with a file_size_bytes below that size, which writes it as a folder of files, and each file of that folder is then read back by its own name, since a uri pattern does not apply to an http(s) endpoint.

You can also use the following environment variables when starting postgres to configure the http(s) client:

• HTTP_<HTTP CLIENT OPTION>: an http client option, e.g. HTTP_TIMEOUT (only via environment variables)

Google Cloud Storage

The simplest way to configure object storage is by creating a json config file like [~/.config/gcloud/application_default_credentials.json] (can be generated by gcloud auth application-default login):

$ cat ~/.config/gcloud/application_default_credentials.json
{
  "gcs_base_url": "http://localhost:4443",     
  "disable_oauth": true,
  "client_email": "",
  "private_key_id": "",
  "private_key": ""
}

Alternatively, you can use the following environment variables when starting postgres to configure the Google Cloud Storage client:

• GOOGLE_SERVICE_ACCOUNT_KEY: json serialized service account key (only via environment variables)
• GOOGLE_SERVICE_ACCOUNT_PATH: an alternative location for the config file (only via environment variables)
• GOOGLE_<HTTP CLIENT OPTION>: an http client option, e.g. GOOGLE_TIMEOUT (only via environment variables)

Supported Google Cloud Storage uri formats are shown below:

• gs:// <bucket> / <path>

Http Client Options

Each object storage client has its own http client, which can be configured via environment variables that are named after the storage prefix, e.g. AWS_TIMEOUT for S3 and AZURE_TIMEOUT for Azure Blob Storage. The supported options are shown below:

• TIMEOUT: the timeout for a single request, including the time it takes to read the response body. Defaults to 30 seconds. Slow links or large file_size_bytes values might need a higher value,
• CONNECT_TIMEOUT: the timeout for only establishing the connection. Defaults to 5 seconds,
• READ_TIMEOUT: the timeout for reading the response body after the response headers arrived,
• PROXY_URL: the proxy to send the requests through,
• PROXY_CA_CERTIFICATE: a PEM encoded CA certificate to trust when connecting to the proxy,
• PROXY_EXCLUDES: a comma separated list of hosts that should not be sent through the proxy,
• ALLOW_INVALID_CERTIFICATES: skips certificate validation. Insecure, it allows man in the middle attacks,
• DISABLE_SYSTEM_CERTIFICATES: does not trust the certificates of the operating system,
• USER_AGENT: the user agent header to send with the requests,
• DEFAULT_CONTENT_TYPE: the content type header to send with the requests,
• HTTP1_ONLY, HTTP2_ONLY: forces a single http version,
• HTTP2_KEEP_ALIVE_INTERVAL, HTTP2_KEEP_ALIVE_TIMEOUT, HTTP2_KEEP_ALIVE_WHILE_IDLE, HTTP2_MAX_FRAME_SIZE: tune the http2 connections,
• POOL_IDLE_TIMEOUT, POOL_MAX_IDLE_PER_HOST: tune the connection pool,
• RANDOMIZE_ADDRESSES: shuffles the resolved addresses of a host before connecting.

Durations are given with a unit, e.g. 30s, 5m or 1m30s. Booleans are given as true or false.

A request that an object store is slow to answer is retried, which takes minutes by default before it gives up. A statement that is waiting on one is still cancellable while that happens: Ctrl-C, statement_timeout, pg_cancel_backend and pg_terminate_backend end it without waiting for the retries to run out.

Copy Options

pg_parquet supports the following options in the COPY TO command:

• format parquet: you need to specify this option to read or write Parquet files which does not end with .parquet[.<compression>] extension,
• file_size_bytes <string>: the total file size per Parquet file. When set, the parquet files, with target size, are created under parent directory (named the same as file name). By default, when not specified, a single file is generated without creating a parent folder. You can specify total bytes without unit like file_size_bytes 2000000 or with unit (KB, MB, or GB) like file_size_bytes '1MB'. A file ends at a row group boundary, so it can exceed the target by up to one row group, and row_group_size_bytes is capped at the target so that a row group cannot be larger than the file it is written into. A copy into a folder that an earlier copy wrote replaces it: once the copy wrote its own files, the files that the earlier and larger copy left behind are removed, so that reading the folder back does not return the rows of two copies at once. A uri that already names an object is no folder destination and such a copy is refused: the files of it would be written under a name that is taken, which an object store is free to accept and then hide, leaving the folder to read as the object it was written over. A copy that finds no row writes one file holding nothing but the schema, the way a copy without file_size_bytes writes the single file it was given, so that the folder is one a COPY FROM reads no row from rather than one it finds no file in. Two copies into the same folder at the same time are not serialized, however: they number their files the same way, so each overwrites the files of the other and removes those it takes for a previous copy's, and the folder is left holding rows of both although both copies report the rows they wrote. A folder destination written by more than one copy at a time needs a lock of the application's own,
• field_ids <string>: fields ids that are assigned to the fields in Parquet file schema. By default, no field ids are assigned. Pass auto to let pg_parquet generate field ids. You can pass a json string to explicitly pass the field ids, like '{"id": 1}'. A nested column takes a json object rather than an id, whose __root_field_id key is the id of the column itself and whose other keys are its nested fields, the fields of a composite type by name, the element of an array as element, and the entries of a map as key and val, e.g. '{"friends": {"__root_field_id": 2, "element": {"__root_field_id": 3, "name": 4}}}'. A field the mapping leaves out is written without a field id. __root_field_id is reserved, so a nested field cannot be named after it,
• row_group_size <int64>: the number of rows in each row group while writing Parquet files. The default row group size is ,

[!NOTE] A bloom filter only helps the readers that push equality filters down to the Parquet file, like DuckDB, Spark or DataFusion. COPY FROM reads every row of a file, so it never reads a bloom filter. A selected column is a top level column, which expands to all of its leaf columns when it is of a nested type.

pg_parquet supports the following options in the COPY FROM command:

• format parquet: you need to specify this option to read or write Parquet files which does not end with .parquet[.<compression>] extension,
• match_by <string>: method to match Parquet file fields to PostgreSQL table columns. The available methods are position and name. The default method is position. You can set it to name to match the columns by their name rather than by their position in the schema (default). Match by name is useful when field order differs between the Parquet file and the table, but their names match. It also matches the fields of the composite type columns by name.

Configuration

The following GUC parameters are available:

• pg_parquet.enable_copy_hooks: you can set this parameter to on or off to enable or disable the pg_parquet extension. The default value is on.
• pg_parquet.max_parallel_workers: the number of parallel workers a COPY TO or COPY FROM command may use. The default value is 0, which keeps both of them single process. The workers come from the same pool as parallel query workers, so a request above max_parallel_workers is capped to it. A command that the pool cannot spare every worker it asked for runs with the ones it was given. A COPY FROM gains less from a large number of workers than a COPY TO does, because the workers only decode the row groups of the file while the leader inserts every row it hands out.
  • For COPY TO, each worker writes a range of the table's pages, and the ranges end up in the destination without being re-encoded. For an s3://, az:// or gs:// destination, every worker uploads the ranges it wrote as parts of the destination's own multipart upload and the backend only adds the file's footer, so each byte of the file is uploaded once, nothing is downloaded, and nothing of such a copy is written to local disk. A copy whose server crashes can leave an unfinished multipart upload behind, which the bucket's own rule for incomplete uploads is what removes. A part that is left behind this way holds the rows of the copy that wrote it, so a uri pattern that covers it skips it rather than reading those rows again, and reports it as a file the pattern does not read. The parts of that upload differ in size, which a store that requires all but the last part to be the same size, e.g. Cloudflare R2, rejects. The bytes of a range are uploaded as the range wrote them, header and footer included, so the destination holds a little under 1% of bytes nothing points at. For a local file, each worker writes a part file next to the destination instead, and the backend merges the parts into it. With file_size_bytes the destination is a folder of complete files, so each worker writes files of its own into it rather than parts of one file, and the file a worker is in the middle of is finished before its range ends, which leaves a few more and smaller files behind than a single process copy writes. The files of such a folder are numbered in the order the workers claim them, so the rows of the table are not in the order of the file names. Parallel COPY TO applies to a single non-temporary heap table copied to a plain local file, to a folder of files, or to one of those object stores; anything else, e.g. COPY (SELECT ..) TO, a COPY TO PROGRAM or TO STDOUT, or an uri, is copied by a single process. Note that each range writes its own row groups, so a parallel ends up with more and smaller row groups than a single process one.

Supported Types

pg_parquet has rich type support, including PostgreSQL's primitive, array, and composite types. Below is the table of the supported types in PostgreSQL and their corresponding Parquet types.

PostgreSQL Type	Parquet Physical Type	Logical Type
bool	BOOLEAN
smallint	INT16
integer	INT32
bigint	INT64
real	FLOAT
oid	INT32
oid8(6)	INT64	INTEGER(64,false)
double	DOUBLE
numeric(1)	FIXED_LEN_BYTE_ARRAY(16)	DECIMAL(128)
text	BYTE_ARRAY	STRING
json	BYTE_ARRAY	JSON
jsonb	BYTE_ARRAY	JSON
uuid	FIXED_LEN_BYTE_ARRAY(16)	UUID
bytea	BYTE_ARRAY
date (2)	INT32	DATE
timestamp	INT64	TIMESTAMP_MICROS
timestamptz (3)	INT64	TIMESTAMP_MICROS
time	INT64	TIME_MICROS
timetz(3)	INT64	TIME_MICROS
geometry(4)	BYTE_ARRAY	GEOMETRY
geography(4)	BYTE_ARRAY	GEOGRAPHY

Nested Types

PostgreSQL Type	Parquet Physical Type	Logical Type
composite	GROUP	STRUCT
array(7)	element's physical type	LIST
crunchy_map(5)	GROUP	MAP

[!WARNING]

• (1) numeric type is written the smallest possible memory width to parquet file as follows:
  • numeric(P <= 9, S) is represented as INT32 with DECIMAL logical type
  • numeric(9 < P <= 18, S) is represented as INT64 with DECIMAL logical type
  • numeric(18 < P <= 38, S) is represented as FIXED_LEN_BYTE_ARRAY(9-16) with DECIMAL logical type
  • numeric(38 < P, S) is represented as BYTE_ARRAY with STRING logical type
  • numeric is allowed by Postgres. (precision and scale not specified). These are represented by a default precision (38) and scale (9) instead of writing them as string. You get runtime error if your table tries to write a numeric value which is not allowed by the default precision and scale (29 integral digits before decimal point, 9 digits after decimal point). Reading into such a column keeps all the digits of the file's value, be it a decimal or a string, since Postgres stores an unbounded numeric at any precision and scale.
• (2) The date type is represented according to Unix epoch when writing to Parquet files. It is converted back according to PostgreSQL epoch when reading from Parquet files.
• (3) The timestamptz and timetz types are adjusted to UTC when writing to Parquet files. They are converted back with UTC timezone when reading from Parquet files.
• (4) The geometry and geography types are represented as BYTE_ARRAY encoded as WKB with the GEOMETRY and GEOGRAPHY logical types, specified by the parquet geospatial spec, when postgis extension is created. Otherwise, they are represented as BYTE_ARRAY with STRING logical type. The crs of the logical type is taken from the srid of the column's type modifier, e.g. geometry(point, 3857) is written with the EPSG:3857 crs. A column whose srid is lon/lat on the WGS84 ellipsoid is written with the default crs of the spec, which is OGC:CRS84, and a column without an is written with the unset crs of the spec, which is . An that no authority in claims is written with the engine specific form of the spec, e.g. , so that it is not mistaken for an unset crs. An whose holds the separator, or is itself, takes that same form, since its form would not read back as this . A column without an is written with the default crs, since PostGIS geographies are on the ellipsoid. The bounding box and the geometry types of each column are written as geospatial statistics, except for the bounding box of a column that has edges, as described . On read, the of the geometries is restored from the of the logical type, resolving its form through and taking the code of its form as the itself, and falls back to the of the target column's type modifier when the file has no .

[!NOTE] infinity and -infinity values of the date, timestamp and timestamptz types are written as the limits of the underlying INT32/INT64, which is how COPY FROM reads them back. A temporal value in the Parquet file that PostgreSQL cannot represent is rejected with an out of range error.

[!WARNING] Any type that does not have a corresponding Parquet type will be represented, as a fallback mechanism, as BYTE_ARRAY with STRING logical type. e.g. enum

[!NOTE] COPY FROM does not need an exact type match: a file's column is cast to the table's column type whenever PostgreSQL has a cast between them. That is what makes the files of the other writers readable, e.g. the TIME_MILLIS, FIXED_LEN_BYTE_ARRAY or wider than DECIMAL(38) columns of Spark, and the dictionary encoded (pandas categorical), view or null typed columns that the Arrow based writers record in their schema. An INT96 timestamp column, which Hive, Impala and Spark write, records no resolution of its own, so it is read as microseconds, which is the resolution PostgreSQL stores and covers its whole timestamp range. A fractional column that is read into an integer column is rounded the way PostgreSQL rounds it, i.e. a DECIMAL half away from zero and a FLOAT half to even, rather than truncated toward zero.

Postgres Support Matrix

pg_parquet supports the following PostgreSQL versions:

PostgreSQL Major Version	Supported
14	✅
15	✅
16	✅
17	✅
18	✅
19	✅