Postgresql - Tablespace

Postgres Tablespace

Tablespace is a storage location where PostgreSQL database objects such as tables and indexes can be stored. It allows you to define the location of the data files on the file system, which can be useful for performance optimization, data organization, and management.

About Tablespace

It is extenal part of base directory, when created a softlink will be created to pg_tblspc


There are 2 default tablspace
✔pg_default -for storing user data
✔pg_global - used ONLY for shared system catalog

  • Tablespace - Commands

    ✔ CREATE TABLE table1(a int) TABLESPACE USER_DATA;

    ✔ SET default_tablespace = USER_DATA ( Can hold only one tablespace at a time)
    temp_tablespaces parameter is used to hold the tablespace where sorting / indexes and for temporary tablespaces.
    temp_tablespaces='TEMP1','TEMP2'
    ( can have more than one temp tablespace)
    Create Tablespace - Commands
    ✔ Command to create a tablespace in postgreSQL.
    CREATE TABLESPACE USER_DATA LOCATION '/data/USER_DATA';
    ✔ Assign username to a tablespace
    alter user gopi set default_tablespace = USER_DATA;
    ✔ Rename tablespace to new tablespace
    ALTER TABLESPACE USER_DATA RENAME TO NEW_USER_DATA;
    ✔ Change tablespace for a owner
    ✔ ALTER TABLESPACE USER_DATA OWNER TO new_owner;
    ✔ set search path for a owner.
    ALTER USER gopi SET search_path = 'schema_name';
    ✔ set parameter to a owner
    ALTER USER gopi SET work_mem = '64MB';
    ✔ Creation of tablespace for database
    create database DB1 tablespace user_data;
    ✔ altering of tablespace for database
    alter database DB1 set tablespace user_data;
    ✔ droping tablespace
    drop tablespace user_data; -- make sure tablespace is empty
    ✔ Queries & Views
    pg_tablespace : select oid,spcname,spcowner from pg_tablespace;
    pg_class : select oid,relname,relnamespace,reltype,relowner,relfilenode,reltablespace,relpages,reltuples from pg_class where relname in ('ta1','ta');
    pg_user :select usename,usesysid from pg_user;
    pg_database :select datname,oid,dattablespace from pg_Database;
    pg_tables : select * from pg_tables
    ✔ Queries 
                                  select  
                                d.datname,a.oid,a.relname,a.relnamespace,a.reltype,c.usename,a.relfilenode, a.relpages,a.reltuples,b.spcname
                              from pg_class a,
                                pg_tablespace b,
                                pg_user c,
                                pg_database d
                              where a.reltablespace = b.oid
                                and a.relowner = c.usesysid
                                and d.dattablespace = b.oid
                              ;