I created a partitioned table as the following:
CREATE TABLE IF NOT EXISTS log.partitioned_requests ( datetime TIMESTAMP WITH TIME ZONE, ts_month TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('month', datetime), user_id VARCHAR(37), trace_id VARCHAR(37), user_name TEXT, user_email TEXT, protocol TEXT, method TEXT, scheme TEXT, host TEXT, path TEXT, query TEXT, content_type TEXT, request_duration BIGINT, response_duration BIGINT, response_code INTEGER, user_agent TEXT, remote_ip IP, referer TEXT ) PARTITIONED BY (ts_month, user_id) ;
And then inserted a few million rows into it, creating 85 partitions and 1020 shards.
Then I deleted rows from that table:
DELETE FROM log.partitioned_requests WHERE datetime < '2021-12-01'; DELETE FROM log.partitioned_requests WHERE datetime < '2022-01-01'; DELETE FROM log.partitioned_requests WHERE datetime < '2022-02-01'; DELETE FROM log.partitioned_requests WHERE datetime < '2022-03-01';
Everything went fine and all of the rows were deleted. But when I looked in the web-based administrative User Interface, I saw that partitions were still there.
I observed that size (sum of primary shards) of
log.partitioned_requests was about 1000 MiB in the beginning, but after the DELETE queries it started to drop: 950 MiB , 926.6 MiB , 826.9 MiB , …, 177 MiB . It stabilized at 177 MiB in a few minutes .
I know 177 MiB isn’t that much, and not that small (is it metadata?) but if I want to get rid of those partitions, how can I do that?
Should I ‘close’ the partition as described in Partitioned tables — CrateDB: Reference?