Skip to content

pg_lake_engine

Query engine for data lake queries

pg_lake : Query engine for data lake queries

Overview

ID Extension Package Version Category License Language
2564 pg_lake_engine pg_lake 3.7 OLAP Apache-2.0 C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--sLd-- No Yes Yes Yes no no
Matching pgduck_server required.

Packages

Type Repo Version PG Major Compatibility Package Pattern Dependencies
EXT PIGSTY 3.7 19 18 17 16 15 pg_lake pg_extension_base, pg_map
RPM PIGSTY 3.7.0 19 18 17 16 15 pg_lake_$v -
DEB PIGSTY 3.7.0 19 18 17 16 15 postgresql-$v-pg-lake -
Linux / PG PG19 PG18 PG17 PG16 PG15
el8.x86_64 N/A N/A N/A N/A N/A
el8.aarch64 N/A N/A N/A N/A N/A
el9.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el9.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el10.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el10.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d12.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d12.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d13.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d13.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u22.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u22.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u24.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u24.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u26.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u26.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A

Source

github.com/Snowflake-Labs/pg_lake/tree/main/pg_lake_engine

pig build pkg pg_lake;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_lake;		# install via package name, for the active PG version
pig install pg_lake_engine;		# install by extension name, for the current active PG version

pig install pg_lake_engine -v 19;   # install for PG 19
pig install pg_lake_engine -v 18;   # install for PG 18
pig install pg_lake_engine -v 17;   # install for PG 17
pig install pg_lake_engine -v 16;   # install for PG 16

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_extension_base';

Create this extension with:

CREATE EXTENSION pg_lake_engine CASCADE; -- requires pg_extension_base, pg_map

Usage

Sources:

pg_lake_engine is the shared execution layer used by the pg_lake table, copy, and Iceberg extensions. It rewrites eligible PostgreSQL work for pgduck_server, maps PostgreSQL and DuckDB values, and tracks remote files that must be removed after aborts or table changes. It is an internal dependency rather than a standalone analytics interface.

The reviewed upstream release is 3.6.0, with SQL extension version 3.6 and PostgreSQL 16–19 support. Existing packaged versions are tracked separately. Keep libraries and the query server from the same release.

Deployment Boundary

Install it through the top-level extension so its dependency graph and preload directives remain aligned:

shared_preload_libraries = 'pg_extension_base'
CREATE EXTENSION pg_lake CASCADE;

pg_lake_engine requires pg_extension_base and pg_map. Query execution also requires a running local pgduck_server; creating only this extension does not provide a usable lake table.

User-Visible Objects

  • Roles lake_read, lake_write, and lake_read_write: shared privilege groups consumed by the other components.
  • to_postgres(any): returns its input while forcing that expression to be evaluated in PostgreSQL instead of pushed to the lake engine.
  • to_date(double precision): converts a days-since-Unix-epoch value commonly found in Parquet to a PostgreSQL date.
  • lake_engine.deletion_queue: tracks committed orphan-file cleanup; readable by lake_write.
  • lake_engine.in_progress_files: tracks files produced by transactions that have not committed.
  • lake_engine.flush_deletion_queue(regclass) and flush_in_progress_queue(): privileged cleanup functions used by maintenance paths.
SELECT to_postgres(application_only_function(payload))
FROM external_events;

Use to_postgres() only when an expression cannot or should not be pushed down; pulling data back into PostgreSQL may substantially increase transfer and execution cost.

Internal State and Caveats

  • The __lake__internal__nsp__ functions are planner/deparser placeholders and are not a supported direct SQL API.
  • Do not manually update or delete queue rows. Cleanup functions need the extension’s object-store credentials and privilege roles and should be invoked only as documented by operational tooling.
  • Version 3.5 adds last_attempt_at to lake_engine.deletion_queue. Cleanup retries are spaced by pg_lake_engine.vacuum_file_remove_retry_interval; pg_lake_engine.vacuum_file_remove_max_retries limits attempts instead of counting unrelated VACUUM passes.
  • Roles are cluster-wide objects and can outlive an extension instance in one database; review memberships separately when removing pg_lake.

Upstream 3.6.0

Upstream 3.6.0 adds PostgreSQL 19 support and uses DuckDB 1.5.6. Deploy matching libraries and pgduck_server, keep pg_extension_base preloaded, restart loaded processes and apply the supplied SQL updates to version 3.6. The engine migration adds internal UUID pushdown wrappers. They are planner/deparser plumbing, not a general-purpose application API.

Upstream 3.6.1 runtime update

Deploy the matching 3.6.1 pgduck_server with the extension libraries. This patch backports a Brotli decoder security correction and reduces the query server’s S3 uploader maximum file size to 50 GB. SQL remains 3.6; an unchanged SQL version does not prove the patched query server is installed. The C COPY-query export interface is internal integration work, not a new SQL function in this component.

Upstream 3.6.2

The 3.6.2 patch corrects byte-by-byte CSV-value transmission in pgduck_server. Deploy the matching query-server binary with the extension libraries and reconnect/restart processes holding old code. The component SQL version remains 3.6; an unchanged extversion does not prove the query-server fix is installed. Package availability remains a separate catalog fact.

Upstream 3.7.0

The current reviewed release is 3.7.0 and this component’s control version is 3.7. Earlier version sections describe historical behavior and package examples. Upgrade the complete family with matching libraries and pgduck_server, restart processes holding old code, and apply the supplied extension updates to SQL version 3.7. This does not establish package availability.

Table reader