使用多个数据库和每个数据库一个模式更好，还是一个数据库有多个模式更好? [英] Is it better to use multiple databases with one schema each, or one database with multiple schemas?

问题描述

在此评论之后对于我的一个问题，我在考虑使用具有 X 模式的数据库是否更好，反之亦然.

After this comment to one of my questions, I'm thinking if it is better using one database with X schemas or vice versa.

我正在开发一个 Web 应用程序，当人们注册时，我创建(实际上)一个数据库(不，它不是社交网络:每个人都必须有权访问自己的数据，并且永远不会看到其他用户的数据).这就是我用于我的应用程序的先前版本(仍在 MySQL 上运行)的方式:通过 Plesk API，对于每次注册，我都这样做:

I'm developing a web application where, when people register, I create (actually) a database (no, it's not a social network: everyone must have access to his own data and never see the data of the other user). That's the way I used for the previous version of my application (that is still running on MySQL): through the Plesk API, for every registration, I do:

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

现在，我需要对 PostgreSQL 做同样的事情(项目越来越成熟，MySQL 不能满足所有需求).我需要让所有的数据库/模式备份都是独立的:pg_dump 在两种方式下都能完美运行，对于可以配置为仅访问一个模式或一个数据库的用户也是如此.

Now, I'll need to do the same with PostgreSQL (the project is getting mature and MySQL don't fulfil all the needs). I need to have all the databases/schemas backups independent: pg_dump works perfectly in both ways, and the same for the users that can be configured to access just one schema or one database.

那么，假设您是比我更有经验的 PostgreSQL 用户，您认为最适合我的情况的解决方案是什么，为什么?使用 $x 数据库而不是 $x 模式会有性能差异吗?将来什么解决方案会更好地维护(可靠性)?我所有的数据库/模式将总是具有相同的结构！

So, assuming you are more experienced PostgreSQL users than me, what do you think is the best solution for my situation, and why? Will there be performance differences using $x database instead of $x schemas? And what solution will be better to maintain in the future (reliability)? All of my databases/schemas will always have the same structure!

对于备份问题(使用 pg_dump)，使用一个数据库和多个模式可能更好，一次转储所有模式:恢复将非常简单，在开发机器中加载主转储，然后只转储和恢复模式需要:还有一个额外的步骤，但转储所有模式似乎比一个一个转储更快.

For the backups issue (using pg_dump), is maybe better using one database and many schemas, dumping all the schemas at once: recovering will be quite simple loading the main dump in a development machine and then dump and restore just the schema needed: there is one additional step, but dumping all the schema seem faster than dumping them one by one.

嗯，在过去的两年中，应用程序的结构和设计发生了很大变化.我仍在使用一个数据库有许多模式"-方法，但是，我的应用程序的每个版本都有一个数据库:

Well, the application structure and design changed so much during those last two years. I'm still using the "one db with many schemas" -approach, but still, I have one database for each version of my application:

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

对于备份，我定期转储每个数据库，然后在开发服务器上移动备份.我也在使用 PITR/WAL 备份，但正如我之前所说，我不太可能必须一次恢复所有数据库.所以今年可能会被驳回(在我的情况下不是最好的方法).

For backups, I'm dumping each database regularly, and then moving the backups on the development server. I'm also using the PITR/WAL backup but, as I said before, it's not likely I'll have to restore all database at once. So it will probably be dismissed this year (in my situation is not the best approach).

从现在起，一个数据库多模式方法对我来说非常有效，即使应用程序结构完全改变.我差点忘了:我所有的数据库/模式总是具有相同的结构！现在，每个模式都有自己的结构，可以根据用户数据流动态变化.

The one-db-many-schema approach worked very well for me since now, even if the application structure is totally changed. I almost forgot: all of my databases/schemas will always have the same structure! Now, every schema has its own structure that change dynamically reacting to users data flow.

推荐答案

PostgreSQL架构"与 MySQL数据库"大致相同.在 PostgreSQL 安装上有许多数据库可能会出现问题；拥有许多模式将毫无问题地工作.因此，您肯定希望在该数据库中使用一个数据库和多个架构.

A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.

这篇关于使用多个数据库和每个数据库一个模式更好，还是一个数据库有多个模式更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！