Infrastructure at your Service

Daniel Westermann

Can I do it with PostgreSQL? – 3 – Tablespaces

In the last posts of this series we talked about restore points and how you could do things that would require the dual table in Oracle. In this post we’ll look at tablespaces. This is one of the features that is available in PostgreSQL but is totally different from what you know from Oracle. In Oracle you need to create a datafile which is attached to a tablespace. Once you have this you can start creating tables in there if you have the permissions to do so. How does this work in PostgreSQL?

Before we start playing with our own tablespaces you need to know that there are two default tablespaces in each PostgreSQL instance:

([email protected][local]:5439) [postgres] > \db+
                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description 
------------+----------+----------+-------------------+---------+--------+-------------
 pg_default | postgres |          |                   |         | 21 MB  | 
 pg_global  | postgres |          |                   |         | 497 kB | 
(2 rows)

When you create a table and do not specify in which tablespace you want to get it created it will be created in the pg_default tablespace (this is the default tablespace for template0 and template1 and therefore will be the default for every user created database if not overwritten). pg_global contains the shared system catalog.

This means, whenever you create a table without specifying a tablespace in the create table statement it will go to the pg_default tablespace:

([email protected][local]:5439) [postgres] > create table t1 ( a int );
CREATE TABLE
Time: 99.609 ms
([email protected][local]:5439) [postgres] > select tablespace from pg_tables where tablename = 't1';
 tablespace 
------------
 NULL
(1 row)

NULL, in this case, means default. If you want to know where exactly the files that make up the tables are you can use oid2name:

[email protected]:/home/postgres/ [PG961] oid2name -t t1
From database "postgres":
  Filenode  Table Name
----------------------
     24592          t1
[email protected]:/home/postgres/ [PG961] find $PGDATA -name 2459*
/u02/pgdata/PG961/base/13322/24592

In addition oid2name tells you more about the databases and the default tablespace associated to them:

[email protected]:/home/postgres/ [PG961] oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13322       postgres  pg_default
  13321      template0  pg_default
      1      template1  pg_default

So far for the basics. Time to create our own tablespace. When you look at the syntax:

([email protected][local]:5439) [postgres] > \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

… this is quite different from what you know when you work with Oracle. The important point for now is the “LOCATION”. This refers to a directory somewhere the PostgreSQL owner has write access to. This can be a local directory, can be a directory on any storage the host has access to and it even can be on a ramdisk. It really doesn’t matter as long as the PostgreSQL OS user has write permissions to it.

Lets create our first tablespace:

([email protected][local]:5439) [postgres] > \! mkdir /var/tmp/tbs1
([email protected][local]:5439) [postgres] > create tablespace tbs1 location '/var/tmp/tbs1';
CREATE TABLESPACE
Time: 26.362 ms
([email protected][local]:5439) [postgres] > \db+
                                     List of tablespaces
    Name    |  Owner   |   Location    | Access privileges | Options |  Size   | Description 
------------+----------+---------------+-------------------+---------+---------+-------------
 pg_default | postgres |               |                   |         | 21 MB   | 
 pg_global  | postgres |               |                   |         | 497 kB  | 
 tbs1       | postgres | /var/tmp/tbs1 |                   |         | 0 bytes | 
(3 rows)

What happened? The first thing to notice is that we can now see the “Location” column populated when we display all the tablespaces and that the size of our new tablespace is zero (well, not surprising as nothing is created in the tablespace right now). Did PostgreSQL already create datafiles in this location you might ask?

([email protected][local]:5439) [postgres] > \! ls -l /var/tmp/tbs1/
total 0
drwx------. 2 postgres postgres 6 Nov 25 11:03 PG_9.6_201608131

At least a directory which contains the version of PostgreSQL was created. What is inside this directory?

([email protected][local]:5439) [postgres] > \! ls -l /var/tmp/tbs1/PG_9.6_201608131/
total 0

Nothing, so lets create a table in this brand new tablespace:

([email protected][local]:5439) [postgres] > create table t1 ( a int ) tablespace tbs1;
CREATE TABLE
([email protected][local]:5439) [postgres] > \d+ t1
                          Table "public.t1"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 a      | integer |           | plain   |              | 
Tablespace: "tbs1"

How does the directory look like now?:

([email protected][local]:5439) [postgres] > \! ls -l /var/tmp/tbs1/PG_9.6_201608131/
total 0
drwx------. 2 postgres postgres 18 Nov 25 12:02 13322

Ok, 13322 is the OID of the database which the table belongs to:

([email protected][local]:5439) [postgres] > \! oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13322       postgres  pg_default
  13321      template0  pg_default
      1      template1  pg_default

And below that?

([email protected][local]:5439) [postgres] > \! ls -l /var/tmp/tbs1/PG_9.6_201608131/13322/
total 0
-rw-------. 1 postgres postgres 0 Nov 25 12:02 24596

This is the OID of the table. So in summary this is the layout you get per tablespace:

|
|---[LOCATION]
|       |
|       | ----- [FIXED_VERSION_DIRECTORY]
|       |                  |
|       |                  |---------[DATABASE_OID]
|       |                  |              |
|       |                  |              |-----------[TABLE_AND_INDEX_FILES_OID]

One point that is often forgotten is that you can set various parameters on a tablespace level:

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

What you can set per tablespace is:

This can be very helpful when you have tablespaces on disks (ramdisk?) that have very different performance specifications.

A very important point to keep in mind: Each tablespace you create in PostgreSQL creates a symlink in the clusters data directory:

CREATE TABLESPACE tablespace_name
[email protected]:/home/postgres/ [PG961] ls -l $PGDATA/pg_tblspc 
total 0
lrwxrwxrwx. 1 postgres postgres 13 Nov 25 11:03 24595 -> /var/tmp/tbs1

Again, the number (24595) is the OID, in this case of the tablespace:

|
([email protected][local]:5439) [postgres] > select oid,spcname from pg_tablespace where spcname = 'tbs1';
  oid  | spcname 
-------+---------
 24595 | tbs1
(1 row)

This is important to know because when you do backups of you PostgreSQL instance it is critical that you backup the tablespaces as well. You can find all the pointers/symlinks in the pg_tblspc directory.

What else can you do with tablespaces? Of course you can change the default tablespace for the whole instance:

|
([email protected][local]:5439) [postgres] > alter system set default_tablespace='tbs1';
ALTER SYSTEM
Time: 120.406 ms

([email protected][local]:5439) [postgres] > select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 4.279 ms
([email protected][local]:5439) [postgres] > show default_tablespace ;
 default_tablespace 
--------------------
 tbs1
(1 row)

You can assign a tablespace to a database:

|
([email protected][local]:5439) [postgres] > create database db1 TABLESPACE = tbs1;
CREATE DATABASE
Time: 1128.020 ms
([email protected][local]:5439) [postgres] > \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 db1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7233 kB | tbs1       | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7343 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7233 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7233 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            | 

You can make someone else the owner of a tablespace:

|
([email protected][local]:5439) [postgres] > create user u1 password 'u1';
CREATE ROLE
Time: 31.414 ms
([email protected][local]:5439) [postgres] > ALTER TABLESPACE tbs1 OWNER TO u1;
ALTER TABLESPACE
Time: 2.072 ms
([email protected][local]:5439) [postgres] > \db
          List of tablespaces
    Name    |  Owner   |   Location    
------------+----------+---------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs1       | u1       | /var/tmp/tbs1
(3 rows)

And finally you can set one or more tablespaces to be used as temporary tablespaces:

|
([email protected][local]:5439) [postgres] > alter system set temp_tablespaces='tbs1';
ALTER SYSTEM
Time: 4.175 ms

([email protected][local]:5439) [postgres] > select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 3.638 ms
([email protected][local]:5439) [postgres] > show temp_tablespaces ;
 temp_tablespaces 
------------------
 tbs1
(1 row)

Conclusion: Yes, you can have tablespaces in PostgreSQL and they give you great flexibility on how you can organize your PostgreSQL files on disk. The implementation is very different from other vendors, though.

5 Comments

  • Hi Daniel,
    Here is an idea for a future “Can I do it with PostgreSQL?”. My favorite Oracle feature: transportable tablespaces.
    Cheers,
    Franck.

  • posh kanta says:

    HI Daniel,

    How we can calculate the used and free space of tablespace and database like as in Oracle database. is there any query and dictionary like dba_free_space,dba_data_files so that we can calculate the used and free space by database as well as tablesapces.

    Thanks,
    Posh Kanta

    • Daniel Westermann says:

      Hi posh,

      as you can not use tablespaces to enforce space usage there is not way to do that in PostgreSQL. If you want to restrict space usage you’d need to do that on the file system level.

      Cheers,
      Daniel

  • posh kanta says:

    Thanks a lot Daniel for prompt response.
    Like below query how we can use to calculate free space in postgresql database..If new tablespace created how we can limit space on this database? can we compare and is it possible to see the report like as Oracle database.

    SELECT pg_database.datname as “database_name”, pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;

    If you want to restrict space usage you’d need to do that on the file system level.

    could you please post some demo how to restrict space on file system level.?

    If any database is created in any mount point (eg /u05 which has 500GB space and if database size is autoextesible, can database/tablespace grow till 500GB ? )

    Thanks & Regards,
    Posh Kanta

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure