{"id":13629,"date":"2020-03-14T09:29:53","date_gmt":"2020-03-14T08:29:53","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/"},"modified":"2020-03-14T09:29:53","modified_gmt":"2020-03-14T08:29:53","slug":"about-temp_tablespaces-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/","title":{"rendered":"About temp_tablespaces in PostgreSQL"},"content":{"rendered":"

There often is a discussion if tablespaces should be used in PostgreSQL. People used to work with Oracle are often a bit surprised by this discussion as there is no way around using tablespaces in that technology. PostgreSQL does not force you to use tablespaces at all and I am pretty sure that there are plenty of PostgreSQL installations out there that do not use them and never even considered using them and that is absolutely fine. In our PostgreSQL basics workshop<\/a> we cover tablespaces but recommend not to use them if there are no good reasons. Implementing tablespaces in PostgreSQL just because you are forced to use them in other databases is not the way to go. In this post I’d like to talk about one reason where tablespaces can make sense, more precisely: temp_tablespaces<\/a>. <\/p>\n

<\/p>\n

As the name implies, temporary tablespaces are there for temporary objects. The question is then, what exactly is considered as a temporary object? Well, the obvious case is temporary tables. Without any temporary tablespaces, when you create a temporary table, the temporary files get created in the default tablespace of the current database. In a standard PostgreSQL setup it looks like this:<\/p>\n

\npostgres=# l+\n                                                                    List of databases\n   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 \n-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------\n postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 872 MB  | pg_default | default administrative connection database\n template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +| 7393 kB | pg_default | unmodifiable empty database\n           |          |          |             |             | postgres=CTc\/postgres |         |            | \n template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +| 7393 kB | pg_default | default template for new databases\n           |          |          |             |             | postgres=CTc\/postgres |         |            | \n<\/pre>\n
All the databases have a default tablespace called “pg_default” which is a kind of pseudo tablespace as it does not really exist. Asking the catalog about the location of that tablespace will show an empty location:<\/p>\n
\npostgres=# select spcname AS \"Name\"\npostgres-#      , pg_catalog.pg_get_userbyid(spcowner) AS \"Owner\"\npostgres-#      , pg_catalog.pg_tablespace_location(oid) AS \"Location\"\npostgres-#   from pg_catalog.pg_tablespace\npostgres-#  where pg_catalog.pg_tablespace.spcname = 'pg_default'\npostgres-#  order by 1;\n    Name    |  Owner   | Location \n------------+----------+----------\n pg_default | postgres | \n(1 row)\n<\/pre>\n
If we create temporary objects, where will the files be created then?<\/p>\n
\npostgres=# create temporary table tmp1 ( a int, b text, c date );\nCREATE TABLE\npostgres=# select pg_relation_filepath('tmp1');\n pg_relation_filepath \n----------------------\n base\/12732\/t3_16436\n(1 row)\n<\/pre>\n
This is the standard directory of my “postgres” database:<\/p>\n
\npostgres@centos8pg:\/home\/postgres\/ [pgdev] cd $PGDATA\npostgres@centos8pg:\/u02\/pgdata\/DEV\/ [pgdev] oid2name \nAll databases:\n    Oid  Database Name  Tablespace\n----------------------------------\n  12732       postgres  pg_default\n  12731      template0  pg_default\n      1      template1  pg_default\npostgres@centos8pg:\/u02\/pgdata\/DEV\/ [pgdev] ls -l base\/12732\/t3_16436\n-rw-------. 1 postgres postgres 0 Mar 12 18:17 base\/12732\/t3_16436\n<\/pre>\n
So, by default, files required for temporary tables go to the same location as all the other files that make up the specific database. If we populate the temporary table the files will grow, of course:<\/p>\n
\npostgres=# insert into tmp1 (a,b,c) select i,i::text,now() from generate_series(1,100) i;\nINSERT 0 100\npostgres=# ! ls -la $PGDATA\/base\/12732\/t3_16436\n-rw-------. 1 postgres postgres 8192 Mar 12 18:41 \/u02\/pgdata\/DEV\/base\/12732\/t3_16436\npostgres=# insert into tmp1 (a,b,c) select i,i::text,now() from generate_series(1,1000) i;\nINSERT 0 1000\npostgres=# ! ls -la $PGDATA\/base\/12732\/t3_16436\n-rw-------. 1 postgres postgres 49152 Mar 12 18:42 \/u02\/pgdata\/DEV\/base\/12732\/t3_16436\npostgres=# \n<\/pre>\n
The consequence of that is, that I\/O for temporary tables will compete with I\/O for all the other objects in this PostgreSQL cluster. Another consequence is, that temporary tables can potentially fill your file system until it is full and no one will be be able to work from then on. This is the first reason for creating one or more dedicated temporary tablespaces: By doing this you can avoid that temporary tables going crazy impact your whole cluster as long as the temporary tablespace is on it’s own file system.<\/p>\n
Creating a temporary tablespace is not different from creating a normal tablespace as it is actually exactly the same:<\/p>\n
\npostgres=# ! mkdir \/var\/tmp\/tbstmp\npostgres=# create tablespace tbstmp location '\/var\/tmp\/tbstmp';\nCREATE TABLESPACE\npostgres=# db+\n                                      List of tablespaces\n    Name    |  Owner   |    Location     | Access privileges | Options |  Size   | Description \n------------+----------+-----------------+-------------------+---------+---------+-------------\n pg_default | postgres |                 |                   |         | 886 MB  | \n pg_global  | postgres |                 |                   |         | 575 kB  | \n tbstmp     | postgres | \/var\/tmp\/tbstmp |                   |         | 0 bytes | \n(3 rows)\n<\/pre>\n
Once we have the new tablespace we can tell PostgreSQL to use it as the default for temporary objects:<\/p>\n
\npostgres=# alter system set temp_tablespaces = 'tbstmp';\nALTER SYSTEM\npostgres=# select pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n\npostgres=# show temp_tablespaces ;\n temp_tablespaces \n------------------\n tbstmp\n(1 row)\n<\/pre>\n
Creating another temporary table will result in the files being created in the new location:<\/p>\n
\npostgres=# create temporary table tmp2 ( a int, b text, c date );\nCREATE TABLE\npostgres=# select pg_relation_filepath('tmp2');\n              pg_relation_filepath              \n------------------------------------------------\n pg_tblspc\/16442\/PG_13_202003051\/12732\/t3_16443\n(1 row)\n\npostgres=# ! ls -la $PGDATA\/pg_tblspc\/\ntotal 4\ndrwx------.  2 postgres postgres   19 Mar 12 18:50 .\ndrwx------. 20 postgres postgres 4096 Mar 12 18:54 ..\nlrwxrwxrwx.  1 postgres postgres   15 Mar 12 18:50 16442 -> \/var\/tmp\/tbstmp\npostgres=# ! ls -la $PGDATA\/pg_tblspc\/16442\/\ntotal 0\ndrwx------. 3 postgres postgres  29 Mar 12 18:50 .\ndrwxrwxrwt. 7 root     root     163 Mar 12 18:49 ..\ndrwx------. 3 postgres postgres  19 Mar 12 18:53 PG_13_202003051\npostgres=# ! ls -la $PGDATA\/pg_tblspc\/16442\/PG_13_202003051\/\ntotal 0\ndrwx------. 3 postgres postgres 19 Mar 12 18:53 .\ndrwx------. 3 postgres postgres 29 Mar 12 18:50 ..\ndrwx------. 2 postgres postgres 54 Mar 12 18:53 12732\npostgres=# ! ls -la $PGDATA\/pg_tblspc\/16442\/PG_13_202003051\/12732\/\ntotal 8\ndrwx------. 2 postgres postgres   54 Mar 12 18:53 .\ndrwx------. 3 postgres postgres   19 Mar 12 18:53 ..\n-rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16443\n-rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16446\n-rw-------. 1 postgres postgres 8192 Mar 12 18:53 t3_16448\n<\/pre>\n
If you are wondering why there are three files, here is the answer:<\/p>\n
\npostgres=# select relname from pg_class where oid in (16443,16446,16448);\n       relname        \n----------------------\n pg_toast_16443\n pg_toast_16443_index\n tmp2\n(3 rows)\n<\/pre>\n
The toast<\/a> objects get created as well as I have a “text” column in my temporary table. Creating a temporary table with data types that do not require toast objects will result in one file only:<\/p>\n
\npostgres=# create temporary table tmp3 ( a int, b date );\nCREATE TABLE\npostgres=# select pg_relation_filepath('tmp3');\n              pg_relation_filepath              \n------------------------------------------------\n pg_tblspc\/16442\/PG_13_202003051\/12732\/t3_16449\n(1 row)\npostgres=# ! ls -la $PGDATA\/pg_tblspc\/16442\/PG_13_202003051\/12732\/\ntotal 8\ndrwx------. 2 postgres postgres   70 Mar 12 19:07 .\ndrwx------. 3 postgres postgres   19 Mar 12 18:53 ..\n-rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16443\n-rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16446\n-rw-------. 1 postgres postgres 8192 Mar 12 18:53 t3_16448\n-rw-------. 1 postgres postgres    0 Mar 12 19:07 t3_16449\n<\/pre>\n
So for now we know that all temporary tables will go to the new temporary table space. What else will go there from now on. There is a parameter log_temp_files<\/a> which can be used to report temp file usage into the PostgreSQL log file and this comes quite handy if you want to know what goes there. The default setting is “-1” which means of, “0” means log everything, all other values greater than “1” specify the minimum size of the temp files for being logged. Setting it to “0” will, as said, log all temp files being created in the background so let’s do that:<\/p>\n
\npostgres=# alter system set log_temp_files = 0;\nALTER SYSTEM\npostgres=# select pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n<\/pre>\n
Creating another temporary table and then checking the log file will confirm that this is working and we’ll get the information we want:<\/p>\n
\npostgres=# create temporary table tmp4 as select * from generate_series(1,1000000);\nSELECT 1000000\n<\/pre>\n
The entry in the log file will look like this and it confirms that the temporary files have been written to the temporary tablespsace we created above:<\/p>\n
\n2020-03-13 02:33:35.658 CET - 1 - 10535 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10535.0\", size 14000000\n2020-03-13 02:33:35.658 CET - 2 - 10535 - [local] - postgres@postgres STATEMENT:  create temporary table tmp4 as select * from generate_series(1,1000000);\n<\/pre>\n
But: If you create, with the default configuration of PostgreSQL, this temporary table:<\/p>\n
\npostgres=# create temporary table tmp4 as select * from generate_series(1,100000);\nSELECT 10000\n<\/pre>\n
… you will not see any lines in the log file for this. Why? Because there is temp_buffers<\/a> and temporary files will be only be reported in the log file if they exceed the value of this parameter. In the default configuration this is ‘8MB’ and that is not enough for the smaller temporary table to be logged. Decreasing the parameter will log the temporary files for the smaller table as well:<\/p>\n
\npostgres=# set temp_buffers = '1024kB';\nSET\npostgres=# create temporary table tmp5 as select * from generate_series(1,100000);\nSELECT 100000\n<\/pre>\n
So now we know how to log the creation of temporary files to the PostgreSQL log file. What other operations will cause temporary file to be created? Sorts?<\/p>\n
\npostgres=# set work_mem = '64kB';\nSET\npostgres=# select * from generate_series(1,1000000) order by random();\n<\/pre>\n
Yes, definitely:<\/p>\n
\n2020-03-13 02:47:14.297 CET - 19 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.2\", size 26083328\n2020-03-13 02:47:14.297 CET - 20 - 10609 - [local] - postgres@postgres STATEMENT:  select * from generate_series(1,1000000) order by random();\n2020-03-13 02:47:14.298 CET - 21 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.1\", size 14000000\n2020-03-13 02:47:14.298 CET - 22 - 10609 - [local] - postgres@postgres STATEMENT:  select * from generate_series(1,1000000) order by random();\n2020-03-13 02:47:14.298 CET - 23 - 10609 - [local] - postgres@postgres LOG:  duration: 2994.386 ms  statement: select * from generate_series(1,1000000) order by random();\n<\/pre>\n
What about creating indexes?<\/p>\n
\npostgres=# create table tt1 as select * from generate_series(1,1000000);\nSELECT 1000000\npostgres=# create index ii1 on tt1(generate_series );\nCREATE INDEX\n<\/pre>\n
Yes, that creates temporary files as well:<\/p>\n
\n2020-03-13 02:54:00.933 CET - 33 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.0.sharedfileset\/0.0\", size 12394496\n2020-03-13 02:54:00.933 CET - 34 - 10609 - [local] - postgres@postgres STATEMENT:  create index ii1 on tt1(generate_series );\n2020-03-13 02:54:00.934 CET - 35 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.0.sharedfileset\/1.0\", size 7659520\n2020-03-13 02:54:00.934 CET - 36 - 10609 - [local] - postgres@postgres STATEMENT:  create index ii1 on tt1(generate_series );\n2020-03-13 02:54:00.948 CET - 37 - 10609 - [local] - postgres@postgres LOG:  duration: 1149.625 ms  statement: create index ii1 on tt1(generate_series );\n<\/pre>\n
Foreign keys?<\/p>\n
\npostgres=# create table ttt1 as select * from generate_series(1,1000000) a;\nSELECT 1000000\npostgres=# create unique index iii1 on ttt1(a);\nCREATE INDEX\npostgres=# insert into ttt2 select a,a from generate_series(1,1000000) a;\nINSERT 0 1000000\npostgres=# alter table ttt2 add constraint fk_t foreign key (b) references ttt1(a);\nALTER TABLE\n<\/pre>\n
Yes, that as well:<\/p>\n
\n2020-03-13 03:01:07.127 CET - 65 - 10609 - [local] - postgres@postgres LOG:  duration: 1127.768 ms  statement: alter table ttt2 add constraint fk_t foreign key (b) references ttt1(a);\n2020-03-13 03:01:15.375 CET - 66 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.6\", size 67374\n<\/pre>\n
So quite a few operations that generate temporary files in background. Separating this on a separate mount point actually can make a lot of sense. From a performance perspective (if I\/O spread on the storage layer as well) but also from a security perspective as huge operations that require temporary files will not affect “normal” operations on the instance. <\/p>\n
There is another case generating temporary files which is not maybe not clear to everybody. Consider this:<\/p>\n
\npostgres=# create materialized view mv1 as select ttt1.a, ttt2.b from ttt1, ttt2 where ttt1.a = ttt2.b;\nSELECT 1000000\n<\/pre>\n
This will create many temporary files in the background as well:<\/p>\n
\n...\n2020-03-13 03:11:03.721 CET - 4146 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.1289\", size 22704\n2020-03-13 03:11:03.721 CET - 4147 - 10609 - [local] - postgres@postgres STATEMENT:  create materialized view mv1 as select ttt1.a, ttt2.b from ttt1, ttt2 where ttt1.a = ttt2.b;\n2020-03-13 03:11:03.722 CET - 4148 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.143\", size 23136\n2020-03-13 03:11:03.722 CET - 4149 - 10609 - [local] - postgres@postgres STATEMENT:  create materialized view mv1 as select ttt1.a, ttt2.b from ttt1, ttt2 where ttt1.a = ttt2.b;\n2020-03-13 03:11:03.723 CET - 4150 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.1166\", size 23136\n2020-03-13 03:11:03.723 CET - 4151 - 10609 - [local] - postgres@postgres STATEMENT:  create materialized view mv1 as select ttt1.a, ttt2.b from ttt1, ttt2 where ttt1.a = ttt2.b;\n2020-03-13 03:11:03.723 CET - 4152 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.792\", size 23640\n...\n<\/pre>\n
And even refreshes consume temporary files:<\/p>\n
\npostgres=# create unique index mv_i1 on mv1(a);\nCREATE INDEX\npostgres=# refresh materialized view concurrently mv1;\nREFRESH MATERIALIZED VIEW\npostgres=# \n<\/pre>\n
From the log file:<\/p>\n
\n...\n2020-03-13 03:14:05.866 CET - 20543 - 10609 - [local] - postgres@postgres STATEMENT:  refresh materialized view concurrently mv1;\n2020-03-13 03:14:05.866 CET - 20544 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.8193\", size 26768\n2020-03-13 03:14:05.866 CET - 20545 - 10609 - [local] - postgres@postgres CONTEXT:  SQL statement \"CREATE TEMP TABLE pg_temp_3.pg_temp_16513_2 AS SELECT mv.ctid AS tid, newdata FROM public.mv1 mv FULL JOIN pg_temp_3.pg_temp_16513 newdata ON (newdata.a OPERATOR(pg_catalog.=) mv.a AND newdata OPERATOR(pg_catalog.*=) mv) WHERE newdata IS NULL OR mv IS NULL ORDER BY tid\"\n2020-03-13 03:14:05.866 CET - 20546 - 10609 - [local] - postgres@postgres STATEMENT:  refresh materialized view concurrently mv1;\n2020-03-13 03:14:05.866 CET - 20547 - 10609 - [local] - postgres@postgres LOG:  temporary file: path \"pg_tblspc\/16442\/PG_13_202003051\/pgsql_tmp\/pgsql_tmp10609.6147\", size 28487\n2020-03-13 03:14:05.866 CET - 20548 - 10609 - [local] - postgres@postgres CONTEXT:  SQL statement \"CREATE TEMP TABLE pg_temp_3.pg_temp_16513_2 AS SELECT mv.ctid AS tid, newdata FROM public.mv1 mv FULL JOIN pg_temp_3.pg_temp_16513 newdata ON (newdata.a OPERATOR(pg_catalog.=) mv.a AND newdata OPERATOR(pg_catalog.*=) mv) WHERE newdata IS NULL OR mv IS NULL ORDER BY tid\"\n...\n<\/pre>\n
There are more operations that require temporary files in the background but for the scope of this post we stop here. One last thing: The amount of temporary files generated can also be limited by temp_file_limit<\/a>:<\/p>\n
\npostgres=# set temp_file_limit='1MB';\nSET\npostgres=# refresh materialized view concurrently mv1;\nERROR:  temporary file size exceeds temp_file_limit (1024kB)\npostgres=# \n<\/pre>\n","protected":false},"excerpt":{"rendered":"
There often is a discussion if tablespaces should be used in PostgreSQL. People used to work with Oracle are often a bit surprised by this discussion as there is no way around using tablespaces in that technology. PostgreSQL does not force you to use tablespaces at all and I am pretty sure that there are […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-13629","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nAbout temp_tablespaces in PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"About temp_tablespaces in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"There often is a discussion if tablespaces should be used in PostgreSQL. People used to work with Oracle are often a bit surprised by this discussion as there is no way around using tablespaces in that technology. PostgreSQL does not force you to use tablespaces at all and I am pretty sure that there are […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-14T08:29:53+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"About temp_tablespaces in PostgreSQL\",\"datePublished\":\"2020-03-14T08:29:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/\"},\"wordCount\":920,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/\",\"name\":\"About temp_tablespaces in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-03-14T08:29:53+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"About temp_tablespaces in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"About temp_tablespaces in PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"About temp_tablespaces in PostgreSQL","og_description":"There often is a discussion if tablespaces should be used in PostgreSQL. People used to work with Oracle are often a bit surprised by this discussion as there is no way around using tablespaces in that technology. PostgreSQL does not force you to use tablespaces at all and I am pretty sure that there are […]","og_url":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2020-03-14T08:29:53+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"About temp_tablespaces in PostgreSQL","datePublished":"2020-03-14T08:29:53+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/"},"wordCount":920,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/","name":"About temp_tablespaces in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-03-14T08:29:53+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/about-temp_tablespaces-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"About temp_tablespaces in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13629","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=13629"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13629\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13629"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13629"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13629"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13629"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}