PostgreSQL中的伪内存表
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 TABLEt_unlogged( like t_norm including all );rep_test=> CREATE TEMPORARY TABLEt_temp( like t_norm including all );rep_test=> INSERT INTO t_norm( t )SELECT 'Row #' || vFROM generate_series( 1, 1000000 ) v;INSERT 0 1000000Time: 4712.185 ms (00:04.712)rep_test=> INSERT INTO t_temp( t )SELECT 'Row #' || vFROM generate_series( 1, 1000000 ) v;INSERT 0 1000000Time: 1789.473 ms (00:01.789)rep_test=> INSERT INTO t_unlogged( t )SELECT 'Unlogged #' || vFROM generate_series( 1, 1000000 ) v;INSERT 0 1000000Time: 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.confhost replication backup carmensita trust
复制完成后，便可以启动备用节点
% /usr/pgsql-12/bin/pg_ctl -D /postgres/12/replica startin 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-bitLOG: listening on IPv4 address "0.0.0.0", port 5432LOG: listening on IPv6 address "::", port 5432LOG: 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 processHINT: Future log output will appear in directory "log".
Check the tables on the replication side
现在该检查复制主机上的复制数据库了
% psql -h carmensita -U luca rep_testrep_test=> \dLista delle relazioniSchema | Nome | Tipo | Proprietario--------|-------------------|----------|--------------public | t_norm | tabella | lucapublic | t_norm_pk_seq | sequenza | lucapublic | t_unlogged | tabella | lucapublic | 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=> \dLista delle relazioniSchema | Nome | Tipo | Proprietario--------|-------------------|----------|--------------public | t_norm | tabella | lucapublic | t_norm_pk_seq | sequenza | lucapublic | t_unlogged | tabella | lucapublic | 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 TABLETime: 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 TABLETime: 5236.165 ms (00:05.236)rep_test=# truncate t_unlogged;TRUNCATE TABLETime: 21.498 ms
这里需要注意的是，同样，花费了很多时间在存储状态的变化上。在备机上，该表又变得不可用了。
rep_test=> select count(*) from t_unlogged;ERROR: cannot access temporary or unlogged relations during recoveryrep_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/41555100M /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/41550 /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 1000100000000 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 360transaction type: <builtin: TPC-B (sort of)>scaling factor: 1000query mode: preparednumber of clients: 28number of threads: 28duration: 360 snumber of transactions actually processed: 11619832latency average = 0.867 mslatency stddev = 0.588 mstps = 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/sActual DISK READ: 0.00 B/s | Actual DISK WRITE: 431.46 M/sTID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND35100 be/4 postgres 0.00 B/s 6.04 M/s 0.00 % 2.48 % postgres: postgres postgres 127.0.0.1(40952) idle35107 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 transaction35108 be/4 postgres 0.00 B/s 8.91 M/s 0.00 % 2.29 % postgres: postgres postgres 127.0.0.1(40968) COMMIT35113 be/4 postgres 0.00 B/s 7.00 M/s 0.00 % 2.25 % postgres: postgres postgres 127.0.0.1(40978) COMMIT35098 be/4 postgres 0.00 B/s 7.17 M/s 0.00 % 2.24 % postgres: postgres postgres 127.0.0.1(40948) UPDATE35116 be/4 postgres 0.00 B/s 6.78 M/s 0.00 % 2.19 % postgres: postgres postgres 127.0.0.1(40984) COMMIT35097 be/4 postgres 0.00 B/s 6.38 M/s 0.00 % 2.17 % postgres: postgres postgres 127.0.0.1(40946) UPDATE35096 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 transaction35115 be/4 postgres 0.00 B/s 6.17 M/s 0.00 % 2.14 % postgres: postgres postgres 127.0.0.1(40982) COMMIT35105 be/4 postgres 0.00 B/s 6.96 M/s 0.00 % 2.09 % postgres: postgres postgres 127.0.0.1(40962) SELECT35101 be/4 postgres 0.00 B/s 6.67 M/s 0.00 % 2.08 % postgres: postgres postgres 127.0.0.1(40954) COMMIT35106 be/4 postgres 0.00 B/s 6.27 M/s 0.00 % 2.07 % postgres: postgres postgres 127.0.0.1(40964) COMMIT35110 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 transaction35114 be/4 postgres 0.00 B/s 6.34 M/s 0.00 % 2.03 % postgres: postgres postgres 127.0.0.1(40980) COMMIT35120 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 transaction35119 be/4 postgres 0.00 B/s 6.95 M/s 0.00 % 2.00 % postgres: postgres postgres 127.0.0.1(40990) COMMIT35095 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 transaction35104 be/4 postgres 0.00 B/s 5.87 M/s 0.00 % 1.95 % postgres: postgres postgres 127.0.0.1(40960) UPDATE35118 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 transaction35109 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 transaction35117 be/4 postgres 0.00 B/s 6.02 M/s 0.00 % 1.90 % postgres: postgres postgres 127.0.0.1(40986) COMMIT35082 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 transaction35102 be/4 postgres 0.00 B/s 5.92 M/s 0.00 % 1.89 % postgres: postgres postgres 127.0.0.1(40956) BINDCT35111 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 transaction35099 be/4 postgres 0.00 B/s 5.76 M/s 0.00 % 1.83 % postgres: postgres postgres 127.0.0.1(40950) UPDATE35103 be/4 postgres 0.00 B/s 5.53 M/s 0.00 % 1.80 % postgres: postgres postgres 127.0.0.1(40958) COMMIT35112 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 transaction35094 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 transaction49040 be/4 postgres 0.00 B/s 146.86 K/s 0.00 % 0.07 % postgres: wal writer process49039 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 = 10mswal_writer_flush_after = 1MB
pgbench -M prepared -n -r -P 1 -c 28 -j 28 -T 360transaction type: <builtin: TPC-B (sort of)>scaling factor: 1000query mode: preparednumber of clients: 28number of threads: 28duration: 360 snumber of transactions actually processed: 16063922latency average = 0.627 mslatency stddev = 0.145 mstps = 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/sActual DISK READ: 0.00 B/s | Actual DISK WRITE: 338.86 M/sTID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND49040 be/4 postgres 0.00 B/s 32.97 M/s 0.00 % 2.35 % postgres: wal writer process49039 be/4 postgres 0.00 B/s 299.81 M/s 0.00 % 0.59 % postgres: writer process35673 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.01 % postgres: postgres postgres 127.0.0.1(41228) BIND35656 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 transaction35663 be/4 postgres 0.00 B/s 15.43 K/s 0.00 % 0.01 % postgres: postgres postgres 127.0.0.1(41208) UPDATE35672 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 transaction35661 be/4 postgres 0.00 B/s 1095.42 K/s 0.00 % 0.01 % postgres: postgres postgres 127.0.0.1(41204) INSERT35650 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 transaction35653 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.01 % postgres: postgres postgres 127.0.0.1(41188) idle35662 be/4 postgres 0.00 B/s 15.43 K/s 0.00 % 0.01 % postgres: postgres postgres 127.0.0.1(41206) UPDATE35667 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 transaction35635 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41174) UPDATEn transaction35651 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 transaction35666 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41214) idle35648 be/4 postgres 0.00 B/s 15.43 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41178) UPDATE35668 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 transaction35664 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 transaction35659 be/4 postgres 0.00 B/s 7.71 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41200) UPDATE35665 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 transaction35657 be/4 postgres 0.00 B/s 15.43 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41196) BIND35660 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 transaction35652 be/4 postgres 0.00 B/s 655.71 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41186) UPDATE35655 be/4 postgres 0.00 B/s 7.71 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41192) COMMIT35654 be/4 postgres 0.00 B/s 15.43 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41190) idle35658 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41198) idle35670 be/4 postgres 0.00 B/s 15.43 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41222) idle35649 be/4 postgres 0.00 B/s 7.71 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(41180) UPDATE35647 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-tables100000000 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 360transaction type: <builtin: TPC-B (sort of)>scaling factor: 1000query mode: preparednumber of clients: 28number of threads: 28duration: 360 snumber of transactions actually processed: 16551654latency average = 0.609 mslatency stddev = 0.374 mstps = 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/sActual DISK READ: 0.00 B/s | Actual DISK WRITE: 316.41 M/sTID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND49040 be/4 postgres 0.00 B/s 1811.20 K/s 0.00 % 0.74 % postgres: wal writer process49039 be/4 postgres 0.00 B/s 315.29 M/s 0.00 % 0.00 % postgres: writer process34947 be/4 postgres 0.00 B/s 7.71 K/s 0.00 % 0.01 % postgres: postgres postgres 127.0.0.1(40916) SELECT34948 be/4 postgres 0.00 B/s 38.54 K/s 0.00 % 0.01 % postgres: postgres postgres 127.0.0.1(40918) BIND34928 be/4 postgres 0.00 B/s 15.41 K/s 0.00 % 0.01 % postgres: postgres postgres 127.0.0.1(40878) BIND34930 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 transaction34912 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 transaction34944 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 transaction34935 be/4 postgres 0.00 B/s 23.12 K/s 0.00 % 0.01 % postgres: postgres postgres 127.0.0.1(40892) BIND34939 be/4 postgres 0.00 B/s 816.97 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(40900) idle34933 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 transaction34952 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 transaction34936 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 transaction34946 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(40914) idle34929 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 transaction34943 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 transaction34932 be/4 postgres 0.00 B/s 493.26 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(40886) BIND34942 be/4 postgres 0.00 B/s 38.54 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(40906) BIND34931 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 transaction34951 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 transaction34927 be/4 postgres 0.00 B/s 7.71 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(40876) UPDATE waiting34953 be/4 postgres 0.00 B/s 23.12 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(40928) UPDATE waiting34949 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 transaction34945 be/4 postgres 0.00 B/s 46.24 K/s 0.00 % 0.00 % postgres: postgres postgres 127.0.0.1(40912) UPDATE34950 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 transaction49036 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)
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
上一篇：
如何高效处理/索引 JSON
下一篇：
从源码出发，深度剖析字节对齐
请登录后发表评论
登录
全部评论
