使用每个模式一个数据库还是使用一个数据库多个模式，哪种更好？

Question

在我提出问题后，这条评论让我开始考虑是使用一个包含X个模式的数据库更好，还是反过来。

我正在开发一个Web应用程序，在人们注册时，我创建（实际上）一个数据库（不，这不是社交网络：每个人都必须访问自己的数据，永远不会看到其他用户的数据）。这是我用于先前版本的应用程序的方式（仍在MySQL上运行）：通过Plesk API，对于每个注册，我执行以下操作：

1. 创建一个有限权限的数据库用户；
2. 创建一个只能由前面创建的用户和超级用户（用于维护）访问的数据库
3. 填充数据库

现在，我需要在PostgreSQL中执行相同的操作（该项目正在成熟，MySQL不能满足所有需求）。我需要使所有数据库/模式备份独立：pg_dump两种方式都可以完美工作，对于可以配置为仅访问一个模式或一个数据库的用户也是如此。

因此，假设您是比我更有经验的PostgreSQL用户，您认为什么是我情况下的最佳解决方案以及为什么？使用$x数据库而不是$x模式是否会有性能差异？什么解决方案将来更容易维护（可靠性）？我的所有数据库/模式将始终具有相同的结构！

对于备份问题（使用pg_dump），也许最好使用一个数据库和多个模式，一次转储所有模式：恢复将非常简单，在开发机器上加载主要转储文件，然后只需转储和恢复所需的模式：有一个额外的步骤，但似乎同时转储所有模式比逐个转储它们更快。

更新2012

好吧，这些最后两年应用程序的结构和设计都发生了很大变化。我仍然使用“一个带有多个模式”的方法，但仍然为我的应用程序的每个版本创建一个数据库：

Db myapp_01
    \_ my_customer_foo_schema
    \_ my_customer_bar_schema
Db myapp_02
    \_ my_customer_foo_schema
    \_ my_customer_bar_schema

为了备份，我定期转储每个数据库，然后将备份移到开发服务器上。我还使用PITR/WAL备份，但正如我之前所说的，我不太可能一次性还原所有数据库。因此，今年可能会放弃这种方法（在我的情况下不是最佳选择）。

一个数据库多个模式的方法目前对我来说非常有效，即使应用程序结构完全改变也是如此。我几乎忘记了：我所有的数据库/模式都会始终具有相同的结构！现在，每个模式都有自己的结构，根据用户数据流动动态更改。

- Strae

---

"我所有的数据库/模式都将拥有相同的结构！" 你是指它们都具有相同的结构吗？还是从不？ - Osama Al-Maadeed

如果您有1000个客户，那么这意味着您必须更新1000个架构吗？ - Joshua Partogi

那么，你最终选择了什么？不过，有一个问题，虽然查询等的性能可以通过表空间、模式来控制，从而实现多数据库与多模式的等效性能，但对WAL日志有任何影响吗？ - Kapil

@Kapil：嗯，应用程序的设计在这段时间内发生了根本性的变化...让我更新我的问题并提供一些细节。 - Strae

我在我的SAAS应用程序中遇到了同样的问题！我为每个客户拥有一个MySQL数据库，但是我想知道当每个用户都有自己的数据库时，将来如何更改所有用户的模式，请帮帮我？ - Vahid Alvandi

Answer 1

PostgreSQL中的“schema”与MySQL中的“database”大致相同。在PostgreSQL安装中拥有许多数据库可能会变得棘手；而拥有许多模式将不会有问题。因此，您肯定要选择一个数据库，并在该数据库中使用多个模式。

- kquinn

---

Postgres不允许跨数据库查询，这可能会很烦人。 - matt b

在PostgreSQL安装中拥有许多数据库可能会引起问题。请澄清一下：这是通常存在的问题还是仅限于特定情况？为什么会出现这种问题？ - akaihola

在数据库中使用多个模式的最常见用例是构建软件即服务应用程序，在其中每个客户都有自己的模式。尽管这种技术似乎很有吸引力，但我们强烈反对它，因为它已经导致了许多操作问题。例如，即使有适度数量的模式（> 50），也会严重影响Heroku数据库快照工具的性能。 - Neil McGuigan

有趣的是，这似乎与kquinn所得出的（被接受的）答案相反。 - carbocation

针对那些在2015年末阅读此文的人，现在有一个名为“dblink”的Postgres扩展程序可以用于跨数据库查询（这是对@mattb评论的回复）。 - Kamil Gosciminski

Answer 2

我建议不要采用接受的答案 - 使用多个数据库而不是多个模式，因为以下原因：

1. 如果您正在运行微服务，您希望强制执行无法加入您的“模式”之间，因此数据不会缠绕在一起，开发人员不会加入其他微服务的模式，并想知道当其他团队进行更改时，为什么他们的东西不再起作用。
2. 如果负载需要，您稍后可以轻松地迁移到单独的数据库机器。
3. 如果您需要具有高可用性和/或复制设置，则最好拥有完全彼此独立的单独数据库。与整个数据库相比，您无法仅复制一个模式。

- Alan Sereb

---

完全取决于服务。请注意，这是一个相当古老的问题；但最终需要在两个“微服务”之间进行查询（这不是最初的项目中的内容）。使用模式使它变得有点容易，如果我没记错的话，只是需要更好地配置数据库用户的权限。如果我们选择了“N个数据库”的方式，那可能会更难一些（但肯定是可行的）。 - Strae

现在的方法可能会有所不同，可能会暴露某种API，完全将数据库/模式分开。 - Strae

@Strae，你说得对，这是一个老问题，但我只是想重新提出它，并希望能够得到一些关于同样问题的见解。我做了一些研究并决定发表我的意见。 - Alan Sereb

没问题，欢迎这样做！根据我的经验（至少对于我来说），区别并不大；使用一个具有多个模式的数据库可以帮助备份和跨模式查询。 - Strae

我最喜欢的答案。我们不应该假设允许跨模式查询是一件好事情，事实上，我们应该从相反的假设开始！ - Ronnie

Answer 3

毫无疑问，我会选择一对多架构的方法。这样我就可以轻松地导出整个数据库，但只需要恢复其中一个 schema：

1. 导出整个数据库（包括所有 schema），将导出文件加载到新数据库中，仅导出所需的 schema，然后在主数据库中进行还原。
2. 逐个导出每个 schema（但我认为这样机器的压力更大 - 我预计有 500 个 schema！）

另外，我在网上搜到没有自动复制 schema 的程序（使用其中一个作为模板），但许多人建议按照以下方式操作：

1. 创建模板 schema
2. 需要复制时，将其重命名为新名称
3. 导出它
4. 将其重新命名回来
5. 还原备份
6. 完成了。

我已经用 Python 写了两行代码来实现这个过程；希望它们能帮助到某些人（不要在生产环境中使用这些代码，因为它们只是很简单的示例）:

import os
import sys
import pg

# Take the new schema name from the second cmd arguments (the first is the filename)
newSchema = sys.argv[1]

# Temperary folder for the dumps
dumpFile = '/test/dumps/' + str(newSchema) + '.sql'

# Settings
db_name = 'db_name'
db_user = 'db_user'
db_pass = 'db_pass'
schema_as_template = 'schema_name'

# Connection
pgConnect = pg.connect(dbname= db_name, host='localhost', user= db_user, passwd= db_pass)

# Rename schema with the new name
pgConnect.query("ALTER SCHEMA " + schema_as_template + " RENAME TO " + str(newSchema))

# Dump it
command = 'export PGPASSWORD="' + db_pass + '" && pg_dump -U ' + db_user + ' -n ' + str(newSchema) + ' ' + db_name + ' > ' + dumpFile
os.system(command)

# Rename back with its default name
pgConnect.query("ALTER SCHEMA " + str(newSchema) + " RENAME TO " + schema_as_template)

# Restore the previous dump to create the new schema
restore = 'export PGPASSWORD="' + db_pass + '" && psql -U ' + db_user + ' -d ' + db_name + ' < ' + dumpFile
os.system(restore)

# Want to delete the dump file?
os.remove(dumpFile)

# Close connection
pgConnect.close()

- Strae

Answer 4

我建议使用多个数据库和多个模式 :)

在PostgreSQL中，模式很像Oracle中的包，如果您熟悉这些内容的话。数据库用于区分整个数据集，而模式更像是数据实体。

例如，您可以为整个应用程序设置一个数据库，并设置“UserManagement”、“LongTermStorage”等模式。“UserManagement”然后将包含“User”表以及所有所需的存储过程、触发器、序列等，用于用户管理。

数据库是整个程序，模式是组件。

- Callash

---

所以我将拥有一个数据库，其中包含模式：$customer1_user_schema、$customer2_user_schema、$customer3_user_schema、$customer1_documents_schema、$customer2_documents_schema和$customer3_documents_schema？嗯...这似乎不是一个可靠的方法...而且性能如何？我的应用程序代码（将使用PHP和Python）又该怎么办？这么多模式... - Strae

我理解的是：每个客户都有自己的数据库，如customer1_database、customer2_database，而在这些数据库中又分别包含用户模式user_schema和文档模式documents_schema。 - frankhommers

Answer 5

在PostgreSQL环境中，我建议使用一个具有多个模式的数据库，因为您可以（例如）跨模式使用UNION ALL，但不能跨数据库使用。因此，数据库与另一个数据库完全隔离，而模式则未在同一数据库中与其他模式隔离。

如果由于某种原因，您将来需要合并跨模式的数据，则可以轻松地在多个模式上执行此操作。对于多个数据库，您需要多个数据库连接，并通过应用程序逻辑手动收集和合并每个数据库的数据。后者在某些情况下具有优势，但对于大部分情况，我认为单个数据库多个模式的方法更加实用。

- emax

Answer 6

一些架构比一些数据库更轻量级，尽管我找不到证实这一点的参考资料。

但是，如果您真的想保持事物非常分离（而不是重构Web应用程序以便在表中添加“客户”列），您仍然可能希望使用单独的数据库：我断言您可以更轻松地恢复特定客户的数据库，而不会干扰其他客户。

- Troels Arvin

Answer 7

这要取决于您的系统可用性和连接性的设计方式。这些数据库中存储了哪些数据。如果它们是关联数据，那么它们可以放在单个DB实例上。但是，如果它们部分关联，并且如果一个系统出现故障，则部分运行，那么必须放在不同的实例上。

详细说明：

1）当您使用一个DB实例并在其中使用多个数据库时，您会遇到一个问题：如果您的连接断开（由于系统崩溃或mysql服务器停机），所有数据库都会停机，因为它们位于同一实例上，因此所有应用程序都会受到影响。

2）当您为每个数据库分别分离DB实例时，如果任何一个数据库系统停机，则其他应用程序不会受到影响。因此，只有依赖于停机数据库的应用程序会受到影响。

此外，在这两种情况下，我认为您还必须使用复制机制，以便负载均衡可以在从属数据库上完成。

- Naruto

Answer 8

使用单个数据库与多个模式进行工作是在Postgres数据库中练习的好方法，因为：

1. 在Postgres中，没有任何数据跨数据库共享。
2. 对于服务器的任何给定连接，只能访问单个数据库中的数据，即在连接请求中指定的数据库。

使用多个模式：

1. 允许许多用户使用一个数据库而不会相互干扰。
2. 将数据库对象组织成逻辑组以使它们更易于管理。
3. 第三方应用程序可以放入单独的模式中，因此它们不会与其他对象的名称冲突。

- samzna

---

这是引用自什么？ - aquirdturtle