Postgresql - one database for everyone, or one-database per customer

Question

I'm working on a web-based business application where each customer will need to have their own data (think basecamphq.com type model) For scalability and ease-of-upgrades, I'd prefer to have a single database where each customer gets a filtered version of the data. The problem is how to guarantee that they stay sandboxed to their own data. Trying to enforce it in code seems like a disaster waiting to happen. I know Oracle has a way to append a where clause to every query based on a login id, but does Postgresql have anything similar?

If not, is there a different design pattern I could use (like creating a view of each table for each customer that filters)?

Worse case scenario, what is the performance/memory overhead of having 1000 100M databases vs having a single 1Tb database? I will need to provide backup/restore functionality on a per-customer basis which is dead-simple on a single database but quite a bit trickier if they are sharing the database with other customers.

Answer 1

You might want to look into adding Veil to your PostgreSQL installation.

Answer 2

Schemas plus inherited tables might work for this, create your master table then inherit tables into per-customer schemas which provide a company ID or name field default.

Set the permissions per schema for each customer and set the schema search path per user. Use the same table names in each schema so that the queries remain the same.