PostgreSQL 轻量级数字对称加密
背景
之前有位同事向我咨询关于发红包的事情，领红包需要输入验证码。这个码在数据库中预先生成，必须是唯一值。
领红包时更新该记录。
CASE参考：
《fast random data query & delete use ctid in postgresql》
为什么要取随机数字呢？防止用户猜测，领别人的红包，因为你的应用可能是无账号体系的，并且无法获得用户的手机号来做到一对一的防冒领。
现在介绍一个不需要预先生成的方法。你只需要连续的产生数字即可，将数字转换为一个随机的唯一值，领取时再转换回来进行验证。
用到这个插件：
http://pgxn.org/dist/pgspeck/1.0.0/
用法举例：
postgres=# select pgspeck_encrypt32(1,101);
pgspeck_encrypt32
-------------------
3732741981
(1 row)
postgres=# select pgspeck_decrypt32(3732741981,101);
pgspeck_decrypt32
-------------------
1
(1 row)
性能杠杠的。
postgres@digoal-> pgbench -M prepared -n -r -f ./t1.sql -P 1 -c 8 -j 8 -T 10
progress: 1.0 s, 79150.2 tps, lat 0.094 ms stddev 0.563
progress: 2.0 s, 83749.8 tps, lat 0.094 ms stddev 0.201
progress: 3.0 s, 81786.0 tps, lat 0.097 ms stddev 0.262
progress: 4.0 s, 82263.1 tps, lat 0.096 ms stddev 0.143
progress: 5.0 s, 80495.5 tps, lat 0.098 ms stddev 0.557
progress: 6.0 s, 81388.5 tps, lat 0.097 ms stddev 0.181
progress: 7.0 s, 81181.9 tps, lat 0.097 ms stddev 0.230
progress: 8.0 s, 81263.7 tps, lat 0.097 ms stddev 0.256
progress: 9.0 s, 82321.9 tps, lat 0.096 ms stddev 0.251
progress: 10.0 s, 80793.2 tps, lat 0.098 ms stddev 0.373
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 10 s
number of transactions actually processed: 814432
latency average: 0.096 ms
latency stddev: 0.331 ms
tps = 81436.302345 (including connections establishing)
tps = 81885.818438 (excluding connections establishing)
statement latencies in milliseconds:
0.096327 select 1;
32位加密测试
postgres=# alter function pgspeck_encrypt32(int8,int8) volatile;
postgres@digoal-> pgbench -M prepared -n -r -f ./t1.sql -P 1 -c 8 -j 8 -T 10
progress: 1.0 s, 75791.6 tps, lat 0.094 ms stddev 0.391
progress: 2.0 s, 79234.8 tps, lat 0.100 ms stddev 0.247
progress: 3.0 s, 76112.6 tps, lat 0.104 ms stddev 0.270
progress: 4.0 s, 75863.8 tps, lat 0.104 ms stddev 0.281
progress: 5.0 s, 74879.8 tps, lat 0.106 ms stddev 0.467
progress: 6.0 s, 75245.8 tps, lat 0.105 ms stddev 0.385
progress: 7.0 s, 74679.0 tps, lat 0.106 ms stddev 0.363
progress: 8.0 s, 73644.8 tps, lat 0.107 ms stddev 0.397
progress: 9.0 s, 73358.4 tps, lat 0.107 ms stddev 0.621
progress: 10.0 s, 75177.2 tps, lat 0.106 ms stddev 0.353
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8 duration: 10 s
number of transactions actually processed: 754115
latency average: 0.104 ms
latency stddev: 0.390 ms
tps = 75400.408600 (including connections establishing)
tps = 76086.389125 (excluding connections establishing)
statement latencies in milliseconds:
0.103725 select pgspeck_encrypt32(3832012150,100);
TPS损失7.4%