Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 内存表可选项 - unlogged table

10 minute read

背景

内存表，通常被用于不需要持久化，变更频繁，访问RT低的场景。

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

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

《PostgreSQL 内存表》

社区版本使用unlogged table也可以达到类似内存表的功能。

create unlogged table

unlogged table 的几个特点：

1、unlogged table不记录wal日志，写入速度快，备库无数据，只有结构。

2、当数据库crash后，数据库重启时自动清空unlogged table的数据。

3、正常关闭数据库，再启动时，unlogged table有数据。

4、unlogged table通常用于中间结果，频繁变更的会话数据

unlogged table 与普通表的性能对比

1 普通表 synchronous_commit=on

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 buffer。所以每个进程的写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

2 普通表 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 buffer。所以每个进程的写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               
35671 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41224) idle in transaction  
35669 be/4 postgres    0.00 B/s    7.71 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(41220) idle in transaction  
49060 be/4 postgres    0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(65006) idle

3 unlogged table

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 table不写 WAL日志。

writer process 是bgwrite进程，异步write dirty shared buffer。

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

小结

1、写性能

unlogged table > 普通表(异步事务) > 普通表(同步事务)

2、资源消耗

unlogged table 不写WAL，IO开销小。

3、适应场景

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

4、注意事项

4.1、unlogged table不记录wal日志，写入速度快，备库无数据，只有结构。

4.2、当数据库crash后，数据库重启时自动清空unlogged table的数据。

参考

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

《PostgreSQL 内存表》

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

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...