Skip to content

Storage: make ROW_GROUP_SIZE configurable #14406

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 11 commits into from
Oct 17, 2024
Merged

Storage: make ROW_GROUP_SIZE configurable #14406

merged 11 commits into from
Oct 17, 2024

Conversation

Mytherin
Copy link
Collaborator

@Mytherin Mytherin commented Oct 16, 2024 •
edited
Loading

This PR makes the ROW_GROUP_SIZE of DuckDB's storage format configurable using the ROW_GROUP_SIZE parameter that can be passed in when attaching:

ATTACH 'file.db' (ROW_GROUP_SIZE 204800);

If none is specified, the default row group size (122880) is chosen (which is also the current row group size).

The row group size influences the target row group size when ingesting data into a table. Note that the row group size only influences new data written to a database. A database can also be attached with a different row group size. The row group size does not need to be fixed within the same database file, even for row groups within the same table, e.g. the following is valid:

ATTACH 'file.db' (ROW_GROUP_SIZE 204800);
CREATE TABLE file.tbl AS FROM range(1000000) t(i);
DETACH file;
ATTACH 'file.db' (ROW_GROUP_SIZE 122880);
INSERT INTO file.tbl FROM range(1000000);

SELECT row_group_id, MAX(count) FROM pragma_storage_info('file.tbl') GROUP BY ALL ORDER BY ALL;
┌──────────────┬────────────┐
│ row_group_id │ max(count) │
│    int64     │   int64    │
├──────────────┼────────────┤
│            0 │     204800 │
│            1 │     204800 │
│            2 │     204800 │
│            3 │     204800 │
│            4 │     180800 │
│            5 │     122880 │
│            6 │     122880 │
│            7 │     122880 │
│            8 │     122880 │
│            9 │     122880 │
│           10 │     122880 │
│           11 │     122880 │
│           12 │     122880 │
│           13 │      16960 │
├──────────────┴────────────┤
│ 14 rows         2 columns │
└───────────────────────────┘

Backwards Compatibility

Previous versions of DuckDB can read files with varying row group sizes, however they do not support updating or deleting rows in tables with row group sizes > 122880 as the version manager/update manager are hard-coded to support only up to 122880 rows.

Mytherin added 11 commits October 15, 2024 17:52
@Mytherin Mytherin merged commit d4a62a1 into duckdb:feature Oct 17, 2024
40 checks passed
@abramk
Copy link
Contributor

abramk commented Oct 17, 2024

@Mytherin can you share some advice on optimal ROW_GROUP_SIZE by workload? I'm thinking specifically two types of tables - small tables that have a lot of inserts/updates/deletes and large tables that are bulk-populated using the appender api.

@Mytherin
Copy link
Collaborator Author

We haven't done much experimentation with it - but logically row group sizes are the threshold at which (1) parallelism happens, (2) checkpoints happen, (3) compression happens. Larger row groups means you need larger data sets to get good parallelism, and it means you need to rewrite more data when changes are made to a row group (e.g. updates/deletes). On the flip side, if your row groups are too small, your compression ratio suffers.

I would therefore say that large tables that don't have any updates/deletes applied to them could benefit from larger row group sizes - but small tables with lots of interactions could benefit from smaller row group sizes. The degree to which this matters should still be profiled and validated, though.

@Mytherin Mytherin deleted the configurablerowgroupsize branch December 8, 2024 06:52
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.
2 participants
@Mytherin @abramk