I previously explained that Postgres allows multiple databases per cluster. The outstanding question might be, when should I use multiple clusters, multiple databases, or multiple schemas? The following table outlines the advantages of the various container types:
|Isolated Server Start/Stop||✓|
|Private System Tables||✓||✓||&nnbsp;|
|Isolated Resource Usage||✓|
|Shared Resource Usage(1)||✓||✓|
(1) A large number of data containers increases the usefulness of resource sharing, e.g. shared_buffers. Resource sharing includes log shipping and streaming replication sharing.
(2) User and database names, being global objects, are visible in all databases. It is impossible to query across databases, except via an external database session, e.g. dblink. Schema permissions allow data access control, but pg_class still shows all tables defined in the database.
Hopefully this chart helps users choose the proper container for their data needs.
Enterprise-ready Postgres tools for high availability, monitoring, and disaster recovery. Download Now.
Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value of Postgres advances and new technology enhancements. He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.