pg_lake: Postgres Meets the Data Lakehouse

Snowflake for Developers/Postgres/pg_lake: Postgres Meets the Data Lakehouse

On this page

pg_lake: Postgres Meets the Data Lakehouse

Postgres

Elizabeth Christensen

What is pg_lake?

pg_lake is a Postgres extension that bridges your transactional database with the data lakehouse. It enables you to read and write Apache Iceberg tables directly from Postgres SQL, without leaving the database environment.

Key Capabilities

Read Iceberg tables — Query data stored in cloud object storage (S3, GCS, Azure Blob) as if it were a local Postgres table.
Write Iceberg tables — Export Postgres data to Iceberg format for consumption by analytics engines.
Parquet support — Read and write Parquet files for interoperability with the broader data ecosystem.
Open source — Works with any S3-compatible object storage and standard Iceberg catalogs.

Why This Matters

Traditional architectures require separate ETL pipelines to move data between your operational database and your analytics lakehouse. pg_lake eliminates this middleman, letting Postgres participate directly in the lakehouse ecosystem.

Exporting Data to the Lakehouse

Use Cases for Export

Analytics offloading — Move historical or aggregated data to the lakehouse where columnar engines can process it efficiently.
Data sharing — Publish datasets in open formats that any tool in the ecosystem can consume.
Archival — Move cold data out of Postgres while keeping it queryable through foreign tables.

Export to Parquet

-- Export a table to Parquet in S3
SELECT pg_lake.export_to_parquet(
  'public.orders',
  's3://my-bucket/exports/orders/'
);

This writes the entire orders table as Parquet files to the specified S3 path. Parquet's columnar format provides excellent compression and fast analytical reads.

Write to Iceberg

-- Write to an Iceberg table
SELECT pg_lake.write_to_iceberg(
  'public.events',
  's3://my-bucket/iceberg/events/',
  partition_by => ARRAY['date(created_at)']
);

Iceberg adds table-level features on top of Parquet: schema evolution, time travel, partition evolution, and ACID transactions at the file level.

Incremental Exports

For ongoing synchronization, use change-tracking exports that only write new or modified rows:

-- Export only rows modified since the last export
SELECT pg_lake.incremental_export(
  'public.orders',
  's3://my-bucket/iceberg/orders/',
  since => '2026-05-01'::timestamptz
);

aside negative Large initial exports can take significant time and generate substantial I/O. Schedule initial bulk exports during low-traffic windows.

Querying External Data from Postgres

pg_lake registers a foreign data wrapper that lets you create foreign tables pointing to files in cloud storage. Queries against these tables are pushed down and optimized for the underlying format.

Create a Foreign Table

-- Create a foreign table pointing to Parquet files in S3
CREATE FOREIGN TABLE historical_orders (
  order_id   bigint,
  customer_id bigint,
  total      numeric(10,2),
  created_at timestamptz
)
SERVER pg_lake_server
OPTIONS (
  path 's3://my-bucket/iceberg/orders/',
  format 'iceberg'
);

Join Operational and Historical Data

The real power is seamlessly joining hot operational data in Postgres with cold historical data in the lakehouse:

-- Join operational and historical data
SELECT c.name, sum(o.total) AS lifetime_value
FROM customers c
JOIN historical_orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY lifetime_value DESC;

This query joins a local Postgres table (customers) with an Iceberg table in S3 (historical_orders) — no data movement required.

Supported Formats

Format	Read	Write	Features
Iceberg	Yes	Yes	Schema evolution, time travel, partition pruning
Parquet	Yes	Yes	Columnar, compressed, widely supported
CSV	Yes	No	Simple interchange
JSON	Yes	No	Semi-structured data

Bidirectional Pipelines with Snowflake

Postgres → Snowflake

Sync operational data from Postgres to Snowflake for analytics, machine learning, and reporting. pg_lake writes Iceberg tables that Snowflake can read natively through its Iceberg Tables feature.

Common patterns:

Continuous sync of transactional data for real-time dashboards
Periodic snapshots for ML training datasets
Event streams for behavioral analytics

Snowflake → Postgres

Push enriched data and ML predictions back to Postgres for use in your application:

Enriched customer profiles from Snowflake ML models
Aggregated metrics for application display
Predictions and scores that drive application logic

Apache Iceberg as the Interchange Format

Both Postgres (via pg_lake) and Snowflake speak Iceberg natively. This creates a clean integration point:

Postgres writes Iceberg tables to shared object storage
Snowflake reads those tables for analytics and ML
Snowflake writes results back as Iceberg tables
Postgres reads the enriched data via foreign tables

No proprietary formats, no vendor lock-in, no fragile ETL connectors.

aside positive Snowflake Postgres supports zero-ETL data sharing with the Snowflake AI Data Cloud. Data flows automatically without building or maintaining pipelines.

Common Use Cases

IoT and Time-Series

Postgres handles real-time ingestion and recent queries. pg_lake archives older partitions to the lakehouse where they remain queryable at lower cost.

-- Archive partitions older than 90 days
SELECT pg_lake.write_to_iceberg(
  'public.sensor_readings_2026_02',
  's3://my-bucket/iceberg/sensor_readings/',
  partition_by => ARRAY['device_id', 'date(recorded_at)']
);

-- Drop the local partition after confirming export
DROP TABLE sensor_readings_2026_02;

Feature Stores for ML

Store feature vectors and training data in Iceberg format for consumption by ML pipelines, while keeping serving features in Postgres for low-latency inference.

Regulatory Archival

Meet data retention requirements by archiving to immutable Iceberg tables in object storage. Iceberg's time-travel capability provides point-in-time access for audits.

Multi-System Analytics

Query across Postgres, Snowflake, Spark, Trino, and any other Iceberg-compatible engine using a single open table format.

Conclusion

Next Steps

Explore the Scaling guide for partitioning strategies that complement lakehouse archival
Read the AI in Postgres guide to learn how embeddings can flow between Postgres and Snowflake
Check out Snowflake Postgres Getting Started for initial setup

Updated 2026-07-08

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances