Tables and storage

Airhouse stores your tables with DuckLake, an open lakehouse format. DuckLake splits every table into two parts:

Data: Parquet files in S3-compatible object storage.
Metadata: the catalog of schemas, tables, columns, files, and snapshots, stored in Postgres.

Each Airhouse database has its own catalog and its own storage location, so data in one database is never visible from another.

Create and load tables

Create tables in the lake catalog. The default schema is main, and admins can create more schemas:

CREATE TABLE lake.main.orders (
  order_id    BIGINT,
  customer_id BIGINT,
  status      TEXT,
  total       DECIMAL(12, 2),
  ordered_at  TIMESTAMP
);

INSERT INTO lake.main.orders VALUES
  (1, 100, 'paid',    120.00, '2026-03-01 10:00'),
  (2, 101, 'pending',  35.50, '2026-03-01 11:30');

Create a table from a query:

CREATE TABLE lake.main.paid_orders AS
SELECT * FROM lake.main.orders WHERE status = 'paid';

For bulk loads from your machine, use COPY ... FROM STDIN (psql’s \copy). See Connect with psql.

Every committed write creates new Parquet files and a new snapshot. Load data in batches (multi-row INSERT, INSERT ... SELECT, or COPY) instead of many single-row inserts.

Update and delete

UPDATE and DELETE work as in Postgres:

UPDATE lake.main.orders SET status = 'paid' WHERE order_id = 2;
DELETE FROM lake.main.orders WHERE status = 'cancelled';

Transactions

DuckLake gives you ACID transactions. Each statement runs in its own transaction unless you group statements explicitly:

BEGIN;
INSERT INTO lake.main.orders VALUES (3, 102, 'paid', 80.00, '2026-03-02 09:15');
UPDATE lake.main.orders SET status = 'refunded' WHERE order_id = 1;
COMMIT;

Other connections see your changes only after COMMIT. Use ROLLBACK to discard them. Writes to the same database are committed one at a time, while reads never wait for writes. Writes to different databases don’t affect each other.

Schema changes

Adding, renaming, and dropping columns only changes catalog metadata. Existing Parquet files aren’t rewritten, so these changes are instant even on large tables:

ALTER TABLE lake.main.orders ADD COLUMN channel TEXT;
ALTER TABLE lake.main.orders RENAME COLUMN channel TO sales_channel;
ALTER TABLE lake.main.orders DROP COLUMN sales_channel;

Snapshots and time travel

Every committed change creates a new snapshot of the catalog. List the snapshots of your database:

SELECT * FROM lake.snapshots();

Query a table as it was at an earlier snapshot or point in time:

-- As of snapshot 3
SELECT * FROM lake.main.orders AT (VERSION => 3);

-- As of one hour ago
SELECT * FROM lake.main.orders AT (TIMESTAMP => now() - INTERVAL 1 HOUR);

Time travel is useful for auditing changes, debugging a pipeline, or recovering rows deleted by mistake:

INSERT INTO lake.main.orders
SELECT * FROM lake.main.orders AT (VERSION => 3)
WHERE order_id = 2;

Views

Admins can create views to share reusable query logic:

CREATE VIEW lake.main.revenue_by_day AS
SELECT date_trunc('day', ordered_at) AS day, sum(total) AS revenue
FROM lake.main.orders
WHERE status = 'paid'
GROUP BY ALL;

Where your data lives

When you run Airhouse yourself, you choose the object storage bucket (see Configuration). Each database’s Parquet files are stored under its own prefix in that bucket, and its catalog lives in Postgres. Because the data is standard Parquet and the catalog is standard DuckLake, your data stays in open formats.

Get Started

Concepts

Connect

Guides

Reference

Tables and storage

Create and load tables

Update and delete

Transactions

Schema changes

Snapshots and time travel

Views

Where your data lives

​Create and load tables

​Update and delete

​Transactions

​Schema changes

​Snapshots and time travel

​Views

​Where your data lives

Create and load tables

Update and delete

Transactions

Schema changes

Snapshots and time travel

Views

Where your data lives