gem-hunter

Stream Apache Arrow RecordBatches into Postgres.

For Development: See DEVELOPMENT.md for setup instructions including PostgreSQL installation requirements for running tests.

Postgres supports two bulk load formats: text (including CSV) and a custom binary format. Loading data from CSVs is convenient but has a lot of problems:

• CSV has no standard for missing values. You have to configure the load with a specific string (or lack thereof) to interpret as null.
• CSV is untyped so you can end up loading a float as an int and such.
• There is no standard for quoting delimiters in text columns, leading to the need to escape characters or sanitize the data before loading it.
• CSV files don't natively support any sort of compression. This results in larger files in storage and more data transferred compared to compressible formats.

Other data systems, particularly data warehouses like BigQuery and Redshift, have robust support for exporting data to Parquet and CSV. Exporting to CSV has the same pitfalls as loading from CSV, and sometimes even conflicting semantics for nulls, escaped delimiters, quotation and type conversions.

Since Postgres does not natively support loading from Parquet this library provides an io-free encoder that can convert from Parquet to Postgres' binary format on the fly. It accepts Arrow data as an input which means great support for reading Parquet files from all sorts of sources (disk, HTTP, object stores, etc.) in an efficient and performant manner.

Benchmarks using the NYC Yellow Cab dataset show that it takes pgpq less than 1 second to encode 1M rows and that the cost of encoding + binary copy is lower than the cost of a native CSV copy (which ignores the cost of a CSV export if the data was a Parquet file in the first place).

A Python wrapper that is published on PyPi. It takes pyarrow data as an input.

See py for more info and a use example.

The core is written in Rust and can be used in Rust-based projects. It doesn't depend on any particular database driver and accepts arrow-rs objects as inputs.

See core.

We support nearly all scalar data types, all three of Arrow's list layouts, and structs (as Postgres composite types).

The non-default output types (JSON/JSONB for strings) are selected per column with StringEncoderBuilder::new_with_output.

A struct column becomes a Postgres composite type, and PostgresSchema::ddl emits the CREATE TYPE for it using the Arrow field names.

A composite's OID goes on the wire wherever one is nested — a struct inside a struct, or an array of structs — and Postgres allocates that OID when the type is created, so it has to come from the database you are loading into:

select oid from pg_type where typname = 'my_struct_t';

let encoder = ArrowToPostgresBinaryEncoder::try_new(&schema)?
    .with_composite_oids(&HashMap::from([("my_struct_t".to_string(), oid)]))?;

Top-level struct columns need nothing extra: binary COPY declares no column types.

For more complex data types, like a struct with list fields, you might be better off dumping the data into a JSONB column. The arrow-json rust crate arrow-json Python package provide support for converting arbitrary Arrow arrays into arrays of JSON strings, which can then be loaded into a JSONB column.

• Many hosted Postgres flavors dont support extensions
• You're in control of compute and can scale it outside of your database, which is typically much cheaper and flexible
• No dependencies of Parquet or Postgres librararies, this is a fully self contained binary
• You control auth on both ends (to the source and destination)