Many databases

pondra serve on a folder of lakes serves one database per lake, behind one HTTP port and one Postgres port. A Postgres client picks a database by name (psql -d sales), as it would on Postgres. An HTTP client adds /db/sales to its URL. Use it when one machine holds several lakes and you don’t want a port, a process and a service for each.

pondra serve data --addr 0.0.0.0:8080 --pg 0.0.0.0:5432 --admin-token "$TOKEN"

Each lake in data (data/sales, data/crm, …) is a database, named after its folder. The console at http://host:8080/ lists them all.

pondra serve data looks at the folder to decide what it is. If data holds a lake (it has a catalog/ folder), it serves that one lake. If its subfolders hold lakes, it serves each as a database, as here. If data is new or empty, it makes a lake there. For a service, say which you mean: pondra serve --lakes /srv/data serves exactly this folder of lakes and guesses nothing. The command line has the rules.

Try it

Make two lakes in a folder, serve the folder, and ask one database about the other:

pondra sql --dir data/sales "CREATE TABLE orders AS SELECT 1 AS id, 10.5 AS amount UNION ALL SELECT 2, 20.0"
pondra sql --dir data/crm "CREATE TABLE customers AS SELECT 1 AS id, 'ann' AS name UNION ALL SELECT 2, 'bob'"

pondra serve data --addr 127.0.0.1:8088 &
until curl -s 127.0.0.1:8088/databases > /dev/null; do sleep 0.2; done

curl -s 127.0.0.1:8088/databases
curl -s 127.0.0.1:8088/db/sales/sql -d "SELECT c.name, o.amount FROM orders o JOIN crm.customers c ON c.id = o.id ORDER BY o.id"
kill %1

[{"name":"crm","running":false,"default":false},{"name":"sales","running":false,"default":false}]
[{"name":"ann","amount":10.5},{"name":"bob","amount":20.0}]

Neither database ran until the query named sales. Open http://127.0.0.1:8088/ while the server runs and the console lists both.

How it works

The process that serves the folder holds no lake itself. Each database runs as a node of its own, a pondra serve on that lake, which the process starts as a child process the first time a connection or a request names it. The process passes that database’s traffic through to its node:

• Postgres connections by the database in their startup message;
• HTTP requests under /db/{name}/…, which is that database’s whole API.

A database stops once no connection to it is open, no request is on its way, nothing has come for PONDRA_DATABASE_IDLE_SECS (600 seconds), and it has nothing left to tier. A statement that runs longer than that, or a connection a BI tool keeps open, keeps it running. The next query starts it again: on a local disk here the first answer took about 70 ms, and the ones after it about 1 ms. So a database nobody uses costs nothing, and each one keeps everything a node does: leading its cluster, tiering, views, tasks and recovery after a crash.

Connect

Python

import pondra

sales = pondra.connect("http://host:8080/db/sales", token=TOKEN)
sales.sql("SELECT sum(amount) FROM orders").item()

JavaScript

import { connect } from "pondra";

const sales = connect("http://host:8080/db/sales", { token: TOKEN });
console.log(await sales.sql("SELECT sum(amount) AS total FROM orders"));

Shell

curl -H "Authorization: Bearer $TOKEN" host:8080/db/sales/sql -d 'SELECT sum(amount) FROM orders'

psql

PGPASSWORD="$TOKEN" psql -h host -p 5432 -U admin -d sales

• A request without /db/ goes to the default database: --default, else lake, else the only one. So a client written for a node of one lake works unchanged.
• The Postgres user says which token the password is (admin, writer, or any other name for the read token), as on a node of one lake.
• An unknown database is an error with Postgres’s own code (3D000, database "nope" does not exist), so tools report it the usual way.

Make and drop databases

From any database, in SQL:

CREATE DATABASE hr;
DROP DATABASE hr;

• CREATE DATABASE hr makes the lake data/hr and serves it. POST /databases with {"name": "hr"} does the same over HTTP, and the console’s + does it too.
• DROP DATABASE hr stops its node and deletes its folder, as Postgres drops a database’s files. It needs an admin token, and you run it from another database, as in Postgres. DROP DATABASE IF EXISTS is quiet about one that isn’t there. On a node that serves one lake, there is no folder of lakes to drop it from, so it says to use DETACH instead.
• GET /databases lists them, with whether each is running and which is the default:

[{"name": "crm", "running": false, "default": false}, {"name": "sales", "running": true, "default": true}]

Query across databases

Every database sees the others as name.schema.table, so one query can join them:

SELECT c.name, sum(o.amount) AS spent
FROM orders o
JOIN crm.customers c ON c.id = o.customer_id
GROUP BY c.name;

The query runs on the node of the database you are connected to, and reads the other lake’s files directly. No data moves between nodes first.

Who may use which database

Each database has its own users and grants, made in it with CREATE USER and GRANT (users, roles and grants). The server’s tokens are every database’s. A user signs in to one database, and is nobody in the others:

• Another database that signs in on its own (it has a user, or the server has tokens) is read and written only through itself, or with one of the server’s tokens. Its superuser can’t read it from the database next door either.
• Another database with no sign-in is open to anyone, so it is open from the next one too. A user granted some tables still needs a grant naming its table there:

GRANT SELECT ON crm.customers TO ann;   -- made in sales: ann reads crm.customers through sales

ON ALL TABLES and ON SCHEMA grants cover their own database’s tables only. The same holds at every door: SQL over HTTP and Postgres, listings such as pondra.tables, and the Iceberg catalog.

Clusters of one database

A database’s node can lead a cluster. It advertises itself as host:8080/db/sales, so a node on another machine joins that database’s cluster through the process that serves the folder:

pondra serve /shared/data/sales --addr 10.0.0.6:8080

For a lake in a bucket, run pondra serve s3://bucket/sales on as many machines as you like; Clusters explains how the nodes share the work.

A folder in a bucket

The folder of lakes can be a bucket’s prefix, as a lake can:

pondra serve --lakes s3://bucket/data --addr 0.0.0.0:8080 --pg 0.0.0.0:5432 --admin-token "$TOKEN"

Each lake under the prefix (s3://bucket/data/sales, …) is a database, CREATE DATABASE hr makes s3://bucket/data/hr, and DROP DATABASE deletes that prefix’s objects. The server lists the prefix to find its databases, a few requests each time it is asked (/databases, the console, a connection that names a database it hasn’t started). On R2 or S3 that takes a moment, so name the folder with --lakes rather than have it looked at, and keep the prefix for lakes. The server and each database’s node read the bucket’s credentials from the environment, as a node of a lake in a bucket does.

Options

The options of pondra serve that matter for a folder of lakes. The command line has the rest.

Option	What it does
PATH	The folder of lakes (default the current folder). Each subfolder that holds a lake is a database.
--lakes FOLDER	Exactly this folder of lakes, for scripts and services. Nothing is guessed
--addr	Where HTTP clients, the console and other nodes reach the server (default 127.0.0.1:8080)
--pg	Speak the Postgres protocol here, such as 0.0.0.0:5432
--default NAME	The database a client that names none gets (default lake, else the only one)
--read-token, --write-token, --admin-token	Tokens, as on a node of one lake. Every database’s node gets them
--tls-cert, --tls-key, --tls-ca	TLS on the HTTP and Postgres ports, as on a node of one lake (TLS)
--python	Python for functions, procedures and the console’s Python cells, as pondra serve --python
Every other option	Applies to each database’s node: --reader (every database read-only), --tier-secs, --memory-gb, --cache-dir, --cache-gb, --ack, --replicas, --fsync, --publish, --flush-ms, --task-ms, --retain-secs, --changelog-secs and --backlog

Each database is a process of its own, so an option such as --memory-gb is for each database’s node, not for all of them together.

PONDRA_DATABASE_IDLE_SECS sets how long an unused database runs (600 seconds by default).

What a folder of lakes doesn't do yet

• Flight and Kafka ports. A Flight or Kafka port speaks for one lake today, so a folder of lakes refuses --flight, --kafka (and --kafka-advertise), and --attach, --advertise and --attach-found too, each with a message that names the flag. Routing them to many databases (Kafka by topic, such as sales.orders, and Flight by the database the client names) is planned for the next round. Serve such a lake on its own: pondra serve data/sales --flight 0.0.0.0:8815.
• Per-database settings and users made once. ALTER DATABASE sales SET … is planned, and so are users made once for every database. Today each database has users of its own, and the tokens are the same for every database.