Oracle vs PostgreSQL -Research precations(3)
Oracle transaction rollback, by the rollback segments, to save original data implementation, but, the PG does not have the rollback segments! for example by an update operation, to explain that PG exists a space skyrocket problems on the implementing mechanism.
When executing Update command, Oracle local update, if original block space appears not enough space situation, by a link way links on the new blocks(inaccurate, general expression); PG’s update, that not a local update, just reserved original data, by a newly added tuple(data line) to save new data, original data clean by Vacuum mechanism. Vacuum mechanism need to satisfy MVCC(multiple version concurrent control) requests, and on the other situation, that wouldn’t clean “rubbish” data, would be resulting in data tablespace constantly growing when transaction busy time.
--------------------------- Session A
-- start transaction
begin;
-- inquiry current transaction
select txid_current();
txid_current
--------------
1500987
(1 row)
-- nothing to do, would be resulting in Vacuum couldn't clean "rubbish" data
--------------------------- Session B
-- start transaction
begin;
select txid_current();
txid_current
--------------
1500988
(1 row)
-- create table&insert100w data
drop table if exists t1;
create table t1(id int,c1 varchar(50));
insert into t1 select generate_series(1,100),'#TESTDATA#';
------------------- omit output above operation
select txid_current();
txid_current
--------------
1500988
(1 row)
-- commit transaction
end;
-- check data table
select ctid, xmin, xmax, cmin, cmax,id from t1;
testdb=# select ctid, xmin, xmax, cmin, cmax,id from t1;
ctid | xmin | xmax | cmin | cmax | id
---------+---------+------+------+------+-----
(0,1) | 1500988 | 0 | 4 | 4 | 1
(0,2) | 1500988 | 0 | 4 | 4 | 2
(0,3) | 1500988 | 0 | 4 | 4 | 3
(0,4) | 1500988 | 0 | 4 | 4 | 4
......
-- check data occupy spaces
\set v_tablename t1
SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
pg_size_pretty
----------------
8192 bytes
(1 row)
-- use pgbench go on pression test, constently update data
cat update.sql
\set rowid random(1,100)
begin;
update t1 set c1=c1||:rowid where id= :rowid;
end;
pgbench -c 2 -C -f ./update.sql -j 1 -n -T 600 -U xdb testdb
-- to check data occupy spaces after a while
SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
pg_size_pretty
----------------
1344 kB
(1 row)
From original 8192 Bytes becomes to 1344KB, the spaces have “skyrocketed”.
Investigate the reason, that because of resulted in PG’s implementing mechanism: if it existed some transaction, start on the update data before, then when update data before and after that data need to store, whatever how many times update data all need to store.
推荐阅读
-
Oracle vs PostgreSQL,研发注意事项(10)- PostgreSQL数据类型转换规则#2
-
Oracle vs PostgreSQL,研发注意事项(5)- 字符类型
-
Oracle vs PostgreSQL DBA(13)- 拆分(split)分区
-
PostgreSQL vs Oracle checksum 配置与性能
-
Oracle vs PostgreSQL Develop(15) - DISTINCT ON
-
Oracle vs PostgreSQL,研发注意事项(9)- PostgreSQL数据类型转换规则#1
-
Oracle vs PostgreSQL,研发注意事项(7)- 类型转换
-
Oracle vs PostgreSQL,研发注意事项(11)- PostgreSQL数据类型转换规则#3
-
Oracle vs PostgreSQL Develop(16) - Prepared Statement
-
Oracle vs PostgreSQL Develop(17) - ARRAY