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

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