Index size after autovacuum

Question

Good day. I was reading Postgres official documentation related to Vacuum process and Reindex routine. Some sentences were unclear for me so I want to clarify them.(Postgres documentation for version 12)

First of all. I did understand that autovacuum checks table for dead tuples, stores their locations in special memory called "maintenance_work_mem" and then when this memory is full, vacuum delete corresponding pages in all indexes that have references to those locations. Documentation about reindex says

B-tree index pages that have become completely empty are reclaimed for re-use. However, there is still a possibility of inefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated

The question is. If "page remains allocated" then does it mean that autovacuum doesn't return physical space from deleted pages inside the index to OS? For example index takes 1 GB of memory. I deleted all but one row from table and ran vacuum. In this case index will still occupy 1 Gb of memory . Am I right?

Answer 1

The README in src/backend/access/nbtree has a lot of in-depth information about this. Quotes in this answer are from there.

If you really delete all but one rows in the table, almost all pages in the index get deleted.

We consider deleting an entire page from the btree only when it's become completely empty of items. (Merging partly-full pages would allow better space reuse, but it seems impractical to move existing data items left or right to make this happen --- a scan moving in the opposite direction might miss the items if so.) Also, we never delete the rightmost page on a tree level (this restriction simplifies the traversal algorithms, as explained below). Page deletion always begins from an empty leaf page. An internal page can only be deleted as part of deleting an entire subtree. This is always a "skinny" subtree consisting of a "chain" of internal pages plus a single leaf page. There is one page on each level of the subtree, and each level/page covers the same key space.

The space is not released to the operating system, however:

Reclaiming a page doesn't actually change its state on disk --- we simply record it in the shared-memory free space map, from which it will be handed out the next time a new page is needed for a page split.

The tree will become “skinny”, because the depth of an index never shrinks. PostgreSQL has an optimization for that:

Because we never delete the rightmost page of any level (and in particular never delete the root), it's impossible for the height of the tree to decrease. After massive deletions we might have a scenario in which the tree is "skinny", with several single-page levels below the root. Operations will still be correct in this case, but we'd waste cycles descending through the single-page levels. To handle this we use an idea from Lanin and Shasha: we keep track of the "fast root" level, which is the lowest single-page level. The meta-data page keeps a pointer to this level as well as the true root. All ordinary operations initiate their searches at the fast root not the true root.

If you run REINDEX INDEX on the index or VACUUM (FULL) the table, the index will be rebuilt, and the space will be freed.

Answer 2

Yes for VACUUM (but no for VACUUM FULL):

select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

create table t(s text);
CREATE TABLE

insert into t select generate_series(1,300000)::text;
INSERT 0 300000

select pg_size_pretty(pg_table_size('t'));
 pg_size_pretty 
----------------
 10 MB
(1 row)

create index on t(s);
CREATE INDEX

select pg_size_pretty(pg_indexes_size('t'));
 pg_size_pretty 
----------------
 6600 kB
(1 row)

delete from t where s <> '1';
DELETE 299999

select count(*) from t;
 count 
-------
     1
(1 row)

select pg_size_pretty(pg_table_size('t'));
 pg_size_pretty 
----------------
 10 MB
(1 row)

select pg_size_pretty(pg_indexes_size('t'));
 pg_size_pretty 
----------------
 6600 kB
(1 row)

vacuum t;
VACUUM
select pg_size_pretty(pg_table_size('t'));
 pg_size_pretty 
----------------
 48 kB
(1 row)

select pg_size_pretty(pg_indexes_size('t'));
 pg_size_pretty 
----------------
 6600 kB
(1 row)

vacuum full t;
VACUUM
select pg_size_pretty(pg_table_size('t'));
 pg_size_pretty 
----------------
 16 kB
(1 row)

select pg_size_pretty(pg_indexes_size('t'));
 pg_size_pretty 
----------------
 16 kB
(1 row)

And no for REINDEX:

select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

create table t(s text);
CREATE TABLE

insert into t select generate_series(1,300000)::text;
INSERT 0 300000

select pg_size_pretty(pg_table_size('t'));
 pg_size_pretty 
----------------
 10 MB
(1 row)

create index on t(s);
CREATE INDEX

select pg_size_pretty(pg_indexes_size('t'));
 pg_size_pretty 
----------------
 6600 kB
(1 row)

delete from t where s <> '1';
DELETE 299999

select count(*) from t;
 count 
-------
     1
(1 row)

select pg_size_pretty(pg_table_size('t'));
 pg_size_pretty 
----------------
 10 MB
(1 row)

select pg_size_pretty(pg_indexes_size('t'));
 pg_size_pretty 
----------------
 6600 kB
(1 row)

reindex table t;
REINDEX

select pg_size_pretty(pg_table_size('t'));
 pg_size_pretty 
----------------
 10 MB
(1 row)

select pg_size_pretty(pg_indexes_size('t'));
 pg_size_pretty 
----------------
 16 kB
(1 row)