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

thank you for asking this,
i have first time followed the debugger into the source :)

postgres/vacuum.c at 0734b0e983443882ec509ab4501c30ba9b706f5f ·
postgres/postgres (github.com)
anyways.

if you notice, one filenode changes at a time, (while i have my gdb
debugger on, iterating to one partition vacuum full at a time)

postgres(at)db:~/playground/pg14/base/17718$ oid2name -d test
From database "test":
Filenode Table Name
----------------------
17944 t1
17948 t2
17952 t3

^^^^ before any vacuuming

-- now start vacuuming
postgres(at)db:~/playground/pg14/base/17718$ oid2name -d test
From database "test":
Filenode Table Name
----------------------
17956 t1 --- this one is first
17948 t2
17952 t3

postgres(at)db:~/playground/pg14/base/17718$ oid2name -d test
From database "test":
Filenode Table Name
----------------------
17956 t1
17948 t2
17952 t3
postgres(at)db:~/playground/pg14/base/17718$ oid2name -d test
From database "test":
Filenode Table Name
----------------------
17956 t1
17960 t2 --this one is second and so on
17952 t3

if you want to play along,
i put a break point at vacuum_rel
Breakpoint 1, vacuum_rel (relid=17890, relation=0x0,
params=params(at)entry=0x7ffe6ff560d0)
at vacuum.c:1785
vaccum_rel
<https://github.com/postgres/postgres/blob/0734b0e983443882ec509ab4501c30ba9b706f5f/src/backend/commands/vacuum.c#L1784>
vaccum_full
<https://github.com/postgres/postgres/blob/0734b0e983443882ec509ab4501c30ba9b706f5f/src/backend/commands/vacuum.c#L1998>

unless the experts correct me.

On Wed, 16 Jun 2021 at 22:58, kaido vaikla <kaido(dot)vaikla(at)gmail(dot)com> wrote:

> 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
>>
>

--
Thanks,
Vijay
Mumbai, India