I'm building an application which will have many customers (200+) and we're thinking about using PostgreSQL schemas to separate them.
Now, my question is, when I make a change in one table (e.g. adding a column), how to I get all other schemas to sync their table structure to the desired one?
Or how do I look for this in the Internet? I haven't found much. Maybe I'm missing the right keyword?
If you want replicate same structure you probably need use inherit
https://www.postgresql.org/docs/11/ddl-inherit.html
CREATE TABLE cities (
name text,
population float,
altitude int -- in feet
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
In this case you can create a JhonCustomer table based in the original Customer table. If Customer table change then JhonCustomer also change.
But again I still think you should add custumer_id to your tables instead of create 200 schemas
inherit as a solution. I dont know what are the OP requiremnts because the question is very broad.Row Level Security with a customer_id column would by how I approach this.
I've used this model where you trust the application to handle setting the session variable for the right customer. This really shouldn't change much in the application itself since the other approach would require the app to handle setting the client's schema.
To learn more about RLS, CrunchyData has some great info as does this post by Caleb Brewer.
Navigating/exploring your database becomes tedious with so many schemas. Regardless of the tool (psql, PgAdmin, etc), trying to review/find schemas in a list of 200+ is miserable. This impacts your developers, admins and analysts, making every one of their lives less fun and less productive. This greatly increases the cost of maintenance.
Second: Performance! Indexes on the parent table are not inherited by their children tables. If you want an index on a column that's inherited by every schema you need to explicitly create those indexes in each schema's version of the table. So one table needing 3 indexes on it now requires 200+ * 3 indexes. Need to drop an index.... that's 200+ times you need to drop that index.
Control: What happens when a dev creates a new table in a client schema "because this important client needs it right now?" Finding oddities such as this become virtually impossible. This is another area where cost of maintenance can increase exponentially.
If you insist on using one schema per customer, table inheritance probably won't help more than it hurts.
If you need to deploy one schema per customer, automated deployment should be your goal. Even if you choose to use RLS and keep all the data in one set of tables, automation should still be a high priority. Tools like Ansible and Sqitch can be invaluable for this. Using this approach you would write your DDL with the schema set as a parameter (variable) that is only populated at deploy-time. This way each schema gets deployed as an exact version with all objects in your database, including indexes and check constraints on tables, views, and functions.
costumer_idto your tables.