1. 临时表

相较于普通表，临时表具有三个独特的特性: 

  • 临时表存储在特殊的模式（ schema）中, 以便它们只对后台创建（creating backend）可见 
  • 临时表有本地缓冲区管理器管理，而普通表由共享缓冲区管理器管理 
  • 临时表不记录预写式日志

2. 无日志表

unlogged表是一种介于正常表和临时表之间的类型表，在unlogged表新建的索引也属于unlogged，unlogged表在写入数据时候并不将数据写入到持久的write-ahead log文件中，在数据库异常关机或者异常崩溃后该表的数据会被truncate掉，但是在写入性能上会比正常表快几倍。

unlogged table主要有几个特点：

  • unlogged table不记录wal日志，写入速度快，备库无数据，只有结构（unlogged table > 普通表(异步事务) > 普通表(同步事务)）；
  • 当数据库crash后，数据库重启时自动清空unlogged table的数据；
  • 正常关闭数据库，再启动时，unlogged table有数据；
  • unlogged table通常用于存储中间结果，频繁变更的会话数据；
  • 使用pg_basebackup备份时，不会备份TEMPORARY table和 UNLOGGED table；

3. 测试普通表和unlogged表数据插入速度

3.1 创建普通表

在新建正常表并插770M的数据的情况下耗时近16分钟

uber_geocoder=# \dt+
                                      List of relations
          Schema          |          Name          | Type  |  Owner   |  Size  | Description
--------------------------+------------------------+-------+----------+--------+-------------
 uber_geocoder_tw_15q3_v2 | compiling_info         | table | postgres | 16 kB  |
 uber_geocoder_tw_15q3_v2 | data_process_reports   | table | postgres | 16 kB  |
 uber_geocoder_tw_15q3_v2 | info                   | table | postgres | 16 kB  |
 uber_geocoder_tw_15q3_v2 | twn_addr_compact       | table | postgres | 774 MB |
(4 rows)
 
uber_geocoder=# \timing
Timing is on.
uber_geocoder=# create table twn_addr_compact_loggod as select * from twn_addr_compact ;
SELECT 258902
Time: 977250.581 ms

3.2 创建unlogged表

新建一张unlogged表并插入770M数据进行测试

uber_geocoder=# create unlogged table twn_addr_compact_unloggod as select * from twn_addr_compact ;
SELECT 258902
Time: 300683.321 ms

在同等条件下，unlogged表的插入速度为5分钟，性能提高了三倍。

将postgres进程kill掉，然后再启动数据库，就会发现我们刚才创建的unlogged表数据丢失。

3.3 查看当前数据库中所有的unlogged表

uber_geocoder=# select n.nspname as schema_name,c.relname as table_name from pg_catalog.pg_namespace n, pg_catalog.pg_class c where c.relnamespace=n.oid and n.nspname != 'pg_toast' and c.relkind='r' and c.relpersistence = 'u';
       schema_name        |        table_name
--------------------------+---------------------------
 edbstore                 | emp
 uber_geocoder_tw_15q3_v2 | twn_addr_compact_unloggod
(2 rows)

3.4 批量将unlogged表修改为普通表

uber_geocoder=# select 'ALTER TABLE'||' '||concat(n.nspname,'.' ,c.relname)||' '||'SET LOGGED ;' AS convert_logged_sql from pg_catalog.pg_namespace n, pg_catalog.pg_class c where c.relnamespace=n.oid and n.nspname != 'pg_toast' and c.relkind='r' and c.relpersistence = 'u';
                             convert_logged_sql
-----------------------------------------------------------------------------
 ALTER TABLE edbstore.emp SET LOGGED ;
 ALTER TABLE uber_geocoder_tw_15q3_v2.twn_addr_compact_unloggod SET LOGGED ;
(2 rows)

select n.nspname ||'.'|| c.relname as table_name, pg_table_size(n.nspname ||'.'|| c.relname) as table_size from pg_catalog.pg_namespace n, pg_catalog.pg_class c where c.relnamespace=n.oid and n.nspname != 'pg_toast' and c.relkind='r' and c.relpersistence = 'u' order by table_size desc;