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

Hi,

But my question was did you check, if t1 and/or t2 relfile was deleted
before complete vacuum?
Why I had, in my example, temporarily 335 files extra (old relfiles?). And
removed after vacuum completely finished.
br
Kaido

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

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