Hi Adrian,
Here is test case, basically when autovacuum runs it did release the space to disk since it had may be continuous blocks which can be released to disk but the space used by index is still being held until I ran the reindex on the table (I assume reindex for index would work as well). Subsequent insert statement did not utilize the blocks in index segment as we can see below so index bloats are still not addressed or may be I am doing something wrong:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-apple-darwin18.7.0, compiled by Apple LLVM version 10.0.1 (clang-1001.0.46.4), 64-bit
(1 row)
postgres=#
postgres=# CREATE TABLE validate_pg_repack
postgres-# (
postgres(# effectivedate timestamp,
postgres(# masterentityid integer not null,
postgres(# primaryissueid varchar(65535),
postgres(# longshortindicator varchar(65535),
postgres(# pg_repack_id varchar(65535)
postgres(# );
CREATE TABLE
postgres=# CREATE SEQUENCE validate_pg_repack_masterentityid_seq INCREMENT 1 START 1 OWNED BY validate_pg_repack.masterentityid;
CREATE SEQUENCE
postgres=#
postgres=# CREATE unique INDEX idx_pg_repack_masterentityid ON validate_pg_repack USING btree (masterentityid);
CREATE INDEX
postgres=#
postgres=# CREATE INDEX idx_pg_repack_effectivedate ON validate_pg_repack USING btree (effectivedate);
CREATE INDEX
postgres=#
postgres=# INSERT INTO validate_pg_repack (effectivedate,masterentityid,primaryissueid,longshortindicator,pg_repack_id) SELECT
postgres-# now() + round(random() * 1000) * '1 second' :: interval,
postgres-# nextval('validate_pg_repack_masterentityid_seq'),
postgres-# 'some-phone-' || round(random() * 65000),
postgres-# 'some-phone-' || round(random() * 1000),
postgres-# 'some-phone-' || round(random() * 1000)
postgres-# FROM
postgres-# generate_series(1, 900000);
INSERT 0 900000
postgres=#
postgres=# select pg_sleep(30);
pg_sleep
----------
(1 row)
postgres=# select relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from pg_stat_all_tables where relname ='validate_pg_repack';
relname | n_tup_ins | n_tup_del | last_autoanalyze | autoanalyze_count
--------------------+-----------+-----------+-------------------------------+-------------------
validate_pg_repack | 900000 | 0 | 2020-04-21 19:34:09.579475-07 | 1
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
80 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------+-------+----------+--------------------+-------+-------------
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack | 24 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack | 19 MB |
(2 rows)
postgres=# delete from validate_pg_repack where masterentityid > 450000;
DELETE 450000
postgres=# select pg_sleep(30);
pg_sleep
----------
(1 row)
postgres=# select relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from pg_stat_all_tables where relname ='validate_pg_repack';
relname | n_tup_ins | n_tup_del | last_autoanalyze | autoanalyze_count
--------------------+-----------+-----------+-------------------------------+-------------------
validate_pg_repack | 900000 | 450000 | 2020-04-21 19:35:11.029405-07 | 2
(1 row)
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
40 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------+-------+----------+--------------------+-------+-------------
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack | 24 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack | 19 MB |
(2 rows)
postgres=# INSERT INTO validate_pg_repack (effectivedate,masterentityid,primaryissueid,longshortindicator,pg_repack_id) SELECT
postgres-# now() + round(random() * 1000) * '1 second' :: interval,
postgres-# nextval('validate_pg_repack_masterentityid_seq'),
postgres-# 'some-phone-' || round(random() * 65000),
postgres-# 'some-phone-' || round(random() * 1000),
postgres-# 'some-phone-' || round(random() * 1000)
postgres-# FROM
postgres-# generate_series(450000, 900000);
INSERT 0 450001
postgres=# select pg_sleep(120);
pg_sleep
----------
postgres=#
postgres=# select relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from pg_stat_all_tables where relname ='validate_pg_repack';
relname | n_tup_ins | n_tup_del | last_autoanalyze | autoanalyze_count
--------------------+-----------+-----------+-------------------------------+-------------------
validate_pg_repack | 1350001 | 450000 | 2020-04-21 19:37:10.829261-07 | 3
(1 row)
postgres=#
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
80 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------+-------+----------+--------------------+-------+-------------
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack | 39 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack | 29 MB |
(2 rows)
postgres=# reindex table CONCURRENTLY validate_pg_repack;
REINDEX
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
80 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------+-------+----------+--------------------+-------+-------------
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack | 19 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack | 19 MB |
(2 rows)
postgres=#
postgres=# drop table validate_pg_repack cascade;
DROP TABLE
postgres=#
postgres=#
postgres=#
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-apple-darwin18.7.0, compiled by Apple LLVM version 10.0.1 (clang-1001.0.46.4), 64-bit
(1 row)
postgres=#
postgres=# CREATE TABLE validate_pg_repack
postgres-# (
postgres(# effectivedate timestamp,
postgres(# masterentityid integer not null,
postgres(# primaryissueid varchar(65535),
postgres(# longshortindicator varchar(65535),
postgres(# pg_repack_id varchar(65535)
postgres(# );
CREATE TABLE
postgres=# CREATE SEQUENCE validate_pg_repack_masterentityid_seq INCREMENT 1 START 1 OWNED BY validate_pg_repack.masterentityid;
CREATE SEQUENCE
postgres=#
postgres=# CREATE unique INDEX idx_pg_repack_masterentityid ON validate_pg_repack USING btree (masterentityid);
CREATE INDEX
postgres=#
postgres=# CREATE INDEX idx_pg_repack_effectivedate ON validate_pg_repack USING btree (effectivedate);
CREATE INDEX
postgres=#
postgres=# INSERT INTO validate_pg_repack (effectivedate,masterentityid,primaryissueid,longshortindicator,pg_repack_id) SELECT
postgres-# now() + round(random() * 1000) * '1 second' :: interval,
postgres-# nextval('validate_pg_repack_masterentityid_seq'),
postgres-# 'some-phone-' || round(random() * 65000),
postgres-# 'some-phone-' || round(random() * 1000),
postgres-# 'some-phone-' || round(random() * 1000)
postgres-# FROM
postgres-# generate_series(1, 900000);
INSERT 0 900000
postgres=#
postgres=# select pg_sleep(30);
pg_sleep
----------
(1 row)
postgres=# select relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from pg_stat_all_tables where relname ='validate_pg_repack';
relname | n_tup_ins | n_tup_del | last_autoanalyze | autoanalyze_count
--------------------+-----------+-----------+-------------------------------+-------------------
validate_pg_repack | 900000 | 0 | 2020-04-21 19:34:09.579475-07 | 1
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
80 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------+-------+----------+--------------------+-------+-------------
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack | 24 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack | 19 MB |
(2 rows)
postgres=# delete from validate_pg_repack where masterentityid > 450000;
DELETE 450000
postgres=# select pg_sleep(30);
pg_sleep
----------
(1 row)
postgres=# select relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from pg_stat_all_tables where relname ='validate_pg_repack';
relname | n_tup_ins | n_tup_del | last_autoanalyze | autoanalyze_count
--------------------+-----------+-----------+-------------------------------+-------------------
validate_pg_repack | 900000 | 450000 | 2020-04-21 19:35:11.029405-07 | 2
(1 row)
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
40 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------+-------+----------+--------------------+-------+-------------
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack | 24 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack | 19 MB |
(2 rows)
postgres=# INSERT INTO validate_pg_repack (effectivedate,masterentityid,primaryissueid,longshortindicator,pg_repack_id) SELECT
postgres-# now() + round(random() * 1000) * '1 second' :: interval,
postgres-# nextval('validate_pg_repack_masterentityid_seq'),
postgres-# 'some-phone-' || round(random() * 65000),
postgres-# 'some-phone-' || round(random() * 1000),
postgres-# 'some-phone-' || round(random() * 1000)
postgres-# FROM
postgres-# generate_series(450000, 900000);
INSERT 0 450001
postgres=# select pg_sleep(120);
pg_sleep
----------
(1 row)
postgres=# select relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from pg_stat_all_tables where relname ='validate_pg_repack';
relname | n_tup_ins | n_tup_del | last_autoanalyze | autoanalyze_count
--------------------+-----------+-----------+-------------------------------+-------------------
validate_pg_repack | 1350001 | 450000 | 2020-04-21 19:37:10.829261-07 | 3
(1 row)
postgres=#
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
80 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------+-------+----------+--------------------+-------+-------------
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack | 39 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack | 29 MB |
(2 rows)
postgres=# reindex table CONCURRENTLY validate_pg_repack;
REINDEX
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
80 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------+-------+----------+--------------------+-------+-------------
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack | 19 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack | 19 MB |
(2 rows)
postgres=#
postgres=# drop table validate_pg_repack cascade;
DROP TABLE
postgres=#
postgres=#
postgres=#
Regards,
Virendra Kumar
On Tuesday, April 21, 2020, 3:54:13 PM PDT, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 4/21/20 2:32 PM, Virendra Kumar wrote:
> Autovacuum does takes care of dead tuples and return space to table's
> allocated size and can be re-used by fresh incoming rows or any updates.
>
> Index bloat is still not being taken care of by autovacuum process. You
> should use pg_repack to do index rebuild. Keep in mind that pg_repack
> requires double the space of indexes, since there will be two indexes
> existing during rebuild processes.
You sure about that? On Postgres 12:
--2020-04-21 15:47:27.452 PDT-0DEBUG: plant1: vac: 5154 (threshold
1081), anl: 5154 (threshold 565)
--2020-04-21 15:47:27.452 PDT-0DEBUG: autovac_balance_cost(pid=18701
db=25092, rel=26497, dobalance=yes cost_limit=200, cost_limit_base=200,
cost_delay=2)
--2020-04-21 15:47:27.452 PDT-0DEBUG: CommitTransaction(1) name:
unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG: vacuuming "public.plant1"
--2020-04-21 15:47:27.504 PDT-0DEBUG: scanned index "p_no_pkey" to
remove 5114 row versions
--2020-04-21 15:47:27.504 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.514 PDT-0DEBUG: scanned index "common_idx" to
remove 5114 row versions
--2020-04-21 15:47:27.514 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.01 s
--2020-04-21 15:47:27.515 PDT-0DEBUG: scanned index "genus_idx" to
remove 5114 row versions
--2020-04-21 15:47:27.515 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG: "plant1": removed 5114 row
versions in 121 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "p_no_pkey" now contains
5154 row versions in 31 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "common_idx" now contains
5154 row versions in 60 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "genus_idx" now contains
5154 row versions in 47 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.518 PDT-0DEBUG: "plant1": found 5154 removable,
5154 nonremovable row versions in 195 out of 195 pages
--2020-04-21 15:47:27.518 PDT-0DETAIL: 0 dead row versions cannot be
removed yet, oldest xmin: 9715
There were 256 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s.
--2020-04-21 15:47:27.518 PDT-0LOG: automatic vacuum of table
"production.public.plant1": index scans: 1
pages: 0 removed, 195 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 5154 removed, 5154 remain, 0 are dead but not yet
removable, oldest xmin: 9715
buffer usage: 753 hits, 0 misses, 255 dirtied
avg read rate: 0.000 MB/s, avg write rate: 30.586 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s
>
> Regards,
> Virendra Kumar
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
> Autovacuum does takes care of dead tuples and return space to table's
> allocated size and can be re-used by fresh incoming rows or any updates.
>
> Index bloat is still not being taken care of by autovacuum process. You
> should use pg_repack to do index rebuild. Keep in mind that pg_repack
> requires double the space of indexes, since there will be two indexes
> existing during rebuild processes.
You sure about that? On Postgres 12:
--2020-04-21 15:47:27.452 PDT-0DEBUG: plant1: vac: 5154 (threshold
1081), anl: 5154 (threshold 565)
--2020-04-21 15:47:27.452 PDT-0DEBUG: autovac_balance_cost(pid=18701
db=25092, rel=26497, dobalance=yes cost_limit=200, cost_limit_base=200,
cost_delay=2)
--2020-04-21 15:47:27.452 PDT-0DEBUG: CommitTransaction(1) name:
unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG: vacuuming "public.plant1"
--2020-04-21 15:47:27.504 PDT-0DEBUG: scanned index "p_no_pkey" to
remove 5114 row versions
--2020-04-21 15:47:27.504 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.514 PDT-0DEBUG: scanned index "common_idx" to
remove 5114 row versions
--2020-04-21 15:47:27.514 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.01 s
--2020-04-21 15:47:27.515 PDT-0DEBUG: scanned index "genus_idx" to
remove 5114 row versions
--2020-04-21 15:47:27.515 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG: "plant1": removed 5114 row
versions in 121 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "p_no_pkey" now contains
5154 row versions in 31 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "common_idx" now contains
5154 row versions in 60 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "genus_idx" now contains
5154 row versions in 47 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.518 PDT-0DEBUG: "plant1": found 5154 removable,
5154 nonremovable row versions in 195 out of 195 pages
--2020-04-21 15:47:27.518 PDT-0DETAIL: 0 dead row versions cannot be
removed yet, oldest xmin: 9715
There were 256 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s.
--2020-04-21 15:47:27.518 PDT-0LOG: automatic vacuum of table
"production.public.plant1": index scans: 1
pages: 0 removed, 195 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 5154 removed, 5154 remain, 0 are dead but not yet
removable, oldest xmin: 9715
buffer usage: 753 hits, 0 misses, 255 dirtied
avg read rate: 0.000 MB/s, avg write rate: 30.586 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s
>
> Regards,
> Virendra Kumar
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
Attachment:
test_case.sql
Description: Binary data
