pg_lake_engine

Overview

Query-engine component. pg_extension_base auto-loads its module; delegated DuckDB execution additionally requires the separately running PG-major pgduck_server. Extension SQL/control version is 3.4; source and DEB/RPM package version is 3.4.0.

Version

Build

You can build the RPM / DEB packages for pg_lake using pig build:

pig build pkg pg_lake         # build RPM / DEB packages

Install

You can install pg_lake directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_lake;          # Install for current active PG version

pig ext install -y pg_lake -v 18  # PG 18
pig ext install -y pg_lake -v 17  # PG 17
pig ext install -y pg_lake -v 16  # PG 16

dnf install -y pg_lake_18       # PG 18
dnf install -y pg_lake_17       # PG 17
dnf install -y pg_lake_16       # PG 16

apt install -y postgresql-18-pg-lake   # PG 18
apt install -y postgresql-17-pg-lake   # PG 17
apt install -y postgresql-16-pg-lake   # PG 16

Preload:

shared_preload_libraries = 'pg_extension_base';

Create Extension:

CREATE EXTENSION pg_lake_engine CASCADE;  -- requires: pg_extension_base, pg_map

Usage

Sources:

• Official pg_lake architecture overview
• Version 3.4 control file
• Base SQL objects
• Version 3.4 cleanup-queue change

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.

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.4 adds resolve_metadata to the deletion queue so Iceberg metadata can be expanded into exact referenced files during VACUUM, moving object-store traversal off the DROP path.
• Roles are cluster-wide objects and can outlive an extension instance in one database; review memberships separately when removing pg_lake.