Could you please clarify what you mean by "schemas"?
My understanding is that a schema is an abstract definition of what your database fields/relations are. So that's too vague for me.
Adding a customer_id field to every single table in the database would technically fit the definition of "schema", but I suspect you mean something else altogether. Please elaborate.
Neeleshs' reply is what I meant. In Postgres, it's like a namespace.
But you have a point: adding customer_id is not unreasonable. In fact, this is my preferred logical approach, but I'll typically only do this on Oracle. It's DRY, meaning that, in the abstract definition of schema, you're modeling your relations once. If you have widgets belonging to customers, you have a one widgets relation containing a customer id, rather than one widgets relation in each customer schema. It's an elegant design, and it takes even greater advantage of the benefits I described earlier.
I'd advocate against it when building a multitenant SaaS application on Postgres if the following hold:
1. You have many, many customers
2. Each customer has a lot of data
The reasons:
1. You'll quickly hit a place where it's hard to throw more vertical processing power at it. You'll need a serious SAN with lots of spindles to be able to service many request to a lot of data, and plenty of CPU and RAM to handle the grunt work. Let's face it: you're on Postgres because you're broke :) I really love it, but sometimes I'd rather buy a solution to a problem (e.g., VPD). However, if you're rolling out schemas, you can build a new database and move them discretely. This isn't automatic or transparent, but it's very clean and very easy.
2. You're likely dealing with clients with special needs. It might not be appropriate to roll out new features to every customer all at the same time. If you ALTER TABLE widgets... it's all or nothing. Sometimes that's great, but sometimes it's not, and in building multitenant SaaS apps, I find it's most often not. In repeating your design, you're buying the flexibility to deal with your customers on a more granular level.
3. Others have already mentioned this: sometimes it's easier to articulate the integrity of your system to technical auditors with a hard separation like independent schemas. It's a little easier to show that a particular database account has access only to a particular schema than it is to convince him that your code does a sufficient job of enforcing the rules. I've done a lot of work where regulatory compliance is a concern, and this is always an easier sell.
The thing is that most vendors have solutions to these problems, and if Postgres doesn't have already built, you can probably build it from their primitives. This is just my general thinking under this particular set of circumstances.
My understanding is that a schema is an abstract definition of what your database fields/relations are. So that's too vague for me.
Adding a customer_id field to every single table in the database would technically fit the definition of "schema", but I suspect you mean something else altogether. Please elaborate.