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

[kaido vaikla <kaido.vaikla@xxxxxxxxx>]

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.github@xxxxxxxxx> wrote:

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