Re: How does vacuum full works if table is partitioned?

Hi Vijay,

Thnx for your test. But did you check, if t1 and/or t2 relfile was deleted
before complete vacuum?

I did simple test with partitioned table. 66 empty partitions.
kvpg=# \d test2.time_taptest_table2
Partitioned table "test2.time_taptest_table2"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------------
col1 | integer | | |
col2 | text | | | 'stuff'::text
col3 | timestamp with time zone | | not null | now()
Partition key: RANGE (col3)
Indexes:
"time_taptest_table2_col3_idx" btree (col3)
Number of partitions: 67 (Use \d+ to list them.)

kvpg=# select count(*) from test2.time_taptest_table2;
count
-------
0
(1 row)

*Terminal 1:*
$ oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
16388 kvpg pg_default
38353 kvpg2 pg_default
14187 postgres pg_default
14186 template0 pg_default
1 template1 pg_default
$ cd $PGDATA/base/16388
$ while true ; do date ; ls -l | wc -l; echo ====; sleep 10; done;

Wed Jun 16 20:20:34 EEST 2021
672
====
Wed Jun 16 20:20:44 EEST 2021
672
====
Wed Jun 16 20:20:54 EEST 2021
1007
====
Wed Jun 16 20:21:04 EEST 2021
1007
====
...

Wed Jun 16 20:22:24 EEST 2021
1007
====
Wed Jun 16 20:22:34 EEST 2021
672
====

*Same time Terminal 2:*
$ date; time vacuumdb -f -d kvpg -t test2.time_taptest_table2
Wed Jun 16 20:20:50 EEST 2021
vacuumdb: vacuuming database "kvpg"

real 0m0.601s
user 0m0.001s
sys 0m0.003s

You see, 0.5sec vacuuming, but about 2min 335 extra files

br
Kaido

On Wed, 16 Jun 2021 at 17:26, Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

> On Wed, 16 Jun 2021 at 18:37, kaido vaikla <kaido(dot)vaikla(at)gmail(dot)com> wrote:
> >
> > Hi,
> >
> > Will VACUUM FULL create all new partitions at once and removes all
> together if vacuum is done or does it per partition? Or something third?
> >
>
>
> I think it is one partition at a time.
>
> ***********************
> # in session [1] do the setup
>
> postgres=# create table t(id int, value int) partition by list(id)
> with (autovacuum_enabled = off);
> CREATE TABLE
> postgres=# create table t1 partition of t for values in (1) with
> (autovacuum_enabled = off);
> CREATE TABLE
> postgres=# create table t2 partition of t for values in (2) with
> (autovacuum_enabled = off);
> CREATE TABLE
> postgres=# create table t3 partition of t for values in (3) with
> (autovacuum_enabled = off);
> CREATE TABLE
> postgres=# \d+ t
> Partitioned table "public.t"
> Column | Type | Collation | Nullable | Default | Storage |
> Compression | Stats target | Description
>
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> id | integer | | | | plain |
> | |
> value | integer | | | | plain |
> | |
> Partition key: LIST (id)
> Partitions: t1 FOR VALUES IN (1),
> t2 FOR VALUES IN (2),
> t3 FOR VALUES IN (3)
> Options: autovacuum_enabled=off
>
> postgres=# insert into t select (1), (2), (3);
>
> do $$
> declare i int;
> begin
> for i in 1..10000 loop
> update t set id = id;
> end loop;
> end; $$;
> DO
>
> ***********************
> in a parallel session [2], lock one partition for update
> postgres=# begin;
> BEGIN
> postgres=*# select * from t3 where id = 3 for update;
> id | value
> ----+-------
> 3 | 3
> (1 row)
>
>
> ***********************
> in another parallel session [3] strace the session pid to track
> truncate syscall.
>
> strace -r -f -p 2050 -e trace=truncate
> strace: Process 2050 attached
> 0.000000 truncate("base/14008/17850", 0) = 0
> 0.000291 truncate("base/14008/17850.1", 0) = -1 ENOENT (No such
> file or directory)
> 0.000372 truncate("base/14008/17850_fsm", 0) = 0
> 0.000226 truncate("base/14008/17850_fsm.1", 0) = -1 ENOENT (No
> such file or directory)
> 0.000061 truncate("base/14008/17850_vm", 0) = -1 ENOENT (No such
> file or directory)
> 0.000019 truncate("base/14008/17850_init", 0) = -1 ENOENT (No
> such file or directory)
> 0.009866 truncate("base/14008/17853", 0) = 0
> 0.000263 truncate("base/14008/17853.1", 0) = -1 ENOENT (No such
> file or directory)
> 0.000151 truncate("base/14008/17853_fsm", 0) = 0
> 0.000134 truncate("base/14008/17853_fsm.1", 0) = -1 ENOENT (No
> such file or directory)
> 0.000041 truncate("base/14008/17853_vm", 0) = -1 ENOENT (No such
> file or directory)
> 0.000048 truncate("base/14008/17853_init", 0) = -1 ENOENT (No
> such file or directory)
> 1.003913 --- SIGALRM {si_signo=SIGALRM, si_code=SI_KERNEL} ---
> 55.328486 truncate("base/14008/17841", 0) = 0 ----- this was
> waiting for lock and hence did not complete vaccum full, but remaining
> 0.000397 truncate("base/14008/17841.1", 0) = -1 ENOENT (No such
> file or directory)
> 0.000063 truncate("base/14008/17841_fsm", 0) = 0
> 0.000208 truncate("base/14008/17841_fsm.1", 0) = -1 ENOENT (No
> such file or directory)
> 0.000094 truncate("base/14008/17841_vm", 0) = -1 ENOENT (No such
> file or directory)
> 0.000022 truncate("base/14008/17841_init", 0) = -1 ENOENT (No
> such file or directory)
>
>
>
>
> ***********************
> in paralllel session [3], run vaccum full, it completes for partition
> t1, and t2 but wait for t3 which in a transaction. [3]
>
> vacuum (full,verbose,analyze) t;
> INFO: analyzing "public.t" inheritance tree
> INFO: "t1": scanned 45 of 45 pages, containing 1 live rows and 10000
> dead rows; 1 rows in sample, 1 estimated total rows
> INFO: "t2": scanned 45 of 45 pages, containing 1 live rows and 10000
> dead rows; 1 rows in sample, 1 estimated total rows
> INFO: "t3": scanned 89 of 89 pages, containing 1 live rows and 144
> dead rows; 1 rows in sample, 1 estimated total rows
> INFO: vacuuming "public.t1"
> INFO: "t1": found 10000 removable, 1 nonremovable row versions in 45 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
> INFO: analyzing "public.t1"
> INFO: "t1": scanned 1 of 1 pages, containing 1 live rows and 0 dead
> rows; 1 rows in sample, 1 estimated total rows
> INFO: vacuuming "public.t2"
> INFO: "t2": found 10000 removable, 1 nonremovable row versions in 45 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
> INFO: analyzing "public.t2"
> INFO: "t2": scanned 1 of 1 pages, containing 1 live rows and 0 dead
> rows; 1 rows in sample, 1 estimated total rows
>
> --------- this was waiting for accessexclusive lock on t3.
> INFO: vacuuming "public.t3"
> INFO: "t3": found 56 removable, 1 nonremovable row versions in 89 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
> INFO: analyzing "public.t3"
> INFO: "t3": scanned 1 of 1 pages, containing 1 live rows and 0 dead
> rows; 1 rows in sample, 1 estimated total rows
> VACUUM
>
>
>
>
>
> --
> Thanks,
> Vijay
> Mumbai, India
>