LakeSoul LF AI & Data Sandbox Project

License PG wire pgvector Python Daft Release

中文介绍

LakeSoul

The agent-native lakehouse.
Postgres-native. Vector-native. Python-native. Apache 2.0.

Traditional data platforms are a patchwork of systems: a warehouse for analytics, a CDC pipeline for freshness, a vector database for retrieval, an OLTP database for state, and ETL to keep them consistent. Each system is another API for the agent to learn and another copy of the data to reconcile; a closed platform adds a lock-in you cannot undo.

LakeSoul takes another path. Agents already have PostgreSQL clients, so LakeSoul puts an open lakehouse behind the PostgreSQL wire protocol: federated queries over data where it lives, CDC subscriptions for freshness, vector and structured search in one plan, incremental SQL, and the same tables from a single process to a PB-scale distributed cluster.

Quick Start · Architecture · Docs

Postgres-native

Point any PostgreSQL client at LakeSoul. CREATE TABLE, INSERT ... ON CONFLICT, UPDATE, COPY ... FROM STDIN and queries run in the lakehouse engine, against object storage — not in a sidecar, and not through a CDC pipeline.

CREATE TABLE orders (
  id     bigint primary key,
  region text,
  amount numeric(20,4)
) WITH (hash_bucket_num = 8);

INSERT INTO orders VALUES (1, 'APAC', 42.00)
ON CONFLICT (id) DO UPDATE
SET region = EXCLUDED.region,
    amount = EXCLUDED.amount;

SELECT region, sum(amount) FROM orders GROUP BY 1;

The endpoint implements the PostgreSQL wire protocol, the extended query protocol, pg_catalog and psql \d. DDL, INSERT ... ON CONFLICT, UPDATE, COPY ... FROM STDIN, RETURNING and explicit transaction blocks execute in the lakehouse engine over object storage. The compatibility matrix tracks the exact SQL surface.

Federated query

Install the ADBC driver for the source database with dbc install postgresql, then expose a remote table as a foreign table and join it with local data:

CREATE EXTENSION adbc_fdw;

CREATE SERVER app_source
FOREIGN DATA WRAPPER adbc_fdw
OPTIONS (
  driver 'postgresql',
  uri 'postgresql://source:5432/app'
);

CREATE USER MAPPING FOR CURRENT_USER
SERVER app_source
OPTIONS (user 'reader', password '...');

CREATE FOREIGN TABLE remote_orders (
  id     bigint,
  region text,
  amount numeric(20,4)
)
SERVER app_source
OPTIONS (schema_name 'public', table_name 'orders');

SELECT u.id, u.name, sum(o.amount) AS total
FROM local_users u
JOIN remote_orders o USING (id)
GROUP BY 1, 2;

The same wrapper reads MySQL, SQLite, DuckDB, BigQuery, Snowflake and Trino; S3 Parquet, CSV and JSON files are foreign tables too.

PostgreSQL subscriptions

The same endpoint can subscribe to PostgreSQL and MySQL sources and keep LakeSoul CDC tables in sync. Define the subscription with SQL; the endpoint validates the source and target, takes a managed snapshot, and starts a supervised local subscriber process. One subscription can map multiple source tables.

CREATE SUBSCRIPTION users_from_app
CONNECTION 'postgresql://user:pass@source:5432/app'
PUBLICATION app_pub
WITH (
  source_table     = 'public.users',
  target_table     = 'users',
  snapshot         = 'managed',
  auto_create      = true,
  schema_evolution = 'auto'
);

The source needs logical WAL, a publication, and REPLICA IDENTITY FULL; the target is a LakeSoul CDC table that can be created automatically from the source schema or pre-created. The subscriber adds nullable columns and widens safe types when the source schema changes. The initial copy runs in parallel across CTID ranges (snapshot_parallelism, snapshot_memory_bytes). ALTER SUBSCRIPTION ... DISABLE/ENABLE and DROP SUBSCRIPTION control the process. A subscription can map one or more source tables; each table snapshots and commits independently. MySQL 8.0/8.4 sources use the same statement with TABLES (...); the subscriber reads the row-based binlog. See PostgreSQL subscriptions for the full setup and SQL reference.

Incremental SQL

With incremental SQL enabled for the session, an INSERT ... SELECT issued from psql updates its target incrementally: each run reads only the commits since its cursor and writes only the changed rows. The result is identical to a full recompute.

-- The target is a keyed table carrying the CDC change column "rowKinds".
-- This switch enables incremental SQL for the session.
SET lakesoul.ivm.enabled = on;

INSERT INTO daily_revenue
SELECT date_trunc('day', ts) AS day, region, sum(amount)
FROM orders
GROUP BY 1, 2;

Vector-native

pgvector-compatible SQL over ANN indexes that live next to the data. New rows update the index; there is no rebuild and no second system to operate.

-- documents.embedding is vector(768)
SELECT id, body, embedding <-> '[0.12, 0.98, ...]' AS distance
FROM documents
WHERE tenant_id = 'acme' AND category = 'contract'
ORDER BY embedding <-> '[0.12, 0.98, ...]'
LIMIT 10;

Python-native

The Python SDK reads object storage directly through the Rust IO layer. No gateway, no JDBC hop — Daft and Ray shard the read across workers, PyTorch gets a dataset, Pandas and DuckDB consume Arrow.

from lakesoul import LakeSoulCatalog

catalog = LakeSoulCatalog.from_env()
scan = catalog.scan("documents").select("id", "body")
table = scan.to_arrow_table()   # or .to_daft(), .to_ray(), .to_torch()

The same table your BI dashboard queries is the table your training job reads. Video frames, robot episodes and tensors live as blobs beside their metadata.

Authentication and Permissions

Clients connect with a username and password; the endpoint scopes each connection to the permissions granted to that user. Run separate compute clusters for different teams or agents when you also want to isolate load.

psql "postgresql://alice:******@lakesoul.example.com:5432/default"

See Authentication and Permissions for the full model.

Architecture

┌──────────────────────────────────────────────────────────────┐
│  psycopg · JDBC · RAG        Python SDK · Daft · Ray · Torch │
└───────────────┬──────────────────────────┬───────────────────┘
                │ PG wire                  │ native IO / Arrow
   ┌────────────────────┐  ┌────────────────────┐  ┌────────────────────┐
   │ Compute cluster A  │  │ Compute cluster B  │  │ Compute cluster C  │
   │ Subscription · FDW │  │ Incremental SQL    │  │ BI Query · ANN     │
   └──────────┬─────────┘  └──────────┬─────────┘  └──────────┬─────────┘
              ▼                       ▼                       ▼
┌──────────────────────────────────────────────────────────────┐
│  object storage (S3 / OSS / HDFS)  +  PostgreSQL metadata    │
└──────────────────────────────────────────────────────────────┘

Quick Start

Option A — the PostgreSQL endpoint

# 1. Metadata database
docker run -d --name lakesoul-pg -p 5432:5432 \
  -e POSTGRES_USER=lakesoul_test -e POSTGRES_PASSWORD=lakesoul_test \
  -e POSTGRES_DB=lakesoul_test postgres:18
docker cp script/meta_init.sql lakesoul-pg:/
docker exec -i lakesoul-pg sh -c \
  "PGPASSWORD=lakesoul_test psql -U lakesoul_test -f meta_init.sql"

# 2. The PostgreSQL endpoint: database "default" is the LakeSoul namespace
cargo run -p postgres-lakesoul -- --port 5433 \
  --warehouse-prefix file:///tmp/lakesoul/warehouse

# 3. Connect with any PostgreSQL client
psql "postgresql://lakesoul_test:lakesoul_test@127.0.0.1:5433/default"
CREATE TABLE t (id bigint primary key, v text);
INSERT INTO t VALUES (1, 'hello');
SELECT * FROM t;

Option B — Python

pip install lakesoul
from lakesoul import LakeSoulCatalog

catalog = LakeSoulCatalog.from_env()
print(catalog.scan("t").to_arrow_table().num_rows)

See the documentation for object storage configuration and the full local setup.

How it compares

A table format only describes file layout: no engine, no indexes, no incremental state. A database ships an engine, but owns the storage. LakeSoul is neither: the catalog, the storage format, the execution engine and the indexes are one codebase, while the data stays in your object storage, in open files.

Postgres extension Postgres + columnar mirror Managed Postgres LakeSoul
Data lives in your Postgres tables, or Iceberg through an FDW Postgres WAL plus a columnar copy the provider's storage your object storage, in open files
Executes in the Postgres process Postgres and the mirror the provider's service the lakehouse engine
Write model Postgres MVCC logical decoding into the mirror Postgres MVCC primary-key upsert + merge-on-read
Incremental compute — — — incremental SQL over the commit log
Vector index pgvector on heap — pgvector lake-resident, maintained on write
Self-hosted yes yes no yes, Apache 2.0

Categories, not vendor scorecards: examples are pg_duckdb and pg_lake, pg_mooncake, and Lakebase. The dated, sourced comparison lives in the docs.

Works with your stack

Community

LakeSoul is a sandbox project of LF AI & Data.

License

Apache License 2.0 — see LICENSE.