Repository navigation
Specify Postgres schema #2633
Description
What do you want to change?
I am interested to use multiple schemas in Postgres. This can for example be used to handle multiple tenants in a single database. Although it's possible to share tables across tenants, it can be better to use more than one schema instead. This isolates each tenant's data from the others. It also reduces the performance costs of each tenant's db load from impacting other tenants because tables hold fewer lines.
Is there a way to use schemas via SQL statements in SQL?
Eg select * from tenant1.user,
where I can replace tenant1 accordingly?
What database engines need to be changed?
PostgreSQL
What programming language backends need to be changed?
Go
Activity
rickb777 commentedon Aug 18, 2023
Additionally, switching schemas would be a useful feature for integration tests that need to be isolated from each other. Creating temporary schemas, using them during a test run, then tearing them down afterwards is a useful technique to allow partitioning such that different tests don't interfere with each other.
rickb777 commentedon Aug 18, 2023
BTW the current workaround is to set up each tenant to have its own default schema in Postgres (i.e. per user role), and then use a separate connection pool with the credentials for each tenant.
This is clumsy and not ideal for the integration test use-casr.