What is the hierarchy of Database related objects in postgres SQL? Should it be like, table space must be created at instance level unlike other RDBMS(where we have table space under database). If so we create the table space at instance level, what is the purpose of database? and what is difference between table space and database on postgres server?
2 Answers
An instance (in PostgreSQL called cluster) is a data directory initialized with initdb with a PostgreSQL server process.
A tablespace is a directory outside the data directory where objects can also be stored. Tablespaces are useful for certain corner cases like distributing I/O or limiting space for a subset of the data.
A database is a container for objects with permissions, organized in schemas.
The difference is that tablespaces are a physical concept, it defines a space where the data are stored, while databases are a logical concept about how data are organized, what they mean, how they are related, who is allowed to access them and so on.
The two concepts are orthogonal.
A database can have tables in several tablespaces, and a tablespace can contain data from several databases.
4 Comments
srivatsasa
so as like other RDBMS, won't we have a table space under a database? .. because in other RDBMS like DB2, we will have in this hierarchy server->instance->database->tablespace->tables.
Laurenz Albe
Right, it is different here. See the documentation.
Juan Carlos Oropeza
at the end do you mean
data from several databases. ??Laurenz Albe
Yes. I have fixed the answer.
Database is where you organize all your objects. Tablespace is just storage space for those object.
You can storage your db object in different Tablespace. For example one table is storage in a Tablespace in diskA but another Table use a Tablespace in diskB to improve the performance. Or maybe you need a tablespace for big tables and dont mind use a slow big HDD for those objects.
answered Nov 16, 2017 at 14:23
Juan Carlos Oropeza
48.4k14 gold badges87 silver badges128 bronze badges