Creating postgres schemas in specific tablespaces

Question

According to Simple Configuration Recommendations for PostgreSQL the recommended best practice for setting up the most flexible and manageable environment is to create a application specific tablespace that has its own mountpoint at /pgdata-app_tblspc and "For every schema there should be a minimum of two tablespaces. One for tables and one for indexes"

I am able to create these mount points and tablespaces, but wondering how to assign schemas to specific tablespaces. As far as I can tell, tablespaces are pegged to databases through the CREATE DATABASE ... TABLESPACE ... command, but there is no TABLESPACE directive in the CREATE SCHEMA command.

Following the logic of the Simple Configuration Recommendation document, it appears the implicit recommendation is to create one database per application, with each database mapped to two tablespaces: one for data and the other for indexes.

However, the same document goes on to say that application specific databases is not the preferred way of maintaining data separation between applications. Having one database with multiple schemas is the way to go.

What am I missing here? Appreciate any pointers.

Answer 1

• Why does CREATE SCHEMA not have a tablespace clause?

  Schemas provide a logical separation of data, while tablespaces provide a physical separation. Only objects that hold data, like tables and indexes, have a tablespace clause in their CREATE statement. A schema does not have an associated data file.

  If you want the tables that live in different schemas to reside in different tablespaces, you'll have to add a tablespace clause to each CREATE TABLE and CREATE INDEX statement.

• Should you use two tablespaces per application, one for tables and one for indexes?

  I would say that this depends on your performance requirements and the amount of data.

  If you are dealing with a multi-terabyte data warehouse and you want to optimize performance by distributing your data over different storage systems, using tablespaces will be an interesting option.

  For a smallish database I'd say that this is not worth the trouble, and you'd be better of if you buy enough RAM to fit the database in memory.

• Are different databases or different schemas the better way of separating data for different applications?

  If the applications need to access each other's data, put them in different schemas in one database. Otherwise use two databases to ensure that they cannot mess with each other's data.

Overall, tablespaces are good if you want to limit the growth of a table or the tablespaces are on different storage systems for load distribution.