When to Use Multiple Clusters, Databases, or Schemas?

April 23, 2012

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:
 

Feature Cluster Database Schema
Isolated Server Start/Stop      ✓    
Connection Control      ✓      ✓  
Private System Tables      ✓      ✓ &nnbsp;
Private Plug-Ins      ✓      ✓  
Isolated Administration      ✓    
Shared Administration        ✓      ✓
Isolated Resource Usage      ✓    
Shared Resource Usage(1)        ✓      ✓
Data Isolation(2)      ✓      ✓  
Cross-Container Queries          ✓


(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.

Share this

Relevant Blogs

The Varying Cost of Synchronous Replication

Synchronous replication in Postgres is expensive. Very expensive. For any transaction to complete in a synchronous context, it must be acknowledged by at least one allocated replica system. Depending on...
February 23, 2023

More Blogs

What’s New in Postgres 15: Webinar Highlights

Recently, EDB hosted an exciting webinar entitled “What’s New in Postgres 15,” which covered the development process for the latest version of PostgreSQL, as well as new features and benefits...
January 15, 2023