Saturday, May 27, 2023

pgstattuple - for tuple-level statistics & removing bloat by pg_repack

 pgstattuple module/extension provides various functions to obtain tuple-level statistics, however its not installed by default.

postgres=#  select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;
ERROR:  function pgstattuple(unknown) does not exist
LINE 1: ...(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattupl...
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

To create extension, you need to install 

[root@linuxpg1 ~]# yum install postgresql14-contrib

Loaded plugins: langpacks, ulninfo

ol7_UEKR5                                                | 3.0 kB     00:00

ol7_latest                                               | 3.6 kB     00:00

pgdg-common/7Server/x86_64/signature                     |  198 B     00:00

pgdg-common/7Server/x86_64/signature                     | 2.9 kB     00:00 !!!

pgdg11/7Server/x86_64/signature                          |  198 B     00:00

pgdg11/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg12/7Server/x86_64/signature                          |  198 B     00:00

pgdg12/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg13/7Server/x86_64/signature                          |  198 B     00:00

pgdg13/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg14/7Server/x86_64/signature                          |  198 B     00:00

pgdg14/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg15/7Server/x86_64/signature                          |  198 B     00:00

pgdg15/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

(1/6): pgdg13/7Server/x86_64/primary_db                    | 287 kB   00:03

(2/6): pgdg-common/7Server/x86_64/primary_db               | 178 kB   00:03

(3/6): pgdg15/7Server/x86_64/primary_db                    | 106 kB   00:00

(4/6): pgdg14/7Server/x86_64/primary_db                    | 193 kB   00:04

(5/6): pgdg12/7Server/x86_64/primary_db                    | 373 kB   00:05

(6/6): pgdg11/7Server/x86_64/primary_db                    | 487 kB   00:11

Resolving Dependencies

--> Running transaction check

---> Package postgresql14-contrib.x86_64 0:14.8-1PGDG.rhel7 will be installed

--> Processing Dependency: postgresql14-server(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64

--> Processing Dependency: postgresql14-libs(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64

--> Processing Dependency: postgresql14(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64

--> Processing Dependency: libpython3.6m.so.1.0()(64bit) for package: postgresql 14-contrib-14.8-1PGDG.rhel7.x86_64

--> Running transaction check

---> Package postgresql14.x86_64 0:14.7-1PGDG.rhel7 will be updated

---> Package postgresql14.x86_64 0:14.8-1PGDG.rhel7 will be an update

---> Package postgresql14-libs.x86_64 0:14.7-1PGDG.rhel7 will be updated

---> Package postgresql14-libs.x86_64 0:14.8-1PGDG.rhel7 will be an update

---> Package postgresql14-server.x86_64 0:14.7-1PGDG.rhel7 will be updated

---> Package postgresql14-server.x86_64 0:14.8-1PGDG.rhel7 will be an update

---> Package python3-libs.x86_64 0:3.6.8-18.0.5.el7 will be installed

--> Processing Dependency: python(abi) = 3.6 for package: python3-libs-3.6.8-18.0.5.el7.x86_64

--> Running transaction check

---> Package python3.x86_64 0:3.6.8-18.0.5.el7 will be installed

--> Processing Dependency: python3-pip for package: python3-3.6.8-18.0.5.el7.x86_64

--> Processing Dependency: python3-setuptools for package: python3-3.6.8-18.0.5.el7.x86_64

--> Running transaction check

---> Package python3-pip.noarch 0:9.0.3-8.0.3.el7 will be installed

---> Package python3-setuptools.noarch 0:39.2.0-10.0.1.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

================================================================================

Package                  Arch       Version               Repository      Size

================================================================================

Installing:

 postgresql14-contrib     x86_64     14.8-1PGDG.rhel7      pgdg14         688 k

Installing for dependencies:

 python3                  x86_64     3.6.8-18.0.5.el7      ol7_latest      70 k

 python3-libs             x86_64     3.6.8-18.0.5.el7      ol7_latest     6.9 M

 python3-pip              noarch     9.0.3-8.0.3.el7       ol7_latest     1.6 M

 python3-setuptools       noarch     39.2.0-10.0.1.el7     ol7_latest     628 k

Updating for dependencies:

 postgresql14             x86_64     14.8-1PGDG.rhel7      pgdg14         1.5 M

 postgresql14-libs        x86_64     14.8-1PGDG.rhel7      pgdg14         271 k

 postgresql14-server      x86_64     14.8-1PGDG.rhel7      pgdg14         5.4 M

Transaction Summary

================================================================================

Install  1 Package  (+4 Dependent packages)

Upgrade             ( 3 Dependent packages)


Total size: 17 M

Total download size: 9.9 M

Is this ok [y/d/N]: y

Downloading packages:

(1/5): python3-3.6.8-18.0.5.el7.x86_64.rpm                 |  70 kB   00:01

(2/5): python3-pip-9.0.3-8.0.3.el7.noarch.rpm              | 1.6 MB   00:00

(3/5): python3-setuptools-39.2.0-10.0.1.el7.noarch.rpm     | 628 kB   00:00

(4/5): python3-libs-3.6.8-18.0.5.el7.x86_64.rpm            | 6.9 MB   00:06

(5/5): postgresql14-contrib-14.8-1PGDG.rhel7.x86_64.rpm    | 688 kB   00:08

--------------------------------------------------------------------------------

Total                                              1.1 MB/s | 9.9 MB  00:08

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : python3-libs-3.6.8-18.0.5.el7.x86_64                        1/11

  Installing : python3-pip-9.0.3-8.0.3.el7.noarch                          2/11

  Installing : python3-setuptools-39.2.0-10.0.1.el7.noarch                 3/11

  Installing : python3-3.6.8-18.0.5.el7.x86_64                             4/11

  Updating   : postgresql14-libs-14.8-1PGDG.rhel7.x86_64                   5/11

  Updating   : postgresql14-14.8-1PGDG.rhel7.x86_64                        6/11

  Updating   : postgresql14-server-14.8-1PGDG.rhel7.x86_64                 7/11

  Installing : postgresql14-contrib-14.8-1PGDG.rhel7.x86_64                8/11

  Cleanup    : postgresql14-server-14.7-1PGDG.rhel7.x86_64                 9/11

  Cleanup    : postgresql14-14.7-1PGDG.rhel7.x86_64                       10/11

  Cleanup    : postgresql14-libs-14.7-1PGDG.rhel7.x86_64                  11/11

  Verifying  : python3-3.6.8-18.0.5.el7.x86_64                             1/11

  Verifying  : postgresql14-libs-14.8-1PGDG.rhel7.x86_64                   2/11

  Verifying  : python3-pip-9.0.3-8.0.3.el7.noarch                          3/11

  Verifying  : python3-libs-3.6.8-18.0.5.el7.x86_64                        4/11

  Verifying  : postgresql14-14.8-1PGDG.rhel7.x86_64                        5/11

  Verifying  : python3-setuptools-39.2.0-10.0.1.el7.noarch                 6/11

  Verifying  : postgresql14-contrib-14.8-1PGDG.rhel7.x86_64                7/11

  Verifying  : postgresql14-server-14.8-1PGDG.rhel7.x86_64                 8/11

  Verifying  : postgresql14-server-14.7-1PGDG.rhel7.x86_64                 9/11

  Verifying  : postgresql14-14.7-1PGDG.rhel7.x86_64                       10/11

  Verifying  : postgresql14-libs-14.7-1PGDG.rhel7.x86_64                  11/11


Installed:

  postgresql14-contrib.x86_64 0:14.8-1PGDG.rhel7


Dependency Installed:

  python3.x86_64 0:3.6.8-18.0.5.el7

  python3-libs.x86_64 0:3.6.8-18.0.5.el7

  python3-pip.noarch 0:9.0.3-8.0.3.el7

  python3-setuptools.noarch 0:39.2.0-10.0.1.el7


Dependency Updated:

  postgresql14.x86_64 0:14.8-1PGDG.rhel7

  postgresql14-libs.x86_64 0:14.8-1PGDG.rhel7

  postgresql14-server.x86_64 0:14.8-1PGDG.rhel7


Complete!

[root@linuxpg1 ~]# su - postgres

Last login: Sat May 27 17:22:44 CDT 2023 on pts/1

ps-bash-4.2$ psql

psql (14.8)

Type "help" for help.


postgres=# create extension pgstattuple;

CREATE EXTENSION

postgres=#

Now you can use pgstattuple to query bloat (dead_tuple_percent)


postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;

 table_size | index_size | dead_tuple_percent

------------+------------+--------------------

 65 MB      | 21 MB      |                  0

(1 row)


postgres=# delete from ridx1 where x % 3 = 0;

DELETE 333333

postgres=# analyze ridx1;

ANALYZE

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;

 table_size | index_size | dead_tuple_percent

------------+------------+--------------------

 65 MB      | 21 MB      |              29.78

(1 row)

Index bloat

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size | bloat_ratio

------------+------------+-------------

 65 MB      | 21 MB      |       39.86

(1 row)

 postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1

INFO: Dry run enabled, not executing repack

ERROR: pg_repack failed with error: pg_repack 1.4.8 is not installed in the database

-bash-4.2$ psql

psql (14.8)

Type "help" for help.


postgres=# select * from pg_extension;
  oid  |   extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+-------------+----------+--------------+----------------+------------+-----------+--------------
 14473 | plpgsql     |       10 |           11 | f              | 1.0        |           |
 33437 | pgstattuple |       10 |         2200 | t              | 1.5        |           |
(2 rows)

postgres=# create extension pg_repack;
CREATE EXTENSION
postgres=# select * from pg_extension;
  oid  |   extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+-------------+----------+--------------+----------------+------------+-----------+--------------
 14473 | plpgsql     |       10 |           11 | f              | 1.0        |           |
 33437 | pgstattuple |       10 |         2200 | t              | 1.5        |           |
 33448 | pg_repack   |       10 |         2200 | f              | 1.4.8      |           |
(3 rows)

postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1
INFO: Dry run enabled, not executing repack
WARNING: relation "public.ridx1" must have a primary key or not-null unique keys

-bash-4.2$ psql
psql (14.8)
Type "help" for help.

postgres=# alter table ridx1 add constraint ridx1_pk primary key (x);
ALTER TABLE
postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1
INFO: Dry run enabled, not executing repack
INFO: repacking table "public.ridx1"

-bash-4.2$ psql
psql (14.8)
Type "help" for help.

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;
 table_size | index_size | bloat_ratio
------------+------------+-------------
 65 MB      | 21 MB      |       39.86
(1 row)

postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack -d postgres --table ridx1
INFO: repacking table "public.ridx1"

-bash-4.2$ psql
psql (14.8)
Type "help" for help.

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;
 table_size | index_size |    bloat_ratio
------------+------------+-------------------
 43 MB      | 14 MB      | 9.939999999999998
(1 row)

No comments:

Post a Comment