Tablespaces are a key component for disk space management in relational database management systems. They serve as containers for storing objects such as tables, indexes, views, sequences and other data structures. Tablespaces allow database administrators to better manage disk space by allowing fine-grained management of the storage space used by the database. In this blog post, we will explore what a tablespace is, how to create it, and how to use it.

What is a Tablespace

Tablespaces are created to solve the problem of disk fragmentation, which occurs when database data files are stored on multiple physical disks. Tablespaces allow you to divide database objects into logical groups and store these groups in separate physical data files. By creating multiple tablespaces, you can simplify maintenance tasks and you can optimize the performance of the database by using different I/O channels.

Tablespaces can be of two types: permanent tablespaces and temporary tablespaces. Permanent tablespaces store objects that are not intended to be deleted, such as tables, indexes and views. Temporary tablespaces store temporary data, such as the results of a query, or a temporary table.

In addition to managing the storage, tablespaces can be used to optimize performance by grouping related objects together. For example, if you have a set of tables that are frequently queried together, you can place them in the same tablespace to improve query performance. Similarly, tablespaces can be used to simplify maintenance tasks, such as backing up or restoring the database, by allowing you to work with smaller, more manageable units of data.

How to create a Tablespace

Creating a tablespace is a straightforward process that involves using SQL commands to specify the name, location of tablespace. Here are the steps to create a tablespace:

Log in to your database management system using an account with the appropriate privileges. Open a SQL editor or command prompt. The command to create a Tablespace in SQL is the following:

CREATE TABLESPACE test_tablespace LOCATION '/Library/PostgreSQL/tablespaces/15/';

Notes:

  • The directory must exist
  • The directory must be empty
  • The directory must be owned by the user running the database

Remove a Tablespace

To delete a tablespace in PostgreSQL, you use the DROP TABLESPACE command

postgres=# DROP TABLESPACE 'test_tablespace';
ERROR:  tablespace "'test_tablespace'" is not empty

postgres=# DROP TABLESPACE 'test_tablespace';
DROP TABLE

postgres=# DROP TABLESPACE 'test_tablespace';
DROP TABLESPACE
postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

To check which objects are currently using a particular tablespace, you use the following SQL query:

postgres=# SELECT * FROM pg_catalog.pg_tables WHERE tablespace = 'test_tablespace';

schemaname | tablename | tableowner |   tablespace    | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+-----------------+------------+----------+-------------+-------------
 public     | my_table  | postgres   | test_tablespace | f          | f        | f           | f

How to use a Tablespace

Default tablespace

The default tablespace is the location where the database objects are created when no specific tablespace is specified during their creation. The default tablespace can be set at the database level or at the role level. It is recommended to set a default tablespace for the database or the role. But if you don’t define any tablespace yourself, the database will use the pg_global tablespace.

To set the default tablespace for a database or role:

ALTER DATABASE my_database SET default_tablespace = test_tablespace;

ALTER ROLE my_user SET default_tablespace = test_tablespace;

Note that the default tablespace only affects new objects created after it has been set. Existing objects will remain in their current tablespace unless they are explicitly moved.

When creating a new object

When you create an object such as a table, index or view, you can specify the Tablespace in which you want to store the object. If you do not specify a Tablespace, the object will be stored in the default Tablespace (the name is pg_default) of the database.

CREATE TABLE my_table(i int) TABLESPACE test_tablespace;

How to move existing an object into another tablespace

To move an existing object (such as a table or index) to another tablespace, you can use the ALTER statement with the SET TABLESPACE option.

ALTER TABLE my_table SET TABLESPACE new_tablespace;

To know the information about which table is on your tablespace you work on, you can use this command.

postgres=# SELECT * FROM pg_catalog.pg_tables WHERE tablespace = 'new_tablespace';

ALTER TABLESPACE can be used to change the location of an object to a tablespace.

You must own the tablespace to change the definition of a tablespace. To alter the owner, you must also be a direct or indirect member of the new owning role. Keep in mind that the operation requires an exclusive lock for the object. For the time being, no one else can access the object which is to be moved.

How to move an entire database to another tablespace

Imagine you want to move an entire database to a new or another tablespace. We have different steps to follow.

First, create the new tablespace where you want to move the database. Like we see before:

CREATE TABLESPACE new_tablespace LOCATION '/path/to/tablespaces';

After, we will stop all connections to the database. You can do this by disconnecting all clients or by using the pg_terminate_backend()

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'your_database_name';

Or you can find the different sessions using your database and you can delete them.

Then update the system catalog to reflect the new tablespace location. You can do this using the ALTER DATABASE command:

	ALTER DATABASE your_database_name SET TABLESPACE new_tablespace;

In conclusion, tablespaces are a critical feature in managing disk space in Postgres databases. By using tablespaces, database administrators can better manage the storage space used by different objects such as tables, indexes, and other data structures within the database. Postgres offers several types of tablespaces, including the default tablespace, user-defined tablespaces, and shared tablespaces, providing flexibility in organizing and allocating storage space. With the ability to create and manage tablespaces effectively, Postgres users can improve their database’s performance, scalability, and reliability. Overall, tablespaces are an essential tool for any Postgres database administrator to master in order to optimize their database management strategies.