Limits and isolation¶

Installation: direct access to the cloud database is closed¶

The proxy keeps organizations apart. A connection of the application role to the cloud database that bypasses the proxy sees and changes every organization's rows, as without LokaDB. That is outside LokaDB's responsibility, so close it:

pg_hba of the cloud database admits the application role only from the proxies' addresses (and the relay's, if it connects as that role), and rejects it from everywhere else. Put these lines above the general ones:
```
host  all  app  PROXY1_ADDR/32 scram-sha-256
host  all  app  PROXY2_ADDR/32 scram-sha-256
host  all  app  all           reject
```
The owner (for enable, move-org) and administrators get lines of their own.

A logical move also needs a line for its temporary role. The customer database subscribes to the cloud as lokadb_repl_<oid>_org<N> (created by move-org, dropped after the move), and the connection reaches the cloud from the relay's address. Put the line before the general reject, or the move fails at the subscription step with a pg_hba refusal:

host  all  /^lokadb_repl_  RELAY_ADDR/32  scram-sha-256

(/^lokadb_repl_ is the regular-expression form of the role name in pg_hba.conf.) Moves by triggers (-mode trigger, which auto picks) do not use this role and need no such line. 2. Network: the cloud database is not reachable from application, user or customer networks; only the proxies, the relay and administrators' machines reach its port. 3. Verify: lokadb check -proxy-hosts PROXY1_ADDR,PROXY2_ADDR … reads the cloud's pg_hba rules and reports any rule that admits the application role from another address (cloud_direct_access).

The application role must not bypass RLS: the proxy refuses superusers, roles with BYPASSRLS or REPLICATION, and sessions that can SET ROLE into such a role (FATAL 28000, with the ALTER ROLE that fixes it). Administrative commands (enable, move-org) connect to the cloud directly and may run as a superuser.

What LokaDB does not isolate¶

LokaDB is responsible for one thing: the rows of the LokaDB tables of a moved organization are stored only in its database. Whatever the application and the cloud do with data outside these tables stays on the SaaS side. Promise customers full isolation or data residency only together with this list.

The application's own stores. LokaDB does not see them, and check does not mention them: caches, queues, sessions (Redis and others); search indexes (Elasticsearch, OpenSearch, Meilisearch…); files and attachments (S3…); the application's logs, traces and error reports (Sentry…) — values from rows often end up there; analytics and warehouses the application exports to. Keep the moved organization's data out of them, or keep those stores with the organization too.

Materialized views over LokaDB tables store their rows in the cloud database; after a move, REFRESH reads moved organizations from their databases and stores their rows (or what is computed from them) in the cloud again. check warns (matview_stores_moved_rows). Restrict the view to cloud organizations by a condition on the tenant column, or replace it with a plain view or an application-filled table.

Copies made before the move. Backups and snapshots of the cloud database taken before move-org contain the organization's rows until they expire. Replicas and CDC (publications, Debezium) already passed them on; after the move its changes no longer flow there, but what was exported stays. Rows without an organization stay in the cloud.

What remains in the cloud for each moved organization: <table>__refs (each row's key and organization number, no content); metadata (lokadb.orgs, lokadb.lookups, the connection credentials of its database in the user mapping, the move log while a move runs); cloud tables that were not chosen (reference data, users, plans…) in full.

Data in transit through the cloud (profile fdw). During a query, a moved organization's rows pass through the cloud Postgres: they are in its memory, a large sort or hash can spill them briefly to temporary files on its disk, and its log can keep them (log_statement, log_min_duration_statement, auto_explain write statement text with values; constraint errors write key values). Turn those settings off in the cloud or treat the cloud log as customer data. The cloud database administrator can see these rows while they pass. If that is not acceptable for a customer, use the direct profile: its statements go straight to its database, around the cloud Postgres, and the cloud database can be left without credentials to reach it.

LokaDB itself. The proxy log writes statements without values (literals replaced by $n); the workload recording keeps normalized statements without parameter values. The relay sees Postgres traffic inside the tunnel in clear unless TLS runs end to end to the organization's database (tunnel.md).

Not an authorization layer. LokaDB does not widen the set of rows a query can reach, but it does not narrow it to the user's own organization either: if the application asks for another organization's document by id, the proxy routes the query to where that document lives. The application still checks who may read what. In cooperative mode the proxy additionally refuses a statement that names an organization other than the one the application declared.

Isolation rests on the proxy. lokadb.org is an ordinary session variable; the RLS policies are routing, not a barrier. The proxy takes the organization only from the statement, so it can narrow a statement but not widen it: a wrong organization gives an empty or partial answer, never another organization's rows. The proxy refuses statements and startup parameters that set lokadb.* themselves, and statements that create, change or drop LokaDB's policies or turn off RLS of LokaDB tables.

Transactions behave differently. There is no atomic commit across the cloud and an organization's database (two-phase commit is not possible with postgres_fdw): remote commits come before the local one, and a failure between them leaves a row without its __refs entry (refs-diff / refs-sync find and fix it). In the organization's database postgres_fdw opens REPEATABLE READ transactions even if the cloud uses READ COMMITTED: a statement on a moved organization sees the snapshot of its remote transaction's start, and may get a serialization error (40001) under concurrent writes where it did not before. Treat 40001 as retryable in the application.

How many organizations one cloud database can hold¶

LokaDB makes one partition per organization. What grows with their number (measured: 32 clients through the proxy; the full table is in performance.md):

A statement of one organization grows slowly. The proxy adds the partition-key condition, so the planner takes one partition at once. A point read of one organization takes 0.55 ms with one organization and 1.1 ms with 3000; throughput of reads, updates and inserts does not fall up to 100 organizations.
Memory of the cloud backend grows with the number of partitions. LokaDB's triggers are compiled for every partition a backend touches, so a backend that writes into many partitions (or reads from all of them) holds memory in proportion to their number. At 1000 organizations, compared with 100, reads lose about 47 %, updates about 30 % and inserts about 37 %; at 3000 inserts fall another 3.5 times (1,432 ops/s, p99 116 ms), because mixed writes become bound by the memory of the cloud database.
The first statement of a new connection grows with the number of partitions: 1.6 ms with one organization, 14.8 ms with 3000 (the backend loads their metadata). With upstream_pool (on by default for all databases) it is paid once per pooled connection, not once per client.
Deleting a document while the organization is in the cloud grows with the number of organizations: the foreign key is checked on every partition.
Prepared statements: with the extended protocol, a statement with an organization condition is prepared once per pair "application statement × organization" on the connection to the cloud and then only executed. A connection keeps up to keyed_statements such pairs (default 1024, about 25 KB of cloud backend memory each) and closes the least recently used when it needs a new one. If a connection works with more pairs (thousands of organizations interleaved on one connection), statements are prepared and planned anew on almost every call: planning costs 0.5–0.8 ms under load, about 80 % of the cost of a point read in the cloud. A larger keyed_statements (memory) or fewer organizations per connection helps. Postgres plans the first five executions of any prepared statement anew in any case.

In practice: up to about 100 organizations there is no noticeable cost; at about 1000 it works, with the lower throughput above, and the cloud database needs plenty of memory for mixed writes; at about 3000, reads (12.6 thousand ops/s) and updates (15.9 thousand) work, and mixed writes are not recommended yet (a change to the triggers is planned and may move these limits).

Known limitations¶

Area	Limitation
Finding the organization	Recognized: `=`, `IN`, `= ANY(array)` on the tenant column, key or a foreign key in `AND` conditions; `VALUES` and `UNNEST` in inserts; join equalities; subqueries that name the organization. Not recognized (need an `allow` entry under `reject`): `<> ANY`, `OR` between keys, subqueries in the select list, other `INSERT … SELECT`. A value that does not read as a key (`NULL` in an array) is treated as no organization.
Keys and organizations	Keys: integer types, `uuid`, `text` / `varchar` with a deterministic collation, or several such columns. Organization column: integer or `uuid`.
Upsert and `MERGE`	For a moved organization on `fdw`, upsert is rewritten (`NOT EXISTS` + `INSERT`) and serialized per table and organization by an advisory lock in the cloud. Not covered: a multi-statement query string; a transaction that read the organization's database before the upsert (it may get a unique violation for a concurrent insert). `xmax` in `RETURNING` does not tell insert from update. `MERGE` supports one `WHEN MATCHED` and one `WHEN NOT MATCHED` branch; the command tag is `INSERT 0 n` or `UPDATE n`. On `fdw+direct`, upsert runs natively in the organization's database.
Aggregates through `postgres_fdw`	A single-table query of one moved organization goes straight to its foreign partition, and `GROUP BY` and `ORDER BY … LIMIT` run there if the partition has statistics: autovacuum does not analyze foreign tables, so run `ANALYZE <parent table>` while organizations grow. Queries across organizations, joins of two LokaDB tables, a LokaDB table on the nullable side of an outer join, or in a subquery go through the parent and aggregate in the cloud. `now() - interval` is never pushed down (a stable function): use a parameter.
`COPY FROM`	Works, but on `fdw` it is expensive: rows go to the organization one by one with per-row triggers, so a large import into a moved organization is many times slower than into a plain table. Import into a moved organization on `fdw+direct` with the organization named (`SET LOCAL lokadb.tenant`): `COPY` then goes straight to its database. `check` reports `copy_from_cost`.
Prepared statements	Survive `enable` and DDL: they are re-analyzed on `Bind`. A statement prepared with SQL `PREPARE` cannot be executed through protocol `Bind` (error `26000`).
Transactions	Two commits, the organization first (see above).
`enable` under writes	`enable` converts the tables under a short lock, and the downtime of the switch does not grow with the table (`enable -prepare` does the heavy work online). A write to a child table (`document_versions` under `documents`) that the proxy still took for a plain table and that waited for the lock can get, once, the error `lokadb: the row reached the table without its organization … retry the statement` (SQLSTATE `23503`) and not be written: the child's organization column is filled from `lokadb.org`, which the proxy had not set for that statement. Writes to a table with an explicit organization are not affected. Repeat a statement outside a transaction; inside one, repeat the transaction. A retry by the proxy itself is planned. Do not read this page as "zero errors during `enable`".
Moving	One organization at a time per cloud database. The switch-over pause covers reads and writes of all organizations of the moved tables (seconds at a distance of tens of milliseconds, see performance.md). The first move of an organization starts by splitting it out of the DEFAULT partition: the copy runs while it works, and it is paused only at the end, for a time that grows with its volume (about 2 s per million rows of a table). Application DDL during a move is refused. Writers that bypass the proxy during a move get errors, not lost writes.
Partitions	One partition per organization; a statement of one organization does not get slower with their number. Not narrowed: the nullable side of outer joins, tables inside a join that has its own alias (`(documents d JOIN users u ON …) j` hides `d` and `u`) or column aliases (`documents d(i, o)`), statements the proxy rewrites (upsert), statements across organizations — their cost grows with the number of partitions. A unique index from a migration is unique within each partition. A feed query (`WHERE organization_id = $1 ORDER BY id DESC LIMIT 20`) needs an index on `(organization_id, id)`, as without LokaDB.
DEFAULT partition	Postgres prunes a LIST DEFAULT partition only by equality, so every statement of an organization also probes the cloud DEFAULT partition — through an index on the partition key, not a scan.
DDL	Replayed: column changes, `RENAME COLUMN`, indexes. Refused: table renames, `DROP TABLE`, constraints, `DROP INDEX` of LokaDB tables, `TRUNCATE`. See migrations.md.
Integrity	Foreign keys from LokaDB tables to cloud tables are kept by LokaDB's triggers for writes through the cloud, not deferred to commit. Writes made directly in a customer database are not checked and are not in `__refs` until `refs-sync`. `ON DELETE CASCADE` between LokaDB tables of a moved organization costs one extra read per deleted parent row.
Cursors, `LISTEN` / `NOTIFY`	Work on every profile, including `WITH HOLD`. In a direct transaction a cursor on LokaDB tables is refused once (`0A000`) and such transactions then go through the cloud. Two `WITH HOLD` cursors of different organizations in one transaction, and one query string fetching from cursors of two organizations, are refused.
Poolers	In front of the proxy — any mode, including transaction (the proxy sets `lokadb.org` before each statement). Between the proxy and Postgres — only a direct connection or a pooler in session mode (`"upstream_pooling": "session"`).
Connection cache	`upstream_pool` is on by default for all databases (`"upstream_pool": false` turns it off). A password change, `NOLOGIN`, `VALID UNTIL`, a `pg_hba` change or `ALTER ROLE … SET` reaches pooled connections only after `max_age` (5 min) — at once after `kill -HUP` of the proxy. Named `dblink` connections and `LOAD`ed libraries survive `DISCARD ALL` and go to the next client of the same role: whoever uses them should turn the cache off or give those clients a role of their own.
Tunnel	Revocation reaches live sessions within two seconds only while the registry is reachable. An agent that was off longer than its certificate's lifetime plus `renew_expired` needs a new token. Enrolment failures are limited per client address; behind an L4 balancer without the client address all clients share one.
`check`	Sees only statements already run; samples orphan and `NULL` counts on tables over 1M rows (`-count-limit -1` counts exactly); after `enable` checks only the workload. Reading other roles' `pg_stat_statements` needs `pg_read_all_stats`.
Not tested yet	Many concurrent clients at scale; organizations above 300 000 rows on the e2e stand; moving an organization of 10+ GB over a WAN; organizations created after `enable` in real applications. These are part of the measurement program.