Skip to content

Truncate Database File on Checkpoint #7824

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 3 commits into from
Jun 5, 2023
Merged

Truncate Database File on Checkpoint #7824

merged 3 commits into from
Jun 5, 2023

Conversation

Mytherin
Copy link
Collaborator

@Mytherin Mytherin commented Jun 5, 2023 •
edited
Loading

This PR adds support for truncating the DuckDB database file on checkpoint if there are free blocks at the end of the file. As a result - we can now shrink the DuckDB file if tables or rows are deleted and there is space left at the end of the file.

Note that this won't always work as expected in its current form. If we drop a big table and then checkpoint, the file will likely not shrink. That is because as part of the checkpoint process we will write new metadata to the file. As we cannot overwrite the big table yet (to maintain ACID properties) - the metadata is written to the end of the file. As a result of the new metadata being at the end of the file, the file will not be able to be shrunk yet.

On a subsequent checkpoint, the file will be able to shrink as the new metadata can then overwrite the old data in the big table.

For example:

CREATE TABLE uuids(i VARCHAR);
INSERT INTO uuids SELECT uuid()::varchar FROM range(1000000);
PRAGMA database_size;
┌───────────────┬───────────────┬────────────┬──────────────┬─────────────┬─────────────┬──────────┬──────────────┬──────────────┐
│ database_name │ database_size │ block_size │ total_blocks │ used_blocks │ free_blocks │ wal_size │ memory_usage │ memory_limit │
│    varchar    │    varchar    │   int64    │    int64     │    int64    │    int64    │ varchar  │   varchar    │   varchar    │
├───────────────┼───────────────┼────────────┼──────────────┼─────────────┼─────────────┼──────────┼──────────────┼──────────────┤
│ test          │ 22.0MB        │     262144 │           84 │          84 │           0 │ 0 bytes  │ 21.4MB       │ 54.9GB       │
└───────────────┴───────────────┴────────────┴──────────────┴─────────────┴─────────────┴──────────┴──────────────┴──────────────┘
-- deleting and then checkpointing does not free any space
DELETE FROM uuids;
CHECKPOINT;
PRAGMA database_size;
┌───────────────┬───────────────┬────────────┬──────────────┬─────────────┬─────────────┬──────────┬──────────────┬──────────────┐
│ database_name │ database_size │ block_size │ total_blocks │ used_blocks │ free_blocks │ wal_size │ memory_usage │ memory_limit │
│    varchar    │    varchar    │   int64    │    int64     │    int64    │    int64    │ varchar  │   varchar    │   varchar    │
├───────────────┼───────────────┼────────────┼──────────────┼─────────────┼─────────────┼──────────┼──────────────┼──────────────┤
│ test          │ 22.0MB        │     262144 │           84 │          84 │           0 │ 8.0MB    │ 21.4MB       │ 54.9GB       │
└───────────────┴───────────────┴────────────┴──────────────┴─────────────┴─────────────┴──────────┴──────────────┴──────────────┘
-- performing an unrelated change and checkpointing again frees the space
CREATE TABLE new_table(i INT);
CHECKPOINT;
PRAGMA database_size;
┌───────────────┬───────────────┬────────────┬──────────────┬─────────────┬─────────────┬──────────┬──────────────┬──────────────┐
│ database_name │ database_size │ block_size │ total_blocks │ used_blocks │ free_blocks │ wal_size │ memory_usage │ memory_limit │
│    varchar    │    varchar    │   int64    │    int64     │    int64    │    int64    │ varchar  │   varchar    │   varchar    │
├───────────────┼───────────────┼────────────┼──────────────┼─────────────┼─────────────┼──────────┼──────────────┼──────────────┤
│ test          │ 786KB         │     262144 │            3 │           3 │           0 │ 0 bytes  │ 0 bytes      │ 54.9GB       │
└───────────────┴───────────────┴────────────┴──────────────┴─────────────┴─────────────┴──────────┴──────────────┴──────────────┘

@Mytherin Mytherin merged commit 4b93477 into duckdb:feature Jun 5, 2023
@jwills jwills mentioned this pull request Jun 6, 2023
Open
@Mytherin Mytherin deleted the truncatecheckpoint branch July 5, 2023 03:40
@Mytherin Mytherin mentioned this pull request Aug 8, 2023
Merged
@Mause Mause mentioned this pull request Oct 9, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
1 participant
@Mytherin