Postgresql - 每个人一个数据库，或每个客户一个数据库 [英] Postgresql - one database for everyone, or one-database per customer

问题描述

我正在开发一个基于Web的业务应用程序，其中每个客户都需要有自己的数据（考虑basecamphq.com类型模型）。对于可扩展性和易于升级，我希望有一个数据库其中每个客户获取数据的过滤版本。问题是如何保证他们保持沙箱到自己的数据。试图在代码中强制执行它似乎是一场灾难等待发生。我知道Oracle有一种方法，根据登录ID附加一个where子句到每个查询，但Postgresql有什么相似吗？

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)?

更糟糕的情况是，拥有1000个100M数据库与拥有单个1Tb数据库的性能/内存开销是多少？我需要在每个客户的基础上提供备份/恢复功能，这在单个数据库上是非常简单的，但如果他们与其他客户共享数据库，则会有点麻烦。

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.

推荐答案

您可能希望查看添加 Veil 到您的PostgreSQL安装。

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

这篇关于Postgresql - 每个人一个数据库，或每个客户一个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！