作者简介
Hans-Jürgen Schönig从90年代开始接触PostgreSQL。他是CYBERTEC的CEO以及技术主管,CYBERTEC是该领域的市场领导者之一,自2000年以来已为全球无数客户提供服务。
译者简介
陈雁飞,开源PostgreSQL 爱好者,一直从事PostgreSQL数据库运维工作。
校对者简介
崔鹏,任职于海能达通信股份有限公司,数据库开发高级工程师,致力于postgresql数据库在专网通信领域、公共安全领域的应用与推广。
分别创建基于PostgreSQL heap和zheap的样本数据
test=# SET temp_buffers TO '1 GB';
SET
test=# CREATE TEMP TABLE raw AS
SELECT id,
hashtext(id::text) as name,
random() * 10000 AS n, true AS b
FROM generate_series(1, 10000000) AS id;
SELECT 10000000
test=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-----------+------+-------+-------+-------------+--------+-------------
pg_temp_5 | raw | table | hs | temporary | 498 MB |
(1 row)
填充Zheap表
test=# \timing
Timing is on.
test=# CREATE TABLE h1 (LIKE raw) USING heap;
CREATE TABLE
Time: 7.836 ms
test=# INSERT INTO h1 SELECT * FROM raw;
INSERT 0 10000000
Time: 7495.798 ms (00:07.496)
test=# CREATE TABLE z1 (LIKE raw) USING zheap;
CREATE TABLE
Time: 8.045 ms
test=# INSERT INTO z1 SELECT * FROM raw;
INSERT 0 10000000
Time: 27947.516 ms (00:27.948)
test=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-----------+------+-------+-------+-------------+--------+-------------
pg_temp_5 | raw | table | hs | temporary | 498 MB |
public | h1 | table | hs | permanent | 498 MB |
public | z1 | table | hs | permanent | 251 MB |
(3 rows)
更新操作以及zheap空间消耗情况
test=# BEGIN;
BEGIN
test=*# SELECT pg_size_pretty(pg_relation_size('z1'));
pg_size_pretty
----------------
251 MB
(1 row)
test=*# UPDATE z1 SET id = id + 1;
UPDATE 10000000
test=*# SELECT pg_size_pretty(pg_relation_size('z1'));
pg_size_pretty
----------------
251 MB
(1 row)
一句话关于回滚
test=# BEGIN;
BEGIN
Time: 0.309 ms
test=*# UPDATE h1 SET id = id - 1 WHERE id < 100000;
UPDATE 99999
Time: 741.518 ms
test=*# ROLLBACK;
ROLLBACK
Time: 0.181 ms
test=# BEGIN;
BEGIN
Time: 0.151 ms
test=*# UPDATE z1 SET id = id - 1 WHERE id < 100000;
UPDATE 99998
Time: 1066.378 ms (00:01.066)
test=*# ROLLBACK;
ROLLBACK
Time: 41.539 ms
Zheap:全力以赴
结束语
PostgreSQL中文社区欢迎广大技术人员投稿
投稿邮箱:press@postgres.cn
文章转载自PostgreSQL中文社区,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。