PostgreSQL中的伪内存表

标签：127.0 PostgreSQL postgres unlogged 0.1 0.00 idle 内存

To WAL or not to WAL?

和其他许多数据库一样，PostgreSQL允许某个表不记录日志，简而言之就是 "把我排除在WAL日志之外！"。这样的表不是崩溃安全的，同样也不能复制，因为PostgreSQL的复制依赖于WAL日志。

但是，当你在复制的情况下处理这样的表时，会发生什么？这篇文章试图为你提供一些阐述，说明什么是可能的，会发生什么。

Creating and populating a database to test

首先，让我们创建一个干净的数据库，只是为了让测试环境与其他数据库分开。

testdb=# CREATE DATABASE rep_test WITH OWNER luca;
CREATE DATABASE

现在让我们创建并填充三个表（一个临时表，一个无日志表和一个普通的表）。

rep_test=> CREATE TABLE t_norm( pk int GENERATED ALWAYS AS IDENTITY,
                  t text,
                  primary key( pk ) );
 
rep_test=> CREATE UNLOGGED TABLE 
           t_unlogged( like t_norm including all );
 
rep_test=> CREATE TEMPORARY TABLE 
           t_temp( like t_norm including all );
           
 
rep_test=> INSERT INTO t_norm( t )
               SELECT 'Row #' || v
               FROM generate_series( 1, 1000000 ) v;
INSERT 0 1000000
Time: 4712.185 ms (00:04.712)
 
rep_test=> INSERT INTO t_temp( t )
            SELECT 'Row #' || v
            FROM generate_series( 1, 1000000 ) v;
INSERT 0 1000000
Time: 1789.473 ms (00:01.789)
 
rep_test=> INSERT INTO t_unlogged( t )
               SELECT 'Unlogged #' || v
               FROM generate_series( 1, 1000000 ) v;
INSERT 0 1000000
Time: 1746.729 ms (00:01.747)

现在的情况如下：

Table	Status	Insertion time
`t_norm`	Ordinary table	4.7 secs
`t_temp`	Temporary table	1.8 secs
`t_unlogged`	Unlogged table	1.7 secs

正如你所看到的，临时表和无日志表的时间基本相同，这是因为两者都没有插入WAL记录，因此不会涉及到崩溃恢复的机制。这也意味着，针对临时表和无日志的表写事务要快得多。当然，以上并不是对INSERT时间的绝对测量，但在此报告只是为了让你了解差异。由于有一个临时表，你需要持续保持与主节点的会话，否则你会丢失该表中的所有数据。

Doing the physical replication

启动物理复制。这不是一个关于如何进行物理复制的教程，我会呈现我在单独的机器上执行的命令，以便使复制集群继续运行

% pg_basebackup -X stream --create-slot --slot 'carmensita_physical_replication_slot' -R -r 100M -D /postgres/12/replica -l "Test unlogged tables" -P -d "dbname=backup user=backup host=miguel" -T /wal=/postgres/12

原来的集群位于名为miguel的机器上，而复制的插槽位于名为carmensita的机器上。这是我经常用来做实验工作的两台机器。还请注意，我使用了一个backup的数据库和角色来流式传递信息，可以想象，您需要在pg_hba.conf中启用复制连接

% tail $PGDATA/pg_hba.conf
 
host    replication     backup  carmensita  trust

复制完成后，便可以启动备用节点

% /usr/pgsql-12/bin/pg_ctl -D /postgres/12/replica start 
in attesa che il server si avvii....
 LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.2.1 20201125 (Red Hat 10.2.1-9), 64-bit
 LOG:  listening on IPv4 address "0.0.0.0", port 5432
 LOG:  listening on IPv6 address "::", port 5432
 LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
 LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
 LOG:  redirecting log output to logging collector process
 HINT:  Future log output will appear in directory "log".

Check the tables on the replication side

现在该检查复制主机上的复制数据库了

% psql -h carmensita -U luca rep_test
 
rep_test=> \d
                Lista delle relazioni
 Schema |       Nome        |   Tipo   | Proprietario 
--------|-------------------|----------|--------------
 public | t_norm            | tabella  | luca
 public | t_norm_pk_seq     | sequenza | luca
 public | t_unlogged        | tabella  | luca
 public | t_unlogged_pk_seq | sequenza | luca
(4 righe)
 
rep_test=> select count(*) from t_norm;
  count  
---------
 1000000
(1 riga)
 
rep_test=> select count(*) from t_unlogged;
ERROR:  cannot access temporary or unlogged relations during recovery

正如你所看到的，临时表不见了，即使在另一个主连接上仍然可用。这并不奇怪，临时表只能在每个连接的基础上使用，因此不会被复制。更有趣的是，无日志表t_unlogged和相关的序列已经被复制了，但是它们仅仅只是作为一个占位符存在，事实上不可能对无日志表进行操作。因此，无日志表只复制其结构，而不复制其数据！

Switching from unlogged to logged

在主节点上，现在是时候把t_unlogged的unlogged状态改为logged了，这可以通过ALTER TABLE命令完成。我们也来检查一下pg_class中的relpersistence标识的状态，看看它是如何从u（unlogged）变为p（logged）的。

rep_test=> \d
                Lista delle relazioni
 Schema |       Nome        |   Tipo   | Proprietario 
--------|-------------------|----------|--------------
 public | t_norm            | tabella  | luca
 public | t_norm_pk_seq     | sequenza | luca
 public | t_unlogged        | tabella  | luca
 public | t_unlogged_pk_seq | sequenza | luca
(4 righe)
 
rep_test=> select count(*) from t_norm;
  count  
---------
 1000000
(1 riga)
 
rep_test=> select count(*) from t_unlogged;
ERROR:  cannot access temporary or unlogged relations during recovery

值得注意的是，将unlogged的状态改为logged的状态需要11秒时间，这比普通表的插入时间还要长。这里的意思是，PostgreSQL必须把表中的所有记录插入到WAL日志中，因为刚刚发生了对每一行的insert操作。

rep_test=# alter table t_unlogged set logged;
ALTER TABLE
Time: 11485.505 ms (00:11.486)

之后，在复制的备机上，该表也变成了普通表。

rep_test=> select count(*) from t_unlogged;
  count  
---------
 1000000

Switching from logged to unlogged

如果t_unlogged表再次变成unlogged，现在会发生什么：

rep_test=# alter table t_unlogged set unlogged;
ALTER TABLE
Time: 5236.165 ms (00:05.236)
rep_test=# truncate t_unlogged;
TRUNCATE TABLE
Time: 21.498 ms

这里需要注意的是，同样，花费了很多时间在存储状态的变化上。在备机上，该表又变得不可用了。

rep_test=> select count(*) from t_unlogged;
ERROR:  cannot access temporary or unlogged relations during recovery
rep_test=> select relpages, reltuples from pg_class where oid = 't_unlogged'::regclass;
 relpages | reltuples 
----------|-----------
        0 |         0

Does the replica knows about the unlogged tables?

当然，事实上pg_class知道这个表正在包含多少元组和数据块。然而，该表并没有消耗备库上的存储空间。换句话说，复制侧的数据库知道该表在主节点上占用了多少空间，因为pg_class（和其他系统表）也被复制了。表的数据在磁盘上丢失了。让我们在主库上看看这个情况。

rep_test=# select relpages, reltuples, 
                  pg_size_pretty( pg_relation_size( 't_unlogged') ), 
                  pg_relation_filepath( oid ) 
                  from pg_class where oid = 't_unlogged'::regclass;
 relpages | reltuples | pg_size_pretty | pg_relation_filepath 
----------|-----------|----------------|----------------------
    12738 |     2e+06 | 100 MB         | base/41441/41555

在磁盘上的文件大小是

% sudo du -h $PGDATA/base/41441/41555
100M    /postgres/12/data/base/41441/41555

在复制端是如何的呢？信息是一样的，但在磁盘上什么都没有

rep_test=# select relpages, reltuples, 
                  pg_size_pretty( pg_relation_size( 't_unlogged') ), 
                  pg_relation_filepath( oid ) 
                  from pg_class where oid = 't_unlogged'::regclass;
                  
 relpages | reltuples | pg_size_pretty | pg_relation_filepath 
----------|-----------|----------------|----------------------
    12738 |     2e+06 | 0 bytes        | base/41441/41555

在磁盘上，实际上，没有被表占用的空间

% sudo du -h /postgres/12/replica/base/41441/4155
0       /postgres/12/replica/base/41441/4155

Unlogged but replicated ~ ordinary

一个unlogged的表如果被复制，就失去了速度优势。为什么呢？因为一旦它变成logged，系统必须提供所有的机器来同步该表。如果你 "停止 "复制，移除复制槽和其他相关的东西，表就会获得速度上的提升。

Conclusions

正如预期的那样，PostgreSQL只复制有日志的表，而不是临时表或者无日志表。然而，后者作为占位符存在于复制端，一旦你把它们变成了logged，它们就会完全被搬运到复制端。

前文译自 To WAL or not to WAL? When unlogged becomes logged…

PostgreSQL 内存表选项 - 无日志表

背景

内存表常用于不需要持久化、变更频繁、RT低的场景。

目前社区版的PostgreSQL没有内存表的功能。postgrespro 提供了两个插件来实现内存表的类似功能。

https://postgrespro.com/docs/enterprise/10/in-memory

PostgreSQL 内存表

社区版也可以使用unlogged表来实现类似内存表的功能。

create unlogged table

无日志表的几个特点：

1.Unogged表不记录WAL日志，写入速度快。备库中没有数据，只有结构。2.当数据库崩溃时，unlogged的表数据会在数据库重启后自动清除。3.正常关闭数据库，重启时unlogged表有数据。4.unlogged table通常用于中间结果，频繁更改会话数据

无日志表和普通表的性能对比

pgbench -i -s 1000   
  
100000000 of 100000000 tuples (100%) done (elapsed 96.10 s, remaining 0.00 s)  
vacuum...  
set primary keys...  
done.

pgbench -M prepared -n -r -P 1 -c 28 -j 28 -T 360  
  
transaction type: <builtin: TPC-B (sort of)>  
scaling factor: 1000  
query mode: prepared  
number of clients: 28  
number of threads: 28  
duration: 360 s  
number of transactions actually processed: 11619832  
latency average = 0.867 ms  
latency stddev = 0.588 ms  
tps = 32277.202497 (including connections establishing)  
tps = 32279.414353 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  \set aid random(1, 100000 * :scale)  
         0.001  \set bid random(1, 1 * :scale)  
         0.001  \set tid random(1, 10 * :scale)  
         0.001  \set delta random(-5000, 5000)  
         0.061  BEGIN;  
         0.137  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;  
         0.092  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;  
         0.105  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;  
         0.104  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;  
         0.088  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);  
         0.277  END;

同步提交，BACKEND PROCESS需要主动刷WAL缓冲区。所以每个进程的IO都很高。

Total DISK READ :       0.00 B/s | Total DISK WRITE :     430.03 M/s  
Actual DISK READ:       0.00 B/s | Actual DISK WRITE:     431.46 M/s  
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                         
35100 be/4 postgres    0.00 B/s    6.04 M/s  0.00 %  2.48 % postgres: postgres postgres 127.0.0.1(40952) idle                 
35107 be/4 postgres    0.00 B/s    7.82 M/s  0.00 %  2.34 % postgres: postgres postgres 127.0.0.1(40966) idle in transaction  
35108 be/4 postgres    0.00 B/s    8.91 M/s  0.00 %  2.29 % postgres: postgres postgres 127.0.0.1(40968) COMMIT               
35113 be/4 postgres    0.00 B/s    7.00 M/s  0.00 %  2.25 % postgres: postgres postgres 127.0.0.1(40978) COMMIT               
35098 be/4 postgres    0.00 B/s    7.17 M/s  0.00 %  2.24 % postgres: postgres postgres 127.0.0.1(40948) UPDATE               
35116 be/4 postgres    0.00 B/s    6.78 M/s  0.00 %  2.19 % postgres: postgres postgres 127.0.0.1(40984) COMMIT               
35097 be/4 postgres    0.00 B/s    6.38 M/s  0.00 %  2.17 % postgres: postgres postgres 127.0.0.1(40946) UPDATE               
35096 be/4 postgres    0.00 B/s    7.84 M/s  0.00 %  2.16 % postgres: postgres postgres 127.0.0.1(40944) idle in transaction  
35115 be/4 postgres    0.00 B/s    6.17 M/s  0.00 %  2.14 % postgres: postgres postgres 127.0.0.1(40982) COMMIT               
35105 be/4 postgres    0.00 B/s    6.96 M/s  0.00 %  2.09 % postgres: postgres postgres 127.0.0.1(40962) SELECT               
35101 be/4 postgres    0.00 B/s    6.67 M/s  0.00 %  2.08 % postgres: postgres postgres 127.0.0.1(40954) COMMIT               
35106 be/4 postgres    0.00 B/s    6.27 M/s  0.00 %  2.07 % postgres: postgres postgres 127.0.0.1(40964) COMMIT               
35110 be/4 postgres    0.00 B/s    6.17 M/s  0.00 %  2.06 % postgres: postgres postgres 127.0.0.1(40972) idle in transaction  
35114 be/4 postgres    0.00 B/s    6.34 M/s  0.00 %  2.03 % postgres: postgres postgres 127.0.0.1(40980) COMMIT               
35120 be/4 postgres    0.00 B/s    6.05 M/s  0.00 %  2.02 % postgres: postgres postgres 127.0.0.1(40992) idle in transaction  
35119 be/4 postgres    0.00 B/s    6.95 M/s  0.00 %  2.00 % postgres: postgres postgres 127.0.0.1(40990) COMMIT               
35095 be/4 postgres    0.00 B/s    6.41 M/s  0.00 %  1.97 % postgres: postgres postgres 127.0.0.1(40942) idle in transaction  
35104 be/4 postgres    0.00 B/s    5.87 M/s  0.00 %  1.95 % postgres: postgres postgres 127.0.0.1(40960) UPDATE               
35118 be/4 postgres    0.00 B/s    5.47 M/s  0.00 %  1.93 % postgres: postgres postgres 127.0.0.1(40988) idle in transaction  
35109 be/4 postgres    0.00 B/s    6.35 M/s  0.00 %  1.91 % postgres: postgres postgres 127.0.0.1(40970) idle in transaction  
35117 be/4 postgres    0.00 B/s    6.02 M/s  0.00 %  1.90 % postgres: postgres postgres 127.0.0.1(40986) COMMIT               
35082 be/4 postgres    0.00 B/s    5.96 M/s  0.00 %  1.90 % postgres: postgres postgres 127.0.0.1(40938) idle in transaction  
35102 be/4 postgres    0.00 B/s    5.92 M/s  0.00 %  1.89 % postgres: postgres postgres 127.0.0.1(40956) BINDCT               
35111 be/4 postgres    0.00 B/s    6.08 M/s  0.00 %  1.89 % postgres: postgres postgres 127.0.0.1(40974) idle in transaction  
35099 be/4 postgres    0.00 B/s    5.76 M/s  0.00 %  1.83 % postgres: postgres postgres 127.0.0.1(40950) UPDATE               
35103 be/4 postgres    0.00 B/s    5.53 M/s  0.00 %  1.80 % postgres: postgres postgres 127.0.0.1(40958) COMMIT               
35112 be/4 postgres    0.00 B/s    5.63 M/s  0.00 %  1.80 % postgres: postgres postgres 127.0.0.1(40976) idle in transaction  
35094 be/4 postgres    0.00 B/s    5.37 M/s  0.00 %  1.75 % postgres: postgres postgres 127.0.0.1(40940) idle in transaction  
49040 be/4 postgres    0.00 B/s  146.86 K/s  0.00 %  0.07 % postgres: wal writer process  
49039 be/4 postgres    0.00 B/s  250.00 M/s  0.00 %  0.00 % postgres: writer process

普通表synchronous_commit=off

alter role postgres set synchronous_commit=off;  
wal_writer_delay = 10ms  
wal_writer_flush_after = 1MB

pgbench -M prepared -n -r -P 1 -c 28 -j 28 -T 360  
  
transaction type: <builtin: TPC-B (sort of)>  
scaling factor: 1000  
query mode: prepared  
number of clients: 28  
number of threads: 28  
duration: 360 s  
number of transactions actually processed: 16063922  
latency average = 0.627 ms  
latency stddev = 0.145 ms  
tps = 44621.854810 (including connections establishing)  
tps = 44624.950701 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  \set aid random(1, 100000 * :scale)  
         0.001  \set bid random(1, 1 * :scale)  
         0.001  \set tid random(1, 10 * :scale)  
         0.000  \set delta random(-5000, 5000)  
         0.055  BEGIN;  
         0.124  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;  
         0.089  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;  
         0.099  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;  
         0.098  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;  
         0.085  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);  
         0.074  END;

BACKEND PROCESS不需要主动刷WAL缓冲区，因为是异步提交。所以每个进程的IO都不高。

Total DISK READ :       0.00 B/s | Total DISK WRITE :     334.69 M/s  
Actual DISK READ:       0.00 B/s | Actual DISK WRITE:     338.86 M/s  
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                         
49040 be/4 postgres    0.00 B/s   32.97 M/s  0.00 %  2.35 % postgres: wal writer process  
49039 be/4 postgres    0.00 B/s  299.81 M/s  0.00 %  0.59 % postgres: writer process  
35673 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(41228) BIND                 
35656 be/4 postgres    0.00 B/s    7.71 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(41194) idle in transaction  
35663 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(41208) UPDATE               
35672 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(41226) idle in transaction  
35661 be/4 postgres    0.00 B/s 1095.42 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(41204) INSERT               
35650 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(41182) idle in transaction  
35653 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(41188) idle                 
35662 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(41206) UPDATE               
35667 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(41216) idle in transaction       
35635 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41174) UPDATEn transaction  
35651 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41184) idle in transaction  
35666 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41214) idle                 
35648 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41178) UPDATE               
35668 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41218) idle in transaction  
35664 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41210) idle in transaction  
35659 be/4 postgres    0.00 B/s    7.71 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41200) UPDATE               
35665 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41212) idle in transaction  
35657 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41196) BIND                 
35660 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41202) idle in transaction  
35652 be/4 postgres    0.00 B/s  655.71 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41186) UPDATE               
35655 be/4 postgres    0.00 B/s    7.71 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41192) COMMIT               
35654 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41190) idle                 
35658 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41198) idle    
35670 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41222) idle                 
35649 be/4 postgres    0.00 B/s    7.71 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41180) UPDATE               
35647 be/4 postgres    0.00 B/s   15.43 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41176) UPDATE

无日志表

pgbench -i -s 1000 --unlogged-tables   
  
100000000 of 100000000 tuples (100%) done (elapsed 81.19 s, remaining 0.00 s)  
vacuum...  
set primary keys...  
done.

pgbench -M prepared -n -r -P 1 -c 28 -j 28 -T 360  
  
transaction type: <builtin: TPC-B (sort of)>  
scaling factor: 1000  
query mode: prepared  
number of clients: 28  
number of threads: 28  
duration: 360 s  
number of transactions actually processed: 16551654  
latency average = 0.609 ms  
latency stddev = 0.374 ms  
tps = 45973.045817 (including connections establishing)  
tps = 45976.645113 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  \set aid random(1, 100000 * :scale)  
         0.001  \set bid random(1, 1 * :scale)  
         0.001  \set tid random(1, 10 * :scale)  
         0.000  \set delta random(-5000, 5000)  
         0.054  BEGIN;  
         0.119  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;  
         0.088  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;  
         0.096  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;  
         0.095  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;  
         0.081  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);  
         0.072  END;

unlogged表不写入WAL日志。writer进程是一个bgwrite进程，异步写脏的共享缓冲区。

Total DISK READ :       0.00 B/s | Total DISK WRITE :     322.65 M/s  
Actual DISK READ:       0.00 B/s | Actual DISK WRITE:     316.41 M/s  
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                         
49040 be/4 postgres    0.00 B/s 1811.20 K/s  0.00 %  0.74 % postgres: wal writer process  
49039 be/4 postgres    0.00 B/s  315.29 M/s  0.00 %  0.00 % postgres: writer process  
34947 be/4 postgres    0.00 B/s    7.71 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(40916) SELECT  
34948 be/4 postgres    0.00 B/s   38.54 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(40918) BIND                 
34928 be/4 postgres    0.00 B/s   15.41 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(40878) BIND                 
34930 be/4 postgres    0.00 B/s   23.12 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(40882) idle in transaction  
34912 be/4 postgres    0.00 B/s    7.71 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(40874) idle in transaction  
34944 be/4 postgres    0.00 B/s  655.11 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(40910) idle in transaction  
34935 be/4 postgres    0.00 B/s   23.12 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(40892) BIND                 
34939 be/4 postgres    0.00 B/s  816.97 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40900) idle                 
34933 be/4 postgres    0.00 B/s   23.12 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40888) idle in transaction  
34952 be/4 postgres    0.00 B/s    7.71 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40926) idle in transaction  
34936 be/4 postgres    0.00 B/s   23.12 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40894) idle in transaction  
34946 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40914) idle                 
34929 be/4 postgres    0.00 B/s  801.55 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40880) idle in transaction  
34943 be/4 postgres    0.00 B/s  493.26 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40908) idle in transaction  
34932 be/4 postgres    0.00 B/s  493.26 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40886) BIND                 
34942 be/4 postgres    0.00 B/s   38.54 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40906) BIND                 
34931 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40884) idle in transaction  
34951 be/4 postgres    0.00 B/s   30.83 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40924) idle in transaction  
34927 be/4 postgres    0.00 B/s    7.71 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40876) UPDATE waiting            
34953 be/4 postgres    0.00 B/s   23.12 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40928) UPDATE waiting       
34949 be/4 postgres    0.00 B/s   38.54 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40920) idle in transaction  
34945 be/4 postgres    0.00 B/s   46.24 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40912) UPDATE               
34950 be/4 postgres    0.00 B/s   15.41 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(40922) idle in transaction  
49036 be/4 postgres    0.00 B/s    3.85 K/s  0.00 %  0.00 % postgres: logger process

概括

写性能

无日志表 > 普通表（异步事务） > 普通表（同步事务）

资源消耗

unogged表不写WAL，IO开销小。

适应场景

批量计算的中间结果，频繁变化的会话数据。

注意事项

1.Unogged表不记录wal日志，写入速度快。备库中没有数据，只有结构。2.当数据库崩溃时，unlogged的表数据会在数据库重启后自动清除。

参考

https://postgrespro.com/docs/enterprise/10/in-memory

PostgreSQL Memory Table

https://www.postgresql.org/docs/11/static/sql-createtable.html

前文译自 PostgreSQL Memory Table Options - unlogged table

小结

可以看到，无日志表是有一定的优势的，在特定场景下可以发挥意想不到的效果，在v10以前，哈希索引也是无日志的。

Unlogged table

Table type with WAL logging disabled, improving write performance at the expense of reliability

An unlogged table is a type of a table where data written to it will not be written to write-ahead logs. This improves write speed considerably compared to normal tables. However, unlogged tables are not crash-safe and will be automatically truncated following a server crash or unclean shutdown. Unlogged tables will also not be replicated.

Unlogged tables were introduced in PostgreSQL 9.1. From PostgreSQL 9.5, ALTER TABLE can be used to change a normal table to an unlogged one, and vice-versa.

Change history

•PostgreSQL 9.5]•logged/unlogged state of a table can be changed via ALTER TABLE (f41872d0)•PostgreSQL 9.1•added (commit 53dbc27c)

标签：127.0,PostgreSQL,postgres,unlogged,0.1,0.00,idle,内存
From： https://blog.51cto.com/u_13216675/5821086