当前位置： 首页 > article >正文

PostgreSQL 字段使用pglz压缩测试

article 2025/2/23 18:53:46

PostgreSQL 字段使用pglz压缩测试

测试一：

创建测试表 yewu1.test1，并插入1000w行数据
创建测试表 yewu1.test2，使用 pglz压缩字段，并插入1000w行数据

–创建测试表1，并插入1000w行数据

white=# create table yewu1.test1 (name varchar(20));
CREATE TABLE
white=# 
white=# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test1'::regclass;
 attname  | attcompression 
----------+----------------
 tableoid | 
 cmax     | 
 xmax     | 
 cmin     | 
 xmin     | 
 ctid     | 
 name     | 
(7 rows)
white=# 
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$#     FOR aa IN 1..10000000 LOOP
white$#         INSERT INTO yewu1.test1 VALUES ('white ' || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=# 
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test1')) AS table_size;
 table_size 
------------
 422 MB
(1 row)

–创建测试表2，使用 pglz压缩字段，并插入1000w行数据

white=# 
white=# create table yewu1.test2 (name varchar(20) COMPRESSION pglz);
CREATE TABLE
white=# 
white=# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test2'::regclass;
 attname  | attcompression 
----------+----------------
 tableoid | 
 cmax     | 
 xmax     | 
 cmin     | 
 xmin     | 
 ctid     | 
 name     | p
(7 rows)
white=# 
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$#     FOR aa IN 1..10000000 LOOP
white$#         INSERT INTO yewu1.test2 VALUES ('white ' || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=# 
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test2')) AS table_size;
 table_size 
------------
 422 MB
(1 row)

对比表yewu1.test1和yewu1.test2的大小，没体现出压缩了。

测试二：

创建测试表 yewu1.test3，text数据类型，并插入1000w行数据
创建测试表 yewu1.test4，text数据类型，使用 pglz压缩字段，并插入1000w行数据

–创建测试表3，并插入1000w行数据

white=# create table yewu1.test3 (name text);
CREATE TABLE
white=# 
white=# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test3'::regclass;
 attname  | attcompression 
----------+----------------
 tableoid | 
 cmax     | 
 xmax     | 
 cmin     | 
 xmin     | 
 ctid     | 
 name     | 
(7 rows)

white=# 
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$#     FOR aa IN 1..10000000 LOOP
white$#         INSERT INTO yewu1.test3 VALUES ('white ' || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=# 
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test3')) AS table_size;
 table_size 
------------
 422 MB
(1 row)

–创建测试表4，使用 pglz压缩字段，并插入1000w行数据

white=# create table yewu1.test4 (name text COMPRESSION pglz);
CREATE TABLE
white=# 
white=# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test4'::regclass;
 attname  | attcompression 
----------+----------------
 tableoid | 
 cmax     | 
 xmax     | 
 cmin     | 
 xmin     | 
 ctid     | 
 name     | p
(7 rows)

white=# 
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$#     FOR aa IN 1..10000000 LOOP
white$#         INSERT INTO yewu1.test4 VALUES ('white ' || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=# 
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test4')) AS table_size;
 table_size 
------------
 422 MB
(1 row)

对比表yewu1.test3和yewu1.test4的大小，没体现出压缩了。

测试三：

创建测试表 yewu1.test5，text数据类型，并插入1000w行重复的数据
创建测试表 yewu1.test6，text数据类型，使用 pglz压缩字段，并插入1000w行重复的数据

–创建测试表5，并插入1000w行重复的数据

white=# create table yewu1.test5 (name text);
CREATE TABLE
white=# 
white=# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test5'::regclass;
 attname  | attcompression 
----------+----------------
 tableoid | 
 cmax     | 
 xmax     | 
 cmin     | 
 xmin     | 
 ctid     | 
 name     | 
(7 rows)

white=# 
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$#     FOR aa IN 1..10000000 LOOP
white$#         INSERT INTO yewu1.test5 VALUES ('white12345678');
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=# 
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test5')) AS table_size;
 table_size 
------------
 422 MB
(1 row)

–创建测试表6，使用 pglz压缩字段，并插入1000w行重复的数据

white=# create table yewu1.test6 (name text COMPRESSION pglz);
CREATE TABLE
white=# 
white=# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test6'::regclass;
 attname  | attcompression 
----------+----------------
 tableoid | 
 cmax     | 
 xmax     | 
 cmin     | 
 xmin     | 
 ctid     | 
 name     | p
(7 rows)

white=# 
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$#     FOR aa IN 1..10000000 LOOP
white$#         INSERT INTO yewu1.test6 VALUES ('white12345678');
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=# 
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test6')) AS table_size;
 table_size 
------------
 422 MB
(1 row)

对比表yewu1.test5和yewu1.test6的大小，没体现出压缩了。

测试四：

创建测试表 yewu1.test7，带有主键，text数据类型，并插入1000w行重复的数据
创建测试表 yewu1.test8，带有主键，text数据类型，使用 pglz压缩字段，并插入1000w行重复的数据

–创建测试表7，带有主键，并插入1000w行重复的数据

white=# create table yewu1.test7 (
white(#     id serial primary key,
white(#     name text
white(# );
CREATE TABLE
white=# 
white=# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test7'::regclass;
 attname  | attcompression 
----------+----------------
 tableoid | 
 cmax     | 
 xmax     | 
 cmin     | 
 xmin     | 
 ctid     | 
 id       | 
 name     | 
(8 rows)

white=# 
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$#     FOR aa IN 1..10000000 LOOP
white$#         INSERT INTO yewu1.test7 VALUES (aa,'white' || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=# 
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test7')) AS table_size;
 table_size 
------------
 490 MB
(1 row)

–创建测试表8，带有主键，使用 pglz压缩字段，并插入1000w行重复的数据

white=# create table yewu1.test8 (
white(#     id serial primary key,
white(#     name text COMPRESSION pglz
white(# );
CREATE TABLE
white=# 
white=# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test8'::regclass;
 attname  | attcompression 
----------+----------------
 tableoid | 
 cmax     | 
 xmax     | 
 cmin     | 
 xmin     | 
 ctid     | 
 id       | 
 name     | p
(8 rows)

white=# 
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$#     FOR aa IN 1..10000000 LOOP
white$#         INSERT INTO yewu1.test8 VALUES (aa,'white' || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=# 
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test8')) AS table_size;
 table_size 
------------
 490 MB
(1 row)

对比表yewu1.test7和yewu1.test8的大小，没体现出压缩了。

测试五：

清空测试表 yewu1.test8，并修改字段存储类型为MAIN，再插入1000w行重复的数据

–清空测试表8，并修改字段存储类型为MAIN，再插入1000w行重复的数据

white=# truncate table yewu1.test8;
TRUNCATE TABLE
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test8')) AS table_size;
 table_size 
------------
 8192 bytes
(1 row)

white=# 
white=# SELECT attname, attcompression,attstorage
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test8'::regclass;
 attname  | attcompression | attstorage 
----------+----------------+------------
 tableoid |                | p
 cmax     |                | p
 xmax     |                | p
 cmin     |                | p
 xmin     |                | p
 ctid     |                | p
 id       |                | p
 name     | p              | x
(8 rows)

white=# 
white=# ALTER TABLE yewu1.test8 ALTER COLUMN name SET STORAGE MAIN;
ALTER TABLE
white=# SELECT attname, attcompression,attstorage
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.test8'::regclass;
 attname  | attcompression | attstorage 
----------+----------------+------------
 tableoid |                | p
 cmax     |                | p
 xmax     |                | p
 cmin     |                | p
 xmin     |                | p
 ctid     |                | p
 id       |                | p
 name     | p              | m
(8 rows)

white=# 
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$#     FOR aa IN 1..10000000 LOOP
white$#         INSERT INTO yewu1.test8 VALUES (aa,'white' || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=# 
white=# SELECT pg_size_pretty(pg_table_size('yewu1.test8')) AS table_size;
 table_size 
------------
 490 MB
(1 row)

–未完待续


http://www.kler.cn/a/327189.html

相关文章：

  • OceanBase企业级分布式关系数据库
  • TypeScript 算法手册 - 【冒泡排序】
  • 海陆钻井自动化作业机器人比例阀放大器
  • Apache Solr：深入探索与常见误区解析
  • 深度学习实战：UNet模型的训练与测试详解
  • 关于 JVM 个人 NOTE
  • ARM Assembly: 第8课 branching
  • Web自动化中常用XPath定位方式
  • D23【 python 接口自动化学习】- python 基础之判断与循环
  • Docker入门指南：快速学习Docker的基本操作
  • 网络编程（13）——单例模式
  • BCJR算法——卷积码的最大后验译码
  • Ubuntu 开机自启动 .py / .sh 脚本，可通过脚本启动 roslaunch/roscore等
  • 联邦学习（三只决策和大数据分析）(学习笔记)
  • 【网络安全】TCP和UDP
  • 防止电脑电池老化，禁止usb或者ac接口调试时充电
  • 计算神经学笔记01
  • 后端-对表格数据进行添加、删除和修改
  • 单片机的原理及应用
  • 2024年华为OD机试真题-找终点-Java-OD统一考试（E卷）