Tablespace Administration in Postgres – Q&A

Share via:

Tablespace Administration in Postgres – Q&A

1.  What is tablespace in Postgres?

A. It specifies in which location contents of the tables should be stored.

  • We can’t use a database without tablespace
  • Default tablespace is pg_default
  • Users while creating objects can specify on which tablespace objects should be stored.
  • Contents will be stored in form of files.

2. What are the types of tablespaces in Postgres?

A. There are 2 types of tablespace

  • Default
  • Non-default

By default, we have 2 types of tablespaces, pg_default, and pg_global.

3. What is the default tablespace for all the databases?

A. pg_default is the default tablespace for all the databases.

Which is used for user-defined objects

The default path of pg_default tablespace is mentioned below

4. Why non-default tablespaces are created?

A. Non-default tablespaces are created in order to store the contents in other locations. Apart from pgdata.

Pgdata is the default location.

  • In order to have a bigger storage area
  • To have better performance
  • Can make default tablespace for the database.

5. What is the default location where the database files will be stored?

A. The database files will be stored in the default location mentioned below

    • Location : – $pgdata/base/oid of the database.
    • Under base location, oid numbers are the databases oid number
    • This is similar to dbid in oracle

6. How many default tablespace can the database have?

A. At any given point in time, the database can have only one default tablespace.

7. What is pg_global tablespace?

A. pg_global is used for storing internal objects i.e catalog objects

  • And the location will be the same as pg_default tablespace location i.e $pgdata location.
  • Location of pgdata :- basepath/version/data/base/oid
  • Used for dictionary objects.

8. When a client creates a table inside the database on which tablespace the table resides by default?

A. It will reside in the pg_default tablespace location of that database.

9. Can we make non-default tablespace into the default table for the database?

A. Yes it’s possible. While creating a database or altering a database.

Below is the output

    • Below are the steps while creating the database.

      • Altering the database.

10. What is oid of the pg_default and pg_global?

A. The oid of pg_default and pg_global tablespaces are mentioned below.

11. Can we see the location of default tablespaces?

A. No, we can’t see the location of default tablespaces

2 Ways to check the default tablespace details

Note: spcowner value – 10 is the Postgres owner value

12. Command to create non-default tablespace?

A.  Below is the output

Note: The location is showing as empty in the default tablespace

  • And the location which is visible is considered as non-default tablespace
  • A subdirectory is created with the name pg.

13. Command to check the pgdata location from the database level?

A.  Below is the output

14. Can one database can use multiple tablespaces?

A.  One database can use multiple tablespaces, but the default tablespace is one.

Below is the output.

Using pg_default tablespace. But objects can be stored in different tablespaces.

Newtbs is the nondefault tablespace, where sampletab table contents are stored.

15. Command to check the tablespace size or the amount of space used?

A.  Below is the command

16. How to check if the temporary tablespace is there for a particular session? And how to assign it if not exists?

A.  Below are the steps

Below is the command to check if temp tablespace is assigned for a particular session or not

Below is the command to assign to a particular session.

If no temporary tablespace is created, then it will go to pg_default.

Below is the example,

We have created a table in temp tablespace

Once logged in

Contents are removed.



Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.33 out of 5)

Add Comment