> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oleander.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Postgres

> Query a live Postgres database from the lake, and import tables into Iceberg with a parallel, snapshot-consistent read.

Register a Postgres database and it becomes a live, read-only catalog in the lake. Query it directly for ad hoc work, or import tables into Iceberg when you want a snapshot you can run large jobs against.

Connections are managed in [Settings → Lake](https://oleander.dev/app/settings/lake).

<Tip>
  For production databases, point oleander at a read replica with a dedicated read-only user.
</Tip>

## Setup

1. Open [Settings → Lake](https://oleander.dev/app/settings/lake) and choose **Add Postgres connection**.
2. Paste a connection string to fill the fields automatically, or enter them by hand:

| Field               | Description                                                                                              |
| ------------------- | -------------------------------------------------------------------------------------------------------- |
| **Connection name** | Lowercase letters, numbers, and underscores, e.g. `my_postgres`. This becomes the catalog prefix in SQL. |
| **Host**            | Database host, e.g. `db.example.com`                                                                     |
| **Port**            | Defaults to `5432`                                                                                       |
| **Database**        | The database to expose                                                                                   |
| **SSL mode**        | Defaults to `prefer`                                                                                     |
| **Username**        | The role oleander connects as                                                                            |
| **Password**        | Stored encrypted and never returned to the browser                                                       |

The dialog generates the SQL for a read-only user if you want to create one - copy it and run it against your database before saving.

## Querying live

Tables are reachable as `connection_name.schema.table`:

```sql theme={null}
-- Read straight from Postgres
SELECT id, email, created_at
FROM my_postgres.public.users
WHERE created_at >= '2026-01-01'
LIMIT 100;

-- Join Postgres against your Iceberg lake
SELECT u.email, count(*) AS events
FROM my_postgres.public.users u
JOIN oleander.default.events e ON e.user_id = u.id
GROUP BY 1;
```

<Note>
  **A Postgres table selects DuckDB.** DuckDB is the only engine that attaches external connections, so the [query router](/platform/query-routing) routes any query naming a connection table here before it considers input size - leave `engine` on `auto`. Asking for Bloom, Polars, or Spark on one of these queries returns an engine capability error rather than rerouting silently.
</Note>

Live reads run against your database, so treat them the way you would treat any query against production: filter, limit, and prefer a replica. Once a table is [imported](#importing-into-iceberg) it lives in Iceberg and the router is free to send it to any engine.

## Importing into Iceberg

For anything larger than an ad hoc read, import the table. The import runs as a Spark JDBC read and lands the data as an Iceberg table in your lake, where every engine can reach it.

The import plans itself from Postgres catalog metadata:

* Partition count follows table size.
* Partition ranges come from the integer primary key, balanced by histogram, or from physical `ctid` chunks when there is no suitable key.
* Every partition reads from **one exported MVCC snapshot**, so the copy is consistent even against a live, changing database.

Reads run with a bounded number of connections against the source. It is suitable for very large tables.

The destination defaults to `oleander.default.<connection>_<table>`. `OVERWRITE` replaces it, `APPEND` adds to it.

<Tip>
  For large tables, prefer more executors (4-8) over bigger machines. Each executor core opens one connection to the source database.
</Tip>

An import does not return rows. It submits a job and returns a `run_id` you can monitor like any other run, and once it completes you can sample the destination with a normal query.

## Table syncing

A one-off import gives you a snapshot. Put the same import on a schedule and the Iceberg copy tracks the source instead.

<Note>
  **Scheduled syncs are incremental.** After the first full import, each run picks up **new and changed rows only** rather than recopying the table, so a sync over a large table costs a fraction of the initial import.
</Note>

Schedules run **hourly** or **daily**. Each one is a `connection.source_table → destination_table` pair, listed under **Scheduled syncs** in [Settings → Lake](https://oleander.dev/app/settings/lake) with its cadence and next run time, where it can be paused or removed.

Every sync run is a normal oleander run: it appears in run history, emits lineage with the Postgres table as the input and the Iceberg table as the output, and carries cost like any other job. A sync that starts failing or silently stops moving rows shows up in [observability](/observability/overview) the same way a pipeline regression does.

Pick a cadence against how fresh the lake copy needs to be, not how often the source changes - the incremental read is cheap, but each run is still a Spark job against your database.

## From an agent

Two tools cover Postgres over [MCP](/mcp/introduction):

| Tool                        | What it does                                                                                        |
| --------------------------- | --------------------------------------------------------------------------------------------------- |
| `postgres_connections_list` | List registered connections - name, host, port, database, username. Credentials are never returned. |
| `postgres_tables_import`    | Import a table into Iceberg with the partitioned, snapshot-consistent read described above          |

For ad hoc reads of live data an agent uses `query_run` against `connection.schema.table` instead.