PostgreSQL 彻底解决膨胀问题

PostgreSQL 9.6新增的一个patch，暂时还没有提交，这个patch主要是防止数据库中的某些long sql导致LONG snapshot导致数据库膨胀。
原理参考：
http://blog.163.com/digoal@126/blog/static/1638770402015329115636287/

下载补丁和数据库源码（使用2015-10-13号master分支源码）
http://www.postgresql.org/message-id/flat/BLU437-SMTP193E7367B788D2EC2B2B81DC160@phx.gbl#BLU437-SMTP193E7367B788D2EC2B2B81DC160@phx.gbl
打补丁
[root@digoal soft_bak]# tar -zxvf postgresql-aa7f949.tar.gz

[root@digoal soft_bak]# cd postgresql-aa7f949
[root@digoal postgresql-aa7f949]# patch -p1 < ../snapshot-too-old-v3.diff
patching file doc/src/sgml/config.sgml
patching file src/backend/access/brin/brin.c
patching file src/backend/access/brin/brin_revmap.c
patching file src/backend/access/gin/ginbtree.c
patching file src/backend/access/gin/gindatapage.c
patching file src/backend/access/gin/ginget.c
patching file src/backend/access/gin/gininsert.c
patching file src/backend/access/gist/gistget.c
patching file src/backend/access/hash/hash.c
patching file src/backend/access/hash/hashsearch.c
patching file src/backend/access/heap/heapam.c
patching file src/backend/access/heap/pruneheap.c
patching file src/backend/access/nbtree/nbtinsert.c
patching file src/backend/access/nbtree/nbtpage.c
patching file src/backend/access/nbtree/nbtsearch.c
patching file src/backend/access/spgist/spgscan.c
patching file src/backend/commands/vacuum.c
patching file src/backend/commands/vacuumlazy.c
patching file src/backend/storage/ipc/ipci.c
patching file src/backend/storage/ipc/procarray.c
patching file src/backend/storage/lmgr/lwlocknames.txt
patching file src/backend/utils/errcodes.txt
patching file src/backend/utils/misc/guc.c
patching file src/backend/utils/misc/postgresql.conf.sample
patching file src/backend/utils/time/snapmgr.c
patching file src/include/access/brin_revmap.h
patching file src/include/access/gin_private.h
patching file src/include/access/nbtree.h
patching file src/include/storage/bufmgr.h
patching file src/include/utils/rel.h
patching file src/include/utils/snapmgr.h
patching file src/include/utils/snapshot.h
patching file src/test/modules/Makefile
patching file src/test/modules/sto/.gitignore
patching file src/test/modules/sto/Makefile
patching file src/test/modules/sto/t/001_snapshot_too_old.pl
patching file src/test/modules/sto/t/002_snapshot_too_old_select.pl
patching file src/test/perl/TestLib.pm
安装
# ./configure --prefix=/opt/pgsql9.5devel
# gmake world -j 32
# gmake install-world

su - pg95
vi .bash_profile
# add by digoal
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=1931
export PGDATA=/data01/pg_root_1931
export LANG=en_US.utf8
export PGHOME=/opt/pgsql9.5devel
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGDATABASE=postgres
export PGUSER=postgres
alias rm='rm -i'
alias ll='ls -lh'
unalias vi

. ~/.bash_profile
pg95@digoal-> initdb -D $PGDATA -U postgres -W -E UTF8 --locale=C

为了快速测试效果，设置1分钟的snapshot，实际应用中用户可以根据实际的需要设置。
vi $PGDATA/postgresql.conf
old_snapshot_threshold = 1min
启动数据库
pg_ctl start
测试：
会话1
postgres=# create table test(id int, crt_time timestamp);
CREATE TABLE
postgres=# insert into test values (1,now()) returning *;
 id |          crt_time
----+----------------------------
  1 | 2015-11-09 14:24:26.161537
(1 row)
INSERT 0 1
会话2，开启一个RC隔离级别的事务
postgres=# begin transaction isolation level repeatable read;
BEGIN
postgres=# select *,now(),clock_timestamp() from test;
 id |          crt_time          |              now              |       clock_timestamp
----+----------------------------+-------------------------------+------------------------------
  1 | 2015-11-09 14:24:26.161537 | 2015-11-09 14:24:37.289911+08 | 2015-11-09 14:24:47.88468+08
(1 row)
会话1
postgres=# update test set crt_time=now() where id=1 returning *;
 id |          crt_time
----+----------------------------
  1 | 2015-11-09 14:25:04.101895
(1 row)
UPDATE 1
会话2
postgres=# select *,now(),clock_timestamp() from test;
 id |          crt_time          |              now              |        clock_timestamp
----+----------------------------+-------------------------------+-------------------------------
  1 | 2015-11-09 14:24:26.161537 | 2015-11-09 14:24:37.289911+08 | 2015-11-09 14:25:12.821213+08
(1 row)
......

在发生snapshot old错误前，我们看到这个事务占领了一个snapshot id backend_xmin  ， 数据库无法回收这之后产生的垃圾。
-[ RECORD 2 ]----+-------------------------------------------------------
datid            | 13241
datname          | postgres
pid              | 16575
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2015-11-09 14:19:59.45462+08
xact_start       | 2015-11-09 14:24:37.289911+08
query_start      | 2015-11-09 14:25:12.820912+08
state_change     | 2015-11-09 14:25:12.821263+08
waiting          | f
state            | idle in transaction
backend_xid      |
backend_xmin     | 1769
query            | select *,now(),clock_timestamp() from test;

postgres=# vacuum verbose test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL:  1 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

当这个会话去访问1分钟前的快照时，报错。
postgres=# select *,now(),clock_timestamp() from test;
ERROR:  snapshot too old

这个时候，可以回收之前的垃圾了，
postgres=# vacuum verbose test;
INFO:  vacuuming "public.test"
INFO:  "test": found 1 removable, 1 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
因为这个事务已经释放了backend_xmin     。
-[ RECORD 2 ]----+--------------------------------------------
datid            | 13241
datname          | postgres
pid              | 16575
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2015-11-09 14:19:59.45462+08
xact_start       |
query_start      | 2015-11-09 14:26:00.545314+08
state_change     | 2015-11-09 14:26:00.545849+08
waiting          | f
state            | idle in transaction (aborted)
backend_xid      |
backend_xmin     |
query            | select *,now(),clock_timestamp() from test;

再次发起请求直接报要求用户回滚事务。
postgres=# select *,now(),clock_timestamp() from test;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
年龄也可以正常的降低：
postgres=# vacuum freeze test;
VACUUM
postgres=# select age(relfrozenxid) from pg_class where relname='test';
 age
-----
   0
(1 row)

测试游标（貌似snapshot too old对游标不起作用）：
会话1
postgres=# insert into test select generate_series(1,100),clock_timestamp();
INSERT 0 100
会话2
postgres=# begin;
BEGIN
postgres=# declare c1 scroll cursor with hold for select * from test order by id;
DECLARE CURSOR
会话1
postgres=# update test set crt_time=now() where id=15;
UPDATE 1
postgres=# select * from test where id=15;
 id |          crt_time
----+----------------------------
 15 | 2015-11-09 14:58:19.889967
(1 row)
会话2，已超1分钟。
postgres=# select clock_timestamp();
        clock_timestamp
-------------------------------
 2015-11-09 14:59:23.534014+08
(1 row)
无法回收垃圾
postgres=# vacuum verbose test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 102 nonremovable row versions in 1 out of 1 pages
DETAIL:  1 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
fetch前后数据一致。
postgres=# fetch 1 from c1;
 id |          crt_time
----+----------------------------
 10 | 2015-11-09 14:40:16.318757
(1 row)

postgres=# fetch 1 from c1;
 id |         crt_time
----+---------------------------
 11 | 2015-11-09 14:40:16.31876
(1 row)

postgres=# fetch 1 from c1;
 id |          crt_time
----+----------------------------
 12 | 2015-11-09 14:40:16.318763
(1 row)

postgres=# fetch 1 from c1;
 id |          crt_time
----+----------------------------
 13 | 2015-11-09 14:40:16.318765
(1 row)

postgres=# fetch 1 from c1;
 id |          crt_time
----+----------------------------
 14 | 2015-11-09 14:40:16.318767
(1 row)

postgres=# fetch 1 from c1;
 id |          crt_time
----+----------------------------
 15 | 2015-11-09 14:40:16.319126
(1 row)

postgres=# fetch 1 from c1;
 id |         crt_time
----+---------------------------
 16 | 2015-11-09 14:40:16.31915
(1 row)

postgres=# fetch 100 from c1;
 id  |          crt_time
-----+----------------------------
  17 | 2015-11-09 14:40:16.319153
  18 | 2015-11-09 14:40:16.319155
  19 | 2015-11-09 14:40:16.319158
  20 | 2015-11-09 14:40:16.31916
  21 | 2015-11-09 14:40:16.319163
  22 | 2015-11-09 14:40:16.319165
  23 | 2015-11-09 14:40:16.319168
  24 | 2015-11-09 14:40:16.31917
  25 | 2015-11-09 14:40:16.319173
  26 | 2015-11-09 14:40:16.319175
  27 | 2015-11-09 14:40:16.319178
  28 | 2015-11-09 14:40:16.31918
  29 | 2015-11-09 14:40:16.319183
  30 | 2015-11-09 14:40:16.319185
  31 | 2015-11-09 14:40:16.319188
  32 | 2015-11-09 14:40:16.31919
  33 | 2015-11-09 14:40:16.319193
  34 | 2015-11-09 14:40:16.319195
  35 | 2015-11-09 14:40:16.319198
  36 | 2015-11-09 14:40:16.3192
  37 | 2015-11-09 14:40:16.319203
  38 | 2015-11-09 14:40:16.319205
  39 | 2015-11-09 14:40:16.319208
  40 | 2015-11-09 14:40:16.31921
  41 | 2015-11-09 14:40:16.319213
  42 | 2015-11-09 14:40:16.319215
  43 | 2015-11-09 14:40:16.319218
  44 | 2015-11-09 14:40:16.31922
  45 | 2015-11-09 14:40:16.319222
  46 | 2015-11-09 14:40:16.319225
  47 | 2015-11-09 14:40:16.319227
  48 | 2015-11-09 14:40:16.31923
  49 | 2015-11-09 14:40:16.319232
  50 | 2015-11-09 14:40:16.319235
  51 | 2015-11-09 14:40:16.319237
  52 | 2015-11-09 14:40:16.31924
  53 | 2015-11-09 14:40:16.319242
  54 | 2015-11-09 14:40:16.319245
  55 | 2015-11-09 14:40:16.319247
  56 | 2015-11-09 14:40:16.31925
  57 | 2015-11-09 14:40:16.319252
  58 | 2015-11-09 14:40:16.319255
  59 | 2015-11-09 14:40:16.319257
  60 | 2015-11-09 14:40:16.319259
  61 | 2015-11-09 14:40:16.319262
  62 | 2015-11-09 14:40:16.319264
  63 | 2015-11-09 14:40:16.319267
  64 | 2015-11-09 14:40:16.319269
  65 | 2015-11-09 14:40:16.319272
  66 | 2015-11-09 14:40:16.319274
  67 | 2015-11-09 14:40:16.319277
  68 | 2015-11-09 14:40:16.319279
  69 | 2015-11-09 14:40:16.319282
  70 | 2015-11-09 14:40:16.319284
  71 | 2015-11-09 14:40:16.319287
  72 | 2015-11-09 14:40:16.319303
  73 | 2015-11-09 14:40:16.319306
  74 | 2015-11-09 14:40:16.319309
  75 | 2015-11-09 14:40:16.319311
  76 | 2015-11-09 14:40:16.319314
  77 | 2015-11-09 14:40:16.319316
  78 | 2015-11-09 14:40:16.319319
  79 | 2015-11-09 14:40:16.319321
  80 | 2015-11-09 14:40:16.319324
  81 | 2015-11-09 14:40:16.319326
  82 | 2015-11-09 14:40:16.319329
  83 | 2015-11-09 14:40:16.319331
  84 | 2015-11-09 14:40:16.319334
  85 | 2015-11-09 14:40:16.319336
  86 | 2015-11-09 14:40:16.319339
  87 | 2015-11-09 14:40:16.319341
  88 | 2015-11-09 14:40:16.319343
  89 | 2015-11-09 14:40:16.319346
  90 | 2015-11-09 14:40:16.319348
  91 | 2015-11-09 14:40:16.319351
  92 | 2015-11-09 14:40:16.319353
  93 | 2015-11-09 14:40:16.319356
  94 | 2015-11-09 14:40:16.319358
  95 | 2015-11-09 14:40:16.319361
  96 | 2015-11-09 14:40:16.319363
  97 | 2015-11-09 14:40:16.319366
  98 | 2015-11-09 14:40:16.319368
  99 | 2015-11-09 14:40:16.319371
 100 | 2015-11-09 14:40:16.319373
(84 rows)

postgres=# fetch 100 from c1;
 id | crt_time
----+----------
(0 rows)

postgres=# \h fetch
Command:     FETCH
Description: retrieve rows from a query using a cursor
Syntax:
FETCH [ direction [ FROM | IN ] ] cursor_name

where direction can be empty or one of:

    NEXT
    PRIOR
    FIRST
    LAST
    ABSOLUTE count
    RELATIVE count
    count
    ALL
    FORWARD
    FORWARD count
    FORWARD ALL
    BACKWARD
    BACKWARD count
    BACKWARD ALL

postgres=# fetch backward 100 from c1;
 id  |          crt_time
-----+----------------------------
 100 | 2015-11-09 14:40:16.319373
  99 | 2015-11-09 14:40:16.319371
  98 | 2015-11-09 14:40:16.319368
  97 | 2015-11-09 14:40:16.319366
  96 | 2015-11-09 14:40:16.319363
  95 | 2015-11-09 14:40:16.319361
  94 | 2015-11-09 14:40:16.319358
  93 | 2015-11-09 14:40:16.319356
  92 | 2015-11-09 14:40:16.319353
  91 | 2015-11-09 14:40:16.319351
  90 | 2015-11-09 14:40:16.319348
  89 | 2015-11-09 14:40:16.319346
  88 | 2015-11-09 14:40:16.319343
  87 | 2015-11-09 14:40:16.319341
  86 | 2015-11-09 14:40:16.319339
  85 | 2015-11-09 14:40:16.319336
  84 | 2015-11-09 14:40:16.319334
  83 | 2015-11-09 14:40:16.319331
  82 | 2015-11-09 14:40:16.319329
  81 | 2015-11-09 14:40:16.319326
  80 | 2015-11-09 14:40:16.319324
  79 | 2015-11-09 14:40:16.319321
  78 | 2015-11-09 14:40:16.319319
  77 | 2015-11-09 14:40:16.319316
  76 | 2015-11-09 14:40:16.319314
  75 | 2015-11-09 14:40:16.319311
  74 | 2015-11-09 14:40:16.319309
  73 | 2015-11-09 14:40:16.319306
  72 | 2015-11-09 14:40:16.319303
  71 | 2015-11-09 14:40:16.319287
  70 | 2015-11-09 14:40:16.319284
  69 | 2015-11-09 14:40:16.319282
  68 | 2015-11-09 14:40:16.319279
  67 | 2015-11-09 14:40:16.319277
  66 | 2015-11-09 14:40:16.319274
  65 | 2015-11-09 14:40:16.319272
  64 | 2015-11-09 14:40:16.319269
  63 | 2015-11-09 14:40:16.319267
  62 | 2015-11-09 14:40:16.319264
  61 | 2015-11-09 14:40:16.319262
  60 | 2015-11-09 14:40:16.319259
  59 | 2015-11-09 14:40:16.319257
  58 | 2015-11-09 14:40:16.319255
  57 | 2015-11-09 14:40:16.319252
  56 | 2015-11-09 14:40:16.31925
  55 | 2015-11-09 14:40:16.319247
  54 | 2015-11-09 14:40:16.319245
  53 | 2015-11-09 14:40:16.319242
  52 | 2015-11-09 14:40:16.31924
  51 | 2015-11-09 14:40:16.319237
  50 | 2015-11-09 14:40:16.319235
  49 | 2015-11-09 14:40:16.319232
  48 | 2015-11-09 14:40:16.31923
  47 | 2015-11-09 14:40:16.319227
  46 | 2015-11-09 14:40:16.319225
  45 | 2015-11-09 14:40:16.319222
  44 | 2015-11-09 14:40:16.31922
  43 | 2015-11-09 14:40:16.319218
  42 | 2015-11-09 14:40:16.319215
  41 | 2015-11-09 14:40:16.319213
  40 | 2015-11-09 14:40:16.31921
  39 | 2015-11-09 14:40:16.319208
  38 | 2015-11-09 14:40:16.319205
  37 | 2015-11-09 14:40:16.319203
  36 | 2015-11-09 14:40:16.3192
  35 | 2015-11-09 14:40:16.319198
  34 | 2015-11-09 14:40:16.319195
  33 | 2015-11-09 14:40:16.319193
  32 | 2015-11-09 14:40:16.31919
  31 | 2015-11-09 14:40:16.319188
  30 | 2015-11-09 14:40:16.319185
  29 | 2015-11-09 14:40:16.319183
  28 | 2015-11-09 14:40:16.31918
  27 | 2015-11-09 14:40:16.319178
  26 | 2015-11-09 14:40:16.319175
  25 | 2015-11-09 14:40:16.319173
  24 | 2015-11-09 14:40:16.31917
  23 | 2015-11-09 14:40:16.319168
  22 | 2015-11-09 14:40:16.319165
  21 | 2015-11-09 14:40:16.319163
  20 | 2015-11-09 14:40:16.31916
  19 | 2015-11-09 14:40:16.319158
  18 | 2015-11-09 14:40:16.319155
  17 | 2015-11-09 14:40:16.319153
  16 | 2015-11-09 14:40:16.31915
  15 | 2015-11-09 14:40:16.319126
  14 | 2015-11-09 14:40:16.318767
  13 | 2015-11-09 14:40:16.318765
  12 | 2015-11-09 14:40:16.318763
  11 | 2015-11-09 14:40:16.31876
  10 | 2015-11-09 14:40:16.318757
   9 | 2015-11-09 14:40:16.318755
   8 | 2015-11-09 14:40:16.318753
   7 | 2015-11-09 14:40:16.31875
   6 | 2015-11-09 14:40:16.318748
   5 | 2015-11-09 14:40:16.318745
   4 | 2015-11-09 14:40:16.318743
   3 | 2015-11-09 14:40:16.31874
   2 | 2015-11-09 14:40:16.318733
   1 | 2015-11-09 14:40:16.318608
(100 rows)

postgres=# fetch backward 100 from c1;
 id |          crt_time
----+----------------------------
  1 | 2015-11-09 14:25:04.101895
(1 row)

postgres=# fetch backward 100 from c1;
 id | crt_time
----+----------
(0 rows)

postgres=# fetch 1000 from c1;
 id  |          crt_time
-----+----------------------------
   1 | 2015-11-09 14:25:04.101895
   1 | 2015-11-09 14:40:16.318608
   2 | 2015-11-09 14:40:16.318733
   3 | 2015-11-09 14:40:16.31874
   4 | 2015-11-09 14:40:16.318743
   5 | 2015-11-09 14:40:16.318745
   6 | 2015-11-09 14:40:16.318748
   7 | 2015-11-09 14:40:16.31875
   8 | 2015-11-09 14:40:16.318753
   9 | 2015-11-09 14:40:16.318755
  10 | 2015-11-09 14:40:16.318757
  11 | 2015-11-09 14:40:16.31876
  12 | 2015-11-09 14:40:16.318763
  13 | 2015-11-09 14:40:16.318765
  14 | 2015-11-09 14:40:16.318767
  15 | 2015-11-09 14:40:16.319126
  16 | 2015-11-09 14:40:16.31915
  17 | 2015-11-09 14:40:16.319153
  18 | 2015-11-09 14:40:16.319155
  19 | 2015-11-09 14:40:16.319158
  20 | 2015-11-09 14:40:16.31916
  21 | 2015-11-09 14:40:16.319163
  22 | 2015-11-09 14:40:16.319165
  23 | 2015-11-09 14:40:16.319168
  24 | 2015-11-09 14:40:16.31917
  25 | 2015-11-09 14:40:16.319173
  26 | 2015-11-09 14:40:16.319175
  27 | 2015-11-09 14:40:16.319178
  28 | 2015-11-09 14:40:16.31918
  29 | 2015-11-09 14:40:16.319183
  30 | 2015-11-09 14:40:16.319185
  31 | 2015-11-09 14:40:16.319188
  32 | 2015-11-09 14:40:16.31919
  33 | 2015-11-09 14:40:16.319193
  34 | 2015-11-09 14:40:16.319195
  35 | 2015-11-09 14:40:16.319198
  36 | 2015-11-09 14:40:16.3192
  37 | 2015-11-09 14:40:16.319203
  38 | 2015-11-09 14:40:16.319205
  39 | 2015-11-09 14:40:16.319208
  40 | 2015-11-09 14:40:16.31921
  41 | 2015-11-09 14:40:16.319213
  42 | 2015-11-09 14:40:16.319215
  43 | 2015-11-09 14:40:16.319218
  44 | 2015-11-09 14:40:16.31922
  45 | 2015-11-09 14:40:16.319222
  46 | 2015-11-09 14:40:16.319225
  47 | 2015-11-09 14:40:16.319227
  48 | 2015-11-09 14:40:16.31923
  49 | 2015-11-09 14:40:16.319232
  50 | 2015-11-09 14:40:16.319235
  51 | 2015-11-09 14:40:16.319237
  52 | 2015-11-09 14:40:16.31924
  53 | 2015-11-09 14:40:16.319242
  54 | 2015-11-09 14:40:16.319245
  55 | 2015-11-09 14:40:16.319247
  56 | 2015-11-09 14:40:16.31925
  57 | 2015-11-09 14:40:16.319252
  58 | 2015-11-09 14:40:16.319255
  59 | 2015-11-09 14:40:16.319257
  60 | 2015-11-09 14:40:16.319259
  61 | 2015-11-09 14:40:16.319262
  62 | 2015-11-09 14:40:16.319264
  63 | 2015-11-09 14:40:16.319267
  64 | 2015-11-09 14:40:16.319269
  65 | 2015-11-09 14:40:16.319272
  66 | 2015-11-09 14:40:16.319274
  67 | 2015-11-09 14:40:16.319277
  68 | 2015-11-09 14:40:16.319279
  69 | 2015-11-09 14:40:16.319282
  70 | 2015-11-09 14:40:16.319284
  71 | 2015-11-09 14:40:16.319287
  72 | 2015-11-09 14:40:16.319303
  73 | 2015-11-09 14:40:16.319306
  74 | 2015-11-09 14:40:16.319309
  75 | 2015-11-09 14:40:16.319311
  76 | 2015-11-09 14:40:16.319314
  77 | 2015-11-09 14:40:16.319316
  78 | 2015-11-09 14:40:16.319319
  79 | 2015-11-09 14:40:16.319321
  80 | 2015-11-09 14:40:16.319324
  81 | 2015-11-09 14:40:16.319326
  82 | 2015-11-09 14:40:16.319329
  83 | 2015-11-09 14:40:16.319331
  84 | 2015-11-09 14:40:16.319334
  85 | 2015-11-09 14:40:16.319336
  86 | 2015-11-09 14:40:16.319339
  87 | 2015-11-09 14:40:16.319341
  88 | 2015-11-09 14:40:16.319343
  89 | 2015-11-09 14:40:16.319346
  90 | 2015-11-09 14:40:16.319348
  91 | 2015-11-09 14:40:16.319351
  92 | 2015-11-09 14:40:16.319353
  93 | 2015-11-09 14:40:16.319356
  94 | 2015-11-09 14:40:16.319358
  95 | 2015-11-09 14:40:16.319361
  96 | 2015-11-09 14:40:16.319363
  97 | 2015-11-09 14:40:16.319366
  98 | 2015-11-09 14:40:16.319368
  99 | 2015-11-09 14:40:16.319371
 100 | 2015-11-09 14:40:16.319373
(101 rows)
回滚后可以正常回收垃圾。
postgres=#   rollback;
ROLLBACK

postgres=# vacuum verbose test;
INFO:  vacuuming "public.test"
INFO:  "test": found 1 removable, 101 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

[参考]
1. http://www.postgresql.org/message-id/flat/BLU437-SMTP193E7367B788D2EC2B2B81DC160@phx.gbl#BLU437-SMTP193E7367B788D2EC2B2B81DC160@phx.gbl
2. http://git.postgresql.org/gitweb/?p=postgresql.git;a=snapshot;h=aa7f9493a02f5981c09b924323f0e7a58a32f2ed;sf=tgz
3. http://blog.163.com/digoal@126/blog/static/1638770402015329115636287/

时间： 2024-11-21 02:39:59

PostgreSQL 彻底解决膨胀问题的相关文章

PgSQL · 案例分享 · PostgreSQL+HybridDB解决企业TP+AP混合需求

背景随着IT行业在更多的传统行业渗透,我们正逐步的在进入DT时代,让数据发挥价值是企业的真正需求,否则就是一堆废的并且还持续消耗企业人力,财力的数据. 传统企业可能并不像互联网企业一样,有大量的开发人员.有大量的技术储备,通常还是以购买IT软件,或者以外包的形式在存在. 数据的核心 - 数据库,很多传统的行业还在使用传统的数据库. 但是随着IT向更多行业的渗透,数据类型越来越丰富(诸如人像.X光片.声波.指纹.DNA.化学分子.图谱数据.GIS.三维.多维等等-- ),数据越来越多,怎么处理

数据库选型十八摸之 PostgreSQL - 致架构师、开发者

标签 PostgreSQL , 数据库特性 , 数据库应用场景分析 , 数据库选型背景数据库对于一家企业来说,相比其他基础组件占据比较核心的位置. 有很多企业由于最初数据库选型问题,导致一错再错,甚至还有为此付出沉痛代价的. 数据库的选型一定要慎重,但是这么多数据库,该如何选择呢? 我前段时间写过一篇关于数据库选型的文章,可以参考如下 <数据库选型思考> 另外,PostgreSQL这个数据库这些年的发展非常的迅猛,虽然国内还跟不上国外的节奏,但是相信国人逐渐会融合进去. 所以我专门针对Po

PostgreSQL on ECS多云盘的部署、快照备份和恢复

标签 PostgreSQL , ECS , 云盘 , 快照 , 一致性备份 , 时间点恢复 , zfs , lvm , raid , 并行计算背景随着阿里云云盘的发展,云盘的性能已经越来越好了.IOPS可以做到十万以上,读写吞吐也超过1GB/s了.相信随着网络的发展,SSD云盘IOPS突破40万,读写吞吐突破4GB/s也不远了. 不过这里的IOPS和吞吐是指并发的IO能力,单次IO的延迟与本地还是不能比(将来RDMA网络也许能解决这个问题). PostgreSQL 如何解决SSD云盘IO延迟

视频帧人脸检测 (I)

这个是用前后帧对比的方式进行的人脸检测,因为之前用的视频单帧图片进行人脸检测的效果有限,所以企图能通过其他手段优化视频人脸检测的处理. 我现在的思路就是先尽量精确的检测出人的范围(外接矩形),然后只在这个范围里面人脸检测,这样就可以提高误判率老师要求的时间限制是2周内,这个时间有一点点紧了,虽然这个项目我觉得不难,但是中途遇到的问题是不可预知的... 这段代码存在一个问题,就是用膨胀操作的时候,如果核太大,计算量会陡增,甚至比单帧处理的还慢...所以问题必须解决膨胀问题,不能只用膨胀来形成连通

6月23日直播：《NoSQL、RDS和大数据异构融合实战》

欢迎报名在线培训,专家全程在线答疑:https://yq.aliyun.com/webinar/join/18 ,为你详解PostgreSQL FDW原理--PostgreSQL的外部表功能,当前已经支持包括MongoDB.Redis.MySQL等近30种外部数据源,并分享在金融.物联网等领域的案例. PostgreSQL是有着40年历史以核心交易型事务处理为发展重心的关系型数据库,在NewSQL时代它除了支持JSON.GIS等丰富功能外还提供FDW外部数据通道功能,让NoSQL.Big Dat

阿里数据库内核月报：2017年01月

# 01 MySQL · 引擎特性 · InnoDB 同步机制 # 02 MySQL · myrocks · myrocks index condition pushdown # 03 PgSQL · 案例分享 · PostgreSQL+HybridDB解决企业TP+AP混合需求 # 04 MongoDB · 特性分析 · 网络性能优化 # 05 MySQL · 捉虫动态 · event_scheduler 慢日志记错 # 06 PgSQL · 引擎介绍 · 向量化执行引擎简介 # 07 SQL

由古论今，三千年加密算法发展史

作为一种安全手段,加密算法和我们大多数人的生活发生着紧密联系.只要你上网,都会从中受益,因为凡是使用https的网站都已经使用了加密协议TLS/SSL.因此,我们都在被动享受加密算法带来的隐私保护及通讯安全.此外,从安全技术层面,网络层到主机文件层,无论哪层加密应用或协议背后都是由各种加密算法所支撑. 本文就尝试由古及今,对这种与我们网络生活息息相关的加密算法的发展演变,以及在整个过程中先后出现的集中关键加密算法一一论述,带领大家了解背后这种有趣的. 加密算法主要作用是把明文变成密文,防止信息泄

怎样解决因Seo行业的膨胀带来的外链资源紧缩问题

说seo行业是网络营销的新兴行业未免太过牵强,因为在国内这个行业从零四年以后发展就很迅猛,尤其是当下简直是如日冲天,这主要和seo行业的性质有关,最主要的一点就是它很适合草根创业,也正是这种发展速度致使百分之七十以上的站长都了解seo:都知道外链在当下任是网站优化排名的重要因素(导致很多平台成为链接农场);都知道减少导出链接站内权重的积聚(导致很多外链平台的导出链接受到严格的控制),最终直接导致网站外链资源的紧缩. 为什么说seo行业发展如日冲天 Seo行业发展速度尤为惊人,说seo行业发展

HybridDB for PostgreSQL 列存表(AO表)的膨胀、垃圾检查与空间收缩

标签 PostgreSQL , Greenplum , 垃圾检测 , 膨胀 , 列存表 , gp_appendonly_compaction_threshold 背景 Greenplum支持行存储(堆存储)与AO存储,堆存储的垃圾回收和膨胀检测方法请参考: <如何检测.清理Greenplum膨胀.垃圾 - 阿里云HybridDB for PG最佳实践> 对于AO存储,虽然是appendonly,但实际上GP是支持DELETE和UPDATE的,被删除或更新的行,通过BITMAP来标记. AO存储