[Bug]: columnar.vacuum() does not release disk space

[fzhougithub]

What's wrong?

We already have metadata tables that show 100% deleted and empty stripe, but the vacuum could not touch it after many times 1 stripe run.

columnar/columnar/src/backend/columnar/columnar_metadata.c

Line 16 in d1f7657

* * maps logical row numbers to stripe IDs

Looks like the vacuum is a rewrite-and-compact utility, it is not metadata-driven tombstone reclamation engine.

Can you help add the function that refer to metadata table info to vacuum the stripe with high deleted row first, then, less rowcount strip second?

Thanks a lot
Frank

[JerrySievert]

you might try the UDF columnar.vacuum_full(), but make a backup first.

note that I haven't been at Hydra for a couple of years now, and cannot help you with code or support, just some ideas of what might work for you.

[fzhougithub]

@JerrySievert

Thanks a lot for your reply

We do have large single table, stats like

select stripeid, rowcount, deletedrows, chunkcount,datalength from columnar.stats('test.a') order by deletedrows desc limit 100;
 stripeid | rowcount | deletedrows | chunkcount | datalength
----------+----------+-------------+------------+------------
    12056 |   100000 |      100000 |         10 |   57310479
    12051 |   150000 |       81350 |         15 |   70790276
    12060 |    56223 |       56223 |          6 |   27200748
     9940 |    49986 |       49986 |          5 |   39315874
    12052 |    52906 |       26312 |          6 |   24557527
      832 |   140023 |       21532 |         15 |   92842896
      898 |   139933 |       20336 |         14 |   93714218
    12053 |    11912 |       10935 |          2 |    5562298
      664 |   139954 |        7478 |         14 |   84534241
      530 |   148258 |        5035 |         15 |   87351617
      547 |   139913 |        3964 |         14 |   79515782
      565 |   119975 |        3863 |         12 |   66377469
      818 |   139962 |        3845 |         14 |  101776529
     1512 |   140059 |        3772 |         15 |  110689484
     1173 |   120030 |        3453 |         13 |   72599921
     1182 |   120013 |        3319 |         13 |   77072155
     1369 |   120013 |        3250 |         13 |   74483015
      574 |   120061 |        3236 |         13 |   63621595
     1360 |   139966 |        3234 |         14 |   82304820
      767 |   140008 |        3109 |         15 |   97806199
      721 |   120013 |        3026 |         13 |   72619408
      674 |   140074 |        3014 |         15 |   91192627
     1464 |   119949 |        3008 |         12 |   88081235
      757 |   120044 |        3004 |         13 |   86647323
      748 |   119983 |        2989 |         12 |   86287927
     1426 |   120018 |        2988 |         13 |   73444661
      556 |   120039 |        2943 |         13 |   58117977
     1164 |   119967 |        2905 |         12 |   84001886
      730 |   140027 |        2903 |         15 |   91388906
     1437 |   140028 |        2811 |         15 |   89223364
      739 |   119957 |        2797 |         12 |   82862702 
     1446 |   119954 |        2783 |         12 |   78898626
     1492 |   120024 |        2774 |         13 |   89361809
......

I did run "select columnar.vacuum('test.a')", it exhausted all of the RAM and swap space and cause OOM crash

Since total stripes of the table test.a is 4842, I changed to use for loop to clean up with limited stripes

for i in {1..50};do echo $i; psql -d test -f vacuum_a.sql > vacuum_a.log 2>&1 ; sleep 3; done`

cat vacuum_a.sql 

\timing on
\set ON_ERROR_STOP on
select now();
BEGIN;
LOCK TABLE test.a IN ACCESS EXCLUSIVE MODE;
SELECT columnar.vacuum('test.a',100);
commit;

After it completed. there are still 2 stripes not be cleaned up

select stripeid, rowcount, deletedrows, chunkcount,datalength from columnar.stats('test.a') order by deletedrows desc limit 100;
 stripeid | rowcount | deletedrows | chunkcount | datalength
----------+----------+-------------+------------+------------
      832 |   140023 |       21532 |         15 |   92842896
      898 |   139933 |       20336 |         14 |   93714218
      664 |   139954 |        7478 |         14 |   84534241
      530 |   148258 |        5035 |         15 |   87351617
      547 |   139913 |        3964 |         14 |   79515782
      565 |   119975 |        3863 |         12 |   66377469
      818 |   139962 |        3845 |         14 |  101776529
     1512 |   140059 |        3772 |         15 |  110689484
     1173 |   120030 |        3453 |         13 |   72599921
     1182 |   120013 |        3319 |         13 |   77072155
     1369 |   120013 |        3250 |         13 |   74483015
      574 |   120061 |        3236 |         13 |   63621595
     1360 |   139966 |        3234 |         14 |   82304820
      767 |   140008 |        3109 |         15 |   97806199
      721 |   120013 |        3026 |         13 |   72619408
      674 |   140074 |        3014 |         15 |   91192627
     1464 |   119949 |        3008 |         12 |   88081235
      757 |   120044 |        3004 |         13 |   86647323
      748 |   119983 |        2989 |         12 |   86287927
     1426 |   120018 |        2988 |         13 |   73444661
      556 |   120039 |        2943 |         13 |   58117977
     1164 |   119967 |        2905 |         12 |   84001886
      730 |   140027 |        2903 |         15 |   91388906
     1437 |   140028 |        2811 |         15 |   89223364
      739 |   119957 |        2797 |         12 |   82862702
     1446 |   119954 |        2783 |         12 |   78898626
     1492 |   120024 |        2774 |         13 |   89361809
......

The total number of stripes dropped down from 4842 to 2410, but the total table size got increased from about 201GB to 435GB.

ls -larth /apps/lib/pgsql/14/data/base/16386/2695371097*|grep 'Jul 28'|wc -l
234

ls -larth /apps/lib/pgsql/14/data/base/16386/2695371097*|grep -v 'Jul 28'|wc -l
201

select pg_size_pretty(pg_total_relation_size('test.a'));
 pg_size_pretty
----------------
 434 GB
(1 row)

Then, I changed to run 1 stripe per call like

for i in {1..2500};do echo $i >> a.log; psql -d test -f vacuum_a.sql >> vacuum_a.log 2>&1 ; sleep 1; done

cat vacuum_a.sql

\timing on
\set ON_ERROR_STOP on
select now();
BEGIN;
LOCK TABLE test.a IN ACCESS EXCLUSIVE MODE;
SELECT columnar.vacuum('test.a',1);
commit;

But, after that, 2 dirty stripes are still there.

ods_domani=# select * from columnar.stats('test.a') order by deletedrows desc limit 100;
 stripeid | fileoffset  | rowcount | deletedrows | chunkcount | datalength
----------+-------------+----------+-------------+------------+------------
      832 |  6240954795 |   140023 |       21532 |         15 |   92842896
      898 |  6427646110 |   139933 |       20336 |         14 |   93714218
      664 |  4799268298 |   139954 |        7478 |         14 |   84534241
      530 |  3855348881 |   148258 |        5035 |         15 |   87351617
      547 |  3942700498 |   139913 |        3964 |         14 |   79515782
      565 |  4080334257 |   119975 |        3863 |         12 |   66377469
      818 |  6139178266 |   139962 |        3845 |         14 |  101776529
     1512 | 11342977844 |   140059 |        3772 |         15 |  110689484
     1173 |  8726130149 |   120030 |        3453 |         13 |   72599921
     1182 |  8798730070 |   120013 |        3319 |         13 |   77072155
     1369 | 10124769279 |   120013 |        3250 |         13 |   74483015
      574 |  4146711726 |   120061 |        3236 |         13 |   63621595
     1360 | 10042464459 |   139966 |        3234 |         14 |   82304820
      767 |  5700453588 |   140008 |        3109 |         15 |   97806199
      721 |  5280647322 |   120013 |        3026 |         13 |   72619408
      674 |  4883802539 |   140074 |        3014 |         15 |   91192627
     1464 | 10869332480 |   119949 |        3008 |         12 |   88081235
      757 |  5613806265 |   120044 |        3004 |         13 |   86647323

Why the columnar.vacuum did not release disk space? 
Should I run postgresql vacuum_full command like 'vacuum full test.a" to release disk space? 

Thank you