Closed thecatontheflat closed 3 months ago
I think a table-rewrite (VACUUM FULL) should also work.
@onurctirtir Since I'm not sure if that works too (potentially even a simpler solution), could you please let me know if I should add it to the docs?
@onurctirtir Since I'm not sure if that works too (potentially even a simpler solution), could you please let me know if I should add it to the docs?
Yes, I think we should suggest VACUUM (FULL) instead of alter_table_set_access_method() workaround.
[local] onurctirtir@onurctirtir:9700-97789=# create table tbl_2 (a int) using columnar;
CREATE TABLE
Time: 23.525 ms
[local] onurctirtir@onurctirtir:9700-97789=# insert into tbl_2 values (1);
INSERT 0 1
Time: 17.152 ms
[local] onurctirtir@onurctirtir:9700-97789=# insert into tbl_2 values (2);
INSERT 0 1
Time: 12.168 ms
[local] onurctirtir@onurctirtir:9700-97789=# select * from columnar.options ;
┌──────────┬───────────────────────┬──────────────────┬─────────────┬───────────────────┐
│ relation │ chunk_group_row_limit │ stripe_row_limit │ compression │ compression_level │
├──────────┼───────────────────────┼──────────────────┼─────────────┼───────────────────┤
│ tbl_2 │ 10000 │ 150000 │ zstd │ 3 │
└──────────┴───────────────────────┴──────────────────┴─────────────┴───────────────────┘
(1 row)
Time: 15.491 ms
[local] onurctirtir@onurctirtir:9700-97789=# select * from columnar.stripe ;
┌──────────┬─────────────┬────────────┬─────────────┬─────────────┬──────────────┬─────────────────┬───────────┬───────────────────┬──────────────────┐
│ relation │ storage_id │ stripe_num │ file_offset │ data_length │ column_count │ chunk_row_count │ row_count │ chunk_group_count │ first_row_number │
├──────────┼─────────────┼────────────┼─────────────┼─────────────┼──────────────┼─────────────────┼───────────┼───────────────────┼──────────────────┤
│ tbl_2 │ 10000000000 │ 1 │ 16336 │ 14 │ 1 │ 10000 │ 1 │ 1 │ 1 │
│ tbl_2 │ 10000000000 │ 2 │ 24504 │ 14 │ 1 │ 10000 │ 1 │ 1 │ 150001 │
└──────────┴─────────────┴────────────┴─────────────┴─────────────┴──────────────┴─────────────────┴───────────┴───────────────────┴──────────────────┘
(2 rows)
Time: 14.453 ms
[local] onurctirtir@onurctirtir:9700-97789=# vacuum (full) tbl_2;
VACUUM
Time: 25.059 ms
[local] onurctirtir@onurctirtir:9700-97789=# select * from columnar.stripe ;
┌──────────┬─────────────┬────────────┬─────────────┬─────────────┬──────────────┬─────────────────┬───────────┬───────────────────┬──────────────────┐
│ relation │ storage_id │ stripe_num │ file_offset │ data_length │ column_count │ chunk_row_count │ row_count │ chunk_group_count │ first_row_number │
├──────────┼─────────────┼────────────┼─────────────┼─────────────┼──────────────┼─────────────────┼───────────┼───────────────────┼──────────────────┤
│ tbl_2 │ 10000000001 │ 1 │ 16336 │ 18 │ 1 │ 10000 │ 2 │ 1 │ 1 │
└──────────┴─────────────┴────────────┴─────────────┴─────────────┴──────────────┴─────────────────┴───────────┴───────────────────┴──────────────────┘
(1 row)
Time: 3.130 ms
Updated the docs to include VACUUM (FULL)
option as well as the original method
@microsoft-github-policy-service agree