pg_lake_engine
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 |
| Relationships | |
|---|---|
| Schemas | __lake__internal__nsp__ lake_engine lake_struct pg_catalog |
| Requires | pg_extension_base pg_map |
| Need By | pg_lake_copy pg_lake_iceberg pg_lake_table |
| See Also | pg_duckdb pg_ducklake pg_mooncake duckdb_fdw pg_parquet pg_clickhouse columnar storage_engine orioledb |
| Siblings | pg_lake pg_extension_base pg_extension_updater pg_map pg_lake_iceberg pg_lake_table pg_lake_copy |
Packages
| 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
pg_lake-3.7.0.tar.gz
Install
Make sure PGDG and PIGSTY repo available:
Install this extension with pig:
Config this extension to shared_preload_libraries:
Create this extension with:
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:
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, andlake_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 PostgreSQLdate.lake_engine.deletion_queue: tracks committed orphan-file cleanup; readable bylake_write.lake_engine.in_progress_files: tracks files produced by transactions that have not committed.lake_engine.flush_deletion_queue(regclass)andflush_in_progress_queue(): privileged cleanup functions used by maintenance paths.
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.5addslast_attempt_attolake_engine.deletion_queue. Cleanup retries are spaced bypg_lake_engine.vacuum_file_remove_retry_interval;pg_lake_engine.vacuum_file_remove_max_retrieslimits 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.