Enhancement of Astore Row-Level Compression

Availability

This feature is available since MogDB 3.1.0.

Introduction

MogDB implements the row-level compression feature of Astore row-store tables, which saves disk space and improves storage efficiency by automatically compressing data in compressed tables, while ensuring data integrity and accessibility. This makes MogDB an efficient database management system for scenarios that store large amounts of data.

Benefits

Astore row-level compression can save organizations a significant amount of storage space, reducing the storage space occupied by the database by 50% to 76%. Such storage space savings are very valuable to enterprises, reducing storage costs, improving storage efficiency, and helping them meet the challenges of explosive data growth. Meanwhile, in peak business scenarios, compressed tables outperform uncompressed tables by 7.3%, which further proves the advantages of compressed tables. In addition to saving storage space, compressed tables can also improve query performance and reduce disk IO operations, thus providing enterprises with higher efficiency and responsiveness.

Description

Astore row-level compression supports the following features:

• Creation and modification of compression tables;
• Automatic completion of compression or decompression operations during read and write operations on compression tables;
• Support for compression table primary and standby synchronization;
• Support for compression table expired version recovery;
• Supports the automatic completion of compression when data is imported into the compression table;
• Supports displaying the compression of all compression tables of the system through the GS_COMPRESSION view;
• Compatible with first-level partition table, including all functions of moving, migrating, merging, updating, splitting, adding, deleting, truncating partitions;
• Compatible with secondary partitioned tables. The operations related to secondary sub-partitions include adding, deleting, splitting, truncating partitions;
• Support the creation of compressed tables in segmented page mode, and segmented page compressed table additions, deletions, modifications, checks and other features can be executed normally;
• Compressed tables are compatible with the tools already released by MogDB;
• add compress page process in autovacuum thread to reduce the number of disk IO times, locking times, so as to reduce the background compression overhead;

At the same time, Astore row-level compression optimizes the relevant compression algorithms; table compression effect is transparent to the user.

Constrains

• Only works for Astore rowstore tables, not for Ustore rowstore tables, columnstore tables, and MOTs;

• Creates uncompressed tables by default;

• Cannot specify compression attributes for system tables;

• Cannot specify compression attributes for table tables;

• The tablespace compression attribute is not supported;

• Partition compression tables. compression is performed only if the amount of data in a single partition is greater than 128MB;

• Ordinary vacuum command, will not execute compression; vacuum full command will execute compression;

• The space saved by the background compression will not be immediately reacted to the space occupied by the disk, the subsequent data insertion will reuse the space saved by the compression;

• Version 3.0 segment-page compression tables are not supported for upgrading to version 5.0. If there is a segmented compressed table in version 3.0, before upgrading, please import the data in the segmented compressed table to a non-compressed table for backup, then delete the segmented compressed table, re-create the segmented compressed table after upgrading, and import the backup data to the newly created segmented compressed table;

Example

1. Create compressed and uncompressed tables.

   MogDB=# CREATE TABLE tb_mogdb_compress (id INT, name TEXT, addr TEXT, info TEXT) WITH (compression = yes);
   CREATE TABLE
   MogDB=# CREATE TABLE tb_mogdb_no_compress (id INT, name TEXT, addr TEXT, info TEXT);
   CREATE TABLE

2. Insert random data.

   MogDB=# INSERT INTO tb_mogdb_compress VALUES (generate_series(0, 1999999), 'fasdfasdhigasidfdfhgioashdfgohaosdgh', 'fasdfasdfasdahasdhsfsdgstyjdth', 'fasdhgsoidfhisdifgiosdfiogio');
   INSERT 0 2000000
   MogDB=# INSERT INTO tb_mogdb_no_compress VALUES (generate_series(0, 1999999), 'fasdfasdhigasidfdfhgioashdfgohaosdgh', 'fasdfasdfasdahasdhsfsdgstyjdth', 'fasdhgsoidfhisdifgiosdfiogio');
   INSERT 0 2000000

3. Executing vacuum full immediately triggers the compression command.

   MogDB=# vacuum full tb_mogdb_compress;

   Note: Generally in practice, you can set the time interval between two background compression operations to trigger compression by the parameter autocmpr_naptime. For details, please refer to autocmpr_naptime.

4. View the size occupied by compressed and uncompressed tables.

   MogDB=# \d+
                                                List of relations
    Schema |         Name         | Type  | Owner  |  Size  |              Storage              | Description 
   --------+----------------------+-------+--------+--------+-----------------------------------+-------------
    public | tb_mogdb_compress    | table | yaojun | 105 MB | {orientation=row,compression=yes} | 
    public | tb_mogdb_no_compress | table | yaojun | 256 MB | {orientation=row,compression=no}  | 
   (2 rows)

Related Pages

CREATE TABLE, ALTER TABLE, VACUUM, COPY, GS_COMPRESSION