# Postgres Schemas Behind FastAPI: Nine Namespaces, Not Walls

- Author: Abdullah Chaudary
- Category: Full-Stack
- Published: 2026-10-16T21:37:01.587Z
- Updated: 2026-10-16T21:37:01.613Z
- Reading time: 11 min
- Tags: Postgres, FastAPI, SQLAlchemy, Alembic, Database Design

> Nine Postgres schemas in one database held 5 connections where nine databases held 45. Map with __table_args__, set Alembic include_schemas, and expect foreign keys to cross schemas.

On 16 October 2026 I ran the same 50-request load against two layouts: nine databases left 45 connections open afterwards, and one database with nine schemas left 5, on a server capped at 100. Schemas are the cheaper answer, but they are namespaces and permission units, not walls: foreign keys cross them.

This post covers Postgres multiple schemas behind FastAPI and SQLAlchemy async: mapping, sessions, joins, Alembic and grants. It is about fixed domain schemas, not schema-per-tenant.

I architected a multi-country CRM platform on FastAPI, SQLAlchemy 2.0 async and PostgreSQL 16 that keeps 33 tables in nine schemas: `auth`, `leads`, `scraping`, `audit`, `enrichment`, `routing`, `deals`, `comms` and `messaging`. Asked what has broken because of that layout, my answer was nothing, so the failures below come from a demo I built on 16 October 2026 with the platform's nine schema names, 10 invented tables and sample rows. The code is the demo's, not the platform's. Its versions (PostgreSQL 16.13, Python 3.12.15, SQLAlchemy 2.0.54, asyncpg 0.32.0, Alembic 1.20.0, FastAPI 0.143.3) are newer than the platform's (SQLAlchemy 2.0.49, asyncpg 0.31.0, Alembic 1.18.4), so details may differ.

**TL;DR:** When domains belong to one application, put them in schemas: one engine served nine schemas with 5 connections where nine databases held 45. Set the schema per table with `__table_args__`, use `include_schemas=True` with an `include_name` filter in Alembic, write `CREATE SCHEMA` by hand, and grant per schema. Foreign keys still cross the boundary.

## Should Postgres Domains Use Separate Schemas or Separate Databases?

Use schemas when the domains belong to one application. PostgreSQL 16 does not allow more than one database per connection, so each database needs its own connections, while schemas share one pool. In my demo the same 50-request load held 5 connections with schemas and 45 with databases, and a query across databases failed outright.

On the platform the reason was short: it is one application, and separate databases mean separate connections and a lot of overhead. The demo put numbers on that. Each request touched 3 domains; layout A used one session for them, layout B one session per database, and every engine used `pool_size=5, max_overflow=0`. I sampled `pg_stat_activity` every 5 ms, three runs each.

- **Layout A, one engine and nine schemas:** peak 5 backends in each of the three runs, 5 held open idle after the load, median wall time 735.7 ms.
- **Layout B, nine engines and nine databases:** peak 45 backends in each run, 45 held open idle, median wall time 446.9 ms.
- **Control, one engine with ****pool\_size=45****:** peak 45 backends in each run, 45 held open idle, median wall time 502.9 ms.

Wall time is not a throughput claim. Layout B finished sooner because it had 45 connections to work with, and the control run gave the single engine the same 45 and landed in the same range (430.0 to 736.5 ms across its three runs, on one shared Docker host). The lever is the connection budget: a second app process on layout B would ask for 90 of the 100 connections.

Transactions differ too. One transaction inserted into `leads.companies`, `leads.contacts`, `deals.deals` and `comms.email_logs`, then raised, and 0 rows were left. Across databases, an `INSERT ... SELECT` from `crm_deals` into `crm_leads` failed with `cross-database references are not implemented: "crm_leads.leads.contacts"`. The platform's integration tests run against one tmpfs PostgreSQL 16 container with transaction-rollback fixtures.

## How Do You Map Postgres Multiple Schemas With FastAPI and SQLAlchemy Async?

Put the schema on each table with `__table_args__ = {"schema": "leads"}` on one `DeclarativeBase` with one `MetaData`, and name foreign keys with the schema prefix, such as `ForeignKey("leads.contacts.id")`. One `MetaData` keeps cross-domain relationships resolvable and gives Alembic a single `target_metadata`.

This is the mapping the demo uses:

```python
class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=NAMING)

class Contact(Base):
    __tablename__ = "contacts"
    __table_args__ = {"schema": "leads"}
    id: Mapped[int] = mapped_column(primary_key=True)
    company_id: Mapped[int] = mapped_column(ForeignKey("leads.companies.id"))

class Deal(Base):
    __tablename__ = "deals"
    __table_args__ = {"schema": "deals"}
    id: Mapped[int] = mapped_column(primary_key=True)
    contact_id: Mapped[int] = mapped_column(ForeignKey("leads.contacts.id"))
    contact: Mapped[Contact] = relationship(lazy="raise")
```

The tidier-looking alternative is a `DeclarativeBase` per domain, each with `MetaData(schema="leads")` and so on. I tried it in the demo. Each base gets its own registry and its own `MetaData`, and both cross-domain links broke. The `relationship()` from a `deals` model to a `leads` model failed at mapper initialisation:

```text
InvalidRequestError: When initializing mapper Mapper[Deal2(deals)], expression 'Contact2' failed to locate a name ('Contact2').
```

The foreign key failed at `create_all`, even with the `leads` tables created first:

```text
NoReferencedTableError: Foreign key associated with column 'deals.contact_id' could not find table 'leads.contacts' with which to generate a foreign key to target column 'id'
```

The cost of the single-`MetaData` version is one `__table_args__` line per model, which also puts each table's schema in plain sight in its class.

### Do Fixed Domain Schemas Need `schema_translate_map`?

No. SQLAlchemy documents `schema_translate_map` as an execution option that repurposes a set of `Table` objects to render under different schema names, a multi-tenancy tool. It does not affect `text()`, and the ORM `Session` supports one map per `Session`. Fixed domain schemas have fixed names, so the demo uses neither it nor a `search_path` trick.

## How Should FastAPI Give Each Request Its Own Async Session?

Open one `AsyncSession` per request in a dependency that uses `yield`, built by an `async_sessionmaker` with `expire_on_commit=False`. One engine serves every schema, because the schema is stored on the tables, not on the session. SQLAlchemy 2.0 warns that one `AsyncSession` is not safe across concurrent tasks, so `asyncio.gather` needs a session per task.

The demo's wiring is five lines:

```python
engine = create_async_engine(DSN, pool_size=5, max_overflow=0, pool_pre_ping=True)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

async def get_session():
    async with SessionLocal() as session:
        yield session
```

The endpoint takes `Annotated[AsyncSession, Depends(get_session)]` and joins `deals.deals` to `leads.contacts` and `leads.companies`. Under uvicorn 0.54.0 with an httpx `AsyncClient`, `GET /deals/1` returned 200 with the joined row, `GET /deals/999` returned 404 with `{"detail":"deal not found"}`, and 50 concurrent requests through the 5-connection pool all returned 200. That is a smoke test, not a benchmark.

`Deal.contact` is declared `lazy="raise"`, so an accidental lazy load fails loudly instead of attempting I/O in async code; the platform's rule is eager loading only. By default, FastAPI runs the code after `yield` once the response has been sent; `Depends(get_session, scope="function")` runs it before. The demo uses the default.

## How Do You Query and Join Across Postgres Schemas in SQLAlchemy?

Join the mapped classes as usual. SQLAlchemy renders schema-qualified names, so one statement spans three schemas in one round trip and no `search_path` setting is involved. The identical join with `deals` and `leads` in separate databases failed with sqlstate `0A000`.

This is the SQL that `echo=True` printed for a `select()` over `Deal`, `Contact` and `Company` with an `EXISTS` on `EmailLog`:

```sql
SELECT deals.deals.title, deals.deals.stage, leads.contacts.full_name, leads.companies.name AS company, EXISTS (SELECT comms.email_logs.id
FROM comms.email_logs
WHERE comms.email_logs.contact_id = leads.contacts.id) AS emailed
FROM deals.deals JOIN leads.contacts ON deals.deals.contact_id = leads.contacts.id JOIN leads.companies ON leads.contacts.company_id = leads.companies.id ORDER BY deals.deals.id
```

That reach is also where the boundary stops. On the platform, `comms`, `deals`, `routing` and `enrichment` hold foreign keys into `leads.contacts` and `leads.companies`, and the `messaging` tables reference `auth.users` for per-user ownership. A schema groups a domain's tables. It does not stop domains depending on each other.

The unqualified-name case surprised me more than the join. With the default `search_path` of `"$user", public`, a query for `contacts` failed with `relation "contacts" does not exist`. After I created an empty `public.contacts`, the same query silently resolved to `public`: count 0, while `leads.contacts` held 2 rows. Nothing errored and the answer was wrong.

`SET LOCAL search_path TO leads` fixed that inside a transaction and reverted on rollback, but with a bound parameter through asyncpg, `SET LOCAL search_path TO :schema` failed with `syntax error at or near "$1"`. The schema-qualified model returned the same 2 rows under four different `search_path` values. SQLAlchemy's PostgreSQL dialect advises keeping `search_path` at its default and naming other schemas explicitly in `Table` definitions, which is what the mapping above does.

## How Do You Run Alembic Migrations Across Multiple Postgres Schemas?

Set `include_schemas=True` so autogenerate scans every schema, and filter with `include_name`, which receives the default schema as `None`. Alembic 1.20.0 never emitted `CREATE SCHEMA` in any run of the demo, so write it into the first revision by hand. Place `alembic_version` with `version_table_schema`.

Each step below ran the real `alembic` CLI against a fresh or populated database, in this order.

- **Step 1, ****include\_schemas**** off (the default), empty database:** 10 `create_table` ops plus `op.drop_table('alembic_version')`. No `CREATE SCHEMA`.
- **Step 2, ****include\_schemas=True**** with ****include\_name****, empty database:** 10 correct `create_table` ops with `schema=`. `alembic upgrade head` failed with `schema "auth" does not exist` (`InvalidSchemaNameError`), exit code 1, no schemas left behind.
- **Step 3, the same revision plus nine ****CREATE SCHEMA IF NOT EXISTS**** calls at the top of ****upgrade()****:** upgrade OK, one table per schema, two in `leads`, `alembic_version` in `public`.
- **Step 4a, ****include\_schemas**** off, populated database, new column ****leads.contacts.linkedin\_url****:** the same 10 `create_table` ops again, and the new column missed. Applying it failed with `relation "users" already exists` (`DuplicateTableError`).
- **Step 4b, ****include\_schemas=True**** without ****include\_name****, plus a stray ****scratch.tmp\_import****:** `drop_table('tmp_import', schema='scratch')`, `drop_table('alembic_version')` and the `add_column`.
- **Step 4c, ****include\_schemas=True**** with ****include\_name****:** exactly one op, `add_column('contacts', ..., schema='leads')`. Upgrade OK.

Step 4a is the quiet trap. Without `include_schemas`, Alembic compared only the default schema, saw none of my nine, and proposed to create everything again. The revision looked plausible until it ran. Step 4b shows why the filter matters: unfiltered, autogenerate proposed dropping a table from a schema the models never mention.

The final `env.py` configuration:

```python
target_metadata = Base.metadata          # one MetaData holds all nine schemas

def include_name(name, type_, parent_names):
    if type_ == "schema":
        return name in SCHEMAS            # the default schema (None) and strays are skipped
    return True

context.configure(connection=connection, target_metadata=target_metadata,
                  include_schemas=True, include_name=include_name,
                  version_table_schema="public")
```

The fix for step 2 is plain SQL, one statement per call:

```python
def upgrade() -> None:
    for schema in ("auth", "leads", "scraping", "audit", "enrichment",
                   "routing", "deals", "comms", "messaging"):
        op.execute(f"CREATE SCHEMA IF NOT EXISTS {schema}")
```

The loop matters. Under asyncpg, an `op.execute()` with two statements in one string failed with `cannot insert multiple commands into a prepared statement`. The platform's 17 Alembic migrations are raw `op.execute()` SQL, so that rule applies to its style of migration whenever the driver is asyncpg.

The stray `drop_table('alembic_version')` came from `version_table_schema`: `"public"` with the filter off produced it, `None` did not, and with the nine-schema filter on no op was emitted, so I kept that. A new `ops` schema for the table failed with `schema "ops" does not exist` until `env.py` ran `CREATE SCHEMA IF NOT EXISTS ops` first.

A migration that fails loudly beats one that waits. I wrote up [a Postgres migration step that hung in CI waiting on a prompt](/blog/payload-migrate-hang-docker-postgres), and the deploy side is in [how a migration runs on every deploy of a self-hosted stack](/blog/one-vps-nextjs-payload-cloudflare-nginx). Both concern Payload, not Alembic.

## How Do You Restrict Access to One Postgres Schema With GRANT USAGE?

A role cannot touch objects in a schema it does not own until the owner grants `USAGE` on that schema, and `USAGE` alone allows nothing else. In the demo, a role got `permission denied for schema comms`, then `permission denied for table email_logs`, then a sequence error, one missing grant at a time.

I split the platform into schemas so permissions and ownership can be reasoned about per domain while it stays one deployable. To see what that takes, I created a login role `comms_writer` with no grants, over tables owned by `postgres`:

1. **No grants:** `SELECT count(*) FROM comms.email_logs` failed with `permission denied for schema comms`.
2. **Plus ****USAGE ON SCHEMA comms****:** the same query failed with `permission denied for table email_logs`.
3. **Plus ****SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA comms****:** the query returned `0`.
4. **Same grants, ****INSERT ... RETURNING id****:** failed with `permission denied for sequence email_logs_id_seq`.
5. **Plus ****USAGE ON ALL SEQUENCES IN SCHEMA comms****:** the insert succeeded.
6. **No grants on other schemas:** `SELECT count(*) FROM leads.contacts` failed with `permission denied for schema leads`.

The foreign key is where the wall leaks. `has_schema_privilege('leads', 'USAGE')` returned `False` for `comms_writer`, yet an insert into `comms.email_logs` with an existing `contact_id` succeeded, so the foreign key check ran without any grant on `leads`. This fits PostgreSQL running the check with the referenced table owner's rights, which I did not verify in the source. An insert with `contact_id = 424242` failed with `DETAIL:  Key (contact_id)=(424242) is not present in table "contacts".` That error tells a role with no access to `leads` whether a contact id exists.

Declaring a new foreign key is stricter: `CREATE TABLE comms.fk_probe (..., contact_id int REFERENCES leads.contacts(id))` needed `USAGE` on `leads` and `REFERENCES (id)` on the column, and `SELECT` on `leads.contacts` stayed denied.

`GRANT ... ON ALL TABLES` is a snapshot. After `ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA comms GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO comms_writer`, the existing `comms.unsubscribes` was still denied and the next table, `comms.bounces`, was readable and insertable. PostgreSQL 16 documents that default privileges do not affect existing objects, and `pg_default_acl` binds each entry to the creating role, so tables created by another role are not covered.

PostgreSQL 16 also says `USAGE` is not a completely secure way to prevent object access: object names stay visible in the system catalogs, and an existing session may keep a lookup made before a revoke.

Per-domain roles mean per-domain credentials, and a rotated one fails at runtime in a way that looks like a grants bug, as in [what a worker sees when its Postgres credential rotates and permission denied follows](/blog/vault-secret-rotation-application-downtime).

## Limitations: What Does This Not Cover?

Nothing here is a production measurement or the platform's own code. Every figure and error string comes from one demo run on 16 October 2026, on the versions listed at the top: 10 tables and 2 migrations, against the platform's 33 tables and 17 migrations.

- **No production load.** The connection test is 50 requests on one Docker host. Memory per backend and PgBouncer were not measured.
- **No tenancy.** The demo is schema-per-domain only, with no `schema_translate_map` or schema-per-tenant setup.
- **One role.** Tables were owned by the superuser. Separate owner roles per schema, row-level security and credential rotation were not tested.
- **One migration chain.** Several chains or branch labels per schema were not tried, and downgrades were not exercised.
- **Cross-database access.** `postgres_fdw` and `dblink`, which PostgreSQL 16 documents as the routes across databases, and two-phase commit were not installed or tested. Only the error message is shown.

A schema is a name you can grant on, and a foreign key will walk through it anyway.

If you run domain schemas under separate roles, which role owns the tables that Alembic creates, and how do you keep `ALTER DEFAULT PRIVILEGES` attached to that owner? My demo had a single superuser owner, so I have no answer for the case with several.

## References

- SQLAlchemy 2.0 documentation, read 2026-10-16: [Specifying the Schema Name](https://docs.sqlalchemy.org/en/20/core/metadata.html#specifying-the-schema-name), [Declarative Table Configuration](https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html#orm-declarative-table-configuration), [Translation of Schema Names](https://docs.sqlalchemy.org/en/20/core/connections.html#translation-of-schema-names), [Asynchronous I/O](https://docs.sqlalchemy.org/en/20/orm/extensions/asyncio.html), [PostgreSQL dialect: remote-schema table introspection and search\_path](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path).
- Alembic documentation, read 2026-10-16: [`EnvironmentContext.configure`](https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure) and [Omitting Schema Names from the Autogenerate Process](https://alembic.sqlalchemy.org/en/latest/autogenerate.html#omitting-schema-names-from-the-autogenerate-process).
- PostgreSQL 16 documentation, read 2026-10-16: [Schemas](https://www.postgresql.org/docs/16/ddl-schemas.html), [Privileges](https://www.postgresql.org/docs/16/ddl-priv.html), [ALTER DEFAULT PRIVILEGES](https://www.postgresql.org/docs/16/sql-alterdefaultprivileges.html), [Managing Databases](https://www.postgresql.org/docs/16/manage-ag-overview.html).
- FastAPI documentation, read 2026-10-16: [Dependencies with yield](https://fastapi.tiangolo.com/tutorial/dependencies/dependencies-with-yield/).
- The demo (Compose file, models, `env.py`, scripts and every log) from 16 October 2026: the source for every measured figure and error string above.