pg_squeeze

os: ubuntu 16.04
db: postgresql 10.6

pg_squeeze is an extension that removes unused space from a table and
optionally sorts tuples according to particular index (as if CLUSTER [2]
command was executed concurrently with regular reads / writes). In fact we try
to replace pg_repack [1] extension.

First, make sure that your table has either primary key or unique
constraint. This is necessary to process changes other transactions might do
while “pg_squeeze” is doing its work.

版本

# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 16.04.5 LTS
Release:	16.04
Codename:	xenial
#
# su - postgres
$ psql -c "select version();"
                                                     version                                                     
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

下载安装

# su - postgres
$ git clone https://github.com/cybertec-postgresql/pg_squeeze.git
$ cd pg_squeeze
$ git branch -a
$ git checkout VERSION3_1_15

1. Set PG_CONFIG environment variable to point to pg_config command of your PostgreSQL installation.

$ cd ~/pg_squeeze
$ vi Makefile

PG_CONFIG=/usr/pgsql-10/bin/pg_config

2. make
3. sudo make install

$ make all
$ make install
/bin/mkdir -p '/usr/pgsql-10/lib'
/bin/mkdir -p '/usr/pgsql-10/share/extension'
/bin/mkdir -p '/usr/pgsql-10/share/extension'
/usr/bin/install -c -m 755  pg_squeeze.so '/usr/pgsql-10/lib/pg_squeeze.so'
/usr/bin/install -c -m 644 .//pg_squeeze.control '/usr/pgsql-10/share/extension/'
/usr/bin/install -c -m 644 .//pg_squeeze--1.0.sql  '/usr/pgsql-10/share/extension/'

$ ls -l /usr/pgsql-10/lib/* |grep -i pg_squeeze;
-rwxr-xr-x 1 postgres postgres  298520 Apr  8 11:39 /usr/pgsql-10/lib/pg_squeeze.so

$ ls -l /usr/pgsql-10/share/extension/* |grep -i pg_squeeze;
-rw-r--r-- 1 postgres postgres 18391 Apr  8 11:39 /usr/pgsql-10/share/extension/pg_squeeze--1.0.sql
-rw-r--r-- 1 postgres postgres   184 Apr  8 11:39 /usr/pgsql-10/share/extension/pg_squeeze.control

4. Apply the following settings to postgresql.conf:
   wal_level = logical
   max_replication_slots = 10 # … or add 1 to the current value.
   shared_preload_libraries = ‘pg_squeeze’ # … or add the library to the existing ones.

postgres=# show wal_level;show max_replication_slots;show shared_preload_libraries;
 wal_level 
-----------
 logical
(1 row)

 max_replication_slots 
-----------------------
 10
(1 row)

                    shared_preload_libraries                     
-----------------------------------------------------------------
 
(0 row)

$ vi $PGDATA/postgresql.conf
wal_level = logical
max_replication_slots = 10 #
shared_preload_libraries = 'pg_squeeze'

5. Start the PG cluster.

$ pg_ctl stop -m fast -D /data/pgsql-10/data
$ pg_ctl start -D /data/pgsql-10/data

6. As a superuser, run

postgres=# CREATE EXTENSION pg_squeeze;
CREATE EXTENSION


postgres=# \dx
                                        List of installed extensions
        Name        | Version |   Schema   |                           Description                           
--------------------+---------+------------+-----------------------------------------------------------------
 pg_squeeze         | 1.0     | squeeze    | A tool to remove unused space from a relation.
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# \dn
    List of schemas
    Name    |  Owner   
------------+----------
 public     | postgres
 squeeze    | postgres
(2 rows)

使用

准备基础数据:创建 public.tmp_t0,插入 100w 条数据

postgres=# create table tmp_t0( c0 int8 primary key,c1 varchar(100),c2 varchar(100));
CREATE TABLE
postgres=#
postgres=# insert into tmp_t0 select id,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,1000000) as id;
INSERT 0 1000000
postgres=#
postgres=# \dt+ tmp_t0
                     List of relations
 Schema |  Name  | Type  |  Owner   |  Size  | Description 
--------+--------+-------+----------+--------+-------------
 public | tmp_t0 | table | postgres | 104 MB | 
(1 row)

插入定期清理数据: 定期清理public.tmp_t0表，1分钟一次，

postgres=# insert into squeeze.tables (tabschema,tabname,first_check,free_space_extra,task_interval) 
values('public','tmp_t0',now(),20,interval '1 minutes');

表示空闲空间超过 20%就会对表进行重建.

手动启动pg_squeeze的进程需要调用

postgres=# SELECT squeeze.start_worker();

不需要时关闭pg_squeeze的进程调用

postgres=# SELECT squeeze.stop_worker();

查看当前表的膨胀情况及此时表的大小

postgres=# select * from squeeze.tables_internal;
 table_id | class_id | class_id_toast |    free_space     | last_task_created | last_task_finished 
----------+----------+----------------+-------------------+-------------------+--------------------
        1 |    57797 |              0 | 0.784470774697771 |                   | 
(1 row)

postgres=# \dt+ tmp_t0
                     List of relations
 Schema |  Name  | Type  |  Owner   |  Size  | Description 
--------+--------+-------+----------+--------+-------------
 public | tmp_t0 | table | postgres | 103 MB | 
(1 row)

删除 50w 数据

postgres=# delete from tmp_t0 where c0 <=500000;
DELETE 500000

稍等一会,查看当前表的膨胀情况 及 public.tmp_t0 的大小

postgres=# select * from squeeze.tables_internal;
 table_id | class_id | class_id_toast | free_space |       last_task_created       |      last_task_finished      
----------+----------+----------------+------------+-------------------------------+------------------------------
       11 |          |                |            | 2019-04-09 08:34:31.563214+08 | 2019-04-09 08:34:31.65691+08
(1 row)

Time: 0.528 ms

postgres=# \dt+ tmp_t0
                    List of relations
 Schema |  Name  | Type  |  Owner   | Size  | Description 
--------+--------+-------+----------+-------+-------------
 public | tmp_t0 | table | postgres | 52 MB | 
(1 row)

参考:
https://github.com/cybertec-postgresql/pg_squeeze
https://www.cybertec-postgresql.com/en/products/pg_squeeze/