I’ve seen people in my Google searches
about schemas make a separate
schema+tables for each online customer
to their website. They have like
100,000 schemas. Q3: What am I missing
here? This seems extreme to say the
least. They should be adding record(s)
to standard table(s) for each customer
not making schemas and tables for each
customer.
One database per tenant (customer) is easy to build, and it gives you the strongest isolation between tenants, and the simplest disaster recovery. But it’s relatively expensive.
One schema per tenant is also easy to build. There’s a smaller degree of isolation between tenants. A dbms can support more tenants per server with one schema per tenant than with one database per tenant. Disaster recovery for one tenant is more complicated than with one database per tenant.
A shared schema requires every row to have a tenant identifier. Hardware and backup are cheaper; disaster recovery for one tenant can be a real bitch. (To recover data for a single tenant, you have to recover some rows in every table. Performance suffers for all tenants when that happens.) Isolation is trickier. Since tenants share tables, making sure no tenant can access other tenants data is a lot harder than with one database or one schema per tenant.
The search term for this stuff is “multi-tenant database design”. SO also has a multi-tenant tag.
Another common use is to group database objects that belong together. For example, if you were developing an accounting database, all the objects that implement “accounts payable” features might go in the “ap” schema. I use schemas for PostgreSQL extensions, too. In my db, I installed the hstore extension in the “hst” schema, the tablefunc extension in the “tbf” schema, etc.