PGCOMPACTTABLE DEMO

by bensonyerima

pgcompacttable is a tool for reducing size of bloated tables and indexes without heavy locks.

It is designed to reorganize data in tables and rebuild indexes in order to revert back disk space without database performance impact.

In target database contrib module pgstattuple should be installed via create extension if not exists pgstattuple;

Unlike another popular tool pg_repack this tool has some advantages:

  • does not requires lots of free space
    • tables are processed in-place
    • indexes are rebuild one by one, from smallest to largest therefore maximum space required is the size of the largest index
  • tables are processed with adaptive delays to prevent heavy IO and replication lag spikes (see --delay-ratio option)

FIRST YOU NEED TO GO TO GITHUB AND DOWN LOAD THE zip FILE OF PGCOMPACTTABLE AT
https://github.com/dataegret/pgcompacttable —THUMPS UP TO THESE GUYS

then you will need to unzip the file and cd into the bin directory… see sample below

[root@stage firefox]# su - enterprisedb
Last login: Thu Nov 28 08:52:47 MST 2019 on pts/1
-bash-4.2$ pwd
/opt/edb/as9.6
-bash-4.2$ 
-bash-4.2$ ll
drwxr-xr-x.  6 root         daemon          4096 Nov 15 21:26 include
drwxr-xr-x.  6 root         daemon            75 Nov  9 18:51 installer
drwxr-xr-x.  4 root         daemon          8192 Nov 15 21:26 lib
-rw-------.  1 enterprisedb enterprisedb    4445 Nov  9 21:03 pg_hba.conf
-rw-------.  1 enterprisedb enterprisedb    4480 Nov  9 21:04 pgold
-rwxr-xr-x.  1 root         daemon           939 Nov 15 21:26 pgplus_env.sh
-rw-rw-r--.  1 enterprisedb enterprisedb   95701 Nov 28 08:49 pgtoolkit-master.zip    
-bash-4.2$ 
-bash-4.2$ 
-bash-4.2$ unzip pgtoolkit-master.zip  

-bash-4.2$ ll
-rwxr-xr-x.  1 root         daemon           939 Nov 15 21:26 pgplus_env.sh
drwxrwxr-x.  7 enterprisedb enterprisedb     189 Jul 23  2015 pgtoolkit-master     
-rw-rw-r--.  1 enterprisedb enterprisedb   95701 Nov 28 08:49 pgtoolkit-master.zip
drwxr-xr-x.  6 root         daemon           215 Nov  9 18:51 scripts
-bash-4.2$ cd pgtoolkit-master    
-bash-4.2$ ll
total 36
drwxrwxr-x. 2 enterprisedb enterprisedb   23 Jul 23  2015 bin
-rw-rw-r--. 1 enterprisedb enterprisedb 6240 Jul 23  2015 CHANGES.md
-rwxr-xr-x. 1 enterprisedb enterprisedb  132 Jul 23  2015 environment
drwxrwxr-x. 2 enterprisedb enterprisedb   23 Jul 23  2015 fatpack
drwxrwxr-x. 3 enterprisedb enterprisedb   23 Jul 23  2015 lib
-rw-rw-r--. 1 enterprisedb enterprisedb 1036 Jul 23  2015 LICENSE.md
-rwxr-xr-x. 1 enterprisedb enterprisedb  664 Jul 23  2015 make_fatpack
drwxrwxr-x. 2 enterprisedb enterprisedb   25 Jul 23  2015 misc
-rwxr-xr-x. 1 enterprisedb enterprisedb   96 Jul 23  2015 pre-commit
-rw-rw-r--. 1 enterprisedb enterprisedb 4783 Jul 23  2015 README.md
drwxrwxr-x. 3 enterprisedb enterprisedb   17 Jul 23  2015 t
-rw-rw-r--. 1 enterprisedb enterprisedb 1580 Jul 23  2015 TODO.md
-bash-4.2$ cat README.md

-bash-4.2$ cd bin     
-bash-4.2$ ll
total 8
-rwxr-xr-x. 1 enterprisedb enterprisedb 6879 Jul 23  2015 pgcompact
-bash-4.2$ 
-bash-4.2$./pgcompact --help    
-bash-4.2$./pgcompact --man

PRACTICAL EXAMPLE…

LETS CREATE A TABLE AND GET SOME DEAD TUPPLE IN THERE.

edb=# create table peter as select * from generate_series(1,10000000) as ID;
SELECT 10000000
edb=# \dt+
                      List of relations
 Schema | Name  | Type  |    Owner     |  Size  | Description 
--------+-------+-------+--------------+--------+-------------
 public | peter | table | enterprisedb | 346 MB | 
(1 row)

edb=# insert into peter select * from generate_series(10000013,10000020);
INSERT 0 8
edb=# insert into peter select * from generate_series(10000020,10000040);
INSERT 0 21
edb=# \dt+

edb=# select * from pgstattuple('peter');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 362479616 |    10000042 | 280001176 |         77.25 |                0 |              0 |                  0 |    1239160 |         0.34
(1 row)

AS SEEN ABOVE, WE DO NOT HAVE ANY DEAD TUPLES YET….SO LETS DELETE TO CREATE DEAD TUPPLES

edb=# delete from peter where ID='10000000';
DELETE 1
edb=# select * from pgstattuple('peter');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 362479616 |    10000041 | 280001148 |         77.25 |                1 |             28 |                  0 |    1239160 |         0.34
(1 row)

OK NOW WE HAVE DEAD TUPPLES…

-bash-4.2$ cd /opt/edb/as9.6/pgtoolkit-master
-bash-4.2$ pwd
/opt/edb/as9.6/pgtoolkit-master
-bash-4.2$ ll
total 36
drwxrwxr-x. 2 enterprisedb enterprisedb   23 Jul 23  2015 bin
-rw-rw-r--. 1 enterprisedb enterprisedb 6240 Jul 23  2015 CHANGES.md
-rwxr-xr-x. 1 enterprisedb enterprisedb  132 Jul 23  2015 environment
drwxrwxr-x. 2 enterprisedb enterprisedb   23 Jul 23  2015 fatpack
drwxrwxr-x. 3 enterprisedb enterprisedb   23 Jul 23  2015 lib
-rw-rw-r--. 1 enterprisedb enterprisedb 1036 Jul 23  2015 LICENSE.md
-rwxr-xr-x. 1 enterprisedb enterprisedb  664 Jul 23  2015 make_fatpack
drwxrwxr-x. 2 enterprisedb enterprisedb   25 Jul 23  2015 misc
-rwxr-xr-x. 1 enterprisedb enterprisedb   96 Jul 23  2015 pre-commit
-rw-rw-r--. 1 enterprisedb enterprisedb 4783 Jul 23  2015 README.md
drwxrwxr-x. 3 enterprisedb enterprisedb   17 Jul 23  2015 t
-rw-rw-r--. 1 enterprisedb enterprisedb 1580 Jul 23  2015 TODO.md
-bash-4.2$ cd bin
-bash-4.2$ ll
total 8
-rwxr-xr-x. 1 enterprisedb enterprisedb 6879 Jul 23  2015 pgcompact

LETS RUN DRY RUN TO SEE THE INFORMATION WE WILL HAVE ABOUT THE TABLE AND HOW MUCH SIZE WE CAN GAIN

sh-4.2$ ./pgcompact -p 5444 -d edb -t peter --dry-run 
Argument "EnterpriseDB 9" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Argument "22 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Argument "7 20120313 (Red Hat 4" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Argument "7-23), 64-bit" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Thu Nov 28 11:30:36 2019 edb, public.peter NOTICE Statistics: 44249 pages (44262 pages including toasts and indexes), approximately 0.34% (152 pages) can be compacted reducing the size by 1217 kB.
-bash-4.2$

THE LAST LINE TELL US SOME GOOD INFORMATION ABOUT THAT TABLE ‘peter . pgcompacttable –dbname billing -t operations -f Force compact table operations in database billing.
Notice, tables and indexes with bloat less than 20% (hardcoded MINIMAL_COMPACT_PERCENT constant) are considered normal and not processed until option –force is taken.

So pgcompact is good for running on large tables. we are going to run on the small table we have and you will see that it wont remove dead tupples

-bash-4.2$ ./pgcompact -p 5444 -d edb -t peter  <=====compact peter

Argument "EnterpriseDB 9" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Argument "22 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Argument "7 20120313 (Red Hat 4" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Argument "7-23), 64-bit" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Thu Nov 28 11:40:56 2019 edb, public.peter NOTICE Statistics: 44249 pages (44264 pages including toasts and indexes), approximately 0.34% (152 pages) can be compacted reducing the size by 1217 kB.
Thu Nov 28 11:40:56 2019 edb NOTICE Processing complete.
Thu Nov 28 11:40:56 2019 edb NOTICE Processing results: size reduced by 0 bytes (-16 kB including toasts and indexes) in total.
Thu Nov 28 11:40:56 2019 NOTICE Processing complete: 0 retries from 10.
Thu Nov 28 11:40:56 2019 NOTICE Processing results: size reduced by 0 bytes (-16 kB including toasts and indexes) in total, 0 bytes (-16 kB) edb.

The above shows that the deat tupple have not been removed. So we need to use the –force option to remove that…

-bash-4.2$ ./pgcompact -p 5444 -d edb -t peter --force    
Argument "EnterpriseDB 9" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Argument "22 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Argument "7 20120313 (Red Hat 4" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Argument "7-23), 64-bit" isn't numeric in pack at /opt/edb/as9.6/pgtoolkit-master/lib/PgToolkit/Utils.pm line 205.
Thu Nov 28 12:00:53 2019 edb, public.peter NOTICE Statistics: 44249 pages (44264 pages including toasts and indexes), approximately 0.34% (152 pages) can be compacted reducing the size by 1217 kB.
Thu Nov 28 12:00:53 2019 edb, public.peter NOTICE Processing forced.
Thu Nov 28 12:00:54 2019 edb, public.peter NOTICE Processing complete.
Thu Nov 28 12:00:54 2019 edb, public.peter NOTICE Processing results: 44258 pages left (44273 pages including toasts and indexes), size reduced by -72 kB (-72 kB including toasts and indexes) in total.
Thu Nov 28 12:00:54 2019 edb NOTICE Processing complete.
Thu Nov 28 12:00:54 2019 edb NOTICE Processing results: size reduced by -72 kB (-72 kB including toasts and indexes) in total.
Thu Nov 28 12:00:54 2019 NOTICE Processing complete: 0 retries from 10.
Thu Nov 28 12:00:54 2019 NOTICE Processing results: size reduced by -72 kB (-72 kB including toasts and indexes) in total, -72 kB (-72 kB) edb.
-bash-4.2$

As seen above it has reduced size by -72 kb which is good..

edb=# select * from pgstattuple('peter');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 362561536 |    10000074 | 280002072 |         77.23 |                0 |              0 |                  0 |    1311384 |         0.36
(1 row)

edb=#

Now we have no dead tupple

About the author

bensonyerima

Hi, I'm Benson Yerima, a database administrator with an obsession for all things tech. This blog is dedicated for helping people learn about database technology.

View all posts

10,173 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *