Label
PostgreSQL, memory table, unlogged table
background
Memory tables are often used in scenarios where persistence is not required, changes are frequent, and RT is low.
At present, the community version of PostgreSQL does not have the function of memory tables. postgrespro provides two plug-ins to implement similar functions of memory tables.
https://postgrespro.com/docs/enterprise/10/in-memory
The unlogged table can also be used in the community version to achieve functions similar to memory tables.
create unlogged table
Several features of unlogged table:
1. Unogged table does not record wal logs. It has fast writing speed. There is no data in the backup database, only structure.
2. When the database crash, the unlogged table data is automatically cleared when the database restarts.
3. Close the database normally and unlogged table has data when restarting.
4. unlogged table s are usually used for intermediate results, frequently changing session data
Performance comparison between unlogged table and common table
1 General table 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;
Synchronized submission, BACKEND PROCESS needs to brush wal buffer on its own initiative. So the IO of each process is very high.
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 General table 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 does not need to actively brush wal buffer for asynchronous submission. So the IO of each process is not high.
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 does not write WAL logs.
writer process is a bgwrite process, asynchronous 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
Summary
1. Writing Performance
Unlogged Table > common table (asynchronous transaction) > common table (synchronous transaction)
2. Resource Consumption
Unogged table does not write WAL, IO overhead is small.
3. Adapting to Scene
Intermediate results of batch computing, frequently changing session data.
4. Notes
4.1. Unogged table does not record wal logs. It has fast writing speed. There is no data in the backup, only structure.
4.2. When the database crash, the unlogged table data is automatically cleared when the database restarts.
Reference resources
https://postgrespro.com/docs/enterprise/10/in-memory
https://www.postgresql.org/docs/11/static/sql-createtable.html