Question/feature request: merging several chunks due to small ETL interval
muntdan opened this issue · 7 comments
What's wrong?
FIY: Discord channel is not working and you allow now only bug reports but this is are questions/feature request.
Previous Discord advice was to try to make the ETL batch as closer to 150K.
As I am unable to run with more than 15min delay I am around 30k batch insert.
But because I choose to partition my tables by tenants for query performance the actual 30k is probably split by 10 tenants internally and reaches 10 different strip/chunks with an average of 3K/batch. Thus being again very far from the 150K or at least the 10k/chunk. I now run simple Vacuum after each insert as advised on Discord.
Is there any mechanism or would you consider adding such feature to merge manually or automatically several stripe/chunks beside/togheter with vacuum or other command ?
Does the columnar.vacuum make and actual stripe merge/reduction of number of stripes/chunks ?
Documentation only states: "stripes to consolidate" but being in the Space Reclamation area I am not sure it actually merges them or only defragments the space between them ?
Right, you can use vacuum to do this. Are you seeing results that indicate this isn't having the desired effect?
columnar.vacuum does not try to address this, it's looking for stripes which are at least 20% updated or deleted rows.
For this small ETL batches issue I am more concerned for the large number of stripes & chunks, which I already tested previously causes slower query performance. Full vacuum blocks a big table for to long period of time.
Standard simple vacuum recommendation I received back than on Discord (from @mkaruza if I remember correctly) optimizes the performance in some regards but does not addresses the large number of stripes & chunks which in a large production database will reach a pretty huge number.
So if "columnar.vacuum does not try to address this", could there be other options ?
Right, not vacuum full tablename, just vacuum tablename, it's designed for this exact case. What does your vacuum verbose tablename say?
@mkaruza could comment about how the vacuum works exactly. Possibly this could be improved further but we'd need to know more about the case you are encountering.
To rephrase this in a more technical/practical question:
On a brand new table:
I insert first batch of 3000rows => 1st Stripe is created with 1st Chunk.
I insert second batch of 3000rows => I assume second Chunk is created although 1st chunk had 7000 more rows capacity.
I run simple vacuum. What effect does it have on the 2 chunks ?
Is it better to run now columnar.vacuum ?
If indeed the 2 chunks are merged what happens if this is an old Stripe which already had 15 small/incomplete chunks ? does this means it remains forever with the ~5 merged chunks or does it now accepts new data even if is not the last Stripe ?
My understanding is the vacuum will combine both stripes and chunks up to the maximum stripe size. I don't know the full details of the implementation but you can test it like so and see where its limitations are. Note that vacuum verbose runs vacuum after the statistics are printed, so you have to run it twice to see the before-and-after effect of running the vacuum. You can see here that it went from 2 stripes in 2 chunks to 1 chunk in 1 stripe.
postgres=# create table test_vacuum (i int) using columnar;
CREATE TABLE
Time: 39.342 ms
postgres=# insert into test_vacuum select generate_series(1, 3000);
INSERT 0 3000
Time: 18.313 ms
postgres=# insert into test_vacuum select generate_series(1, 3000);
INSERT 0 3000
Time: 10.551 ms
postgres=# vacuum verbose test_vacuum;
INFO: statistics for "test_vacuum":
storage id: 10000000000
total file size: 32768, total data size: 13526
compression rate: 1.83x
total row count: 6000, stripe count: 2, average rows per stripe: 3000
chunk count: 2, containing data for dropped columns: 0, zstd compressed: 2
VACUUM
Time: 56.130 ms
postgres=# vacuum verbose test_vacuum;
INFO: statistics for "test_vacuum":
storage id: 10000000000
total file size: 24576, total data size: 7129
compression rate: 3.47x
total row count: 6000, stripe count: 1, average rows per stripe: 6000
chunk count: 1, containing data for dropped columns: 0, zstd compressed: 1
VACUUM
Time: 33.640 ms
Is it better to run now columnar.vacuum?
If you are not using UPDATE or DELETE then there's no reason to ever call columnar.vacuum. The purpose of this function is space reclamation, or you could also think of it as table compaction, delete materialization, etc. Without it, the table would only continue to grow in size (on disk) when you update or delete without any way to reclaim space.
Here's an example, you can see that after running DELETE the "total row count" is still 1,000,000, but after running columnar.vacuum, the table is compacted and there is only the 500,000 rows on disk.
postgres=# truncate test_vacuum;
TRUNCATE TABLE
Time: 14.788 ms
postgres=# insert into test_vacuum select generate_series(1, 1000000);
INSERT 0 1000000
Time: 314.002 ms
postgres=# delete from test_vacuum where i % 2 = 0;
DELETE 500000
Time: 726.878 ms
postgres=# vacuum verbose test_vacuum;
INFO: statistics for "test_vacuum":
storage id: 10000000001
total file size: 2736128, total data size: 2697277
compression rate: 1.53x
total row count: 1000000, stripe count: 7, average rows per stripe: 142857
chunk count: 100, containing data for dropped columns: 0, zstd compressed: 100
VACUUM
Time: 76.027 ms
postgres=# select columnar.vacuum('test_vacuum', 10);
vacuum
--------
9
(1 row)
Time: 98.648 ms
postgres=# vacuum verbose test_vacuum;
INFO: statistics for "test_vacuum":
storage id: 10000000001
total file size: 1376256, total data size: 1351768
compression rate: 1.53x
total row count: 500000, stripe count: 4, average rows per stripe: 125000
chunk count: 51, containing data for dropped columns: 0, zstd compressed: 51
VACUUM
Time: 42.439 ms
Excellent information. Thank you so much for such fast and complete information.
Maybe this could be compiled into a maintenance guide or small ETL batch guide chapter into Documentation together with the examples you provided ;)