9

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.

Improve this question

1 Answer 1

Reset to default
12

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Another common use-case for TableSpace - To place frequent/heavily accessed indexes or tables on devices that perform very fast e.g., SSDs and place tables/indexes with lower access speed requirements onto non-ssd devices ex: HDDs.
It seems to me that you are saying that logical separation and physical separation can never have any commonality in logic? Thats a big statement. I don't like rules that exclude possibilities just on the basis that certain combinations simply weren't considered. There are so many use cases out there, its unreasonable to exclude them unless there is an arguable reason why it shouldn't exist - such as it causing an error state. Is there something "wrong" with an entire schema being logically categorized as within a physical tablespace? In my case I am archiving a schema - that seems reasonable.
@AlexiTheodore You are of course free to put tables in different schemas into different tablespaces. It is just a use case that is unusual enough that nobody bothered to add a special feature for it. The two concepts are orthogonal.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.