Tablespaces in PostgreSQL A Complete Guide

Welcome to our comprehensive guide on understanding tablespace in PostgreSQL. In this article, we will delve into the concept of tablespaces and explore their significance in managing your PostgreSQL databases. Whether you are a beginner or an experienced user, understanding tablespaces is crucial for optimizing performance, organizing your database objects, and facilitating efficient backup and recovery processes. So, let’s dive in and explore the world of tablespaces in PostgreSQL.

Understanding Tablespace in PostgreSQL

In PostgreSQL, a tablespace refers to a logical name assigned to a specific location on a disk drive. This location serves as a storage area for data-files that store various database objects such as tables, views, indexes, and more.

Advantages of Using a Tablespace in PostgreSQL

One of the key advantages of utilizing tablespaces is the organization of database objects within designated locations. This organization greatly facilitates the backup and recovery processes, making them more efficient and manageable.

Another significant advantage is the impact on database performance. By strategically assigning data-files to different devices, you can optimize performance by placing frequently accessed database objects on faster storage devices while relegating tables with less frequently accessed data to slower devices.

Default Tablespaces in PostgreSQL

By default, PostgreSQL provides two tablespaces:

  1. pg_default – This tablespace is responsible for storing user data, including databases like template 0 and template 1.
  2. pg_global – This tablespace is dedicated to holding global database data, such as system catalogs.

By leveraging these default tablespaces, PostgreSQL offers a structured and efficient environment for managing your database objects.

CREATE TABLESPACE -Syntax

CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION directory_path;

On your screen, you’ll find the syntax for creating a tablespace in PostgreSQL. The statement begins with the reserved phrase “CREATE TABLESPACE.” You can write it in either uppercase or lowercase, as case doesn’t matter. However, I prefer using capital letters for SQL reserved keywords.

Following the “CREATE TABLESPACE” phrase, we need to provide the tablespace name. You have the freedom to choose any name for your tablespace, but it’s important to ensure it is logical. Additionally, note that names starting with “PG_” are reserved for PostgreSQL and should be avoided.

Next, we encounter the “OWNER” keyword. This allows us to specify the username who will own the tablespace. While this parameter is optional, if omitted, the user executing the “CREATE TABLESPACE” statement will be assigned as the owner by default.

Subsequently, we come across the “LOCATION” keyword. With this keyword, we define the location on our disk drive where we want to create the tablespace. We specify a directory path using this keyword. Typically, this involves using an absolute path to an empty directory. It is crucial to ensure that the user has ownership of this directory in order to perform read and write operations.

Create A Directory for Tablespace in PostgreSQL

To create a tablespace, it is essential to first create a directory. If you have installed your database directly on your Mac or Windows system, simply create a folder on your system. That’s all you need to do.

However, if you, like me, have set up your database on Docker, you need to follow a process to create the directory. Here’s how:

Step 1: Start Your Docker

Begin by ensuring that your Docker is running. The easiest way to do this is through the Docker Desktop App. Simply double-click on the Docker Desktop app to start it.

Step 2: Start The Postgres Container

After starting the Docker machine, you must run the container that contains your PostgreSQL Database. In my case, the container’s name is “Postgres_img.” To start this container, open your Command Prompt or Terminal and execute the following command:

>Docker Start <your_container_name>
#for Example
>Docker Start Postgres_img

Step 3: Login as ROOT Into Bash

To create the directory, we must access the bash shell with the ROOT user. In order to execute the “Make Directory (mkdir)” command, we will utilize this bash shell To access the bash shell, use the same command prompt from the previous step and enter this command.

>docker exec --user root -it <container_name> bash
#For Example
>docker exec --user root -it postgres_img bash

Step 4: Create The Directory For Tablespace in PostgreSQL

For this demonstration, I will create a directory named “tablespace” inside “/home”. Let’s proceed with the creation:

>mkdir /home/tablespace

Upon successful execution of this command, your “tablespace” directory will be created.

Step 5: Fix The Permission

Merely creating a directory won’t suffice. To utilize this directory for our tablespace, it’s essential to ensure that we possess all the necessary permissions on it. To rectify these permissions, we will alter the “Owner” and “Group” of this directory from root to postgres. To accomplish this, execute the command provided below.

>chown postgres:postgres /home/tablespace

Step 6: Set Permission on The Postgres Group

After changing the group of our directory from root to postgres, the next step is to grant “read, write, and execute” permissions to the postgres group. To achieve this, execute the following command.

>chmod g+rwx /home/tablespace

That completes the necessary steps. You can utilize the aforementioned instructions to create a directory anywhere within the file system of your Docker container. Now, you are fully prepared to create your own tablespace.

CREATE TABLESPACE in PostgreSQL- Example

Let’s create a tablespace using the provided syntax. We’ll name this tablespace “ts_users” and assign it to the user “postgres,” who is the default super user included in the database installation. To specify the location, we’ll utilize a directory called “tablespace,” which I’ve already set up within my Postgres container on Docker.

CREATE TABLESPACE ts_users
OWNER postgres
LOCATION '/var/lib/postgresql/data/tablespace';

How to View Tablespace Details?

To begin, let’s quickly clear the screen.

Suppose you want to view the details of all the tablespaces you have created thus far. For this purpose, Postgres offers a backslash command:

\db

Executing this command will display brief information about the tablespaces.

If you desire more comprehensive details, use

\db+

This command will provide a detailed overview of your tablespaces. Let’s execute it. As demonstrated below,

And with that, we wrap up this tutorial. We appreciate you taking the time to read through this blog post. In our next article, we will delve deeper into the practical aspects of tablespaces and guide you through the process of creating a database in PostgreSQL.

If you found the information in this blog post helpful, we would be grateful if you could show your support by leaving a like and considering subscribing to our blog for future updates.

Thank you for being a part of our community. Stay tuned for more valuable content!

Best regards, Manish from RebellionRider.com