{"id":9417,"date":"2016-11-25T15:02:36","date_gmt":"2016-11-25T14:02:36","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-3-tablespaces\/"},"modified":"2016-11-25T15:02:36","modified_gmt":"2016-11-25T14:02:36","slug":"can-i-do-it-with-postgresql-3-tablespaces","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-3-tablespaces\/","title":{"rendered":"Can I do it with PostgreSQL? – 3 – Tablespaces"},"content":{"rendered":"

In the last posts of this series we talked about restore points<\/a> and how you could do things that would require the dual table in Oracle<\/a>. 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?<\/p>\n

<\/p>\n

Before we start playing with our own tablespaces you need to know that there are two default tablespaces<\/a> in each PostgreSQL instance:<\/p>\n

(postgres@[local]:5439) [postgres] > db+\n                                  List of tablespaces\n    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description \n------------+----------+----------+-------------------+---------+--------+-------------\n pg_default | postgres |          |                   |         | 21 MB  | \n pg_global  | postgres |          |                   |         | 497 kB | \n(2 rows)\n<\/pre>\n
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.<\/p>\n
This means, whenever you create a table without specifying a tablespace in the create table statement it will go to the pg_default tablespace:<\/p>\n
(postgres@[local]:5439) [postgres] > create table t1 ( a int );\nCREATE TABLE\nTime: 99.609 ms\n(postgres@[local]:5439) [postgres] > select tablespace from pg_tables where tablename = 't1';\n tablespace \n------------\n NULL\n(1 row)\n<\/pre>\n
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<\/a>:<\/p>\n
postgres@pgbox:\/home\/postgres\/ [PG961] oid2name -t t1\nFrom database \"postgres\":\n  Filenode  Table Name\n----------------------\n     24592          t1\npostgres@pgbox:\/home\/postgres\/ [PG961] find $PGDATA -name 2459*\n\/u02\/pgdata\/PG961\/base\/13322\/24592\n<\/pre>\n
In addition oid2name tells you more about the databases and the default tablespace associated to them:<\/p>\n
postgres@pgbox:\/home\/postgres\/ [PG961] oid2name \nAll databases:\n    Oid  Database Name  Tablespace\n----------------------------------\n  13322       postgres  pg_default\n  13321      template0  pg_default\n      1      template1  pg_default\n<\/pre>\n
So far for the basics. Time to create our own tablespace. When you look at the syntax:<\/p>\n
(postgres@[local]:5439) [postgres] > h create tablespace\nCommand:     CREATE TABLESPACE\nDescription: define a new tablespace\nSyntax:\nCREATE TABLESPACE tablespace_name\n    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]\n    LOCATION 'directory'\n    [ WITH ( tablespace_option = value [, ... ] ) ]\n<\/pre>\n
… 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.<\/p>\n
Lets create our first tablespace:<\/p>\n
(postgres@[local]:5439) [postgres] > ! mkdir \/var\/tmp\/tbs1\n(postgres@[local]:5439) [postgres] > create tablespace tbs1 location '\/var\/tmp\/tbs1';\nCREATE TABLESPACE\nTime: 26.362 ms\n(postgres@[local]:5439) [postgres] > db+\n                                     List of tablespaces\n    Name    |  Owner   |   Location    | Access privileges | Options |  Size   | Description \n------------+----------+---------------+-------------------+---------+---------+-------------\n pg_default | postgres |               |                   |         | 21 MB   | \n pg_global  | postgres |               |                   |         | 497 kB  | \n tbs1       | postgres | \/var\/tmp\/tbs1 |                   |         | 0 bytes | \n(3 rows)\n<\/pre>\n
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?<\/p>\n
(postgres@[local]:5439) [postgres] > ! ls -l \/var\/tmp\/tbs1\/\ntotal 0\ndrwx------. 2 postgres postgres 6 Nov 25 11:03 PG_9.6_201608131\n<\/pre>\n
At least a directory which contains the version of PostgreSQL was created. What is inside this directory?<\/p>\n
(postgres@[local]:5439) [postgres] > ! ls -l \/var\/tmp\/tbs1\/PG_9.6_201608131\/\ntotal 0\n<\/pre>\n
Nothing, so lets create a table in this brand new tablespace:<\/p>\n
(postgres@[local]:5439) [postgres] > create table t1 ( a int ) tablespace tbs1;\nCREATE TABLE\n(postgres@[local]:5439) [postgres] > d+ t1\n                          Table \"public.t1\"\n Column |  Type   | Modifiers | Storage | Stats target | Description \n--------+---------+-----------+---------+--------------+-------------\n a      | integer |           | plain   |              | \nTablespace: \"tbs1\"\n<\/pre>\n
How does the directory look like now?:<\/p>\n
(postgres@[local]:5439) [postgres] > ! ls -l \/var\/tmp\/tbs1\/PG_9.6_201608131\/\ntotal 0\ndrwx------. 2 postgres postgres 18 Nov 25 12:02 13322\n<\/pre>\n
Ok, 13322 is the OID of the database which the table belongs to:<\/p>\n
(postgres@[local]:5439) [postgres] > ! oid2name\nAll databases:\n    Oid  Database Name  Tablespace\n----------------------------------\n  13322       postgres  pg_default\n  13321      template0  pg_default\n      1      template1  pg_default\n<\/pre>\n
And below that?<\/p>\n
(postgres@[local]:5439) [postgres] > ! ls -l \/var\/tmp\/tbs1\/PG_9.6_201608131\/13322\/\ntotal 0\n-rw-------. 1 postgres postgres 0 Nov 25 12:02 24596\n<\/pre>\n
This is the OID of the table. So in summary this is the layout you get per tablespace:<\/p>\n
|\n|---[LOCATION]\n|       |\n|       | ----- [FIXED_VERSION_DIRECTORY]\n|       |                  |\n|       |                  |---------[DATABASE_OID]\n|       |                  |              |\n|       |                  |              |-----------[TABLE_AND_INDEX_FILES_OID]\n<\/pre>\n
One point that is often forgotten is that you can set various parameters on a tablespace level:<\/p>\n
CREATE TABLESPACE tablespace_name\n    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]\n    LOCATION 'directory'\n    [ WITH ( tablespace_option = value [, ... ] ) ]\n<\/pre>\n
What you can set per tablespace is:<\/p>\n