pg_parquet: reading and writing Parquet from inside PostgreSQL

COPY, extended rather than replaced

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

The design decision is that everything happens through the existing COPY syntax. A query result becomes a Parquet file:

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

and a Parquet file becomes table data:

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

The point of staying inside COPY is that it is the one operation every PostgreSQL deployment already has, with its existing permissions model, its existing transaction semantics and its existing tooling. A team that already runs COPY for CSV does not have to learn a new pipeline, and the Parquet file is a first-class copy target rather than a side channel.

The README is explicit about the scope of the tool. There are mainly three things you can do with pg_parquet: export Postgres tables and queries to Parquet files, to stdin and stdout, or to a program's stream; ingest data from Parquet files to Postgres tables with type coercion and schema inference; and inspect the schema and metadata of Parquet files. That last capability, inspecting schema and metadata, is what makes the extension useful for exploring files you did not write yourself.

Inferring a table from a Parquet file

The feature that goes beyond a plain copy is schema inference. You can use the CREATE TABLE () WITH (definition_from = <uri>) command to infer the columns of a Postgres table, and you can even infer and populate the table in one step with 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');

That is a genuinely useful primitive for an exploration workflow. Pointing a query at an unfamiliar Parquet file and discovering its shape inside the database, without leaving SQL or installing a client, changes what a database is useful for. The inspection commands listed in the quick reference cover the same ground from the other direction: inspecting Parquet schema, inspecting Parquet metadata, and inspecting Parquet column statistics.

The complexity arrives with composite types, and the documentation handles it rather than hiding it. If an inferred column is of a composite type, a new type is created named as parquet_structs.struct_<hash>, where the hash is determined by the field types and names of the composite type. The type is created by the user who runs the command, so that user needs CREATE privilege on the parquet_structs schema, which the extension grants to public. And the generated types are not dropped together with the table.

That last sentence is the one to sit with. Every inference run against a file with a nested structure leaves a type behind in the schema, named by a hash of its shape, owned by whoever ran it. A cluster where analysts run inference ad hoc accumulates schema objects that nothing cleans up and that nobody can easily correlate with the table they came from. If you plan to use inference in a loop, plan for the schema as well.

Composite types, arrays and the round trip

The COPY example in the README is deliberately gnarly, and it is worth reading closely because it shows what the extension handles.

The example creates a composite type for a product item with an id, a name and a price, then a composite type for a product that contains an array of those items, then a table with a plain integer column, a product column, an array of products, a timestamp and a timestamptz. It inserts a row with nested ROW and ARRAY constructors, including NULLs inside the array, then copies the table to a Parquet file with compression 'gzip', and copies the file back into the same table.

So the claim being demonstrated is a round trip that preserves nested composite types, arrays containing composites, NULL elements, and the distinction between timestamp without time zone and timestamp with time zone. The compression option is a second copy option, and the Copy Options section of the quick reference lists more of them.

The dependency set in the Cargo manifest shows how much of the format is covered. The arrow, arrow-cast and arrow-schema crates at version 60 are the Arrow layer, and the parquet crate at version 60 carries the compression codecs: crc, snap, brotli, flate2-zlib-rs, simdutf8, lz4 and zstd, along with arrow, object_store, canonical extension types and geospatial support. There is a separate parquet-geospatial crate at the same version, which tells you that geospatial types have their own path through the code rather than being an afterthought.

The type coercion on the read side is the other half. Copying a Parquet file into a Postgres table means mapping Parquet types onto Postgres types, and where the two do not line up the extension coerces rather than refuses. That is convenient and it is also lossy in ways you only discover by reading the data back.

Object store access through object_store and the AWS SDK

The storage story is delegated to a general purpose library rather than being written per provider. The object_store crate at version 0.14 is pulled in with the aws, azure, fs, gcp and http features enabled, and the aws-config and aws-credential-types crates handle credentials with the rustls and rt-tokio features. azure_storage at 0.21 covers Blob Storage directly, and there is also a glob crate and a url crate for pattern handling and URI parsing.

Two details in that list are worth noticing. The AWS SDK is configured with rustls rather than the platform's own TLS stack, so certificate handling does not depend on the operating system's OpenSSL, which matters for a database extension that has to run on whatever the platform ships. And the tokio dependency is present but trimmed to the rt, time and macros features, since the extension runs inside the database's own process rather than as a standalone service with its own runtime.

The Makefile in the repository shows how the test environments for each backend are stood up, and it is a more interesting document than it looks. There are separate container targets for MinIO, Azurite, a fake GCS server and a WebDAV server, and the image list includes a comment explaining that MinIO gated its community images, that the minio/minio and quay.io/minio/minio paths no longer allow anonymous pulls and that dl.min.io returns 410, so the build uses an archived MinIO image under a different namespace. The readiness checks are written with a comment explaining that containers do not always come up, for example when an image pull is rate limited, so the checks give up after a timeout rather than waiting forever, and that curl is only asked to reach the endpoint rather than to get a 2xx, because a bare GET on the root of these services answers 400 or 403 once they are up.

Those two comments are a good illustration of what running an extension against real object stores costs. The backends are emulated in tests, and the emulation has sharp edges of its own.

Building it with cargo-pgrx, and the root permission problem

There is no package for this extension. The installation section of the README is titled Installation From Source, and the steps are explicit: after installing Postgres, set up rustup and cargo-pgrx to build the extension.

The build pins versions. The example exports CARGO_PGRX_VERSION=0.19.3 and PG_MAJOR=19, installs cargo-pgrx with cargo install --force --locked, configures pgrx against a specific pg_config with cargo pgrx init, appends shared_preload_libraries = 'pg_parquet' to the pgrx data directory's postgresql.conf, and then either runs a session with cargo pgrx run --features pg"${PG_MAJOR}" or installs the extension with cargo pgrx install --release --features pg"${PG_MAJOR}". Finally the extension is created in the database with CREATE EXTENSION pg_parquet.

The permission wrinkle is documented at length, and it is the part that bites on a packaged Postgres. pgrx installs the extension into the directories of the pg_config it is configured with, and for a packaged Postgres those directories are owned by root. cargo pgrx install can be told to use sudo, but cargo pgrx run has no such flag and instead needs those directories to be writable. The alternative given is cargo pgrx init --pg"${PG_MAJOR}" download, which has pgrx build its own Postgres under ~/.pgrx, a directory that belongs to you and needs no root permissions at all.

There is a further wrinkle after the sudo route. The files of a packaged pg_parquet are still owned by root after installation, they are overwritten in place rather than recreated, and the documentation explains that you have to remove them before you can install over the top, with the exact rm command given for both the pkglibdir and the sharedir extension paths.

The version matrix in the Cargo manifest explains why the pins are so careful. The default feature is pg19, and there are features for pg19 down to pg14, with pgrx pinned to exactly 0.19.3. The pgrx version has to match the pgrx dependency in the manifest, which the build script comment repeats.

Where the extension is the wrong tool

The first limitation is the deployment one. A PostgreSQL extension is a shared library loaded into the database server process. That means this extension has to be compiled for your Postgres version, installed into the server's directories, added to shared_preload_libraries and restarted, which rules out most managed Postgres services where you cannot load libraries. The repository offers no packaged builds, so every installation is a source build with its own toolchain requirements.

The second limitation is that this is a COPY extension, not a table format. You cannot query a Parquet file with a WHERE clause and get pushed-down filtering, and there is no Parquet relation you can join against. If your problem is running analytics over a lake of Parquet files, this is the wrong tool and the answer is a query engine that speaks Parquet and pushes predicates into the reader. pg_parquet is for moving data across the boundary, and it does that well.

The third limitation is what happens when a read and a write disagree. Type coercion on ingest and the lossy nature of mapping a Parquet schema onto Postgres types mean a round trip is not guaranteed to be identical, and the created composite types that inference leaves behind are a permanent trace of a decision that a pipeline tool would have made in memory and discarded.

The real alternative, and it is not a small one, is to keep the boundary outside the database. A job that reads from S3 with a Python or Rust library and writes into Postgres over the wire gives you a stable schema, a retryable pipeline, a place to put validation and transformation, and no requirement to install a custom extension on a production database. That approach is more code and it is a separate system to operate, and it does not give you the ability to write a Parquet file with a single SQL statement against a query you just wrote. The choice depends on how often the boundary is crossed and who is doing the crossing. For a scheduled export that runs nightly, an external job is the safer answer. For an analyst who wants to look at a file from a SQL prompt, or a database that is the only thing in the pipeline, the extension earns its place.

Editorial conclusion

Adopt pg_parquet if your PostgreSQL database needs to exchange Parquet with object storage and you want that exchange to happen in SQL without an external pipeline, and if you can live with the extension being in the database's process and having to be installed from source. Do not adopt it as a general query engine over Parquet, since it is a COPY extension rather than a table format, and do not expect it to work on a managed Postgres service where you cannot load shared libraries. Before you deploy it, test the schema inference path with your real data, because the documentation notes that inferred composite types are created as parquet_structs.struct_<hash> and are not dropped with the table, and check that the role running the copy has CREATE on the parquet_structs schema.

Frequently asked questions

Official sources

1. CrunchyData/pg_parquet on GitHub
2. Issues
3. README
4. Releases