Why is the db.file size still large after deleting data or dropping tables? #12286
Closed
Description
What happens?
I created a table (persistent, dbfile), inserted 1 billion tuples into it, and then deleted these tuples. However, the dbfile remains 89MB in size.
To Reproduce
./duckdb vacuum.dbCREATE TABLE test (x INT, y AS (x + 100));
insert into test select range FROM range(1000000000);
delete from test where x % 10 = 7; --dbfile size : 187MB
delete from test where x % 10 = 6; --dbfile size : 363MB
delete from test where x % 10 = 5; --dbfile size : 404M
delete from test where x % 10 = 4; --dbfile size : 404M
delete from test where x % 10 = 3; --dbfile size : 566M
delete from test where x % 10 = 2; --dbfile size : 566M
delete from test where x % 10 = 1; --dbfile size : 404M
delete from test where x % 10 = 0; --dbfile size : 404M
delete from test where x % 10 = 8; --dbfile size : 90M
delete from test where x % 10 = 9; --dbfile size : 89M
drop table test; --dbfile size : 89MOS:
MacOs
DuckDB Version:
0.10.3
DuckDB Client:
cli
Full Name:
EricDuck
Affiliation:
personal
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a stable release
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
- Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
- Yes, I have