PostgreSQL Tablespaces

Summary: In this tutorial, you’ll learn about PostgreSQL tablespaces and how to create and use tablespaces to store database objects.

Introduction to PostgreSQL tablespaces #

A tablespace is a storage location on a disk where PostgreSQL stores database objects such as tables and indexes.

By default, PostgreSQL stores all database objects in the default tablespaces. However, you can create tablespaces to optimize performance and efficiently manage disk usage.

Creating tablespaces #

To create a new tablespace, you use the CREATE TABLESPACE statement with the following syntax:

CREATE TABLESPACE tablespace_name
OWNER role_name
LOCATION 'path_to_directory';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• Fisst, specify the name of the tablespace in the CREATE TABLESPACE clause.
• Second, provide the name of the owner of the tablespace. If you don’t, PostgreSQL will assign the current role as the owner.
• Third, specify the directory path where PostgreSQL stores this tablespace’s database objects.

It’s important to note two points:

• The directory of the tablespace must exist and PostgreSQL must be the owner of that directory.
• PostgreSQL must have read and write access to the directory.

We’ll create a new tablespace called pg_tablespace.

Step 1. Create a directory for the tablespace and change the owner to postgres: #

mkdir -p /var/lib/postgresql/tablespaces/my_tablespace
chown postgres:postgres /var/lib/postgresql/tablespaces/my_tablespaceCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can replace the path with a real directory path on your server.

Step 2. Create a new tablespace in PostgreSQL: #

CREATE TABLESPACE my_tablespace
    OWNER postgres
    LOCATION '/var/lib/postgresql/tablespaces/my_tablespace';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using tablespaces #

After creating the new tablespace, you can use it in statements like CREATE TABLE, CREATE INDEX, etc.

For example, the following statement creates a new table and assigns it to the pg_tablespace:

CREATE TABLE customers(
     customer_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     name VARCHAR(255) NOT NULL,
     phone VARCHAR(25) NOT NULL,
     address VARCHAR
) TABLESPACE my_tablespace;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The clause TABLESPACE my_tablespace assigns the my_tablespace to the customers table.

You can store indexes in a tablespace located in fast storage to optimize query performance. For example:

CREATE INDEX ON customers(name)
TABLESPACE my_tablespace;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following table lists all object types that you can assign tablespaces to them:

Here’s a table listing all object types that can be stored in a PostgreSQL tablespace, along with the corresponding SQL statements and their meanings:

Default tablespaces #

PostgreSQL comes with two default tablespaces:

• pg_default
• pg_global

pg_default #

The pg_default tablespace is where PostgreSQL stores all database objects (tables, indexes, sequences, etc.) unless you explicitly specify a different tablespace.

The pg_default tablespace is located in the data directory of the PostgreSQL installation.

pg_global #

The pg_global tablespace stores shared system catalogs that are global to all databases within a single PostgreSQL instance.

Why Use Tablespaces? #

• Enhance disk I/O performance by distributing data across multiple storage devices.
• Place indexes on high-speed storage like SSD for faster query performance.
• Use tablespaces for multi-tenant architectures, storing tenant-specific data in separate locations.

Summary #

• A tablespace is a storage location that holds database objects.
• Use the CREATE TABLESPACE statement to create a new tablespace.