pg_lake_table
pg_lake : Data lake tables and Iceberg tables
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 2566 | pg_lake_table | 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 | __pg_lake_table_writes lake_file lake_file_cache lake_table pg_catalog |
| Requires | btree_gist pg_lake_engine pg_lake_iceberg |
| Need By | pg_lake pg_lake_copy |
| See Also | pg_ducklake pg_mooncake columnar pg_parquet storage_engine orioledb pg_sorted_heap aws_s3 pg_bulkload file_fdw |
| Siblings | pg_lake pg_extension_base pg_extension_updater pg_map pg_lake_engine pg_lake_iceberg 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_table
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_table exposes object-store files as PostgreSQL foreign tables and provides the USING iceberg table syntax. It owns the pg_lake and pg_lake_iceberg foreign servers, file inspection/cache utilities, table catalogs, and transaction hooks; Iceberg metadata encoding is delegated to pg_lake_iceberg.
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.
Query External Files
Install the complete stack, including the required pg_lake_engine, pg_lake_iceberg, and btree_gist dependencies:
An empty column list asks pg_lake to infer the file schema:
Create a writable Iceberg table through the extension-provided table access method:
File and Table Utility Index
lake_file.list(pattern): lists matching object paths, sizes, modification times, and ETags.lake_file.size(path)andlake_file.exists(path): inspect one remote object.lake_file.preview(url, format, compression): returns inferred column names and types.lake_file.delete(url): deletes a remote object; restrict it to roles that are allowed to remove data.lake_file_cache.add(path, refresh),remove(path), andlist(): manage the local file cache for members oflake_read.lake_iceberg.table_size(regclass): totals the current data-file sizes of an Iceberg table.- Foreign servers
pg_lakeandpg_lake_iceberg: read-file and writable-Iceberg entry points. - Access methods
icebergandpg_lake_iceberg: aliases used to translateCREATE TABLE ... USINGinto the extension’s foreign-table representation.
Operational Caveats
pg_extension_basemust be preloaded andpgduck_servermust be running with credentials for every referenced location.lake_read,lake_write, andlake_read_writecontrol access to servers, schemas, and utilities. Grant the narrowest role required by each application.- External tables are references to files, not imported copies. File replacement, cross-region access, and cache invalidation can change latency or results independently of PostgreSQL catalog state.
- Iceberg inserts are optimized for batches rather than single rows. Use a staging heap table for high-rate row-at-a-time ingestion and periodically flush batches.
- Internal
lake_table.*catalogs track files, field IDs, partitions, and recovery state. Do not modify them directly.
Version 3.5 adds indexes to file-deletion mappings, column statistics, and partition-value catalogs to avoid repeated child-catalog scans during bulk removal. Apply the SQL migration as well as installing the new library.
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 table component registers a separate object-store catalog-export worker and changes catalog-name helpers to STABLE. Apply its SQL migration; replacing binaries alone does not register the worker.
Upstream 3.6.1 catalog behavior
Read-only tables attached with catalog = 'snowflake' and inferred columns now default lowercase_column_names to true. Other documented read-only catalog cases retain their existing default; the option is fixed when the table is created and does not rename existing columns. Review inferred names and case collisions when attaching new tables.
The family retains SQL version 3.6. Deploy matching extension libraries and pgduck_server: the patch also includes a Brotli decoder security correction in the query server. Existing package versions are recorded independently and are not evidence that this upstream patch is installed.
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.
For newly attached read-only catalog = 'snowflake' tables, lowercase_column_names defaults to true and is fixed at creation. Case collisions are rejected. out_of_range_values = 'error' rejects infinite intervals on PostgreSQL 17+; clamp stores them as NULL without a warning. Review this loss of information before enabling it.