5 ms·

Yeah, it's really hard, but I think you're on to something that would be really useful. For connection pooling, if there were an easy way to set the schema sea

Yeah, it's really hard, but I think you're on to something that would be really useful. For connection pooling, if there were an easy way to set the schema search path prior to using a connection object in application code, something like that might work. I obviously don't like the extra query per request, but there are worse things! For the multi-version domain model, the only way I could think of doing it was partitioning the deployment environment according to version. So, 2n servers, n running each version, and slowly transitioning users from the old segment to the new. That sounds really hard, but not impossible. It also starts getting into some tough environment, human resource and priority questions, but for what it's worth, it'd keep the application logic cleaner! :) Anyway, if you ever end up with something elegant on this, I would be truly interested in reading about it. It's a good example of how a DBA concept can help app dev teams, who, in alarming numbers, prioritize an RDBMS' ease of use over its power.

hrbrtglm 12y ago
Well, let me explain the structure I choose : Each tenant is assigned a subdomain (No vhost, all routed by the app to the same codebase) with the name he choosed when he subscribed. So this name is retrieved by inspecting the host header the browser send while connecting to the webapp, this name is checked in the public schema to find an appropriate uuid assigned (if it exists of course). Something like : App_DB (database) -- public (public schema) -- tenants (tenants table) -- tenant_uuid uuid -- tenant_name text (same as subdomain name) -- coderev numeric (for split testing) -- some tenant general info like creation date, choosen plan, ... -- some other tables accessible by all tenants like shared stats, queues, etc ... I then use this tenant_uuid as the schema name for this tenant : -- "b6e42fd1-d5b9-4de4-ba6b-6eca1dae06ff" (tenant_uuid schema name) -- users (it's a multi-user webapp per tenants, so ...) -- email (for auth) -- password (bcrypt for auth) -- etc ... -- other tables needed for the webapp This for each tenant, so some tenants can have a different schema stucture based on their public.coderev When the user login, his credentials are checked in his tenant schema, the tenant name and uuid are set in his session for not messing with other tenants data. I think there are 2 ways to deal with connection pooling (If we are both talking about PgBouncer || pgpool connection pooling to be sure). - The first one is to create a new PostgreSQL user for each tenant in order to access only his schema. Then there is no need to set the schema search path prior the connection object as by definition his search_path will be set to $user,public (http://www.postgresql.org/docs/9.1/static/ddl-schemas.html http://www.postgresql.org/docs/9.1/static/ddl-schemas.html) But then, I can't really see the point of a connection pooling. I don't really like this solution, so many users, roles, passwords ... - The second (which I choose) is to connect with a role having access to all the schemas. You can set this one for connection pooling. The search_path is only set to public, and the queries use the qualified name to access the tenant data, ie: SELECT * FROM "b6e42fd1-d5b9-4de4-ba6b-6eca1dae06ff".users You can use the connections readily made available by the connection pool and query the data for the tenant you need without touching the search_path. The security is now dealt with the application and not the DB. For the multi-version domain model, the coderev set in the public.tenants schema is retrieved with the tenant_uuid associated with the tenant name and then used by the webapp controller to route to the associated code version. No need for a second server, just a different branch on the same server. Transitioning a user then just really mean updating his schema and routing it through the new codebase. And that's why I said it's very ugly, because I still need to implement a good DDL and DML stategy in order to navigate between different versions without losing data. Like you said, it's a tough environment. Unfortunately, I have nothing elegant to propose but I'm also interested in reading how others deal with that kind of stuff. I still lack fluency to write a blog post or something like that which could encourage debating or discussions. I'd be very glad if yourself find some interesting stuff on this topic to share it with me, my email address is my hn username @ gmail.com

hrbrtglm 12y ago

Well, let me explain the structure I choose : Each tenant is assigned a subdomain (No vhost, all routed by the app to the same codebase) with the name he choosed when he subscribed. So this name is retrieved by inspecting the host header the browser send while connecting to the webapp, this name is checked in the public schema to find an appropriate uuid assigned (if it exists of course). Something like : App_DB (database) -- public (public schema) -- tenants (tenants table) -- tenant_uuid uuid -- tenant_name text (same as subdomain name) -- coderev numeric (for split testing) -- some tenant general info like creation date, choosen plan, ... -- some other tables accessible by all tenants like shared stats, queues, etc ... I then use this tenant_uuid as the schema name for this tenant : -- "b6e42fd1-d5b9-4de4-ba6b-6eca1dae06ff" (tenant_uuid schema name) -- users (it's a multi-user webapp per tenants, so ...) -- email (for auth) -- password (bcrypt for auth) -- etc ... -- other tables needed for the webapp This for each tenant, so some tenants can have a different schema stucture based on their public.coderev When the user login, his credentials are checked in his tenant schema, the tenant name and uuid are set in his session for not messing with other tenants data. I think there are 2 ways to deal with connection pooling (If we are both talking about PgBouncer || pgpool connection pooling to be sure). - The first one is to create a new PostgreSQL user for each tenant in order to access only his schema. Then there is no need to set the schema search path prior the connection object as by definition his search_path will be set to $user,public (http://www.postgresql.org/docs/9.1/static/ddl-schemas.html http://www.postgresql.org/docs/9.1/static/ddl-schemas.html) But then, I can't really see the point of a connection pooling. I don't really like this solution, so many users, roles, passwords ... - The second (which I choose) is to connect with a role having access to all the schemas. You can set this one for connection pooling. The search_path is only set to public, and the queries use the qualified name to access the tenant data, ie: SELECT * FROM "b6e42fd1-d5b9-4de4-ba6b-6eca1dae06ff".users You can use the connections readily made available by the connection pool and query the data for the tenant you need without touching the search_path. The security is now dealt with the application and not the DB. For the multi-version domain model, the coderev set in the public.tenants schema is retrieved with the tenant_uuid associated with the tenant name and then used by the webapp controller to route to the associated code version. No need for a second server, just a different branch on the same server. Transitioning a user then just really mean updating his schema and routing it through the new codebase. And that's why I said it's very ugly, because I still need to implement a good DDL and DML stategy in order to navigate between different versions without losing data. Like you said, it's a tough environment. Unfortunately, I have nothing elegant to propose but I'm also interested in reading how others deal with that kind of stuff. I still lack fluency to write a blog post or something like that which could encourage debating or discussions. I'd be very glad if yourself find some interesting stuff on this topic to share it with me, my email address is my hn username @ gmail.com