Postgres Schemas Behind FastAPI: Nine Namespaces, Not Walls

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:

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:

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:

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:

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:

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:

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:

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, and the deploy side is in how a migration runs on every deploy of a self-hosted stack. 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.

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, Declarative Table Configuration, Translation of Schema Names, Asynchronous I/O, PostgreSQL dialect: remote-schema table introspection and search_path.
• Alembic documentation, read 2026-10-16: EnvironmentContext.configure and Omitting Schema Names from the Autogenerate Process.
• PostgreSQL 16 documentation, read 2026-10-16: Schemas, Privileges, ALTER DEFAULT PRIVILEGES, Managing Databases.
• FastAPI documentation, read 2026-10-16: 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.