PostgreSQL Schemas -- Usage Scenario/Case

Question

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.

Neville K's answer captures the essence but is perhaps a bit brief.

Schemas are essentially namespaces. They're useful in the same kinds of situations that namespaces are useful in programming: where you have many things, so many that you want to partition them into separate sub-collections (many -- but fewer than (say) 10,000 such sub-collections, given that there's a single level), while being able to inter-operate among them. Using schemas can allow more 'natural' naming standards for other database objects.

As an aside, the value of namespaces isn't appreciated by new programmers either. That seemingly trivial benefit of allowing multiple objects to have the same name isn't so trivial, it turns out. It's only when one has worked for a while on larger projects (with thousands of 'code objects': tables, views, indices, stored procedures, domains, etc.) that one comes to understand that the benefits of namespaces outweigh their costs.

In an earlier age (and not with PostgreSQL), I worked for a computer bureau with about 20 customers, ranging from 250 concurrent users of the DBMS down to one, each via private leased telephone lines. (This was before the internet.) Each customer had its own schema, with an admin user (role) that could create and drop other users as employees came and went, grant and revoke privileges, and do a limited amount of data definition work and import/export in their own schema. As a developer I had to use schemas because there was only one DBMS instance and one database. So... if the above is not convincing, you could say that schemas are in the SQL standard (for historical reasons), therefore PostgreSQL has support for schemas.

Today, a somewhat similar situation to mine might arise in a lab environment, where several researchers (or hundreds of students) each want to work on their own data while having access to a common set in the public schema. Using schemas helps stop accidents: inadvertently trampling on each others' data.

Here's another common usage I've seen. Schemas and search_path allow two or more developers to work on the same single copy of a large db without needing their own copies, but without getting in each other's way.

Let's say Joe is working on the comments table, and Jim is working on the workflow table. They both need the users, groups, etc tables. Joe creates a schema, creates his own working version of the comments table, and can muck about with it at will:

create schema joe;
create table joe.comments (rest of new tabledef here);
set search_path='joe','public';

Now when Joe does alter table on the joe.comments table, Jim sees no changes and his development is not impacted by a broken joe.comments table etc. When Joe's code, having a search_path of 'joe','public' runs, it sees joe's comments tables while everyone else sees the original.

After thorough testing, Joe's comments table can be basically put in place of the original in one fell swoop with no interruption to Jim's development. Multiply this time 40 developers and it allows all 40 to work on the same dev db without blowing each other's stuff up (or less often at least).

The key benefit for schemas is to provide a logical grouping for your database tables. The most likely use cases are:

• To run different logical applications within the same DB - for instance, you might have an enterprise system, and want to create schemas called "userprofiles", "projects", "finance" etc.
• To create similar versions of the same group of tables, for instance for "development", "qa" and "production"