4 ms·

As a long-time MySQL user who hasn't used Postgres for over a decade, can you share (in non-documentation language) what schemas are good for and how they're us

by porker 13y ago

As a long-time MySQL user who hasn't used Postgres for over a decade, can you share (in non-documentation language) what schemas are good for and how they're useful?

jakobe 13y ago
One compelling use case I've heard for schemas is versioning. Say you want to upgrade a stored function in your database. You need to make sure that both the new and the old version of the function are available until all application code that uses the stored procedure has been updated. Without schemas, you'd have to add a version identifier to each function, like get_customer_5(), get_customer_6() etc. With schemas, you can just put different versions of the functions into different schemas, and just change the search_path in your application code to use functions from the new schema. When all application code has been updated, you can drop the old schema.
hrbrtglm 13y ago
I'll take a typical context of a SaaS app, multi-tenant architecture. When it comes to store the data of your tenants, you have to decide between different approaches which will impact the level of isolation of each tenant's data. I will not discuss the benefits and disadvantages of each because it clearly depends of your needs, security guidelines and sometimes regulations regarding your business, backup strategy, geo-isolation, sharding, etc ... With MySQL, you can choose 2 way to architecture your data : - the fully isolated way : each tenant has his own database. - the shared way : all tenants share the same schema, you usually use a tenant_id column on tables to query your data adequately. Some other RDBMS like PostgreSQL offer you a third way between the fully isolated a shared data system : - all tenant access the same database but their data are stored on their own schema. It's up to you to decide if the tenants access their schema with the same database role (kind of user) or if you create a role for accessing solely their own schema. Example : App_DB -- Public schema -- tenant_id uuid -- tenant_name (subdomain name for example) -- etc ... -- tenant (named after the public.tenant_id) -- your application tables -- tenant (named after the public.tenant_id) -- your application tables -- tenant (named after the public.tenant_id) -- Some beta tables model for version n+1 of your app or whatever, you can add more table columns for this very tenant if he needs special features for example. I hope it's non-documentation language but still english language :) (Need to improve my english and my writing skills)
- porker 13y ago
  Thank you, that's really helpful :) Only last week I was sketching the database design (in MySQL) for a SaaS app, thinking how messy it was to add a tenant_id column and parameter onto every query...

by porker 13y ago

As a long-time MySQL user who hasn't used Postgres for over a decade, can you share (in non-documentation language) what schemas are good for and how they're useful?

jakobe 13y ago

One compelling use case I've heard for schemas is versioning. Say you want to upgrade a stored function in your database. You need to make sure that both the new and the old version of the function are available until all application code that uses the stored procedure has been updated. Without schemas, you'd have to add a version identifier to each function, like get_customer_5(), get_customer_6() etc. With schemas, you can just put different versions of the functions into different schemas, and just change the search_path in your application code to use functions from the new schema. When all application code has been updated, you can drop the old schema.

hrbrtglm 13y ago

I'll take a typical context of a SaaS app, multi-tenant architecture. When it comes to store the data of your tenants, you have to decide between different approaches which will impact the level of isolation of each tenant's data. I will not discuss the benefits and disadvantages of each because it clearly depends of your needs, security guidelines and sometimes regulations regarding your business, backup strategy, geo-isolation, sharding, etc ... With MySQL, you can choose 2 way to architecture your data : - the fully isolated way : each tenant has his own database. - the shared way : all tenants share the same schema, you usually use a tenant_id column on tables to query your data adequately. Some other RDBMS like PostgreSQL offer you a third way between the fully isolated a shared data system : - all tenant access the same database but their data are stored on their own schema. It's up to you to decide if the tenants access their schema with the same database role (kind of user) or if you create a role for accessing solely their own schema. Example : App_DB -- Public schema -- tenant_id uuid -- tenant_name (subdomain name for example) -- etc ... -- tenant (named after the public.tenant_id) -- your application tables -- tenant (named after the public.tenant_id) -- your application tables -- tenant (named after the public.tenant_id) -- Some beta tables model for version n+1 of your app or whatever, you can add more table columns for this very tenant if he needs special features for example. I hope it's non-documentation language but still english language :) (Need to improve my english and my writing skills)

porker 13y ago
Thank you, that's really helpful :) Only last week I was sketching the database design (in MySQL) for a SaaS app, thinking how messy it was to add a tenant_id column and parameter onto every query...