使用多个数据库各使用一个模式，还是使用一个数据库同时使用多个模式更好?

问题描述

在此评论之后对于我的一个问题，我在考虑使用具有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).

从现在开始，即使应用程序结构已完全更改，单db-多次模式"方法对我来说也非常有效.我几乎忘记了:我的所有数据库/方案总是会具有相同的结构！现在，每个模式都有其自己的结构，该结构会动态响应用户数据流.

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.

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