[R] Dataset re-partitioning consumes considerable amount of memory

A short background: I was trying to create a dataset from a big pile of csv files (couple of hundreds). In first step the csv were parsed and saved to parquet files because there were many inconsistencies between csv files. In a consequent step the dataset was re-partitioned using one column (code_key).

 
```java

new_dataset <- open_dataset(
  temp_parquet_folder, 
  format = "parquet",
  unify_schemas = TRUE
  )
new_dataset |> 
  group_by(code_key) |> 
write_dataset(
    folder_repartitioned_dataset, 
    format = "parquet"
  )
```
 

This re-partitioning consumed a considerable amount of memory (5 GB). 
- Is this a normal behavior?  Or a bug?
- Is there any rule of thumb to estimate the memory requirement for a dataset re-partitioning? (it’s important when scaling up this approach)

The drawback is that this memory space is not freed up after the re-partitioning  (I am using RStudio). 
The `gc()` useless in this situation. And there is no any associated object (to the repartitioning) in the `R` environment which can be removed from memory (using the `rm()` function).
- How one can regain this memory space used by re-partitioning?

The rationale behind choosing the dataset re-partitioning: if my understanding is correct,  in the current arrow version the append is not working when writing parquet files/datasets. (the original csv files were partly partitioned according to a different variable)

Can you recommend any better approach?

**Reporter**: [Zsolt Kegyes-Brassai](https://issues.apache.org/jira/browse/ARROW-16320) / @kbzsl
#### Original Issue Attachments:
- [100m_1_create.jpg](https://issues.apache.org/jira/secure/attachment/13042998/100m_1_create.jpg)
- [100m_2_rds.jpg](https://issues.apache.org/jira/secure/attachment/13042999/100m_2_rds.jpg)
- [100m_3_parquet.jpg](https://issues.apache.org/jira/secure/attachment/13043000/100m_3_parquet.jpg)
- [100m_4_read_rds.jpg](https://issues.apache.org/jira/secure/attachment/13043008/100m_4_read_rds.jpg)
- [100m_5_read-parquet.jpg](https://issues.apache.org/jira/secure/attachment/13043009/100m_5_read-parquet.jpg)
- [Rgui_mem.jpg](https://issues.apache.org/jira/secure/attachment/13042928/Rgui_mem.jpg)
- [Rstudio_env.jpg](https://issues.apache.org/jira/secure/attachment/13042926/Rstudio_env.jpg)
- [Rstudio_mem.jpg](https://issues.apache.org/jira/secure/attachment/13042927/Rstudio_mem.jpg)

_{**Note**: *This issue was originally created as [ARROW-16320](https://issues.apache.org/jira/browse/ARROW-16320). Please see the [migration documentation](https://github.com/apache/arrow/issues/14542) for further details.*}

Start by reproducing the R workflow using open_dataset(), group_by(code_key), and write_dataset() on the referenced parquet dataset while monitoring memory before and after gc(). Determine whether the retained memory is expected or a leak, and document or fix the behavior so memory usage and reclamation are clear.

Written by the indexing model from the issue text.