PostgreSQL有一种介于正常表和临时表之间的类型表，称之为unlogged表，在该表新建的索引也属于unlogged，该表在写入数据时候并不将数据写入到持久的write-ahead log文件中，在数据库异常关机或者异常崩溃后该表的数据会被truncate掉，但是在写入性能上会比正常表快几倍。
分别测试正常表和unlogged表数据插入速度区别
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=# iming Timing is on.
uber_geocoder=# create table twn_addr_compact_loggod as select * from twn_addr_compact ; SELECT 258902 Time: 977250.581 ms
可以看到在新建正常表并插770M的数据的情况下耗时近16分钟
新建一张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表数据丢失。
如何查看当前数据库中所有的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)
如果需要批量将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;