PostgreSQL 临时表空间，常在闺中无人识！-51CTO.COM

介绍

顾名思义，临时表空间是用于临时对象的。那么问题来了，究竟什么才算是临时对象呢？一个显而易见的对象是临时表。在没有任何临时表空间的情况下，创建临时表时，会在当前数据库的默认表空间中创建临时文件。在一个标准的 PostgreSQL 部署环境中，它看起来像这样：

postgres=# l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 872 MB  | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7393 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7393 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |

所有数据库都有一个名为 “pg_default” 的默认表空间，这是一种伪表空间，因为它实际上并不存在。从系统表查询该表空间的位置时，会显示一个空位置：

SELECT spcname AS "Name"
     , pg_catalog.pg_get_userbyid(spcowner) AS "Owner"
     , pg_catalog.pg_tablespace_location(oid) AS "Location"
  FROM pg_catalog.pg_tablespace
 WHERE pg_catalog.pg_tablespace.spcname = 'pg_default'
 ORDER BY 1;

    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
(1 row)

如果我们创建临时对象，那么文件会创建到哪里？

CREATE TEMPORARY TABLE tmp1 ( a int, b text, c date );

SELECT pg_relation_filepath('tmp1');
 pg_relation_filepath
----------------------
 base/12732/t3_16436
(1 row)

这是一个 “postgres” 数据库的标准目录：

$ cd $PGDATA

$ oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  12732       postgres  pg_default
  12731      template0  pg_default
      1      template1  pg_default

$ ls -l base/12732/t3_16436
-rw-------. 1 postgres postgres 0 Mar 12 18:17 base/12732/t3_16436

因此，默认情况下，临时表所需的文件，会与组成特定数据库的所有其他文件位于同一位置。当然，如果我们填充临时表，文件会增长：

INSERT INTO tmp1 (a,b,c) SELECT i, i::text, now() FROM generate_series(1,100) i;

$ ls -la $PGDATA/base/12732/t3_16436
-rw-------. 1 postgres postgres 8192 Mar 12 18:41 /data/pgsql/base/12732/t3_16436

INSERT INTO tmp1 (a,b,c) SELECT i, i::text, now() FROM generate_series(1,1000) i;

$ ls -la $PGDATA/base/12732/t3_16436
-rw-------. 1 postgres postgres 49152 Mar 12 18:42 /data/pgsql/base/12732/t3_16436

使用默认表空间处理临时对象，会有哪些影响？

1. 临时表的 I/O 会和该 PostgreSQL 实例中所有其他对象的 I/O 竞争。

2. 临时表可能会填满您的文件系统，直到空间满后数据库服务器停止工作。这是创建一个或多个专用的临时表空间的首要原因：通过这样做，只要临时表空间位于其自己的文件系统上，就可以避免临时表疯狂地影响整个实例。

3. 减慢了临时表访问和排序操作。尽管可以增加temp_buffers和work_mem参数值，但这些值是会话级别的，过高的值可能会导致内存使用过多，和潜在的内存竞争问题。因此，我们可以选择更快的文件系统或者存储设备，创建单独的临时表空间。

临时表空间

创建临时表空间与创建普通表空间没有什么不同，因为它们实际上完全相同：

$ mkdir /var/tmp/tbstmp

CREATE TABLESPACE tbstmp LOCATION '/var/tmp/tbstmp';

postgres=# db+
                                      List of tablespaces
    Name    |  Owner   |    Location     | Access privileges | Options |  Size   | Description 
------------+----------+-----------------+-------------------+---------+---------+-------------
 pg_default | postgres |                 |                   |         | 886 MB  | 
 pg_global  | postgres |                 |                   |         | 575 kB  | 
 tbstmp     | postgres | /var/tmp/tbstmp |                   |         | 0 bytes | 
(3 rows)

一旦我们有了新的表空间，我们就可以告诉 PostgreSQL 将其用作临时对象的默认表空间：

ALTER SYSTEM SET temp_tablespaces = 'tbstmp';

SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

SHOW temp_tablespaces;
 temp_tablespaces
------------------
 tbstmp
(1 row)

使用临时表

创建另一个临时表，表文件将会创建在新的位置：

CREATE TEMPORARY TABLE tmp2 ( a int, b text, c date );

SELECT pg_relation_filepath('tmp2');
              pg_relation_filepath
------------------------------------------------
 pg_tblspc/16442/PG_13_202003051/12732/t3_16443
(1 row)

$ ls -la $PGDATA/pg_tblspc/
total 4
drwx------.  2 postgres postgres   19 Mar 12 18:50 .
drwx------. 20 postgres postgres 4096 Mar 12 18:54 ..
lrwxrwxrwx.  1 postgres postgres   15 Mar 12 18:50 16442 -> /var/tmp/tbstmp

$ ls -la $PGDATA/pg_tblspc/16442/
total 0
drwx------. 3 postgres postgres  29 Mar 12 18:50 .
drwxrwxrwt. 7 root     root     163 Mar 12 18:49 ..
drwx------. 3 postgres postgres  19 Mar 12 18:53 PG_13_202003051

$ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/
total 0
drwx------. 3 postgres postgres 19 Mar 12 18:53 .
drwx------. 3 postgres postgres 29 Mar 12 18:50 ..
drwx------. 2 postgres postgres 54 Mar 12 18:53 12732

$ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/12732/
total 8
drwx------. 2 postgres postgres   54 Mar 12 18:53 .
drwx------. 3 postgres postgres   19 Mar 12 18:53 ..
-rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16443
-rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16446
-rw-------. 1 postgres postgres 8192 Mar 12 18:53 t3_16448

如果您想知道为什么会有三个文件，答案在这里：

SELECT relname FROM pg_class WHERE oid IN (16443,16446,16448);

       relname
----------------------
 pg_toast_16443
 pg_toast_16443_index
 tmp2
(3 rows)

由于临时表中有一个 “text” 列，因而还会创建 toast 对象。使用不需要 toast 对象的数据类型创建一个临时表，将只生成一个表文件：

CREATE TEMPORARY TABLE tmp3 ( a int, b date );

SELECT pg_relation_filepath('tmp3');
              pg_relation_filepath
------------------------------------------------
 pg_tblspc/16442/PG_13_202003051/12732/t3_16449
(1 row)

$ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/12732/
total 8
drwx------. 2 postgres postgres   70 Mar 12 19:07 .
drwx------. 3 postgres postgres   19 Mar 12 18:53 ..
-rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16443
-rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16446
-rw-------. 1 postgres postgres 8192 Mar 12 18:53 t3_16448
-rw-------. 1 postgres postgres    0 Mar 12 19:07 t3_16449

记录临时文件

至此，现在我们知道了，所有临时表都将转到新的临时表空间。从现在开始，还有哪些数据会去那里？有一个参数 log_temp_files 可用于将临时文件的使用情况报告到 PostgreSQL 日志文件中，如果您想知道临时表空间中有什么，这会非常方便。该参数默认设置为 “-1”，表示禁止记录任何内容，设为 “0” 表示记录所有内容，所有其他大于 “1” 的值表示记录超出该大小的临时文件。如前所述，将其设置为 “0” 将会在后台记录创建的所有临时文件，因此让我们这样做：

ALTER SYSTEM SET log_temp_files = 0;

SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

创建另一个临时表，然后检查日志文件，可以确认日志记录生效了，我们可以获得想要的信息：

CREATE TEMPORARY TABLE tmp4 AS SELECT * FROM generate_series(1,1000000);

日志文件中的记录会如下所示，它表明临时文件已写入到我们上面创建的临时表空间：

2020-03-13 02:33:35.658 [10535] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10535.0", size 14000000
2020-03-13 02:33:35.658 [10535] STATEMENT:  CREATE TEMPORARY TABLE tmp4 AS SELECT * FROM generate_series(1,1000000);

但是，如果您使用 PostgreSQL 的默认配置创建此临时表：

CREATE TEMPORARY TABLE tmp4 AS SELECT * FROM generate_series(1,100000);

... 您不会在日志文件中看到任何记录。为什么？因为有一个参数 temp_buffers，而临时文件只有在超过该参数值时，才会出现在日志文件中。在默认配置中，该值为 “8MB”，对于较小的临时表不会记录日志。将参数调小，才会记录较小的临时表文件：

SET temp_buffers = '1024kB';

CREATE TEMPORARY TABLE tmp5 AS SELECT * FROM generate_series(1,100000);

排序

至此，现在我们知道了，如何将临时文件的创建记录到 PostgreSQL 日志文件中。还有哪些其他操作会导致创建临时文件？排序呢？

SET work_mem = '64kB';

SELECT * FROM generate_series(1,1000000) ORDER BY random();

是的，当然会：

2020-03-13 02:47:14.297 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.2", size 26083328
2020-03-13 02:47:14.297 [10609] STATEMENT:  SELECT * FROM generate_series(1,1000000) ORDER BY random();
2020-03-13 02:47:14.298 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.1", size 14000000
2020-03-13 02:47:14.298 [10609] STATEMENT:  SELECT * FROM generate_series(1,1000000) ORDER BY random();
2020-03-13 02:47:14.298 [10609] LOG:  duration: 2994.386 ms  statement: SELECT * FROM generate_series(1,1000000) ORDER BY random();

创建索引

CREATE TABLE tt1 AS SELECT * FROM generate_series(1,1000000);

CREATE INDEX ii1 ON tt1(generate_series);

是的，这也会创建临时文件：

2020-03-13 02:54:00.933 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.0.sharedfileset/0.0", size 12394496
2020-03-13 02:54:00.933 [10609] STATEMENT:  CREATE INDEX ii1 ON tt1(generate_series);
2020-03-13 02:54:00.934 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.0.sharedfileset/1.0", size 7659520
2020-03-13 02:54:00.934 [10609] STATEMENT:  CREATE INDEX ii1 ON tt1(generate_series);
2020-03-13 02:54:00.948 [10609] LOG:  duration: 1149.625 ms  statement: CREATE INDEX ii1 ON tt1(generate_series);

添加外键

CREATE TABLE ttt1 AS SELECT * FROM generate_series(1,1000000) a;

CREATE UNIQUE INDEX iii1 ON ttt1(a);

INSERT INTO ttt2 SELECT a,a FROM generate_series(1,1000000) a;

ALTER TABLE ttt2 ADD CONSTRAINT fk_t FOREIGN KEY (b) REFERENCES ttt1(a);

是的，那也会：

2020-03-13 03:01:07.127 [10609] LOG:  duration: 1127.768 ms  statement: ALTER TABLE ttt2 ADD CONSTRAINT fk_t FOREIGN KEY (b) REFERENCES ttt1(a);
2020-03-13 03:01:15.375 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.6", size 67374

因此，相当多的操作会在后台生成临时文件。将其分离到单独的挂载目录实际上很有意义。从性能角度看（如果 I/O 也分布在存储层上），也可从安全角度来看，这样需要临时文件的大型操作不会影响到实例上的“正常”操作。

创建物化视图

还有另一种情况会生成临时文件，但可能不是每个人都清楚。考虑下这样的操作：

CREATE MATERIALIZED VIEW mv1 AS SELECT ttt1.a, ttt2.b FROM ttt1, ttt2 WHERE ttt1.a = ttt2.b;

这也会在后台创建许多临时文件：

...
2020-03-13 03:11:03.721 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.1289", size 22704
2020-03-13 03:11:03.721 [10609] STATEMENT:  CREATE MATERIALIZED VIEW mv1 AS SELECT ttt1.a, ttt2.b FROM ttt1, ttt2 WHERE ttt1.a = ttt2.b;
2020-03-13 03:11:03.722 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.143", size 23136
2020-03-13 03:11:03.722 [10609] STATEMENT:  CREATE MATERIALIZED VIEW mv1 AS SELECT ttt1.a, ttt2.b FROM ttt1, ttt2 WHERE ttt1.a = ttt2.b;
2020-03-13 03:11:03.723 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.1166", size 23136
2020-03-13 03:11:03.723 [10609] STATEMENT:  CREATE MATERIALIZED VIEW mv1 AS SELECT ttt1.a, ttt2.b FROM ttt1, ttt2 WHERE ttt1.a = ttt2.b;
2020-03-13 03:11:03.723 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.792", size 23640
...

刷新物化视图

甚至刷新时也会产生临时文件：

CREATE UNIQUE INDEX mv_i1 ON mv1(a);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;

看下日志文件：

...
2020-03-13 03:14:05.866 [10609] STATEMENT:  REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
2020-03-13 03:14:05.866 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.8193", size 26768
2020-03-13 03:14:05.866 [10609] 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"
2020-03-13 03:14:05.866 [10609] STATEMENT:  REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
2020-03-13 03:14:05.866 [10609] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.6147", size 28487
2020-03-13 03:14:05.866 [10609] 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"
...

还有更多操作需要在后台使用临时文件，但对于本文的范围，我们到此为止。最后补充下：生成的临时文件数量还可能受到 temp_file_limit 的限制：

SET temp_file_limit = '1MB';

REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
ERROR:  temporary file size exceeds temp_file_limit (1024kB)