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

On Wed, 16 Jun 2021 at 18:37, kaido vaikla <kaido.vaikla@gmail.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