Pg 临时表空间 怎么用

郑全

介绍

    顾名思义，临时表空间是用于临时对象的。

    那么问题来了，究竟什么才算是临时对象呢？一个显而易见的对象是临时表。

     在没有任何临时表空间的情况下，创建临时表时，会在当前数据库的默认表空间中创建临时文件。

     在一个标准的 PostgreSQL 部署环境中，它看起来像这样：

     

1. 
   
2.    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
   
3. -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
   
4. postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 872 MB  | pg_default | default administrative connection database
   
5. template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7393 kB | pg_default | unmodifiable empty database
   
6.            |          |          |             |             | postgres=CTc/postgres |         |            |
   
7. template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7393 kB | pg_default | default template for new databases
   
8.            |          |          |             |             | postgres=CTc/postgres |         |            |

复制代码

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

        

1. SELECT spcname AS "Name"
   
2.      , pg_catalog.pg_get_userbyid(spcowner) AS "Owner"
   
3.      , pg_catalog.pg_tablespace_location(oid) AS "Location"
   
4.   FROM pg_catalog.pg_tablespace
   
5. WHERE pg_catalog.pg_tablespace.spcname = 'pg_default'
   
6. ORDER BY 1;
   
7. 
   
8.     Name    |  Owner   | Location
   
9. ------------+----------+----------
   
10. pg_default | postgres |
    
11. (1 row)

复制代码

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

1. CREATE TEMPORARY TABLE tmp1 ( a int, b text, c date );
   
2. 
   
3. SELECT pg_relation_filepath('tmp1');
   
4. pg_relation_filepath
   
5. ----------------------
   
6. base/12732/t3_16436
   
7. (1 row)

复制代码

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

1. $ cd $PGDATA
   
2. 
   
3. $ oid2name
   
4. All databases:
   
5.     Oid  Database Name  Tablespace
   
6. ----------------------------------
   
7.   12732       postgres  pg_default
   
8.   12731      template0  pg_default
   
9.       1      template1  pg_default
   
10. 
    
11. $ ls -l base/12732/t3_16436
    
12. -rw-------. 1 postgres postgres 0 Mar 12 18:17 base/12732/t3_16436

复制代码

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

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

复制代码

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

复制代码

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

复制代码

1. $ ls -la $PGDATA/base/12732/t3_16436
   
2. -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参数值，但这些值是会话级别的，过高的值可能会导致内存使用过多，和潜在的内存竞争问题。因此，我们可以选择更快的文件系统或者存储设备，创建单独的临时表空间。

临时表空间

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

1. $ mkdir /var/tmp/tbstmp

复制代码

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

复制代码

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

复制代码

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

1. ALTER SYSTEM SET temp_tablespaces = 'tbstmp';
   
2. 
   
3. SELECT pg_reload_conf();
   
4. pg_reload_conf
   
5. ----------------
   
6. t
   
7. (1 row)
   
8. 
   
9. SHOW temp_tablespaces;
   
10. temp_tablespaces
    
11. ------------------
    
12. tbstmp
    
13. (1 row)

复制代码

使用临时表

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

1. CREATE TEMPORARY TABLE tmp2 ( a int, b text, c date );
   
2. 
   
3. SELECT pg_relation_filepath('tmp2');
   
4.               pg_relation_filepath
   
5. ------------------------------------------------
   
6. pg_tblspc/16442/PG_13_202003051/12732/t3_16443
   
7. (1 row)

复制代码

1. $ ls -la $PGDATA/pg_tblspc/
   
2. total 4
   
3. drwx------.  2 postgres postgres   19 Mar 12 18:50 .
   
4. drwx------. 20 postgres postgres 4096 Mar 12 18:54 ..
   
5. lrwxrwxrwx.  1 postgres postgres   15 Mar 12 18:50 16442 -> /var/tmp/tbstmp
   
6. 
   
7. $ ls -la $PGDATA/pg_tblspc/16442/
   
8. total 0
   
9. drwx------. 3 postgres postgres  29 Mar 12 18:50 .
   
10. drwxrwxrwt. 7 root     root     163 Mar 12 18:49 ..
    
11. drwx------. 3 postgres postgres  19 Mar 12 18:53 PG_13_202003051
    
12. 
    
13. $ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/
    
14. total 0
    
15. drwx------. 3 postgres postgres 19 Mar 12 18:53 .
    
16. drwx------. 3 postgres postgres 29 Mar 12 18:50 ..
    
17. drwx------. 2 postgres postgres 54 Mar 12 18:53 12732
    
18. 
    
19. $ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/12732/
    
20. total 8
    
21. drwx------. 2 postgres postgres   54 Mar 12 18:53 .
    
22. drwx------. 3 postgres postgres   19 Mar 12 18:53 ..
    
23. -rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16443
    
24. -rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16446
    
25. -rw-------. 1 postgres postgres 8192 Mar 12 18:53 t3_16448

复制代码

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

1. SELECT relname FROM pg_class WHERE oid IN (16443,16446,16448);
   
2. 
   
3.        relname
   
4. ----------------------
   
5. pg_toast_16443
   
6. pg_toast_16443_index
   
7. tmp2
   
8. (3 rows)

复制代码

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

1. CREATE TEMPORARY TABLE tmp3 ( a int, b date );
   
2. 
   
3. SELECT pg_relation_filepath('tmp3');
   
4.               pg_relation_filepath
   
5. ------------------------------------------------
   
6. pg_tblspc/16442/PG_13_202003051/12732/t3_16449
   
7. (1 row)

复制代码

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

复制代码

记录临时文件

至此，现在我们知道了，所有临时表都将转到新的临时表空间。从现在开始，还有哪些数据会去那里？

有一个参数 log_temp_files 可用于将临时文件的使用情况报告到 PostgreSQL 日志文件中，如果您想知道临时表空间中有什么，

这会非常方便。该参数默认设置为 “-1”，表示禁止记录任何内容，设为 “0” 表示记录所有内容，所有其他大于 “1” 的值表示记录超出该大小的临时文件。

如前所述，将其设置为 “0” 将会在后台记录创建的所有临时文件，因此让我们这样做：

1. ALTER SYSTEM SET log_temp_files = 0;
   
2. 
   
3. SELECT pg_reload_conf();
   
4. pg_reload_conf
   
5. ----------------
   
6. t
   
7. (1 row)

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

... 您不会在日志文件中看到任何记录。为什么？因为有一个参数 temp_buffers，而临时文件只有在超过该参数值时，才会出现在日志文件中。

在默认配置中，该值为 “8MB”，对于较小的临时表不会记录日志。

将参数调小，才会记录较小的临时表文件：

1. SET temp_buffers = '1024kB';
   
2. 
   
3. CREATE TEMPORARY TABLE tmp5 AS SELECT * FROM generate_series(1,100000);

复制代码

排序

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

1. SET work_mem = '64kB';
   
2. 
   
3. SELECT * FROM generate_series(1,1000000) ORDER BY random();

复制代码

是的，当然会：

1. 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
   
2. 2020-03-13 02:47:14.297 [10609] STATEMENT:  SELECT * FROM generate_series(1,1000000) ORDER BY random();
   
3. 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
   
4. 2020-03-13 02:47:14.298 [10609] STATEMENT:  SELECT * FROM generate_series(1,1000000) ORDER BY random();
   
5. 2020-03-13 02:47:14.298 [10609] LOG:  duration: 2994.386 ms  statement: SELECT * FROM generate_series(1,1000000) ORDER BY random();

复制代码

创建索引

1. CREATE TABLE tt1 AS SELECT * FROM generate_series(1,1000000);
   
2. 
   
3. CREATE INDEX ii1 ON tt1(generate_series);

复制代码

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

1. 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
   
2. 2020-03-13 02:54:00.933 [10609] STATEMENT:  CREATE INDEX ii1 ON tt1(generate_series);
   
3. 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
   
4. 2020-03-13 02:54:00.934 [10609] STATEMENT:  CREATE INDEX ii1 ON tt1(generate_series);
   
5. 2020-03-13 02:54:00.948 [10609] LOG:  duration: 1149.625 ms  statement: CREATE INDEX ii1 ON tt1(generate_series);

复制代码

添加外键

1. CREATE TABLE ttt1 AS SELECT * FROM generate_series(1,1000000) a;
   
2. 
   
3. CREATE UNIQUE INDEX iii1 ON ttt1(a);
   
4. 
   
5. INSERT INTO ttt2 SELECT a,a FROM generate_series(1,1000000) a;
   
6. 
   
7. ALTER TABLE ttt2 ADD CONSTRAINT fk_t FOREIGN KEY (b) REFERENCES ttt1(a);

复制代码

是的，那也会：

1. 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);
   
2. 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 也分布在存储层上），也可从安全角度来看，这样需要临时文件的大型操作不会影响到实例上的“正常”操作。

创建物化视图

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

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

复制代码

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

1. ...
   
2. 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
   
3. 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;
   
4. 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
   
5. 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;
   
6. 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
   
7. 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;
   
8. 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
   
9. ...

复制代码

刷新物化视图

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

1. CREATE UNIQUE INDEX mv_i1 ON mv1(a);
   
2. 
   
3. REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;

复制代码

看下日志文件：

1. ...
   
2. 2020-03-13 03:14:05.866 [10609] STATEMENT:  REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
   
3. 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
   
4. 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"
   
5. 2020-03-13 03:14:05.866 [10609] STATEMENT:  REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
   
6. 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
   
7. 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"
   
8. ...

复制代码

还有更多操作需要在后台使用临时文件，但对于本文的范围，我们到此为止。

最后补充下：生成的临时文件数量还可能受到 temp_file_limit 的限制：

1. SET temp_file_limit = '1MB';
   
2. 
   
3. REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
   
4. ERROR:  temporary file size exceeds temp_file_limit (1024kB)

复制代码